--Show sample code in Oracle Database 12c that defines columns of major SQL data types in a table and retrieves column values from a Java program. --Operation check environment this time: Oracle Database 12c Release 2 (12.2.0.1.0) Enterprise Edition (on Docker) + Oracle JDBC Thin driver (ojdbc8.jar) 19.7.0.0 + 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 {
//Specify runtimeOnly if you just want to use the Oracle JDBC Driver at runtime
//runtimeOnly 'com.oracle.database.jdbc:ojdbc8:19.7.0.0'
//This time oracle.jdbc.Specify implementation to use OracleTypes
implementation 'com.oracle.database.jdbc:ojdbc8:19.7.0.0'
}
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 oracle.jdbc.OracleTypes; //Not required if you just use it normally(This time used to get information about Oracle extended JDBC type)
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:oracle:thin:@//localhost:1521/testdb";
String user = "javarista";
String password = "cafebabe";
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
//Create table
//Define columns with various SQL 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 CHAR), --Fixed length character string(Basically maximum length 2000 bytes)
my_varchar2 VARCHAR2(512 CHAR), --Variable length string(Basically maximum length 4000 bytes)
my_clob CLOB, -- Character Large Object
--Binary type
my_raw RAW(256), --Variable length binary(Basically maximum length 2000 bytes)
my_blob BLOB, -- Binary Large Object
--Boolean type
my_number_1 NUMBER(1), --0 is interpreted as false, all other values are interpreted as true
--Integer type
my_number_38 NUMBER(38), --Up to 38 digits
my_smallint SMALLINT, --ANSI data type converted to Oracle data type
my_integer INTEGER, --ANSI data type converted to Oracle data type
--Floating point type
my_binary_float BINARY_FLOAT, --Single precision floating point number 4 bytes
my_binary_double BINARY_DOUBLE, --Double precision floating point number 8 bytes
my_float FLOAT, --Oracle data type FLOAT(126)ANSI data type converted to
my_double_precision DOUBLE PRECISION, --Oracle data type FLOAT(126)ANSI data type converted to
my_real REAL, --Oracle data type FLOAT(63)ANSI data type converted to
--Fixed point type
my_fixed_point_number NUMBER(7, 4), --Fixed point number
my_numeric NUMERIC(7, 4), --ANSI data type converted to Oracle data type
my_decimal DECIMAL(7, 4), --ANSI data type converted to Oracle data type
--Time type
my_date DATE, --date+Hours, minutes, and seconds
my_timestamp TIMESTAMP(9), --date+Hours, minutes, and seconds+Nanoseconds
my_timestamp_with_time_zone TIMESTAMP(9) WITH TIME ZONE --date+Hours, minutes, and seconds+Nanoseconds+Time zone
)""");
//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', -- VARCHAR2
'Hello', -- CLOB
--Binary type
HEXTORAW('CAFEBABE'), -- RAW
HEXTORAW('CAFEBABE'), -- BLOB,
--Boolean type
1, --0 is interpreted as false, all other values are interpreted as true
--Integer type
12345678901234567890123456789012345678, -- NUMBER(38)
32767, -- SMALLINT
2147483647, -- INTEGER
--Floating point type
123.0001, -- BINARY_FLOAT
123.0001, -- BINARY_DOUBLE
123.0001, -- FLOAT
123.0001, -- DOUBLE PRECISION
123.0001, -- REAL
--Fixed point type
123.0001, -- NUMBER(7, 4)
123.0001, -- NUMERIC(7, 4)
123.0001, -- DECIMAL(7, 4)
--Time type
TO_DATE('2001-02-03 04:05:06', 'YYYY-MM-DD HH24:MI:SS'), -- DATE
TO_TIMESTAMP('2001-02-03 04:05:06.999999999', 'YYYY-MM-DD HH24:MI:SS.FF9'), -- TIMESTAMP(9)
TO_TIMESTAMP_TZ('2001-02-03 04:05:06.999999999 +00:00', 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM') -- TIMESTAMP(9) WITH TIME ZONE
)""");
//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_varchar2=" + rs.getString("my_varchar2"));
System.out.println("my_clob=" + rs.getClob("my_clob"));
//Binary type
System.out.println("my_raw=" + Arrays.toString(rs.getBytes("my_raw")));
System.out.println("my_blob=" + rs.getBlob("my_blob"));
//Boolean type
System.out.println("my_number_1=" + rs.getBoolean("my_number_1"));
//Integer type
System.out.println("my_number_38=" + rs.getBigDecimal("my_number_38"));
System.out.println("my_smallint=" + rs.getInt("my_smallint"));
System.out.println("my_integer=" + rs.getInt("my_integer"));
//Floating point type
System.out.println("my_binary_float=" + rs.getFloat("my_binary_float"));
System.out.println("my_binary_double=" + rs.getDouble("my_binary_double"));
System.out.println("my_float=" + rs.getDouble("my_float"));
System.out.println("my_double_precision=" + rs.getDouble("my_double_precision"));
System.out.println("my_real=" + rs.getDouble("my_real"));
//Fixed point type
System.out.println("my_fixed_point_number=" + rs.getBigDecimal("my_fixed_point_number"));
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.getTimestamp("my_date"));
System.out.println("my_timestamp=" + rs.getTimestamp("my_timestamp").toInstant());
System.out.println("my_timestamp_with_time_zone=" + rs.getTimestamp("my_timestamp_with_time_zone").toInstant());
}
stmt.close();
con.close();
}
//Get JDBC type name
private static String getJdbcTypeName(int type) throws IllegalAccessException {
//Search by Java standard JDBC type
Field[] fs = Types.class.getDeclaredFields();
for (Field f : fs) {
if (type == f.getInt(null)) {
return f.getName();
}
}
//Search by JDBC type of Oracle extension
fs = OracleTypes.class.getDeclaredFields();
for (Field f : fs) {
if (type == f.getInt(null)) {
return "OracleTypes." + f.getName();
}
}
//Since there was no matching JDBC type, the type value is converted to a string and returned.
return "" + type;
}
}
Run with Gradle's run task.
$ gradle run
Starting a Gradle Daemon (subsequent builds will be faster)
> 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_VARCHAR2 - VARCHAR - VARCHAR2 - java.lang.String
MY_CLOB - CLOB - CLOB - oracle.jdbc.OracleClob
MY_RAW - VARBINARY - RAW - [B
MY_BLOB - BLOB - BLOB - oracle.jdbc.OracleBlob
MY_NUMBER_1 - NUMERIC - NUMBER - java.math.BigDecimal
MY_NUMBER_38 - NUMERIC - NUMBER - java.math.BigDecimal
MY_SMALLINT - NUMERIC - NUMBER - java.math.BigDecimal
MY_INTEGER - NUMERIC - NUMBER - java.math.BigDecimal
MY_BINARY_FLOAT - OracleTypes.BINARY_FLOAT - BINARY_FLOAT - java.lang.Float
MY_BINARY_DOUBLE - OracleTypes.BINARY_DOUBLE - BINARY_DOUBLE - java.lang.Double
MY_FLOAT - NUMERIC - NUMBER - java.lang.Double
MY_DOUBLE_PRECISION - NUMERIC - NUMBER - java.lang.Double
MY_REAL - NUMERIC - NUMBER - java.lang.Double
MY_FIXED_POINT_NUMBER - NUMERIC - NUMBER - java.math.BigDecimal
MY_NUMERIC - NUMERIC - NUMBER - java.math.BigDecimal
MY_DECIMAL - NUMERIC - NUMBER - java.math.BigDecimal
MY_DATE - TIMESTAMP - DATE - java.sql.Timestamp
MY_TIMESTAMP - TIMESTAMP - TIMESTAMP - oracle.sql.TIMESTAMP
MY_TIMESTAMP_WITH_TIME_ZONE - OracleTypes.TIMESTAMPTZ - TIMESTAMP WITH TIME ZONE - oracle.sql.TIMESTAMPTZ
Column name-Column value
my_char=Hello
my_varchar2=Hello
my_clob=oracle.sql.CLOB@7c711375
my_raw=[-54, -2, -70, -66]
my_blob=oracle.sql.BLOB@3a44431a
my_number_1=true
my_number_38=12345678901234567890123456789012345678
my_smallint=32767
my_integer=2147483647
my_binary_float=123.0001
my_binary_double=123.0001
my_float=123.0001
my_double_precision=123.0001
my_real=123.0001
my_fixed_point_number=123.0001
my_numeric=123.0001
my_decimal=123.0001
my_date=2001-02-03 04:05:06.0
my_timestamp=2001-02-02T19:05:06.999999999Z
my_timestamp_with_time_zone=2001-02-03T04:05:06.999999999Z
BUILD SUCCESSFUL in 16s
2 actionable tasks: 2 executed
[Oracle Database JDBC Developer's Guide, 12c Release 2 \ (12 \ .2 ) -Accessing and Manipulating Oracle Data](https://docs.oracle.com/cd/E82638_01/jjdbc/accessing-and- manipulating-Oracle-data.html#GUID-EE0C380B-AB3E-4D1C-B02F-E3E599C72F91)
Because the BOOLEAN database type does not exist, a datatype conversion is always performed when using getBoolean. The getBoolean method is only supported for columns for numbers. When getBoolean is applied to such a column, the 0 (zero) value is interpreted as false and the other values are interpreted as true. When applied to another type of column, getBoolean returns the exception java.lang.NumberFormatException.
-Introduction to JDBC API -Mapping between SQL and Java types
Recommended Posts