SpringBoot+MyBatis+Druid+MySQL实现数据库操作

1、新建SpringBoot项目

1.1、新建SpringBoot项目

使用SpringBoot初始化器，或者通过新建Maven项目的方式，新建一个SpringBoot项目，项目基本骨架如下：

1.2、配置pom.xml 

在pom.xml文件中，引入必要的依赖包
mybatis-spring-boot-starter --引入mybatis的依賴
druid-spring-boot-starter --引入数据库连接池
mysql-connector-java --引入数据库驱动

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

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

        <!-- 引入druid的数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <!-- 引入数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.28</version>
            <scope>runtime</scope>
        </dependency>

1.3、配置properties

配置application.properties
主要用来配置本地的数据库连接，web应用的监听端口等，以及mapper的扫描路径
spring.application.name=druid_study
server.port=8080

spring.datasource.url=jdbc:mysql://localhost:3306/david?useUnicode=true&characterEncoding=UTF8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.initial-size=50
spring.datasource.druid.min-idle=50
spring.datasource.druid.max-active=100
# 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000

#扫描数据库的mapper.xml文件
mybatis.mapper-locations=classpath*:mapper/*.xml

2、编写代码

2.1、创建表

创建数据库表：t_student_info
create table if not exists david.t_student_info
(
    id varchar(20) not null comment '主键'
        primary key,
    name varchar(50) default ' ' null comment '姓名',
    age decimal(3) default 20 null comment '年龄',
    sex char default 'M' null comment '性别 M-男，W-女',
    phone varchar(20) null comment '手机号',
    email varchar(30) null comment '电子邮箱',
    rsv1 varchar(100) null comment '备注字段'
)
comment '学生信息表';
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10001', '张三', 20, 'M', '13988887777', 'zhangsan@163.com', '备注');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10002', '李四', 18, 'W', '15988887777', 'lisi@163.com', '李四是张三的妹妹');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10003', '苍老师', 18, 'W', '15866668888', 'canglaoshi@163.com', '这里是苍老师的备注');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10004', '刘备', 30, 'M', '18666666666', 'liubei@163.com', '我是刘皇叔，专门卖草鞋');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10005', '关羽', 29, 'M', '18655555555', 'guanyu@163.com', '我是关羽，卖绿豆的');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10006', ' 孙悟空', 500, 'M', '19100002222', 'wukong@163.com', '我是孙悟空，我有金箍棒');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10007', ' 猪八戒', 488, 'M', '18900003333', 'bajie@163.com', '猪八戒');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10008', ' 沙僧', 200, 'M', '18678652388', 'shaseng@163.com', '我是沙僧');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10009', ' 唐僧', 25, 'M', '18766809012', 'shaseng@163.com', '我是唐僧，你们都是弟弟');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10010', ' 老树龙井', 20, 'W', '15988801234', 'longjing@163.com', 'laoshulongjing');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10011', ' 苏檀儿', 22, 'W', '18809826678', 'sutaner@163.com', '苏檀儿');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10012', ' 宁毅', 20, 'M', '15900008888', 'ningyi@163.com', '宁毅');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10013', ' 小婵', 18, 'W', '18566780092', 'xiaochan@163.com', '我是小婵');

2.2、生成数据库实体，Dao，Mapper等

根据数据库的结构，生成数据库实体：StudentInfo
数据库Dao接口：StudentInfoDAO
mapper文件：StudentInfoDAO.xml

2.3、编写Service及ServiceImpl类

 

编写服务类：StudentInfoService，增加一个服务方法findAll()
StudentInfoService
/**
 * @author zhang_wei
 * @version 1.0.0
 * @Classname StudentInfoService
 * @Date 2021/2/26 15:35
 * @Created by zhang_wei
 * @since 1.0.0
 */
public interface StudentInfoService {

    /**
     * 查询所有
     * @return
     */
    List<StudentInfo> findAll();

}

编写服务实现类：StudentInfoServiceImpl
StudentInfoServiceImpl
/**
 * @author zhang_wei
 * @version 1.0.0
 * @Classname StudentInfoServiceImpl
 * @Date 2021/2/26 15:35
 * @Created by zhang_wei
 * @since 1.0.0
 */
@Service
public class StudentInfoServiceImpl implements StudentInfoService {

    Logger logger = LoggerFactory.getLogger(StudentInfoServiceImpl.class);

    @Autowired
    StudentInfoDAO studentInfoDAO;

    @Override
    public List<StudentInfo> findAll() {
        return studentInfoDAO.findAll();
    }
}

2.4、编写Dao中的方法，及Mapper.xml中的SQL

StudentInfoDAO中增加findAll方法， 如下：
    /**
     * 查询所有
     * @return
     */
    List<StudentInfo> findAll();

StudentInfoDAO.xml 增加findAll的SQL，如下：
   <select id="findAll" parameterType="java.lang.String" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from t_student_info
    </select>

2.5、编写Controller，拦截web请求

编写StudInfoController类，增加findAll方法查询所有的数据，
package com.iambest.druid.controller;

import com.iambest.druid.entity.StudentInfo;
import com.iambest.druid.model.PageRequest;
import com.iambest.druid.model.PageResult;
import com.iambest.druid.service.StudentInfoService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;

/**
 * @author zhang_wei
 * @version 1.0.0
 * @Classname StudInfoController
 * @Date 2021/2/26 11:07
 * @Created by zhang_wei
 * @since 1.0.0
 */
@Controller
public class StudInfoController {

    Logger logger = LoggerFactory.getLogger(StudInfoController.class);

    @Autowired
    StudentInfoService studentInfoService;

    @RequestMapping("/getAll")
    @ResponseBody
    public List<StudentInfo> getAllStudentInfos() {
        List<StudentInfo> result = studentInfoService.findAll();
        logger.info("result={}", result);
        return result;
    }

}

2.6、修改Application主程序，增加Dao的扫描

修改DruidApplication类，增加MapperScan扫描，扫描我们的dao所在的包
package com.iambest.druid;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;

/**
 * @author zhang_wei
 * @version 1.0.0
 * @Classname DruidApplication
 * @Date 2021/2/26 9:17
 * @Created by zhang_wei
 * @since 1.0.0
 */
@MapperScan(basePackages = "com.iambest.druid.dao")
@SpringBootApplication
public class DruidApplication {

    public static void main(String[] args) {
        SpringApplication.run(DruidApplication.class, args);
    }
}

3、启动服务，并测试

3.1、启动服务

启动服务，控制台输出如下，表示服务启动成功：
2021-03-03 22:07:05.218 [main] INFO  AnnotationMBeanExporter [] - Bean with name 'statFilter' has been autodetected for JMX exposure
2021-03-03 22:07:05.218 [main] INFO  AnnotationMBeanExporter [] - Bean with name 'dataSource' has been autodetected for JMX exposure
2021-03-03 22:07:05.222 [main] INFO  AnnotationMBeanExporter [] - Located MBean 'dataSource': registering with JMX server as MBean [com.alibaba.druid.spring.boot.autoconfigure:name=dataSource,type=DruidDataSourceWrapper]
2021-03-03 22:07:05.223 [main] INFO  AnnotationMBeanExporter [] - Located MBean 'statFilter': registering with JMX server as MBean [com.alibaba.druid.filter.stat:name=statFilter,type=StatFilter]
2021-03-03 22:07:05.231 [main] INFO  Http11NioProtocol [] - Starting ProtocolHandler ["http-nio-8080"]
2021-03-03 22:07:05.240 [main] INFO  NioSelectorPool [] - Using a shared selector for servlet write/read
2021-03-03 22:07:05.249 [main] INFO  TomcatEmbeddedServletContainer [] - Tomcat started on port(s): 8080 (http)
2021-03-03 22:07:05.253 [main] INFO  DruidApplication [] - Started DruidApplication in 3.102 seconds (JVM running for 3.783)

3.2、测试服务

打开浏览器，输入：http://localhost:8080/getAll
浏览器输出如下，表示成功：

查看IDEA的控制台，输出如下：
2021-03-03 22:11:12.157 [http-nio-8080-exec-7] DEBUG findAll [a77726b7-fb26-4fae-935d-bd3f00a7fc90] - ==>  Preparing: select id, `name`, age, sex, phone, email, rsv1 from t_student_info 
2021-03-03 22:11:12.157 [http-nio-8080-exec-7] DEBUG findAll [a77726b7-fb26-4fae-935d-bd3f00a7fc90] - ==> Parameters: 
2021-03-03 22:11:12.160 [http-nio-8080-exec-7] DEBUG findAll [a77726b7-fb26-4fae-935d-bd3f00a7fc90] - <==      Total: 13
2021-03-03 22:11:12.160 [http-nio-8080-exec-7] INFO  StudInfoController [a77726b7-fb26-4fae-935d-bd3f00a7fc90] - result=[StudentInfo [Hash = 46730193, id=10001, name=张三, age=20, sex=M, phone=13988887777, email=zhangsan@163.com, rsv1=备注, serialVersionUID=1], StudentInfo [Hash = 46730194, id=10002, name=李四, age=18, sex=W, phone=15988887777, email=lisi@163.com, rsv1=李四是张三的妹妹, serialVersionUID=1], StudentInfo [Hash = 46730195, id=10003, name=苍老师, age=18, sex=W, phone=15866668888, email=canglaoshi@163.com, rsv1=这里是苍老师的备注, serialVersionUID=1], StudentInfo [Hash = 46730196, id=10004, name=刘备, age=30, sex=M, phone=18666666666, email=liubei@163.com, rsv1=我是刘皇叔，专门卖草鞋, serialVersionUID=1], StudentInfo [Hash = 46730197, id=10005, name=关羽, age=29, sex=M, phone=18655555555, email=guanyu@163.com, rsv1=我是关羽，卖绿豆的, serialVersionUID=1], StudentInfo [Hash = 46730198, id=10006, name= 孙悟空, age=500, sex=M, phone=19100002222, email=wukong@163.com, rsv1=我是孙悟空，我有金箍棒, serialVersionUID=1], StudentInfo [Hash = 46730199, id=10007, name= 猪八戒, age=488, sex=M, phone=18900003333, email=bajie@163.com, rsv1=猪八戒, serialVersionUID=1], StudentInfo [Hash = 46730200, id=10008, name= 沙僧, age=200, sex=M, phone=18678652388, email=shaseng@163.com, rsv1=我是沙僧, serialVersionUID=1], StudentInfo [Hash = 46730201, id=10009, name= 唐僧, age=25, sex=M, phone=18766809012, email=shaseng@163.com, rsv1=我是唐僧，你们都是弟弟, serialVersionUID=1], StudentInfo [Hash = 46730223, id=10010, name= 老树龙井, age=20, sex=W, phone=15988801234, email=longjing@163.com, rsv1=laoshulongjing, serialVersionUID=1], StudentInfo [Hash = 46730224, id=10011, name= 苏檀儿, age=22, sex=W, phone=18809826678, email=sutaner@163.com, rsv1=苏檀儿, serialVersionUID=1], StudentInfo [Hash = 46730225, id=10012, name= 宁毅, age=20, sex=M, phone=15900008888, email=ningyi@163.com, rsv1=宁毅, serialVersionUID=1], StudentInfo [Hash = 46730226, id=10013, name= 小婵, age=18, sex=W, phone=18566780092, email=xiaochan@163.com, rsv1=我是小婵, serialVersionUID=1]]