Main Tutorials

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 Author

author image
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

Subscribe
Notify of
62 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Afriano
8 years ago

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 🙂

sarat
6 years ago

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

Luiz Gustavo
6 years ago

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 configuration.

Is that possible? I can not find an example of this.

Thanks

Luiz Gustavo
6 years ago
Reply to  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
8 years ago

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 ?

Santosh
2 years ago

WARNING: Exception encountered during context initialization – cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘customer’ defined in class path resource [beans.xml]: Error setting property values; nested exception is org.springframework.beans.NotWritablePropertyException: Invalid property ‘driverClassName’ of bean class [shoppingMall.Customer]: Bean property ‘driverClassName’ is not writable or has an invalid setter method. Does the parameter type of the setter match the return type of the getter?
Exception in thread “main” org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘customer’ defined in class path resource [beans.xml]: Error setting property values; nested exception is org.springframework.beans.NotWritablePropertyException: Invalid property ‘driverClassName’ of bean class [shoppingMall.Customer]: Bean property ‘driverClassName’ is not writable or has an invalid setter method. Does the parameter type of the setter match the return type of the getter?
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1726)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1434)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:601)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:944)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:144)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:85)
at shoppingMall.Main.main(Main.java:8)

I dont know now to rectify this error

Vishwas Atrey
4 years ago

I want some validation. Every time you get the connection using
con = dataSource.getConnection()
this means you are not establishing connection every time (not for the first time) you are just taking the connection from connection pool. Am I right?

Hetal Rachh
5 years ago

I am getting below error when I run the code. Can anyone help?

Exception in thread “main” java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at co.mkyong.customermodelimpl.CustomerModelImpl.insert(CustomerModelImpl.java:41)
at co.mkyong.ModelApps.CustomerDAOApp.main(CustomerDAOApp.java:15)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:155)
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:120)
at co.mkyong.customermodelimpl.CustomerModelImpl.insert(CustomerModelImpl.java:32)
… 1 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:343)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2132)
… 17 more
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)

Pablo
6 years ago

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

sarat
6 years ago
Reply to  Pablo

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
7 years ago

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

bhuvana
7 years ago

i am getting error when i imported to my eclipse

Petista Doutrinador
7 years ago

What’s “ps” ?

Michael Munsey
7 years ago

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

ishika
8 years ago

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
8 years ago

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
8 years ago

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
8 years ago

Tks mkyong

Hima Rayaprolu
9 years ago

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
9 years ago

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

Prateek Ashtikar
9 years ago

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
9 years ago

Excellent tutorial, many thanks from central Europe 😉

Akanksha
9 years ago

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
9 years ago

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
9 years ago
Reply to  Raj Kumar

add main class info in pom.xml

SkorpEN
9 years ago

To pom.xml

org.springframework
spring-jdbc
3.0.3.RELEASE

Younis Irshad
8 years ago
Reply to  SkorpEN

Thank you so much

SkorpEN
9 years ago

constructor is also required

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

Ahasan Siddiqui
6 years ago
Reply to  SkorpEN

Can be used…

Antonio Musarra
10 years ago

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
7 years ago

The link says 404 : Not found

Tien
10 years ago

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
8 years ago
Reply to  Tien

Run your IDE as Administrator

rakesh
10 years ago

How i run this program

Jaikrat
10 years ago
Reply to  rakesh

Just run the main.

swapnil
10 years ago

Nice once, code is perfect

Satish
10 years ago

thnx a lot..it helped me

jagruti
10 years ago

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