Spring + JDBC example

In this tutorial, we will extend last Maven + Spring hello world example by adding JDBC support, to use Spring + JDBC to insert a record into a customer table.

1. Customer table

In this example, we are using MySQL database.


CREATE TABLE `customer` (
  `CUST_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) NOT NULL,
  `AGE` int(10) unsigned NOT NULL,
  PRIMARY KEY (`CUST_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

2. Project Dependency

Add Spring and MySQL dependencies in Maven pom.xml file.

File : pom.xml


<project xmlns="http://maven.apache.org/POM/4.0.0" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
  http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.mkyong.common</groupId>
  <artifactId>SpringExample</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>SpringExample</name>
  <url>http://maven.apache.org</url>
  
  <dependencies>
    
        <!-- Spring framework -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring</artifactId>
		<version>2.5.6</version>
	</dependency>
    
        <!-- MySQL database driver -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.9</version>
	</dependency>
		
  </dependencies>
</project>

3. Customer model

Add a customer model to store customer’s data.


package com.mkyong.customer.model;

import java.sql.Timestamp;

public class Customer 
{
	int custId;
	String name;
	int age;
	//getter and setter methods
	
}

4. Data Access Object (DAO) pattern

Customer Dao interface.


package com.mkyong.customer.dao;

import com.mkyong.customer.model.Customer;

public interface CustomerDAO 
{
	public void insert(Customer customer);
	public Customer findByCustomerId(int custId);
}

Customer Dao implementation, use JDBC to issue a simple insert and select statement.


package com.mkyong.customer.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mkyong.customer.dao.CustomerDAO;
import com.mkyong.customer.model.Customer;

public class JdbcCustomerDAO implements CustomerDAO
{
	private DataSource dataSource;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public void insert(Customer customer){
		
		String sql = "INSERT INTO CUSTOMER " +
				"(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";
		Connection conn = null;
		
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, customer.getCustId());
			ps.setString(2, customer.getName());
			ps.setInt(3, customer.getAge());
			ps.executeUpdate();
			ps.close();
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
			
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}
	
	public Customer findByCustomerId(int custId){
		
		String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";
		
		Connection conn = null;
		
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, custId);
			Customer customer = null;
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				customer = new Customer(
					rs.getInt("CUST_ID"),
					rs.getString("NAME"), 
					rs.getInt("Age")
				);
			}
			rs.close();
			ps.close();
			return customer;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
				conn.close();
				} catch (SQLException e) {}
			}
		}
	}
}

5. Spring bean configuration

Create the Spring bean configuration file for customerDAO and datasource.
File : Spring-Customer.xml


<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<bean id="customerDAO" class="com.mkyong.customer.dao.impl.JdbcCustomerDAO">
		<property name="dataSource" ref="dataSource" />
	</bean>

</beans>

File : Spring-Datasource.xml


<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

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

		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/mkyongjava" />
		<property name="username" value="root" />
		<property name="password" value="password" />
	</bean>

</beans>

File : Spring-Module.xml


<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

	<import resource="database/Spring-Datasource.xml" />
	<import resource="customer/Spring-Customer.xml" />

</beans>

6. Review project structure

Full directory structure of this example.

7. Run it


package com.mkyong.common;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.mkyong.customer.dao.CustomerDAO;
import com.mkyong.customer.model.Customer;

public class App 
{
    public static void main( String[] args )
    {
    	ApplicationContext context = 
    		new ClassPathXmlApplicationContext("Spring-Module.xml");
    	 
        CustomerDAO customerDAO = (CustomerDAO) context.getBean("customerDAO");
        Customer customer = new Customer(1, "mkyong",28);
        customerDAO.insert(customer);
    	
        Customer customer1 = customerDAO.findByCustomerId(1);
        System.out.println(customer1);
        
    }
}

output


Customer [age=28, custId=1, name=mkyong]

Download Source Code

Download it– SpringJDBCExample.zip (10 KB)

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
46 Comment threads
18 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
50 Comment authors
Hetal RachhsaratAhasan SiddiquiPabloLuiz Gustavo Recent comment authors
newest oldest most voted
Afriano
Guest
Afriano

found some errors in these class, i modified them then run well.

1. App.java
instead :
Customer customer = new Customer(1, “mkyong”,28);

i change it to :
Customer customer = new Customer();
customer.setName(“beceng”);
customer.setAge(31);

2. JdbcCustomerDao.java
insert method ->

i modified insert query to :
String sql = “INSERT INTO CUSTOMER ” +
“(NAME, AGE) VALUES (?, ?)”;

and

//ps.setInt(1, customer.getCustId());
ps.setString(1, customer.getName());
ps.setInt(2, customer.getAge());

the reason is, cust_id in database is constraint / serial / auto increment, so (i guess) dont need to set that id.

well, thats my experience.
thanks mkyong :)

Luiz Gustavo
Guest
Luiz Gustavo

Hi folks! Is there any way to do the same, but instead of configuring datasource inside the application use an external datasource? For example, I’d like not to use Spring-Datasource.xml, instead get datasources from external datasource accessed through JNDI, for example, and have these datasources participate in the transaction. Why? Suppose I have many modules that use the same datasource configuration, let’s say more than 20 modules, and change these configurations for any different deployment would be tedious. In this case would be better to have a datasource configured in the container and have all the modules sharing the same… Read more »

Luiz Gustavo
Guest
Luiz Gustavo

Hi!

I found this post (http://www.journaldev.com/2597/spring-datasource-jndi-with-tomcat-example) with a possible solution. I’ll try it.

eskay
Guest
eskay

I am getting this error when I run this code

Caused by: org.springframework.beans.NotWritablePropertyException: Invalid property ‘dataSource’ of bean class [com.tutorialspoint.FundDaoImpl]: Bean property ‘dataSource’ is not writable or has an invalid setter method. Does the parameter type of the setter match the return type of the getter?

Did anyone have this issue ?

sarat
Guest
sarat

1. Yaa complete project very good to practice for new bies like me ….constructor is missing in “Customer class” please add people who are very new with java they face problem.

public Customer(int cust_id,String name,int age) {
this.cust_id=cust_id;
this.name = name;
this.age = age;
}

2. second thing is you need to create a database by yourself (this example assumed that already database existed) upon that they created “CUSTOMER” table. this is the place where you need to add you database name “Spring-Datasource.xml”>>”jdbc:mysql://localhost:3306/mkyongjava” in place of “mkyongjava”.

I hope this will help some one facing this problem

Pablo
Guest
Pablo

I have a little question, where I must create table? In main class or DAO?

sarat
Guest
sarat

thing is you need to create a database by yourself (this example assumed that already database existed) upon that they created “CUSTOMER” table. this is the place where you need to add you database name “Spring-Datasource.xml”>>”jdbc:mysql://localhost:3306/mkyongjava” in place of “mkyongjava”.

bhuvana
Guest
bhuvana

can you plz help i am getting error what after imported this project

bhuvana
Guest
bhuvana

i am getting error when i imported to my eclipse

Petista Doutrinador
Guest
Petista Doutrinador

What’s “ps” ?

Michael Munsey
Guest
Michael Munsey

Great tutorial! I generally return an int of whatever executeUpdate returns on an insert statement.

ishika
Guest
ishika

hello,
i want to write a code in spring where when username and password is checked from database (PostgreSQL) then only if correct then next page is opened else “incorrect username/password” message is shown.
thank you

guasisi
Guest
guasisi

I followed your code but when I try to run the main App, it keeps saying “selection doesn’t contain a main type”. Do you know why is that? Thank you!

Alain
Guest
Alain

I spent 9am-11pm trying to find out why this didn’t work. Answer: my driver was 5.1.9 (article written in 2010) as soon as I upgraded to 5.1.36 it worked.

AnOutlier
Guest
AnOutlier

Tks mkyong

Hima Rayaprolu
Guest
Hima Rayaprolu

A clear cut coding for basic learners like me.I faced a problem with SQL connection.MySQL was not started first.Is there any plugin that i need to add in pom.xml to start MYSQL.
Note.To build the code i started MySql manually

Jeca
Guest
Jeca

This was driving me crazy! I did everything ok except for: part. Thank you so much, very useful post!

Prateek Ashtikar
Guest
Prateek Ashtikar

Hello Jagruti,
First you need to understand spring IOC(Dependency Injection), because in this project “dataSource” bean is created in “Spring-Datasource” and this is getting refereed DAOImplementation class, thats why its giving null pointer exception. For this you need to write a code to first connection to DB (like simple JDBC). Hope this will be helpful. For any concerns please let me know.

regards

Józef Tokarski
Guest
Józef Tokarski

Excellent tutorial, many thanks from central Europe ;-)

Akanksha
Guest
Akanksha

isn’t it incorrect to create the object of class Customer as
Customer customer = new Customer(1, “mkyong”,28); ????
creating object with new keyword breaks the inversion of control feature of spring and make it tightly bound…..plzz reply soon

Raj Kumar
Guest
Raj Kumar

When I run this app, I get the following error:

MacBook-Air:SpringJDBCExample raj$ java -jar target/SpringExample-1.0-SNAPSHOT.jar

no main manifest attribute, in target/SpringExample-1.0-SNAPSHOT.jar

Any help will be appreciated.

Thanks,
Raj

kamal
Guest
kamal

add main class info in pom.xml

SkorpEN
Guest
SkorpEN

To pom.xml

org.springframework
spring-jdbc
3.0.3.RELEASE

Younis Irshad
Guest
Younis Irshad

Thank you so much

SkorpEN
Guest
SkorpEN

constructor is also required

public Customer(int custId, String name, int age) {
this.age = age;
this.custId = custId;
this.name = name;}

Ahasan Siddiqui
Guest
Ahasan Siddiqui

Can be used…

Antonio Musarra
Guest
Antonio Musarra

Good morning everyone.
I have made ??available on my GitHub repository a project (http://www.dontesta.it/blog/blog-2/repository-update-standalone-jdbc-template-spring-application/) of an application, which uses a standalone Spring JDBC Template and annotations.

Bye,
Antonio.

Pooja
Guest
Pooja

The link says 404 : Not found

Tien
Guest
Tien

I get the following Exception, although i verify that the username/password to my database are correct (by using tool gui) .
Exception in thread “main” java.lang.RuntimeException: java.sql.SQLException: Access denied for user ‘root’@’localhost’ (using password: YES)
Could you help me to fix it?
Thank you

Ghaffouli Mehdi
Guest
Ghaffouli Mehdi

Run your IDE as Administrator

rakesh
Guest
rakesh

How i run this program

Jaikrat
Guest
Jaikrat

Just run the main.

swapnil
Guest
swapnil

Nice once, code is perfect

Satish
Guest
Satish

thnx a lot..it helped me

jagruti
Guest
jagruti

Hi
I am using this tutorial to learn spring jdbc, I am not using maven.
Can you please refer to the uploaded proj and let me know why it is giving null pointer wile setting the connection.
conn = dataSource.getConnection();
I am new to spring
Please let me know where i can put my code.
your reply is appreciated, dont know which door to knock for help.
Thanks

mike
Guest
mike

Maybe because I’m using Spring 3, but your example does not work. setDataSource (in the DAO implementation) is never injected with a DataSource object :(

mike
Guest
mike

Its all working now! The issue was my fault. Your tutorial was not the problem. Thanks!

?? ?
Guest
?? ?

I’m having the exactly same problem how did you fix it?

You should have posted your fix :( XD

Ibnu Djalil
Guest
Ibnu Djalil

Change the code like this:
@Resource(name=”dataSource”)
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}

kunal
Guest
kunal

The post is nice but I don’t understand why you are explicitly inserting CUST_ID since you have declared it as autoincrement.

`CUST_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

egitman
Guest
egitman

You don’t need to, but for the purpose of the example, the full insert is given. Also to get the result set you have to search for an existing id, to be sure that that id exists you insert it. You can start a base incremental value for example from 1000, and the next incremental will be 1001 not 1. I am working on a Spring DAO framework, I will added here if i found something interesting.

egitman
Guest
egitman

Hi, Just for reference I would like to add some more Spring JDBC help. There is a JdbcTemplate class that takes care of your transactions. Build the jdbcTemplate in constructor and the use it in update and find methods The equivalent for insert is: public class JdbcCustomerDAO implements CustomerDAO { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { jdbcTemplate = new JdbcTemplate(dataSource); } public void insert(Customer customer) { String sql = "INSERT INTO CUSTOMER " + "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)"; jdbcTemplate.update(sql, new Object[]{ customer.getCustId(), customer.getName(), customer.getAge() }); } public Customer findByCustomerId(int custId) { String sql = "SELECT… Read more »

Guest
Guest
Guest

A clear cut coding for basic learners like me.I faced a problem with SQL connection.MySQL was not started first.Is there any plugin that i need to add in pom.xml to start MYSQL.
Note.To build the code i started MySql manually