第三篇：SpringBoot用JdbcTemplates访问Mysql

此篇博文，将通过jdbc访问关系型数据库mysql，通过搜spring的jdbcTemplate去访问。

创建数据库

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'test', '11');
INSERT INTO `student` VALUES ('2', 'test1', '30');

创建工程：

引入的依赖如下

<?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>

    <groupId>com.wujie</groupId>
    <artifactId>spring-jdbc-template</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>spring-jdbc-template</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

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

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>

    </dependencies>

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


</project>

修改配置文件如下：

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/my
    username: root  #修改为自己的用户名密码
    password: root

创建一个实体类:

public class Student {
    /**
     * id
     */
    private Integer id;
    /**
     * 姓名
     */
    private String name;
    /**
     * 年龄
     */
    private Integer age;
    //省略getter、setter
}

创建一个Dao层：

public interface StudentDao {
    int add (Student student);
    int update(Student student);
    int delete(Integer id);
    Student findStudentById(Integer id);
    List<Student> findStudent();
}

实现Dao层：

@Repository
public class StudentDaoImpl implements StudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int add(Student student) {
        return jdbcTemplate.update("insert into student(name, age) values(?, ?)",student.getName(),student.getAge());
    }

    @Override
    public int update(Student student) {
        return jdbcTemplate.update("update student set name = ?,age=? where id=?",student.getName(),student.getAge(),student.getId());
    }

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

    @Override
    public Student findStudentById(Integer id) {
        List<Student> list = jdbcTemplate.query("select * from student where id = ?", new Object[]{id}, new BeanPropertyRowMapper<>(Student.class));
        if (list != null && list.size() >0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<Student> findStudent() {
        List<Student> list = jdbcTemplate.query("select * from student", new Object[]{}, new BeanPropertyRowMapper<>(Student.class));
        if (list != null && list.size() >0) {
            return list;
        }
        return null;
    }
}

Service层：

public interface StudentService {
    int add (Student student);
    int update(Student student);
    int delete(Integer id);
    Student findStudentById(Integer id);
    List<Student> findStudent();
}

实现service层：

@Service
public class StudentServiceImpl implements StudentService{

    @Autowired
    private StudentDao studentDao;
    @Override
    public int add(Student student) {
        return studentDao.add(student);
    }

    @Override
    public int update(Student student) {
        return studentDao.update(student);
    }

    @Override
    public int delete(Integer id) {
        return studentDao.delete(id);
    }

    @Override
    public Student findStudentById(Integer id) {
        return studentDao.findStudentById(id);
    }

    @Override
    public List<Student> findStudent() {
        return studentDao.findStudent();
    }
}

创建Controller

@RestController
@RequestMapping("student")
public class StudentController {
    @Autowired
    private StudentService studentService;
    @RequestMapping("add")
    public String addStudent(Student student){
        int t = studentService.add(student);
        if (t == 1){
            return student.toString();
        }else {
            return "增加失败";
        }
    }

    @RequestMapping("update/{id}")
    public String update(Student student,@RequestParam("id") Integer id){
        student.setId(id);
        int t = studentService.update(student);
        if(t == 1){
            return student.toString();
        }else {
            return "更新失败";
        }
    }

    @RequestMapping("{id}")
    public Student getStudentById(@RequestParam("id") Integer id){
        Student student = studentService.findStudentById(id);
        if(student != null){
            return student;
        }else {
            return null;
        }
    }

    @RequestMapping("list")
    public List<Student> getStudent(){
        return studentService.findStudent();
    }
}

欢迎关注我的公众号我们一起学习：

源码下载

文章推荐：

springboot入门：springboot初级入门