Spring Boot数据库操作之基本的JDBC访问和JDBCTemplate使用

绝地灬酷狼 2022-05-18 02:24 185阅读 0赞

![70][]

作者：谭东

我们先看下**最基础的JDBC**的使用方式吧。我们这里使用MySQL。电脑里可以安装个mysql，便于本地测试使用。

Mysql下载地址：[https://dev.mysql.com/downloads/][https_dev.mysql.com_downloads]

下载后，解压mysql压缩包到某一文件件，在根目录新建my.ini文件，写入配置：

[mysqld]
    character-set-server=utf8 
    collation-server=utf8_general_ci 
    
    [mysql]
    default-character-set = utf8
    
    [mysql.server]
    default-character-set = utf8
    
    
    [mysqld_safe]
    default-character-set = utf8
    
    
    [client]
    default-character-set = utf8

管理员身份运行cmd命令窗口。切换到mysql的bin文件夹下。输入命令：mysqld --install。

安装完毕，然后初始化：mysqld  --initialize。启动服务：net start mysql。

登录mysql：mysql -u root -p，首次登陆一般是没有密码的，如果你安装的是mysql 5.7及以上版本，默认是有个随机密码了。

需要手动在mysql目录下搜索\*.err，以文本形式打开才能看到如下内容：

\[Note\] A temporary password is generated for root@localhost: ?g\#4:Yju&1Kp

红色部分为密码。这样登录成功后再去修改密码即可：alter user root@localhost identified by 'Mysql123456'。新密码最好有大写字母，小写字母和数字。

最后我们创建个数据库：create database student。

然后在student数据库里创建student表：create table student(id int primary key auto\_increment,name varchar(100),sex varchar(10),age int,tel varchar(60));

在之前的pom.xml里引入mysql库。

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.9-rc</version>
            </dependency>

application.properties里加入mysql配置信息。

server.port=8082
    spring.datasource.url=jdbc:mysql://localhost:3306/student?serverTimezone=GMT=&useUnicode=true&characterEncoding=utf-8&useSSL=true
    spring.datasource.username=root
    spring.datasource.password=Mysql123456
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

编写JDBC工具类。

package com.tandong.testjavaweb.utils;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.*;
    import java.util.Properties;
    
    public class JDBCUtils {
        // mysql 连接参数
        private static String jdbcDriver = null;
        private static String url = null;
        private static String user = null;
        private static String password = null;
        private static boolean isCfg = false; // 记录是否读取过jdbc.properties文件配置 、避免多次读取
        // 线程安全的懒汉模式单例
        private static JDBCUtils jdbcUtils = null;
    
        public static synchronized JDBCUtils getInstance() {
            if (jdbcUtils == null) {
                if (!isCfg) {
                    initJDBCParameter();
                }
                return jdbcUtils = new JDBCUtils();
            }
            return jdbcUtils;
        }
    
        /**
         * 读取属性文件中的mysql配置
         */
        private static void initJDBCParameter() {
            Properties properties = new Properties();
            InputStream in = JDBCUtils.class.getResourceAsStream("/application.properties");//加载 application.properties资源文件，如果该文件在包内则加包名
            try {
                properties.load(in);
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            if (jdbcDriver == null || url == null || user == null || password == null) {
                jdbcDriver = properties.getProperty("spring.datasource.driver-class-name");
                url = properties.getProperty("spring.datasource.url");
                user = properties.getProperty("spring.datasource.username");
                password = properties.getProperty("spring.datasource.password");
                isCfg = true;
            }
        }
    
        /**
         * 获取数据连接
         *
         * @return
         * @throws Exception
         */
        public Connection getConnection() throws Exception {
            Class.forName(jdbcDriver);
            return DriverManager.getConnection(url, user, password);
        }
    
        /**
         * 释放数据库链接资源
         *
         * @param con  // 数据库连接对象
         * @param stmt // 执行sql操作对象
         * @param rs   // 查询结果集对象
         */
        public void closeConnection(Connection con, Statement stmt, ResultSet rs) {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

编写个Student实体类。

package com.tandong.testjavaweb.entity;
    
    public class Student {
        public int id;
        public String name;
        public String sex;
        public int age;
        public String tel;
    
        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 getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public int getAge() {
            return age;
        }
    
        public void setAge(int age) {
            this.age = age;
        }
    
        public String getTel() {
            return tel;
        }
    
        public void setTel(String tel) {
            this.tel = tel;
        }
    
        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", age=" + age +
                    ", tel='" + tel + '\'' +
                    '}';
        }
    }

编写StudentDao类。

package com.tandong.testjavaweb.dao;
    
    import com.tandong.testjavaweb.entity.Student;
    
    import java.sql.SQLException;
    import java.util.List;
    
    public interface StudentsDao {
    
        //添加方法
        void add(Student s) throws SQLException;
    
        //更新方法
        void update(Student s) throws SQLException;
    
        //删除方法
        void delete(int id) throws SQLException;
    
        //查找方法
        Student findById(int id) throws SQLException;
    
        //查找所有
        List<Student> findAll()throws SQLException;
    }

编写StudentDao的实现类，StudentDaoImpl。

package com.tandong.testjavaweb.dao;
    
    import com.tandong.testjavaweb.entity.Student;
    import com.tandong.testjavaweb.utils.JDBCUtils;
    import org.springframework.stereotype.Service;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    @Service
    public class StudentsDaoImpl implements StudentsDao {
        @Override
        public void add(Student s) throws SQLException {
            Connection conn = null;
            PreparedStatement ps = null;
            String sql = "insert into student(id,name,sex,age,tel)values(?,?,?,?,?)";
            try {
                conn = JDBCUtils.getInstance().getConnection();
                ps = conn.prepareStatement(sql);
                ps.setInt(1, s.id);
                ps.setString(2, s.name);
                ps.setString(3, s.sex);
                ps.setInt(4, s.age);
                ps.setString(5, s.tel);
                ps.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
                throw new SQLException("添加数据失败！");
            } finally {
                JDBCUtils.getInstance().closeConnection(conn, ps, null);
            }
            System.out.println("数据添加成功！");
        }
    
        @Override
        public void update(Student s) throws SQLException {
            Connection conn = null;
            PreparedStatement ps = null;
            if (s == null) return;
            // 根据对象属性 拼接 更新sql
            String sql = "update student set ";
            StringBuilder sb = new StringBuilder(sql);
            sb.append("name=?");
            sb.append(",sex=?");
            sb.append(",tel=?");
            if (s.age != 0) sb.append(",age=?");
            if (s.id != 0) sb.append("where id=?");
            // 如果sql语句和原始语句一样，或者 没有包含条件语句不允许执行
            if (sb.toString().equals(sql) || sb.toString().contains("where")) return;
            try {
                conn = JDBCUtils.getInstance().getConnection();
                ps = conn.prepareStatement(sql);
                ps.setString(1, s.name);
                ps.setInt(2, s.age);
                ps.setString(3, s.sex);
                ps.setString(4, s.tel);
                ps.setInt(5, s.id);
                ps.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
                throw new SQLException("更新数据失败");
            } finally {
                JDBCUtils.getInstance().closeConnection(conn, ps, null);
            }
            System.out.println("数据更新成功！");
        }
    
        @Override
        public void delete(int id) throws SQLException {
            Connection conn = null;
            PreparedStatement ps = null;
            String sql = "delete from student where id=?";
            try {
                conn = JDBCUtils.getInstance().getConnection();
                ps = conn.prepareStatement(sql);
                ps.setInt(1, id);
                ps.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
                throw new SQLException(" 删除数据失败");
            } finally {
                JDBCUtils.getInstance().closeConnection(conn, ps, null);
            }
            System.out.println("数据删除成功！");
        }
    
        @Override
        public Student findById(int id) throws SQLException {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            Student s = null;
            String sql = "select * from student where id=?";
            try {
                conn = JDBCUtils.getInstance().getConnection();
                ps = conn.prepareStatement(sql);
                ps.setInt(1, id);
                rs = ps.executeQuery();
                if (rs.next()) {
                    s = new Student();
                    s.id = id;
                    s.name = rs.getString("name");
                    s.age = rs.getInt("age");
                    s.sex = rs.getString("sex");
                    s.tel = rs.getString("tel");
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new SQLException("根据ID查询数据失败");
            } finally {
                JDBCUtils.getInstance().closeConnection(conn, ps, rs);
            }
            System.out.println("数据查询成功！");
            return s;
        }
    
        @Override
        public List<Student> findAll() throws SQLException {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            Student s;
            List<Student> students = new ArrayList<Student>();
            String sql = "select * from student";
            try {
                conn = JDBCUtils.getInstance().getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    s = new Student();
                    s.id = rs.getInt("id");
                    s.name = rs.getString("name");
                    s.age = rs.getInt("age");
                    s.sex = rs.getString("sex");
                    s.tel = rs.getString("tel");
                    students.add(s);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new SQLException("查询所有数据失败");
            } finally {
                JDBCUtils.getInstance().closeConnection(conn, ps, rs);
            }
            System.out.println("数据查询成功！");
            return students;
        }
    }

编写访问查询接口。

package com.tandong.testjavaweb.controller;
    
    import com.tandong.testjavaweb.dao.StudentsDaoImpl;
    import com.tandong.testjavaweb.entity.Student;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.*;
    
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 注意@RestController用于写接口
     *
     * @Controller 用于写页面跳转
     */
    @RestController
    //@Controller
    @SpringBootApplication
    @RequestMapping("/student")
    public class StudentController {
        @Autowired
        StudentsDaoImpl studentsDao;
    
        /**
         * ResopnseBody如果返回的是Object，会把返回结果转为Json格式输出
         *
         * @param userName
         * @return
         */
        @ResponseBody
        @RequestMapping(value = "/getUserName", method = RequestMethod.GET)
        public Object getUserNameGet(@RequestParam(value = "name") String userName) {
            return userName;
        }
    
        @RequestMapping(value = "/getName", method = RequestMethod.GET)
        public String getName(@RequestParam(value = "name") String name) {
            return "Hello " + name;
        }
    
        @RequestMapping(value = "/addStudent", method = RequestMethod.GET)
        public String listUser() {
            Student student = new Student();
            student.setAge(12);
            student.setName("学生");
            student.setSex("女");
            student.setTel("13333333333");
            try {
                studentsDao.add(student);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return "插入成功";
        }
    
        @RequestMapping(value = "/list", method = RequestMethod.GET)
        public Object getLists() {
            List<Student> userList = null;
            try {
                userList = studentsDao.findAll();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return userList;
        }
    }

其中@Autowired是自动装配的注解。

好了，我们运行下。

访问：[http://localhost:8082/student/addStudent][http_localhost_8082_student_addStudent]，插入一条数据。

![70 1][]

多插入几条，我们再访问：[http://localhost:8082/student/list][http_localhost_8082_student_list]，查询数据。

![70 2][]

附带下mysql常用命令：

删除mysql服务命令：sc delete mysql，或mysqld --remove

**接下来再看下Spring-Boot-jdbc使用方式，也就是JDBCTemplate。**

pom.xml导包：

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>

基于原来的StudentImpl，我们修改下：

package com.tandong.testjavaweb.dao;
    
    import com.tandong.testjavaweb.entity.Student;
    import com.tandong.testjavaweb.utils.JDBCUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Service;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    @Service
    public class StudentsDaoImpl implements StudentsDao {
        @Autowired
        JdbcTemplate jdbcTemplate;
    
        @Override
        public void add(Student s) throws SQLException {
            jdbcTemplate.update("insert into student(id,name,sex,age,tel)values(?,?,?,?,?)", s.getId()
                    , s.getName(), s.getSex(), s.getAge(), s.getTel());
        }
    
        @Override
        public void update(Student s) throws SQLException {
            jdbcTemplate.update("UPDATE  student SET name=? ,sex=?,age=?,tel=? WHERE id=?",
                    s.getName(), s.getSex(), s.getAge(), s.getTel(), s.getId());
        }
    
        @Override
        public void delete(int id) throws SQLException {
            jdbcTemplate.update("DELETE from TABLE student where id=?", id);
        }
    
        @Override
        public Student findById(int id) throws SQLException {
            List<Student> list = jdbcTemplate.query("select * from student where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Student.class));
            if (list != null && list.size() > 0) {
                Student student = list.get(0);
                return student;
            } else {
                return null;
            }
        }
    
        @Override
        public List<Student> findAll() throws SQLException {
            List<Student> list = jdbcTemplate.query("select * from student", new Object[]{}, new BeanPropertyRowMapper(Student.class));
            if (list != null && list.size() > 0) {
                return list;
            } else {
                return null;
            }
        }
    }

StudentController不需要修改，重新编译运行，这样就可以达到一样的效果了。

完整项目Github地址：[https://github.com/jaychou2012/Spring-Boot-Web][https_github.com_jaychou2012_Spring-Boot-Web]

[70]: /images/20220518/f91fc817ded949c18a8a4de33e1374bb.png
[https_dev.mysql.com_downloads]: https://dev.mysql.com/downloads/
[http_localhost_8082_student_addStudent]: http://localhost:8082/student/addStudent
[70 1]: /images/20220518/84b82011f75244f5a3b002fe77542597.png
[http_localhost_8082_student_list]: http://localhost:8082/student/list
[70 2]: /images/20220518/837a5215e14e443d9faae2a3548da037.png
[https_github.com_jaychou2012_Spring-Boot-Web]: https://github.com/jaychou2012/Spring-Boot-Web