第1集 新版Mybatis开发必备调试之控制台打印Sql
简介:讲解Mybatis在控制台打印sql
Mybatis使⽤流程 :
- 创建mybatis-confifig.xml 全局的配置⽂件
- 创建XXXMapper.xml配置⽂件
- 创建SqlSessionFactory
- ⽤SqlSessionFactory创建SqlSession对象
- ⽤SqlSession执⾏增删改查CRUD
内置的⽇志⼯⼚提供⽇志功能, 使⽤log4j配置打印sql,添加依赖
<dependency><groupId>org.slf4j</groupId><artifactId>slf4j-log4j12</artifactId><version>1.7.30</version></dependency>
在应⽤的classpath中创建名称为 log4j.properties 的⽂件
#如果写error的话,只会在报错的时候显示执行的SQL语句 log4j.rootLogger=debug, stdout #项目的名字 log4j.logger.XJ.com..MybatisDemo=DEBUG log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
第2集 Mybatis实战参数别名使⽤之查询视频列表
简介:讲解Mybatis查询视频列表和参数别名的使⽤
查询视频列表,返回
List
查询⼀样,结果使⽤List
接收
两种取别名方式:
<typeAliases> <typeAlias type="Xj.com.MybatisDemo.domain.Video" alias="Video"></typeAlias> <!--<!–package取别名,别名一般是类名–>--> <!--<package name="Xj.com.MybatisDemo.domain"></package>--> </typeAliases>
常⽤的查询
默认参数查询
单个参数,可以使⽤别名,也可以使⽤默认的名称,默认名称的话可以随意
使⽤参数别名
模糊查询
mysql
⾃带函数使⽤
mapper.xml文件:
<select id="queryVideoByPointAndTitleLike" resultType="Video" parameterType="Video">
select * from video
where point = #{point}and
title like concat("%",#{title},"%")
</select>
mapper接口:
//查询评分8.8,标题包含JavaScript行
List<Video> queryVideoByPointAndTitleLike(Video video);
Video实体类:
public class Video {
private int id;
private String title;
private String summary;
private String CoverImg;
private int price;
private Date CreateTime;
private int Cid;
private double point;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSummary() {
return summary;
}
public void setSummary(String summary) {
this.summary = summary;
}
public String getCoverImg() {
return CoverImg;
}
public void setCoverImg(String coverImg) {
CoverImg = coverImg;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public Date getCreateTime() {
return CreateTime;
}
public void setCreateTime(Date createTime) {
CreateTime = createTime;
}
public int getCid() {
return Cid;
}
public void setCid(int cid) {
Cid = cid;
}
public double getPoint() {
return point;
}
public void setPoint(double point) {
this.point = point;
}
@Override
public String toString() {
return "Video{" +
"id=" + id +
", title='" + title + '\'' +
", summary='" + summary + '\'' +
", CoverImg='" + CoverImg + '\'' +
", price=" + price +
", CreateTime=" + CreateTime +
", Cid=" + Cid +
", point=" + point +
'}';
}
}
测试类:
//模糊查询测试
@Test
public void LikeSelecttest(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
VideoMapper mapper = sqlSession.getMapper(VideoMapper.class);
Video video = new Video();
video.setPoint(8.8);
video.setTitle("JavaScript");
//查询评分位8.8,标题包含JavaScript行
List<Video> videos = mapper.queryVideoByPointAndTitleLike(video);
System.out.println(videos.toString());
}
运行结果:
重点:参数记得取别名,⽅便识别和使⽤
第3集 Mybatis 配置驼峰字段映射java对象和数据库字段
简介:讲解Mybatis配置驼峰字段到java类
数据库字段是下划线,
java
属性是驼峰,怎么查询映射上去?
⽅法⼀: select cover_img as coverImg from video //
多字段怎么办
Mybatis⾃带配置( 加在⽂件顶部)
<!-- 下划线⾃动映射驼峰字段 --><settings><setting name="mapUnderscoreToCamelCase" value="true"/></settings>
<select id="queryVideoByPointAndTitleLike" resultType="Video" parameterType="Video">
select * from video
where point = #{point}and
title like concat("%",#{title},"%")
</select>
mapper接口:
//查询评分8.8,标题包含JavaScript行
List<Video> queryVideoByPointAndTitleLike(Video video);
测试类:
@Test
public void LikeSelecttest() throws ParseException {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
VideoMapper mapper = sqlSession.getMapper(VideoMapper.class);
Video video = new Video();
video.setPoint(8.8);
video.setTitle("JavaScript");
//查询评分位8.8,标题包含JavaScript行
List<Video> videos = mapper.queryVideoByPointAndTitleLike(video);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String CreateTime = dateFormat.format(videos.get(0).getCreateTime());
//字符串转换为Date类型
Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(CreateTime);
videos.get(0).setCreateTime(date);
System.out.println(CreateTime);
System.out.println(videos.toString());
}
运行结果:
CreateTime和Cid也映射上去了。
第4集 Mybatis⼊参parameterType和取值类型讲解
简介:讲解
Mybatis
⼊参
parameterType
和取值类型讲解
- parameterType 参数类型
- 可以是基本类型
parameterType="java.lang.Long"parameterType="java.lang.String
- 可以是Java集合List或者Map
parameterType="java.util.Map"parameterType="java.util.List"
- 可以是Java⾃定义对象
parameterType="net.xdclass.online_class.domain.Video"
取参数值,具体某个字段的类型,从java类型映射到数据库类型
- 例⼦ #{title, jdbcType=VARCHAR}
- 注意:
-
多数情况不加是正常使⽤,但是如果出现报错:⽆效的列类型,则是缺少 jdbcType;
-
只有当字段可为 NULL 时才需要 jdbcType 属性常⻅的数据库类型和java 列席对⽐JDBC Type Java TypeCHAR StringVARCHAR StringLONGVARCHAR StringNUMERIC java.math.BigDecimalDECIMAL java.math.BigDecimalBIT booleanBOOLEAN booleanTINYINT byteSMALLINT shortINTEGER INTEGERINTEGER intBIGINT longREAL floatFLOAT doubleDOUBLE doubleBINARY byte[]VARBINARY byte[]LONGVARBINARY byte[]DATE java.sql.DateTIME java.sql.TimeTIMESTAMP java.sql.TimestampCLOB ClobBLOB BlobARRAY ArrayDISTINCT mapping of underlying typeSTRUCT StructREF RefDATALINK java.net.URL
-
-
第5集 Mybatis实战插⼊语法之视频新增和⾃增主键
简介:讲解Mybatis插⼊语法的使⽤和如何获得⾃增主键
新增⼀条视频记录
Mapper.xml文件:
<!--插入一条记录 useGeneratedKeys返回插入的id值-->
<insert id="add" parameterType="Video" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
insert into video(`title`,`price`,`create_time`,`c_id`,`point`)
value(#{title,jdbcType=VARCHAR},
#{price,jdbcType=INTEGER},#{CreateTime,jdbcType=DATE},
#{Cid,jdbcType=INTEGER},#{point,jdbcType=DOUBLE})
</insert>
VideoMapper接口:
//新增一条记录,并且自动返回插入的id且映射到对象中
int add(Video video);
测试类:
//插入测试
@Test
public void AddVideoTest(){
Video video = new Video();
video.setTitle("自学课程");
video.setCreateTime(new Date());
video.setPrice(44);
video.setCid(6);
video.setPoint(9.9);
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
VideoMapper mapper = sqlSession.getMapper(VideoMapper.class);
mapper.add(video);
//提交事务
sqlSession.commit();
System.out.println("插入完之后,id会自动映射到该对象中:"+video);
SqlSessionUtil.CloseSqlSession();
}
运行结果:返回了自增id的值并且自动映射到对象中
第6集 Mybatis实战foreach批量插⼊语法之视频批量插⼊
批量插⼊多条视频记录
foreach: ⽤于循环拼接的内置标签,常⽤于 批量新增、in查询等常⻅
包含以下属性:collection:必填,值为要迭代循环的集合类型,情况有多种⼊参是List 类型的时候, collection 属性值为 list⼊参是Map 类型的时候, collection 属性值为 map 的 key 值item:每⼀个元素进⾏迭代时的别名index:索引的属性名,在集合数组情况下值为当前索引值,当迭代对象是 map 时,这个值是 map的 keyopen:整个循环内容的开头字符串close:整个循环内容的结尾字符串separator: 每次循环的分隔符
<!--批量插⼊-->
<insert id="addBatch"
parameterType="net.xdclass.online_class.domain.Video"
useGeneratedKeys="true" keyProperty="id" keyColumn="id" >
INSERT INTO `video` ( `title`, `summary`, `cover_img`, `price`,
`create_time`, `point`)
VALUES
<foreach collection="list" item="video" separator=",">
(#{video.title,jdbcType=VARCHAR},#{video.summary,jdbcType=VARCHAR},#
{video.coverImg,jdbcType=VARCHAR},
#{video.price,jdbcType=INTEGER},
#{video.createTime,jdbcType=TIMESTAMP},#{video.point,jdbcType=DOUBLE})
</foreach>
如果批量插⼊要获取⾃增id, 可以按照单条记录获得⾃增id的⽅式