Current date inside single quotes in java

Question

static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd- MMM-yyyy");
static String date = simpleDateFormat.format(new Date());

private static final String SELECT_BY_AUDIT_KEY_QUERY = "SELECT * FROM 
     t02 WHERE t02_create_date = " + date;

This outputs as :

SELECT * FROM t02_transaction_log WHERE t02_create_date = 03-Jul-2018

What i want is this :

SELECT * FROM t02_transaction_log WHERE t02_create_date = '03-Jul-2018'

How can i achieve this get the date in between single quotes?

Answer 1

You should be using a prepared statement here:

String sql = "SELECT * FROM t02 WHERE t02_create_date = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDate(1, new Date());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    // process result set
}

While statements are important for preventing things like SQL injection, they are equally useful because they free you from having to worry about formatting details in your query. In the query string above, we just represent the date by a ? placeholder, and let the driver handle marshaling the current date into the correct format to be sent to Oracle.

Answer 2

tl;dr

get the date in between single quotes?

Don’t. You should be using smart objects, not dumb strings, to exchange date values with your database.

Use java.time classes that replaced troublesome legacy classes.

For date-only values, use LocalDate class that replaced java.sql.Date.

myPreparedStatement.setObject( 
    … , 
    LocalDate.of( 2018 , Month.JANUARY , 23 ) 
)

…and retrieval…

myResultSet.getObject( 
    … ,
    LocalDate.class 
)

java.time

The Answer by Tim Biegeleisen is correct, but uses troublesome old classes. The java.util.Date, java.sql.Date, SimpleDateFormat, and related old date-time classes were supplanted years ago by the java.time classes.

As of JDBC 4.2 and later, we can directly exchange java.time objects with the database. Use PreparedStatement::setObject and ResultSet::getObject methods.

Date-only

If your database column is of a date-only type without time-of-day and without time zone, akin to the SQL-standard DATE type, use LocalDate class. Example code:

LocalDate localDate = LocalDate.of( 2018 , Month.JANUARY , 23 ) ;  // 2018-01-23.
String sql = "SELECT * FROM t02 WHERE t02_create_date = ? ;" ;  // Tip: Make a habit of closing your SQL properly with a semicolon. Won't hurt, and might help.
PreparedStatement ps = conn.prepareStatement( sql ) ;
ps.setObject( 1 , localDate );
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    LocalDate ld = rs.getObject( 1 , LocalDate.class ) ;  // Retrieve an `LocalDate` class.
}

Date-time

If your database column is of a date-with-time-of-day type, akin to the SQL-standard TIMESTAMP WITH TIME ZONE type, use Instant class. Example code:

LocalDate instant = LocalDate.of( 
String sql = "SELECT * FROM t02 WHERE t02_create_date = ? ;" ;  // Tip: Make a habit of closing your SQL properly with a semicolon. Won't hurt, and might help.
PreparedStatement ps = conn.prepareStatement( sql ) ;
ps.setObject( 1 , instant );
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    Instant instantRetrieved = rs.getObject( 1 , Instant.class ) ;  // Retrieve an `Instant` class.
    // You may want to adjust from UTC to some other time zone.
    ZoneID z = ZoneId.of( "Africa/Tunis" ) ;
    ZonedDateTime zdt = instantRetrieved.atZone( z ) ;  // Same moment, same point on the timeline, different wall-clock time as seen by the people of some particular region (a time zone). 
}

---

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.

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

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

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.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 3

Try this

 private static final String SELECT_BY_AUDIT_KEY_QUERY = "SELECT * FROM 
     t02 WHERE t02_create_date = to_date('" + date + "', 'dd-mon-yyyy')";