Logging PreparedStatements in Java

One thing that always been a pain is to log SQL (JDBC) errors when you have a PreparedStatement instead of the query itself.

You always end up with messages like:

2008-10-20 09:19:48,114 ERROR LoggingQueueConsumer-52 [Logger.error:168] Error 
executing SQL: [INSERT INTO private_rooms_bans (room_id, name, user_id, msisdn, 
nickname) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE room_id = ?, name = ?, 
user_id = ?, msisdn = ?, nickname = ?]

Of course I could write a helper method for retrieving the values and parsing/substitute the question marks with real values (and probably will go down that path if I don't get an outcome of this question), but I just wanted to know if this problem was resolved before by someone else and/or if is there any generic logging helper that would do that automagically for me.

Edited after a few answers:

The libraries provided so far seems to be suitable to logging the statements for debugging, which no doubt is useful. However, I am looking to a way of taking a PreparedStatement itself (not some subclass) and logging its SQL statement whenever an error occur. I wouldn't like to deploy a production app with an alternate implementation of PreparedStatement.

I guess what I am looking for an utility class, not a PreparedStatement specialization.

Thanks!

Asked by: Ada646 | Posted: 21-01-2022

Answer 1

I tried log4jdbc and it did the job for me.

SECURITY NOTE: As of today August 2011, the logged results of a log4jdbc prepared statement are NOT SAFE to execute. They can be used for analysis, but should NEVER be fed back into a DBMS.

Example of log generated by logjdbc:

2010/08/12 16:30:56 jdbc.sqlonly org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) 8. INSERT INTO A_TABLE (ID_FILE,CODE1,ID_G,ID_SEQUENCE,REF,NAME,BAR,DRINK_ID,AMOUNT,DESCRIPTION,STATUS,CODE2,REJECT_DESCR,ID_CUST_REJ) VALUES (2,'123',1,'2','aa','awe',null,'0123',4317.95,'Rccc','0',null,null,null)

The library is very easy to setup:

My configuration with HSQLDB :

jdbc.url=jdbc:log4jdbc:hsqldb:mem:sample

With Oracle :

jdbc.url=jdbc:log4jdbc:oracle:thin:@mybdd:1521:smt
jdbc.driverClass=net.sf.log4jdbc.DriverSpy

logback.xml :

<logger name="jdbc.sqlonly" level="DEBUG"/>

Too bad it wasn't on a maven repository, but still useful.
From what I tried, if you set

You will only get the statements in error, however, I don't know if this library has an impact on performance.

Answered by: Roman964 | Posted: 22-02-2022

Answer 2

This is very database-dependent. For example, I understand that some JDBC drivers (e.g. sybase, maybe ms-sql) handle prepared statements by create a temporary stored procedure on the server, and then invoking that procedure with the supplied arguments. So the complete SQL is never actually passed from the client.

As a result, the JDBC API does not expose the information you are after. You may be able to cast your statement objects the internal driver implementation, but probably not - your appserver may well wrap the statements in its own implementation.

I think you may just have to bite the bullet and write your own class which interpolates the arguments into the placeholder SQL. This will be awkward, because you can't ask PreparedStatement for the parameters that have been set, so you'll have to remember them in a helper object, before passing them to the statement.

It seems to me that one of the utility libraries which wrap your driver's implementation objects is the most practical way of doing what you're trying to achieve, but it's going to be unpleasant either way.

Answered by: Julian362 | Posted: 22-02-2022

Answer 3

Use P6Spy: Its Oracle, Mysql, JNDI, JMX, Spring and Maven friendly. Highly configurable. Simple and low level integration Can print the stacktrace. Can only print heavy calls - time threashold based.

Answered by: Lenny217 | Posted: 22-02-2022

Answer 4

If you are using MySQL, MySQL Connector's PreparedStatement.toString() does include the bound parameters. Though third-party connection pools may break this.
Sub-class PreparedStatement to build up the query string as parameters are added. There's no way to extract the SQL from a PreparedStatement, as it uses a compiled binary form.

LoggedPreparedStatement looks promising, though I haven't tried it.

One advantage of these over a proxy driver that logs all queries is that you can modify the query string before logging it. For example in a PCI environment you might want to mask card numbers.

Answered by: Tess766 | Posted: 22-02-2022

Similar questions

mysql - Wildcards in Java PreparedStatements

Here's my current SQL statement: SEARCH_ALBUMS_SQL = "SELECT * FROM albums WHERE title LIKE ? OR artist LIKE ?;"; It's returning exact matches to the album or artist names, but not anything else. I can't use a '%' in the statement or I get errors. How do I add wildcards to a prepared statement? (I'm using Java5 and MySQL) Thanks!

jdbc - Where to close java PreparedStatements and ResultSets?

Consider the code: PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.createStatement(myQueryString); rs = ps.executeQuery(); // process the results... } catch (java.sql.SQLException e) { log.error("an error!", e); throw new MyAppException("I'm sorry. Your query did not work."); } finally { ps.close(); rs.close(); } The above does not compile, because both

mysql - Should I be using PreparedStatements for all my database inserts in Java?

What is the recommended method for escaping variables before inserting them into the database in Java? As I understand, I can use PreparedStatement.setString() to escape the data, but PreparedStatement seems somewhat impractical if I don't plan to run the same query ever again.. Is there a better way to do it without preparing every query?

java - Can RowSets be used with PreparedStatements?

I have just found RowSets for database querying with JDBC. They are stateless and cacheable, they look to be superior to ResultSets. Can PreparedStatements be used with them though? PreparedStatements are a performance booster for querying very large databases, and not something I would want to give up (before something is said, this is not premature optimization, we have a proven speed need!!). I need the fastest quer...

java - PreparedStatements and performance

So I keep hearing that PreparedStatements are good for performance. We have a Java application in which we use the regular 'Statement' more than we use the 'PreparedStatement'. While trying to move towards using more PreparedStatements, I am trying to get a more thorough understanding of how PreparedStatements work - on the client side and the server side. So if we have some typical CRUD operations and upda...

java - Why do I need a connection to create PreparedStatements?

I would like to use prepared statements, for many reasons. But, I would like to create a method that looks like this: /* This opens a connection, executes the query, and closes the connection */ public static void executeNonQuery(String queryString); In other words, I want my application l...

multithreading - Is this use of PreparedStatements in a Thread in Java correct?

I'm still an undergrad just working part time and so I'm always trying to be aware of better ways to do things. Recently I had to write a program for work where the main thread of the program would spawn "task" threads (for each db "task" record) which would perform some operations and then update the record to say that it has finished. Therefore I needed a database connection object and PreparedStatement objects in or ava...

mysql - Is it expensive to hold on to PreparedStatements? (Java & JDBC)

I'm trying to figure out if it's efficient for me to cache all of my statements when I create my database connection or if I should only create those that are most used and create the others if/when they're needed.. It seems foolish to create all of the statements in all of the client threads. Any feedback would be greatly appreciated.

java - how to retrieve data in struts using PreparedStatements with the help of ArrayList?

I am a beginner in struts want to be practice in DAO. I want to know how to retrieve data from table using PreparedStatement and ArrayList?

java - Closing PreparedStatements

Does use of PreparedStatements and ResultSets creates a "new database instance" everytime they are used? Or, whith other words, if I use a PreparedStatement and a ResultSet, should I close them after every use or once I finish? Example: while (...){ p = connection.prepareStatement(...); r = p.executeQuery(); while (r.next()) { .... } } // We close at the end...

Still can't find your answer? Check out these amazing Java communities for help...

top