Java/Database SQL JDBC/Database Type vs Java Type

Get Available Sql Types In A Database

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    ResultSet rs = null;
    DatabaseMetaData meta = conn.getMetaData();
    rs = meta.getTypeInfo();
    while (rs.next()) {
      // Get the database-specific type name
      String typeName = rs.getString("TYPE_NAME");
      // Get the java.sql.Types type to which this
      // database-specific type is mapped
      short dataType = rs.getShort("DATA_TYPE");
      // Get the name of the java.sql.Types value.
      System.out.println("type name="+typeName);
      System.out.println("dataType="+dataType);
      System.out.println("jdbcType="+dataType);
    }   
    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Get Available Table Types

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    ResultSet rs = null;
    DatabaseMetaData meta = conn.getMetaData();
    rs = meta.getTableTypes();
    while (rs.next()) {
      String tableType = rs.getString(1);
      System.out.println("tableType=" + tableType);
    }
    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/octopus";
    String username = "root";
    String password = "root";
    Class.forName(driver); // load MySQL driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Sql DateTime

 
public class SqlDateTime {
  public static void main(String[] args) {
    java.util.Date javaDate = new java.util.Date();
    long javaTime = javaDate.getTime();
    System.out.println("The Java Date is: " + javaDate.toString());
    java.sql.Date sqlDate = new java.sql.Date(javaTime);
    System.out.println("The SQL DATE is: " + sqlDate.toString());
    java.sql.Time sqlTime = new java.sql.Time(javaTime);
    System.out.println("The SQL TIME is: " + sqlTime.toString());
    java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(javaTime);
    System.out.println("The SQL TIMESTAMP is: " + sqlTimestamp.toString());
  }
}

Standard SQL Data Types with Their Java Equivalents

 
SQL type            Java Type                 Description
BINARY              byte[]                    Byte array. Used for binary large objects.
BIT                 boolean                   Boolean 0 / 1 value
CHAR                String                    Fixed-length character string. 
DATETIME            java.sql.Date             Date and Time as: yyyy-mm-dd hh:mm:ss
DECIMAL             java.math.BigDecimal      Arbitrary-precision signed decimal numbers. 
FLOAT               double                    Floating-point number, mapped to double
INTEGER             int                       32-bit integer values
LONGVARBINARY       byte[]                    Variable-length character string. 
LONGVARCHAR         String                    Variable-length character string. 
NCHAR               String                    National Character Unicode fixed-length character string
NUMERIC             java.math.BigDecimal      Arbitrary-precision signed decimal numbers. 
NTEXT               String                    Large string variables. 
NVARCHAR            String                    National Character Unicode variable-length character string
REAL                float                     Floating-point number, mapped to float
SMALLINT            short                     16-bit integer values
TIME                java.sql.Time             Thin wrapper around java.util.Date
TIMESTAMP           java.sql.Timestamp        Composite of a java.util.Date and a separate nanosecond value
VARBINARY           byte[]                    Byte array
VARCHAR             String                    Variable-length character string.