JavaEE simple example – Spring’s database programming

A brief introduction:

In the module that we introduced the Spring architecture before, we have introduced the jdbc module of Spring. For Spring, it can integrate the MyBatis framework to complete the operation of SQL statements, so we are more concerned about the transaction management of Spring in this module of JDBC. , rather than the operation of the SQL statement, so for the operation method of the SQL statement, we simply introduce how to use it. First of all, we need to introduce the jdbcTemplate class, which is the template class. We need to use the tag to create this template class in Spring’s Bean management XML configuration file, and then there is a property in this class that is DataSource, which requires us to use dependencies. The way of injection is to enter the cashing of another class, so we also need to use a tag to create this DataSource class. Finally, we declare a jdbcTemplate attribute in the database operation class we created. The type is also jdbcTemplate and then we create it before The jdbcTemplate property is injected into the properties of our custom class using dependency injection. That is to say, we need a process like this: DataSource -> jdbcTemplate -> custom database operation class and this delivery process is through dependency injection way, like nesting dolls.

How to use:

In official terms, when configuring JdbcTemplate, you need to inject dataSource into JdbcTemplate, and other classes need to use JdbcTemplate beans, and you also need to inject JdbcTemplate into the bean (usually injected into the Dao class, and in the Dao class. Database related operations.

Let’s look at the XML configuration file directly to understand it better:

<?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:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- Create a data source object -->
    <bean id="dateSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/jdbc"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>
<!-- Create template class object -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dateSource"/>
    </bean>
<!-- Create an object of a custom database operation class, in the end we only need or this object -->
    <bean id="ManipulatingDatabase" class="Semester_4.SpringJDBC.jdbcTemplate.ManipulatingDatabase">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
    <bean id="student" class="Semester_4.SpringJDBC.jdbcTemplate.student"/>
</beans>

Then this jdbcTemplate class provides a lot of methods for updating and querying the database, and you can use these methods to operate the database.

execute() method: SQL statement used to execute DDL, such as creating a database, creating a data table

update() method: SQL statements used to update data, including additions and deletions, can be executed with this method

query() method: SQL statement used to execute query data.

Regarding the processing of the query result set returned by the method, the RowMapper interface is used to map the result set to a class, and which class is specifically mapped to is controlled by the generic type of RowMapper. By creating the implementation class of the RowMapper interface, the object of the BeanPropertyRowMapper class returns the object of the result set processing interface, and then receives the return value from the query method to map the query result to the class, thereby operating the object of the class to obtain the query result set.

Let’s look at the implementation of the code directly, and we will better understand the theory we said before

Code implementation:

The XML configuration file has been shown before, so let’s look directly at the class that gets the object:

package Semester_4.SpringJDBC.jdbcTemplate;

import org.springframework.jdbc.core.JdbcTemplate;

public class ManipulatingDatabase {
    private JdbcTemplate jdbcTemplate;

    @Override
    public String toString() {
        return "ManipulatingDatabase{" +
                "jdbcTemplate=" + jdbcTemplate +
                '}';
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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

    public ManipulatingDatabase(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public ManipulatingDatabase() {
    }
}

Then we define a class to process the query results:

package Semester_4.SpringJDBC.jdbcTemplate;

public class student {
    private int id;
    private String name;
    private String password;
    private int id_card;
    private int order_id;

    @Override
    public String toString() {
        return "student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", id_card=" + id_card +
                ", order_id=" + order_id +
                '}';
    }

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getId_card() {
        return id_card;
    }

    public void setId_card(int id_card) {
        this.id_card = id_card;
    }

    public int getOrder_id() {
        return order_id;
    }

    public void setOrder_id(int order_id) {
        this. order_id = order_id;
    }

    public student(int id, String name, String password, int id_card, int order_id) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.id_card = id_card;
        this. order_id = order_id;
    }

    public student() {
    }
}

Finally we continue testing in the test class:

package Semester_4.SpringJDBC.jdbcTemplate;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class text {
    public static void main(String[] args) {
// create IoC container
        ApplicationContext ac = new ClassPathXmlApplicationContext("JdbcTemplate.xml");
// Get the object of the custom database operation class from the container, and execute the database operation method through the jdbcTemplate attribute of this class
// The value of the attribute in this class comes from the value obtained after we configured the three beans in the configuration file and performed dependency injection
        ManipulatingDatabase md = (ManipulatingDatabase) ac. getBean("ManipulatingDatabase");
        JdbcTemplate jdbcTemplate = md. getJdbcTemplate();
// Call the method of executing SQL to create a database. This method has no return value. If no error is reported, it means that the creation has been successful
        jdbcTemplate.execute("create table if not exists user_info(id int)");
// Call the method of updating data, this method can perform the operation of adding, deleting and modifying data
        jdbcTemplate.update("insert into user_info values (1)");
// Get the mapping class of the result set, the generic type of this class is the Java class mapped by our result set
// Since this class will automatically find the mapping of the columns and attributes of the data table, we need to follow certain rules when creating a class that encapsulates the result set
// The main thing is that the type and attribute name of the attribute in our class should be consistent with the column name and attribute of the data table
// Secondly, the creation of the BeanPropertyRowMapper class requires a parameter, which is the class object of our result set encapsulation class
        RowMapper<student> rm = new BeanPropertyRowMapper<student>(student.class);
        student s = new student();
        s.setId(1);
// Then call the query() method of jdbcTemplate to execute the query statement, the parameter of this method is our
// SQL statements and our result set processing class, the return value of the method is a List collection, the elements of this collection are students, and each student encapsulates a row of query results
// After traversing the List collection, we can get our query results
        List<student> query = jdbcTemplate. query("select * from student", rm);
        for(student s2 : query){
            System.out.println(s2.toString());
        }
    }
}

Operation result:

The operation is relatively slow, because the underlying encapsulation is relatively poor, but it doesn’t matter, we will use MyBatis when performing persistent operations on the database in the following links, so we only need to understand this piece of knowledge

Points to note:

In jdbcTemplate, we mainly know how to use the RowMapper result set processing class to process the result set we obtained. In the execution of our SQL statement, we can use maybatis to help us execute it. In the JDBC module, the most The important content is still the management aspect of the transaction.