Skip to content

Posts tagged ‘sql’

22
Aug

Selecting Rows With Chinese Characters in MySQL

…or Russian/Cyrillic characters, or Arabic, or any characters of non-latin origin.

On this blog, I’ve let tons of spam comments pile up in the pending queue. Their number grew to the point of being completely unmanageable. Finally I decided to clean it up, and I had to find a way to bulk delete rows from the wp_comments table in the WordPress database. One thing I noticed was that hundreds, probably thousands, of spam comments contained Chinese characters. Other comments contained Russian or other non-latin characters. But how to select comments in which non-latin characters appear without selecting for specific strings (for example by using LIKE or REGEXP)?

It turns out the way to do it is like this:

SELECT  * 
FROM    wp_comments 
WHERE   comment_content != CONVERT(comment_content USING latin1)

CONVERT … USING will convert the column value to the specified character set, replacing any characters that do not map into the set with a question mark. If there are no Chinese/Russian/etc. characters, the output of CONVERT … USING will be the same as the unconverted column value.

This was the SQL statement I used to delete the unwanted posts:

DELETE FROM wp_comments 
WHERE   comment_content != CONVERT(comment_content USING latin1) AND 
        comment_approved = '0'

If there is any interest, I can post some other SQL statements I used to clean up the spam.

21
Aug

Log SQL Statements With Parameter Values Filled In (Spring JDBC)

If you’re using parameterized queries with Spring JDBC (and why wouldn’t you?), it’s easy to log them with a simple log.debug() statement. But your logged statements will be full of ?’s instead of the values, which makes them much less useful. Suppose you’d like to know what was substituted for those question marks. It’s not so difficult if you use AspectJ.  Here is what is needed:

  1. aspectjrt.jar and aspectjweaver.jar from here.
  2. An aspect with a pointcut on the JdbcOperations interface, which JdbcTemplate implements.
  3. @Before advice that intercepts the execution of JdbcTemplate methods and logs de-parameterized SQL statements.
  4. Configuration of Spring applicationContext.xml to get it working.

Let’s start off with our class:

@Aspect
public class SqlLogger {
    private static final Logger log = LoggerFactory.getLogger(SqlLogger.class);

Here, I’m using an org.slf4j.Logger, but any logging framework will work.

Next step is to add the method that will capture the SQL and parameters as they are executed. Again, I’m using Spring JDBC, so all calls are made to an object that implements JdbcOperations.  We can set up our class to spy on all relevant calls by defining our method like so:

Read moreRead more

21
Apr

Profile SQL statements in Java / Spring

Wouldn’t it be nice if there were a way to time your application’s SQL statements unobtrusively? This information could give you insight into the performance of your queries and updates and help you identify slow, poorly-performing SQL. Of course, there is a way to add such SQL profiling to your Spring application, by using AspectJ.

I use Spring JDBC and wanted to identify slow SQL queries in my application so that I could tune them in order to improve overall performance.  Capturing the execution times whenever SQL is executed can be done by creating a pointcut on the methods of JdbcTemplate.  Here is what we need:

  1. aspectjrt.jar and aspectjweaver.jar from here.
  2. An aspect with a pointcut on the JdbcOperations interface, which JdbcTemplate implements.
  3. @Around advice that times the execution of JdbcTemplate methods and stores this data for later retrieval.
  4. Configuration of Spring applicationContext.xml to get it working.

The pointcut looks like this, with the String argument being the SQL statement:
Read moreRead more