第二章 使用传统方式访问数据库

发表于 | 分类于

课程目录

传统方式访问数据库

  • JDBC
  • Spring JdbcTemplate
  • 弊端分析

准备工作

JDBC

  • Connection
  • Statement
  • ResultSet
  • Test Case

开始编码

创建 Maven 工程

pom.xml 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?xml version="1.0" encoding="UTF-8"?>
<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.imooc</groupId>
    <artifactId>springdata</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- MySql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
        <!-- Junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
        </dependency>
    </dependencies>

</project>

数据表准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建 spring_data 数据库
create database spring_data;
# 进入 spring_data 数据库
use spring_data;
# 创建学生表 
create table student(
  id int not null auto_increment,
  name varchar(20) not null,
  age int not null,
  primary key (id)
);
# 创建学生数据
insert into student(name,age) values ("zhangsan",20);
insert into student(name,age) values ("lisi",21);
insert into student(name,age) values ("wangwu",22);

开发 JDBCUtil 工具类

  • 获取 Connection
  • 关闭 Connection、Statement、ResultSet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
package com.imooc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类
 * 1) 获取工具类
 * 2）释放资源
 *
 * @author weilai
 * @version 1.0.0 2018/8/22
 */
public class JDBCUtil {

    /**
     * 获取Connection
     *
     * @return 所获得到的JDBC的Connection
     */
    public static Connection getConnection() throws Exception {

        /**
         * 不建议硬编码到代码里
         *
         * 最佳实践：配置性的建议写到配置文件中
         */

        String url = "jdbc:mysql:///spring_data?useSSL=false";
        String user = "root";
        String password = "weilai";
        String driverClass = "com.mysql.jdbc.Driver";

        InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(inputStream);

        url = (String) properties.get("jdbc.url");
        user = (String) properties.get("jdbc.user");
        password = (String) properties.get("jdbc.password");
        driverClass = (String) properties.get("jdbc.driverClass");

        Class.forName(driverClass);
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 释放 DB 相关的资源
     *
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void release(
            ResultSet resultSet,
            Statement statement, 
            Connection connection
        ) {

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

}

db.properties

1
2
3
4
jdbc.url=jdbc:mysql:///spring_data?useSSL=false
jdbc.user=root
jdbc.password=weilai
jdbc.driverClass=com.mysql.jdbc.Driver

DAO 开发

  • com.imooc.domain.Student.java 实体类
  • com.imooc.dao.StudentDAO.java StudentDAO接口
  • com.imooc.dao.StudentDAOImpl.java StudentDAO接口实现
  • com.imooc.dao.StudentDAOTest.java StudentDAO接口实现单元测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
...
    public List<Student> query() {
        List<Student> studentList = new ArrayList<Student>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select id,name,age from student";
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            Student student;
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                student = new Student();
                student.setId(id);
                student.setUsername(name);
                student.setAge(age);
                studentList.add(student);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
        return studentList;
    }
    
    public void save(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "insert into student(name,age) values (?,?)";
        try {
            connection = JDBCUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getUsername());
            preparedStatement.setInt(2,student.getAge());
            preparedStatement.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(resultSet, preparedStatement, connection);
        }
    }
...

Spring JDBC Template 方式

引入 Maven 依赖

1
2
3
4
5
6
7
8
9
10
11
<!-- Spring data Jdbc-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.5.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.5.RELEASE</version>
</dependency>

创建 Spring JDBC Template 实现类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package com.imooc.dao;

import com.imooc.domain.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * StudentDAO 访问接口实现类：通过 Spring JDBC Template 的JDBC方式操作
 *
 * @author weilai
 * @version 1.0.0 2018/8/23
 */
public class StudentDAOSpringJdbcImpl implements StudentDAO {

    private JdbcTemplate jdbcTemplate;

    public List<Student> query() {
        final List<Student> studentList = new ArrayList<Student>();
        String sql = "select id,name,age from student";
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet resultSet) throws SQLException {
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");

                    Student student = new Student();
                    student.setId(id);
                    student.setUsername(name);
                    student.setAge(age);
                    studentList.add(student);
                }
            }
        });
        return studentList;
    }

    public void save(Student student) {
        String sql = "insert into student(name,age) values (?,?)";
        jdbcTemplate.update(sql, student.getUsername(), student.getAge());
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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

创建 Spring 配置文件 beans.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="weilai"/>
        <property name="url" value="jdbc:mysql:///spring_data?useSSL=false"/>
    </bean>

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

    <bean id="studentDAO" class="com.imooc.dao.StudentDAOSpringJdbcImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

</beans>

实现 单元测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
package com.imooc.dao;

import com.imooc.domain.Student;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

import static org.junit.Assert.*;

/**
 * Spring JDBC Template 单元测试
 * @author weilai
 * @version 1.0.0 2018/8/23
 */
public class StudentDAOSpringJdbcImplTest {

    private ApplicationContext context = null;

    private StudentDAO studentDAO = null;

    @Before
    public void setUp() {
        context = new ClassPathXmlApplicationContext("beans.xml");
        System.out.println("setUp");
    }

    @After
    public void tearDown() {
        context = null;
        System.out.println("tearDown");
    }

    @Test
    public void query() {
        studentDAO = (StudentDAO) context.getBean("studentDAO");
        List<Student> studentList = studentDAO.query();
        for (Student student : studentList) {
            System.out.print("id:" + student.getId());
            System.out.print(" name:" + student.getUsername());
            System.out.println(" age:" + student.getAge());
        }
    }

    @Test
    public void save() {
        studentDAO = (StudentDAO) context.getBean("studentDAO");
        Student student = new Student();
        student.setUsername("weilai");
        student.setAge(40);
        studentDAO.save(student);
    }
}

弊端分析

  1. DAO 里编写了非常非常多的代码。
  2. DAOImpl 里有很多重复的代码。
  3. 需要自己开发 分页等许多其他功能。