More‎ > ‎

How to Reduce Boilerplate Code With Spring JDBC Template?

BY MARKUS SPRUNCK


A simple code sample describes the difference in respect to code size, coverage and static code analysis warnings. Spring's JDBCTemplate can significantly reduce the boilerplate code and due to the smaller code - increase code coverage of standard Junit tests. But it has also the disadvantage to hide some FindBugs warnings related to security.

Code Size and Code Coverage

In respect to code size the use of the JDBCTemplate has a big advantage over native JDBC implementations. The native JDBC sample code (JDBCSample.java) has 135 instructions and the JDBCTemplate code (JDBCSpring.java and JDBCSampleSpringOutput.java) needs 59 instructions. 

The additional file application-config.xml can be ignored, because it is needed just once in the application.

Figure 1: Project structure and code coverage with EclEmma

In the JDBCTemplate sample shows 100% for line coverage and 100% for coverage of branches. Compared with 73,3% for line coverage and 42,9% for coverage of branches in the native JDBC implementation. 

Two FindBugs Warnings Missing for JDBCTemplate

The sample code of the native JDBC implementation two FindBugs warnings appear, i.e.: 
  • DMI_CONSTANT_DB_PASSWORD (see Figure 2) and
  • SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE (see Figure 3). 
In the Spring version of the sample these warnings will not be reported by FindBugs. The information about the database password are in the file application-config.xml as configuration of bean with the id="dataSource".

Figure 2: DMI_CONSTANT_DB_PASSWORD

Passing a non constant String to the executeQuery(sql) will be reported by FindBugs with a warning if the string can
be manipulated after compilation. With JDBCTemplate this code is hidden in the Spring libraries and FindBugs can't report
it in your source code.

Figure 3:  SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE

Both warnings are - in general - not serious. But, if your application has advanced security requirements (web-application, banking environment, aviation/military industry, etc.) and JDBCTemplate is used, you should define Spring related coding standards and review the code.  

Sample Code

File #1: JDBCTest.java

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package com.sw_engineering_candies.sample;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
import org.apache.log4j.Logger;
 
class JDBCTest {
 
    private static final Logger LOGGER = Logger.getLogger(JDBCTest.class);
 
    private static final String SQL_CORRECT = "SELECT answer, owner FROM T_ADVICE WHERE owner LIKE 'Jo%'";
 
    private static final String SQL_WRONG = "SELECT answer, owner, text FROM T_ADVICE WHERE owner LIKE 'Jo%'";
 
    private static void prepareDatabase() {
        LOGGER.info("create and fill test database\n");
        Statement statement = null;
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:derby:database;create=true", "APP", "mypwd");
            statement = connection.statement();
            statement.execute("create table T_ADVICE (answer varchar(255), " + "owner varchar(255))");
            statement.execute("insert into T_ADVICE ( answer, owner ) values " + "('Don''t Panic', 'Joe')");
            statement.execute("insert into T_ADVICE ( answer, owner ) values " + "('Don''t Panic, too', 'Bob')");
            statement.execute("insert into T_ADVICE ( answer, owner ) values " + "('Keep Smiling', 'John')");
            statement.execute("insert into T_ADVICE ( answer, owner ) values " + "('Smille', 'Jeff')");
        } catch (final SQLException e) {
            LOGGER.error(e.getMessage());
        } finally {
            if (null != statement) {
                try {
                    statement.close();
                } catch (final SQLException e) {
                    LOGGER.error(e.getMessage());
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (final SQLException e) {
                    LOGGER.error(e.getMessage());
                }
            }
        }
    }
 
    public static void main(final String[] args) {
 
        prepareDatabase();
 
        final JDBCSample sample1 = new JDBCSample();
        sample1.execute(SQL_CORRECT);
        sample1.execute(SQL_WRONG);
 
        final JDBCSampleSpring sample2 = new JDBCSampleSpring();
        sample2.execute(SQL_CORRECT);
        sample2.execute(SQL_WRONG);
    }
}

File #2: JDBCSample.java

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package com.sw_engineering_candies.sample;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import org.apache.log4j.Logger;
 
class JDBCSample {
 
    private static final Logger LOGGER = Logger.getLogger(JDBCSample.class);
 
    private Connection connection;
 
    public JDBCSample() {
        try {
            connection = DriverManager.getConnection("jdbc:derby:database;create=false", "APP", "mypwd");
        } catch (final Exception e) {
            LOGGER.error(e.getMessage());
        }
    }
 
    public static void dispResultSet(final ResultSet resultSet) throws SQLException {
        boolean more = resultSet.next();
        while (more) {
            LOGGER.info(resultSet.getString(1) + resultSet.getString(2));
            more = resultSet.next();
        }
    }
 
    void execute(final String sqlStatement) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sqlStatement);
            dispResultSet(rs);
        } catch (final Exception e) {
            LOGGER.error(e.getMessage() + '\n');
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (final SQLException e) {
                LOGGER.error(e.getMessage());
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (final SQLException e) {
                LOGGER.error(e.getMessage());
            }
        }
    }
}

File #3: JDBCSampleSpring.java

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.sw_engineering_candies.sample;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
import javax.sql.DataSource;
 
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
 
class JDBCSampleSpring {
 
    private static final Logger LOGGER = Logger.getLogger(JDBCSampleSpring.class);
 
    private static final String CONFIG_PATH = "classpath*:com/sw_engineering_candies/sample/application-config.xml";
 
    private final DataSource dataSource;
 
    public JDBCSampleSpring() {
        final ApplicationContext context = new ClassPathXmlApplicationContext(CONFIG_PATH);
        dataSource = (DataSource) context.getBean("dataSourceBean");
    }
 
    private static class JDBCSampleSpringOutput implements RowCallbackHandler {
        @Override
        public void processRow(final ResultSet resultSet) throws SQLException {
            do {
                LOGGER.info(resultSet.getString(1) + resultSet.getString(2));
            } while (resultSet.next());
        }
    }
 
    void execute(final String sqlStatement) {
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        try {
            jdbcTemplate.query(sqlStatement, new JDBCSampleSpringOutput());
        } catch (final DataAccessException e) {
            LOGGER.error(e.getMessage());
        }
    }
 
}

File #4: application-config.xml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
<beans>
  
    ...
 
    <context:annotation-config></context:annotation-config>
    <context:component-scan base-package="com.sw_engineering_candies.sample"></context:component-scan>
 
    <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource" name="dataSourceBean">
        <property name="driverClassName" value="org.apache.derby.jdbc.EmbeddedDriver"></property>
        <property name="url" value="jdbc:derby:database;create=false"></property>
        <property name="username" value="APP"></property>
        <property name="password" value="mypwd"></property>
    </bean>
    ...
     
</beans>

File #5: log4j.properties

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# Set root category priority to DEBUG and set its only appender to A1
log4j.rootCategory=INFO, A1
# A1 is set to be a PluginLogAppender (writes to .log).
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A2=org.apache.log4j.RollingFileAppender
# A1 uses PatternLayout.
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
#A2: Rolling file appender:
log4j.appender.A2.file=simple_quiz.log
log4j.appender.A2.MaxFileSize=1MB
log4j.appender.A2.MaxBackupIndex=10
log4j.appender.A2.layout=org.apache.log4j.PatternLayout
log4j.appender.A2.layout.ConversionPattern=%p %d{ABSOLUTE} %c{1} %m%n
# ALL|DEBUG|INFO|WARN|ERROR|FATAL|OFF
log4j.logger.org.springframework=WARN

The program should print the following to the standard output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
0    [main] INFO  com.sw_engineering_candies.sample.JDBCTest  - create and fill test database
2226 [main] ERROR com.sw_engineering_candies.sample.JDBCTest  - Table/View 'T_ADVICE' already exists in Schema 'APP'.
2946 [main] INFO  com.sw_engineering_candies.sample.JDBCSample  - Don't PanicJoe
2946 [main] INFO  com.sw_engineering_candies.sample.JDBCSample  - Keep SmilingJohn
2946 [main] ERROR com.sw_engineering_candies.sample.JDBCSample  -
    Column 'TEXT' is either not in any table in the FROM list or appears within a join specification and
    is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP
    BY list. If this is a CREATE or ALTER TABLE  statement then 'TEXT' is not a column in the target table.
4303 [main] INFO  com.sw_engineering_candies.sample.JDBCSampleSpring  - Don't PanicJoe
4303 [main] INFO  com.sw_engineering_candies.sample.JDBCSampleSpring  - Keep SmilingJohn
4414 [main] ERROR com.sw_engineering_candies.sample.JDBCSampleSpring  -
    StatementCallback; bad SQL grammar [SELECT answer, owner, text FROM T_ADVICE WHERE owner LIKE 'Jo%'];
    nested exception is java.sql.SQLSyntaxErrorException: Column 'TEXT' is either not in any table in the
    FROM list or appears within a join specification and is outside the scope of the join specification or
    appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement
    then 'TEXT' is not a column in the target table.

Project Configuration

The sample is compiled with JDK1.7.0 and need the following libraries:
  • commons-logging.jar
  • derby.jar
  • log4j-1.2.13.jar
  • org.springframework.asm-3.1.1.RELEASE.jar
  • org.springframework.beans-3.1.1.RELEASE.jar
  • org.springframework.context-3.1.1.RELEASE.jar
  • org.springframework.core-3.1.1.RELEASE.jar
  • org.springframework.expression-3.1.1.RELEASE.jar
  • org.springframework.jdbc-3.1.1.RELEASE.jar
  • org.springframework.transaction-3.1.1.RELEASE.jar

Recommendations

  • The Class JDBC Template is a good choice to reduce the boilerplate code in your application.

  • Be aware of the hidden security related warnings of static code analysis tools, like FindBugs.

Sponsored Link