[Java] Sample code to get values of major SQL types in Java + MySQL 8.0

5 minute read

Overview

  • In MySQL 8.0, the column of the main SQL data type is defined in the table, and the sample code to get the value of the column from the Java program is shown.
  • Operating environment for this time: MySQL Ver 8.0.21 for osx10.15 on x86_64 (Homebrew) + MySQL Connector/J 8.0.21 + Java 14 (AdoptOpenJDK 14.0.2) + Gradle 6.6 + macOS Catalina

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 {
  // Use MySQL Connector/J 8.0.21 at runtime
  runtimeOnly'mysql:mysql-connector-java:8.0.21'
}

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.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.util.Arrays;

class JdbcSample {

  public static void main(String[] args) throws Exception {

    // connect to MySQL
    String url = "jdbc:mysql://localhost/testdb";
    String user = "foo";
    String password = "cafebabe";
    Connection con = DriverManager.getConnection(url, user, password);
    Statement stmt = con.createStatement();

    // create table
    // define columns with various MySQL 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(8), --fixed length string (max length 255 characters)
        my_varchar VARCHAR(1024), --Variable length string (max length 65535 bytes (however, the specified number is the number of characters))
        my_tinytext TINYTEXT, --Variable length string (max length 255 bytes)
        my_text TEXT, --Variable length string (max length 65535 bytes)
        my_mediumtext MEDIUMTEXT, --Variable length string (max length 16777215 bytes)
        my_longtext LONGTEXT, --Variable length string (max length 4294967295 bytes)

        --Binary type
        my_bit BIT(16), --bit field value (max length 64 bits)
        my_binary BINARY(4), --fixed length binary (max length 255 bytes)
        my_varbinary VARBINARY(4), --Variable length binary (max length 65535 bytes)
        my_tinyblob TINYBLOB, --Binary Large Object (max length 255 bytes)
        my_blob BLOB, --Binary Large Object (max length 65535 bytes)
        my_mediumblob MEDIUMBLOB, --Binary Large Object (max length 16777215 bytes)
        my_longblob LONGBLOB, --Binary Large Object (max length 4294967295 bytes)

        --Boolean type
        my_boolean BOOLEAN, --Boolean

        --Integer type
        my_tinyint TINYINT, --1 byte
        my_smallint SMALLINT, --2 bytes
        my_mediumint MEDIUMINT, --3 bytes
        my_integer INTEGER, --4 bytes
        my_bigint BIGINT, --8 bytes

        --Floating point type
        my_float FLOAT, --single precision float 4 bytes
        my_double DOUBLE, --double precision floating point number double 8 bytes

        --Fixed point type
        my_numeric NUMERIC, --fixed point number
        my_decimal DECIMAL, --fixed point number

        --Time type
        my_date DATE, --date
        my_time TIME, --hour minute second
        my_datetime DATETIME(6), --year month day + hour minute second + microsecond
        my_timestamp TIMESTAMP(6)-year month day + hour minute second + microsecond + time zone
      ) ENGINE=InnoDB""");

    // add record
    // (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("""
      insert into test values (
        --String type
        'Hello', --CHAR
        'Hello', --VARCHAR
        'Hello', --TINYTEXT
        'Hello', --TEXT
        'Hello', --MEDIUMTEXT
        'Hello', --LONGTEXT

        --Binary type
        b'0111111110000000', --BIT
        X'CAFEBABE', --BINARY,
        X'CAFEBABE', --VARBINARY,
        X'CAFEBABE', --TINYBLOB,
        X'CAFEBABE', --BLOB,
        X'CAFEBABE', --MEDIUMBLOB,
        X'CAFEBABE', --LONGBLOB,

        --Boolean type
        TRUE, --BOOLEAN

        --Integer type
        127 ,-- TINYINT
        32767 ,-- SMALLINT
        8388607 ,-- MEDIUMINT
        2147483647 ,-- INTEGER
        9223372036854775807,-BIGINT

        --Floating point type
        123.0001, --FLOAT
        123.0001, --DOUBLE

        --Fixed point type
        123.0001, --NUMERIC
        123.0001, --DECIMAL

        --Time type
        '2001-02-03', --DATE
        '04:05:06', --TIME
        '9999-12-31 23:59:59.999999', --DATETIME
        '2038-01-19 03:14:07.999999+00:00'-TIMESTAMP
      )""");

    // get record
    ResultSet rs = stmt.executeQuery("select * from test");
    while (rs.next()) {

      // Get the Java object type for the column's JDBC or SQL type
      System.out.println("Column name-JDBC type-Database specific SQL type-Java object type");
      ResultSetMetaData rsmd = rs.getMetaData();
      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        System.out.println(rsmd.getColumnName(i) + " - " +
          getJdbcTypeName(rsmd.getColumnType(i)) + " - " +
          rsmd.getColumnTypeName(i) + " - " +
          rsmd.getColumnClassName(i));
      }
      System.out.println();

      // カラムの値を取得していく
      System.out.println("カラム名 - カラムの値");

      // 文字列型
      System.out.println("my_char=" + rs.getString("my_char"));
      System.out.println("my_varchar=" + rs.getString("my_varchar"));
      System.out.println("my_tinytext=" + rs.getString("my_tinytext"));
      System.out.println("my_text=" + rs.getString("my_text"));
      System.out.println("my_mediumtext=" + rs.getString("my_mediumtext"));
      System.out.println("my_longtext=" + rs.getString("my_longtext"));

      // バイナリ型
      System.out.println("my_bit=" + Arrays.toString(rs.getBytes("my_bit")));
      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_tinyblob=" + Arrays.toString(rs.getBytes("my_tinyblob")));
      System.out.println("my_blob=" + Arrays.toString(rs.getBytes("my_blob")));
      System.out.println("my_mediumblob=" + Arrays.toString(rs.getBytes("my_mediumblob")));
      System.out.println("my_longblob=" + Arrays.toString(rs.getBytes("my_longblob")));

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

      // 整数型
      System.out.println("my_tinyint=" + rs.getInt("my_tinyint"));
      System.out.println("my_smallint=" + rs.getInt("my_smallint"));
      System.out.println("my_mediumint=" + rs.getInt("my_mediumint"));
      System.out.println("my_integer=" + rs.getInt("my_integer"));
      System.out.println("my_bigint=" + rs.getLong("my_bigint"));

      // 浮動小数点型
      System.out.println("my_float=" + rs.getFloat("my_float"));
      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_datetime=" + rs.getTimestamp("my_datetime"));
      System.out.println("my_timestamp=" + rs.getTimestamp("my_timestamp").toInstant());
    }

    stmt.close();
    con.close();
  }

  // JDBC 型の名称を取得する
  private static String getJdbcTypeName(int type) throws IllegalAccessException {
    Field[] fs = Types.class.getDeclaredFields();
    for (Field f : fs) {
      if (type == f.getInt(null)) {
        return f.getName();
      }
    }
    return null;
  }
}

実行結果

Gradle の run タスクで実行。

$ gradle run

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

> Task :run
カラム名 - JDBC 型 - データベース固有の SQL 型 - Java オブジェクトの型
my_char - CHAR - CHAR - java.lang.String
my_varchar - VARCHAR - VARCHAR - java.lang.String
my_tinytext - VARCHAR - TINYTEXT - java.lang.String
my_text - LONGVARCHAR - TEXT - java.lang.String
my_mediumtext - LONGVARCHAR - MEDIUMTEXT - java.lang.String
my_longtext - LONGVARCHAR - LONGTEXT - java.lang.String
my_bit - BIT - BIT - java.lang.Boolean
my_binary - BINARY - BINARY - [B
my_varbinary - VARBINARY - VARBINARY - [B
my_tinyblob - VARBINARY - TINYBLOB - [B
my_blob - LONGVARBINARY - BLOB - [B
my_mediumblob - LONGVARBINARY - MEDIUMBLOB - [B
my_longblob - LONGVARBINARY - LONGBLOB - [B
my_boolean - BIT - BIT - java.lang.Boolean
my_tinyint - TINYINT - TINYINT - java.lang.Integer
my_smallint - SMALLINT - SMALLINT - java.lang.Integer
my_mediumint - INTEGER - MEDIUMINT - java.lang.Integer
my_integer - INTEGER - INT - java.lang.Integer
my_bigint - BIGINT - BIGINT - java.lang.Long
my_float - REAL - FLOAT - java.lang.Float
my_double - DOUBLE - DOUBLE - java.lang.Double
my_numeric - DECIMAL - DECIMAL - java.math.BigDecimal
my_decimal - DECIMAL - DECIMAL - java.math.BigDecimal
my_date - DATE - DATE - java.sql.Date
my_time - TIME - TIME - java.sql.Time
my_datetime - TIMESTAMP - DATETIME - java.sql.Timestamp
my_timestamp - TIMESTAMP - TIMESTAMP - java.sql.Timestamp

カラム名 - カラムの値
my_char=Hello
my_varchar=Hello
my_tinytext=Hello
my_text=Hello
my_mediumtext=Hello
my_longtext=Hello
my_bit=[127, -128]
my_binary=[-54, -2, -70, -66]
my_varbinary=[-54, -2, -70, -66]
my_tinyblob=[-54, -2, -70, -66]
my_blob=[-54, -2, -70, -66]
my_mediumblob=[-54, -2, -70, -66]
my_longblob=[-54, -2, -70, -66]
my_boolean=true
my_tinyint=127
my_smallint=32767
my_mediumint=8388607
my_integer=2147483647
my_bigint=9223372036854775807
my_float=123.0
my_double=123.0001
my_numeric=123
my_decimal=123
my_date=2001-02-03
my_time=04:05:06
my_datetime=9999-12-31 23:59:59.999999
my_timestamp=2038-01-19T03:14:07.999999Z

BUILD SUCCESSFUL in 1s
2 actionable tasks: 2 executed

参考資料- JDBC API 入門 - SQL と Java の型のマッピング