3

From what I understand, MySQL 5.1 supports server-side prepared statements. Therefore the following code should prepare the statement once, and execute it 10 times:

    Connection conn = ds.getConnection();
    PreparedStatement stmt = conn.prepareStatement("SELECT COUNT(*) FROM users WHERE user_id=?");
    for (int i=0; i<10; i++)
    {
        stmt.setString(1, "FOO"+i);
        ResultSet res = stmt.executeQuery();
        res.close();
    }
    stmt.close();
    conn.close();

What I see instead in the mysqld log is the query being executed directly:

    SELECT @@session.tx_isolation
    SELECT USER()
    SELECT COUNT(*) FROM users WHERE user_id='FOO0'
    SELECT COUNT(*) FROM users WHERE user_id='FOO1'
    SELECT COUNT(*) FROM users WHERE user_id='FOO2'
            ...

I see the query sent in the full each time in the protocol logs too (using tcpdump).

Using Connector/J 5.1.12 and MySQL 5.1.44. No funny JDBC options in the JDBC URL. Going straight to the driver for this test, no pool.

Why aren't the statements being prepared?

sehugg
  • 3,615
  • 5
  • 43
  • 60

1 Answers1

8

The Connector/J driver handles prepared statements locally unless you turn on real server side statements using the connection parameter useServerPrepStmts=true.

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
  • 2
    I'm not sure if this has been fixed since then (reading changelogs isn't my idea of a good time), but there were a bunch of problems with server-side prepared statements and so they were disabled by default in Connector/J (http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-0-5.html). – Christopher Schultz Feb 13 '14 at 17:04