EDIT: In this post I’ll use Spring XML configuration, in new applications you should definitely use Spring JavaConfig configuration (via annotations and Java classes). For more info see reddit comment discussion here.
In this blog post we will create simple CRUD (Create Retrieve Update Delete) application using Spring and JDBC. Before we start we need to setup our database. I will assume that you already have Postgres running on your box.
Because we want to follow good programming practices we will create a
separate user in Postgres database dedicated only for our application.
Open pgAdmin and execute following SQL to create user
cruddb database with
crud as db owner:
Now it’s time to switch to
cruddb database and create
We should create this table logged as
curd will be denied access to the table.
If you don’t want to login as
curd you may create
table from superuser account and then grant permissions
I assume that you already have Maven installed because we are going to use it to create our CRUD application:
NOTE: Try to use
archetype:create instead of
archetype:generate if you
get an error using above command
Now we may load our application into our favorite IDE or just stick to command line.
Since we will be using Spring and Postgres JDBC driver we need to add them as a dependencies to our POM. We also want to use connection pooling (database connections are expensive to create so we want to reuse them whenever possible) so we will add a dependency on HikariCP library:
We should also change to using Java 8 so we may use lambdas and all cool stuff, we do this by adding to our POM:
And finally we will use
to strightforward running our application from
NOTE: You may find complete
pom.xml in attached source code
You may write
to rebuild CURD application and
to start it.
Add following code to the
We must also create
After these two steps we should have working Spring application. Right now no beans are registered in Spring container, this will change in the next section.
Setup Spring JDBC Data source
To enable Spring to access database
we must define data source in
DriverManagerDataSource class is provided by Spring as one
of several implementations of
DriverManagerDataSource returns a new connection to database
every time application asks for a connection.
Connection is created using specified JDBC driver.
SingleConnectionDataSource is another DataSource implementation
provided by Spring
that returns always the
same connection (having single connection
has serious implications in multithreaded
apps - when two threads want to
access database concurrently one of them must wait).
Now we may use
dataSource bean to insert row into
Writing simple DAO
It is always a good idea to isolate data access code into
a separate component, in our case we will create
AppDataDAO bean that
will be responsible for CURD operations on
To make passing and retrieving data via
AppDataDAO easier we will define
AppData class that will represent a single row from
It is a good practice to program to interface, so
instead of creating a single bean
AppDataDAO we will create
AppDataDAO interface and then provide an implementation:
Finally we may create
JDBCAppDataDAO class that will implement
A few things to notice: logic responsible for acquiring and releasing
a db connection was encapsulated in
Instead of getting connection straight from
DataSource we use
DataSourceUtils class to get and release connection. This become important
when we later start using transactions, because transactions are
attached to connections we will no longer be responsible for creating
and closing connection - a transaction manager will do that for
DataSourceUtils is asked for a new connection it first checks
if any transaction is running and if it is it returns connection used
by that transaction. If no transaction is active
a new connection is created.
The last thing that we must do is to register our bean in Spring container:
and use it to insert a row:
Opening a new connection to database is expensive operation. Instead of constantly opening and closing connections we should reuse them whenever possible. Because manually managing and resetting connections (before we can reuse connection we must reset it state - this will for example clear any pending errors on connection) is error-prone it is wise to use one of many connection pool libraries. Here we will use HikariCP library (CP stands for Connection Pool).
Let’s start by creating HikariCP configuration file
Then we must change our
dataSource bean definition to:
That’s all - now our application draws connections from connection pool!
Adding transactions to CRUD app
and exeucte the following code in
Of course running this program results in error and only one row is inserted to database:
In real life application we often want to perform either all of database operations or none of them. In our example this will mean that we either want to insert both rows to db or none of them should be inserted. Transactions can solve these problem for us. Transactions also offers some level of isolation between database operations performed by different users - but this topic is beyond this simple tutorial. For more information please check Wikipedia.
Transactions are usually handled at application service level, we will
follow this pattern. As usually we will start by creating
Then we may write implementation of
To define boundaries of transaction we use Spring provided
TransactionTemplate class. All database operations executed
in callback passed to
will be performed within transaction. In callback we have access
ts parameter that allows us to manually rollback current transaction.
When we throw runtime exception from callback, transaction will be
rolled back automatically. This behaviour doesn’t occur for
checked exceptions, if we want to rollback transaction in that case
we must catch exception manually and then invoke
Before we can run this code we need to register
TransactionManager in Spring container:
Finally we may add to the
Again running our program results in error but this time neither of rows
is inserted in
When we comment out call to
someOperation() both rows
are inserted - just as we wanted.
Annotation driven transactions
TransactionManager is cumbersome so Spring provides a better
alternative, we may declare transaction boundaries using annotations.
Let’s change our
CRUDAppDemoServiceImpl class to:
We may see that
TransactionTemplate is gone, and a new annotation
@Transactional means that we
want to start transaction when we call this method and commit it
when we return from it. As with
TransactionTemplate if method
RuntimeException transaction will be rolled back.
@Transactional we pass a single parameter
that means that we want Spring to use existing transaction if one
is currently active or start a new one otherwise.
If Spring will use already active transaction,
then transaction will be committed or rolled
back not at our method level but at method that started it.
plenty of options you may want to consult official documentation to
see them all.
@Transactional work we also need to enable it in Spring
configuration file. First we must add
tx namespace to Spring XML:
And then we must enable annotation driven transactions:
That’s it! Now we can use transactions without using