6.从零开始学springboot-连接数据库的几种方式-JDBC

前言

上一节实现了JPA操作mysql，这一节我们实现jdbc也就是jdbcTemplate操作mysql。

创建空项目

首先我们使用IDEA创建一个基础的springboot2.0的项目（创建的过程略，可以参考前几章），无需加载任何依赖（依赖还是手动加载感悟更深）。

添加依赖

pom.xml：

        <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-jdbc</artifactId>
        </dependency>

如图

添加配置

application.yml:

spring:
  datasource:
    url: jdbc:mysql://192.168.145.131:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

如图

建库

创建test库，建表：

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `person` VALUES ('1', '1', '1');
INSERT INTO `person` VALUES ('2', '2', '2');
INSERT INTO `person` VALUES ('3', '3', '3');
INSERT INTO `person` VALUES ('4', '4', '4');
INSERT INTO `person` VALUES ('5', '5', '5');
INSERT INTO `person` VALUES ('6', '6', '6');
INSERT INTO `person` VALUES ('7', '7', '7');
INSERT INTO `person` VALUES ('8', '8', '8');
INSERT INTO `person` VALUES ('9', '9', '9');
INSERT INTO `person` VALUES ('10', '10', '10');
INSERT INTO `person` VALUES ('11', '11', '11');

目录结构

如图，请自行建立对应的package和class

完善

entity/Person:

package com.mrcoder.sbjdbc.entity;

public class Person {

    private Integer id;
    private String name;
    private Integer age;

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

    public void setName(String name) {
        this.name = name;
    }

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

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

    public Integer getId() {
        return id;
    }

    public Integer getAge() {
        return age;
    }

    public String getName() {
        return name;
    }
}

dao/PersonDao:

package com.mrcoder.sbjdbc.dao;

import com.mrcoder.sbjdbc.entity.Person;
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;

@Repository
public class PersonDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Person> getPersonList() {

        List<Person> list = jdbcTemplate.query("select * from person", new Object[]{}, new BeanPropertyRowMapper(Person.class));
        if (list != null && list.size() > 0) {
            return list;
        } else {
            return null;
        }

    }
}

service/PersonService:

package com.mrcoder.sbjdbc.service;

import com.mrcoder.sbjdbc.entity.Person;

import java.util.List;

public interface  PersonService {
    List<Person> getPersonList();
}

service/impl/PersonServiceImpl:

package com.mrcoder.sbjdbc.service.impl;

import com.mrcoder.sbjdbc.dao.PersonDao;
import com.mrcoder.sbjdbc.entity.Person;
import com.mrcoder.sbjdbc.service.PersonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class PersonServiceImpl implements PersonService {
    @Autowired
    PersonDao personDao;

    @Override
    public List<Person> getPersonList() {
        return personDao.getPersonList();
    }
}

controller/Persontroller:

package com.mrcoder.sbjdbc.controller;

import com.mrcoder.sbjdbc.entity.Person;
import com.mrcoder.sbjdbc.service.PersonService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class PersonController {
    @Autowired
    PersonService personService;

    @RequestMapping(value = "/list", method = RequestMethod.GET)
    public List<Person> getPersonList() {
        return personService.getPersonList();
    }

}

运行

项目地址

https://github.com/MrCoderStack/SpringBootDemo/tree/master/sb-jdbc

https://gitee.com/MrCoderStack/SpringBootDemo/tree/master/sb-jdbc

请关注我的订阅号