Spring开发复习小记（2）-与MyBatis集成

Spring与MyBatis集成

在上一篇文章中，我用Java类实现了Spring配置的Web容器启动，并且还做了上传文件的测试，今天我就来集成MyBatis，搭建数据库访问层。

添加依赖

与Hibernate依赖添加不同是，我们添加MyBatis只用添加两个依赖
，在写这篇记录的时候，我选用的最新版本如下所示。

<dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.0</version>
</dependency>
<dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.1</version>
</dependency>

配置RootConfig

在第一篇提到的RootConfig下面添加如下的Bean配置

//*****************************省略号
public class RootConfig {
//*****************************省略号
    Logger logger = Logger.getLogger(this.getClass());
    private String jdbcDriverClassName = "com.mysql.jdbc.Driver";

    private String jdbcUsername = "root";

    private String jdbcUrl = "jdbc:mysql://localhost:3306/shiro?useSSL=true&charset=utf-8";

    private String jdbcPassword = "root";

    @Bean
    public DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(jdbcDriverClassName);
        dataSource.setUrl(jdbcUrl);
        dataSource.setUsername(jdbcUsername);
        dataSource.setPassword(jdbcPassword);
        dataSource.setMaxActive(30);
        return dataSource;
    }

    @Bean
    public DataSourceTransactionManager transactionManager() throws PropertyVetoException {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactoryBean sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource());
        sqlSessionFactory.setConfigLocation(new ClassPathResource("config.xml"));
        sqlSessionFactory.setMapperLocations(new ClassPathResource[]{new ClassPathResource("mapper/UserDao.xml"),
                                                                        new ClassPathResource("mapper/RoleDao.xml"),
                                                                        new ClassPathResource("mapper/PermissionDao.xml")});
        sqlSessionFactory.setTypeAliasesPackage("com.hc.model");

        return sqlSessionFactory;
    }

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setBasePackage("com.hc.dao");
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        logger.debug("--------------------------->MapperScannerConfigurer启动了");
        return mapperScannerConfigurer;
    }

}

首先我们需要配置一个数据连接池，这是开发标配，我采用了阿里开源的Druid，在这里一些配置一些简单的数据库地址用户密码就好。不要问我为什么不用配置文件，我试了很多次，都报了空指针错误，蛋疼ing。。。（心碎状）

再下来，我们需要配置MyBatis必须的SqlSessionFactory,但是要与Spring集成的话，我们要去配置org.mybatis.spring包下的SqlSessionFactoryBean。
由于MyBatis官方文档中推荐的是XML配置，所以我选择了这种方式，怎么稳妥怎么来。
配置SqlSessionFactoryBean，一般要指明要用的DateSource数据源，Mapper文件的所在文件夹，还有就是被映射的Model包，MyBatis的总配置文件（也就是sqlSessionFactory.setConfigLocation(new ClassPathResource("config.xml"));这段代码）不需要明确指出，可指可不指。

MapperScannerConfigurer是要去找对应的Dao接口的，我们都知道，使用MyBatis的特点之一就是Dao用接口，用mapper文件写Sql语句，最后由MyBatis给我们具体实现对数据库的操作。

DataSourceTransactionManager：如果想使用事务，那么这个类是不能缺少的，看名字就知道了嘛

不知道有没有小伙伴注意到我的SqlSessionFactoryBean配置了name属性，这个是为了后面跟Shiro集成用的，留点小期待吧~

完成Dao层

数据库建表的DDL

在这里我使用了开涛大神给出的一个参考DDL

drop table if exists sys_users;
drop table if exists sys_roles;
drop table if exists sys_permissions;
drop table if exists sys_users_roles;
drop table if exists sys_roles_permissions;

create table sys_users (
  id bigint auto_increment,
  username varchar(100),
  password varchar(100),
  salt varchar(100),
  locked bool default false,
  constraint pk_sys_users primary key(id)
) charset=utf8 ENGINE=InnoDB;
create unique index idx_sys_users_username on sys_users(username);

create table sys_roles (
  id bigint auto_increment,
  role varchar(100),
  description varchar(100),
  available bool default false,
  constraint pk_sys_roles primary key(id)
) charset=utf8 ENGINE=InnoDB;
create unique index idx_sys_roles_role on sys_roles(role);

create table sys_permissions (
  id bigint auto_increment,
  permission varchar(100),
  description varchar(100),
  available bool default false,
  constraint pk_sys_permissions primary key(id)
) charset=utf8 ENGINE=InnoDB;
create unique index idx_sys_permissions_permission on sys_permissions(permission);

create table sys_users_roles (
  user_id bigint,
  role_id bigint,
  constraint pk_sys_users_roles primary key(user_id, role_id)
) charset=utf8 ENGINE=InnoDB;

create table sys_roles_permissions (
  role_id bigint,
  permission_id bigint,
  constraint pk_sys_roles_permissions primary key(role_id, permission_id)
) charset=utf8 ENGINE=InnoDB;

其实主要就是三个表

• 用户表 sys_users
• 角色表 sys_roles
• 权限表 sys_permissions

三个Dao对象

public interface UserDao {
    User findById(@Param("id") long id);
    User findByUsername(@Param("username")String username);
    int insertOne(@Param("user") User user);
    int updatePassword(@Param("id") long id,@Param("password") String password);
}

public interface RoleDao {
    Role findRoleById(@Param("id") long id);
    Role findRoleByRole(String roleName);
    Set<Role> listRole(long userId);
    Set<String> listRoleName(long userId);
}

public interface PermissionDao {
    //直接查询
    Set<Permission> findPermissionByRole(long role_id);

    //通过left join查询
    Set<Permission> findPermissionByUser(long user_id);

    Set<String> findPermissionNameByUserId(long user_id);
}

为了方便，我只是用了查询。对应的也就是简单的select语句，会有left join操作，没什么好写的了，我直接给出mapper的源码吧

UserDao.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 = "com.hc.dao.UserDao">

    <sql id="cols">
        sys_users.id,
        sys_users.username,
        sys_users.password,
        sys_users.salt,
        sys_users.locked
    </sql>

    <select id="findById" parameterType="java.lang.Long" resultType="com.hc.model.User">
        SELECT <include refid="cols"/> FROM sys_users WHERE id = #{id}
    </select>

    <select id="findByUsername" parameterType="java.lang.String" resultType="com.hc.model.User">
        SELECT <include refid="cols"/> FROM sys_users WHERE username = #{username}
    </select>

    <insert id="insertOne" parameterType="com.hc.model.User">
        INSERT ignore INTO sys_users( <include refid="cols"/> )
        VALUES (#{user.id},#{user.username},#{user.password},#{user.salt},#{user.locked})
    </insert>

    <update id="updatePassword">
        UPDATE sys_users SET password = #{password} WHERE id = #{id}
    </update>
</mapper>

RoleDao.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 = "com.hc.dao.RoleDao">
    <select id="findRoleById" parameterType="java.lang.Long" resultType="com.hc.model.Role">
        SELECT
          *
        FROM
          sys_roles
        WHERE
          id = #{id}
    </select>

    <select id="findRoleByRole" parameterType="java.lang.String" resultType="com.hc.model.Role">
        SELECT
          *
        FROM
          sys_roles
        WHERE
          role = #{roleName}
    </select>

    <select id="listRole" parameterType="java.lang.Long" resultType="com.hc.model.Role">
        SELECT sys_roles.id,
                sys_roles.role,
                sys_roles.description,
                sys_roles.available
        FROM sys_roles LEFT JOIN sys_users_roles
                                ON sys_users_roles.role_id = sys_roles.id
                                WHERE sys_users_roles.user_id = #{userId}
    </select>

    <select id="listRoleName" parameterType="java.lang.Long" resultType="java.lang.String">
        SELECT
        sys_roles.role
        FROM sys_roles LEFT JOIN sys_users_roles
        ON sys_users_roles.role_id = sys_roles.id
        WHERE sys_users_roles.user_id = #{userId}
    </select>
</mapper>

PermissionDao.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="com.hc.dao.PermissionDao">
    <select id="findPermissionByRole" parameterType="java.lang.Long" resultType="com.hc.model.Permission">
        SELECT
          sys_permissions.id,
          sys_permissions.permission,
          sys_permissions.description,
          sys_permissions.available
        FROM
          sys_permissions
        LEFT JOIN
          sys_roles_permissions
        ON
          sys_permissions.id = sys_roles_permissions.permission_id
        WHERE
          sys_roles_permissions.role_id = #{role_id}
    </select>

    <select id="findPermissionByUser" parameterType="java.lang.Long" resultType="com.hc.model.Permission">
        SELECT sys_permissions.id,
                sys_permissions.available,
                sys_permissions.permission,
                sys_permissions.description
        FROM
          sys_permissions
        WHERE sys_permissions.id
            IN (SELECT
                    sys_roles_permissions.permission_id
                FROM sys_users_roles
                  LEFT JOIN
                  sys_roles_permissions
                  ON
                    sys_users_roles.role_id = sys_roles_permissions.role_id
                  WHERE sys_users_roles.user_id = #{user_id})
    </select>

    <select id="findPermissionNameByUserId" parameterType="java.lang.Long" resultType="java.lang.String">
        SELECT
          sys_permissions.permission
        FROM
          sys_permissions
        WHERE sys_permissions.id
        IN (SELECT
          sys_roles_permissions.permission_id
        FROM sys_users_roles
        LEFT JOIN
          sys_roles_permissions
        ON
          sys_users_roles.role_id = sys_roles_permissions.role_id
        WHERE sys_users_roles.user_id = #{user_id})
    </select>
</mapper>

至此，不妨做个单元测试，去验证对错吧！