CODEWITHSUNDEEP
javasqlhsqldb
user3002166
user3002166

Reputation: 730

difference between prepareStatement() and preparedStatement() behaviour

I have noticed there is plenty of posts regarding CreateStatement vs PreparedStatement, however my question is different (just to notify this is not a duplicate of one of those search results)

When I query the database from java code, I can do it in (at least) two ways:

PreparedStatement object:

String quer =  "insert into table1 (name, phone, bday, lastasked);
PreparedStatenent pst = con.prepareStatement(quer);
pst.setString(1, "Stacy");
pst.setString(2, "555-0123456");
pst.setString(3, bdayFromInput);
pst.setString(4, "now()"); //should invoke sql now() function
pst.execute()

Build entire query:

String query = "insert into table1 (name, phone, bday, lastasked) VALUES ('Stacy', '555-123456', '" + bdayFromInput + "',  now());";
try{
    con.prepareStatement(query).execute();
}catch (SQLException e){
    System.err.println("sql exception occured");
}

The problem is related to the "now()" function from the sql - my guess is there is some kind of wrapper that transforms the setString to an actual string (as per 'now()') - making the instruction invalid (especially that the string bday - which is defined as a date in the table column works fine with hand written dates)

What I want to accomplish is to store the last time the row was altered as the lastasked column while using the nice PreparedStatement object and set methods in the time of the database as a timestamp that can't really be tempered. If there is a better way of doing it, I'm open to suggestions.

The reason I want to use the PreparedStatement object is the ease of handling blobs - I want to insert later on a blob with photo and personal journal/notes which would make two separate blob, and while VARCHAR or LONG VARCHAR might be good enough for short notes, I don't think it would be enough for longer texts, and surely not useful for picture

Upvotes: 0

Views: 428

Answers (1)

beny23
beny23

Reputation: 35008

You can mix the two approaches:

String sql= "insert into table1 (name, phone, bday, lastasked) values (?,?,?,now())";
PreparedStatenent pst = con.prepareStatement(sql);
pst.setString(1, "Stacy");
pst.setString(2, "555-0123456");
pst.setString(3, bdayFromInput);
pst.execute();

Note, your 2nd approach is susceptible to SQL injection, which is why using PreparedStatement is recommended when passing values to the JDBC driver.

Upvotes: 2

Related Questions