Spring 下 MyBatis 的基本使用

索引：

目录索引

参看代码 GitHub :

pom.xml

dbconfig.properties

DbConfig.java

MySqlBean.java

User.java

UserMapper.java

UserDynaSqlProvider.java

UserService.java

UserServiceImpl.java

MyBatisController.java

mybatis.jsp

一、引入类库

         <!-- mybatis核心包 -->
         <dependency>
             <groupId>org.mybatis</groupId>
             <artifactId>mybatis</artifactId>
         </dependency>
         <!--mybatis spring 插件 -->
         <dependency>
             <groupId>org.mybatis</groupId>
             <artifactId>mybatis-spring</artifactId>
         </dependency>
         <!-- Mysql数据库驱动包 -->
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
         </dependency>
         <!-- connection pool -->
         <dependency>
             <groupId>com.alibaba</groupId>
             <artifactId>druid</artifactId>
             <!--<scope>runtime</scope>-->
         </dependency>

二、DB 与 Pool 的配置

 #MySQL
 jdbc.driver=com.mysql.jdbc.Driver

 #基本属性 url、user、password
 jdbc.url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
 jdbc.username=root
 jdbc.password=liumeng

 #配置初始化大小、最小、最大
 jdbc.initialSize=
 jdbc.minIdle=
 jdbc.maxActive=

 #配置获取连接等待超时的时间
 jdbc.maxWait=

 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
 jdbc.timeBetweenEvictionRunsMillis=

 #配置一个连接在池中最小生存的时间，单位是毫秒
 jdbc.minEvictableIdleTimeMillis=

 jdbc.validationQuery=SELECT 'x'
 jdbc.testWhileIdle=true
 jdbc.testOnBorrow=false
 jdbc.testOnReturn=false

 #打开PSCache，并且指定每个连接上PSCache的大小
 jdbc.poolPreparedStatements=false
 jdbc.maxPoolPreparedStatementPerConnectionSize=

 #配置监控统计拦截的filters
 jdbc.filters=stat

 三、Bean 的 配置

　　1.配置属性到bean

 package lm.solution.web.config.properties;

 import org.springframework.beans.factory.annotation.Value;
 import org.springframework.context.annotation.AnnotationConfigApplicationContext;
 import org.springframework.context.annotation.PropertySource;
 import org.springframework.stereotype.Component;

 @Component(value = "dbConfig")
 @PropertySource("classpath:dbconfig.properties")
 public class DbConfig {

     @Value("${jdbc.url}")
     private String url;
     public String getUrl(){
         return this.url;
     }

     @Value("${jdbc.username}")
     private String username;
     public String getUsername(){
         return this.username;
     }

     @Value("${jdbc.password}")
     private String password;
     public String getPassword(){
         return this.password;
     }

     @Value("${jdbc.initialSize}")
     private Integer initialSize;
     public Integer getInitialSize(){
         return this.initialSize;
     }

     @Value("${jdbc.minIdle}")
     private Integer minIdle;
     public Integer getMinIdle(){
         return this.minIdle;
     }

     @Value("${jdbc.maxActive}")
     private Integer maxActive;
     public Integer getMaxActive(){
         return this.maxActive;
     }

     @Value("${jdbc.maxWait}")
     private Long maxWait;
     public Long getMaxWait() {
         return this.maxWait;
     }

     @Value("${jdbc.timeBetweenEvictionRunsMillis}")
     private Long timeBetweenEvictionRunsMillis;
     public Long getTimeBetweenEvictionRunsMillis(){
         return this.timeBetweenEvictionRunsMillis;
     }

     @Value("${jdbc.minEvictableIdleTimeMillis}")
     private Long minEvictableIdleTimeMillis;
     public Long getMinEvictableIdleTimeMillis(){
         return this.minEvictableIdleTimeMillis;
     }

     @Value("${jdbc.validationQuery}")
     private String validationQuery;
     public String getValidationQuery(){
         return this.validationQuery;
     }

     @Value("${jdbc.testWhileIdle}")
     private Boolean testWhileIdle;
     public Boolean getTestWhileIdle(){
         return this.testWhileIdle;
     }

     @Value("${jdbc.testOnBorrow}")
     private Boolean testOnBorrow;
     public Boolean getTestOnBorrow(){
         return this.testOnBorrow;
     }

     @Value("${jdbc.testOnReturn}")
     private Boolean testOnReturn;
     public Boolean getTestOnReturn(){
         return this.testOnReturn;
     }

     @Value("${jdbc.poolPreparedStatements}")
     private Boolean poolPreparedStatements;
     public Boolean getPoolPreparedStatements(){
         return this.poolPreparedStatements;
     }

     @Value("${jdbc.maxPoolPreparedStatementPerConnectionSize}")
     private Integer maxPoolPreparedStatementPerConnectionSize;
     public Integer getMaxPoolPreparedStatementPerConnectionSize(){
         return this.maxPoolPreparedStatementPerConnectionSize;
     }

     @Value("${jdbc.filters}")
     private String filters;
     public String getFilters(){
         return this.filters;
     }

     private final static Object object=new Object();
     private static DbConfig config;
     public static DbConfig getConfig(){
         return config;
     }

     static {
         synchronized(object) {
             AnnotationConfigApplicationContext configContext = new AnnotationConfigApplicationContext(DbConfig.class);
             config = (DbConfig) configContext.getBean("dbConfig");
         }
     }

 }

　　2.配置组件 Bean

 package lm.solution.web.config.beans;

 import com.alibaba.druid.pool.DruidDataSource;
 import lm.solution.web.config.properties.DbConfig;
 import org.mybatis.spring.SqlSessionFactoryBean;
 import org.mybatis.spring.mapper.MapperScannerConfigurer;
 import org.springframework.context.annotation.Bean;
 import org.springframework.context.annotation.Configuration;
 import org.springframework.jdbc.datasource.DataSourceTransactionManager;

 import java.sql.SQLException;

 @Configuration
 public class MySqlBean {

     // dataSource 配置数据源
     @Bean(
             name = "dataSource",
             initMethod = "init",
             destroyMethod = "close"
     )
     public DruidDataSource druidDataSource(){

         try {

             //
             DbConfig db=DbConfig.getConfig();

             //
             DruidDataSource dataSource = new DruidDataSource();
             dataSource.setUrl(db.getUrl());
             dataSource.setUsername(db.getUsername());
             dataSource.setPassword(db.getPassword());
             dataSource.setInitialSize(db.getInitialSize());
             dataSource.setMinIdle(db.getMinIdle());
             dataSource.setMaxActive(db.getMaxActive());
             dataSource.setMaxWait(db.getMaxWait());
             dataSource.setTimeBetweenEvictionRunsMillis(db.getTimeBetweenEvictionRunsMillis());
             dataSource.setMinEvictableIdleTimeMillis(db.getMinEvictableIdleTimeMillis());
             dataSource.setValidationQuery(db.getValidationQuery());
             dataSource.setTestWhileIdle(db.getTestWhileIdle());
             dataSource.setTestOnBorrow(db.getTestOnBorrow());
             dataSource.setTestOnReturn(db.getTestOnReturn());
             dataSource.setPoolPreparedStatements(db.getPoolPreparedStatements());
             dataSource.setMaxPoolPreparedStatementPerConnectionSize(db.getMaxPoolPreparedStatementPerConnectionSize());
             dataSource.setFilters(db.getFilters());
             return dataSource;

         }catch (SQLException se){
             se.printStackTrace();
         }

         return null;

     }

     // mybatis和spring完美整合，不需要mybatis的配置映射文件
     @Bean(name = "sqlSessionFactory")
     public SqlSessionFactoryBean sqlSessionFactoryBean(DruidDataSource dataSource){

         SqlSessionFactoryBean factory=new SqlSessionFactoryBean();
         factory.setDataSource(dataSource);
         return factory;

     }

     // DAO接口所在包名，Spring会自动查找其下的类
     @Bean
     public MapperScannerConfigurer mapperScannerConfigurer(){

         MapperScannerConfigurer scannerConfigurer=new MapperScannerConfigurer();
         scannerConfigurer.setBasePackage("lm.solution.mapper");
         scannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
         return scannerConfigurer;

     }

     // 对dataSource 数据源进行事务管理
     @Bean(name = "transactionManager")
     public DataSourceTransactionManager dataSourceTransactionManager( DruidDataSource dataSource ){

         DataSourceTransactionManager transactionManager=new DataSourceTransactionManager();
         transactionManager.setDataSource(dataSource);
         return transactionManager;

     }

 }

四、操作 mysql 数据库 -- 简单的CRUD

　　1. 实体

 package lm.solution.pojo.entity;

 public class User {
     private Integer id;

     private String name;

     private String age;

     private Integer salary;

     public Integer getId() {
         return id;
     }

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

     public String getName() {
         return name;
     }

     public void setName(String name) {
         this.name = name == null ? null : name.trim();
     }

     public String getAge() {
         return age;
     }

     public void setAge(String age) {
         this.age = age == null ? null : age.trim();
     }

     public Integer getSalary() {
         return salary;
     }

     public void setSalary(Integer salary) {
         this.salary = salary;
     }
 }

　　2.MyBatis Mapper

 package lm.solution.mapper;

 import lm.solution.pojo.entity.User;
 import lm.solution.mapper.provider.UserDynaSqlProvider;
 import org.apache.ibatis.annotations.*;

 import java.util.List;

 public interface UserMapper {

     @Select(" select * from user ")
     List<User> findAllUsers();

     @Select(" select * from user where id=#{id} ")
     User findById(long id);

     @Select(" select * from user where name=#{name} ")
     User findByName(String name);

     @InsertProvider(type= UserDynaSqlProvider.class,method = "saveUser")
     @Options(useGeneratedKeys = true,keyColumn = "id")
     Integer saveUser(User user);

     @UpdateProvider(type = UserDynaSqlProvider.class,method = "updateUser")
     Integer updateUser(User user);

     @Delete(" delete from user where id=#{id} ")
     Integer deleteUser(long id);

     @Delete(" delete from user where 1=1 ")
     Integer deleteAllUsers();
 }

 package lm.solution.mapper.provider;

 import lm.solution.pojo.entity.User;
 import org.apache.ibatis.jdbc.SQL;

 public class UserDynaSqlProvider {

     public String saveUser(final User user) {
         return new SQL() {
             {
                 INSERT_INTO("user");
                 if(user.getName()!=null&&!"".equals(user.getName())){
                     VALUES("name","#{name}");
                 }
                 if(user.getAge()!=null&&!"".equals(user.getAge())){
                     VALUES("age","#{age}");
                 }
                 if(user.getSalary()!=null){
                     VALUES("salary","#{salary}");
                 }
             }
         }.toString();
     }

     public String updateUser(final User user){
         return  new SQL(){
             {
                 UPDATE("user");
                 if(user.getName()!=null && !"".equals(user.getName())){
                     SET(" name=#{name} ");
                 }
                 if(user.getAge()!=null && !"".equals(user.getAge())){
                     SET(" age=#{age} ");
                 }
                 if(user.getSalary()!=null){
                     SET(" salary=#{salary} ");
                 }
                 WHERE(" id=#{id} ");
             }
         }.toString();
     }

 }

　　3.Service 

 package lm.solution.service.mysql;

 import lm.solution.pojo.entity.User;
 import java.util.List;

 public interface UserService {

     List<User> findAllUsers();

     User findById(long id);

     boolean isUserExist(User user);

     void saveUser(User user);

     void updateUser(User user);

     void deleteUser(long id);

     void deleteAllUsers();

 }

 package lm.solution.service.mysql.impl;

 import lm.solution.pojo.entity.User;
 import lm.solution.mapper.UserMapper;
 import lm.solution.service.mysql.UserService;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 import org.springframework.transaction.annotation.Transactional;
 import org.springframework.transaction.annotation.Propagation;
 import org.springframework.transaction.annotation.Isolation;
 import java.util.List;

 @Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT)
 @Service("userService")
 public class UserServiceImpl implements UserService {

     @Autowired
     private UserMapper userMapper;

     @Override
     public List<User> findAllUsers() {
         return userMapper.findAllUsers();
         //return null;
     }

     @Override
     public User findById(long id) {
         //return null;

         return userMapper.findById(id);
     }

     @Override
     public boolean isUserExist(User user) {

         User userDB=userMapper.findByName(user.getName());
         if(userDB==null){
             return  false;
         }else {
             return true;
         }
     }

     @Override
     public void saveUser(User user) {
         userMapper.saveUser(user);
     }

     @Override
     public void updateUser(User user) {
         userMapper.updateUser(user);
     }

     @Override
     public void deleteUser(long id) {
         userMapper.deleteUser(id);
     }

     @Override
     public void deleteAllUsers() {
         userMapper.deleteAllUsers();
     }

 }

　　4.Controller

 package lm.solution.web.controller.db;

 import com.fasterxml.jackson.core.JsonProcessingException;
 import com.fasterxml.jackson.databind.ObjectMapper;
 import lm.solution.pojo.entity.User;
 import lm.solution.service.mysql.UserService;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.beans.factory.annotation.Qualifier;
 import org.springframework.stereotype.Controller;
 import org.springframework.web.bind.annotation.*;

 import java.io.IOException;
 import java.util.List;

 @Controller
 public class MyBatisController {

     private static ObjectMapper objectMapper;

     @Autowired
     @Qualifier("userService")
     private UserService userService;

     static {
         objectMapper = new ObjectMapper();
     }

     @RequestMapping(
             value = "/mybatis/userall",
             method = RequestMethod.GET,
             produces = {"application/json;charset=UTF-8"})
     @ResponseBody
     public String allUsers() {

         try {
             List<User> users = userService.findAllUsers();
             return objectMapper.writeValueAsString(users);
         } catch (IOException ioe) {
             ioe.printStackTrace();
             return "{}";
         }

     }

     @RequestMapping(
             value = "/mybatis/{id}",
             method = RequestMethod.GET,
             produces = {"application/json;charset=UTF-8"})
     @ResponseBody
     public String getUserById(@PathVariable("id") long id) {

         try {
             User user = userService.findById(id);
             return objectMapper.writeValueAsString(user);
         } catch (IOException ioe) {
             ioe.printStackTrace();
             return "{}";
         }
     }

     @RequestMapping(
             value = "/mybatis/create",
             method = RequestMethod.POST,
             consumes = {"application/json"},
             produces = {"application/json;charset=UTF-8"})
     @ResponseBody
     public String create(@RequestBody User user) {

         try {

             userService.saveUser(user);
             return objectMapper.writeValueAsString(user);

         }catch (JsonProcessingException jpe){
             jpe.printStackTrace();
         }

         return "{}";

     }

     @RequestMapping(
             value = "/mybatis/update",
             method = RequestMethod.PUT,
             consumes = {"application/json"},
             produces = {"application/json;charset=UTF-8"})
     @ResponseBody
     public String update(@RequestBody User user) {

         try {

             userService.updateUser(user);
             return objectMapper.writeValueAsString(user);

         }catch (JsonProcessingException jpe){
             jpe.printStackTrace();
         }

         return "{}";

     }

     @RequestMapping(
             value = "/mybatis/{id}",
             method = RequestMethod.DELETE,
             produces = {"application/json;charset=UTF-8"})
     @ResponseBody
     public String deleteById(@PathVariable("id") long id) {
         userService.deleteUser(id);
         return "success";

     }

     @RequestMapping(
             value = "/mybatis",
             method = RequestMethod.DELETE,
             produces = {"application/json;charset=UTF-8"})
     @ResponseBody
     public String deleteAll() {
         userService.deleteAllUsers();
         return "success";

     }

 }

　　5.View

 <%--
   Created by IntelliJ IDEA.
   User: liumeng
   Date: 2018/3/2
   Time: 14:25
   To change this template use File | Settings | File Templates.
 --%>
 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
 <html>
 <head>
     <title>MySQL op by MyBatis</title>
     <script src="/assets/js/jquery-3.3.1.js"></script>
 </head>
 <body>
 <p>
     <button id="btnAllusers">GET-查看所有</button>
     <br>
 <div id="getAllusers"></div>
 <br>
 <script>
     $(function () {
         $('#btnAllusers').on("click", function (e) {
             var request = $.ajax({
                 url: "/mybatis/userall",
                 method: "GET"
             });

             request.done(function (data) {
                 if (data) {
                     var html = "";
                     for (var i = 0; i < data.length; i++) {
                         html += "<span>id</sapn>:<span>" + data[i].id + "</span><br>";
                         html += "<span>name</sapn>:<span>" + data[i].name + "</span><br>";
                         html += "<span>age</sapn>:<span>" + data[i].age + "</span><br>";
                         html += "<span>salary</sapn>:<span>" + data[i].salary + "</span><br>";
                         html += "<br>----------------------------------------------------------<br>";
                     }
                     $("#getAllusers").html(html);
                 }
             });

             request.fail(function (jqXHR, textStatus) {
                 alert("Request failed: " + textStatus);
             });
         });
     });
 </script>
 <hr>
 <button id="btnUserByID">GET-查看ByID</button>
 <br>
 <span>ID:</span><input type="text" id="userbyIDtx">
 <br>
 <div id="UserByID"></div>
 <br>
 <script>
     $(function () {
         $('#btnUserByID').on("click", function (e) {
             var request = $.ajax({
                 url: "/mybatis/"+$("#userbyIDtx").val(),
                 method: "GET",
                 dataType:"json"
             });

             request.done(function (data) {
                 if (data) {
                     var html = "";
                     html += "<span>id</sapn>:<span>" + data.id + "</span><br>";
                     html += "<span>name</sapn>:<span>" + data.name + "</span><br>";
                     html += "<span>age</sapn>:<span>" + data.age + "</span><br>";
                     html += "<span>salary</sapn>:<span>" + data.salary + "</span><br>";
                     html += "<br>----------------------------------------------------------<br>";
                     $("#UserByID").html(html);
                 }
             });

             request.fail(function (jqXHR, textStatus) {
                 alert("Request failed: " + textStatus);
             });
         });
     });
 </script>
 <hr>
 <button id="createOne">POST-创建一个</button>
 <br>
 <span>ID:</span><input type="text" disabled="disabled" ><br>
 <span>Name:</span><input type="text" id="createName" ><br>
 <span>Age:</span><input type="text" id="createAge" ><br>
 <span>Salary:</span><input type="text" id="createSalary" >
 <br>
 <div id="createUser"></div>
 <br>
 <script>
     $(function () {
         $('#createOne').on("click", function (e) {
             var data={
                 name:$("#createName").val(),
                 age:$("#createAge").val(),
                 salary:$("#createSalary").val()
             };

             var request = $.ajax({
                 url: "/mybatis/create",
                 method: "POST",
                 data:JSON.stringify(data),
                 contentType:"application/json"
                 //dataType:"json"
             });

             request.done(function (data) {
                 if (data) {
                     var html = "";
                     html += "<span>id</sapn>:<span>" + data.id + "</span><br>";
                     html += "<span>name</sapn>:<span>" + data.name + "</span><br>";
                     html += "<span>age</sapn>:<span>" + data.age + "</span><br>";
                     html += "<span>salary</sapn>:<span>" + data.salary + "</span><br>";
                     html += "<br>----------------------------------------------------------<br>";
                     $("#createUser").html(html);
                 }
             });

             request.fail(function (jqXHR, textStatus) {
                 alert("Request failed: " + textStatus);
             });
         });
     });
 </script>
 <hr>
 <button id="putOne">PUT-更新一个</button>
 <br>
 <span>ID:</span><input type="text" id="putId" ><br>
 <span>Name:</span><input type="text" id="putName" ><br>
 <span>Age:</span><input type="text" id="putAge" ><br>
 <span>Salary:</span><input type="text" id="putSalary" >
 <br>
 <div id="putUser"></div>
 <br>
 <script>
     $(function () {
         $('#putOne').on("click", function (e) {
             var data={
                 id:$("#putId").val(),
                 name:$("#putName").val(),
                 age:$("#putAge").val(),
                 salary:$("#putSalary").val()
             };

             var request = $.ajax({
                 url: "/mybatis/update",
                 method: "PUT",
                 data:JSON.stringify(data),
                 contentType:"application/json"
                 //dataType:"json"
             });

             request.done(function (data) {
                 if (data) {
                     var html = "";
                     html += "<span>id</sapn>:<span>" + data.id + "</span><br>";
                     html += "<span>name</sapn>:<span>" + data.name + "</span><br>";
                     html += "<span>age</sapn>:<span>" + data.age + "</span><br>";
                     html += "<span>salary</sapn>:<span>" + data.salary + "</span><br>";
                     html += "<br>----------------------------------------------------------<br>";
                     $("#createUser").html(html);
                 }
             });

             request.fail(function (jqXHR, textStatus) {
                 alert("Request failed: " + textStatus);
             });
         });
     });
 </script>
 <hr>
 <button id="deleteOne">DELETE-删除一个</button>
 <br>
 <span>ID:</span><input type="text" id="deleteId" ><br>
 <!--<span>Name:</span><input type="text" id="putName" ><br>
 <span>Age:</span><input type="text" id="putAge" ><br>
 <span>Salary:</span><input type="text" id="putSalary" >-->
 <br>
 <div id="deleteUser"></div>
 <br>
 <script>
     $(function () {
         $('#deleteOne').on("click", function (e) {
             // var data={
             //     id:$("#putId").val(),
             //     name:$("#putName").val(),
             //     age:$("#putAge").val(),
             //     salary:$("#putSalary").val()
             // };

             var request = $.ajax({
                 url: "/mybatis/"+$("#deleteId").val(),
                 method: "DELETE",
                 // data:JSON.stringify(data),
                 contentType:"application/json"
                 //dataType:"json"
             });

             request.done(function (data) {
                 if (data) {
                     var html = "";
                     // html += "<span>id</sapn>:<span>" + data.id + "</span><br>";
                     // html += "<span>name</sapn>:<span>" + data.name + "</span><br>";
                     // html += "<span>age</sapn>:<span>" + data.age + "</span><br>";
                     // html += "<span>salary</sapn>:<span>" + data.salary + "</span><br>";
                     // html += "<br>----------------------------------------------------------<br>";
                     $("#createUser").html(data);
                 }
             });

             request.fail(function (jqXHR, textStatus) {
                 alert("Request failed: " + textStatus);
             });
         });
     });
 </script>
 <hr>
 <button id="deleteAll">DELETE-删除所有</button>
 <br>
 <div id="deleteUserAll"></div>
 <br>
 <script>
     $(function () {
         $('#deleteAll').on("click", function (e) {
             var request = $.ajax({
                 url: "/mybatis",
                 method: "DELETE",
                 // data:JSON.stringify(data),
                 contentType:"application/json"
                 //dataType:"json"
             });

             request.done(function (data) {
                 if (data) {
                     var html = "";
                     // html += "<span>id</sapn>:<span>" + data.id + "</span><br>";
                     // html += "<span>name</sapn>:<span>" + data.name + "</span><br>";
                     // html += "<span>age</sapn>:<span>" + data.age + "</span><br>";
                     // html += "<span>salary</sapn>:<span>" + data.salary + "</span><br>";
                     // html += "<br>----------------------------------------------------------<br>";
                     $("#deleteUserAll").html(data);
                 }
             });

             request.fail(function (jqXHR, textStatus) {
                 alert("Request failed: " + textStatus);
             });
         });
     });
 </script>
 <hr>
 </p>
 </body>
 </html>

　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　蒙

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2018-05-08 00:15 周二

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2018-05-11 22:51 周五