MyBatis框架
1.什么是ORM
ORM即对象关系映射(Object-Relationl Mapping),它的作用是在关系型数据库和对象之间作一个映射,这样,我们在具体的操作数据库的时候,就不需要再去和复杂的SQL语句打交道,只要像平时操作对象一样操作它就可以了 。
2.mybatis所需jar
<mybatis.version>3.2.8</mybatis.version>
<slf4j.version>1.7.12</slf4j.version>
<log4j.version>1.2.17</log4j.version>
<druid.version>1.0.9</druid.version>
<!-- mybatis框架包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- log -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
3.创建主配置文件mybatis.xml相关配置
<configuration>
<!--引入properties文件-->
<properties resource="jdbc.properties"></properties>
<settings>
<!-- value="STDOUT_LOGGING" 配置打印SQL语句 -->
<setting name="logImpl" value="LOG4J"/>
</settings>
<!--简化包名-->
<typeAliases>
<package name="com.hz.pojo"/>
</typeAliases>
<!--创建数据源 默认加载cs-->
<environments default="cs">
<environment id="cs">
<!--caiyong JDBC方式加载事务-->
<transactionManager type="JDBC"></transactionManager>
type属性、其有三种取值:
POOLED:使用Mybatis自带的数据库连接池来管理数据库连接
UNPOOLED:不使用任何数据库连接池来管理数据库连接
JNDI:jndi形式使用数据库连接、主要用于项目正常使用的时候
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--扫描mapper文件-->
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
4. 编写测试类
String resource = "mybatis-config.xml";
try {
InputStream is = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
/**
* true 自动提交事务 (默认)
* false 关闭自动提交
*/
SqlSession sqlSession = factory.openSession(true);
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.getUserId(1);
//关闭sqlsession
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
SQL映射文件
<mapper namespace="com.hz.dao.UserDao">
<select id="getUserId" parameterType="Long" resultType="User" >
SELECT * FROM sf_user where user_id=#{user_id}
</select>
</mapper>
标签解释:
mapper:根元素 ,只有一个属性namespace,用来区分不同的mapper文件,必须与接口绑定。
select:映射查询语句
delete:映射删除语句
update: 映射修改语句
insert: 映射插入语句
sql: 可以重用sql块,也可以被其他语句引用
resultMap : 用来描述数据库结果集和对象的对应关系
cache :配置给定命名空间的缓存
SQL映射文件参数传递
- 单参数:
接口中定义
public List<User> getUserListByName(String user_name);
mapper中使用
<select id="getUserListByName" parameterType="String" resultType="User" >
SELECT * FROM sf_user where user_name like concat('%',#{user_name},'%')
</select>
id :与接口中方法名相同
parameterType:指定要接收的参数类型
resultType:返回值类型,对象或集合都可以写对象
- 多个参数:
使用注解@Param
public List<User> getUserList(@Param("user_name") String user_name, @Param("user_state") int user_state);
使用对象
public int updateUser(User user);
<update id="updateUser" parameterType="User">
update sf_user set user_name=#{user_name} where user_id=#{user_id}
</update>
注意:#{user_name} 为User对象下属性名,不能随便写
update,insert,delete 都没有resultType 默认返回受影响行数int类型
使用map集合
public List<User> getUserListMap(Map<String,Object> map);
<select id="getUserListMap" parameterType="Map" resultType="User">
SELECT * FROM sf_user where user_name like concat('%',#{user_name},'%') and user_state=#{user_state}
</select>
注意:#{user_name} 为map的KEY
SQL映射文件指定返回列
select 标签中的属性
resultType:标识返回值类型,基础类型与复杂数据类型
resultMap:用来引入外部的一个resultMap,与外部resultMap标签中id对应
在mybatis主配置文件中关闭自动映射
<setting name="autoMappingBehavior" value="NONE"/>
<select id="getUserListMap" parameterType="Map" resultMap="resultMap">
SELECT * FROM sf_user where user_name like concat('%',#{user_name},'%') and user_state=#{user_state}
</select>
<resultMap id="resUser" type="User">
<id property="user_id" column="user_id"></id>
<result property="user_name" column="user_name"></result>
</resultMap>
SQL映射文件获取一对一关系值(对象套对象association)
//角色实体类
public class Role {
private long role_id;
private String role_name;
private String role_remarks;
//管理员实体类
public class Admin {
private long admin_id;
private String admin_name;
private Role admin_role;//角色对象
<resultMap id="mapAdmin" type="Admin">
<id property="admin_id" column="admin_id"></id>
<result property="admin_name" column="admin_name"></result>
<association property="admin_role" javaType="Role">
<id property="role_id" column="role_id"></id>
<result property="role_name" column="role_name"></result>
<result property="role_remarks" column="role_remarks"></result>
</association>
</resultMap>
<select id="AdminMapper" parameterType="Long" resultMap="mapAdmin">
SELECT * FROM sf_admin a left join sf_role b on a.admin_role_id=b.role_id where admin_id=#{admin_id}
</select>
或
<resultMap id="mapAdmin" type="Admin">
<id property="admin_id" column="admin_id"></id>
<result property="admin_name" column="admin_name"></result>
<association property="admin_role" resultMap="rolemap" javaType="Role"/>
</resultMap>
<resultMap id="rolemap" type="Role">
<id property="role_id" column="role_id"></id>
<result property="role_name" column="role_name"></result>
<result property="role_remarks" column="role_remarks"></result>
</resultMap>
使用association实现一对一
property:属性名
javaType:所属数据类型
SQL映射文件获取一对多关系值(对象套集合)
//收货地址实体类
public class Address {
private long address_id;
private String address_name;
//用户实体类
public class User {
private long user_id;
private String user_name;
private List<Address> addr_list;
<resultMap id="user_addr_map" type="User">
<id property="user_id" column="user_id"></id>
<result property="user_name" column="user_name"></result>
<collection property="addr_list" ofType="Address">
<id property="address_id" column="address_id"></id>
<result property="address_name" column="address_name"></result>
</collection>
</resultMap>
<select id="getUserId" parameterType="Long" resultMap="user_addr_map" >
SELECT * FROM sf_user a right join sf_address b on a.user_id=b.address_user_id where user_id=#{user_id}
</select>
动态SQL
- if
<select id="getUserListMap" parameterType="Map" resultMap="resultMap">
SELECT * FROM sf_user where user_name like concat('%',#{user_name},'%') and user_state=#{user_state}
</select>
如:
SELECT * FROM sf_user
<where>
<if test="user_name!=null">
user_name like concat('%',#{user_name},'%')
</if>
<if test="user_state!=null">
and user_state=#{user_state}
</if>
</where>
注意:user_state为Integer类型
2.if + trim
SELECT * FROM sf_user
<trim prefix="where" prefixOverrides="and ">
<if test="user_name!=null">
and user_name like concat('%',#{user_name},'%')
</if>
<if test="user_state!=null">
and user_state=#{user_state}
</if>
</trim>
prefix 给sql语句拼接的前缀
suffix 给sql语句拼接的后缀
prefixOverrides 去除sql语句前面的关键字或者字符,该关键字或者字符由prefixOverrides属性指定,假设该属性指定为"AND",当sql语句的开头为"AND",trim标签将会去除该"AND"
suffixOverrides 去除sql语句后面的关键字或者字符,该关键字或者字符由suffixOverrides属性指定
3.if + set
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
4.foreach
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="val" collection="list"
open="(" separator="," close=")">
#{val}
</foreach>
</select>
item: 循环后获取的每个对象值
collection:指定循环集合类型 list,array,map-key
open:前缀
close:后缀
separator:中间以什么隔开
5.choose(when otherwise)
<choose>
<when test ="条件1"> …</when>
<when test ="条件2"> …</when>
<when test ="条件3"> …</when>
…
<otherwise>…</otherwise>
</choose>
6.sql
//建立sql片段
<sql id="query_user_where">
......
</sql>
SELECT * FROM sf_user
<trim prefix="where" prefixOverrides="and ">
<include refid="query_user_where"/>
</trim>