本文共 6342 字,大约阅读时间需要 21 分钟。
1、 spring 框架一站式框架
(1)针对 javaee 三层,每一层都有解决技术
(2)在 dao 层,使用 jdbcTemplate
2、 spring 对不同的持久化层技术都进行封装

(1)jdbcTemplate对jdbc进行封装
3 、jdbcTemplate 使用和 dbutils 使用很相似,都数据库进行 crud 操作
实现准备
1、导入 jar 包
(1)Spirng 基础包(不一定都需要)

(2)jdbcTemplate 包

(3)jdbc-mysql 驱动包

2、新建数据库
(1)本地服务器 localhost,用户名 root,密码为空
(2)新建数据库 spring
(3)新建数据表 user,三个字段,分别是 id,username,password
增加,删除,修改
JdbcTemplateDemo1
- package com.liuyanzhao.jdbc;
- import org.junit.Test;
- import org.springframework.jdbc.core.JdbcTemplate;
- import org.springframework.jdbc.core.RowMapper;
- import org.springframework.jdbc.datasource.DriverManagerDataSource;
- import java.sql.*;
- import java.util.List;
- public class JdbcTemplateDemo1 {
-
- @Test
- public void saveUser() {
-
- DriverManagerDataSource dataSource = new DriverManagerDataSource();
- dataSource.setDriverClassName("com.mysql.jdbc.Driver");
- dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/spring");
- dataSource.setUsername("root");
- dataSource.setPassword("");
-
- JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
-
- String sql = "insert into user values(?,?,?)";
- int rows = jdbcTemplate.update(sql,1,"刘曌","123456");
- System.out.println(rows);
- }
-
- @Test
- public void updateUser() {
-
- DriverManagerDataSource dataSource = new DriverManagerDataSource();
- dataSource.setDriverClassName("com.mysql.jdbc.Driver");
- dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/spring" +
- "?useUnicode=true&characterEncoding=utf8");
- dataSource.setUsername("root");
- dataSource.setPassword("");
-
- JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
-
- String sql = "update user set username=?,password=? where id=?";
- int rows = jdbcTemplate.update(sql,"刘言曌","666666",1);
- System.out.println(rows);
- }
-
- @Test
- public void removeUser() {
-
- DriverManagerDataSource dataSource = new DriverManagerDataSource();
- dataSource.setDriverClassName("com.mysql.jdbc.Driver");
- dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/spring" +
- "?useUnicode=true&characterEncoding=utf8");
- dataSource.setUsername("root");
- dataSource.setPassword("");
-
- JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
-
- String sql = "delete from user where id=?";
- int rows = jdbcTemplate.update(sql,1);
- System.out.println(rows);
- }
- }
查询
查询分为 “查询记录总数”,“查询单个记录”,“查询多个记录”
1、查询记录总数,返回一个整数
- @Test
- public void countUser() {
-
- DriverManagerDataSource dataSource = new DriverManagerDataSource();
- dataSource.setDriverClassName("com.mysql.jdbc.Driver");
- dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/spring" +
- "?useUnicode=true&characterEncoding=utf8");
- dataSource.setUsername("root");
- dataSource.setPassword("");
-
- JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
-
- String sql = "select count(*) from user";
- int count = jdbcTemplate.queryForObject(sql,Integer.class);
- System.out.println(count);
- }
2、查询单个对象,返回一个对象
(1)新建 user 实体类
- package com.liuyanzhao.jdbc;
- public class User {
- private int id;
- private String username;
- private String password;
- public int getId() {
- return id;
- }
- public String getUsername() {
- return username;
- }
- public String getPassword() {
- return password;
- }
- public void setId(int id) {
- this.id = id;
- }
- public void setUsername(String username) {
- this.username = username;
- }
- public void setPassword(String password) {
- this.password = password;
- }
- @Override
- public String toString() {
- return "User{" +
- "id=" + id +
- ", username='" + username + '\'' +
- ", password='" + password + '\'' +
- '}';
- }
- }
(2)新建 MyRowMapper 类,我们可以把它放到 JdbcTemplateDemo1 类中
- class MyRowMapper implements RowMapper<User> {
- @Override
- public User mapRow(ResultSet rs, int num) throws SQLException {
-
- int id = rs.getInt("id");
- String username = rs.getString("username");
- String password = rs.getString("password");
-
- User user = new User();
- user.setId(id);
- user.setUsername(username);
- user.setPassword(password);
- return user;
- }
- }
(3)查询单条记录信息
- @Test
- public void getUser() {
-
- DriverManagerDataSource dataSource = new DriverManagerDataSource();
- dataSource.setDriverClassName("com.mysql.jdbc.Driver");
- dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/spring" +
- "?useUnicode=true&characterEncoding=utf8");
- dataSource.setUsername("root");
- dataSource.setPassword("");
-
- JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
-
- String sql = "select * from user where username=?";
- User user = jdbcTemplate.queryForObject(sql,new MyRowMapper(),"刘言曌");
- System.out.println(user);
- }
3、查询多条记录,返回一个 list 集合
(1)同上
(2)同上
(3)查询多条记录信息
- @Test
- public void listUser() {
-
- DriverManagerDataSource dataSource = new DriverManagerDataSource();
- dataSource.setDriverClassName("com.mysql.jdbc.Driver");
- dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/spring" +
- "?useUnicode=true&characterEncoding=utf8");
- dataSource.setUsername("root");
- dataSource.setPassword("");
-
- JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
-
- String sql = "select * from user";
- List<User> list = jdbcTemplate.query(sql,new MyRowMapper());
- System.out.println(list);
- }
补充
我们这里补充一下 原生的 查询单条记录的代码,复习一下 JDBC,同时比较一下 的 jdbcTemplate 和 原生 JDBC 代码的区别
- @Test
- public void getUserByJDBC() throws SQLException {
- final String URL = "jdbc:mysql://127.0.0.1:3306/spring?useUnicode=true&characterEncoding=utf8";
- final String USERNAME = "root";
- final String PASSWORD = "";
- Connection conn = null;
- PreparedStatement ptmt = null;
- ResultSet rs = null;
-
- try {
- Class.forName("com.mysql.jdbc.Driver");
- conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
- String sql = "select * from user where username=?";
-
- ptmt = conn.prepareStatement(sql);
-
- ptmt.setString(1,"刘言曌");
-
- rs = ptmt.executeQuery();
-
- while (rs.next()) {
-
- int id = rs.getInt("id");
- String username = rs.getString("username");
- String password = rs.getString("password");
-
- User user = new User();
- user.setId(id);
- user.setUsername(username);
- user.setPassword(password);
- System.out.println(user);
- }
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } finally {
-
- rs.close();
- ptmt.close();
- conn.close();
- }
- }
参考:传智播客视频
本文链接: