Lesson 07 - Data Access with JDBC in Spring

Note: In this section I am going to use MySQL Database to create tables, store data and ddl scripts are placed just before the domain object for your reference

When you are writing a jdbc example, you must have noticed we need to write lots of boiler plate code along with business logic, like opening a connection, preparing statements, process the result sets, handling transaction and finally cleaning up resources after the program is finished. Spring's JDBC approach is quite fantastic where in you provide only business logic and spring takes care of all boiler plate code. Below example is a classic one. Where in we are just printing entries from a table called tbl_ourpets but we need to write similar code for any other operation.

In this example all boiler plate code is marked as BP in comments. Spring is a breath of fresh air. There are various ways to hit database like JDBCTemplate, NamedParameterJdbcTemplate and SimpleJdbcInsert, SimpleJdbcCall approach optimises database metadata. I will show you RDBMS object style as well. You can always mix and match all these techniques.

JdbcTemplate: classic and most lowest level approach. All major jdbc classes uses it internally.Supports java 5 generic and varargs.
NamedParameterJdbcTemplate: wrapper on JdbcTemplate support naming the parameters rather ? symbol.
SimpleJdbcInsert/SimpleJdbcCall: uses database metadata to query and reduced your coding. You just give table name and map of parameters matching column names. We see an example on this.
RDBMS Object Style: requires you to create few thread safe object during initialisation of data access layer like once you create/compile the query you could run it multiple times by passing different parameters.

Understanding JDBC Core Classes & Error Handling:

JdbcTemplate

this is the central class in jdbc core package and handles creation/release of resources. It performs basic jdbc tasks like statement creation, execution etc.. as a developer you need to provide logic like how to manipulate resultset. it runs selectqueries, updates data, runs stored procs, iterates over resultsets, catches jdbc exceptions and translate them to unchecked dao exceptions (from org.springframework.dao package). You need to note few interfaces you must implement (they are call back - any template approach you follow in java, you need to remember that you must implement call back interfaces) so here PreparedStatementCreator prepares a statement, CallableStatementCreator is for running stored procs, RowCallbackHanlder to extract values from each row of a ResultSet.  Keep your log4j at DEBUG level to see how the SQL is generated from spring classes. Instances of JdbcTemplate are thread safe once configured. so you can use it across multiple DAOs.It maintains reference to DataSource (but this is not conversational state i.e nt a business data state). Common practice is to inject DataSource in to your DAO. Below example explains it well. All examples in this section, for table creation, I have pasted MySql DDL script in comments just before the respective java class.

This shows JdbcTempalte is very hassle free of writing jdbc code but above code still using ? placeholder to mark parameter names. There NamedParameterJdbcTemplate comes in to picture.

NamedParameterJdbcTemplate

This wraps JdbcTemplate to do all the underlying work but allows us to define named parameters rather ? in queries. Most of the method of this class expects a SQLParameterSource interface implementations (such as MapSqlParameterSource and BeanPropertySqlParameterSource etc..) Advantage of MapSqlParameterSource is you can provide inputs as a map of string values and BeanPropertySqlParameterSource allows us to use given source bean property values as named parameter values. Since NamedParameterJdbcTemplate wraps JdbcTemplate, we can access underlying JdbcTemplate with the help of getJdbcOptions() method. Below example explains this.

And now we will see how transalation of exceptions happens in spring from SQLExceptions to spring's specific DataAccessException(from dao package). To realize this, a class must implement SQLExceptionTranslator interface. SQLErrorCodeSQLExceptionHandler is the default implementation we use. Refer this link where in error codes are held in bean type called SQLErrorCodes and depending on property databaseProductName this class is created and populated by an SQLErrorCodesFactory. TODO

Other facilities of JdbcTemplate

With the help of JdbcTemplate, its fairly easy to run any SQL statement. For example below statement literally create a table in the underlying database.

this.jdbcTemplate.execute("create table ABC (id integer, name varchar(100))");

Similarly as  I shown in above example, method getTotalPetCount() uses a method called queryForInt() to return a integer value. Similarly we have queryForObject which takes a SQL query and type of the return value as parameter like below:

this.jdbcTemplate.queryForObject("select petname from tbl_ourpets where rownum=1", String.class);

Added to queryForInt, queryForObject we have queryForList() which returns list of objects. In my example printAllPets() uses this. In JdbcTemplate's methods one of the important method is "update()" method which is used to udpate/insert new data.

this.jdbcTemplate.update("update mytable set name = ? where id = ?", name, id)

advantage of this method is you could pass varargs or array o objects as input to this method. When we are generating keys as auto generated values consider using "KeyHolder" as input to update method. The generated key will be stored in the passed in KeyHolder object and we can retrieve it by keyHolder.getKey() method. We see an example using this in next section.Nothing to be worried if you did not understand this.

Some theory on Other classes of Spring JDBC

DataSourceUtils: is a convenient class to obtain connections from JNDI and close connection if necessary.

SmartDataSource: Implement this interface by classes that provide connection to a relational database. Actually it extends concept of DataSource that allows to query where connection should be closed after a given operation. Its the best option if you reuse the connections.

AbstractDataSource: This is the abstract class for all spring data source  implementation. Consider implementing this if you are writing a new DataSource implementation on your own.

SingleConnectionDataSource: Name itself indicating, wraps a single Connection which is not closed after each use. so this is not ideal for multi threaded.

DriverManagerDataSource: Implementation of DataSource that provides a new connection every time which configures a plain JDBC driver. This is to be used only for testing purpose. In my examples, I am using apache common's dbcp implementation which can be changes like below to use spring's implementation of DriverMabagerDataSource.

<!-- class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" -->

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >

<property name="driverClassName" value="com.mysql.jdbc.Driver" />

<property name="url" value="jdbc:mysql://127.0.0.1:3306/test" />

<property name="username" value="" /> <!-- give user name here -->

<property name="password" value="" /> <!-- give password here -->

</bean>

DataSourceTranasctionManager: its an implementation of PlatformTransactionManager for single JDBC datasources. It binds a JDBC connection from the specified data source to the currently executing thread potentially allowing for one thread connection per data source. Spring suggests to use DataSourceUtils.getConnection(DataSource) instead of standard DataSource.getConnection as it throws unchecked exceptions from org.springframework.dao hierarchy rather checked SQLExceptions.

JDBC Batch Operations

for improving the performance and to avoid multiple round trips to database most of the recent drivers supports JDBC Batch operations. In the below example first of all I am going to show you the code and then explain how this works..

Now lets try to understand whats happening here. First thing is to achieve batch processing by jdbctemplate you must implement BatchPreparedStatementSetter (dont get confused with PreparedStatementCretor/CallableStatementCreator) as second parameter to the update method. While implementing this interface you must give the batch size as well. setValues() method will be used to set the values for PreparedStatement. if you are reading data from a file then keep considerable batch size and last batch might not have all records so you can useInterruptibleBatchPreparedStatementSetter  allows to interrupt once batch input is exhausted. In the above example we passed array of Maps containing parameter values as well passed to the NamedParameterJdbcTemplae.update method. I have shown you 2 ways to do that.

Understanding SimpleJdbc* classes

So far in all above code we have been giving all database table column names and hand crafted queries to insert/update data. But with SimpleJdbcInsert we could use database meta data (since database driver can get this information from database) and reduced your lines of code. We create SimpleJdbcInsert using the datasource as shown in below example. we use methods on this object called "withTableName" to indicate the table name we are addressing. Remember, Once an simpleJdbcInsert instance is compiled, you should not re-config it again; i.e if you try to set to another table name etc.. you should create a new simpleJdbcInsert instace if you need to do so. (compiled means you have instantiated a simpleJdbcInsert instance and setup data source and table name already.). In the same lines we could use SimpleJdbcCall to call a stored procedure and return the values from it. In below example we are running get_author_info stored procedure to pull some information from database. The way you create SimpleJdbcCall is in line with

SimpleJdbcInsert. So the outcome is self explanatory.

Modelling JDBC Operations as Simple Java Objects

In this section we look at classes provided by spring framework to access database in more oo manner. In this model, you run queries and get the results as java business objects where in columns of database tables mapped to java bean properties.  But remember most of the time you could achieve the same effect by JdbcTemplate as well. Lets start by understanding basic classes SqlQuery, a reusable thread safe class encapsulates an SQL query. Mostly we use MappingSqlQuery - a concrete class of must implement mapRow() method of this abstract class. In this method we convert each row of the result set into a proper java object as shown in below example. If your query requires any parameters, you need to use declareParameter method passing a SqlParameter. Similarly, we will look into SqlUpdate which is to encapsulate an update operation. Below example explains the same:

This explains SqlUpdate, SqlExecute usage. I am not writing example to show how to call stored procedures using this technique since its very rare we use that in development. But there are couple of cases where we need to handle BLOB/CLOB data in the project so that case,we need to depend on spring's approach for storing/fetching clob/blob data. If you are storing images/binary files then they are BLOB (Binary large objects) and if you are storing long strings/text then we call it as CLOB (Character Large Object). The two special tools we need to consider are LobHandler/LobCreator for this purpose. They provide following methods for our convenience: Note: in my table creation script I am using MEDIUMBLOB for image (blob) since my input image is big high in size. Refer MySql Data types to see whether you want to use BLOB or MEDIUMBLOB to store your images.

(copied form spring ref documentation)

• BLOB
  
  • byte[] – getBlobAsBytes and setBlobAsBytes
  • InputStream – getBlobAsBinaryStream and setBlobAsBinaryStream

• CLOB
  
  • String – getClobAsString and setClobAsString
  • InputStream – getClobAsAsciiStream and setClobAsAsciiStream
  • Reader – getClobAsCharacterStream and setClobAsCharacterStream

The following example shows us how to make use of them. In the example we completely write code as we wrote for previous examples but using AbstractLobCreatingPreparedStatementCallback class and we implement setValues method to set the correct values for prepared statement using method of lobCreator. Similarly we use lobHandler to read the data from the tables.
Inputs we are going to store are like below (one text content and one frog image) 

 So source is (I am keeping text file again here for your reference) So outcome would be like below (Finally I am keeping image to show how it looks like in database tables, I am using Sequel Pro as MySql IDE):

Previous Next