JDBC - Data Types

---

---

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

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

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:\Users\Saikat\clob_output_mysql_create_table.txt
Blob data is stored at: C:\Users\Saikat\output_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:\Users\Saikat\clob_output_mysql_create_table.txt
Blob data is stored at: C:\Users\Saikat\output_saikat_upwork1.jpg

C:\>