The Holy Java

Building the right thing, building it right, fast

Posts Tagged ‘jdbc’

JDBC: What resources you have to close and when?

Posted by Jakub Holý on February 18, 2013

I was never sure what resources in JDBC must be explicitely closed and wasn’t able to find it anywhere explained. Finally my good colleague, Magne Mære, has explained it to me:

In JDBC there are several kinds of resources that ideally should be closed after use.  Even though every Statement and PreparedStatement is specified to be implicitly closed when the Connection object is closed, you can’t be guaranteed when (or if) this happens, especially if it’s used with connection pooling. You should explicitly close your Statement and PreparedStatement objects to be sure. ResultSet objects might also be an issue, but as they are guaranteed to be closed when the corresponding Statement/PreparedStatement object is closed, you can usually disregard it.

Summary: Always close PreparedStatement/Statement and Connection. (Of course, with Java 7+ you’d use the try-with-resources idiom to make it happen automatically.)

PS: I believe that the close() method on pooled connections doesn’t actually close them but just returns them to the pool.

A request to my dear users: References to any good resources would be appreciate.

Posted in Languages | Tagged: , , | 3 Comments »

The power of batching or speeding JDBC by 100

Posted by Jakub Holý on September 20, 2010

We all know that one coarse-grained operation is more efficient than a number of fine-grained ones when communicating over the network boundary but until recently I haven’t realized how big that difference may be. While performing a simple query individually for each input record proceeded with the speed of 11k records per hour, when I grouped each 100 queries together (with “… WHERE id IN (value1, .., value100)), all 200k records were processed in 13 minutes. In other words, using a batch of the size 100 led to the speed-up of nearly two orders of magnitude!

The moral: It really pays of to spend a little more time on writing the more complex batch-enabled JDBC code whenever dealing with larger amounts of data. (And it wasn’t that much more effort thanks to Groovy SQL.)

Posted in Databases, Languages | Tagged: , , , | Comments Off

Most interesting links of August

Posted by Jakub Holý on August 31, 2010

I hope everybody is enjoying the holiday and not spending hours on tech blogs and sites. At least I do :-) and thus this month’s list is a short one:

  • Working With Static Imports in Eclipse – how to make working with static imports (nearly) as easy as with the normal ones (especially useful for fluent interfaces and “DSLs”), mainly by adding types like JUnit’s Assert and Mockito to your favorite imports and setting Eclipse to always generate static imports in the form <type>.*
  • 5 things you didn’t know about … Java Database Connectivity – it was interesting to learn that JDBC specifies some scalar functions that drivers may support and translate into the DB’s language such as “{CURRENT_DATE()}”; for common functions supported by most drivers this should make your implementation more portable
  • Four Things to Remember about java.lang.String – a really good one thanks to information on how to compare correctly the same Unicode character/string that can be encoded in different ways with java.text.Normalizer.normalize and Locale-sensitive comparison ignoring optionally unimportant differences such as letter size and accents (using a Collator)

Posted in Languages, Tools, Top links of month | Tagged: , , , | Comments Off

Most interesting links of July

Posted by Jakub Holý on August 2, 2010

This month about performance, the Java language and patterns, the development process, and a few interesting news.

Read the rest of this entry »

Posted in Languages, Top links of month | Tagged: , , , , | Comments Off

A logging wrapper around PreparedStatement to provide detailed info upon error

Posted by Jakub Holý on May 23, 2009

In my java web application I use JDBC to store data in batches into a database. When there is a problem the whole batch insert fails and it’s difficult to find out what data caused it to fail. Therefore I’ve created a wrapper around PreparedStatement that remembers values passed into the various set* methods and can provide a comma-separated listing of all rows in the batch upon failure.

This is my LoggingStatementDecorator that stores values for later logging; based on java.lang.reflect.Proxy:

package eu.ibacz.example;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.PreparedStatement;
import java.util.LinkedList;
import java.util.List;

/**
 * Remember values passed into a sql statement via setString etc. for later logging. 
 */
class LoggingStatementDecorator implements InvocationHandler {
    
    /** File's Subversion info (version etc.). */
    public static final String SVN_ID = "$id$";
    
    private List<List<Object>> batch = new LinkedList<List<Object>>();
    private List<Object> currentRow = new LinkedList<Object>();
    private PreparedStatement target;
    private boolean failed = false;
    
    public LoggingStatementDecorator(PreparedStatement target) {
        if (target == null) throw new IllegalArgumentException("'target' can't be null.");
        this.target = target;
    }


     // @see java.lang.reflect.InvocationHandler#invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object[]) */
    public Object invoke(Object proxy, Method method, Object[] args)
            throws Throwable {
        
        final Object result; 
        
        try {
            result = method.invoke(target, args);
            failed = false;
        } catch (InvocationTargetException e) {
            failed = true;
            throw e.getTargetException();
        } catch (Exception e) {
            failed = true;
            throw e;
        }
        
        if ( method.getName().startsWith("setNull") 
                && (args.length >=1 && Integer.TYPE == method.getParameterTypes()[0] ) ) {
            handleSetSomething((Integer) args[0], null);
        } else if ( method.getName().startsWith("set") 
                && (args.length >=2 && Integer.TYPE == method.getParameterTypes()[0] ) ) {
            handleSetSomething((Integer) args[0], args[1]);
        } else if ("addBatch".equals(method.getName())) {
            handleAddBatch();
        }
        
        return result;
    }
    
    private void handleSetSomething(int index, Object value) {
        currentRow.add(value);
    }
    
    private void handleAddBatch() {
        batch.add(currentRow);
        currentRow = new LinkedList<Object>();
    }
    
    public List<List<Object>> getValues() {
        return batch;
    }
    
    public PreparedStatement getTarget() { return target; }
    
    /** Has the last method called on the Statement caused an exception? */
    public boolean isFailed() { return failed; }
    
    public String toString() { return "LoggingHandler[failed="+failed+"]"; }
    
    /** Values as comma-separated values. */
    public String getValuesAsCsv() {
        StringBuilder csv = new StringBuilder();
        for (List<Object> row : getValues()) {
            for (Object field : row) {
                // Escape Strings
                if (field instanceof String) {
                    field = "'" + ((String) field).replaceAll("'", "''") + "'";
                }
                csv.append(field).append(",");
            }
            csv.append("\n");
        }
        return csv.toString();
    } /* getValuesAsCsv */
    
    public PreparedStatement createProxy() {
        return (PreparedStatement) Proxy.newProxyInstance(
                PreparedStatement.class.getClassLoader(),
                new Class[] { PreparedStatement.class },
                this);
    };
    
}

And this is how you use it:

        // ...
        PreparedStatement stmt = null;
        try {
            LoggingStatementDecorator stmtHandler = new LoggingStatementDecorator( connection.prepareStatement("insert into mytable values(?,?)") );
            stmt =  stmtHandler.createProxy();
            
            // add data to the batch
            for(int i=0; i<10; ++i) {
                stmt.setInt(1, i);
                stmt.setString(2, "Row number " + i);
                stmt.addBatch();
            }
            
            stmt.executeBatch();
            
        } catch (SQLException e) {
            // ... some rollback etc.
            
            LoggingStatementDecorator stmtHandler = (LoggingStatementDecorator)
                    ((stmt instanceof Proxy)? Proxy.getInvocationHandler(stmt) : null);
                // TODO include the insert sql in the log!!!
                StringBuilder log = new StringBuilder();
                log = buildFailureInfo("mytable", stmtHandler, log);
                LOG.error("Failure while processing data:" + log, e);
            }
        }
            

    private StringBuilder buildFailureInfo(String table, LoggingStatementDecorator stmtHandler, StringBuilder details) {
        
        if (stmtHandler != null && stmtHandler.isFailed()) {
            // Already insertion of records failed
            details.append("\nInsert of records failed. Table=").append(table)
                .append("), values=[\n").append(stmtHandler.getValuesAsCsv()).append("]");
            
        }
        
        return details;
    } /* buildFailureInfo */

When an excepion occures, you get nice log that shall tell you all you need to detect the problem or reproduce it.

Fotnote: Of course I could have perhaps used the open-source P6Spy but I’m afraid it would log more than I need (I believe it to be bound to a data source, not a particular webapp’s PreparedStatement).

Posted in Languages | Tagged: , , , , | Comments Off

Truncating UTF String to the given number of bytes while preserving its validity [for DB insert]

Posted by Jakub Holý on November 2, 2007

Often you need to insert a String from Java into a database column with a fixed length specified in bytes.
Using

string.substring(0, DB_FIELD_LENGTH);

isn’t enough because it only cuts down the number of characters but in UTF-8 a single character may be represented by 1-4 bytes. But you cannot just turn the string into an array of bytes and use its first DB_FIELD_LENGTH elements because you could end up with an invalid UTF-8 character at the end (one that is represented by 2+ bytes while only its 1st byte fits into the field). There are two solutions for truncation the string in such a way, that it has at most DB_FIELD_LENGTH bytes and is a valid UTF-8 string.

Approach 1: Replace the invalid trailing byte(s) with a ‘rectangle’

This is as simple as:

int maxLen = DB_FIELD_LENGTH-2;
string = new String( string.getBytes("UTF-8") , 0, maxLen, "UTF-8");

The new String constructor will automatically replace any invalid character (i.e. incomplete utf-8 char; we may only have one at the end) with the character \uFFFD, which looks like an empty rectangle. This character requires 3 bytes in utf-8 – therefore we decrease DB_FIELD_LENGTH by 2; the resulting string will have either exactly maxLen bytes if its last byte(s) is a valid utf-8 character or maxLen+2 bytes if it isn’t valid and this 1 byte was replaced by \uFFFD (3B).

Approach 2: Skip the invalid trailing byte(s) altogether

If you don’t want to have the rectangle character in the place of a split multibyte character, you must do yourself what the String constructor does internally, in a bit different way:

import java.nio.*; import java.nio.charset.*;
Charset utf8Charset = Charset.forName("UTF-8");
CharsetDecoder cd = utf8Charset.newDecoder();
byte[] sba = string.getBytes("UTF-8");
// Ensure truncating by having byte buffer = DB_FIELD_LENGTH
ByteBuffer bb = ByteBuffer.wrap(sba, 0, DB_FIELD_LENGTH); // len in [B]
CharBuffer cb = CharBuffer.allocate(DB_FIELD_LENGTH); // len in [char] <= # [B]
// Ignore an incomplete character
cd.onMalformedInput(CodingErrorAction.IGNORE)
cd.decode(bb, cb, true);
cd.flush(cb);
string = new String(cb.array(), 0, cb.position());

The string will end with the last valid character in the given range.

Approach 3: Manually remove the invalid trailing bytes

As you can see, the approach 2 requires quite lot of coding and method calls. If you know the details of UTF-8, namely how to distinguish an invalid byte or byte sequence then you can simply truncate the byte array and then remove/replace the invalid bytes. I’d be glad for the code :-)

Posted in Databases, Languages | Tagged: , , , , | Comments Off