Inserting and Selecting Date (Java) (MySQL)

Question

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.

What do you mean by custom dates? Can you give a concrete example of what you want (are the start_date and end_date timestamps in your DB)? — Sujay
– Sujay, Commented Nov 13, 2012 at 17:53
Your examples are using the current date/time. If that's all you want, let MySQL do it for you, e.g. NOW(). If not, tell us what kind of date range you're after. — Alain Collins
– Alain Collins, Commented Nov 13, 2012 at 17:54
Also note that YEAR(myfield) is a function on the field, and mysql won't be able to use an index to get the data quickly. — Alain Collins
– Alain Collins, Commented Nov 13, 2012 at 17:55
It must select already inserted dates, then populate a table to be generated into PDF (I'm using iText). I wish to insert a past date so all my tables would be according to the same example. — Rasshu
– Rasshu, Commented Nov 13, 2012 at 17:57
About the 2nd idea, I was thinking about that: SELECT * FROM table WHERE (YEAR(st_year)=?) AND (MONTH(st_month)=?) AND (DAY(st_day)=?);(...)etc(...) String slash, data, st_year, st_month, st_day; StringTokenizer st = new StringTokenizer(slash, "/"); data = st.nextToken(); day = data; (...)etc(...) — Rasshu
– Rasshu, Commented Nov 13, 2012 at 18:04

Roman C · Accepted Answer · 2012-11-14 22:39:16Z

4

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();
    }
  }
}

edited Nov 14, 2012 at 22:39

answered Nov 13, 2012 at 17:56

Roman C

1

Sign up to request clarification or add additional context in comments.

1 Comment

Rasshu Over a year ago

OK. Thanks. Now, how the value of a Date field (that is, I must input the date, not use the current one) could feed a PreparedStatement? Like in "pStmt.setString(1,myString)".

Collectives™ on Stack Overflow

Inserting and Selecting Date (Java) (MySQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related