Of Stack

The JDBC API is used to manipulate the database in Java's Spring framework program

  • 2020-04-01 04:28:39
  • OfStack

While working with the database using plain old JDBC, it becomes tedious to write unnecessary code to handle exceptions, open and close database connections, etc., but Spring's JDBC framework requires all the low-level details from opening connections, preparing and executing SQL statements, procedural exceptions, handling transactions, and finally closing connections.

So all you have to do is define the connection parameters, specify the SQL statement to execute, and do the necessary work to get the data from the database at each iteration.

Spring JDBC provides methods and corresponding different classes to interact with the database. I'm going to take the classic and most popular approach, using the JdbcTemplateclass framework. This is the communication and exception handling center framework class that manages all databases.

JdbcTemplate class
The JdbcTemplate class performs SQL queries, updates statements, and stored procedure calls, iterating over the result set and extracting the returned parameter values. It also catches JDBC exceptions and converts them to generic, more informative, except for the hierarchy defined in the org.springframework.dao package.

The instance of the JdbcTemplate class is a configured thread. So, you can configure an instance of a JdbcTemplate and safely inject this Shared reference to multiple daos.

When using the JdbcTemplate class, it is common practice to configure a DataSource in the Spring configuration file and then inject the Shared DataSource into the DAO class by dependency injection, or to create a JdbcTemplate in the setter DataSource.

Configure data source
Let's create the student for the database test database table. Assuming that you use a MySQL database, if you use another database, you can change your DDL and SQL queries accordingly. 


CREATE TABLE Student(
  ID  INT NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20) NOT NULL,
  AGE INT NOT NULL,
  PRIMARY KEY (ID)
);

Now we need to provide a data source to the JdbcTemplate class, so it can be configured to get database access. You can configure the data source's XML file with a snippet of code, as shown in the following figure: 


<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/TEST"/>
  <property name="username" value="root"/>
  <property name="password" value="password"/>
</bean>

Data access object (DAO)
The DAO represents a data access object that is typically used for database interactions. Daos exist to provide the ability to read and write data to a database, and they should expose this functionality through the rest of the application through the interfaces that access them.

Support for data access objects (daos) in Spring makes it easy to implement data access technologies in a consistent manner with those of JDBC, Hibernate, JPA, and JDO.

Execute SQL statement
Let's look at how to perform CRUD (create, read, update, and delete) operations using tables in the SQL and JdbcTemplate object database.

Query for an integer: 


String SQL = "select count(*) from Student";
int rowCount = jdbcTemplateObject.queryForInt( SQL );

Query long integer: 


String SQL = "select count(*) from Student";
long rowCount = jdbcTemplateObject.queryForLong( SQL );

Simple queries using bound variables: 


String SQL = "select age from Student where id = ?";
int age = jdbcTemplateObject.queryForInt(SQL, new Object[]{10});

In the query string: 


String SQL = "select name from Student where id = ?";
String name = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, String.class);

Query and return an object: 


String SQL = "select * from Student where id = ?";
Student student = jdbcTemplateObject.queryForObject(SQL, 
         new Object[]{10}, new StudentMapper());

public class StudentMapper implements RowMapper<Student> {
  public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
   Student student = new Student();
   student.setID(rs.getInt("id"));
   student.setName(rs.getString("name"));
   student.setAge(rs.getInt("age"));
   return student;
  }
}

Query and return multiple objects: 


String SQL = "select * from Student";
List<Student> students = jdbcTemplateObject.query(SQL,
             new StudentMapper());

public class StudentMapper implements RowMapper<Student> {
  public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
   Student student = new Student();
   student.setID(rs.getInt("id"));
   student.setName(rs.getString("name"));
   student.setAge(rs.getInt("age"));
   return student;
  }
}

Insert a row into the table: 


String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 11} );
Update a row to the table: 

String SQL = "update Student set name = ? where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 10} );

Delete rows from the table: 


String SQL = "delete Student where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{20} );

Execute the DDL statement
You can use execute(...) The JdbcTemplate of the method to execute any SQL statement or DDL statement. Here is an example of creating a table using the CREATE statement: 


String SQL = "CREATE TABLE Student( " +
  "ID  INT NOT NULL AUTO_INCREMENT, " +
  "NAME VARCHAR(20) NOT NULL, " +
  "AGE INT NOT NULL, " +
  "PRIMARY KEY (ID));"

jdbcTemplateObject.execute( SQL );

SQL stored procedure
SimpleJdbcCall's class can be used to call stored procedures with IN and OUT parameters. You can use this approach while working with any favorite Apache Derby, DB2, MySQL and Microsoft SQL servers, Oracle and Sybase RDBMS.

Second, consider the following MySQL stored procedure that requires a student id and a return on the student's name and age with the OUT parameter. So let's create the stored procedure in the test database using the MySQL command prompt: 


DELIMITER $$

DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id INTEGER,
OUT out_name VARCHAR(20),
OUT out_age INTEGER)
BEGIN
  SELECT name, age
  INTO out_name, out_age
  FROM Student where id = in_id;
END $$

DELIMITER ;

Now that we have written the Spring JDBC application, we will perform the simple create and read operations of our student desk.
To create a Spring application:
The following is the content of the data access object interface file studentdao.java: 


package com.yiibai;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
  
  public void setDataSource(DataSource ds);
  
  public void create(String name, Integer age);
  
  public Student getStudent(Integer id);
  
  public List<Student> listStudents();

}

Here is the content of the student.java file: 


package com.yiibai;

public class Student {
  private Integer age;
  private String name;
  private Integer id;

  public void setAge(Integer age) {
   this.age = age;
  }
  public Integer getAge() {
   return age;
  }

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

  public void setId(Integer id) {
   this.id = id;
  }
  public Integer getId() {
   return id;
  }
}

Here is the studentmapper.java file: 


package com.yiibai;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<Student> {
  public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
   Student student = new Student();
   student.setId(rs.getInt("id"));
   student.setName(rs.getString("name"));
   student.setAge(rs.getInt("age"));
   return student;
  }
}

The following is the implementation class file studentjdbctemplate.java definition DAO interface StudentDAO: 


package com.yiibai;

import java.util.Map;

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class StudentJDBCTemplate implements StudentDAO {
  private DataSource dataSource;
  private SimpleJdbcCall jdbcCall;

  public void setDataSource(DataSource dataSource) {
   this.dataSource = dataSource;
   this.jdbcCall = new SimpleJdbcCall(dataSource).
            withProcedureName("getRecord");
  }

  public void create(String name, Integer age) {
   JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
   String SQL = "insert into Student (name, age) values (?, ?)";

   jdbcTemplateObject.update( SQL, name, age);
   System.out.println("Created Record Name = " + name + " Age = " + age);
   return;
  }

  public Student getStudent(Integer id) {
   SqlParameterSource in = new MapSqlParameterSource().
               addValue("in_id", id);
   Map<String, Object> out = jdbcCall.execute(in);

   Student student = new Student();
   student.setId(id);
   student.setName((String) out.get("out_name"));
   student.setAge((Integer) out.get("out_age"));

   return student;
  }

  public List<Student> listStudents() {
   String SQL = "select * from Student";
   
   List <Student> students = jdbcTemplateObject.query(SQL, 
                   new StudentMapper());
   return students;
  }

}

A few words about the program above: when you write the execution code for the call, you need to create an SqlParameterSource that contains the IN parameter. It is important to match the name of the input value with the parameter name declared in the stored procedure. The execute method takes the incoming parameter and returns a map containing the name of the input parameter specified by any column in the stored procedure. Now let's modify the main application file mainapp.java, which is as follows: 


package com.yiibai;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.yiibai.StudentJDBCTemplate;

public class MainApp {
  public static void main(String[] args) {
   ApplicationContext context = 
       new ClassPathXmlApplicationContext("Beans.xml");

   StudentJDBCTemplate studentJDBCTemplate = 
   (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
   
   System.out.println("------Records Creation--------" );
   studentJDBCTemplate.create("Zara", 11);
   studentJDBCTemplate.create("Nuha", 2);
   studentJDBCTemplate.create("Ayan", 15);

   System.out.println("------Listing Multiple Records--------" );
   List<Student> students = studentJDBCTemplate.listStudents();
   for (Student record : students) {
     System.out.print("ID : " + record.getId() );
     System.out.print(", Name : " + record.getName() );
     System.out.println(", Age : " + record.getAge());
   }

   System.out.println("----Listing Record with ID = 2 -----" );
   Student student = studentJDBCTemplate.getStudent(2);
   System.out.print("ID : " + student.getId() );
   System.out.print(", Name : " + student.getName() );
   System.out.println(", Age : " + student.getAge());
  
  }
}

The following is the configuration file beans.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" 
  xsi:schemaLocation="http://www.springframework.org/schema/beans
  http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

  <!-- Initialization for data source -->
  <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/TEST"/>
   <property name="username" value="root"/>
   <property name="password" value="password"/>
  </bean>

  <!-- Definition for studentJDBCTemplate bean -->
  <bean id="studentJDBCTemplate" 
   class="com.yiibai.StudentJDBCTemplate">
   <property name="dataSource" ref="dataSource" />  
  </bean>
   
</beans>

Once the source code and bean configuration files are created, let's run the application. If all goes well, this will print the following information: 


------Records Creation--------
Created Record Name = Zara Age = 11
Created Record Name = Nuha Age = 2
Created Record Name = Ayan Age = 15
------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15
----Listing Record with ID = 2 -----
ID : 2, Name : Nuha, Age : 2

Related articles: