[Java] Sample code to get values of major JDBC types in Java + H2 Database

5 minute read

Overview

  • Show the sample code that defines the main JDBC type column in the table in H2 Database and gets the column value from the Java program
  • Confirmation environment of this time: H2 Database 1.4.200 + Java 14 (AdoptOpenJDK 14.0.2) + Gradle 6.5.1 + macOS Catalina

What is a JDBC type?

A JDBC type is a type that bridges between SQL types and Java language types. Similar to the type that represents SQL data (such as CHAR or INTEGER).

SQL type ← (mapping) → JDBC type ← (mapping) → Java language type

Introduction to JDBC API - SQL and Java type mapping

There are considerable differences between the SQL types supported by different database products. Even if different databases support SQL types that have the same meaning, they may give them different names. For example, most major databases support SQL types for large binary values, but Oracle calls these types LONG RAW, Sybase IMAGE, Informix BYTE, and DB2 LONG VARCHAR FOR BIT DATA. ..

JDBC programmers usually do not have to worry about the actual SQL type names used by the target database. In many cases, JDBC programmers do not have to program into existing database tables and pay attention to the exact SQL type name that created them.

JDBC defines a set of generic SQL type identifiers in class java.sql.Types. The types in that set are designed to represent the most commonly used SQL types. Programming with the JDBC API allows programmers to refer to a generic SQL type using that set of JDBC types without having to be aware of the exact SQL type name used by the target database. ..

Sample code

File list

├── build.gradle
└── src
    └── main
        └── java
            └── JdbcSample.java

build.gradle

plugins {
  id'application'
  id'java'
}

sourceCompatibility = JavaVersion.VERSION_14

repositories {
  mavenCentral()
}

dependencies {
  // Introduced H2 Database 1.4.200
  implementation'com.h2database:h2:1.4.200'
}

tasks.withType(JavaCompile) {
  // Use Java 14 preview function
  options.compilerArgs += ['--enable-preview']
}

application {
  // Use Java 14 preview function
  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
    // define columns with different JDBC types (those that differ slightly from SQL data types)
    // (Use the text block function that can be written like a here document that can be used in 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

        --Integer type
        my_smallint SMALLINT, --short
        my_integer INTEGER, --int
        my_bigint BIGINT, --long

        --Floating point type
        my_real REAL, --single precision 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, --hour minute second
        my_timestamp TIMESTAMP --Date + Hour Minute Second + Nanosecond
      )""");

    // 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', --LONG VARCHAR
        '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 valuesSystem.out.println("カラム名 - カラムの値");

      // 文字列型
      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"));

      // バイナリ型
      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"));

      // 真偽値型
      System.out.println("my_boolean=" + rs.getBoolean("my_boolean"));

      // 整数型
      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"));

      // 浮動小数点型
      System.out.println("my_real=" + rs.getFloat("my_real"));
      System.out.println("my_double=" + rs.getDouble("my_double"));

      // 固定小数点型
      System.out.println("my_numeric=" + rs.getBigDecimal("my_numeric"));
      System.out.println("my_decimal=" + rs.getBigDecimal("my_decimal"));

      // 時間型
      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();
  }
}

実行結果

Gradle の run タスクで実行。

$ gradle run

> Task :compileJava
注意:/Users/foo/bar/src/main/java/JdbcSample.javaはプレビュー言語機能を使用します。
注意:詳細は、-Xlint:previewオプションを指定して再コンパイルしてください。

> Task :run
カラム名 - JDBC 型 - Java オブジェクトの型
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

カラム名 - カラムの値
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

参考資料