简介: 使用 mybatis 连接 mysql 数据库, 一套简单的增删改查流程, 前台用 bootstrap, bootstrap-table 框架, 最后用 druid 监控数据库连接情况
项目源码:https://github.com/y369q369/springBoot.git -> DruidMybatisMysql
私聊QQ: 1486866853
1.demo的完整结构
2. pom.xml 依赖 和 application.yml 配置
1) pom.xml 主要 依赖 mysql-connector-java , mybatis-spring-boot-starter ,druid-spring-boot-starter
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?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.3.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.demo</groupId> <artifactId>DruidMybatisMysql</artifactId> <version>0.0.1-SNAPSHOT</version> <name>DruidMybatisMysql</name> <description>使用druid管理数据库,mybatis连接mysql数据库</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.0</version> </dependency> <!-- web依赖,包含servlet,内置tomcat等 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- thymeleaf模板依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <!-- mysql依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- mybatis依赖 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!-- druid依赖包,配合springBoot项目使用 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.14</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
2) application配置
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
# springBoot内置容器的配置 server: # 端口号 port: 8086 servlet: # 项目前缀 context-path: /dataSource spring: # 数据源配置 datasource: # mysql数据库配置 driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: ok # druid配置 druid: # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 filters: config,wall,stat # 初始化数量 initialSize: 5 # 最小连接池数量 minIdle: 2 # 最大连接池数量 maxActive: 20 # 连接超时时间 maxWait: 60000 # 打开psCache, 对支持游标的数据库性能提升巨大 poolPreparedStatements: true # 指定每个连接PsCache的大小 maxPoolPreparedStatementPerConnectionSize: 20 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 timeBetweenEvictionRunsMillis: 6000 # 指定一个空闲连接最少空闲多久后可被清除,单位是毫秒 minEvictableIdleTimeMillis: 300000 # 验证数据库连接的查询语句 validationQuery: select 'x' # 当连接空闲时,是否执行连接测试 testWhileIdle: true # 当从连接池借用连接时,是否测试该连接 testOnBorrow: false # 在连接归还到连接池时是否测试该连接 testOnReturn: false # 打开mergeSql,慢sql记录 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # thymeleaf 模板引擎配置 thymeleaf: cache: false # thymeleaf模板对html5没有结束符的标签解决 mode: LEGACYHTML5 # thymeleaf修饰的动态页面 自定义根目录(默认就是templates) prefix: classpath:/templates/ # mybatis配置 mybatis: # 映射xml的文件位置 mapper-locations: classpath:mybatis/*.xml # 实体类所在包,简化xml中resultMap中实体类的全类名写法 type-aliases-package: demo.domain
3.代码 : 建表sql , 后台代码 , mapper.xml 和 前台html
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
create table `test`( `test_id` varchar(36) not null comment '唯一id' primary key, `test_password` varchar(32) not null comment '密码', `test_name` varchar(32) not null comment '名称' ) comment '测试'
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package demo.domain; /** * @author GrassPrince * @Da2019年4月3日 2019年4月3日 - 下午8:09:33 * @Description Test */ public class Test { /** 唯一id */ private String testId; /** 密码 */ private String testPassword; /** 名称 */ private String testName; public Test(String testId, String testPassword, String testName) { this.testId = testId; this.testPassword = testPassword; this.testName = testName; } public Test() { } public String getTestId() { return testId; } public void setTestId(String testId) { this.testId = testId; } public String getTestPassword() { return testPassword; } public void setTestPassword(String testPassword) { this.testPassword = testPassword; } public String getTestName() { return testName; } public void setTestName(String testName) { this.testName = testName; } @Override public String toString() { return "Test [testId=" + testId + ", testPassword=" + testPassword + ", testName=" + testName + "]"; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package demo.controller; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.DeleteMapping; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.PutMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.servlet.ModelAndView; import demo.domain.Test; import demo.service.TestService; /** * @author GrassPrince * @Da2019年4月3日 2019年4月3日 - 下午7:42:54 * @Description 测试 controller : 增删改查 */ @Controller @RequestMapping("/test") public class TestController { @Autowired private TestService testService; // 跳转到测试页面 @GetMapping("/testHtml") public ModelAndView testHtml() { return new ModelAndView("test"); } // 根据testId查询test @PostMapping("/queryById") @ResponseBody public List<Test> queryById(@RequestParam ("testId") String testId) { return testService.queryById(testId); } // 根据testId删除test @DeleteMapping("/deleteById") @ResponseBody public String deleteById(@RequestParam("testId") String testId) { Integer deleteNum = testService.deleteById(testId); if(deleteNum == 0) { return "删除失败"; } return "删除成功"; } //批量删除 @DeleteMapping("/deleteIds") @ResponseBody public String deleteIds(@RequestParam("testIds") String testIds) { Integer deleteNum = testService.deleteIds(testIds); if(deleteNum == 0) { return "删除失败"; } return "删除成功"; } // 修改test @PutMapping("/updateTest") @ResponseBody public String updateTest(Test test) { Integer updateNum = testService.updateTest(test); if(updateNum == 0) { return "更新失败"; } return "更新成功"; } // 新建一个test @PostMapping("/add") @ResponseBody public String add(Test test) { Integer addNum = testService.add(test); if(addNum == 0) { return "新增失败"; } return "新增成功"; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package demo.service; import java.util.List; import demo.domain.Test; /** * @author GrassPrince * @Da2019年4月3日 2019年4月3日 - 下午7:50:50 * @Description 测试的service接口 */ public interface TestService { /** 根据testId查询test */ List<Test> queryById(String testId); /** 根据testId删除test */ Integer deleteById(String testId); /** 批量删除 */ Integer deleteIds(String testIds); /** 修改test */ Integer updateTest(Test test); /** 新增test */ Integer add(Test test); }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package demo.service.impl; import java.util.List; import java.util.UUID; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import demo.domain.Test; import demo.mapper.TestMapper; import demo.service.TestService; /** * @author GrassPrince * @Da2019年4月3日 2019年4月3日 - 下午7:51:26 * @Description 测试的 service实现类 */ @Service public class TestServiceImpl implements TestService{ @Autowired private TestMapper testMapper; /** 根据testId查询test */ @Override public List<Test> queryById(String testId) { return testMapper.queryById(testId); } /** 根据testId删除test */ @Override public Integer deleteById(String testId) { return testMapper.deleteById(testId); } /** 批量删除 */ @Override public Integer deleteIds(String testIds) { String[] deleteIds = testIds.split(","); return testMapper.deleteIds(deleteIds); } /** 修改test */ @Override public Integer updateTest(Test test) { return testMapper.updateTest(test); } /** 新增test */ @Override public Integer add(Test test) { // 用uuid设置随机的主键id test.setTestId(UUID.randomUUID().toString().replace("-", "")); return testMapper.add(test); } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package demo.mapper; import java.util.List; import org.apache.ibatis.annotations.Param; import demo.domain.Test; /** * @author GrassPrince * @Da2019年4月3日 2019年4月3日 - 下午7:53:50 * @Description 测试的mapper */ public interface TestMapper { /** 根据testId查询test */ List<Test> queryById(@Param("testId") String testId); /** 根据testId删除test */ Integer deleteById(@Param("testId") String testId); /** 批量删除 */ Integer deleteIds(@Param("testIds") String[] deleteIds); /** 修改test */ Integer updateTest(Test test); /** 新增test */ Integer add(Test test); }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- 对应mapper接口的全类名 --> <mapper namespace="demo.mapper.TestMapper"> <!-- domain实体类 与 数据库表 做映射, type -> 实体类全类名,由于配置过type-aliases-package故只需配置类名 , id -> 这个resultMap 的唯一引用id --> <resultMap type="Test" id="testMap"> <id property="testId" column="test_id"/> <result property="testPassword" column="test_password"/> <result property="testName" column="test_name"/> </resultMap> <!-- id:对应mapper接口的方法名, resultMap: 对应定义的上方的resultMap,返回对应的实体类 --> <!-- 返回值会自动根据对象数量映射成单个对象或对象集合 --> <!-- mapper接口中有参数的方法:已经在接口中通过注解的方式传入,此处不需再次接收 --> <select id="queryById" resultMap="testMap"> select * from test <where> <if test="testId != null and testId != ''"> test_id = #{testId} </if> </where> </select> <delete id="deleteById"> delete from test where test_id = #{testId} </delete> <delete id="deleteIds"> delete from test <where> <if test="testIds != null and testIds.length > 0"> test_id in <foreach collection="testIds" item="testId" open="(" close=")" separator=","> #{testId} </foreach> </if> </where> </delete> <update id="updateTest"> update test set test_password = #{testPassword}, test_name = #{testName} where test_id = #{testId} </update> <insert id="add"> insert into test values(#{testId}, #{testPassword}, #{testName}) </insert> </mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package demo; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication @MapperScan("demo.mapper") public class DruidMybatisMysqlApplication { public static void main(String[] args) { SpringApplication.run(DruidMybatisMysqlApplication.class, args); } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<!DOCTYPE html> <html xmlns:th="http://www.thymeleaf.org"> <head> <title>查询</title> <meta name="keywords" content="keyword1,keyword2,keyword3"> <meta name="description" content="this is my page"> <meta name="content-type" content="text/html; charset=UTF-8"> <!-- springEL的引入 样式格式 --> <!-- bootstrap的样式 --> <link rel="stylesheet" th:href="@{/bootstrap-4.3.1-dist/css/bootstrap.css}"/> <!-- bootstrap-table的样式 --> <link rel="stylesheet" th:href="@{/bootstrap-table-dist/bootstrap-table.css}"/> </head> <body> <div style="text-align: center; padding-top: 80px"> <h1>测试查询</h1> <div class="row" style="margin-top: 50px"> <div class="col-md-4 text-right"> <label for="testId">id:</label> </div> <div class="col-md-2"> <input type="text" id="testId" class="form-control"> </div> <div class="col-md-3"> <button class="btn btn-primary" onclick="init()">查询</button> <button class="btn btn-success" onClick="AddModel()">新增</button> <button class="btn btn-warning" onClick="deleteIds()">批量删除</button> </div> </div> <div style="margin-top: 20px; padding: 0 10%;"> <table id="testTable" class="table table-bordered table-striped table-hover"></table> </div> </div> <!-- 新增/修改test的公用模态框 --> <div id="addTestModel" class="modal fade" tabindex="-1" role="dialog" aria-hidden="true"> <div class="modal-dialog" role="document"> <div class="modal-content"> <div class="modal-header"> <h5 class="modal-title" id="title"></h5> <button type="button" class="close" data-dismiss="modal" aria-label="true"> <span aria-hidden="true">×</span> </button> </div> <div class="modal-body"> <div id="operateId" class="input-group input-group-lg" style="margin:10px 0"> <div class="input-group-prepend" > <label class="input-group-text" for="id">testId</label> </div> <input type="text" id="id" readonly class="form-control" aria-label="Large" aria-describedby="inputGroup-sizing-sm" > </div> <div class="input-group input-group-lg" style="margin:10px 0"> <div class="input-group-prepend"> <label class="input-group-text" for="name">testName</label> </div> <input type="text" id="name" class="form-control" aria-label="Large" aria-describedby="inputGroup-sizing-sm"> </div> <div class="input-group input-group-lg" style="margin:10px 0"> <div class="input-group-prepend"> <label class="input-group-text" for="password">testPassword</label> </div> <input type="text" id="password" class="form-control" aria-label="Large" aria-describedby="inputGroup-sizing-sm"> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-info" id="updateButton" onClick="operateTest('updateTest', 'put')">修改</button> <button type="button" class="btn btn-success" id="addButton" onClick="operateTest('add', 'post')">新增</button> <button type="button" class="btn btn-secondary" data-dismiss="modal">关闭</button> </div> </div> </div> </div> <!-- =============================================================== --> <!-- jquery的js脚本 --> <script type="text/javascript" th:src="@{/jquery-3.3.1.js}"></script> <!-- bootstrap的js脚本 --> <script type="text/javascript" th:src="@{/bootstrap-4.3.1-dist/js/bootstrap.js}"></script> <!-- bootstrap-table的js脚本 --> <script type="text/javascript" th:src="@{/bootstrap-table-dist/bootstrap-table.js}"></script> <!-- bootstrap-table的中文js脚本 --> <script type="text/javascript" th:src="@{/bootstrap-table-dist/locale/bootstrap-table-zh-CN.js}"></script> <!-- js通过内联方式获取后台的request数据 --> <script type="text/javascript"> $(function(){ init(); }) function init() { $("#testTable").bootstrapTable('destroy'); $("#testTable").bootstrapTable({ method:"post", contentType : "application/x-www-form-urlencoded", //springmvc 请求类型为post, 加上 url:"queryById", dataType:"json", cache:false, //是否启用缓存 striped: true, //是否显示行间隔色 clickToSelect: true, //是否启用点击选中行 uniqueId: "testId", //每一行的唯一标识,一般为主键列 queryParams: { //传递的参数 "testId" : $("#testId").val() }, columns:[ //返回的参数 { checkbox: true },{ field:"", title:"序号", formatter: function(value, row, index) { return index + 1; } },{ field:"testId", //后台返回的参数名,变量名要保持一致 title:"唯一id" //页面表格中显示的字段 },{ field:"testName", title:"名称" },{ title:"操作", formatter:function(value,row,index){ return "<button class='btn btn-info' οnclick='updateModel(\"" + row.testId +"\")'>修改</button> " + " <button class='btn btn-warning' οnclick='deleteById(\"" + row.testId +"\")'>删除</button>"; } } ],onLoadSuccess : function(data){ console.log(data); } }); } //打开新增模态框 function AddModel() { clearData(); $("#title").text("新增test"); $("#operateId").hide(); $("#updateButton").hide(); $("#addButton").show(); $("#addTestModel").modal('show'); } //打开修改模态框 function updateModel(testId) { $("#title").text("修改test"); $("#operateId").show(); $("#addButton").hide(); $("#updateButton").show(); var row = $("#testTable").bootstrapTable('getRowByUniqueId', testId) $("#id").val(row.testId); $("#name").val(row.testName); $("#password").val(row.testPassword); $("#addTestModel").modal('show'); } //新增/修改test公共方法 function operateTest(url, type) { $.ajax({ url: url, type: type, data: { "testId" : $("#id").val(), "testName" : $("#name").val(), "testPassword" : $("#password").val(), }, success:function(data){ $("#addTestModel").modal('hide'); init(); alert(data); }, error:function(res){ console.log(res); alert("操作失败"); } }) } // 清除模态框数据 function clearData() { $("#id").val(""); $("#name").val(""); $("#password").val(""); } // 删除test function deleteById(testId) { $.ajax({ url: "deleteById", type: "delete", data: { "testId" : testId, }, success:function(data){ init(); alert(data); }, error:function(res){ console.log(res); alert("操作失败"); } }) } // 批量删除 function deleteIds() { var rows = $("#testTable").bootstrapTable('getSelections'); console.log(rows); if(rows == null || rows.length == 0) { alert("请选择要删除的test!"); }else { if(confirm("确定要清空数据吗?")) { var testIds = ""; for(var i = 0; i < rows.length; i++) { if(i = 0) { testIds += rows[i].testId; }else { testIds += "," + rows[i].testId; } } $.ajax({ url: "deleteIds", type: "delete", data: { "testIds" : testIds, }, success:function(data){ init(); alert(data); }, error:function(res){ console.log(res); alert("操作失败"); } }) } } } </script> </body> </html>
4.页面效果( 服务启动后地址 : http://localhost:8086/dataSource/test/testHtml )
5.集成 druid 管理数据库: 主要是一个配置类和 application.yml的配置文件
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/** * */ package demo.config; import java.util.Arrays; import java.util.HashMap; import java.util.Map; import javax.sql.DataSource; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; /** * @author GrassPrince * @Da2019年4月3日 2019年4月3日 - 下午8:00:17 * @Description druid数据库监测配置类 -> 项目启动后的访问地址 http://localhost:8086/dataSource/druid/index.html */ @Configuration public class DruidConfig { /** * 注入DruidDataSource在yml配置文件中的配置 * prefix: 获取以spring.datasource为前缀的配置内容, 减少一个个@Value获取 */ @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource getDataSource() { return new DruidDataSource(); } /** * 配置Druid的监控 : 一个管理后台的Servlet */ @Bean public ServletRegistrationBean<StatViewServlet> statViewServlet() { ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<StatViewServlet>(new StatViewServlet(), "/druid/*"); Map<String,String> initParams = new HashMap<String,String>(); initParams.put("loginUsername","admin"); //用户名 initParams.put("loginPassword","123456"); //密码 initParams.put("allow",""); //IP白名单(没有配置或者为空,则允许所有访问) initParams.put("deny",""); //IP黑名单 (存在共同时,deny优先于allow) bean.setInitParameters(initParams); return bean; } /** * 配置一个web监控的filter */ @Bean public FilterRegistrationBean<WebStatFilter> webStatFilter(){ FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<WebStatFilter>(); bean.setFilter(new WebStatFilter()); Map<String,String> initParams = new HashMap<>(); initParams.put("exclusions", "*.js,*.css,/druid/*"); 忽略资源 bean.setInitParameters(initParams); bean.setUrlPatterns(Arrays.asList("/*")); return bean; } }
6.配置后页面效果
7.个人心得
1) mybatis的配置文件很少,全在 yml 文件中, 需注意 启动类添加的 注解 @MapperScan("demo.mapper"), 扫描mapper层的接口
2) mysql数据库url的配置 url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC ,
springBoot 2.0 以上的版本 用 mysql 8.0.12客户端 时会出现时区错误, 用 serverTimezone=UTC 解决 , driver-class-name: com.mysql.cj.jdbc.Driver 也改变
3) xml中的参数在mapper接口中通过 @Param 注解注入较实用(list集合和数组都可以直接传)