”;
The JDBC driver converts the Java data type to the appropriate JDBC type, before sending it to the database. It uses a default mapping for most data types. For example, a Java int is converted to an SQL INTEGER. Default mappings were created to provide consistency between drivers.
The following table summarizes the default JDBC data type that the Java data type is converted to, when you call the setXXX() method of the PreparedStatement or CallableStatement object or the ResultSet.updateXXX() method.
SQL vs Java Data Types
SQL | JDBC/Java | setXXX | updateXXX |
---|---|---|---|
VARCHAR | java.lang.String | setString | updateString |
CHAR | java.lang.String | setString | updateString |
LONGVARCHAR | java.lang.String | setString | updateString |
BIT | boolean | setBoolean | updateBoolean |
NUMERIC | java.math.BigDecimal | setBigDecimal | updateBigDecimal |
TINYINT | byte | setByte | updateByte |
SMALLINT | short | setShort | updateShort |
INTEGER | int | setInt | updateInt |
BIGINT | long | setLong | updateLong |
REAL | float | setFloat | updateFloat |
FLOAT | float | setFloat | updateFloat |
DOUBLE | double | setDouble | updateDouble |
VARBINARY | byte[ ] | setBytes | updateBytes |
BINARY | byte[ ] | setBytes | updateBytes |
DATE | java.sql.Date | setDate | updateDate |
TIME | java.sql.Time | setTime | updateTime |
TIMESTAMP | java.sql.Timestamp | setTimestamp | updateTimestamp |
CLOB | java.sql.Clob | setClob | updateClob |
BLOB | java.sql.Blob | setBlob | updateBlob |
ARRAY | java.sql.Array | setARRAY | updateARRAY |
REF | java.sql.Ref | SetRef | updateRef |
STRUCT | java.sql.Struct | SetStruct | updateStruct |
XML | SQLXML | setSQLXML | updateSQLXML |
ROWID | RowId | setRowId | updateRowId |
DISTINCT | BigDecimal | setBigDecimal | updateBigDecimal |
DATALINK | Java.netURL | setURL | updateURL |
NCLOB | NClob | setNClob | updateNClob |
JDBC 3.0 has enhanced support for BLOB, CLOB, ARRAY, and REF data types. The ResultSet object now has updateBLOB(), updateCLOB(), updateArray(), and updateRef() methods that enable you to directly manipulate the respective data on the server.
The setXXX() and updateXXX() methods enable you to convert specific Java types to specific JDBC data types. The methods, setObject() and updateObject(), enable you to map almost any Java type to a JDBC data type.
ResultSet object provides corresponding getXXX() method for each data type to retrieve column value. Each method can be used with column name or by its ordinal position.
ResultSet Methods to Get/Set SQL Values
SQL | JDBC/Java | setXXX | getXXX |
---|---|---|---|
VARCHAR | java.lang.String | setString | getString |
CHAR | java.lang.String | setString | getString |
LONGVARCHAR | java.lang.String | setString | getString |
BIT | boolean | setBoolean | getBoolean |
NUMERIC | java.math.BigDecimal | setBigDecimal | getBigDecimal |
TINYINT | byte | setByte | getByte |
SMALLINT | short | setShort | getShort |
INTEGER | int | setInt | getInt |
BIGINT | long | setLong | getLong |
REAL | float | setFloat | getFloat |
FLOAT | float | setFloat | getFloat |
DOUBLE | double | setDouble | getDouble |
VARBINARY | byte[ ] | setBytes | getBytes |
BINARY | byte[ ] | setBytes | getBytes |
DATE | java.sql.Date | setDate | getDate |
TIME | java.sql.Time | setTime | getTime |
TIMESTAMP | java.sql.Timestamp | setTimestamp | getTimestamp |
CLOB | java.sql.Clob | setClob | getClob |
BLOB | java.sql.Blob | setBlob | getBlob |
ARRAY | java.sql.Array | setARRAY | getARRAY |
REF | java.sql.Ref | SetRef | getRef |
STRUCT | java.sql.Struct | SetStruct | getStruct |
XML | SQLXML | setSQLXML | getSQLXML |
ROWID | RowId | setRowId | getRowId |
DISTINCT | BigDecimal | setBigDecimal | getBigDecimal |
DATALINK | Java.netURL | setURL | getURL |
NCLOB | NClob | setNClob | getNClob |
Date & Time Data Types
The java.sql.Date class maps to the SQL DATE type, and the java.sql.Time and java.sql.Timestamp classes map to the SQL TIME and SQL TIMESTAMP data types, respectively.
Getting Current Time from Java Util Date and Java SQL Date Objects Example
Following example shows how the Date and Time classes format the standard Java date and time values to match the SQL data type requirements.
package com.tutorialspoint; public class SqlDateTime { public static void main(String[] args) { //Get standard date and time java.util.Date javaDate = new java.util.Date(); long javaTime = javaDate.getTime(); System.out.println("The Java Date is:" + javaDate.toString()); //Get and display SQL DATE java.sql.Date sqlDate = new java.sql.Date(javaTime); System.out.println("The SQL DATE is: " + sqlDate.toString()); //Get and display SQL TIME java.sql.Time sqlTime = new java.sql.Time(javaTime); System.out.println("The SQL TIME is: " + sqlTime.toString()); //Get and display SQL TIMESTAMP java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(javaTime); System.out.println("The SQL TIMESTAMP is: " + sqlTimestamp.toString()); }//end main }//end SqlDateTime
Output
Now let us compile the above example as follows −
C:>javac SqlDateTime.java C:>
When you run SqlDateTime, it produces the following result −
C:>java SqlDateTime The Java Date is:Fri Jun 07 09:38:32 IST 2024 The SQL DATE is: 2024-06-07 The SQL TIME is: 09:38:32 The SQL TIMESTAMP is: 2024-06-07 09:38:32.243 C:>
Handling NULL Values
SQL”s use of NULL values and Java”s use of null are different concepts. So, to handle SQL NULL values in Java, there are three tactics you can use −
-
Avoid using getXXX( ) methods that return primitive data types.
-
Use wrapper classes for primitive data types, and use the ResultSet object”s wasNull( ) method to test whether the wrapper class variable that received the value
returned by the getXXX( ) method should be set to null. -
Use primitive data types and the ResultSet object”s wasNull( ) method to test whether the primitive variable that received the value returned by the getXXX( )
method should be set to an acceptable value that you”ve chosen to represent a NULL.
Here is one example to handle a NULL value −
Statement stmt = conn.createStatement( ); String sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); int id = rs.getInt(1); if( rs.wasNull( ) ) { id = 0; }
int, float and varchar Data Types
The java.lang.String class maps to the SQL VARCHAR and other text based types, and the java primitive can be used for SQL primitive types like int, float etc.
Following example shows how to set int, float, varchar SQL data types and get data accordingly.
Let”s first create a new table Officers as follows −
mysql> use TUTORIALSPOINT; mysql> create table Officers -> ( -> off_id int primary key auto_increment, -> salary float not null, -> name varchar (255), -> dept varchar (255) -> ); Query OK, 0 rows affected (0.08 sec) mysql>
Create Data Records
Finally you create few records in Officers table as follows −
mysql> INSERT INTO OFFICERS( NAME, SALARY, DEPT ) VALUES( ''Vijendra Singh'', 30000.00, ''Accounts''); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO OFFICERS( NAME, SALARY, DEPT ) VALUES( ''Jeet Aagarwal'', 40000.00, ''Management -1''); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO OFFICERS( NAME, SALARY, DEPT ) VALUES( ''Gary Hayes'', 20000.00, ''Software Engineer - 1''); Query OK, 1 row affected (0.00 sec) mysql>
Getting Numeric and Text Based Values from Database Example
In this example, we”ve five static strings containing a dababase connection url, username, password, SELECT Query and an UPDATE query. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve created a PreparedStatement object using connection.prepareStatement() method. Now using statement.executeUpdate(), we”ve run the update query. Lastly using statement.executeQuery(), the SELECT Query is executed and result is stored in a resultset. Now resultset is iterated and each record is printed to check the updated content.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.PreparedStatement; public class JDBCDataTypesExample { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT off_id, name, salary, dept FROM Officers"; static final String UPDATE_QUERY ="UPDATE Officers set salary=? WHERE off_id=?"; public static void main(String args[]) { // prepare the database connection and PreparedStatement using connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); PreparedStatement pstmt = conn.prepareStatement(UPDATE_QUERY); ){ // set a float value pstmt.setFloat( 1, (float)15000.00); // set an int value pstmt.setInt(2,2 ); // execute the update query pstmt.executeUpdate(); System.out.println("Update successfully executed....."); ResultSet rs = pstmt.executeQuery(QUERY); System.out.println("-----------------------------------"); System.out.println("Showing results from updated table....."); while(rs.next()){ // get the int value in int variable int off_id = rs.getInt("off_id"); System.out.print(off_id + ","); // get the varchar value in String variable String name =rs.getString("name"); System.out.print(name + ","); // get the float value in float variable float sal = rs.getFloat("salary"); System.out.print(sal + ","); String dept = rs.getString("dept"); System.out.print(dept +"n"); } System.out.print("------------------------------"); } catch( SQLException e){ e.printStackTrace(); } } }
Output
Now let us compile the above example as follows −
C:>javac JDBCDataTypesExample.java C:>
When you run JDBCDataTypesExample, it produces the following result −
C:>java JDBCDataTypesExample Update successfully executed..... ----------------------------------- Showing results from updated table..... 2,Vijendra Singh,15000.0,Accounts 3,Jeet Agarwal,40000.0,Management 1 4,Gary Hayes,20000.0, Software Engineer - 1 ------------------------------ C:>
CLOB and BLOB Data Types
The java.sql.CLOB class maps to the SQL CLOB type, and the java.sql.BLOB class maps to the SQL BLOB data types, respectively.
Following example shows how the CLOB and BLOB classes format the standard Java text and binary values to match the SQL data type requirements.
Let”s first create a new table JDBC_BLOB_CLOB as follows −
mysql> use TUTORIALSPOINT; mysql> create table JDBC_BLOB_CLOB -> ( -> plain_text longtext, -> name varchar (255), -> image longblob -> ); Query OK, 0 rows affected (0.08 sec) mysql>
Getting Image as Binary data and Text as CLOB from Database Example
In this example, we”ve a string containing a dababase connection url. Now using DriverManager.getConnection() method, we”ve prepared a database connection with given username and password. Once connection is prepared, we”ve created a PreparedStatement object using connection.prepareStatement() method. We”re reading a text file content using a FileReader object and image file content using InputStream.
Using PreparedStatement.setClob() method, we set the fileReader for CLOB field and using PreparedStatement.setBlob() method, we set the InputStream for BLOB field. Now using statement.execute(), we”ve run the insert query. Lastly using statement.executeQuery(), a SELECT Query is executed and result is stored in a resultset. Now resultset is iterated and each record is printed to check the updated content.
ResultSet.getClob() method is used to read the value in CLOB variable and ResultSet.getBlob() method is used to read the value in BLOB variable. Using CLOB.getCharacterStream(), a reader is retrieved which is then used to write content of CLOB to a txt format file. Using BLOB.getBinaryStream() method, a stream is retrieved which is then used to write content of BLOB to a jpg format image file.
import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.FileReader; import java.io.FileWriter; import java.io.InputStream; import java.io.Reader; import java.sql.Blob; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class JDBCDataTypesExample { public static void main(String args[]) throws Exception { //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/TUTORIALSPOINT"; Connection con = DriverManager.getConnection(mysqlUrl, "guest", "guest123"); System.out.println("Connection established......"); //Inserting values String query = "INSERT INTO JDBC_BLOB_CLOB(Name, PLAIN_TEXT, IMAGE) VALUES (?, ?, ?)"; PreparedStatement pstmt = con.prepareStatement(query); pstmt.setString(1, "ClOB_BLOB Example"); FileReader fileReader = new FileReader("C:\Users\Saikat\OneDrive\Documents\mysql_create_table.txt"); pstmt.setClob(2, fileReader); InputStream inputStream = new FileInputStream("C:\Users\Saikat\OneDrive\Documents\saikat_upwork1.jpg"); pstmt.setBlob(3, inputStream); pstmt.execute(); System.out.println("Record inserted......"); //Retrieving the results Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * from jdbc_blob_clob"); while(rs.next()) { String name = rs.getString("Name"); Clob clob = rs.getClob("plain_text"); Blob blob = rs.getBlob("image"); System.out.println("Name: "+name); System.out.println("Clob value: "+clob); System.out.println("Blob value: "+blob); System.out.println(""); System.out.print("Clob data is stored at: "); //Storing clob to a file int i = 0; Reader r = clob.getCharacterStream(); String filePath = "C:\Users\Saikat\clob_output_mysql_create_table.txt"; FileWriter writer = new FileWriter(filePath); while ((i=r.read())!=-1) { writer.write(i); } writer.close(); System.out.println(filePath); System.out.print("Blob data is stored at: "); InputStream is = blob.getBinaryStream(); byte byteArray[] = new byte[is.available()]; is.read(byteArray); filePath = "C:\Users\Saikat\output_saikat_upwork1.jpg"; FileOutputStream outPutStream = new FileOutputStream(filePath); outPutStream.write(byteArray); System.out.println(filePath); } } }
Output
Now let us compile the above example as follows −
C:>javac JDBCDataTypesExample.java C:>
When you run JDBCDataTypesExample, it produces the following result −
C:>java JDBCDataTypesExample Connection established...... Record inserted..... 1. Name: ClOB_BLOB Example Clob value: com.mysql.cj.jdbc.Clob@db57326 Blob value: com.mysql.cj.jdbc.Blob@34a875b3 Clob data is stored at: C:UsersSaikatclob_output_mysql_create_table.txt Blob data is stored at: C:UsersSaikatoutput_saikat_upwork1.jpg Name: ClOB_BLOB Example Clob value: com.mysql.cj.jdbc.Clob@1a6c1270 Blob value: com.mysql.cj.jdbc.Blob@18a136ac Clob data is stored at: C:UsersSaikatclob_output_mysql_create_table.txt Blob data is stored at: C:UsersSaikatoutput_saikat_upwork1.jpg C:>
”;