springBoot学习笔记十：JdbcTemplate

一、项目说明

项目环境：jdk1.8+tomcat8+idea2018+mysql5.7+navicat

源代码github地址：

实现目标：通过整合jdbcTemplate，实现数据库的增，删，改，查操作。

二、整合说明

（1）通过idea等方式创建springBoot模板项目

（2）在pom.xml文件中添加相关依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--jdbc依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!--mysql驱动依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--druid连接池依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.28</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

（3）在 application.properties中添加数据库配置

#mysql数据库配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/root?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=root

（4）数据库创建user表

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50725
Source Host           : localhost:3306
Source Database       : root

Target Server Type    : MYSQL
Target Server Version : 50725
File Encoding         : 65001

Date: 2019-06-02 11:03:19
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) DEFAULT NULL,
  `SEX` varchar(255) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '丽丽', '女', '19');
INSERT INTO `user` VALUES ('2', '麻子', '男', '18');

（5）在entity中创建User

package com.example.entity;

/**
 * 用户实体类
 */
public class User {
    private Integer id;
    private String name;
    private String sex;
    private Integer age;

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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 Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public User(Integer id, String name, String sex, Integer age) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
    }

    public User() {
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                '}';
    }
}

（6）在service下创建UserDao

package com.example.service;

import com.example.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 用户dao
 */
@Repository
public class UserDao {
    //注入jdbcTemplate
    @Autowired
    JdbcTemplate jdbcTemplate;

    public int addUser(User user){
        return jdbcTemplate.update("insert into user(name, sex, age) values(?, ?, ?)", user.getName(), user.getSex(), user.getAge());
    }

    public int updateUser(User user){
        return jdbcTemplate.update("update user set name = ?, sex = ?, age = ? where id = ? ", user.getName(), user.getSex(), user.getAge(), user.getId());
    }

    public int deleteUser(Integer id){
        return jdbcTemplate.update("delete from user where id = ? ", id);
    }

    public User getUserById(Integer id){
        return jdbcTemplate.queryForObject("select * from user where id = ?", new BeanPropertyRowMapper<>(User.class), id);
    }

    public List<User> getUserList(){
        return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
    }
}

（7）在impl下创建UserService

package com.example.service.impl;

import com.example.entity.User;
import com.example.service.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 用户service
 */
@Service
public class UserService {
    //注入用户dao
    @Autowired
    UserDao userDao;

    /**
     * 新增用户
     * @param user
     * @return
     */
    public int addUser(User user){
        return userDao.addUser(user);
    }

    /**
     * 修改用户
     * @param user
     * @return
     */
    public int updateUser(User user){
        return userDao.updateUser(user);
    }

    /**
     * 删除用户
     * @param id
     * @return
     */
    public int deleteUser(Integer id){
        return userDao.deleteUser(id);
    }

    /**
     * 根据id查询用户
     * @param id
     * @return
     */
    public User getUserById(Integer id){
        return userDao.getUserById(id);
    }

    /**
     * 查询用户列表
     * @return
     */
    public List<User> getUserList(){
        return userDao.getUserList();
    }
}

（8）在controller下创建UserController

package com.example.controller;

import com.example.entity.User;
import com.example.service.impl.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * 用户controller
 */
@RestController
public class UserController {
    @Autowired
    UserService userService;

    /**
     * jdbcTemplate测试
     */
    @GetMapping("/userOptions")
    public void userOptions(){
        //查询所有用户
        List<User> userList = userService.getUserList();
        System.out.println("查询所有用户=============>" + userList);

        User user = new User();
        user.setName("张三");
        user.setAge(18);
        user.setSex("男");

        //新增用户
        int i = userService.addUser(user);
        System.out.println("新增用户=============>" + i);

        User user1 = new User();
        user1.setId(1);
        user1.setName("张三1");
        user1.setAge(181);
        user1.setSex("男1");

        //更新id等于1的用户
        int j = userService.updateUser(user1);
        System.out.println("更新用户=============>" + j);

        //查询id等于1的用户
        User user2 = userService.getUserById(1);
        System.out.println("根据id查询用户=============>" + user2);

        //删除id等于2的用户
        int k = userService.deleteUser(2);
        System.out.println("删除用户=============>" + k);

        //查询所有用户
        List<User> userList1 = userService.getUserList();
        System.out.println("查询所有用户=============>" + userList1);
    }
}

（9）测试接口

转载于:https://my.oschina.net/tij/blog/3057347