Mybatis:
入门示例:
(1)db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mysql_0219
jdbc.username = root
jdbc.password = 123456
(2)log4j.properties https://blog.csdn.net/sinat_30185177/article/details/73550377
log4j.rootLogger=DEBUG,A1
log4j.logger.org.mybatis=DEBUG
...
(3) mybatis核心配置文件:mybatis-config.xml
(4) BlogMapper.xml
包:com.jingbin.mybatis.mapper
testSelectBlog
发现要连接数据库,学习mysql内容
学习配置好了后:运行报错:Invalid bound statement (not found): mapper.BlogMapper.selectBlog
解决:https://www.cnblogs.com/cailijuan/p/9505244.html
1)使用创建接口的方式
2)不是用接口的方式
数据库里的列名为author_id,属性名为authorId。在BlogMapper.xml里:
1.使用别名
select author_id as authorId from Blog where id=#{id}
2.使用 resultMap
<resultMap type="Blog" id="blogResultMap">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="author_id" property="authorId" jdbcType="INTEGER"/>
</resultMap>
<select id="selectBlog" parameterType="Integer" resultMap="blogResultMap">
select * from blog where id = #{id}
</select>
#
和$
的区别模糊查询:根据博客名字查询博客列表
1)使用#
传参
2)使用$
传参
#
是占位符?,$
是字符串拼接。
mybatis定义:
$
。如果参数是单指类型(简单类型),并且只有一个参数,则花括号里只能写value占位。$
可以直接将%
写里面,可能有sql注入的风险,建议最好使用#
。参数是字符串要使用 ''$
<!-- 使用 $ 不区分大小写的查询 lower-->
<select id="selectBlogByTitle2" parameterType="string" resultType="Blog">
select * from blog where lower(title) like lower('%${value}%')
</select>
</mapper>
需求:按照某一列排序
select * from blog order by CONVERT(${value} USING gbk)
gbk:输入中文时排序成功,否则会失败。且使用gbk规避魅族(gb2313)不排序问题。
需求:查询分页数据
按照参数的顺序,从0开始
select * from blog limit #{0}, #{1}
注解的value值要和mapper的占位参数一致。
select * from blog limit #{offset}, #{pageSize}
List<Blog> selectBlogByPage2(@Param(value = "offset") int offset, @Param(value = "pageSize") int pageSize);
注意:mapper中的参数占位符要和测试中的map的key一一对应
select * from blog limit #{offset}, #{pageSize}
// 测试
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Map<String, Object> objectMap = new HashMap<>();
objectMap.put("offset", 0);
objectMap.put("pageSize", 2);
List<Blog> blogList = blogMapper.selectBlogByPage3(objectMap);
需求:新增一个博客记录
<insert id="insertBlog" parameterType="Blog">
insert into `blog`(
`name`,
`age`,
`title`,
`author_id`,
`featured`
) values (
#{name},
#{age},
#{title},
#{author_id},
#{featured}
)
</insert>
// 提交
session.commit();
insert
节点的属性 useGeneratedKeys
和keyProperty
节点<insert id="insertBlog" parameterType="Blog" useGeneratedKeys="true" keyProperty="id"/>
方式2:在全局配置文件中配置setting
<!--定义数据库链接配置-->
<properties resource="db.properties"/>
<!--具体的insert也得配置 keyProperty节点-->
<settings>
<setting name="useGeneratedKeys" value="true"/>
</settings>
方式3:适用于没有自增主键的数据库
<insert id="insertBlogOracle" parameterType="Blog">
<selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
select seq.nextval as id from dual
</selectKey>
insert into `blog`(
`name`,
`age`,
`title`,
`author_id`,
`featured`
) values (
#{name},
#{age},
#{title},
#{author_id},
#{featured}
)
</insert>
<insert id="insertBlogMysql" parameterType="Blog">
<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
SELECT LAST_INSERT_ID()
</selectKey>
insert into `blog`(
`name`,
`age`,
`title`,
`author_id`,
`featured`
) values (
#{name},
#{age},
#{title},
#{author_id},
#{featured}
)
</insert>
<update id="updateBlog" parameterType="Blog">
update `blog`
set
`name` = #{name},
`age` = #{age},
`title` = #{title},
`author_id` = #{author_id},
`featured` = #{featured}
where
`id` = #{id}
</update>
注意:如果没有为对象设置所有的要修改的属性,那么未设置的属性会用成员变量的默认值填充。 解决: 方式1:数据库查一遍,再返回的数据修改。缺点:又执行了一遍数据库操作 方式2:查询语句里增加if else。
<delete id="deleteBlogById">
delete from blog where id=#{id}
</delete>
动态sql 批量删除:使用动态sql实现 if、[choose、when、otherwise]、where、set、trim、foreach、sql片段
需求:
// 如果用户输入了查询字符串 select * from blog where state = 'ACTIVE' and title like '%o%'
// 用户没有输入查询字符串 select * from blog where state = 'ACTIVE'
select * from blog
where state = 'ACTIVE'
<if test="value != null and value!=''">
and title like value[%%] 具体见代码
</if>
需求:
<select id="selectActiveBlogByTitleOrStyle" parameterType="Blog" resultType="Blog">
select * from blog
where state = 'ACTIVE'
<choose>
<when test="title != null and title!=''">and lower(title) like lower(#{title})</when>
<when test="style != null and style!=''">and style = #{style}</when>
<otherwise> and featured = true</otherwise>
</choose>
</select>
需求:多条件查询,根据状态,标题,是否被推荐 自动修补查询条件,查询语句中的where关键字使用<where>标签替代,不能省略 and or 关键字
<select id="selectBlogByCondition" parameterType="Blog" resultType="Blog">
select * from blog
<where>
<if test="state != null and state!=''">
state = #{state}
</if>
<if test="title != null and title!=''">
and lower(title) like lower(#{title})
</if>
<if test="featured != null">
and featured = #{featured}
</if>
</where>
</select>
需求:按需修改,修改执行的列,未指定的不修改 set 会自动去掉if语句后面的逗号
<update id="updateBlogByCondition" parameterType="Blog">
update `blog`
<set>
<if test="name != null">`name` = #{name},</if>
<if test="age != null">`age` = #{age},</if>
<if test="title != null">`title` = #{title},</if>
<if test="author_id != null">`author_id` = #{author_id},</if>
<if test="featured != null">`featured` = #{featured},</if>
<if test="state != null">`state` = #{state},</if>
<if test="style != null">`style` = #{state},</if>
</set>
where `id` = #{id}
</update>
<select id="selectBlogByConditionTrim" parameterType="Blog" resultType="Blog">
select * from blog
-- prefixOverrides 去掉前面的 and 或 or
<trim prefix="where" prefixOverrides="and | or">
<if test="state != null and state!=''">
state = #{state}
</if>
<if test="title != null and title!=''">
and lower(title) like lower(#{title})
</if>
<if test="featured != null">
and featured = #{featured}
</if>
</trim>
</select>
<update id="updateBlogByConditionTrim" parameterType="Blog">
update `blog`
-- suffixOverrides 去掉后面的 ,
<trim prefix="set" suffixOverrides=",">
<if test="name != null">`name` = #{name},</if>
<if test="age != null">`age` = #{age},</if>
<if test="title != null">`title` = #{title},</if>
<if test="author_id != null">`author_id` = #{author_id},</if>
<if test="featured != null">`featured` = #{featured},</if>
<if test="state != null">`state` = #{state},</if>
<if test="style != null">`style` = #{state},</if>
</trim>
where `id` = #{id}
</update>
需求:批量删除
<!-- parameterType 与 collection 一致-->
<delete id="deleteBlogList" parameterType="list">
delete from blog where id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
@Test
public void testDeleteBlogList() {
SqlSession session = MyBatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
List<Integer> asList = Arrays.asList(1, 2);
int count = blogMapper.deleteBlogList(asList);
// 提交
session.commit();
session.close();
System.out.println("更新了" + count + "条记录");
}