--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
├── build.gradle
└── src
└── main
└── java
└── JdbcSample.java
build.gradle
plugins {
id 'application'
id 'java'
}
sourceCompatibility = JavaVersion.VERSION_14
repositories {
mavenCentral()
}
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'
}
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 with the Java 14 preview function)
stmt.execute("""
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)
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 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++) {
System.out.println(
rsmd.getColumnName(i) + " - " +
getJdbcTypeName(rsmd.getColumnType(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_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());
}
stmt.close();
con.close();
}
//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;
}
}
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-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_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
Column name-Column value
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
-Introduction to JDBC API -Mapping between SQL and Java types
Recommended Posts