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

「爱情、让人受尽委屈。」 2022-08-30 08:46 139阅读 0赞

1、新建SpringBoot项目

1.1、新建SpringBoot项目

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BscGxkb2c_size_16_color_FFFFFF_t_70][]  
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>

<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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BscGxkb2c_size_16_color_FFFFFF_t_70 1][]  
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  
浏览器输出如下，表示成功：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BscGxkb2c_size_16_color_FFFFFF_t_70 2][]  
查看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\]\]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BscGxkb2c_size_16_color_FFFFFF_t_70]: /images/20220829/6420e28d8710411e9e03e4bc52777efc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BscGxkb2c_size_16_color_FFFFFF_t_70 1]: /images/20220829/141efe910abd409f97acbd2f46a85363.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BscGxkb2c_size_16_color_FFFFFF_t_70 2]: /images/20220829/542ac3e00b844b82ba149e53c4106615.png