Can I use a single database connection among multiple threads without any problem? In other words is SRConnection thread safe?
Another question:
I have this database query:
Integer systemBytes = (Integer)con.runScalarQuery(“SELECT TransactionID FROM Messages WHERE TimeSent IS NOT NULL ORDER BY TransactionID DESC”);
Will runScalarQuery automatically add “Limit 1” to this query, so that only one row is returned? I don’t want to add the “Limit 1” myself because that’s not cross-database.
In effect, as thread-safe as java.sql.Connection… which is to say, not quite defined. It’s going to depend on the implementation of the driver as to how safe it is.
In general, I wouldn’t recommend hanging on to the connection and reusing it. The connection pool works well, so you should feel free to get, use, and close connections as you need them.
As for your other question, the runScalarQuery function calls Statement.setMaxRows(1), but whether or not that’s used for optimization is up to the db driver. SRConnection does, however, have the runPrepLimitQuery, which will apply the limit, or you can do what it does, and use conn.getParentDatasource().getTranslator().applyLimit("SELECT ...", 1)
to build your query.
Regards,
Thank you!
Completely incidentally, this morning I happened to learn that the [tt]setMaxRows[/tt] call gets translated by the MySQL Driver into a “SET SQL_SELECT_LIMIT=1” call. Couldn’t find too much information about how this compares to “limit” in regards to performance, but I would hope that it would be comparable.
Just reminded me of this thread, so thought I’d post.