How to Correctly Use setDate() in PreparedStatements to Avoid `java.lang.IllegalArgumentException`?

Using setDate in PreparedStatement

In order to standardize our codebase, we have been tasked with transitioning all instances of hardcoded SQL variables to prepared statements with bound variables. However, we have encountered an issue with the setDate() method.

The code snippet provided is as follows:

DateFormat dateFormatYMD = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
DateFormat dateFormatMDY = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
Date now = new Date();
String vDateYMD = dateFormatYMD.format(now);
String vDateMDY = dateFormatMDY.format(now);
String vDateMDYSQL = vDateMDY;
java.sql.Date date = new java.sql.Date(0000-00-00);

requestSQL = "INSERT INTO CREDIT_REQ_TITLE_ORDER (REQUEST_ID," + " ORDER_DT, FOLLOWUP_DT) " + "values(?,?,?,)";

prs = conn.prepareStatement(requestSQL);
prs.setInt(1, new Integer(requestID));
prs.setDate(2, date.valueOf(vDateMDYSQL));
prs.setDate(3, date.valueOf(sqlFollowupDT));

Upon executing the SQL query, the following error is encountered:

java.lang.IllegalArgumentException
at java.sql.Date.valueOf(Date.java:138)
at com.cmsi.eValuate.TAF.TAFModuleMain.CallTAF(TAFModuleMain.java:1211)

Solution

The setDate() method requires a valid date value. In this case, we have set it to new java.sql.Date(0000-00-00), which is an invalid date.

To resolve this issue, the following solutions can be considered:

Using java.sql.Date

If the table column has a DATE type, the following options are available:

• java.lang.String: Use java.sql.Date.valueOf(java.lang.String) to parse the date string in the format yyyy-[m]m-[d]d, e.g.:

  ps.setDate(2, java.sql.Date.valueOf("2013-09-04"));

• java.util.Date: Convert the java.util.Date object using new java.sql.Date(endDate.getTime()), e.g.:

  ps.setDate(2, new java.sql.Date(endDate.getTime()));

• Current: Use new java.sql.Date(System.currentTimeMillis()) to insert the current date, or java.sql.Date.valueOf(java.time.LocalDate.now()) in Java 8 and above.

Using java.sql.Timestamp

If the table column has a TIMESTAMP or DATETIME type, the following options are available:

• java.lang.String: Use java.sql.Timestamp.valueOf(java.lang.String) to parse the date string in the format yyyy-[m]m-[d]d hh:mm:ss[.f…], e.g.:

  ps.setTimestamp(2, java.sql.Timestamp.valueOf("2013-09-04 13:30:00"));

• java.util.Date: Convert the java.util.Date object using new java.sql.Timestamp(endDate.getTime()), e.g.:

  ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));

• Current: Use new java.sql.Timestamp(System.currentTimeMillis()), java.sql.Timestamp.from(java.time.Instant.now()), or java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()) in Java 8 and above to insert the current timestamp.

The above is the detailed content of How to Correctly Use setDate() in PreparedStatements to Avoid `java.lang.IllegalArgumentException`?. For more information, please follow other related articles on the PHP Chinese website!