持续学习&持续更新中…
守破离
【Java从零到架构师第③季】【10】MyBatis-实现DAO层
MyBatis实现DAO层的三种方案
准备:JavaBean、DAO接口、映射XML
- bean:Skill
- dao:SkillDao
- 映射xml:skill.xml
<sql id="sqlListAll">
SELECT id, name, level, created_time FROM skill
</sql>
<insert id="add" parameterType="Skill" useGeneratedKeys="true" keyProperty="id">
INSERT INTO skill(name, level) VALUES(#{name}, #{level})
</insert>
<delete id="remove" parameterType="Int">
DELETE FROM skill WHERE id = #{id}
</delete>
<update id="update" parameterType="Skill">
UPDATE skill SET name = #{name}, level = #{level} WHERE id = #{id}
</update>
<select id="get" parameterType="Int" resultType="Skill">
<include refid="sqlListAll"/> WHERE id = #{id}
</select>
<select id="list" resultType="Skill">
<include refid="sqlListAll"/>
</select>
方法一:DAO实现类+映射XML
- dao实现类
public class SkillDaoImpl implements SkillDao {
@Override
public boolean add(Skill skill) {
try (SqlSession session = MyBatises.openSession(true)) {
return session.insert("skill.add", skill) > 0;
}
}
@Override
public boolean remove(Integer id) {
try (SqlSession session = MyBatises.openSession(true)) {
return session.delete("skill.remove", id) > 0;
}
}
@Override
public boolean update(Skill skill) {
try (SqlSession session = MyBatises.openSession(true)) {
return session.update("skill.update", skill) > 0;
}
}
@Override
public Skill get(Integer id) {
try (SqlSession session = MyBatises.openSession()) {
return session.selectOne("skill.get", id);
}
}
@Override
public List<Skill> list() {
try (SqlSession session = MyBatises.openSession()) {
return session.selectList("skill.list");
}
}
}
- 测试类
public class SkillDaoTest {
@Test
public void add() {
SkillDao dao = new SkillDaoImpl();
final Skill skill = new Skill("learn programming", 10086);
Assert.assertTrue(dao.add(skill));
System.out.println(skill.getId());
}
@Test
public void remove() {
SkillDao dao = new SkillDaoImpl();
Assert.assertTrue(dao.remove(41));
}
@Test
public void update() {
SkillDao dao = new SkillDaoImpl();
Skill skill = new Skill("666", 666);
skill.setId(42);
Assert.assertTrue(dao.update(skill));
}
@Test
public void get() {
SkillDao dao = new SkillDaoImpl();
System.out.println(dao.get(1).getName());
}
@Test
public void list() {
SkillDao dao = new SkillDaoImpl();
List<Skill> skills = dao.list();
for (Skill skill : skills) {
System.out.println(skill.getId() + "____" + skill.getName());
}
}
}
方法二:getMapper+映射XML
- 一个XML映射文件下,
标签的id
不能重复,必须唯一。 - 相应的,使用注解+DAO接口实现DAO层时,DAO接口中的方法名也不能重复,必须唯一。
- 测试类
public class SkillDaoTest {
@Test
public void add() {
try (final SqlSession session = MyBatises.openSession(true)) {
SkillDao dao = session.getMapper(SkillDao.class);
final Skill skill = new Skill("learn programming", 10086);
Assert.assertTrue(dao.add(skill));
System.out.println(skill.getId());
}
}
@Test
public void remove() {
try (final SqlSession session = MyBatises.openSession(true)) {
SkillDao dao = session.getMapper(SkillDao.class);
Assert.assertTrue(dao.remove(45));
}
}
@Test
public void update() {
try (final SqlSession session = MyBatises.openSession(true)) {
SkillDao dao = session.getMapper(SkillDao.class);
Skill skill = new Skill("666", 666);
skill.setId(30);
Assert.assertTrue(dao.update(skill));
}
}
@Test
public void get() {
try (final SqlSession session = MyBatises.openSession()) {
SkillDao dao = session.getMapper(SkillDao.class);
System.out.println(dao.get(1).getName());
}
}
@Test
public void list() {
try (final SqlSession session = MyBatises.openSession()) {
SkillDao dao = session.getMapper(SkillDao.class);
List<Skill> skills = dao.list();
for (Skill skill : skills) {
System.out.println(skill.getId() + "____" + skill.getName());
}
}
}
}
方法三:getMapper+注解
https://mybatis.org/mybatis-3/zh/java-api.html
- mybatis-config.xml:
<mappers>
<mapper class="programmer.lp.dao.SkillDao"/>
</mappers>
- SkillDao:
public interface SkillDao {
@Insert(value="INSERT INTO skill(name, level) VALUES(#{name}, #{level})")
boolean add(Skill skill);
@Delete("DELETE FROM skill WHERE id = #{id}")
boolean remove(Integer id);
@Update("UPDATE skill SET name = #{name}, level = #{level} WHERE id = #{id}")
boolean update(Skill skill);
@Select("SELECT id, name, level, created_time FROM skill WHERE id = #{id}")
Skill get(Integer id);
@Select("SELECT id, name, level, created_time FROM skill")
List<Skill> list();
}
- 测试类:和方法二的测试类一样
XML和注解混合使用
注解的功能没有XML强大,有时候满足不了需求,所以可以XML+注解混合使用
比如:上面方法三单独使用注解时,插入一条记录(SkillDao.add)直接获取不到id,只能使用XML的useGeneratedKeys、keyProperty来获取id,因此,考虑XML和注解混合使用:
PS:其实可以使用@SelectKey
注解获取新插入对象的id,这儿只是想演示一下XML和注解如何混合使用而已
-
mybatis-config.xml
- 注意顺序:class必须放在resource之上
- 不能使用package标签:
<package name="programmer.lp.dao"/>
<mappers>
<mapper class="programmer.lp.dao.SkillDao" />
<mapper resource="mappers/skill.xml"/>
</mappers>
- SkillDao接口
public interface SkillDao {
boolean add(Skill skill);
@Delete("DELETE FROM skill WHERE id = #{id}")
boolean remove(Integer id);
@Update("UPDATE skill SET name = #{name}, level = #{level} WHERE id = #{id}")
boolean update(Skill skill);
@Select("SELECT id, name, level, created_time FROM skill WHERE id = #{id}")
Skill get(Integer id);
@Select("SELECT id, name, level, created_time FROM skill")
List<Skill> list();
}
- skill.xml:
<?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="programmer.lp.dao.SkillDao">
<insert id="add" parameterType="Skill" useGeneratedKeys="true" keyProperty="id">
INSERT INTO skill(name, level) VALUES(#{name}, #{level})
</insert>
</mapper>
- 测试类:同方法二的测试类
XML与注解的比较
MJ老师的忠告:以后学习Spring等框架的时候首先要牢牢掌握XML形式的使用方法,因为注解形式的用法是基于XML的,是先有XML再有注解的。
- XML形式书写起来更清晰、简洁、方便
- XML可以实现复杂的操作,功能比注解强大(比如:一次性查出所有内容、批量处理…,这些使用XML实现更方便)
- 使用XML可以更好的维护代码,不推荐使用注解来实现。
- .java文件部署到项目时会被编译为.class文件,.xml文件会直接被copy过去。
- 使用注解,就相当于把SQL语句写在Java代码中了,最终这些SQL会被编译为class字节码文件,字节码肯定不方便修改,只能修改Java代码然后重新编译生成字节码文件,然后将其再部署到服务器。
- 而使用XML映射文件配置SQL,则修改起来比较方便,不用修该Java代码,直接修改xml文件即可。
- 使用注解看起来很简洁,但项目较大时,XML文件则看起来更直观。
- 注解比较时髦,适合简单的小型项目
常用注解
https://mybatis.org/mybatis-3/zh/java-api.html
@SelectKey
-
XML形式:
<insert id="insert" parameterType="programmer.lp.Skill"> INSERT INTO skill(name, level) VALUES (#{name}, #{level}) <selectKey resultType="int" keyProperty="id" order="AFTER"> SELECT LAST_INSERT_ID() </selectKey> </insert>
-
注解形式
@Insert("INSERT INTO skill(name, level) VALUES(#{name}, #{level})") @SelectKey( statement = "SELECT LAST_INSERT_ID()", resultType = Integer.class, keyProperty = "id", before = false ) boolean add(Skill skill);
@Options:
@Options
中可以配置很多属性:
-
XML形式:
<insert id="add" parameterType="Skill" useGeneratedKeys="true" keyProperty="id"> INSERT INTO skill(name, level) VALUES(#{name}, #{level}) </insert>
-
注解形式:
@Insert("INSERT INTO skill(name, level) VALUES(#{name}, #{level})") @Options(useGeneratedKeys = true, keyProperty = "id") boolean add(Skill skill);
@Param
- 如果DAO接口有多个形参的话,比如下面这样:
- 方法名在编译之后就不存在了,会被编译为:
-
MyBatis是找不到正确的参数的,因此必须使用
@Param
注解:@Select("SELECT id, name, level, created_time FROM skill LIMIT #{start}, #{size}") List<Skill> listPage(@Param("start") Integer start,@Param("size") Integer size);
@CacheNamespace
-
对应
<cache/>
标签,用来设置二级缓存 -
二级缓存是namespace级别的
//@CacheNamespace() @CacheNamespace(readWrite = false, eviction = FifoCache.class) public interface SkillDao { // ... @Select("SELECT id, name, level, created_time FROM skill WHERE id = #{id}") @Options(useCache = false) Skill get(Integer id); }
<mapper namespace="skill"> <cache readOnly="true" eviction="FIFO"/> <!-- ... --> <select useCache="false" id="get" parameterType="Int" resultType="Skill"> SELECT id, name, level, created_time FROM skill WHERE id = #{id} </select> </mapper>
@Results、@Result、@One、@Many
-
@Results
对应<resultMap>
、@ResultMap
用来引用已经写好的@Results
-
@Result
对应<id>
、<result>
-
@One
对应<association>
、@Many
对应<collection>
xml代码:
<select id="getByPersonId" parameterType="Int" resultType="IdCard"> SELECT id, no, address FROM id_card WHERE person_id = #{personId} </select>
<select id="listByPersonId" parameterType="Int" resultType="BankCard"> SELECT id, no, amout FROM bank_card WHERE person_id = #{personId} </select>
<select id="listByPersonId" parameterType="Int" resultType="Job"> SELECT id, name, duty FROM job JOIN person_job ON person_job.job_id = id AND person_job.person_id = #{personId} </select>
<sql id="sqlListAll"> SELECT id, name FROM person </sql> <resultMap id="rmListPerson" type="Person"> <id property="id" column="id"/> <result property="name" column="name"/> <association property="idCard" javaType="IdCard" column="id" select="id_card.getByPersonId"/> <collection property="bankCards" ofType="BankCard" column="id" select="bank_card.listByPersonId"/> <collection property="jobs" ofType="Job" column="id" select="job.listByPersonId"/> </resultMap> <select id="get" parameterType="Int" resultMap="rmListPerson"> <include refid="sqlListAll"/> WHERE id = #{id} </select> <select id="list" resultMap="rmListPerson"> <include refid="sqlListAll"/> </select>
注解代码:
public interface IdCardDao { @Select("SELECT id, no, address FROM id_card WHERE person_id = #{personId}") IdCard getByPersonId(Integer personId); }
public interface BankCardDao { @Select("SELECT id, no, amout FROM bank_card WHERE person_id = #{personId}") List<BankCard> listByPersonId(Integer personId); }
public interface JobDao { @Select("SELECT id, name, duty FROM job " + "JOIN person_job " + "ON person_job.job_id = id AND person_job.person_id = #{personId}") List<Job> listByPersonId(Integer personId); }
public interface PersonDao { @Select("SELECT id, name FROM person WHERE id = #{id}") @Results( id = "rmListPerson", value={ @Result(property = "id", column = "id", id = true), @Result(property = "name", column = "name"), @Result( property = "idCard", column = "id", one = @One(select = "programmer.lp.dao.IdCardDao.getByPersonId") ), @Result( property = "bankCards", column = "id", many = @Many(select = "programmer.lp.dao.BankCardDao.listByPersonId") ), @Result( property = "jobs", column = "id", many = @Many(select = "programmer.lp.dao.JobDao.listByPersonId") ) } ) Person get(Integer id); @Select("SELECT id, name FROM person") @ResultMap("rmListPerson") List<Person> list(); }
如果想要延迟加载的话:
@ConstructorArgs、@Arg
-
@ConstructorArgs
对应<constructor>
,@Arg
对应<idArg>
、<arg>
-
方式一:
public Skill(@Param("name") String name, @Param("level") Integer level) { this.name = name; this.level = level; }
<resultMap id="rmList" type="Skill"> <constructor> <arg column="name" name="name"/> <arg column="level" name="level"/> <!-- <arg column="level" name="level"/> --> <!-- <arg column="name" name="name"/> --> </constructor> </resultMap> <select id="list" resultMap="rmList"> SELECT * FROM skill </select>
@Select("SELECT id, name, level, created_time FROM skill") @ConstructorArgs({ @Arg(column = "name", name = "name"), @Arg(column = "level", name = "level") // @Arg(column = "level", name = "level"), // @Arg(column = "name", name = "name") }) List<Skill> list();
-
方式二:
public Skill(String name, Integer level) { this.name = name; this.level = level; }
<resultMap id="rmList" type="Skill"> <constructor> <arg column="name" javaType="String"/> <arg column="level" javaType="Int"/> </constructor> </resultMap> <select id="list" resultMap="rmList"> SELECT * FROM skill </select>
@Select("SELECT id, name, level, created_time FROM skill") @ConstructorArgs({ @Arg(column = "name", javaType = String.class), @Arg(column = "level", javaType = Integer.class) }) List<Skill> list();
使用注解进行多表查询
- 使用注解进行多表查询时,不能使用如下方法:
- 只能使用这种方法:
-
如果要想延迟加载的话:
- 开启全局延迟加载:开启全局延迟加载后,所有设置了select属性的关联对象(association、collection)都会开启延迟加载
<setting name="lazyLoadingEnabled" value="true"/>
- 挨个配置延迟加载:
- 注解代码:与上面常用注解中讲@Results、@Result、@One、@Many的代码相同。
注意和一些建议
- 很多编程语言中,在编译方法时,默认不会保留形参变量名,而是会将其编译为arg1、arg2、arg3…或者param1、param2、param3…之类的东西
- 当然,你也可以通过设置编译器的编译参数这种方式,让其编译时保留形参变量名。
- MJ老师的忠告:以后学习Spring等框架的时候首先要牢牢掌握XML形式的使用方法,因为注解形式的用法是基于XML的,是先有XML再有注解的。
- 查询时设置
<id>标签
有助于提升性能
- 一个XML映射文件下,
标签的id
不能重复,必须唯一。 - 相应的,使用注解+DAO接口实现DAO层时,DAO接口中的方法名也不能重复,必须唯一。
-
注解中只有一个value值时,可以省略value不写:
@Delete(value="DELETE FROM skill WHERE id = #{id}") boolean remove(Integer id);
@Delete("DELETE FROM skill WHERE id = #{id}") boolean remove(Integer id);
-
当控制台有红色警告MySQL的SSL相关时,在打开数据库的时候,在url后面添加上
?useSSL="false"
即可。url=jdbc:mysql://localhost:3306/test_mybatis?useSSL=false
-
SkillDao的add、remove、update方法都只操作一条SQL语句,不涉及事务管理,因此可以自动提交事务。
-
批量处理SQL也是一条语句(如下会生成:
INSERT INTO skill(name, level) VALUES (?, ?) , (?, ?) , (?, ?) , (?, ?) , (?, ?)
),也可直接提交事务,不用手动进行事务管理,如果出现异常则就插入不成功,不会更新数据到数据库中。<insert id="adds" parameterType="List" useGeneratedKeys="true" keyProperty="id"> INSERT INTO skill(name, level) VALUES <foreach collection="list" separator="," item="skill"> (#{skill.name}, #{skill.level}) </foreach> </insert>
public void adds() { try (final SqlSession session = MyBatises.openSession(true)) { SkillDao dao = session.getMapper(SkillDao.class); List<Skill> lists = new ArrayList<>(); Skill skill = new Skill("learn 1", 1001); lists.add(skill); skill = new Skill("learn 2", 1002); lists.add(skill); skill = new Skill(null, null); lists.add(skill); // 这两个字段被约束为NOT NULL,因此会出现异常,数据库肯定插入不成功 skill = new Skill("learn 3", 1003); lists.add(skill); skill = new Skill("learn 4", 1004); lists.add(skill); Assert.assertTrue(dao.adds(lists)); for (Skill s : lists) { System.out.println(s.getId()); } } }
- SqlSession的
<T> T getMapper(Class<T> type);
方法会生成DAO接口的代理对象,其中使用了代理模式。
参考
小码哥-李明杰: Java从0到架构师③进阶互联网架构师.
本文完,感谢您的关注支持!