初探JdbcTemplate操作

一、创建数据库

• 执行命令：CREATE DATABASE simonshop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

二、创建用户

• 创建用户表 - t_user

CREATE TABLE t_user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(20) NOT NULL,
password varchar(20) DEFAULT NULL,
telephone varchar(11) DEFAULT NULL,
register_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
popedom int(11) DEFAULT NULL COMMENT ‘0：管理员；1：普通用户’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

• 给用户表添加4条记录

INSERT INTO t_user VALUES (‘1’, ‘admin’, ‘12345’, ‘15734345678’, ‘2016-12-02 08:40:35’, ‘0’);
INSERT INTO t_user VALUES (‘2’, ‘郑晓红’, ‘11111’, ‘13956567889’, ‘2016-12-20 09:51:43’, ‘1’);
INSERT INTO t_user VALUES (‘3’, ‘温志军’, ‘22222’, ‘13956678907’, ‘2016-12-20 09:52:36’, ‘1’);
INSERT INTO t_user VALUES (‘4’, ‘涂文艳’, ‘33333’, ‘15890905678’, ‘2016-12-05 09:52:56’, ‘1’);

三、打开Spring项目

• Spring项目 - SpringDemo

四、添加数据库相关依赖

• 在pom.xml文件里添加相关依赖
  

<!--Spring数据库支持-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.25</version>
        </dependency>
        <!--数据库驱动工具包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <!--数据库连接池框架-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.16</version>
        </dependency>

五、创建用户实体类

• 在org.example.spring包里创建day06.bean子包，然后在子包里面创建User类

package org.example.spring.day06.bean;

import java.util.Date;

/**
 * 功能：用户实体类
 */
public class User {
    private int id;
    private String username;
    private String password;
    private String telephone;
    private Date registerTime;
    private int popedom;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }

    public Date getRegisterTime() {
        return registerTime;
    }

    public void setRegisterTime(Date registerTime) {
        this.registerTime = registerTime;
    }

    public int getPopedom() {
        return popedom;
    }

    public void setPopedom(int popedom) {
        this.popedom = popedom;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", telephone='" + telephone + '\'' +
                ", registerTime=" + registerTime +
                ", popedom=" + popedom +
                '}';
    }
}

六、创建用户数据访问接口

• 在org.example.spring包里创建day06.dao子包，在子包里创建UserDao接口

package org.example.spring.day06.dao;

import org.example.spring.day06.bean.User;

import java.util.List;

/**
 * 功能：用户数据访问接口
 */
public interface UserDao {
    int insert(User user);
    int deleteById(int id);
    int update(User user);
    User findById(int id);
    List<User> findAll();
    User login(String username, String password);
}

七、创建用户数据访问接口实现类

• 在org.example.spring.day06包里创建impl子包，然后在子包里创建UserDaoImpl类

package org.example.spring.day06.dao.impl;

import org.example.spring.day06.bean.User;
import org.example.spring.day06.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

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

// 用户数据访问接口实现类
@Repository("userDao")
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 插入用户记录
    @Override
    public int insert(User user) {
        // 定义SQL字符串
        String strSQL = "INSERT INTO t_user (username, password, telephone, " +
                "register_time, popedom) VALUES (?, ?, ?, ?, ?)";
        // 调用模板的更新方法执行SQL语句
        return jdbcTemplate.update(strSQL, user.getUsername(), user.getPassword(),
                user.getTelephone(), user.getRegisterTime(), user.getPopedom());
    }

    // 按编号删除用户记录
    @Override
    public int deleteById(int id) {
        // 定义SQL字符串
        String strSQL = "DELETE FROM t_user WHERE id = ?";
        // 调用模板的更新方法执行SQL语句
        return jdbcTemplate.update(strSQL, id);
    }

    // 更新用户记录
    @Override
    public int update(User user) {
        // 定义SQL字符串
        String strSQL = "UPDATE t_user SET username = ?, password = ?," +
                "telephone = ?, register = ?, popedom = ? WHERE id = ?";
        // 调用模板的更新方法执行SQL语句
        return jdbcTemplate.update(strSQL, user.getUsername(), user.getPassword(),
                user.getTelephone(), user.getRegisterTime(), user.getPopedom(), user.getId());
    }

    // 按编号查新用户记录
    @Override
    public User findById(int id) {
        // 定义SQL字符串
        String strSQL = "SELECT * FROM t_user WHERE id = ?";
        return jdbcTemplate.queryForObject(strSQL, new RowMapper<User>() {
            // 行映射方法（将用户表记录映射成用户实体）
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                // 创建用户对象
                User user = new User();
                // 从结果集获取字段值设置用户对象属性
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setTelephone(rs.getString("telephone"));
                user.setRegisterTime(rs.getTimestamp("register_time"));
                user.setPopedom(rs.getInt("popedom"));
                // 返回用户对象
                return user;
            }
        },id);
    }


    // 查询全部用户记录
    @Override
    public List<User> findAll() {
        String strSQL = "SELECT * FROM t_user";
        // 查询获取行列表
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(strSQL);
        // 创建用户列表
        List<User> users = new ArrayList<>();
        // 遍历行列表，生成用户列表
        for (Map<String, Object> row : rows) {
            // 创建用户对象
            User user = new User();
            // 从行获取列值设置用户对象属性
            user.setId((Integer) row.get("id"));
            user.setUsername((String) row.get("username"));
            user.setPassword((String) row.get("password"));
            user.setTelephone((String) row.get("telephone"));
            user.setRegisterTime((Date) row.get("register_time"));
            user.setPopedom((Integer) row.get("popedom"));
            // 将用户对象添加到用户列表
            users.add(user);
        }
        // 返回用户列表
        return users;
    }


    // 用户登录
    @Override
    public User login(String username, String password) {
        String strSQL = "SELECT * FROM t_user WHERE username = ? AND password = ?";
        return jdbcTemplate.queryForObject(strSQL, new RowMapper<User>() {
            // 行映射方法（将用户表记录映射成用户实体）
            public User mapRow(ResultSet rs, int row) throws SQLException {
                // 创建用户对象
                User user = new User();
                // 从结果集获取字段值设置用户对象属性
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setTelephone(rs.getString("telephone"));
                user.setRegisterTime(rs.getTimestamp("register_time"));
                user.setPopedom(rs.getInt("popedom"));
                // 返回用户对象
                return user;
            }
        }, username, password);
    }
}

八、创建用户服务类

• 在org.example.spring.day06包里创建service子包，然后在子包里创建UserService类

package org.example.spring.day06.service;

import org.example.spring.day06.bean.User;
import org.example.spring.day06.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

// 用户服务类
@Service("userService")
public class UserService {
    @Autowired
    private UserDao userDao;

    public int addUser(User user) {
        return userDao.insert(user);
    }

    public int deleteUserById(int id) {
        return userDao.deleteById(id);
    }

    public int updateUser(User user) {
        return userDao.update(user);
    }

    public User findUserById(int id) {
        return userDao.findById(id);
    }

    public List<User> findAllUsers() {
        return userDao.findAll();
    }

    public User login(String username, String password) {
        return userDao.login(username, password);
    }
}

九、创建数据库配置属性文件

• 在resources目录里创建数据库配置属性文件 - jdbc.properties

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/simonshop?useSSL=false
jdbc.username=root
jdbc.password=root

十、创建Spring配置文件

• 在resources里创建Spring配置文件 - spring-config.xml

<?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:component-scan base-package="org.example.spring.day06"/>
    <!--声明属性占位符-->
    <context:property-placeholder location="jdbc.properties"/>
    <!--定义数据源Bean-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <!--定义JDBC模板Bean-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

十一、创建用户服务测试类

• 在test/java目录的net.xxr.spring包里创建day06.service子包，然后在子包里创建TestUserService类

package net.xxr.spring.day06.service;

import org.junit.After;
import org.junit.Before;
import org.springframework.context.support.ClassPathXmlApplicationContext;

// 测试用户服务类
public class TestUserService {

    private UserService userService; // 用户服务对象
    private ClassPathXmlApplicationContext context; // 应用容器

    @Before
    public void init() {
        // 基于Spring配置文件创建应用容器
        context = new ClassPathXmlApplicationContext("jdbc/spring-config.xml");
        // 根据名称从应用容器里获取用户服务对象
        userService = (UserService) context.getBean("userService");
    }

    
    @After
    public void destroy() {
        // 关闭应用容器
        context.close();
    }
}

1、测试按编号查询用户方法

• 创建testFindUserById()方法

@Test
    public void testFindUserById() {
        int id = 1;
        try {
            User user = userService.findUserById(id);
            System.out.println(user);
        } catch (Exception e) {
            System.out.println("编号为[" + id + "]的用户未找到！");
        }
    }

• 运行结果
  

2、测试查询全部用户方法

• 创建testFindAllUsers()方法

@Test
    public void testFindAllUsers() {
        List<User> users = userService.findAllUsers();
        users.forEach(System.out::println);
    }

• 运行结果
  

3、测试添加用户方法

• 创建testAddUser()方法

@Test
    public void testAddUser() {
        User user = new User();
        user.setUsername("萌萌哒");
        user.setPassword("345678");
        user.setTelephone("15890903456");
        user.setRegisterTime(new Date());
        user.setPopedom(1);
        int count = userService.addUser(user);
        if (count > 0) {
            System.out.println("用户记录插入成功！");
        } else {
            System.out.println("用户记录插入失败！");
        }
    }

• 运行结果
  

4、测试更新用户方法

• 创建testUpdateUser()方法

@Test
    public void testUpdateUser() {
        int id = 5;
        User user = userService.findUserById(id);
        System.out.println("更新前:" + user);
        user.setUsername("娃哈哈");
        user.setPassword("999999");
        user.setTelephone("13867674560");
        user.setRegisterTime(new Date());
        user.setPopedom(0);
        int count = userService.addUser(user);
        if (count > 0) {
            System.out.println("用户记录更新成功！");
            System.out.println("更新后:" + user);
        } else {
            System.out.println("用户记录更新失败！");
        }
    }

• 运行结果
  

5、测试删除用户方法

• 创建testDeleteUserById()方法

@Test
    public void testDeleteUserById() {
        int id = 5;
        User user = userService.findUserById(id);
        System.out.println("待删记录:" + user);
        int count = userService.deleteUserById(id);
        if (count > 0) {
            System.out.println("用户记录删除成功!");
        } else {
            System.out.println("用户记录删除失败！");
        }
    }

• 运行结果
  

6、测试用户登录方法

• 创建testLogin()方法

@Test
    public void testLogin() {
        String username = "无心剑";
        String password = "12345";
        try {
            User user = userService.login(username, password);
            System.out.println("恭喜，" + username + "，登录成功！");
        } catch (Exception e) {
            System.out.println("遗憾，" + username + "，登录失败！");
        }
    }

• 运行结果