MyBatis学习记录(一)
分三部分记录,MyBatis,MyBatis Spring和Mybatis Generator。
这一片记录MyBatis的一些学习心得。
基本概念
SqlSession
:创建查询使用的是SqlSession
,叫做Session,实际上也是维护了一个数据库的连接,内部通过Connection对象连接数据库。SqlSessionFactory
:用来创建SqlSession
,可以指定一些SqlSession
的属性SqlSessionFacotryBuilder
:用来创建SqlSessionFactory
。而SqlSessionFacotryBuilder
是通过MyBatis的配置文件或配置类创建的
配置
可以通过文件或者类的方法,一个简单的配置文件:
<?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>
<typeAliases>
<typeAlias alias="User"
type="com.mosakashaka.mybatissample.model.User" />
</typeAliases>
<!-- 环境,可以配置多个,default:指定采用哪个环境 -->
<environments default="dev">
<!-- id:唯一标识 -->
<environment id="dev">
<!-- 事务管理器,JDBC类型的事务管理器 -->
<transactionManager type="JDBC" />
<!-- 数据源,池类型的数据源 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/MyMapper.xml" />
</mappers>
</configuration>
有这写信息(不完全列出)
- typeAliases:mapper中使用的类型别名,MyBatis内置java标准类的别名,如果是自定义类,包名比较长,定义别名比较方便
- environments:可以定义多个环境,一般用来指定不同的dataSource配置,其中dataSource也可以指定不同的类型,这里
POOLED
标识会维护一个连接缓存池,避免频繁建立断开连接 - 定义Mappers:mapper是mybatis中的一个基本概念,表示数据库中的表(或者是查询返回的结果),如何map到实体类中。
mappers
一个包含了基本配mapper配置的mapper文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="MyMapper">
<resultMap id="result" type="User">
<result property="id" column="id" />
<result property="username" column="username" />
<result property="password" column="password" />
<result property="name" column="name" />
<result property="age" column="age" />
<result property="sex" column="sex" />
<result property="birthday" column="birthday" />
</resultMap>
<select id="getAll" resultMap="result">
SELECT * FROM tb_user;
</select>
<select id="getById" parameterType="int" resultType="User">
SELECT *
FROM tb_user WHERE id = #{id} ;
</select>
<insert id="insertList" useGeneratedKeys="true" keyProperty="id">
insert into tb_user (username, password, name, age, sex, birthday)
values
<foreach item="item" collection="list" separator=",">
(#{item.username}, #{item.password}, #{item.name}, #{item.age},
#{item.sex}, #{item.birthday})
</foreach>
</insert>
</mapper>
- mapper文件有
namespace
属性,在这里并没有很大的约束,但是到spring里会比较关键 - mappers定义一系列sql语句段:
select
,insert
,update
,delete
等,在其中写sql语句执行 - sql语句段有id,这也是关进属性,代码中指定要执行哪个sql时,需要靠
namespace.sqlid
来说明执行的语句 - sql中可以通过
#{param}
的方式注入调用传入的参数 - sql支持条件化拼接:
if
,when
,foreach
等。 - 强大的
resultMap
功能,可以将查询结果map到复杂的实体类
resultMap
通过语句中的resultMap=xxx
标签,指定语句返回结果被一个resultMap
解析关联到实体类。
一个复杂的查询selectBlogDetails
通过id为detailedBlogResultMap
的ResultMap关联到Blog
实体类:
<!-- Very Complex Statement -->
<select id="selectBlogDetails" resultMap="detailedBlogResultMap">
select
B.id as blog_id,
B.title as blog_title,
B.author_id as blog_author_id,
A.id as author_id,
A.username as author_username,
A.password as author_password,
A.email as author_email,
A.bio as author_bio,
A.favourite_section as author_favourite_section,
P.id as post_id,
P.blog_id as post_blog_id,
P.author_id as post_author_id,
P.created_on as post_created_on,
P.section as post_section,
P.subject as post_subject,
P.draft as draft,
P.body as post_body,
C.id as comment_id,
C.post_id as comment_post_id,
C.name as comment_name,
C.comment as comment_text,
T.id as tag_id,
T.name as tag_name
from Blog B
left outer join Author A on B.author_id = A.id
left outer join Post P on B.id = P.blog_id
left outer join Comment C on P.id = C.post_id
left outer join Post_Tag PT on PT.post_id = P.id
left outer join Tag T on PT.tag_id = T.id
where B.id = #{id}
</select>
<!-- Very Complex Result Map -->
<resultMap id="detailedBlogResultMap" type="Blog">
<constructor>
<idArg column="blog_id" javaType="int"/>
</constructor>
<result property="title" column="blog_title"/>
<association property="author" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
<result property="password" column="author_password"/>
<result property="email" column="author_email"/>
<result property="bio" column="author_bio"/>
<result property="favouriteSection" column="author_favourite_section"/>
</association>
<collection property="posts" ofType="Post">
<id property="id" column="post_id"/>
<result property="subject" column="post_subject"/>
<association property="author" javaType="Author"/>
<collection property="comments" ofType="Comment">
<id property="id" column="comment_id"/>
</collection>
<collection property="tags" ofType="Tag" >
<id property="id" column="tag_id"/>
</collection>
<discriminator javaType="int" column="draft">
<case value="1" resultType="DraftPost"/>
</discriminator>
</collection>
</resultMap>
- association:一对一的查询,通过id属性匹配
- collection:一对多的匹配,通过id属性匹配
- discriminator:根据某一列不同的值,指定
resultMap
使用其他形式。这个例子里不是那么有用,因为这个属性会覆盖所有跟属性。
使用
创建连接
// 指定全局配置文件
String resource = "mybatis-config.xml";
// 读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
// 构建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取sqlSession
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
//执行一些CRUD操作
}
这里用配置文件创建了SqlSessionFactoryBuilder
,进而创建SqlSessionFactory
->sqlSession
。
在sqlSession
执行一些数据库操作
使用
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
System.out.println("sessecion:" + sqlSession.toString());
// 操作CRUD,第一个参数:指定statement,规则:命名空间+“.”+statementId
// bulk insert
User u1 = new User();
u1.setName("测试1");
u1.setUsername("test1");
u1.setPassword("123456");
u1.setSex(0);
u1.setAge(30);
u1.setBirthday(new Date(-1, 2, 10));
User u2 = new User();
u2.setName("测试2");
u2.setUsername("test2");
u2.setPassword("123456");
u2.setSex(0);
u2.setAge(320);
u2.setBirthday(new Date(1, 3, 10));
sqlSession.insert("MyMapper.insertList", new ArrayList<User>() {
{
add(u1);
add(u2);
}
});
sqlSession.commit();
User user = sqlSession.selectOne("MyMapper.getById", 1);
List<User> users = sqlSession.<User>selectList("MyMapper.getAll");
System.out.println(user);
for (User u : users) {
System.out.println(u);
}
}
通过SqlSession
的selectList
,selectOne
,delete
,insert
等方法,调用xml中配置的sql(传入namespace.sqlId)调用,参数是sql中指定的参数。