Query MySQL DB using preparedStatement.setDate

Question

public java.util.List<Tag> getAlltagsByDate(String date ){

    DataSource dataSource = new DataSource();
    Connection conn = dataSource.createConnection();
    ResultSet resultSet = null;
    PreparedStatement stmt = null;
    Tag tags_Data = new Tag();
    String query = "select * from tag_data where tag_data_date  =  ?";
    try {
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        Date nn  =df.parse(date);
        stmt = conn.prepareStatement(query);
        stmt.setDate(1, java.sql.Date.valueOf(date));
        resultSet = stmt.executeQuery(query);

I am getting an error

Can anyone help me with this, I need to query mySQL db where date = input in html

It seems you have a MySQL syntax error exception. Check if the error message doesn't print out the SQL query that was actually executed. — xaviert
– xaviert, Commented Dec 11, 2015 at 7:08
Hi xaviert, direct query if working fine in mysql: select * from tag_data where tag_data_date = '2015-12-11'; — mohit sharma
– mohit sharma, Commented Dec 11, 2015 at 7:10
Can you suggest me a way to search row in db with date(tag_data_date) — mohit sharma
– mohit sharma, Commented Dec 11, 2015 at 7:12
AFAIK the SQL query looks OK. Did you validate the output of all the date parsing? (java.sql.Date.valueOf(date).toString()) — xaviert
– xaviert, Commented Dec 11, 2015 at 7:15

Rick James · Accepted Answer · 2015-12-16 18:58:16Z

4

+50

No, skip the Date part; simply use the string. Let's see the value of (String date ).

MySQL is happy if you can end up with ... tag_data_date = '2015-12-11'.

If String date looks like '2015-12-11', then the conversion to Date is unnecessary.

answered Dec 16, 2015 at 18:58

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

1 Comment

mohit sharma Over a year ago

Hi Rick , very simple solution as you suggested,

Naruto · Accepted Answer · 2015-12-19 11:04:32Z

I have presented a solution. As you have not mentioned much about your DB structure, so ,

Consider test as database name, and consisting of table tag_data having two columns id and tag_data_date as shown below.

 CREATE TABLE `tag_data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tag_data_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Also consider data in table as

INSERT INTO `tag_data` (`id`, `tag_data_date`) VALUES
(1, '2015-12-20 00:00:00');

And your java class as below

public class JDBCPreparedStatementExample {

private static final String DB_DRIVER = "com.mysql.jdbc.Driver"; //mysql driver class
private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/test"; //connectionstring
private static final String DB_USER = "root"; //mysql username
private static final String DB_PASSWORD = "root"; //mysql password

public static void main(String[] argv) throws ParseException {

    try {

        getDateForDate("2015-12-20"); //time passed as arguement

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    }

}
//Method to interact with DB and print data,this can be changed to return value of List<Key> as per your requirement
private static void getDateForDate(String date) throws SQLException, ParseException {

    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
    java.util.Date dateCal  =df.parse(date); // parse date in String to Date Object
    String updateTableSQL = "select * from tag_data where tag_data_date  =  ?";

    try {
//get DB connection
        dbConnection = getDBConnection();
// Create preapared statement           
preparedStatement = dbConnection.prepareStatement(updateTableSQL);

        preparedStatement.setDate(1, new Date(dateCal.getTime()));//convert java.util.Date to java.sql.Date

        // execute update SQL stetement
        ResultSet resultSet = preparedStatement.executeQuery();
         while (resultSet.next()) {
              // It is possible to get the columns via name
              // also possible to get the columns via the column number
              // which starts at 1
              // e.g. resultSet.getString(2);
              int id = resultSet.getInt("id");
              Date tag_data_date = resultSet.getDate("tag_data_date");
              System.out.println("Date: " + tag_data_date);
              System.out.println("Comment: " + id);
            }

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    } finally {

        if (preparedStatement != null) {
            preparedStatement.close();
        }

        if (dbConnection != null) {
            dbConnection.close();
        }

    }

}

private static Connection getDBConnection() {

    Connection dbConnection = null;

    try {

        Class.forName(DB_DRIVER);

    } catch (ClassNotFoundException e) {

        System.out.println(e.getMessage());

    }

    try {

        dbConnection = DriverManager.getConnection(
                        DB_CONNECTION, DB_USER,DB_PASSWORD);
        return dbConnection;

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    }

    return dbConnection;

}

}

,Thank you for solution, i am simple using String for date,and It's working fine now
IN DB you are storing date as string or as DATETIME datatype??
In database i am storing in DATETIME format, and i am generating current time from Java.Util Now i am using Simple strings to search in db. and it's working

Collectives™ on Stack Overflow

Query MySQL DB using preparedStatement.setDate

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related