Spring + JDBC Example

in this tutorial, we'll 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 is using MySQL database.

CREATE TABLE ' customer ' (  ' cust_id ' INT (TEN) UNSIGNED not NULL auto_increment,  ' NAME ' VARCHAR (+) NOT NULL,  ' Age ' INT (Ten) 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>  &L t;! --Spring Framework--><dependency><groupid>org.springframework</groupid><artifactid> spring</artifactid><version>2.5.6</version></dependency>  <!--MySQL database Driv ER--><dependency><groupid>mysql</groupid><artifactid>mysql-connector-java</ artifactid><version>5.1.9</version></dependency>  &LT;/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);p ublic 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);p s.setint (1, Customer.getcustid ());p s.setstring (2, Customer.getname ());p S.setint (3, Customer.getage ());p s.executeupdate ();p s.close ();  } catch (SQLException e) { throw new RuntimeException (e);  } finally {if (conn! = null) {try {conn.close ();} catch (SQLException e) {}}}} pu Blic Customer FINDBYCUstomerid (int custId) { string sql = "SELECT * from CUSTOMER WHERE cust_id =?";  connection conn = null; try {conn = Datasource.getconnection (); PreparedStatement PS = conn.preparestatement (sql);p s.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 ();p s.close (); return customer;} catch (SQLException e) {throw new RuntimeException (e);} finally {if (conn! = null) {try {conn.close ()} catch (Sqlexceptio n 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/beanshttp:// 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/beanshttp:// 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/beanshttp:// 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 CLASSPATHXM Lapplicationcontext ("Spring-module.xml");        Customerdao Customerdao = (Customerdao) context.getbean ("Customerdao");        Customer customer = new Customer (1, "Mkyong", +);        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 (Ten KB)

Spring + JDBC Example