Mybatis入门案例
1、首先导入依赖
2、核心配置文件
<?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>
<!--引用jdbc配置文件-->
<properties resource="jdbc.properties"/>
<!--实体类的别名-->
<typeAliases>
<!--单个类的别名-->
<!--<typeAlias type="cn.yinsh.hello.domain.Product" alias="product"/>-->
<!--给包的所有类设置别名:会消耗性能,但是很方便-->
<package name="cn.yinsh.hello.domain"/>
</typeAliases>
<!--连接数据库-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--注册SQL映射文件-->
<mapper resource="cn/yinsh/hello/productMapper.xml"/>
</mappers>
</configuration>
创建jdbc.properties配置文件,并引入
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///mybatis
jdbc.username=root
jdbc.password=123456
3、SQL映射文件
<?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="cn.yinsh.hello.mapper.ProductMapper">
</mapper>
4、创建实体类
5、dao层:CRUD
实现类
①未抽取工具类并没有创建mapper映射器时写法
@Override
public Product selectById(Long id) {
Product product = null;
try {
//获取配置文件
String resource = "mybatis-config.xml";
//加载配置文件
Reader reader = Resources.getResourceAsReader(resource);
//创建sqlSessionFactory工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
//创建sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取到sql
String sql = "cn.yinsh.hello.productMapper.selectById";
//执行sql语句
product = sqlSession.selectOne(sql,id);
} catch (IOException e) {
e.printStackTrace();
}
return product;
}
@Override
public List<Product> selectAll() {
List<Product> list = null;
try {
//获取配置文件
String resource = "mybatis-config.xml";
//加载配置文件
Reader reader = Resources.getResourceAsReader(resource);
//创建sqlSessionFactory工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
//创建sqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取到sql
String sql = "cn.yinsh.hello.productMapper.selectAll";
//执行sql语句
list = sqlSession.selectList(sql);
} catch (IOException e) {
e.printStackTrace();
}
return list;
②抽取工具类后
@Override
public void insert(Product product) {
session.insert("cn.yinsh.hello.productMapper.insert",product);
logger.debug("productID = " + product.getId());
}
@Override
public void update(Product product) {
session.update("cn.yinsh.hello.productMapper.update",product);
}
@Override
public void delete(Long id) {
session.delete("cn.yinsh.hello.productMapper.delete",id);
}
@Override
public Product selectById(Long id) {
return session.selectOne("cn.yinsh.hello.productMapper.selectById",id);
}
@Override
public List<Product> selectAll() {
return session.selectList("cn.yinsh.hello.productMapper.selectAll");
}
③使用工具类并创建mapper映射器
@Override
public void insert(Product product) {
SqlSession session = null;
try {
//获取sqlsession对象
session = MybatisUtil.getSession();
//获取mapper
ProductMapper mapper = session.getMapper(ProductMapper.class);
mapper.insert(product);
session.commit();
} catch (Exception e) {
e.printStackTrace();
}finally {
session.close();
}
}
@Override
public void batchInsert(List<Product> products) {
SqlSession session = null;
try {
//获取sqlsession对象
session = MybatisUtil.getSession();
//获取mapper
ProductMapper mapper = session.getMapper(ProductMapper.class);
mapper.batchInsert(products);
session.commit();
} catch (Exception e) {
e.printStackTrace();
}finally {
session.close();
}
}
@Override
public void update(Product product) {
SqlSession session = null;
try {
session = MybatisUtil.getSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
mapper.update(product);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Override
public void delete(Long id) {
SqlSession session = null;
try {
session = MybatisUtil.getSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
mapper.delete(id);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Override
public void batchDelete(List<Long> ids) {
SqlSession session = null;
try {
session = MybatisUtil.getSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
mapper.batchDelete(ids);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}
@Override
public Product selectById(Long id) {
//通过工具类获取sqlsession对象
SqlSession session = MybatisUtil.getSession();
//获取mapper映射器
ProductMapper mapper = session.getMapper(ProductMapper.class);
return mapper.selectById(id);
}
@Override
public List<Product> selectAll() {
SqlSession session = MybatisUtil.getSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
return mapper.selectAll();
}
@Override
public List<Product> selectForQuery(ProductQuery productQuery) {
SqlSession session = MybatisUtil.getSession();
ProductMapper mapper = session.getMapper(ProductMapper.class);
return mapper.selectForQuery(productQuery);
}
6、mapper映射器
public interface ProductMapper {
//增
void insert(Product product);
//修改
void update(Product product);
//删
void delete(Long id);
void batchDelete(List<Long> ids);
//根据Id查询
Product selectById(Long id);
//查询所有
List<Product> selectAll();
//按条件查询
List<Product> selectForQuery(ProductQuery productQuery);
//批量插入
void batchInsert(List<Product> products);
}
7、Mybatis获取sqlsession工具类
public class MybatisUtil {
private static SqlSessionFactory sqlSessionFactory = null;
static {
try {
//获取配置文件
String resource = "mybatis-config.xml";
//加载配置文件
Reader reader = Resources.getResourceAsReader(resource);
//创建sqlSessionFactory工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
}
}
//创建sqlSession对象
public static SqlSession getSession(){
return sqlSessionFactory.openSession();
}
}
8、新增自动返回主键
① 增删改默认是有返回值的,默认返回int:当前sql影响的数据条数
② useGeneratedKeys :自动返回主键
③ keyColumn : 数据库中的主键列
④ keyProperty:对象中的主键属性
<insert id="insert" parameterType="product" useGeneratedKeys="true" keyColumn="id" keyProperty="id"><insert>
9、设置别名
<!--实体类的别名-->
<typeAliases>
<!--单个类的别名-->
<!--<typeAlias type="cn.yinsh.hello.domain.Product" alias="product"/>-->
<!--给包的所有类设置别名:会消耗性能,但是很方便-->
<package name="cn.yinsh.hello.domain"/>
</typeAliases>
11、结果集映射
<!--集合映射-->
<!--type="product":可以直接使用别名,但会消耗性能-->
<resultMap id="baseResultMap" type="cn.yinsh.hello.domain.Product">
<id column="id" property="id"/>
<result column="productName" property="productName"/>
<result column="dir_id" property="dir_id"/>
<result column="salePrice" property="salePrice"/>
<result column="supplier" property="supplier"/>
<result column="brand" property="brand"/>
<result column="cutoff" property="cutoff"/>
<result column="costPrice" property="costPrice"/>
</resultMap>
<!--查询所有-->
<select id="selectAll" resultMap="baseResultMap">
select
<include refid="baseColumns"/>
from product
</select>
12、动态SQL:if&Concat&抽取公共sql&WHERE
可以在query实体类中判断productName不等于空字符串’ ',在某些版本中mybatis会把空字符串 ’ ’ 识别为数字类型,导致类型转换异常
<!--抽取公共sql-->
<sql id="baseWhere">
<where>
<if test="productName!=null">
AND productName LIKE CONCAT("%",#{productName},"%")
</if>
<if test="brand != null">
AND brand=#{brand}
</if>
<if test="salePriceMin != null">
AND salePrice >= #{salePriceMin}
</if>
<if test="salePriceMax != null">
AND salePrice <= #{salePriceMax}
</if>
</where>
</sql>
13、动态SQL:SET
<!--修改-->
<update id="update" parameterType="product">
UPDATE product
<set>
<if test="productName != null">
productName=#{productName},
</if>
<if test="dir_id != null">
dir_id=#{dir_id},
</if>
<if test="salePrice != null">
salePrice=#{salePrice},
</if>
<if test="supplier != null">
supplier=#{supplier},
</if>
<if test="brand != null">
brand=#{brand},
</if>
<if test="cutoff != null">
cutoff=#{cutoff},
</if>
<if test="costPrice != null">
costPrice=#{costPrice}
</if>
</set>
WHERE id = #{id}
</update>
14、动态SQL:foreach:批量删除
<!--批量删除-->
<delete id="batchDelete" parameterType="long">
DELETE FROM product WHERE id IN
<foreach collection="list" open="(" item="id" close=")" separator=",">
#{id}
</foreach>
</delete>
collection
指的是集合或者数组,这里接受两种值,如果是集合就写 “list”,如果是数组就写“array”open
开始元素,我们需要使用“( ” 作为开始item
循环的每一个元素,这里的每一个元素就是list中的每一个idseparator
分隔符,我们拼接后的sql需要使用“,”来分割多个IDclose
结束元素,我们需要使用“ )”作为结束#{id}
循环的内容,这里是把id的值取出来了,比如循环三次就如同 : (#{id}
,#{id}
,#{id}
)
循环会在最前面加上 “(” , 后面加上“)” , 然后取出每个item的值即ID ,然后使用分隔符“,”进行分割,最终形成(1,2)
这种效果。
15、动态SQL:foreach:批量插入
<!--批量新增-->
<insert id="batchInsert">
INSERT INTO product(
productName,
dir_id,
salePrice,
supplier,
brand,
cutoff,
costPrice
)VALUES
/*
collection="products"
*/
<foreach collection="list" item="product" separator=",">
(
#{product.productName},
#{product.dir_id},
#{product.salePrice},
#{product.supplier},
#{product.brand},
#{product.cutoff},
#{product.costPrice}
)
</foreach>
</insert>
void batchInsert(@Param("products") List<Product> products);
在接口中添加@Param后xml中的collection就可以写为
<foreach collection="products" item="product" separator=",">
16、特殊符号 “>” , "<"处理
<sql id="baseWhere">
<where>
<if test="productName!=null">
AND productName LIKE CONCAT("%",#{productName},"%")
</if>
<if test="brand != null">
AND brand=#{brand}
</if>
<if test="salePriceMin != null">
AND salePrice >= #{salePriceMin}
</if>
<if test="salePriceMax != null">
AND salePrice <= #{salePriceMax}
</if>
</where>
</sql>
17、取值表达式 #{}和KaTeX parse error: Expected 'EOF', got '#' at position 6: {}区别 #̲{}的底层就是使用的 “?”占…{}使用的是直接把值拼接到SQL中
18、sql映射文件的CRUD
<?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="cn.yinsh.hello.mapper.ProductMapper">
<!--集合映射-->
<resultMap id="baseResultMap" type="cn.yinsh.hello.domain.Product">
<id column="id" property="id"/>
<result column="productName" property="productName"/>
<result column="dir_id" property="dir_id"/>
<result column="salePrice" property="salePrice"/>
<result column="supplier" property="supplier"/>
<result column="brand" property="brand"/>
<result column="cutoff" property="cutoff"/>
<result column="costPrice" property="costPrice"/>
</resultMap>
<!--查询所有-->
<select id="selectAll" resultMap="baseResultMap">
select
<include refid="baseColumns"/>
from product
</select>
<!--条件查询-->
<select id="selectForQuery" resultMap="baseResultMap" >
select
<include refid="baseColumns"/>
from product
<include refid="baseWhere"/>
</select>
<!--抽取字段-->
<sql id="baseColumns">
id,productName,dir_id,salePrice,supplier,brand,cutoff,costPrice
</sql>
<!--抽取公共sql:在query实体类中判断productName不等于空字符串-->
<sql id="baseWhere">
<where>
<if test="productName!=null">
AND productName LIKE CONCAT("%",#{productName},"%")
</if>
<if test="brand != null">
AND brand=#{brand}
</if>
<if test="salePriceMin != null">
AND salePrice >= #{salePriceMin}
</if>
<if test="salePriceMax != null">
AND salePrice <= #{salePriceMax}
</if>
</where>
</sql>
<!--通过ID查询-->
<select id="selectById" parameterType="long" resultMap="baseResultMap">
select
<include refid="baseColumns"/>
from product WHERE id = #{id}
</select>
<!--添加-->
<insert id="insert" parameterType="product"
useGeneratedKeys="true" keyColumn="id" keyProperty="id">
INSERT INTO product(
productName,
dir_id,
salePrice,
supplier,
brand,
cutoff,
costPrice
)VALUES (
#{productName},
#{dir_id},
#{salePrice},
#{supplier},
#{brand},
#{cutoff},
#{costPrice}
)
</insert>
<!--批量新增-->
<insert id="batchInsert">
INSERT INTO product(
productName,
dir_id,
salePrice,
supplier,
brand,
cutoff,
costPrice
)VALUES
/*
collection="products"
*/
<foreach collection="list" item="product" separator=",">
(
#{product.productName},
#{product.dir_id},
#{product.salePrice},
#{product.supplier},
#{product.brand},
#{product.cutoff},
#{product.costPrice}
)
</foreach>
</insert>
<!--删除-->
<delete id="delete" parameterType="long">
DELETE FROM product WHERE id = #{id}
</delete>
<!--批量删除-->
<delete id="batchDelete" parameterType="long">
DELETE FROM product WHERE id IN
<foreach collection="list" open="(" item="id" close=")" separator=",">
#{id}
</foreach>
</delete>
<!--修改-->
<update id="update" parameterType="product">
UPDATE product
<set>
<if test="productName != null">
productName=#{productName},
</if>
<if test="dir_id != null">
dir_id=#{dir_id},
</if>
<if test="salePrice != null">
salePrice=#{salePrice},
</if>
<if test="supplier != null">
supplier=#{supplier},
</if>
<if test="brand != null">
brand=#{brand},
</if>
<if test="cutoff != null">
cutoff=#{cutoff},
</if>
<if test="costPrice != null">
costPrice=#{costPrice}
</if>
</set>
WHERE id = #{id}
</update>
</mapper>