PreparedStatement Logging

Question

I use log4j for the logging. I could see the SQLs through Log4j like below.

Here's my java source which access data base with jdbcTemplate.

public QnaDTO selectBoard(int articleID) {
        String SQL = 
            "SELECT " +
            "   QA.ARTICLE_ID, " +
            "   QA.EMAIL, " +
            "   QA.TEL, " +
            "   QA.CATEGORY_ID, " +
            "   CG.CATEGORY_NAME, " +
            "   QA.SUBJECT, " +
            "   QA.CONTESTS, " +
            "   QA.WRITER_NAME, " +
            "   QA.WRITER_ID, " +
            "   QA.READCOUNT, " +
            "   QA.ANSWER, " +
            "   QA.FILE_NAME, " +
            "   QA.OPEN_FLG, " +
            "   QA.KTOPEN_FLG, " +
            "   TO_CHAR(QA.WRITE_DAY, 'YYYY.MM.DD') WRITE_DAY, " +
            "   QA.DISPOSAL_FLG " +
            "FROM QNA QA JOIN QNA_CATEGORY_GROUP CG " +
            "ON QA.CATEGORY_ID = CG.CATEGORY_ID " +
            "WHERE QA.ARTICLE_ID = ? ";

        QnaDTO qnaDTO = (QnaDTO) jdbcTemplate.queryForObject(
                SQL,
                new Object[]{articleID}, 
                new RowMapper() {
                    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                        QnaDTO qnaDTO = new QnaDTO();
                        qnaDTO.setArticleID(rs.getInt("ARTICLE_ID"));
                        qnaDTO.setCategoryID(rs.getInt("CATEGORY_ID"));
                        qnaDTO.setCategoryName(rs.getString("CATEGORY_NAME"));
                        qnaDTO.setEmail1(rs.getString("EMAIL"));
                        qnaDTO.setTel1(rs.getString("TEL"));
                        qnaDTO.setSubject(rs.getString("SUBJECT"));
                        qnaDTO.setContests(rs.getString("CONTESTS"));
                        qnaDTO.setName(rs.getString("WRITER_NAME"));
                        qnaDTO.setUserID(rs.getString("WRITER_ID"));
                        //
                        qnaDTO.setReadcount(rs.getString("READCOUNT"));
                        qnaDTO.setAnswer(rs.getString("ANSWER"));
                        qnaDTO.setFileName(rs.getString("FILE_NAME"));
                        qnaDTO.setOpenFlg(rs.getString("OPEN_FLG"));
                        qnaDTO.setKtOpenFlg(rs.getString("KTOPEN_FLG"));
                        //
                        qnaDTO.setWriteDay(rs.getString("WRITE_DAY"));
                        qnaDTO.setDisposalFlg(rs.getString("DISPOSAL_FLG"));
                        return qnaDTO;
                    }
                }
            );
            return qnaDTO;
    }

As you can see above. jdbcTemplate.queryForObject(...) is the method which really send Query And Get some result.

Inside of jdbcTemplate.queryForObject, finally logger used

public Object query(final String sql, final ResultSetExtractor rse)
    throws DataAccessException
{
    Assert.notNull(sql, "SQL must not be null");
    Assert.notNull(rse, "ResultSetExtractor must not be null");
    if(logger.isDebugEnabled())
        logger.debug("Executing SQL query [" + sql + "]");
    class _cls1QueryStatementCallback
        implements StatementCallback, SqlProvider
    {

        public Object doInStatement(Statement stmt)
            throws SQLException
        {
            ResultSet rs = null;
            Object obj;
            try
            {
                rs = stmt.executeQuery(sql);
                ResultSet rsToUse = rs;
                if(nativeJdbcExtractor != null)
                    rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
                obj = rse.extractData(rsToUse);
            }
            finally
            {
                JdbcUtils.closeResultSet(rs);
            }
            return obj;
        }

        public String getSql()
        {
            return sql;
        }

        _cls1QueryStatementCallback()
        {
            super();
        }
    }

    return execute(new _cls1QueryStatementCallback());
}

But with above sources, I could only get the SQL with ?. What I want is that my result doesn't have question mark ? It means filling ? with real data.

Is there any way to do this? thanks

when the code has some filling the real data, then you should log that using toString() or any equivalent method. — Jasonw, Apr 04 '12 at 02:10
shouldn't be that difficult, update this question with a few lines of the java codes between your select query and then I will take a look. — Jasonw, Apr 04 '12 at 02:23

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

Jeon, sorry, was occupied with work. :-) Anyway, I have looked into your code and replicate here using spring 2.5. I've also google and I think you want to read this and this further to understand.

From the official documentation,

Finally, all of the SQL issued by this class is logged at the 'DEBUG' level under the category corresponding to the fully qualified class name of the template instance (typically JdbcTemplate, but it may be different if a custom subclass of the JdbcTemplate class is being used).

So you need to figure out how to enable logging with debug level.

Not sure exactly how you trace, but with my trace, I end up to below. So if you enable debug level, you should be able to see the output, maybe not exactly like QA.ARTICLE_ID = 123; but you should probably get the value printed in the next line something like in that example. Anyway, I don't have the exact setup like in your environment but this I think should you give a clue.

public Object execute(PreparedStatementCreator psc, PreparedStatementCallback action)
        throws DataAccessException {

    Assert.notNull(psc, "PreparedStatementCreator must not be null");
    Assert.notNull(action, "Callback object must not be null");
    if (logger.isDebugEnabled()) {
        String sql = getSql(psc);
        logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));
    }

edited May 23 '17 at 12:04

Community

1
1

answered Apr 04 '12 at 09:40

Jasonw

5,054
7
43
48

Yes, I know how to set DEBUG MODE on. – jeon Apr 05 '12 at 00:18
Depending on my research yesterday, there's no way to my extract SQL from PreparedStatement instance. Anyway, I am seeing the value filled SQL now. I could do this because I print them before they're sent to *execute* method or something similar method thanks to you. I appreciate your help!! from Korea. – jeon Apr 05 '12 at 00:25
this is simply source only to see log. – jeon Apr 05 '12 at 00:37
public Object query(String sql, Object[] args, ResultSetExtractor rse) throws DataAccessException { printFormattedSql(sql, args); return query(sql, new ArgPreparedStatementSetter(args), rse); } – jeon Apr 05 '12 at 00:38
public void printFormattedSql(String sql, Object[] args) { for(int i=0; i – jeon Apr 05 '12 at 00:38
jeon, good job! I'm glad it works out fine in your sample code. Keep up the good work. – Jasonw Apr 05 '12 at 01:42

PreparedStatement Logging

1 Answers1