Crudest CRUD using Spring

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.

Setup database

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 crud:

create user crud
  with password 'crud';

Next create cruddb database with crud as db owner:

create database cruddb
  with owner crud
       encoding 'utf-8';

Now it’s time to switch to cruddb database and create app_data table:

create table app_data (
  id serial primary key,
  index int not null,
  value text not null

We should create this table logged as crud user, otherwise 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 to curd user:

grant all on table app_data to crud

-- needed to autogenerate primary key
grant all on sequence app_data_id_seq to crud

Setup application

I assume that you already have Maven installed because we are going to use it to create our CRUD application:

$ mvn archetype:generate \
  -DgroupId="" \
  -DartifactId=crudapp \
  -Dversion=1.0 \
  -DarchetypeArtifactId=maven-archetype-quickstart \

$ tree crudapp/
|-- pom.xml
`-- src
    |-- main
    |   `-- java
    |       `-- io
    |           `-- mc
    |               `-- crudapp
    |                   `--
    `-- test
        `-- java
            `-- io
                `-- mc
                    `-- crudapp

11 directories, 3 files

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:

<!-- pom.xml -->



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 exec-maven-plugin to strightforward running our application from command line:


NOTE: You may find complete pom.xml in attached source code

You may write

$ mvn clean install

to rebuild CURD application and

$ mvn exec:java

to start it.

Setup Spring

Add following code to the main method:

public class Main {
    public static void main(String[] args) {
        ClassPathXmlApplicationContext appContext =
                new ClassPathXmlApplicationContext(new String[] {

        // We can use Spring context here


We must also create resource/spring/app-context.xml file:

<?xml version="1.0" encoding="UTF-8"?>
  <context:annotation-config />

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 app-context.xml:

<!-- Without pooling: -->
<bean id="dataSource"

  <property name="driverClassName" value="org.postgresql.Driver" />
  <property name="url" value="jdbc:postgresql://localhost:5432/cruddb" />
  <property name="username" value="crud" />
  <property name="password" value="crud" />

DriverManagerDataSource class is provided by Spring as one of several implementations of DataSource interface. 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 app_data table:

final String sql = "insert into app_data(index,value) values(?,?)";

DataSource ds = appContext.getBean(DataSource.class);
try (Connection conn = ds.getConnection();
    PreparedStatement stmt =
         conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

       stmt.setInt(1, 101);
       stmt.setString(2, "foo");


       // retrieve id of inserted row
       try(ResultSet rs = stmt.getGeneratedKeys()) {
           Long id = rs.getLong(1);
           System.out.println("id: " + id);
catch (SQLException ex) {

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 app_data table.

To make passing and retrieving data via AppDataDAO easier we will define AppData class that will represent a single row from app_data table:

public class AppData {
    private final int id;
    private final int index;
    private final String value;

    public AppData(int id, int index, String value) { = id;
        this.index = index;
        this.value = value;

    public int getId() { return id; }
    public int getIndex() { return index; }
    public String getValue() { return value; }

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:

public interface AppDataDAO {
    int insert(int index, String value);
    void update(int id, int newIndex, String newValue);
    boolean delete(int id);
    List<AppData> selectAll();

Finally we may create JDBCAppDataDAO class that will implement AppDataDAO interface:

public class JDBCAppDataDAO implements AppDataDAO {
    private DataSource ds;
    public JDBCAppDataDAO(DataSource ds) {
        this.ds = Objects.requireNonNull(ds);

    private interface ConnectionConsumer<T> {
        T consume(Connection conn) throws SQLException;

    private <T> T usingConnection(ConnectionConsumer<T> consumer) {
        Connection connection = DataSourceUtils.getConnection(ds);

        try {
            return consumer.consume(connection);
        catch (SQLException ex) {
            throw new RuntimeException(ex);
        finally {
            DataSourceUtils.releaseConnection(connection, ds);

    public int insert(int index, String value) {
        return usingConnection(conn -> {
            String sql = "insert into app_data(index,value) values(?,?)";
            try (PreparedStatement stmt =
                    conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
                stmt.setInt(1, index);
                stmt.setString(2, value);


                try(ResultSet rs = stmt.getGeneratedKeys()) {
                    if ( {
                        return rs.getInt(1);
                        throw new RuntimeException("no generated key!");

A few things to notice: logic responsible for acquiring and releasing a db connection was encapsulated in usingConnection method. 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 us. When 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:

 <bean name="AppDataDAO"
     class="" />

and use it to insert a row:

AppDataDAO appDataDAO = appContext.getBean(AppDataDAO.class);
appDataDAO.insert(102, "bar");

Using HikariCP

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 resources/db/


Then we must change our dataSource bean definition to:

<bean id="dataSource"
      <bean class="com.zaxxer.hikari.HikariConfig">
          <constructor-arg value="/db/" />

That’s all - now our application draws connections from connection pool!

Adding transactions to CRUD app

Let’s clear app_data table:

truncate table app_data;

and exeucte the following code in main:

AppDataDAO appDataDAO = appContext.getBean(AppDataDAO.class);
appDataDAO.insert(101, "foo");
appDataDAO.insert(102, "bar");

// given:
private static void someOperation() {
  throw new RuntimeException("uber error");

Of course running this program results in error and only one row is inserted to database:

select * from app_data 

select query result

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 CRUDAppDemoService interface:

public interface CRUDAppDemoService {
    void doDemo();

Then we may write implementation of CRUDAppDemoService:

public class CRUDAppDemoServiceImpl implements CRUDAppDemoService {
    private final TransactionTemplate txTemplate;
    private final AppDataDAO appDataDAO;

    public CRUDAppDemoServiceImpl(
      TransactionTemplate txTemplate, AppDataDAO appDataDAO) {
        this.txTemplate = Objects.requireNonNull(txTemplate);
        this.appDataDAO = Objects.requireNonNull(appDataDAO);

    public void doDemo() {
        txTemplate.execute(ts -> {
            appDataDAO.insert(101, "foo");
            appDataDAO.insert(102, "bar"); 

            return null;

    private static void someOperation() {
        throw new RuntimeException("uber error");

To define boundaries of transaction we use Spring provided TransactionTemplate class. All database operations executed in callback passed to execute method will be performed within transaction. In callback we have access to 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 ts.setRollbackOnly().

Before we can run this code we need to register TransactionTemplate and TransactionManager in Spring container:

<bean name="transactionManager"
  <property name="dataSource" ref="dataSource" />

<bean id="TransactionTemplate"
  <property name="transactionManager" ref="transactionManager" />

<bean name="CRUDAppDemoService"
    class="" />

Finally we may add to the main method:

CRUDAppDemoService service = appContext.getBean(CRUDAppDemoService.class);

Again running our program results in error but this time neither of rows is inserted in app_data table. When we comment out call to someOperation() both rows are inserted - just as we wanted.

Annotation driven transactions

Using TransactionManager is cumbersome so Spring provides a better alternative, we may declare transaction boundaries using annotations. Let’s change our CRUDAppDemoServiceImpl class to:

public class CRUDAppDemoServiceImpl implements CRUDAppDemoService {
    private final AppDataDAO appDataDAO;

    public CRUDAppDemoServiceImpl(AppDataDAO appDataDAO) {
        this.appDataDAO = Objects.requireNonNull(appDataDAO);

    @Transactional(propagation = Propagation.REQUIRED)
    public void doDemo() {
        appDataDAO.insert(101, "foo");
        appDataDAO.insert(102, "bar");

    private static void someOperation() {
        throw new RuntimeException("uber error");

We may see that TransactionTemplate is gone, and a new annotation appeared on doDemo() method. @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 trows RuntimeException transaction will be rolled back. To @Transactional we pass a single parameter Propagation.REQUIRED 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. @Transactional has plenty of options you may want to consult official documentation to see them all.

To make @Transactional work we also need to enable it in Spring configuration file. First we must add tx namespace to Spring XML:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns=""

And then we must enable annotation driven transactions:

<tx:annotation-driven transaction-manager="transactionManager"/>

That’s it! Now we can use transactions without using TransactionTemplate.

Source code