引入mybatis-spring-boot-starter依赖
<!--整合mybatis,不用再引入spring-boot-starter-jdbc,因为这个包含了它--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> <!--必须指定版本,不然找不到@Mapper注解--> </dependency> <!--同时要引入mysql驱动--> <!--数据库驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency>
- mybatis-spring-boot-starter依赖:
- 自动检测现有的DataSource
- 创建并注册SqlSessionFactory的实例,该实例使用SqlSessionFactoryBean将该DAtaSource作为输入进行传递
- 自动扫描mappers,将它们注入到Spring上下文,以便将它们注入到你的bean中。
- 就是说,使用了该starter之后,只需要定义一个DataSource即可
配置数据源
spring.datasource.url = jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf-8 spring.datasource.username = root spring.datasource.password = root spring.datasource.driver-class-name = com.mysql.jdbc.Driver
自定义数据源:Springboot默认使用tomcat-jdbc数据源,如果想用其他数据源,除了在配置原件配置数据源以外,还要额外添加以下依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.19</version> </dependency>
- 然后这里使用直接引用配置文件的方式来配置数据库连接池
- application.properties
#阿里巴巴连接池配置
#最大连接池数量
spring.datasource.max-active=20
#最小连接池数量
spring.datasource.min-idle=8
#获取连接时最大等待时间
spring.datasource.maxWait=60000
#初始化时建立连接的个数
spring.datasource.initialSize=2
#申请连接时执行ValidationQuery检测连接是否有效,建议为false,因为做了这个配置会降低性能;若下面那行括号为null则这一行怎么写都不生效
spring.datasource.testOnBorrow=false
#用来检测连接是否有效的sql,要求是一个查询语句
spring.datasource.validationQuery="SELECT 1"
#建议配置为true,不影响性能且保证安全性;作用是在ValidationQuery上加个条件:当空闲时间大于timeBetweenEvictionRunsMillis时才执行连接检测
spring.datasource.testWhileIdle=true
#是否缓存preparedStatement
spring.datasource.poolPreparedStatements=true
- Application.java
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class,args);
}
@Bean(destroyMethod = "close") //destroyMethod = "close":当数据库连接不使用时就把该连接重新放到数据池中,方便下次调用
@ConfigurationProperties(prefix="spring.datasource") //直接使用配置文件中前缀为这个的属性来set DataSource的属性
public DataSource dataSource(){
return new DruidDataSource();
}
}
- ok,这样就算我们自己设置了一个DataSource,Springboot会自动选择我们自己配置的这个DataSource实例
建张表learn_resource,写个实体类LearnResource
public class LearnResource { private Long id; private String author; private String title; private String url; }
方式一:注解方式
dao层写增删查改接口,类上加个@Mapper注解或在入口类加个@MapperScan(“com.dudu.dao”)
@Component //不加的话service那边引入LearnMappers时会警告 @Mapper public interface LearnMapper { //简单的可以直接用注解完成 @Insert("insert into learn_resource values(#{id},#{author},#{title},#{url})") public int add(LearnResource learnResource); @Update("update learn_resource set author=#{author},title=#{title},url=#{url} where id=#{id}") public int update(LearnResource learnResource); //复杂的需要创建动态sql @DeleteProvider(type=LearnSqlBuilder.class,method="deleteByIds") public int deleteByIds(@Param("ids")String[] ids); @Select("select * from learn_resource where id=#{id}") @Results( { @Result(id=true,column="id",property = "id"), @Result(column="author",property = "author"), @Result(column="title",property = "title"), } ) public LearnResource queryLearnResourceById(@Param("id") Long id); /** * @Author haien * @Description 动态查询 * @Date 2018/11/3 * @Param [params] * @return java.util.List<com.dudu.domain.LearnResource> **/ @SelectProvider(type=LearnSqlBuilder.class,method="queryLearnResourceByParams") public List<LearnResource> queryLearnResourceList(Map<String,Object> params); /** * @Author haien * @Description 动态sql的创建类 * @Date 2018/11/3 **/ class LearnSqlBuilder{ /** * @Author haien * @Description 动态查询 * @Date 2018/11/2 * @Param [params] * @return java.lang.String **/ public String queryLearnResourceByParams(final Map<String,Object> params){ //方式一、使用java1234的做法 SQL sql= new SQL(){ { SELECT("*"); FROM("learn_resource"); StringBuffer sb=new StringBuffer(); if(params.get("author")!=null){ sb.append(" and author like '%"+params.get("author") +"%'"); } if(params.get("title")!=null){ sb.append(" and title="+params.get("title")); } if(!sb.toString().equals("")){ WHERE(sb.toString().replaceFirst("and","")); } } }; System.out.println(sql.toString()); return sql.toString(); //方式二、用嘟嘟博客的做法 /* StringBuffer sql=new StringBuffer(); sql.append("selelct * from learn_resource where 1=1"); if(params.get("author")!=null){ sql.append(" and author like '%").append(params.get("author")).append("%'"); } if(params.get("title")!=null){ sql.append(" and title like '%").append(params.get("title")).append("%'"); } System.out.println("查询sql=="+sql.toString()); return sql.toString(); */ } /** * @Author haien * @Description 批量删除 * @Date 2018/11/2 * @Param [ids] * @return java.lang.String **/ public String deleteByIds(@Param("ids")final String[] ids){ StringBuffer sql=new StringBuffer(); sql.append("delete from learn_resource where id in("); for(int i=0;i<ids.length;i++){ if(i==ids.length-1){ sql.append(ids[i]); //最后一个则不用再跟个逗号 }else{ sql.append(ids[i]).append(","); } } sql.append(")"); return sql.toString(); } } }
方式二:xml配置文件
xml配置方式一:通过mybatis.config-location指定config.xml路径,然后在里面设置别名、指定mapper包
application.properties
mybatis.config-location=mybatis-config.xml
mybatis-config.xml
<?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> <typeAliases> <package name="sample.mybatis.domain"/> </typeAliases> <mappers> <mapper resource="sample/mybatis/mapper/CityMapper.xml"/> <mapper resource="sample/mybatis/mapper/HotelMapper.xml"/> </mappers> </configuration>
- 这样既起了别名,映射文件也被扫描到了,数据层接口就不需要@Mapper注解了
- 二:通过mybatis.type-aliases-package指定实体类路径,mybatis.mapperLocations指定映射文件路径,数据层接口要加@Mapper接口
- application.properties
mybatis.type-aliases-package=com.dudu.domain;
mybatis.mapperLocations=classpath:mapper/*.xml //当mapper文件和mapper接口在同一个包下时不用配置
- dao层
@Mapper
public interface LearnMapperWithXml {
public int add(LearnResource learnResource);
public int update(LearnResource learnResource);
public int deleteByIds(String[] ids);
public LearnResource queryLearnResourceById(Long id);
public List<LearnResource> queryLearnResourceList(Map<String,Object> params);
}
根据方式二的路径我们在resources下新建一个文件夹mapper,写LearnMapper.xml
<?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"> <!--为指定接口中的方法配置sql语句--> <mapper namespace="com.dudu.dao.LearnMapperWithXml"> <!--指定接口--> <resultMap id="LearnResource" type="com.dudu.domain.LearnResource"> <!--一定要用完整路径--> <id column="id" property="id"/> <result column="author" property="author"/> <result column="title" property="title"/> <result column="url" property="url"/> </resultMap> <select id="queryLearnResourceList" resultMap="LearnResource" parameterType="hashmap"> <!--大小写不分--> select * from learn_resource <where> <if test="author!=null and author !=''"> and author like '%${author}%' <!--like的话是%${author}%而不是%#{author}%--> </if> <if test="title!=null and title!=''"> and title like '%${title}%' </if> </where> </select> <select id="queryLearnResourceById" resultMap="LearnResource" parameterType="Long"> select * from learn_resource where id=#{id} </select> <insert id="add" parameterType="com.dudu.domain.LearnResource"> insert into learn_resource values(#{id},#{author},#{title},#{url}) </insert> <update id="update" parameterType="com.dudu.domain.LearnResource"> update learn_resource set author=#{author},title=#{title},url=#{url} where id=#{id} </update> <delete id="deleteByIds" parameterType="String"> delete from learn_resource where id in <foreach collection="params" item="idItem" open="(" separator="," close=")"> #{idItem} </foreach> </delete> </mapper>
- :封装sql语句,:调用sql语句
SELECT * FROMbody_index
<select id="find" resultType="*" resultMap="*"> <include refid="select"/> </select>
mybatis的分页:PageHelper分页插件
- 物理分页
- 原理:分页本质上是sql limit去获取数据,而mybatis则是在Statement生成之前对sql进行改造,之后生成的Statement中包含的sql就能实现分页功能了。这是利用mybatis拦截器实现的。
首先引入依赖
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.1.0</version> </dependency>
在service实现层中使用(当然你要放在controller层还是其他层使用都可以)
/** * @Author haien * @Description 动态查询 * @Date 2018/11/3 * @Param [params] * @return java.util.List<com.dudu.domain.LearnResource> **/ @Override public List<LearnResource> queryLearnResourceList(Map<String, Object> params) { //物理分页:对mybatis流程进行增强,添加了limit以及count查询 PageHelper.startPage(Integer.parseInt(params.get("pageNum").toString()), Integer.parseInt(params.get("rows").toString())); //page: 页码(从0开始),rows:容量 return learnMapperWithXml.queryLearnResourceList(params); }
在controller层转换为pageInfo类,它将数据用List封装,另外还包含许多类似总页数、总条数的属性
List<LearnResource> learnResourceList=learnService.queryLearnResourceList(params); PageInfo<LearnResource> pageInfo=new PageInfo<LearnResource>(learnResourceList); //不能直接强转:PageInfo<LearnResource> pageInfo=(PageInfo<LearnResource>)learnService.queryLearnResourceList(params); JSONObject jo=new JSONObject(); jo.put("rows",pageInfo); //LearnResource实体类集合 jo.put("total",pageInfo.getPages()); //总页数 jo.put("records",pageInfo.getTotal()); //总记录数
缺点:不支持条件查询的sql(具体不知道是什么条件查询),不过网上有改良后能支持的PageHelper
<dependency> <groupId>com.mybatis.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>1.0</version> </dependency>
- 代码实例:SpringbootIntegrateMybatis
- 代码实例:SpringbootIntegrateMybatis