Open better2021 opened 5 years ago
application.yml文件中配置
spring: datasource: username: root password: 709463253 url: jdbc:mysql://localhost:3306/node?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT driver-class-name: com.mysql.cj.jdbc.Driver
排序
example.setOrderByClause("age DESC");
//条件查询使用的是属性名
example.createCriteria().andEqualTo("name", "张三");
example.createCriteria().andGreaterThan("id",3);
新增数据并加入id
Text text = new Text(null, "啦啦啦");
if (mTestMapper.insertUseGeneratedKeys(text) <= 0) {
throw new Exception(block.getId() + " 创建失败");
}
// id 被写入
log.info(text.id)
结果顺序 按 in() 中的ID排序
<select id="queryTestList" resultMap="TestResult">
select * from test
<where>
id in
<foreach item="item" index="index" collection="ids" open="(" close=")" separator=",">
#{item}
</foreach>
order by field
<foreach item="item" index="index" collection="ids" open="(id," close=")" separator=",">
#{item}
</foreach>
</where>
</select>
List<Test> queryTestList(@Param("ids") List<String> ids);
对应 mysql语句
select * FROM test where id in (1,3,2,5) order by field (id,1,3,2,5);
find_in_set 的使用
<select id="queryTest" resultMap="TestResult">
select * from test
<where>
<if test="myIds != null">
FIND_IN_SET(#{myIds,jdbcType=VARCHAR},test_ids)
</if>
</where>
</select>
List<TestResult> queryTest(@Param("myIds") String myIds);
select * from app_models where FIND_IN_SET('3',type_ids)
批量更新多个不同值的字段
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update course
<set>
name=${item.name}
</set>
where id = ${item.id}
</foreach>
</update>
对比的基本使用
Example example = new Example(Test.class);
Example.Criteria criteria = example.createCriteria();
criteria.andBetween("data",begin,end)
criteria.andEqualTo("lv", lv);
example.setOrderByClause("sort ASC");
List<TestList> taskLists = mTestListMapper.selectByExample(example);
常见问题
// 实体类对比时
POJo 对象 不要使用 int 要使用 Integer
mybatis.configuration.return-instance-for-empty-row=true
比较复杂的例子
<mapper namespace="org.imooc.dao.BusinessDao">
<resultMap type="Business" id="BusinessResultMap">
<id column="id" property="id" />
<result column="img_file_name" property="imgFileName" />
<result column="title" property="title" />
<result column="subtitle" property="subtitle" />
<result column="price" property="price" />
<result column="distance" property="distance" />
<result column="number" property="number" />
<result column="desc" property="desc" />
<result column="city" property="city" />
<result column="category" property="category" />
<result column="star_total_num" property="starTotalNum" />
<result column="comment_total_num" property="commentTotalNum" />
<association property="cityDic" javaType="Dic">
<result column="city_name" property="name"/>
</association>
<association property="categoryDic" javaType="Dic">
<result column="category_name" property="name"/>
</association>
</resultMap>
<insert id="insert">
insert into business(img_file_name,title,subtitle,price,distance,number,`desc`,city,category,star_total_num,comment_total_num)
values(#{imgFileName},#{title},#{subtitle},#{price},#{distance},#{number},#{desc},#{city},#{category},#{starTotalNum},#{commentTotalNum})
</insert>
<select id="selectById" resultType="Business">
select
id,img_file_name,title,subtitle,price,distance,number,`desc`,city,category,star_total_num,comment_total_num
from business where id=#{_parameter}
</select>
<select id="selectByPage" resultMap="BusinessResultMap">
select b.id,b.img_file_name,b.title,b.subtitle,b.price
,b.distance,b.number,b.`desc`,b.city,b.category,b.star_total_num,b.comment_total_num
,d_city.name city_name,d_category.name category_name
from
business b
left join dic d_city on b.city=d_city.code and
d_city.type='${@org.imooc.constant.DicTypeConst@CITY}'
left join dic d_category on
b.category=d_category.code and d_category.type='${@org.imooc.constant.DicTypeConst@CATEGORY}'
<where>
<if test="title != null and title != ''">
and b.title like '%' #{title} '%'
</if>
<if test="subtitle != null and subtitle != ''">
and b.subtitle like '%' #{subtitle} '%'
</if>
<if test="desc != null and desc != ''">
and b.`desc` like '%' #{desc} '%'
</if>
<if test="city != null and city != ''">
and b.city = #{city}
</if>
<if test="category != null and category != ''">
and b.category = #{category}
</if>
</where>
order by id
</select>
<select id="selectLikeByPage" resultType="Business">
select id,img_file_name,title,subtitle,price,distance,number,`desc`,city,category,star_total_num,comment_total_num
from business
<where>
<if test="title != null and title != ''
and subtitle != null and subtitle != ''
and desc != null and desc != ''">
and
(title like '%' #{title} '%' or subtitle like '%' #{subtitle} '%' or `desc` like '%' #{desc} '%')
</if>
<if test="city != null and city != ''">
and city = #{city}
</if>
<if test="category != null and category != ''">
and category = #{category}
</if>
</where>
order by id
</select>
<update id="updateNumber">
update business b,(select business_id,count(*) cou from orders group by business_id) c
set b.number=b.number + c.cou where b.id=c.business_id
</update>
<update id="updateStar">
update business b,
(select o.business_id,sum(c.star) star_num,count(*) comment_num from comment c,orders o where c.orders_id=o.id and c.create_time <= #{endTime}
<if test="startTime != null">
and c.create_time > #{startTime}
</if>
group by o.business_id)t
set b.star_total_num = b.star_total_num + t.star_num,b.comment_total_num = b.comment_total_num + t.comment_num
where b.id=t.business_id
</update>
</mapper>
输出日志
格式:logging.level.Mapper类的包=debug
配置文件
SpringBoot使用的一个全局配置文件,配置文件名是固定的:
在application.properties 文件中写 server.port=8081,表示应用启动端口为8081
pom.xml 是java的依赖包配置文件,所有的依赖包都在这里配置,配置之后springBoot会自动下载所有的依赖包,相当于js中的package.json文件