一、Mybatis
Orm 框架的本质是简化编程中操作数据库的编码,发展到现在基本上就剩两家了,一个是宣称可以不用写一句 Sql 的 Hibernate,一个是可以灵活调试动态 Sql 的 Mybatis ,两者各有特点,在企业级系统开发中可以根据需求灵活使用。发现一个有趣的现象:传统企业大都喜欢使用 Hibernate ,互联网行业通常使用 Mybatis 。
Hibernate
Hibernate 特点就是所有的 Sql 都用 Java 代码来生成,不用跳出程序去写(看) Sql ,有着编程的完整性,发展到最顶端就是 Spring Data Jpa 这种模式了,基本上根据方法名就可以生成对应的 Sql 了。
Mybatis
Mybatis 初期使用比较麻烦,需要各种配置文件、实体类、Dao 层映射关联、还有一大推其它配置。当然 Mybatis 也发现了这种弊端,初期开发了generator可以根据表结果自动生产实体类、配置文件和 Dao 层代码,可以减轻一部分开发量;后期也进行了大量的优化可以使用注解了,自动管理 Dao 层和配置文件等,发展到最顶端就是Spring Boot+ Mybatis 这种模式了,可以完全注解不用配置文件,也可以简单配置轻松上手。
二、添加Maven依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
三、application.yml配置
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
config-location: classpath:mybatis-config.xml
mapper-locations: classpath*:mapping/**/*.xml
四、mybatis-config.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>
<settings>
<!-- 打印查询语句 -->
<setting name="logImpl" value="STDOUT_LOGGING" />
<!--数据库中test_xx 转为驼峰命名testXx-->
<setting name="mapUnderscoreToCamelCase" value="true" />
<!-- 打开二级缓存 -->
<setting name="cacheEnabled" value="true" />
</settings>
<!-- 一些插件的加载 -->
<plugins>
<plugin interceptor="com.zxhy.strategy.TableShardInterceptor"/>
<plugin interceptor="com.zxhy.base.page.PageHelper"/>
</plugins>
</configuration>
五、编写DAO和mapper.xml文件
package com.zxhy.sys.dao;
import com.zxhy.base.dao.BaseDao;
import com.zxhy.dto.UserWindowDto;
import com.zxhy.sys.entity.UserEntity;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* 系统用户表
*
* @author zxhy
* @email 289346469@qq.com
* @date 2017-05-03 09:41:38
*/
@Repository
public interface UserDao extends BaseDao<UserEntity> {
/**
* 根据登陆用户查询有效的用户
*
* @param loginName
* @return
*/
UserEntity queryByLoginName(String loginName);
/**
* 用户对应的机构id,数据权限控制
*
* @param userId
* @param type 结点类型:0=根节点 ,1=机构,2=部门 具体见:Constant 类
* @return key:organId 组织id key:roleId 角色id
*/
List<Map<String, Object>> queryOrganIdByUserId(String userId, String type);
/**
* 用户对应的机构id,数据权限控制
*
* @param userId
* @param type 结点类型:0=根节点 ,1=机构,2=部门 具体见:Constant 类
*/
List<String> queryOrganIdByUserIdArray(String userId, String type);
/**
* 用户对应的机构id,数据权限控制
*
* @param userId
* @param type 结点类型:0=根节点 ,1=机构,2=部门 具体见:Constant 类
*/
List<String> queryStationIdByUserIdArray(String userId, String type);
/**
* 根据实体类查询
*
* @param map
* @return
*/
List<UserWindowDto> queryByUserEntity(Map<String, Object> map);
/**
* 根据实体类查询
*
* @param map
* @return
*/
int queryTotalByUserEntity(Map<String, Object> map);
/**
* 更新密码
*
* @param params key:passWord 密码, key:id 主键id
* @return
*/
int updatePassword(Map<String, Object> params);
/**
* 批量重置密码
*
* @param params key:passWord 密码, key:sid 主键ids
* @return
*/
int resetPassWord(Map<String, Object> params);
/**
* 批量更新用户状态
*
* @param params key:ids 用户ids
* @return
*/
int updateBatchStatus(Map<String, Object> params);
}
<?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.zxhy.sys.dao.UserDao">
<resultMap type="com.zxhy.sys.entity.UserEntity" id="userMap">
<result property="id" column="id"/>
<result property="bapid" column="bapid"/>
<result property="baid" column="baid"/>
<result property="userName" column="user_name"/>
<result property="loginName" column="login_name"/>
<result property="passWord" column="pass_word"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
<result property="status" column="status"/>
<result property="salt" column="salt"/>
<result property="phone" column="phone"/>
<result property="photo" column="photo"/>
<result property="email" column="email"/>
<result property="createId" column="create_id"/>
<result property="updateId" column="update_id"/>
<result property="remark" column="remark"/>
</resultMap>
<sql id="Base_Column_List">
id,
bapid,
baid,
user_name,
login_name,
pass_word,
create_time,
update_time,
status,
salt,
phone,
photo,
email,
create_id,
update_id,
remark
</sql>
<select id="queryObject" resultType="com.zxhy.sys.entity.UserEntity" resultMap="userMap">
select
<include refid="Base_Column_List"/>,(SELECT name FROM sys_organ o WHERE o.id=s.baid) AS baName
from sys_user s
where id = #{value}
</select>
<!--根据登陆用户查询有效的用户-->
<select id="queryByLoginName" resultType="com.zxhy.sys.entity.UserEntity" resultMap="userMap">
select
<include refid="Base_Column_List"/>,(SELECT name FROM sys_organ o WHERE o.id=u.baid) AS baName,
(SELECT name FROM sys_organ o WHERE o.id=u.bapid) AS bapName
from sys_user u
where login_name = #{0}
</select>
<!--用户对应的组织id,数据权限控制-->
<select id="queryOrganIdByUserId" resultType="java.util.Map">
select
o.id organId,
orl.role_id roleId
from sys_user_role ur LEFT JOIN
sys_organ_role orl ON ur.role_id = orl.role_id
LEFT JOIN
sys_organ o ON orl.organ_id = o.id
where ur.user_id = #{0} AND o.type = #{1}
</select>
<!--用户对应的组织id,数据权限控制 返回数组-->
<select id="queryOrganIdByUserIdArray" resultType="java.lang.String">
select o.id
from sys_user_role ur LEFT JOIN
sys_role r on ur.role_id = r.id
LEFT JOIN
sys_organ_role orl ON ur.role_id = orl.role_id
LEFT JOIN
sys_organ o ON orl.organ_id = o.id
where ur.user_id = #{0} AND o.type = #{1} AND r.status = '0'
</select>
<!--用户对应的组织id,数据权限控制 返回数组-->
<select id="queryStationIdByUserIdArray" resultType="java.lang.String">
select sys_station_organ.FK_STATION from sys_station_organ
left join sys_organ_role on sys_organ_role.organ_id=sys_station_organ.organ_id
left join sys_user_role on sys_user_role.role_id=sys_organ_role.role_id
where sys_user_role.user_id=#{0}
</select>
<select id="queryList" resultType="com.zxhy.sys.entity.UserEntity" resultMap="userMap">
select
<include refid="Base_Column_List"/>,(SELECT name FROM sys_organ o WHERE o.id=s.baid) AS baName,
(SELECT name FROM sys_organ o WHERE o.id=s.bapid) AS bapName
from sys_user s
WHERE 1=1
<if test="userName != null and userName.trim() != ''">
AND user_name=#{userName}
</if>
<choose>
<when test="sidx != null and sidx.trim() != ''">
order by ${sidx} ${order}
</when>
<otherwise>
order by id desc
</otherwise>
</choose>
<if test="offset != null and limit != null">
limit #{offset}, #{limit}
</if>
</select>
<select id="queryByUserEntity" resultType="com.zxhy.dto.UserWindowDto">
select
id,user_name AS name,bapid
from sys_user
WHERE 1=1
<if test="name != null and name.trim() != ''">
AND user_name LIKE concat('%',#{name},'%')
</if>
<choose>
<when test="sidx != null and sidx.trim() != ''">
order by ${sidx} ${order}
</when>
<otherwise>
order by id desc
</otherwise>
</choose>
<if test="offset != null and limit != null">
limit #{offset}, #{limit}
</if>
</select>
<select id="queryTotalByUserEntity" resultType="int">
select
count(*)
from sys_user
WHERE 1=1
<if test="name != null and name.trim() != ''">
AND user_name LIKE concat('%',#{name},'%')
</if>
</select>
<select id="queryTotal" resultType="int">
select count(*)
from sys_user
</select>
<insert id="save" parameterType="com.zxhy.sys.entity.UserEntity">
insert into sys_user
(
`id`,
`bapid`,
`baid`,
`user_name`,
`login_name`,
`pass_word`,
`create_time`,
`update_time`,
`status`,
`salt`,
`phone`,
`photo`,
`email`,
`create_id`,
`update_id`,
`remark`
)
values
(
#{id},
#{bapid},
#{baid},
#{userName},
#{loginName},
#{passWord},
#{createTime},
#{updateTime},
#{status},
#{salt},
#{phone},
#{photo},
#{email},
#{createId},
#{updateId},
#{remark}
)
</insert>
<update id="update" parameterType="com.zxhy.sys.entity.UserEntity">
update sys_user
<set>
<if test="bapid != null">`bapid` = #{bapid},</if>
<if test="baid != null">`baid` = #{baid},</if>
<if test="userName != null">`user_name` = #{userName},</if>
<if test="loginName != null">`login_name` = #{loginName},</if>
<if test="passWord != null">`pass_word` = #{passWord},</if>
<if test="createTime != null">`create_time` = #{createTime},</if>
<if test="updateTime != null">`update_time` = #{updateTime},</if>
<if test="status != null">`status` = #{status},</if>
<if test="salt != null">`salt` = #{salt},</if>
<if test="phone != null">`phone` = #{phone},</if>
<if test="photo != null">`photo` = #{photo},</if>
<if test="email != null">`email` = #{email},</if>
<if test="createId != null">`create_id` = #{createId},</if>
<if test="updateId != null">`update_id` = #{updateId},</if>
<if test="remark != null">`remark` = #{remark}</if>
</set>
where id = #{id}
</update>
<update id="updatePassword" parameterType="java.util.Map">
update sys_user
set pass_word = #{passWord}, salt = #{salt}
where id = #{id}
</update>
<!--批量重置密码-->
<update id="resetPassWord" parameterType="java.util.Map">
update sys_user
set pass_word=#{passWord},salt=#{salt}
where id IN
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</update>
<!--批量更新用户状态-->
<update id="updateBatchStatus" parameterType="java.util.Map">
update sys_user
set status=#{status}
where id IN
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</update>
<delete id="delete">
delete from sys_user
where id = #{value}
</delete>
<delete id="deleteBatch">
delete from sys_user where id in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
</mapper>
六、Service中调用
@Service("userService")
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public UserEntity queryObject(String id) {
UserEntity userEntity = userDao.queryObject(id);
return userEntity;
}