首先我们先搭建一个Maven工程
参考我的文章:
搭建成功:
我们要使用的数据表:
以及和他对应的Brand类:
public class Brand {
private int id;
private String name;
private int people;
private int status;
//1:表示启用 0:表示禁用
public Brand() {
}
public Brand(int id, String name, int people, int status) {
this.id = id;
this.name = name;
this.people = people;
this.status = status;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setPeople(int people) {
this.people = people;
}
public void setStatus(int status) {
this.status = status;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public int getPeople() {
return people;
}
public int getStatus() {
return status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", name='" + name + '\'' +
", people=" + people +
", status=" + status +
'}';
}
}
假如数据表中的字段名与类中的成员变量名字不一致。
方法:
1.字段名:brand_name 成员变量:brandname
可以在mybatis-config.xml中的settings标签使用驼峰映射
可以在select语句中取别名(AS),把字段名改成与成员变量一致
2.使用mapper里的resultMap标签和select标签中的resultMap属性
resultMap标签
属性
id:这个resultMap的名字
type:要转换成的类型路径名(这里我已经在核心配置文件中的标签<typeAliases>起别名;)
autoMapping:如果字段名与成员变量名一致,自动匹配
子标签
id->用来匹配数据表中的主键值,column属性是字段名,property是成员变量名
result->用来匹配数据表中的其他字段
select标签中的resultMap属性:关联resultMap标签
<mapper namespace="com.hhh.dao.BrandMapper"> <resultMap id="brandMap" type="Brand" autoMapping="true"> <id column="brand_id" property="id"/> <result column="brand_name" property="name"/> <result column="brand_people" property="people"/> </resultMap> <select id="findAllBrand" resultMap="brandMap"> select id AS brand_id, //在这里我先把字段名改成与成员变量不一致,方便观察 name AS brand_name, people AS brand_people, status from brand; </select> </mapper>
我们也能发现如果要进行多次查询,select语句中的变量名要写很多次,不方便
方法:
使用mapper中的sql标签,直接把变量名封装起来,并在id属性中起名字
<mapper namespace="com.hhh.dao.BrandMapper"> <sql id="brandFields"> id AS brand_id, name AS brand_name, people AS brand_people, status </sql> <resultMap id="brandMap" type="Brand" autoMapping="true"> <id column="brand_id" property="id"/> <result column="brand_name" property="name"/> <result column="brand_people" property="people"/> </resultMap> <select id="findAllBrand" resultMap="brandMap"> select <include refid="brandFields"/> from brand; </select> </mapper>
#{key}和${key}的区别
${key}:
在核心配置文件中获取外部配置文件的数据
在映射文件中作为sql的占位符参数(注意:需要在接口方法中的参数中加上@Param("参数名"))
#{key}
在映射文件中作为sql的占位符参数(不用声明@Param)
a
区别:
#{}:底层使用PrepareStatement对象,对sql进行预编译,使用占位符,好处:防止SQL注入${}:底层使用Statement对象,对sql进行拼接。会有SQL注入风险
多条件sql语句查询
方法一:散装参数,使用@Param("参数名称")
/** * 根据多条件查询数据 * @param name 品牌名 * @param status 品牌状态 * @return 返回品牌对象 */ public Brand findByCondition(@Param("name") String name, @Param("status") int status);
<select id="findByCondition" resultType="Brand"> select id, name, people, status from brand where name=#{name} //使用的是Param参数的value值-->name and status=#{status}; </select>
@Test public void testFindBrandByCondition() { SqlSession sqlSession=MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); Brand condition = mapper.findByCondition("华为", 1); System.out.println(condition); }
方法二:实体类封装参数
public Brand findByCondition2(Brand brand);
<select id="findByCondition2" resultType="Brand"> select id, name, people, status from brand where name=#{name} //使用的是Brand类的成员变量 and status=#{status}; </select>
@Test public void testFindBrandByCondition2() { SqlSession sqlSession=MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); Brand brand=new Brand(); brand.setName("华为"); brand.setStatus(1); Brand condition = mapper.findByCondition2(brand); System.out.println(condition); }
方法三:使用集合
public Brand findByCondition3(Map brandMap);
<select id="findByCondition3" resultType="Brand"> select id, name, people, status from brand where name=#{name} //使用的是Map集合的键 and status=#{status}; </select>
@Test public void testFindBrandByCondition3() { SqlSession sqlSession=MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); Map brandMap=new HashMap(); brandMap.put("name","华为"); brandMap.put("status",1); Brand condition = mapper.findByCondition3(brandMap); System.out.println(condition); }
多条件查询--动态条件
sql语句会随着用户的输入或外部条件的变化而变化,我们叫动态SQL
if标签:用于判断参数是否有值,使用test属性进行条件判断
<select id="findByCondition" resultType="Brand"> select id, name , people , status from brand where 1=1 <if test="name != null"> 这里的name是#里面的,不是字段名 and name=#{name} </if> <if test="status != 0"> int类型用0 and status=#{status}; </if> </select>
where标签:代替sql语句的where关键字
可以自动取出sql语句中where条件里多余的or和and
<select id="findByCondition2" resultType="Brand"> select id, name, people, status from brand <where> <if test="name != null"> and name=#{name} </if> <if test="status != 0"> and status=#{status}; </if> </where> </select>
可以发现并没有and status=? 这一部分,这就是动态sql
choose标签:类似于switch(只选一个满足的)
<select id="findByCondition3" resultType="Brand"> select id, name, people, status from brand <where> <choose> <when test="name != null"> and name=#{name} </when> <when test="status != 0"> and status=#{status} </when> </choose> </where> </select>
@Test public void testFindBrandByCondition3() { SqlSession sqlSession=MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); Map brandMap=new HashMap(); brandMap.put("name","华为"); brandMap.put("status",1); Brand condition = mapper.findByCondition3(brandMap); System.out.println(condition); }
可以看到只有name=?,因为name已经不为null,已满足条件,所以不执行下面的when
问:如果都不满足when里的条件会怎么样
答:会不执行where条件
主键回填
在数据添加成功后,能直接获取数据表中的主键,把主键封装到对应类对象的id成员变量
这种方式主要用于多表添加,有外键字段的从表,需要主表的主键
<insert id="addBrand" useGeneratedKeys="true" keyProperty="id"> insert into brand(name, people, status) VALUE (#{name},#{people},#{status}); </insert>
@Test public void testaddBrand() { SqlSession sqlSession=MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); Brand brand=new Brand(); brand.setName("小米"); brand.setPeople(200); brand.setStatus(1); int i = mapper.addBrand(brand);//这个是受改变的行数,添加数据的主键封装到id成员变量 System.out.println("主键为:"+brand.getId());//主键为:7 if(i>0) { sqlSession.commit(); System.out.println("添加成功"); } List<Brand> allBrand = mapper.findAllBrand(); for (Brand brand1 : allBrand) { System.out.println(brand1); } MybatisUtil.closeSqlSession(sqlSession); }
修改所有字段
/** * * 修改品牌信息 * @param brand 修改的品牌对象 * @return 受改变的行数 */ public int updateBrand(Brand brand);
<update id="updateBrand"> update brand set name=#{name}, people=#{people},status=#{status} where id=#{id} </update>
@Test public void testUpdateBrand() { SqlSession sqlSession = MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); int i = mapper.updateBrand(new Brand(6, "vivo", 400, 1)); if(i>0) { sqlSession.commit(); System.out.println("修改成功"); } MybatisUtil.closeSqlSession(sqlSession); }
修改部分字段--动态条件
我们可以使用set标签代替set关键字(针对update操作),可以自动删除sql语句中多余的,号
<update id="updateBrand"> update brand <set> <if test="name != null"> ,name=#{name} </if> <if test=" people != 0"> 里面的people是成员变量名 , people=#{people} </if> <if test="status != 0"> ,status=#{status} </if> </set> where id=#{id} </update>
} @Test public void testUpdateBrand() { SqlSession sqlSession = MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); Brand brand=new Brand(); brand.setId(6); brand.setName("oppo"); brand.setPeople(600); // brand.setStatus(1); int i = mapper.updateBrand(brand); if(i>0) { sqlSession.commit(); System.out.println("修改成功"); } MybatisUtil.closeSqlSession(sqlSession); }
可以看到没有status=?
批量删除
foreach标签:遍历容器,取容器中的元素,拼接sql
<foreach collection="集合(数组)" item="从容器中取的每一个元素" separator="元素之间的分隔符号" open="以什么开始" close="以什么结束">
#{id} -->取出来的item元素
</foreach>
/** * 批量删除数据通过id * @param list 删除id的集合 * @return 返回受改变的行数 */ public int deleteBrandByIds(@Param("ids") List<Integer>list);
<delete id="deleteBrandByIds"> delete from brand where id in ids是Param的属性值 <foreach collection="ids" item="id" separator="," open="(" close=")"> #{id} 这里的id是item的值 </foreach> </delete>
@Test public void testDeleteBrandByIds() { SqlSession sqlSession = MybatisUtil.openSession(); BrandMapper mapper = sqlSession.getMapper(BrandMapper.class); List<Integer>ids=new ArrayList<>(); Collections.addAll(ids,1,2,3); int i = mapper.deleteBrandByIds(ids); if(i>0) { sqlSession.commit(); System.out.println("删除成功"); } MybatisUtil.closeSqlSession(sqlSession); }