Spring JDBC tutorial with Example


SPRING JDBC
Spring uses template pattern ,which hides the low level details and provides you easy way with extensions hooks to work with jdbc.

There were many problems with old jdbc API as
  • Lot of code writing such as connection creation ,writing query ,using the result set ,close the connection.
  • Has to handle the exceptions and transactions.
But all things are not needed with Spring jdbc .So ,the moral is that you need not to worry about managing about the infrastructure ,database exceptions (as there is well defined API for this) just take care of data and objects.
So, yes its developer friendly.

Lets take a look on what things a developer has to do and what API will take care
Developer tasks:
  • Define connection parameters.
  • Specify the SQL statement.
  • Declare parameters and provide parameter values.
  • Do the work for each iteration.
Spring tasks:
  • Open the connection.
  • Prepare and execute the statement.
  • Take Care of exceptions as it converts the normal JDBC exceptions into Runtime exceptions which gives better understanding of error messages.
  • Handle transactions.
  • Close the connection, statement and resultset i.e. cleans the resources automatically.
There  are numerous options to query your database . You can use queryforList (),update(),ResultSetExtractor(),RowMapper.
RowMapper () returns you a list of objects .I have used update() in my coding example which is very easy to work with.

Spring framework provides following approaches for JDBC database access:

JdbcTemplate
  • Classic Spring JDBC approach
  • Most popular
NamedParameterJdbcTemplate
  • Wraps a Jdbc Template  to provide named parameters instead of the traditional JDBC "?" placeholders
  • Easy to use  when you have multiple parameters for an SQL statement
SimpleJdbcTemplate
  • Provides combination of both JDBC Template and     NamedParameterJdbcTemplate
SimpleJdbcInsert and SimpleJdbcCall
  • In this approach you only need to provide the name of the table or procedure and provide a map of parameters matching the column names.
  • Only works if the database provides adequate metadata. If the database doesn't provide this metadata, you will have to provide explicit configuration of the parameters

RDBMS Objects including MappingSqlQuery, SqlUpdate and    StoredProcedure
  • First you to need create reusable and thread-safe objects during        initialization of your data access layer
  • This approach is modeled after JDO Query wherein you define your query string, declare parameters, and compile the query. Once you do that, execute methods can be called multiple times with various parameter values passed in.


SPRING JDBC EXAMPLE:

In this example we are going to show how to work with Data Access Objects in Spring framework  with JDBC. It includes the basic CRUD operations.
Note: We have used MySQL database
Step 1: Create a Student table

Create table Student (rollno int, name varchar(100),marks int);
Step 2: Create a new project named SpringJdbc in Eclipse 



Step 3: Create three java classes
com.spring.jdbc.dto.Student.java
com.spring.jdbc.dao.StudentDao.java
com.spring.jdbc.Test.java
and an xml file applicationContext.xml as shown below;



Step 4: Add the required jars such as
spring-framework-3.2.5.RELEASE.jar
mysql-connector-5.1.0.jar
common-logging.jar
...

Step 5: Add properties which are columns in database Student table and its getter, setter methods to Student.java

package com.spring.jdbc.dto;

public class Student {
 private int rollno;
 private String name;
 private int marks;

 public int getRollno() {
  return rollno;
 }

 public void setRollno(int rollno) {
  this.rollno = rollno;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 public int getMarks() {
  return marks;
 }

 public void setMarks(int marks) {
  this.marks = marks;
 }

 public Student() {

 }

 public Student(int rollno, String name, int marks) {
  super();
  this.rollno = rollno;
  this.name = name;
  this.marks = marks;
 }

}

Step 6: StudentDao is a Data Access object class which uses jdbcTemplate class and its function update. It contains query which is to be applied on database in functions such as saveStudent(), DeleteStudent() and updateStudent().

package com.spring.jdbc.dao;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.jdbc.dto.Student;

public class StudentDao {
 private JdbcTemplate jdbcTemplate;

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

 public int saveStudent(Student s) {
    String query = "insert into student values ( " + s.getRollno() + ",'"+ s.getName() + "'," + s.getMarks() + ")";
    return jdbcTemplate.update(query);
 }

 public int updateStudent(Student s) {
    String query = "update student set name='" + s.getName() + "',marks='"+ s.getMarks() + "' where rollno='" + s.getRollno() + "' ";
    return jdbcTemplate.update(query);
 }

 public int deleteStudent(Student s) {
    String query = "delete from student where rollno='" + s.getRollno()
    + "' ";
    return jdbcTemplate.update(query);
 }

}

Step 7: applicationContext.xml – contains information about connection to database and bean to be used as data source.

<?xml version="1.0" encoding="UTF-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
 <bean id="ds"
  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver" />
  <property name="url" value="jdbc:mysql://localhost:3306/xe" />
  <property name="username" value="root" />
  <property name="password" value="root" />
 </bean>
 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="ds" />
 </bean>
 <bean id="sdao" class="com.spring.jdbc.dao.StudentDao">
  <property name="jdbcTemplate" ref="jdbcTemplate" />
 </bean>
</beans>

Step 8: Test.java - the class which has main method and need to be executed.

package com.spring.jdbc;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.spring.jdbc.dao.StudentDao;
import com.spring.jdbc.dto.Student;

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

  ApplicationContext ctx = new ClassPathXmlApplicationContext(
    "applicationContext.xml");
  StudentDao dao = (StudentDao) ctx.getBean("sdao");

  dao.saveStudent(new Student(101, "Amit", 90));
  System.out.println("Student created ");
  /*
   * dao.updateStudent(new Student(101,"Sumit",90));
   * System.out.println("Student updated");
   * 
   * Student s= new Student(); s.setRollno(101); dao.deleteStudent(s);
   * System.out.println("Student Deleted");
   */
 }
}

Step 9: Test your program
Create
       Update
       Delete







Reactions:

0 comments :

Post a Comment