SpringBoot与Mybatis集成:
准备
Maven依赖
引入Druid
1 2 3 4 5
<dependency > <groupId > com.alibaba</groupId > <artifactId > druid</artifactId > <version > 1.1.16</version > </dependency >
引入配置类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
package com.hph.springboot.config;import com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.support.http.StatViewServlet;import com.alibaba.druid.support.http.WebStatFilter;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 javax.sql.DataSource;import java.util.Arrays;import java.util.HashMap;import java.util.Map;@Configuration public class DruidConfig { @ConfigurationProperties (prefix = "spring.datasource" ) @Bean public DataSource druid () { return new DruidDataSource(); } @Bean public ServletRegistrationBean statViewServlet () { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*" ); Map<String,String> initParams = new HashMap<>(); initParams.put("loginUsername" ,"admin" ); initParams.put("loginPassword" ,"123456" ); initParams.put("allow" ,"" ); initParams.put("deny" ,"192.168.1.110" ); bean.setInitParameters(initParams); return bean; } @Bean public FilterRegistrationBean webStatFilter () { FilterRegistrationBean bean = new FilterRegistrationBean(); 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; } }
验证可用
数据准备
SQL文件
1 2 3 4 5 6 7 8
SET FOREIGN_KEY_CHECKS=0 ;DROP TABLE IF EXISTS `department` ;CREATE TABLE `department` ( `id` int (11 ) NOT NULL AUTO_INCREMENT, `departmentName` varchar (255 ) DEFAULT NULL , PRIMARY KEY (`id` ) ) ENGINE =InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;
1 2 3 4 5 6 7 8 9 10 11
SET FOREIGN_KEY_CHECKS=0 ;DROP TABLE IF EXISTS `employee` ;CREATE TABLE `employee` ( `id` int (11 ) NOT NULL AUTO_INCREMENT, `lastName` varchar (255 ) DEFAULT NULL , `email` varchar (255 ) DEFAULT NULL , `gender` int (2 ) DEFAULT NULL , `d_id` int (11 ) DEFAULT NULL , PRIMARY KEY (`id` ) ) ENGINE =InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;
配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
spring: datasource: username: root password: 123456 url: jdbc:mysql://192.168.1.110:3306/mybatis driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 schema: - classpath: /sql/department.sql - classpath: /sql/employee.sql
创建Bean
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
package com.hph.springboot.bean;public class Employee { private Integer id; private String lastName; private Integer gender; private String email; private String dId; public Integer getId () { return id; } public void setId (Integer id) { this .id = id; } public String getLastName () { return lastName; } public void setLastName (String lastName) { this .lastName = lastName; } public Integer getGender () { return gender; } public void setGender (Integer gender) { this .gender = gender; } public String getEmail () { return email; } public void setEmail (String email) { this .email = email; } public String getdId () { return dId; } public void setdId (String dId) { this .dId = dId; } @Override public String toString () { return "Employee{" + "id=" + id + ", lastName='" + lastName + '\'' + ", gender=" + gender + ", email='" + email + '\'' + ", dId='" + dId + '\'' + '}' ; } }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
package com.hph.springboot.bean;public class Department { private Integer id; private String departmentName; public Integer getId () { return id; } public void setId (Integer id) { this .id = id; } public String getDepartmentName () { return departmentName; } public void setDepartmentName (String departmentName) { this .departmentName = departmentName; } }
注意SQL数据表已经生成接下来我们在配置文件中注释掉
注解
Mapper类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
package com.hph.springboot.Mapper;import com.hph.springboot.bean.Department;import org.apache.ibatis.annotations.*;@Mapper public interface DepartmentMapper { @Select ("select * from department where id=#{id}" ) public Department getDeptById (Integer id) ; @Delete ("delete from department where id=#{id}" ) public int deleteDeptById (Integer id) ; @Insert ("insert into department(departmentName) values(#{departmentName})" ) public int insertDept (Department department) ; @Update ("update department set departmentName=#{departmentName} where id=#{id}" ) public int updateDept (Department department) ; }
Controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
package com.hph.springboot.Mapper;import com.hph.springboot.bean.Department;import org.apache.ibatis.annotations.*;@Mapper public interface DepartmentMapper { @Select ("select * from department where id=#{id}" ) public Department getDeptById (Integer id) ; @Delete ("delete from department where id=#{id}" ) public int deleteDeptById (Integer id) ; @Insert ("insert into department(departmentName) values(#{departmentName})" ) public int insertDept (Department department) ; @Update ("update department set departmentName=#{departmentName} where id=#{id}" ) public int updateDept (Department department) ; } }
查询
插入
如何让插入的时候返回主键呢只需要在Insert方法上添加:
1 2 3
@Options (useGeneratedKeys = true ,keyProperty = "id" ) @Insert ("insert into department(department_name) values(#{departmentName})" )public int insertDept (Department department) ;
再次查询
如果我们修改表的字段departmentName为department_Name
Mybatis的自动配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
@Bean @ConditionalOnMissingBean public SqlSessionFactory sqlSessionFactory (DataSource dataSource) throws Exception { SqlSessionFactoryBean factory = new SqlSessionFactoryBean(); factory.setDataSource(dataSource); factory.setVfs(SpringBootVFS.class); if (StringUtils.hasText(this .properties.getConfigLocation())) { factory.setConfigLocation(this .resourceLoader.getResource(this .properties.getConfigLocation())); } Configuration configuration = this .properties.getConfiguration(); if (configuration == null && !StringUtils.hasText(this .properties.getConfigLocation())) { configuration = new Configuration(); } if (configuration != null && !CollectionUtils.isEmpty(this .configurationCustomizers)) { for (ConfigurationCustomizer customizer : this .configurationCustomizers) { customizer.customize(configuration); } } factory.setConfiguration(configuration); if (this .properties.getConfigurationProperties() != null ) { factory.setConfigurationProperties(this .properties.getConfigurationProperties()); } if (!ObjectUtils.isEmpty(this .interceptors)) { factory.setPlugins(this .interceptors); } if (this .databaseIdProvider != null ) { factory.setDatabaseIdProvider(this .databaseIdProvider); } if (StringUtils.hasLength(this .properties.getTypeAliasesPackage())) { factory.setTypeAliasesPackage(this .properties.getTypeAliasesPackage()); } if (this .properties.getTypeAliasesSuperType() != null ) { factory.setTypeAliasesSuperType(this .properties.getTypeAliasesSuperType()); } if (StringUtils.hasLength(this .properties.getTypeHandlersPackage())) { factory.setTypeHandlersPackage(this .properties.getTypeHandlersPackage()); } if (!ObjectUtils.isEmpty(this .properties.resolveMapperLocations())) { factory.setMapperLocations(this .properties.resolveMapperLocations()); } return factory.getObject(); }
配置类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
package com.hph.springboot.config;import org.apache.ibatis.session.Configuration;import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;import org.springframework.context.annotation.Bean;@org .springframework.context.annotation.Configurationpublic class MybatisConfig { @Bean public ConfigurationCustomizer configurationCustomizer () { return new ConfigurationCustomizer() { @Override public void customize (Configuration configuration) { configuration.setMapUnderscoreToCamelCase(true ); } }; } }
QAQ依然可以使用。
小技巧:如果Mapper报下配置的的Mapper类很多一个一个添加可能会有些,麻烦或者遗漏因此我们可以在主类上添加一个注释(“@MapperScan(value = “com.hph.springboot.Mapper”)”) 指定Mapper扫描的包名。
XML配置
mybatis-config.xml
1 2 3 4 5 6 7 8 9 10
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration > <settings > <setting name ="mapUnderscoreToCamelCase" value ="true" /> </settings > </configuration >
EmployeeMapper.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
<?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 namespace ="com.hph.springboot.Mapper.EmployeeMapper" > <select id ="getEmpById" resultType ="com.hph.springboot.bean.Employee" > SELECT * FROM employee WHERE id=#{id} </select > <insert id ="insertEmp" > INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId}) </insert > </mapper >
Mapper类
1 2 3 4 5 6 7 8 9
package com.hph.springboot.Mapper;import com.hph.springboot.bean.Employee;public interface EmployeeMapper { public Employee getEmpById (Integer id) ; public void insertEmp (Employee employee) ; }
Controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
package com.hph.springboot.controller;import com.hph.springboot.Mapper.EmployeeMapper;import com.hph.springboot.bean.Employee;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RestController;@RestController public class EmpController { @Autowired EmployeeMapper employeeMapper; @GetMapping ("/emp/{id}" ) public Employee getEmp (@PathVariable("id" ) Integer id) { return employeeMapper.getEmpById(id); } }
在application.yml配置
1 2 3
mybatis: config-location: classpath:mybatis/mybatis-config.xml mapper-locations: classpath:mybatis/mapper/*.xml
数据准备
数据查询