Spring学习

置顶 | | | | |

Spring-JDBC

在这里插入图片描述

一：JDBC回顾

1、首先创建一个数据库，在该数据库中创建一个表，添加信息。

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
/*
SQLyog Ultimate v12.3.1 (64 bit)
MySQL - 5.7.20-log : Database - db_student
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;`;
create database chew;

use chew;
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) NOT NULL,
  `sage` int(11) NOT NULL,
  `sno` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`age`,`no`) values 

(2,'刘畅',12,'2020001'),

(3,'张飞',12,'2020001'),

(4,'张飞',12,'2020001');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2、通过数据库的信息建立实体类Student

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
package com.iciot.domain;

import java.io.Serializable;
import java.util.Objects;

public class Student implements Serializable {
    private int sid;
    private String sname;
    private int sage;
    private String sno;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public int getSage() {
        return sage;
    }

    public void setSage(int sage) {
        this.sage = sage;
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public Student() {
    }

    public Student(int sid, String sname, int sage, String sno) {
        this.sid = sid;
        this.sname = sname;
        this.sage = sage;
        this.sno = sno;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Student student = (Student) o;
        return sid == student.sid &&
                sage == student.sage &&
                Objects.equals(sname, student.sname) &&
                Objects.equals(sno, student.sno);
    }

    @Override
    public int hashCode() {
        return Objects.hash(sid, sname, sage, sno);
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", sage=" + sage +
                ", sno='" + sno + '\'' +
                '}';
    }
}

3、创建一个JDBC工具类：DBUTils用来获取连接

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
package com.iciot.utils;

import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class DBUTils {
    private static String url;
    private static String user;
    private static Connection con = null;
    private static String password;
    private static String driver;
    static{
        //读取资源文件，获取值
        try {
            //创建Properties集合类
            Properties pro=new Properties();
            //加载文件
            pro.load(new FileReader("resources/jdbc.properties"));
            //获取数据
            url=pro.getProperty("url");
            user=pro.getProperty("user");
            password=pro.getProperty("password");
            driver=pro.getProperty("driver");
            try {
                Class.forName(driver);
                con = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        return con;
    }
    public static void release(Connection con, Statement stat, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat!=null){
            try {
                stat.close();
                stat = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
                con = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

注意：这里我把driver、username、password、url给抽取了

resources下创建一个jdbc.properties文件

1
2
3
4
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///db_student?useSSL=false&useUnicode=true&characterEconding=UTF-8
user=root
password=131411

4、创建接口StudentDao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.iciot.dao;

import com.iciot.domain.Student;

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

public interface StudentDao {
    public void insert(Student student) throws SQLException;
    public void update(Student student)throws SQLException;
    public void delete(int id)throws SQLException;
    public Student findById(int id)throws SQLException;
    public ArrayList<Student> findByName(String name)throws SQLException;
    public ArrayList<Student> findAll()throws SQLException;
}

5、创建实现类StudentDaoImpl

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
package com.iciot.dao.impl;

import com.iciot.dao.StudentDao;
import com.iciot.domain.Student;
import com.iciot.utils.DBUTils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class StudentDaoImpl implements StudentDao {
    @Override
    public void insert(Student student) throws SQLException {
        Connection con = DBUTils.getConnection();
        String sql = "insert into student (sname,sage,sno) values(?,?,?)";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,student.getSname());
        ps.setInt(2,student.getSage());
        ps.setString(3,student.getSno());
        ps.executeUpdate();
        DBUTils.release(con,ps,null);
    }

    @Override
    public void update(Student student) throws SQLException{
       //补全
        Connection connection = DBUTils.getConnection();
        String sql="update student set sname=? where sid=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,student.getSname());
        preparedStatement.setInt(2,student.getSid());
        if (preparedStatement.executeUpdate()>0){
            System.out.println("更改成功！");
        }
        DBUTils.release(connection,preparedStatement,null);
    }

    @Override
    public void delete(int id) throws SQLException{
        //补全
        Connection connection = DBUTils.getConnection();
        String sql="delete from student where sid=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,sid);
        if (preparedStatement.executeUpdate()>0){
            System.out.println("删除成功！");
        }
        DBUTils.release(connection,preparedStatement,null);
    }

    @Override
    public Student findById(int id) throws SQLException{
        Connection con = DBUTils.getConnection();
        String sql = "select * from student where sid=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,sid);
        ResultSet rs = ps.executeQuery();
        Student student = new Student();
        if(rs.next()){
            student.setSname(rs.getString("sname"));
            student.setSage(rs.getInt("sage"));
            student.setSno(rs.getString("sno"));
            student.setSid(rs.getInt("sid"));
        }
        DBUTils.release(con,ps,rs);
        return student;
    }

    @Override
    public ArrayList<Student> findByName(String name) throws SQLException{
        //补全
        Connection con = DBUTils.getConnection();
        String sql = "select * from student where sname=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,sname);
        ResultSet rs = ps.executeQuery();
        ArrayList<Student> al = new ArrayList<>();
        while(rs.next()){
            Student student = new Student();
            student.setSname(rs.getString("sname"));
            student.setSage(rs.getInt("sage"));
            student.setSno(rs.getString("sno"));
            student.setSid(rs.getInt("sid"));
            al.add(student);
        }
        DBUTils.release(con,ps,rs);
        return al;
    }

    @Override
    public ArrayList<Student> findAll() throws SQLException{
        Connection con = DBUTils.getConnection();
        String sql = "select * from student";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        ArrayList<Student> al = new ArrayList<>();
        while(rs.next()){
            Student student = new Student();
            student.setSname(rs.getString("sname"));
            student.setSage(rs.getInt("sage"));
            student.setSno(rs.getString("sno"));
            student.setSid(rs.getInt("sid"));
            al.add(student);
        }
        DBUTils.release(con,ps,rs);
        return al;
    }
}

6、进行测试

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
package com.iciot.test;

import com.iciot.dao.impl.StudentDaoImpl;
import com.iciot.domain.Student;
import org.junit.Test;

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

public class StudentDaoImplTest {
    @Test
    public void testInsert() throws SQLException {
        Student student = new Student();
        student.setSage(12);
        student.setSname("张飞");
        student.setSno("2020001");
        StudentDaoImpl sd = new StudentDaoImpl();
        sd.insert(student);
    }
    @Test
    public void testfindById()throws SQLException{
        StudentDaoImpl sd = new StudentDaoImpl();
        Student s = sd.findById(2);
        System.out.println(s);
    }
    @Test
    public void testfindAll()throws SQLException{
        StudentDaoImpl sd = new StudentDaoImpl();
        ArrayList<Student> al = sd.findAll();
        for(Student s:al) {
            System.out.println(s);
        }
    }
    //update
    @Test
    public void updateStudent() throws SQLException {
        StudentDaoImpl studentDao = new StudentDaoImpl();
        Student student = new Student();
        student.setSname("刘畅");
        student.setSid(2);
        studentDao.update(student);
    }
    //delete
    @Test
    public void deleteStudent() throws SQLException {
        StudentDaoImpl studentDao = new StudentDaoImpl();
        studentDao.delete(1);
    }
    @Test
    public void findStudentByName() throws SQLException {
        StudentDaoImpl sd = new StudentDaoImpl();
        ArrayList<Student> al = sd.findByName("刘畅");
        for(Student s:al) {
            System.out.println(s);
        }
    }
}

二：Spring整合JDBC

1、与上述一样，首先创建一个数据库chew，创建一个表student，插入信息。

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
/*
SQLyog Ultimate v12.3.1 (64 bit)
MySQL - 5.7.20-log : Database - db_student
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;`;
create database chew;

use chew;
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) NOT NULL,
  `sage` int(11) NOT NULL,
  `sno` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`age`,`no`) values 

(2,'刘畅',12,'2020001'),

(3,'张飞',12,'2020001'),

(4,'张飞',12,'2020001');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2、引入Spring-JDBC的Maven坐标以及Mysql连接，以及其它Spring依赖包

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
<dependencies>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.7.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
        </dependency>
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.24</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>5.2.7.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.7.RELEASE</version>
        </dependency>
</dependencies>

3、通过数据库的信息创建实体类

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
package com.liu.dao;

public class Student {
    private int sid;
    private String sname;
    private int sage;
    private String sno;

    public Student() {
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", sage=" + sage +
                ", sno='" + sno + '\'' +
                '}';
    }

    public Student(int sid, String sname, int sage, String sno) {
        this.sid = sid;
        this.sname = sname;
        this.sage = sage;
        this.sno = sno;
    }

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public int getSage() {
        return sage;
    }

    public void setSage(int sage) {
        this.sage = sage;
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }
}

4、在Spring的核心配置文件中配置JdbcTemplate、以及DataSource

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
    <context:property-placeholder location="jdbc.properties"/>
    <!--数据源对象-->
    <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.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <!--jdbc模板对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

5、进行测试

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
import com.liu.dao.Student;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Test
    public void testUpdate(){
        int i = jdbcTemplate.update("update student set name =? where id=?", "GG", 2);
        System.out.println(i);
    }
    @Test
    public void testDelete(){
        jdbcTemplate.update("delete from student where name=?","GG");
    }
    @Test
    public void testQueryAll(){
        List<Student> list = jdbcTemplate.query("select *from student", new BeanPropertyRowMapper<Student>(Student.class));
        System.out.println(list);
    }
    @Test
    public void testQueryBySId(){
        Student student = jdbcTemplate.queryForObject("select *from student where sid=?", new BeanPropertyRowMapper<Student>(Student.class), 3);
        System.out.println(student);
    }
    @Test
    public void testQueryCount(){
        Integer integer = jdbcTemplate.queryForObject("select count(*) from student", Integer.class);
        System.out.println(integer);
    }
}

三：总结

可以发现代码减少了很多。当然使用Spring-jdbc还不是最简单的。mybatis与Spring整合才是最简单的吧~

相关文章
点击查看
-------------------本文结束 感谢您的阅读-------------------