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


--Show sample code in MySQL 8.0 that defines columns of major SQL data types in a table and retrieves column values from a Java program. --Operation check environment 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


plugins {
  id 'application'
  id 'java'

sourceCompatibility = JavaVersion.VERSION_14

repositories {

dependencies {
  //MySQL Connector at run time/J 8.0.Use 21
  runtimeOnly 'mysql:mysql-connector-java:8.0.21'

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

application {
  //Use Java 14 preview feature
  applicationDefaultJvmArgs = ['--enable-preview']
  mainClassName = 'JdbcSample'

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 with the Java 14 preview function)
      create table test (
        --String type
        my_char        CHAR(8),        --Fixed length character string(Maximum length 255 characters)
        my_varchar     VARCHAR(1024),  --Variable length string(Maximum length 65535 bytes(However, the specified number is the number of characters))
        my_tinytext    TINYTEXT,       --Variable length string(Maximum length 255 bytes)
        my_text        TEXT,           --Variable length string(Maximum length 65535 bytes)
        my_mediumtext  MEDIUMTEXT,     --Variable length string(Maximum length 16777215 bytes)
        my_longtext    LONGTEXT,       --Variable length string(Maximum length 4294967295 bytes)

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

        --Boolean type
        my_boolean  BOOLEAN,  --Boolean value

        --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 floating point number float 4 bytes
        my_double  DOUBLE,  --Double precision floating point 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,         --Hours, minutes, and seconds
        my_datetime   DATETIME(6),  --date+Hours, minutes, and seconds+Microseconds
        my_timestamp  TIMESTAMP(6)  --date+Hours, minutes, and seconds+Microseconds+Time zone
      ) ENGINE=InnoDB""");

    //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)
      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 ( {

      //Get the type of a Java object for a 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++) {
          rsmd.getColumnName(i) + " - " +
          getJdbcTypeName(rsmd.getColumnType(i)) + " - " +
          rsmd.getColumnTypeName(i) + " - " +

      //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_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"));

      //Binary type
      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")));

      //Boolean type
      System.out.println("my_boolean=" + rs.getBoolean("my_boolean"));

      //Integer type
      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"));

      //Floating point type
      System.out.println("my_float=" + rs.getFloat("my_float"));
      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_datetime=" + rs.getTimestamp("my_datetime"));
      System.out.println("my_timestamp=" + rs.getTimestamp("my_timestamp").toInstant());


  //Get JDBC type name
  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;

Execution result

Run with Gradle's run task.

$ gradle run

> Task :compileJava
Caution:/Users/foo/bar/src/main/java/ uses the preview language feature.
Caution:Detail is,-Xlint:Please recompile with the preview option.

> Task :run
Column name-JDBC type-Database-specific SQL type-Java object type
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_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

Column name-Column value
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_datetime=9999-12-31 23:59:59.999999

2 actionable tasks: 2 executed

Reference material

-Introduction to JDBC API -Mapping between SQL and Java types

Recommended Posts

Sample code to get the values of major SQL types in Java + MySQL 8.0
Sample code to get the values of major SQL types in Java + Oracle Database 12c
Sample code to get key JDBC type values in Java + H2 Database
Sample source code for finding the least common multiple of multiple values in Java
Get the result of POST in Java
How to get the date in java
Sample code to call the Yahoo! Local Search API in Java
How to get the length of an audio file in java
How to get the absolute path of a directory running in Java
[Java] How to get the authority of the folder
Conditional branching of the result of SQL statement to search only one in Java
[Java] How to get the URL of the transition source
Sample code to convert List to List <String> in Java Stream
[Java] How to get the maximum value of HashMap
Source used to get the redirect source URL in Java
[Java] Get the file in the jar regardless of the environment
How to get the class name of the argument of LoggerFactory.getLogger when using SLF4J in Java
How to get the class name / method name running in Java
A quick explanation of the five types of static in Java
Get to the abbreviations from 5 examples of iterating Java lists
Things to be aware of when writing code in Java
How to derive the last day of the month in Java
Guess the character code in Java
Get Null-safe Map values in Java
List of types added in Java 9
[Java] Get the dates of the past Monday and Sunday in order
The milliseconds to set in /lib/ of Java jre is UTC
Sample code that uses the Mustache template engine JMustache in Java
How to increment the value of Map in one line in Java
SQL to get the schema list (Oracle / MySQL / PostgreSQL / SQLServer / Cassandra)
Java reference to understand in the figure
Sample code to serialize and deserialize Java Enum enums and JSON in Jackson
The story of forgetting to close a file in Java and failing
[Java] How to get the current directory
[Java] How to get to the front of a specific string using the String class
How to find the total number of pages when paging in Java
Sample program that returns the hash value of a file in Java
How to get the value after "_" in Windows batch like Java -version
I want to get the IP address when connecting to Wi-Fi in Java
From Java9, the constructor of the class corresponding to primitive types is deprecated.
I want to get the field name of the [Java] field. (Old tale tone)
The story of writing Java in Emacs
Sample to unzip gz file in Java
How to create your own annotation in Java and get the value
[Swift] How to get the number of elements in an array (super basic)
graphql-ruby: How to get the name of query or mutation in controller Note
How to get the ID of a user authenticated with Firebase in Swift
[Java] Program example to get the maximum and minimum values from an array
Summarize the life cycle of Java objects to be aware of in Android development
[Java] How to easily get the longest character string of ArrayList using stream
[Rails5.2] Support for emoji of Mysql 5.7 in Docker (change character code to utf8mb4)
Let's refer to C ++ in the module of AndroidStudio other project (Java / kotlin)
Code that deletes all files of the specified prefix in AWS S3 (Java)
The story of low-level string comparison in Java
[Java] Handling of JavaBeans in the method chain
The story of making ordinary Othello in Java
About the idea of anonymous classes in Java
The story of learning Java in the first programming
Measure the size of a folder in Java
[Java] Get the length of the surrogate pair string
[Java] Explanation of Strategy pattern (with sample code)