注解开发——Mybatis(四）
注解开发
使用注解方式，代替Dao层xml文件，完成数据库的操作。
注意：当使用注解方式之后，在类路径下，就不能存在Dao层的xml文件，否则xml解析时会报错。
一、mybatis的xml文件配置
<?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>
<properties resource="jdbc.properties">
</properties>
<!--配置延迟加载-->
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="true"/>
</settings>
<!--类配置别名-->
<typeAliases>
<package name="com.one.mybatis.domain"/>
</typeAliases>
<!--在configuration标签下配置-->
<!--配置分页-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!--sql语言的查询-->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC">
</transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--自动映射dao接口和xml文件的关系/找到带有注解的Dao层接口所在位置-->
<package name="com.one.mybatis.dao"/>
<package name="com.one.mybatis.annotationDao"/>
</mappers>
</configuration>二、注解实现Dao层接口规则
1. 一对一查询
Account和User是一对一的关系。
findAll(): 查询所有Account表的信息，同时立即加载关联的User信息
@Select("select * from account")
@Results(id ="account", value = {
@Result(id = true, property = "id", column = "id"),
@Result(property = "uid", column = "uid"),
@Result(property = "money", column = "money"),
@Result(property = "user", column = "uid", one = @One(
select = "com.one.mybatis.annotationDao.UserAnnotationDao.findById",
fetchType = FetchType.EAGER
))
})
List<Account> findAll();
com.one.mybatis.annotationDao.UserAnnotationDao.findById方法实现如下：
@Select("select * from user, user_role where user.id=user_role.uid and user_role.rid=#{rid}")
List<User> findByRid(Integer rid);2. 一对多查询
User和Account是一对多的关系。
findAll(): 查询所有User用户的信息，并延迟加载关联的多个Account信息
@Select("select * from user")
@Results(id = "user", value = {
@Result(id = true, property = "id", column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "sex",column = "sex"),
@Result(property = "address", column = "address"),
@Result(property ="accounts", column = "id", many = @Many(
select = "com.one.mybatis.annotationDao.AccountAnnotationDao.findByUid",
fetchType = FetchType.LAZY))
})
List<User> findAll();
com.one.mybatis.annotationDao.AccountAnnotationDao.findByUid实现方法如下：
@Select("select * from account where uid=#{uid}")
List<Account> findByUid(Integer uid);3. 多对多查询
Role和User是多对多的关系。
findAll():查询所有Role信息，并根据Role.id查询所有关联的多个User信息。
@Select("select * from user")
@Results(id = "user", value = {
@Result(id = true, property = "id", column = "id"),
@Result(property = "username",column = "username"),
@Result(property = "birthday",column = "birthday"),
@Result(property = "sex",column = "sex"),
@Result(property = "address", column = "address"),
@Result(property = "roles", column = "id", many = @Many(
select = "com.one.mybatis.annotationDao.RoleAnnotationDao.findByUid",
fetchType = FetchType.LAZY
))
})
List<User> findAll();
com.one.mybatis.annotationDao.RoleAnnotationDao.findByUid定义如下：
@Select("select * from role JOIN user_role on role.id=user_role.rid and user_role.uid=#{uid}")
List<Role> findByUid(Integer uid);4. 动态查询
使用<if>标签，动态查询User信息。
@Select("<script>select * from user where 1=1 \n" +
" <if test=\"id !=null \">and id = #{id} </if>\n" +
" <if test=\"username !=null \">and username = #{username} </if>\n" +
" <if test=\"birthday !=null \">and birthday = #{birthday} </if>\n" +
" <if test=\"sex !=null \">and sex = #{sex} </if>\n" +
" <if test=\"address !=null \">and address = #{address} </if>\n" +
" </script>")
List<User> findDynamically(User user);5. 通过id查询
@Select("select * from user, user_role where user.id=user_role.uid and user_role.rid=#{rid}")
List<User> findByRid(Integer rid);6. 插入数据
@Insert("insert into user values(null, #{username}, #{birthday}, #{sex}, #{address})")
void insert(User user);7.更新数据
@Update("update user set username=#{username}, birthday=#{birthday}, sex=#{sex}, address=#{address} where id=#{id}")
void update(User user);9.聚合函数查询
@Select("select count(*) from user")
int findCount();10.模糊查询
@Select("select * from user where username like #{username}")
List<User> findByName(String username);11.插入数据并返回id值
@Insert("insert into user values(null,#{username}, #{email}, #{password}, #{phoneNum})")
@SelectKey(keyProperty = "id", keyColumn = "id", resultType =Integer.class, before = false, statement = "select last_insert_id()")
void save(User user);其中，@SelectKey返回最后一条数据的id值，并保存在User实体类的id成员变量中。
keyProperty = "id"：返回值保存在实体类中的成员变量名
keyColumn = "id"：查询的数据库中列名
resultType =Integer.class：返回类型
before = false：是否在SQL语句执行之前查询
statement = "select last_insert_id()"：查询语句12.执行多条SQL语句
配置jdbc的url,加入allowMultiQueries=true
url=jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC&allowMultiQueries=true@Delete({"delete from user_role where userId=#{id};","delete from user where id=#{id};"})
void delete(Integer id);
