Spring - JDBC Example

To run the below example you need below jar files in your class path

• derbyclient.jar
• spring-dao.jar
• spring-jdbc.jar

Customer Table
CREATE TABLE `customer` (
  `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;


Customer.java
Add a customer model to store the customer’s data.

package com;
public class Customer {
private int custId;
private String name;
private int age;
public Customer(int custId, String name, int age) {
super();
this.custId = custId;
this.name = name;
this.age = age;
}
/**
* @return the custId
*/
public int getCustId() {
return custId;
}
/**
* @param custId the custId to set
*/
public void setCustId(int custId) {
this.custId = custId;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the age
*/
public int getAge() {
return age;
}
/**
* @param age the age to set
*/
public void setAge(int age) {
this.age = age;
}
}

ICustomerDAO.java

Add dao layer for the Customer.

package com;

public interface ICustomerDAO {

public void insert(Customer customer);

public Customer findByCustomerId(int custId);

}

JdbcCustomerDAOImpl.java

DAO JDBC implementation with a simple insert and select statement.

package com;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.DataSource;

public class JdbcCustomerDAOImpl implements ICustomerDAO {

private DataSource dataSource;

 public void setDataSource(DataSource dataSource) {

this.dataSource = dataSource;

}

public void insert(Customer customer){

  String sql = "INSERT INTO CUSTOMER (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 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("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) {}

}

}

}

}

applicationContext.xml

Create the Spring bean configuration file for customerDAO and datasource.

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

<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.0.xsd">

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

  <property name="location">

<value&gt;ApplicationProperties.properties</value>

</property>

</bean>

<bean id="customerDAO" class="com.JdbcCustomerDAOImpl">

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

</bean>

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

<property name="driverClassName" value="${jdbc.driverClassName}" />

<property name="url" value="${jdbc.url}" />

<property name="username" value="${jdbc.username}" />

<property name="password" value="${jdbc.password}" />

</bean>

</beans>

ApplicationProperties.properties

jdbc.driverClassName=org.apache.derby.jdbc.ClientDriver

jdbc.url=jdbc:derby://localhost:1527/myeclipse

jdbc.username=myblog

jdbc.password=myblog

Test.java

package com;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Test {

/**

* @param args

*/

public static void main(String[] args) {

// TODO Auto-generated method stub

ApplicationContext context = new ClassPathXmlApplicationContext("/applicationContext.xml");

         ICustomerDAO customerDAO = (ICustomerDAO) context.getBean("customerDAO");

        Customer customer = new Customer(1, "Vardhan",27);

        customerDAO.insert(customer);

        Customer customer1 = customerDAO.findByCustomerId(2);

        System.out.println(" ::: Customer Record From DataBase ::: ");

        System.out.println(" Customer ID ::: "+customer1.getCustId());

        System.out.println(" Customer Name ::: "+customer1.getName());

        System.out.println(" Customer Age ::: "+customer1.getAge());

}

}

OutPut

 ::: Customer Record From DataBase ::: 

 Customer ID ::: 1

 Customer Name ::: Vardhan

 Customer Age ::: 27