Inserting and Selecting Date (Java) (MySQL)

date, java, jdbc, mysql, sql

Solution

The first approach is better, because you were able to use date functions to manipulate values.

private void executeQuery(java.util.Date startDate, java.util.Date endDate) throws SQLException {
  Connection conn = null;
  PreparedStatement pstmt = null;
  try {
    conn = getConnection();
    String query = "select ... between ? and ?";
    pstmt = conn.prepareStatement(query);
    pstmt.setDate(1, new java.sql.Date(startDate.getTime()));
    pstmt.setDate(2, new java.sql.Date(endDate.getTime()));
    //return results
    ResultSet rs = pstmt.executeQuery();
    rs.last();
    System.out.println("last row = " + rs.getRow());
  } finally {
    if (pstmt != null) {
      pstmt.close();
    }
    if (conn != null) {
      conn.close();
    }
  }
}

Problem

I have a table where I have to do a `SELECT ... BETWEEN start_date AND end_date`, as well as insert `Date` values into the same table. I read things like: ``` java.util.Date now = new java.util.Date(); pStmt.setDate( 1, new java.sql.Date( now.getTime() ) ); pStmt.setTimestamp( 2, new java.sql.Timestamp( now.getTime() ) ); pStmt.executeUpdate(); ``` But that kind of code sets or gets the current date, doesn't it? I want to insert and select custom dates myself. I also read it here at StackOverflow: ``` SELECT * FROM myTable WHERE ( YEAR(myfield) = '2009') AND ( MONTH(myfield) = '1') ``` Is it also possible to make the `Date` fields `String`, then use `StringTokenizer` to extract only the day, month or year information and use them in a `SELECT` similar to this code? (In case my first - and simpler - idea is impossible.) So until now, there's two possible solutions. I also need to know which one is faster since the database accessed is in a fail-safe / critical system (a bank) with lots of data.

Original source