Spring learning notes spring and database programming

This article introduces the use of database in Spring and the use of JdbcTemplate.

Traditional JDBC review

We must be familiar with JDBC. At the beginning of learning, we wrote a lot of duplicate template code:

public Student getOne(int id) {

    String sql = "select id,name from student where id = ?";
    Student student = null;
    // Declare JDBC variables
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        // Register driver
        Class.forName("com.myql.jdbc.Driver");
        // Get connection
        con = DriverManager.getConnection("jdbc://mysql://localhost:" +
                "3306/student", "root", "root");
        // Precompiled SQL
        ps = con.prepareStatement(sql);
        // Set parameters
        ps.setInt(1, id);
        // Execute SQL
        rs = ps.executeQuery();
        // POJO returned from assembly result set
        if (rs.next()) {
            student = new Student();
            student.setId(rs.getInt(1));
            student.setName(rs.getString(1));
        }
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        // Close database connection resource
        try {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (con != null && con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return student;
}

Now it's big just looking at it, and I've written it completely

At the same time, this is only a JDBC method, and the main code is PS = con prepareStatement(sql); Such a sentence, and there are a lot of templated code, including establishing and closing connections We must find a way to solve it!

Optimize traditional JDBC

Step 1: create DBUtil class

I think in the first step, we can put forward the repeated template code and create a DBUtil database tool class:

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    static String ip = "127.0.0.1";
    static int port = 3306;
    static String database = "student";
    static String encoding = "UTF-8";
    static String loginName = "root";
    static String password = "root";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        String url = String.format("jdbc:mysql://%s:%d/%s?characterEncoding=%s&useSSL=false", ip, port, database, encoding);
        return DriverManager.getConnection(url, loginName, password);
    }
}

In this way, we can turn the above code into this:

public Student getOne(int id) {

    String sql = "select id,name from student where id = ?";
    Student student = null;
    // Declare JDBC variables
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        // Get connection
        con = DBUtil.getConnection();
        // Precompiled SQL
        ps = con.prepareStatement(sql);
        // Set parameters
        ps.setInt(1, id);
        // Execute SQL
        rs = ps.executeQuery();
        // POJO returned from assembly result set
        ....
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // Close database connection resource
        ....
    }
    return student;
}

It's just a sentence less written and an exception less handled by the registered driver. There's no big change and we must optimize it again

Step 2: use the try catch statement to automatically close the resource

Automatic resource shutdown is a new feature introduced in JDK 7. Students who don't understand it can go to see it written by three hearts Detailed explanation of new features of Java 7

So the code can be further optimized as follows:

public Student getOne(int id) {

    String sql = "select id,name from student where id = ?";
    Student student = null;
    // Include JDBC declaration variables in try(..) The resource will be automatically closed in
    try (Connection con = DBUtil.getConnection(); PreparedStatement ps = con.prepareStatement(sql)) {

        // Set parameters
        ps.setInt(1, id);
        // Execute SQL
        ResultSet rs = ps.executeQuery();
        // POJO returned from assembly result set
        if (rs.next()) {
            student = new Student();
            student.setId(rs.getInt(1));
            student.setName(rs.getString(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return student;
}

This looks much better, but we are still not satisfied, because our core code is to execute SQL statements and get the return set, and then come again

Further improve the DBUtil class:

Add a new method in DBUtil class to directly return the result set:

public static ResultSet getResultSet(String sql, Object[] objects) throws SQLException {

    ResultSet rs = null;
    try (Connection con = getConnection(); PreparedStatement ps = con.prepareStatement(sql)) {

        // Set the value of the SQL placeholder according to the parameters passed in
        for (int i = 0; i < objects.length; i++) {
            ps.setObject(i + 1, objects[i]);
        }
        // Execute the SQL statement and accept the result set
        rs = ps.executeQuery();
    }
    // Return result set
    return rs;
}

In this way, we can optimize our initial code as follows:

public Student getOne(int id) {

    String sql = "select id,name from student where id = ?";
    Object[] objects = {id};
    Student student = null;
    try (ResultSet rs = DBUtil.getResultSet(sql, objects);) {
        student.setId(rs.getInt(1));
        student.setName(rs.getString(1));
    } catch (SQLException e) {
        // Handling exceptions
        e.printStackTrace();
    }
    return student;
}

wooh! It looks much better, but the drawback is that the try catch statement is not removed. We can throw out SQLException without exception handling:

public Student getOne(int id) throws SQLException {

    String sql = "select id,name from student where id = ?";
    Object[] objects = {id};
    Student student = null;
    try (ResultSet rs = DBUtil.getResultSet(sql, objects);) {
        student.setId(rs.getInt(1));
        student.setName(rs.getString(1));
    }
    return student;
}

In fact, the above version is good enough. It's just some obsessive-compulsive disorder.

The DBUtil tool defined by ourselves is already very practical, because it is extracted from the templated code, so we can always use it

JDBC in Spring

To use the JDBC module in Spring, you must import the corresponding jar file:

jar packages to be imported:
spring-jdbc-4.3.16.RELEASE.jar
spring-tx-4.3.16.RELEASE.jar

Maven coordinate reference

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.32</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.0.5.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.0.5.RELEASE</version>
</dependency>

Fortunately, IDEA has been automatically deployed for us when creating the Spring project. Next, let's actually use JDBC in Spring:

Configure database resources

Just as we create the DBUtil class and encapsulate the connected information in it, we need to configure these database resources

Configuration mode:
Use simple database configuration
Using a third-party database connection pool

We can use the built-in classes in Spring to configure, but most of the time we will use the third-party database connection pool for configuration. Because the third-party classes are used, the XML file configuration is generally adopted, and we also use the XML file configuration here:

Use simple database configuration

First, let's try Spring's built-in class org springframework. jdbc. datasource. SimpleDriverDataSource:

<bean id="dateSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="username" value="root"/>
    <property name="password" value="root"/>
    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc://mysql://locolhost:3306/student"/>
</bean>

Let's test it. First write our JDBC operation class like this:

@Component("jdbc")
public class JDBCtest {

    @Autowired
    private DataSource dataSource;

    public User getOne(int stuID) throws SQLException {
        // id username password email
        String sql = "select id, username from user where id = " + stuID;
        User user = new User();
        Connection conn = dataSource.getConnection();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);
        if (rs.next()) {
            user.setId(rs.getLong("id"));
            user.setUsername(rs.getString("username"));
        }
        return user;
    }
}

Then write the test class:

@Test
public void test() throws SQLException {
    ApplicationContext context =
            new ClassPathXmlApplicationContext("applicationContext.xml");
    JDBCtest jdbc = (JDBCtest) context.getBean("jdbc");
    User user = jdbc.getOne(1);
    System.out.println(user.getId());
    System.out.println(user.getUsername());
}

Successfully fetched data from database:

Using a third-party database connection pool

The simple data source configured above is generally used for testing, because it is not a database connection pool, but a very simple database connection application. More often, we need to use a third-party database connection, such as C3P0 database connection pool:

<!--1,load jdbc.properties-->
<context:property-placeholder location="classpath:jdbc.properties"/>

<!--2,Configure data source objects-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="initialPoolSize" value="3"></property>
    <property name="maxPoolSize" value="10"></property>
    <property name="maxStatements" value="100"></property>
    <property name="acquireIncrement" value="2"></property>
</bean>

<!--3,to configure JdbcTemplate object-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
</bean>

Similar to the above tests, the difference is that the relevant jar packages supporting C3P0 database connection pool need to be introduced.

Jdbc Template

Spring provides a Jdbc Template class, which has encapsulated a variable of DataSource type. We can use it directly:

<?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:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

    <bean id="dataSrouce" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/javat?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <context:component-scan base-package="jdbc" />

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSrouce"/>
    </bean>

</beans>

Let's rewrite the JDBC operation class:

@Component("jdbc")
public class JDBCtest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public User getOne(int stuID) throws SQLException {
        // id username password email
        String sql = "SELECT id, username FROM user WHERE id = ?";
        User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User stu = new User();
                stu.setId(resultSet.getLong("id"));
                stu.setUsername(resultSet.getString("username"));
                return stu;
            }
        }, stuID);
        return user;
    }
}

The test class remains unchanged, and the correct results can be obtained by running:

But it doesn't seem to be much simpler. Let's take a look at other CRUD examples:

    /**
     * Add a piece of data
     *
     * @param user
     */
    public void add(User user) {
        this.jdbcTemplate.update("INSERT INTO student(id,username,password,email) VALUES(?,?,?,?)",user.getId(),user.getUsername(),user.getPassword(),user.getEmail());
    }

    /**
     * Update username
     *
     * @param user
     */
    public void update(User user) {
        this.jdbcTemplate.update("update user set name = ? where id = ?", user.getUsername(),user.getId());
    }

    /**
     * Delete a piece of data
     *
     * @param id
     */
    public void delete(int id) {
        this.jdbcTemplate.update("delete from user from id = ?", id);
    }

It should be much simpler now. If you return a collection, you only need to rewrite the getOne() method above a little

reference material:

Integrated development of Java EE Internet lightweight framework
Spring combat
Omnipotent Baidu and omnipotent brain
JDBC encapsulation
Use of Spring's JdbcTemplate

Extended reading: Official documents , Add, delete, modify and query the JdbcTemplate in Spring,① Thorough understanding of database transactions,② Detailed explanation of Spring transaction management,③ Spring transaction management (detailed explanation + example),④ Comprehensively analyze the programmatic transaction management and declarative transaction management of Spring

Added by rledieu on Thu, 27 Jan 2022 05:12:18 +0200

Programming VIP