孙
孙少平
V1
2022/07/02阅读:16主题:默认主题
mybatis学习
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>
<properties resource="properties/db.properties"></properties>
<typeAliases>
<package name="com.god.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.user}"/>
<property name="password" value="${db.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--<mapper resource="com/god/mapper/BrandMapper.xml"/>-->
<package name="com.god.mapper"/>
<package
</mappers>
</configuration>
db.properties
db.url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = Asia/Shanghai
db.user=root
db.password=1234
db.driver=com.mysql.cj.jdbc.Driver
util
public class DBCon {
public static SqlSession getCon() throws IOException {
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = factory.openSession();
return sqlSession;
}
}
mapper
Brand selectById(int id);
方法1:
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand
where id
<![CDATA[
<
]]>
#{id};
</select>
方法2:
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand
where id<#{id}
</select>
___
List<Brand> selectByConditionSingle(Brand brand);
方法1:
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status != null">
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>
方法2:
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<choose>
<when test="status!=null ">
status=#{status}
</when>
<when test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</when>
<when test="companyName != null and companyName != '' ">
and company_name like #{companyName}
</when>
</choose>
</where>
</select>
——————————
List<Brand> selectByCondition(Map map);
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where status = #{status}
and brand_name like #{brandName}
and company_name like #{companyName}
</select>
——————
<sql id="brand_column">
id, brand_name as brandName, company_name as companyName, ordered, description, status
</sql>
<select id="selectAll" resultType="brand">
select
<include refid="brand_column" />
from tb_brand;
</select>
————————
___________________________________________________________________________________________
<mapper namespace="com.god.mapper.BrandMapper">
<resultMap id="brandResultMap" type="Brand">
<result property="brandName" column="brand_name"/>
<result property="companyName" column="company_name"/>
</resultMap>
——————
Integer deleteByIds(@Param("ids") int[] ids);
<delete id="deleteByIds">
delete
from tb_brand
where id in
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
————---
List<Brand> selectByPage(@Param("begin") int begin,@Param("size")int size);
<select id="selectByPage" resultMap="brandResultMap">
select *
from tb_brand
limit #{begin},#{size}
</select>
——————
List<Brand> selectByPageAndCondition(@Param("begin")int begin,@Param("size")int size,@Param("brand")Brand brand);
<select id="selectByPageAndCondition" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="brand.brandName != null and brand.brandName != ''">
brand_name like #{brand.brandName}
</if>
<if test="brand.companyName !=null and brand.companyName !=''">
and company_name like #{brand.companyName}
</if>
<if test="brand.status!= null">
and status =#{brand.status}
</if>
</where>
limit #{begin},#{size}
</select>
——————————
Integer selectTotalCountByCondition(Brand brand);
<select id="selectTotalCountByCondition" resultType="java.lang.Integer">
select count(*)
from tb_brand
<where>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
<if test="companyName != null and companyName != '' ">
and company_name like #{companyName}
</if>
<if test="status != null">
and status = #{status}
</if>
</where>
</select>
</mapper>
MyBatis 参数封装:
* 单个参数:
1. POJO类型:直接使用属性名 (和参数占位符名称一致)
2. Map集合:直接使用键名 (和参数占位符名称一致)
3. Collection:封装为Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
map.put("arg0",collection集合);
map.put("collection",collection集合);
4. List:封装为Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
map.put("arg0",list集合);
map.put("collection",list集合);
map.put("list",list集合);
5. Array:封装为Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
map.put("arg0",数组);
map.put("array",数组);
6. 其他类型:直接使用
* 多个参数:封装为Map集合,可以使用@Param注解,替换Map集合中默认的arg键名
map.put("arg0",参数值1)
map.put("param1",参数值1)
map.put("param2",参数值2)
map.put("agr1",参数值2)
---------------@Param("username")
map.put("username",参数值1)
map.put("param1",参数值1)
map.put("param2",参数值2)
map.put("agr1",参数值2)
User select(@Param("username") String username,@Param("password")String password);
User select(Collection collection);
<select id="select" resultType="user">
select *
from tb_user
where
username = #{arg0}
and password = #{param2}
</select>
___
${} :用于sql拼接, 在表明或者列名不固定的而情况下使用
#{}代替? 防止sql注入,参数传递的时候用 在运行后#{}都会变成?
当select查询结果表的字段和实体类的属性名不一致时,将resultType替换成 resultMap
<
1. 转移字符 <
2. <![CDATA[<]]>
@Param("") : java使用方法传入参数不止一个时,mybaits不知道参数匹配在sql语句中的第几个#{""}
所以超出一个参数的方法 都要加@param()
当方法多个参数属于同一个对象的属性时,可以把所有属性直接封为一个对象 当作参数
举例子:
Integer selectTotalCountByCondition(Brand brand);
<select id="selectTotalCountByCondition" resultType="java.lang.Integer">
select count(*)
from tb_brand
<where>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
<if test="companyName != null and companyName != '' ">
and company_name like #{companyName}
</if>
<if test="status != null">
and status = #{status}
</if>
</where>
</select>
————————
<if test=""></if>
<where>
<choose>
<when<</when>
<when<</when>
<otherwise></otherwise>
</choose>
<where>
useGenerateKey="true" keyProperty="id"
让插入到数据库后返回的对象有一个id值 没加这个条件返回的brand对象的id=null了
举例:
Brand add(Brand brand);
<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>
动态就用<set></set>标签
mybatis会自动把数组封装为map集合 key叫array ,value就是传进来的数组
默认时 array=数组
你可以用@parm("a") 的a改变map集合默认的key名称
——————
作者介绍
孙
孙少平
V1