mybatis配置及使用
- 简介
- Maven坐标
- mybatis-config.xml
- 创建同级目录mapper存放SQL映射文件(xml)
- 编写接口方法(interface)
- 编写SQL语句(查询)
- 执行SQL语句
- 动态查询
- 参数接收
- MyBatis 参数封装
- 特殊字符处理
- 增删改注意事项
- 添加-主键返回
- 批量删除
- 工具类:SqlSessionFactoryUtils.java
- logback.xml
简介
这是一篇关于mybatis的配置及使用的文章,目的帮你快速配置自己的mybatis。
Maven坐标
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.20</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.3</version>
</dependency>
</dependencies>
mybatis-config.xml
在 resources 目录下创建mybatis的配置文件
<?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="com.xxx.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.xxx.mapper"/>
</mappers>
</configuration>
创建同级目录mapper存放SQL映射文件(xml)
即在resource目录下新建directory 命名com/xxx/mapper
编写接口方法(interface)
在 com.xxx.mapper 包写创建名为 BrandMapper 的接口。并在该接口中定义 List selectAll() 方法。
public interface BrandMapper{
List<Brand> selectAll();
}
编写SQL语句(查询)
在 reources 下创建 com/itheima/mapper 目录结构,并在该目录下创建名为 BrandMapper.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">
<mapper namespace="com.xxx.mapper.BrandMapper">
<!--
数据库表的字段名称 和 实体类的属性名称 不一样,则不能自动封装数据
* resultMap:注意大小写
1. 定义<resultMap>标签
2. 在<select>标签中,使用resultMap属性替换 resultType属性
-->
<!--
id:唯一标识
type:映射的类型,支持别名
-->
<resultMap id="brandResultMap" type="Brand">
<!--
id:完成主键字段的映射
result:完成一般字段的映射
column:表的列名
property:实体类的属性名
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<select id="selectAll" resultMap="brandResultMap">
select *
from tb_brand;
</select>
若一样,不修改resultType
<select id="selectAll" resultType="Brand">
select *
from tb_brand;
</select>
</mapper>
执行SQL语句
import com.xxx.mapper.BrandMapper;
import com.xxx.pojo.Brand;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class mybatis1 {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象,用它来执行sql
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 执行sql
//List<User> users = sqlSession.selectList("test.selectAll");
//3.1 获取UserMapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
List<Brand> users = brandMapper.selectAll();
System.out.println(users);
//4. 释放资源
sqlSession.close();
}
}
动态查询
多查询
<!--
动态条件查询
* if: 条件判断
* test:逻辑表达式
* 问题:
* 恒等式
* <where> 替换 where 关键字
-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
/* where 1 = 1*/
<where>
<if test="status != null">
and status = #{status}
</if>
<if test="companyName != null and companyName != '' ">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
</where>
</select>
单查询
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<choose><!--相当于switch-->
<when test="status != null"><!--相当于case-->
status = #{status}
</when>
<when test="companyName != null and companyName != '' "><!--相当于case-->
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != ''"><!--相当于case-->
brand_name like #{brandName}
</when>
</choose>
</where>
</select>
参数接收
/**
* 条件查询
* * 参数接收
* 1. 散装参数:如果方法中有多个参数,需要使用@Param("SQL参数占位符名称")
* 2. 对象参数:对象的属性名称要和参数占位符名称一致
* 3. map集合参数
*
*/
1. List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
2. List<Brand> selectByCondition(Brand brand);
3. List<Brand> selectByCondition(Map map);
MyBatis 参数封装
//接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//封装对象
/* Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(companyName);
brand.setBrandName(brandName);*/
Map map = new HashMap();
// map.put("status" , status);
map.put("companyName", companyName);
// map.put("brandName" , brandName);
特殊字符处理
快捷:CD
select *
from tb_brand
where id
<![CDATA[
<
]]>
#{id};
增删改注意事项
1.SqlSession sqlSession = sqlSessionFactory.openSession(true); //设置自动提交事务,这种情况不需要手动提交事务了
2.//手动提交事务
sqlSession.commit();
添加-主键返回
在 insert 标签上添加如下属性:
useGeneratedKeys:是够获取自动增长的主键值。true表示获取
keyProperty :指定将获取到的主键值封装到哪儿个属性里
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand (brand_name, company_name, ordered, description, status)
values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>
批量删除
接口方法,参数是一个数组,数组中存储的是多条数据的id
例:int[] ids = {1,2,3};
void deleteByIds(int[] ids);
<delete id="deleteByIds">
delete from tb_brand where id
in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
;
</delete>
工具类:SqlSessionFactoryUtils.java
util工具类
package com.itheima.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class SqlSessionFactoryUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSqlSessionFactory(){
return sqlSessionFactory;
}
}
logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<!--
CONSOLE :表示当前的日志信息是可以输出到控制台的。
-->
<appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%level] %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
</encoder>
</appender>
<logger name="com.xxx" level="DEBUG" additivity="false">
<appender-ref ref="Console"/>
</logger>
<!--
level:用来设置打印级别,大小写无关:TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF
, 默认debug
<root>可以包含零个或多个<appender-ref>元素,标识这个输出位置将会被本日志级别控制。
-->
<root level="DEBUG">
<appender-ref ref="Console"/>
</root>
</configuration>