Spring Boot基础学习笔记06

一.JdbcTemplate案例演示

（一）创建数据库与表

1、创建数据库 - simonshop

2、创建用户表 - 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=utf8;

3、在用户表里插入记录

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');

4、查看用户表内容

（二）打开项目【SpringDemo2021】

（三）在pom文件里添加数据库相关依赖

<!--Spring数据库支持-->                                  
<dependency>                                        
    <groupId>org.springframework</groupId>          
    <artifactId>spring-jdbc</artifactId>            
    <version>${spring.version}</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.1.24</version>                       
</dependency>       

（四）创建用户实体类 - User

.在net.hw.spring包里创建lesson06.bean子包，然后在子包里面创建User类

package net.hw.spring.lesson06.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 +
                '}';
    }
}

（五）创建用户数据访问接口 - UserDao

package net.hw.spring.lesson06.dao;

import net.hw.spring.lesson06.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);
}

（七）创建用户服务类 - UserService

package net.hw.spring.lesson06.service;

import net.hw.spring.lesson06.bean.User;
import net.hw.spring.lesson06.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);
    }
}

（八）创建数据库配置属性文件 - jdbc.properties

（九）创建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="net.hw.spring.lesson06"/>
    <!--声明属性占位符-->
    <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>

(十）创建用户服务测试类 - TestUserService

1、测试按编号查询用户方法 - testFindUserById()

2、测试查询全部用户方法 - testFindAllUsers()

3、测试添加用户方法 - testAddUser()

4、测试更新用户方法 - testUpdateUser()

5.测试删除用户方法 - testDeleteUserById()

6、测试用户登录方法 - testLogin()

运行测试方法，查看结果