--Show sample code to define major JDBC type columns in a table in H2 Database and get column values from Java program --Operation check environment this time: H2 Database 1.4.200 + Java 14 (AdoptOpenJDK 14.0.2) + Gradle 6.5.1 + macOS Catalina
JDBC types are types that intervene between SQL types and Java language types. Close to a type that represents SQL data (such as CHAR or INTEGER).
SQL type ← (mapping) → JDBC type ← (mapping) → Java language type
Introduction to JDBC API -Mapping between SQL and Java types
There are significant differences between the SQL types supported by different database products. Even if different databases support SQL types that have the same meaning, they may be given different names. For example, most major databases support SQL types for large binary values, which Oracle calls LONG RAW, Sybase IMAGE, Informix BYTE, and DB2 LONG VARCHAR FOR BIT DATA. ..
JDBC programmers usually don't have to worry about the actual SQL type name used by the target database. In many cases, JDBC programmers do not need to program on existing database tables and pay attention to the exact SQL type name that created those tables.
JDBC defines a set of generic SQL type identifiers in the class java.sql.Types. The types in the set are designed to represent the most commonly used SQL types. Programming with the JDBC API allows programmers to use the set of JDBC types to reference generic SQL types without being aware of the exact SQL type name used by the target database. ..
├── build.gradle
└── src
└── main
└── java
└── JdbcSample.java
build.gradle
plugins {
id 'application'
id 'java'
}
sourceCompatibility = JavaVersion.VERSION_14
repositories {
mavenCentral()
}
dependencies {
// H2 Database 1.4.Introduced 200
implementation 'com.h2database:h2:1.4.200'
}
tasks.withType(JavaCompile) {
//Use Java 14 preview feature
options.compilerArgs += ['--enable-preview']
}
application {
//Use Java 14 preview feature
applicationDefaultJvmArgs = ['--enable-preview']
mainClassName = 'JdbcSample'
}
JdbcSample.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Arrays;
class JdbcSample {
public static void main(String[] args) throws Exception {
//Connect to H2 Database
// mem:In-memory database
// DB_CLOSE_DELAY=-1:Do not delete DB contents when disconnecting
String url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";
String user = "sa";
String password = "";
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
//Create table
//Various JDBC types(Subtly different from SQL data types)Define column with
// (Use the text block function that can be written like a here document that can be used with the Java 14 preview function)
stmt.execute("""
create table test (
--String type
my_char CHAR, --Short fixed length string
my_varchar VARCHAR, --Short variable length string
my_longvarchar LONGVARCHAR, --Long variable length string
my_clob CLOB, -- Character Large Object
--Binary type
my_binary BINARY(4), --Small fixed length binary(SQL data type BIT)
my_varbinary VARBINARY(4), --Small variable length binary(SQL data type BIT VARYING)
my_longvarbinary LONGVARBINARY(4), --Large variable length binary
my_blob BLOB(4), -- Binary Large Object
--Boolean type
my_boolean BOOLEAN, --Boolean value
--Integer type
my_smallint SMALLINT, -- short
my_integer INTEGER, -- int
my_bigint BIGINT, -- long
--Floating point type
my_real REAL, --Single precision floating point number float
my_double DOUBLE, --Double precision floating point number double
--Fixed point type
my_numeric NUMERIC, --Fixed point number
my_decimal DECIMAL, --Fixed point type
--Time type
my_date DATE, --date
my_time TIME, --Hours, minutes, and seconds
my_timestamp TIMESTAMP --date+Hours, minutes, and seconds+Nanoseconds
)""");
//Add record
// (Use the text block function that can be written like a here document that can be used with the Java 14 preview function)
stmt.execute("""
insert into test values (
--String type
'Hello', -- CHAR
'Hello', -- VARCHAR
'Hello', -- LONGVARCHAR
'Hello', -- CLOB
--Binary type
X'CAFEBABE', -- BINARY,
X'CAFEBABE', -- VARBINARY,
X'CAFEBABE', -- LONGVARBINARY,
X'CAFEBABE', -- BLOB,
--Boolean type
TRUE, -- BOOLEAN
--Integer type
32767 , -- SMALLINT
2147483647 , -- INTEGER
9223372036854775807, -- BIGINT
--Floating point type
123.0001, -- REAL
123.0001, -- DOUBLE
--Fixed point type
123.0001, -- NUMERIC
123.0001, -- DECIMAL
--Time type
'2001-02-03', -- DATE
'04:05:06', -- TIME
'2001-02-03 04:05:06.123456789' -- TIMESTAMP
)""");
//Get record
ResultSet rs = stmt.executeQuery("select * from test");
while (rs.next()) {
//Get the Java object type for the column's JDBC type
System.out.println("Column name-JDBC type-Java object type");
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.println(
rsmd.getColumnName(i) + " - " +
rsmd.getColumnTypeName(i) + " - " +
rsmd.getColumnClassName(i));
}
System.out.println();
//Get column values
System.out.println("Column name-Column value");
//String type
System.out.println("my_char=" + rs.getString("my_char"));
System.out.println("my_varchar=" + rs.getString("my_varchar"));
System.out.println("my_longvarchar=" + rs.getString("my_longvarchar"));
System.out.println("my_clob=" + rs.getClob("my_clob"));
//Binary type
System.out.println("my_binary=" + Arrays.toString(rs.getBytes("my_binary")));
System.out.println("my_varbinary=" + Arrays.toString(rs.getBytes("my_varbinary")));
System.out.println("my_longvarbinary=" + Arrays.toString(rs.getBytes("my_longvarbinary")));
System.out.println("my_blob=" + rs.getBlob("my_blob"));
//Boolean type
System.out.println("my_boolean=" + rs.getBoolean("my_boolean"));
//Integer type
System.out.println("my_smallint=" + rs.getShort("my_smallint"));
System.out.println("my_integer=" + rs.getInt("my_integer"));
System.out.println("my_bigint=" + rs.getBigDecimal("my_bigint"));
//Floating point type
System.out.println("my_real=" + rs.getFloat("my_real"));
System.out.println("my_double=" + rs.getDouble("my_double"));
//Fixed point type
System.out.println("my_numeric=" + rs.getBigDecimal("my_numeric"));
System.out.println("my_decimal=" + rs.getBigDecimal("my_decimal"));
//Time type
System.out.println("my_date=" + rs.getDate("my_date"));
System.out.println("my_time=" + rs.getTime("my_time"));
System.out.println("my_timestamp=" + rs.getTimestamp("my_timestamp"));
}
stmt.close();
con.close();
}
}
Run with Gradle's run task.
$ gradle run
> Task :compileJava
Caution:/Users/foo/bar/src/main/java/JdbcSample.java uses the preview language feature.
Caution:Detail is,-Xlint:Please recompile with the preview option.
> Task :run
Column name-JDBC type-Java object type
MY_CHAR - CHAR - java.lang.String
MY_VARCHAR - VARCHAR - java.lang.String
MY_LONGVARCHAR - VARCHAR - java.lang.String
MY_CLOB - CLOB - java.sql.Clob
MY_BINARY - VARBINARY - [B
MY_VARBINARY - VARBINARY - [B
MY_LONGVARBINARY - VARBINARY - [B
MY_BLOB - BLOB - java.sql.Blob
MY_BOOLEAN - BOOLEAN - java.lang.Boolean
MY_SMALLINT - SMALLINT - java.lang.Short
MY_INTEGER - INTEGER - java.lang.Integer
MY_BIGINT - BIGINT - java.lang.Long
MY_REAL - REAL - java.lang.Float
MY_DOUBLE - DOUBLE - java.lang.Double
MY_NUMERIC - DECIMAL - java.math.BigDecimal
MY_DECIMAL - DECIMAL - java.math.BigDecimal
MY_DATE - DATE - java.sql.Date
MY_TIME - TIME - java.sql.Time
MY_TIMESTAMP - TIMESTAMP - java.sql.Timestamp
Column name-Column value
my_char=Hello
my_varchar=Hello
my_longvarchar=Hello
my_clob=clob0: 'Hello'
my_binary=[-54, -2, -70, -66]
my_varbinary=[-54, -2, -70, -66]
my_longvarbinary=[-54, -2, -70, -66]
my_blob=blob0: X'cafebabe'
my_boolean=true
my_smallint=32767
my_integer=2147483647
my_bigint=9223372036854775807
my_real=123.0001
my_double=123.0001
my_numeric=123.0001
my_decimal=123.0001
my_date=2001-02-03
my_time=04:05:06
my_timestamp=2001-02-03 04:05:06.123457
BUILD SUCCESSFUL in 1s
2 actionable tasks: 2 executed
-Introduction to JDBC API -Mapping between SQL and Java types
Recommended Posts