Spring
Spring JDBC
Spring is a popular Java-based framework which is used for developing enterprise-level Applications. It provides Spring JDBC which is a simplified way to interact with RDBMS(Relational Database Management System) using JDBC(Java Database Connectivity) like MySQl, ORACLE etc..
JDBC is a standard Java API which allows Java applications to interact with RDBMS & provides classes & methods to establish connection with databases, executing SQL Queries along with managing database resources. The reason why Spring JDBC took over Simple JDBC is its ability to remove lots of boilerplate code, automatic open & close connection, exception handling & managing resources more wisely & effectively. It provides a high-level of abstraction over verbose JDBC operation.
Lets see few benefit of using Spring JDBC
1: Make Database Interaction Easy: By providing ‘JdbcTemplate’, It reduces a lot of boilerplate code to interact with the database. JdbcTemplate is able to handle common db operations like managing connection, exception handling & reporting more wisely.
2: Exception Handling: The beauty of Spring JDBC is the ability to handle most common exceptions that occur during the development. It helps developers to focus on business logic instead of dealing with common exceptions.
3: Resource Management: Spring JDBC can manage DB resources like connection, statement in a more efficient way than normal JDBC. it closes the open resources automatically after they are in no use & reduces the chances of resource leak.
4: Connection Management: By providing connection pooling that helps to improve performance by ability to reuse the available connections rather than creating new connection for each DB operation
5: Reduce boilerplate code : It reduce the repetitive code for DB operation & helps developers to only focus on business logic
Lets see an example of CRUD operation using Spring JDBC
JdbcTemplate class
The JdbcTemplate class in Java Database Connectivity (JDBC) in Spring JDBC is a powerful tool for easily managing relational databases. Compared to the raw JDBC API, it provides greater abstraction, which has a variety of benefits and improves the performance and performance of database applications.
ons, statements, and results, making it easier to interact with relational databases in a Spring application.
Here are most used methods in JdbcTemplate class.
1:public int update(String query): This method is used to insert, upodate & delete record.
2:public int update(String query,Object… args): This method is used to insert, update and delete records using PreparedStatement by using given arguments.
3: public void execute(String query): This mehod is used to execute DDL Query
4:public T execute(String sql, PreparedStatementCallback action): This methods executes the query by using PreparedStatement callback.
5:public T query(String sql, ResultSetExtractor rse)– This method is used to fetch records using ResultSetExtractor.
6:public List query(String sql, RowMapper rse)– This method is used to fetch records using RowMapper.
Lets see a CRUD Example using JdbcTemplate class. Here we use MySql database. & Eclipse IDE.
Create a Maven project in eclipse and put the below code in
Please see the folder structure & create the packages, xml file & java class accordingly.
Create a data base springjdbc in mysql database
Create a Student table in springjdbc database as give below:-
Create pom.xml and dependencies as given below:👇
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.spring.jdbc</groupId>
<artifactId>springjdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>springjdbc</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.30</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.13.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.2.13.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.13.RELEASE</version>
</dependency>
</dependencies>
</project>
Student.java 👇
package com.spring.jdbctemplate.entity;
public class Student {
private int id;
private String name;
private String city;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(int id, String name, String city) {
super();
this.id = id;
this.name = name;
this.city = city;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", city=" + city + "]";
}
}
Create a configuration file jdbcconfig.xml 👇
<?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/springjdbc" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<bean name="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean>
<bean name="studendImplDao" class="com.spring.jdbctemplate.dao.StudentDaoImplement">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
</beans>StudentDao.java 👇
package com.spring.jdbctemplate.dao;
import java.util.List;
import com.spring.jdbctemplate.entity.Student;
public interface StudentDao {
public int insert(Student student);
public int change(Student student);
public int delete(int studentId);
public Student getStudent(int id);
public List<Student> getAllStudentsRowMapper();
}
StudentDaoImplement.java
package com.spring.jdbctemplate.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.spring.jdbctemplate.entity.Student;
public class StudentDaoImplement implements StudentDao {
private JdbcTemplate jdbcTemplate;
// Insert
@Override
public int insert(Student student) {
// Insert
String query = "insert into student values(?,?,?)";
int rowAffected = this.jdbcTemplate.update(query, student.getId(), student.getName(), student.getCity());
return rowAffected;
}
// Update
@Override
public int change(Student student) {
// Update
String query = "update student set name=?,city=? where id=?";
int r = this.jdbcTemplate.update(query, student.getName(), student.getCity(), student.getId());
return r;
}
// Delete
@Override
public int delete(int studentId) {
String query = "delete from student where id=?";
int r = this.jdbcTemplate.update(query, studentId);
return r;
}
// Get single data from the database
@Override
public Student getStudent(int id) {
// TODO Auto-generated method stub
String query = "select * from student where id = ?";
RowMapper<Student> rowMapper=new RowMapperImpl();
Student student = this.jdbcTemplate.queryForObject(query,rowMapper, id);
return student;
}
// Get the all student from the DB
public List<Student> getAllStudentsRowMapper() {
return jdbcTemplate.query("select * from student", new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int rownumber) throws SQLException {
Student e = new Student();
e.setId(rs.getInt(1));
e.setName(rs.getString(2));
e.setCity(rs.getString(3));
return e;
}
});
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
RowMapperImpl.java
package com.spring.jdbctemplate.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.spring.jdbctemplate.entity.Student;
public class RowMapperImpl implements RowMapper<Student>{
@Override
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
// TODO Auto-generated method stub
Student student=new Student();
student.setId(rs.getInt(1));
student.setName(rs.getString(2));
student.setCity(rs.getString(3));
return student;
}
}
App.java (main java file) 👇
package com.spring.jdbctemplate;
import java.util.List;
import java.util.Scanner;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.spring.jdbctemplate.dao.StudentDao;
import com.spring.jdbctemplate.entity.Student;
public class App {
public static void main(String[] args) {
// TODO Auto-generated method stub
ApplicationContext context=new ClassPathXmlApplicationContext("com/spring/jdbctemplate/jdbcconfig.xml");
StudentDao studentDao = context.getBean("studendImplDao",StudentDao.class);
// insert
// Student student=new Student();
// student.setId(6);
// student.setName("Vinny");
// student.setCity("Assam");
// int insert = studentDao.insert(student);
// Update recored
// int change = studentDao.change(student);
// System.out.println(change);
// get single row
Student student = studentDao.getStudent(4);
System.out.println(student);
// get all student
// List<Student> list = studentDao.getAllStudentsRowMapper();;
// System.out.println(list);
// for(Student e:list)
// System.out.println(e);
//
//
// Scanner sc=new Scanner(System.in);
//
// System.out.println("Enter the Student ID you want to Delete:");
// int id=sc.nextInt();
// int del=studentDao.delete(id);
// System.out.println(del +"row deleted");
//
//
// int result=studentDao.delete(id);
// System.out.println("No of Record deleted"+result);
// sc.close();
//
}
}
Hope this will helpful to get the idea how Jdbctemplate works in spring framework.