There are various data access technologies to perform persistence operations in Java enterprise applications. JDBC is among the most popular one. This tutorial explains how to initialize a database and access data using Spring JDBC.

Spring offers two ways connect to a database. Data source or the DriverManager. The DriverManager is the legacy approach and data source is newer one. It is recommended to use the new DataSource facility to connect to databases and other resources.

DataSource facility has several advantages over DriverManager facility like DataSource increases portability, enables connection pooling and distributed transactions, the DriverManager does not allow such techniques.

Here in this example, we are using data source to communicate with MySQL database. We will create the Cars table with the following attributes.

CREATE TABLE `Cars` (
  `id` int(11) NOT NULL,
  `model` varchar(45) DEFAULT NULL,
  `price` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1. Configuring Bean

The corresponding Java bean class for the above Cars table will look like the following code:

public class Car {
	private int id;
	private String model;
	private double price;

	public Car() { }

	public Car(int id, String model, double price) {
		this.id = id;
		this.model = model;
		this.price = price;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getModel() {
		return model;
	}

	public void setModel(String model) {
		this.model = model;
	}

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}
}

Now, let us configure the Car bean declaration to beans.xml file for Spring IoC to locate, initialize and manage to Car bean instance.

<bean id="carBean" class="com.javatechig.spring.bean.Car" />

2. Configuring data source object

As discussed above, we will be using Spring data source approach to connect to MySQL database. For this you need to add the BasicDataSource bean configuration to your bean.xml file.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
	<property name="url" value="jdbc:mysql://localhost/SpringDemo"/>
	<property name="username" value="root" />
	<property name="password" value="admin" />
</bean>

Notice that in the above bean configuration the localhost indicated the location of MySQL database. Currently it is located on my development machine. Don’t forget to change the username and password of your own, if you configured differently while installing your MySQL server.

3. Understanding Spring JdbcTemplate

Spring JdbcTemplate is a class that takes care of all the boilerplate code required for creating a database connection and closing the resources. It makes our life a lot easier by saving the effort and development time. Add the following bean declaration to initialize the JdbcTemplate instance.

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource" />
</bean>

<bean id="carsDao" class="com.javatechig.spring.bean.CarsDao">
	<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

Notice that we are injecting the datasource bean instance defined above to initialize the JdbcTemplate bean.

4. Executing SQL statements

Spring JdbcTemplate exposes many helpful methods for performing CRUD operations on database. Following are most common methods that we use from JdbcTemplate.

  • execute(String sql) – Issue a single SQL execute, typically a DDL statement.
  • queryForList(String sql, Object[] args) – Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list.
  • update(String sql) – Issue a single SQL update operation (such as an insert, update or delete statement).

Now lets bring our JdbcTemplate to action. We will use the JdbcTemplate bean in our DAO class or any class that will communicate with our database. Let us create a DAO interface with the following methods:

public interface ICarsDao {
	int insertCar(Car car);
	void deleteCar(int id);
}

Now let us implement ICarDao interface and provide the logic to insert and delete a user form database.

public class CarsDao implements ICarsDao {
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public int insertCar(Car car) {
		String inserQuery = "insert into cars (id, model, price) values (?, ?, ?) ";
		Object[] params = new Object[] { car.getId(), car.getModel(), car.getPrice() };
		int[] types = new int[] { Types.INTEGER, Types.VARCHAR, Types.INTEGER };

		return jdbcTemplate.update(inserQuery, params, types);
	}

	public void deleteCar(int model) {
		String delQuery = "delete from cars where id = ?";
		int count = jdbcTemplate.update(delQuery, new Object[] { model });
		if (count != 0)
			System.out.println("Car deleted successfully.");
		else
			System.out.println("Car with given id as it doesn't exist");
	}
	
}

Now that we are ready with the DAO implementation, let us test those methods. Create a Java class TestMain.java and add the following code snippets

public class Main {
	public static void main(String[] args) {

		ApplicationContext appContext = new GenericXmlApplicationContext("beans.xml");
		ICarsDao dao = appContext.getBean("carsDao", ICarsDao.class);
		Car car = new Car(1, "Volkswgen", 300);
		dao.insertCar(car);

		System.out.println("User inserted with id= " + 2);
		dao.deleteCar(3);
	}
}

If all your configurations are fine, you should be able to insert and delete data from Cars table.

This article is about: JDBC, Spring Core,

Nilanchala

A blogger, a bit of tech freak and a software developer. He is a thought leader in the fusion of design and mobile technologies. He is the author of Xamarin Mobile Application Development for Android Book (goo.gl/qUZ0XV3), DZone MVB and founder of stacktips.com.

Join The Discussion

Please note: We reserve the right to delete comments that contains snarky remarks, offensive or off-topic. To know more read our comments policy.
  • Weles Siqueira

    Hi Nilanchala! Greate post! In my case i would like to use the applicationContext in the webapplication. In this case i don´t have a static main method, where could i put this line: ApplicationContext appContext = new GenericXmlApplicationContext(“beans.xml”); ?

    Thanks for your attention!

  • Weles Siqueira

    Hi Nilanchala! Greate post! In my case i would like to use the applicationContext in the webapplication. In this case i don´t have a static main method, where could i put this line: ApplicationContext appContext = new GenericXmlApplicationContext(“beans.xml”); ?

    Thanks for your attention!