CODEWITHSUNDEEP
javamysqldatejdbc
Smajl
Smajl

Reputation: 8005

JDBC delete rows in db with specific timestamp

I have a database in which I am saving messages with tiemstamps. I have table created with this code:

CREATE TABLE messages (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    msgid VARCHAR(64) UNIQUE,
    payload TEXT,
    sender VARCHAR(255),
    cur_timestamp TIMESTAMP(3)
);

This all works perfectly. However, now I am trying to delete some rows with timestamp older than some specified by user. This is done in Java part of my system like this:

// get current timestamp
Date date = new Date();
// timestamp has to be in the same format as in database
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd h:mm:ss.SSS");
String formattedDate = sdf.format(date);
System.out.println("Current time: " + formattedDate);

// subtract desired time
if (msgOld.equals("hour")) {
  date.setTime(date.getTime() - ONE_HOUR_MILLISCONDS);
  formattedDate = sdf.format(date);
} else if (msgOld.equals("day")) {
  date.setTime(date.getTime() - ONE_DAY_MILLISCONDS);
  formattedDate = sdf.format(date);
}

This, too, works fine - the constants look like this: final long ONE_HOUR_MILLISCONDS = 60 * 60 * 1000;

The only problem is that I dont know how to write the query in JDBC. I tried doing this:

// prepare query
String query;
if (msgOld.equals("all")) {
    query = "TRUNCATE TABLE messages";
} else {
    query = "DELETE FROM messages WHERE cur_timestamp < " + date + ";";
}

but it says I have an error in my SQL statement (the second one, the one with TRUNCATE works fine). I tried putting formattedDate instead of date in the statement, too.

Whats wrong with it (probably something really simple - maybe difference between java.util.Date and SQL timestamp...)? Thanks for any tips!

EDIT: Thanks for all responses... I have rewritten the code so it uses prepared statement like this:

// prepare query
        String query;
        PreparedStatement pstmt = null;

        try {
            if (msgOld.equals("all")) {
                query = "TRUNCATE TABLE messages";
                pstmt=conn.prepareStatement(query);
            } else {
            query = "DELETE FROM messages WHERE cur_timestamp < ?;";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, timestamp.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            pstmt.execute();

            System.out.println("Successfully deleted messages.");
            pstmt.close();

        } catch (Exception e) {
            System.out.println("Error with db query.");
            e.printStackTrace();
        }

but I am still getting SQL exception (even if I convert date to timestamp and like this Timestamp timestamp = new Timestamp(date.getTime()); and use it in the satement)

Upvotes: 0

Views: 2141

Answers (2)

Mukesh Kumar Singh
Mukesh Kumar Singh

Reputation: 653

exactly as @Funtik said preparedStatement could be used. See this link here

it would not exactly sove your problem but you will get a clear idea.

Upvotes: 0

WeMakeSoftware
WeMakeSoftware

Reputation: 9162

You cannot just concatenate java.util.Date objects into query string. That's wrong.

Better use PreparedStatement for this kind of things.

Take a look at this tutorial for example

Upvotes: 3

Related Questions