How To Insert Oracle Date Data type in Java

Question

Hello everybody,

I am newbie in java. I tried to create such a data pipe line that copy tables from 1 source to different servers & databases. It work fine with String and Number, but I stuck in Date datatype. I got Exception in thread "main" java.sql.SQLException: ORA-01843: not a valid month.

I have no idea how to change the date format in java or work with date data type.

Here the detail : Oracle 10g (Svr1) & 11g (Svr2), java 8 with ojdbc14.jar

The table's DB

Table = LAB
field = ID(INT), LAB_DESC(VARCHAR2, 50), LAB_DATE(DATE)

The Java Code

//========= Bismillah Arrahman Arrahiem =============//
//============== DARI SINI ========================================//
//    **    **  **  **
//  ****    **  **  **     ****        ** ** **     ** 
//  ****    **  **  **     *  *        ** ** **     **
//     **********   **   ****************************
//                                             *
//
//========================= LAB =============================//
package simpledtpipeline;
import java.sql.*;
public class SimpleDTpipeline {

     public static void main(String[] args) 
       //========= Bismillah =============//
        throws SQLException, ClassNotFoundException, Exception {
 // Load the JDBC driver
 Class.forName("oracle.jdbc.driver.OracleDriver");
 System.out.println("Driver loaded");
//======================================================// 
 // Connect to a database
Connection connectionServer1 = DriverManager.getConnection
("jdbc:oracle:thin:@192.168.7.1:1521:dbone", "user", "pass123");
 System.out.println("Database 192.168.7.1 connected");
//======================================================//
Connection connectionServer2 = DriverManager.getConnection
("jdbc:oracle:thin:@192.168.7.2:1521:dbtwo", "user2", "pass789");
System.out.println("Database 192.168.7.2 connected");
//
//========================= LAB =============================//
Statement SelectLAB = connectionServer1.createStatement();
// Execute a statement
 ResultSet FetchLAB = SelectLAB.executeQuery
("SELECT ID, LAB_DESC, LAB_DATE from LAB ");
//*****************************************************************//
PreparedStatement InsertLAB = connectionServer2.prepareStatement("INSERT INTO LAB "
        + "(ID, LAB_DESC, LAB_DATE) VALUES (?,?, TO_DATE(?,'MM/DD/YYYY')");
while (FetchLAB.next()){
    InsertLAB.setInt(1,FetchLAB.getInt("ID")); 
    InsertLAB.setString(2,FetchLAB.getString("LAB_DESC")); 
    InsertLAB.setString(3,FetchLAB.getString("LAB_DATE")); 
    InsertLAB.execute(); 
}
//======================================================//
//
// Close the connection //
 connectionServer1.close();
 connectionServer2.close();
}
       
    }

Answer 1

tl;dr

myPreparedStatement
.setObject(                                           // Use `setObject` for java.time types.
    3 ,                                               // Third placeholder in the Question's example text.
    LocalDate                                         // Represent a date-only value, without time-of-day and without time zone.
    .parse(
        "01/23/2020" , 
        DateTimeFormatter.ofPattern( "MM/dd/uuuu" )   // Specify formatting codes for your particular text input’s format. Case-sensitive.
    )
)

Formatting codes

I am guessing that the specific problem in your code is incorrect formatting codes: 'MM/DD/YYYY'. I do not know Oracle Database codes, but I would guess they are case-sensitive, as seen in other such tooling. If so, you may be using the wrong case. Or your formatting codes do not match the actual text of your inputs.

Either way, the issue is moot. You should be passing Java objects for your date-time values instead of plain text.

Use date-time objects in Java

You date-time objects on the Java side, rather than mere strings.

Use only the java.time classes in Java, never the legacy classes such as Date, Calendar, and SimpleDateFormat.

JDBC 4.2 requires a JDBC driver to support a subset of the java.time types.

But unfortunately and mysteriously, the spec does not provide type-specific set… methods for the java.time types. Instead, we use setObject on the prepared statement, and getObject on the result set.

LocalDate localDate = LocalDate.of( 2022 , Month.JANUARY , 23 ) ;
myPreparedStatement.setObject( … , localDate ) ;

And retrieval.

LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;

If your inputs are text in the format of month/day/year numbers, parse into LocalDate objects. Trap for DateTimeParseException in case of faulty inputs.

String input = "01/23/2022" ;
DateTimeFormatter f = DateTimeFormatter.ofPattern( "MM/dd/uuuu" ) ;
LocalDate localDate = LocalDate.parse( input , format ) ;

Using localized formats for such text in data-exchange is a poor practice. I suggest you educate the publisher of that textual data about the ISO 8601 standard format for dates: YYYY-MM-DD. So January 23 of 2022 would be 2022-01-23.

Unrelated tips

Calling Class.forName to load a JDBC driver is old-school. No longer necessary for modern Java.

Generally advised to use a DataSource implementation for your particular database rather to hold info to make database connections. Your JDBC driver should provide such an implementation.

The JDBC resources such as Connection and ResultSet implement the AutoCloseable interface. This means we can use try-with-resources syntax to automatically close these resources while simplifying our code.

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
  • Java 9 brought some minor features and fixes.
• Java SE 6 and Java SE 7
  • Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android (26+) bundle implementations of the java.time classes.
  • For earlier Android (<26), the process of API desugaring brings a subset of the java.time functionality not originally built into Android.
    • If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….