映射器是mybatis最强大的工具,也是mybatis使用最多的工具。其主要作用就是配置这些映射器,来动态生成sql操作数据库,处理结果返回值。
有哪些元素
下面就来介绍一下这些元素的使用。
准备代码:
package com.cjy.mybatis.entity;
import java.io.Serializable;
import org.apache.ibatis.type.Alias;
@Alias("person")
public class Person implements Serializable{
private Integer id;
private String userName;
private String marker;
public Person() {
super();
// TODO Auto-generated constructor stub
}
public Person(Integer id, String userName, String marker) {
super();
this.id = id;
this.userName = userName;
this.marker = marker;
}
//省略get set 占位置
}
//加载配置文件生成SqlSessionFactory
package com.cjy.mybatis.util;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MySqlSessionFacoty {
private static String resource = "mybatis-config.xml";
private static SqlSessionFactory sqlSessionFactory = null;
private MySqlSessionFacoty(){}
public static SqlSessionFactory getSqlSessionFactory() {
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
if(sqlSessionFactory == null){
synchronized (MySqlSessionFacoty.class) {
if(sqlSessionFactory == null){
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
}
}
return sqlSessionFactory;
}
public static SqlSession getSqlSession(){
if(sqlSessionFactory == null)
getSqlSessionFactory();
return sqlSessionFactory.openSession();
}
}
- select元素:最常用也是功能最强大,使用频率最高的。执行select前,需要定义参数,它可以是一个简单参数类型如:int、string,也可以是一个复杂参数类型:javaBean、map,执行sql后会将结果集封装到javaBean中。
1.1 测试简单参数效果
//简单参数查询接口方法 --PersonMapper
public Person queryPersonById(Integer id);
1.2 sql映射器代码
--PersonMapper.xml
<!--2. sql 抽取公共部分 -->
<sql id="baseColumns">
id,user_name,marker
</sql>
<select id="queryPersonById" resultType="person" parameterType="int" >
select <include refid="baseColumns"/>
from person where id = #{id}
</select>
1.3测试,这里就是一个简单的根据id查询结果返回javaBean的查询操作
public class PersonMainTest {
PersonMapper mapper = null;
SqlSession openSession = null;
@Before
public void before(){
SqlSessionFactory sqlSessionFactory = MySqlSessionFacoty.getSqlSessionFactory();
openSession = sqlSessionFactory.openSession();
mapper = openSession.getMapper(PersonMapper.class);
}
@Test
public void queryPersonById(){
if(mapper == null)
return ;
Person person = mapper.queryPersonById(1);
System.out.println(person);
/**
2019-07-29 16:09:34 DEBUG mybatis.sql.com.cjy.mybatis.dao.PersonMapper.queryPersonById - <== Total: 1
Person [id=1, userName=张三, marker=测试数据]
*/
}
}
1.4上面就是一个简单的查询测试,下面测试其他几种查询效果
分别是:多参数、map参数、javaBean参数,结合sqlxml 来看,
//mapper接口函数
public Person queryPersonByIdAndLikeNamesToVo(Person person);
public Person queryPersonByIdAndLikeNames(@Param("id") Integer id ,@Param("userName") String userName);
public Person queryPersonByIdAndLikeNamesToMap(Map<String,Object> map);
sql xml编写,使用resultMap来接收查询结果类型,sql来抽取公共部分
<!--1. resultMap:返回值类型定义,主要使用标签id与result,其实还有很多标签可以使用
如:constructor、association...-->
<resultMap type="person" id="baseResult">
<id column="id" property="id"/>
<result column="user_name" property="userName"/>
<result column="marker" property="marker"/>
</resultMap>
<!--2. sql 抽取公共部分 -->
<sql id="baseColumns">
id,user_name,marker
</sql>
<!--3. select 主要属性:不常使用的就不介绍了。
1. id 与接口方法名一直
2. resultType/resultMap查询结果返回值类型,map的方式比type灵活一些,但是需要自己手动匹配一些规则
3. parameterType 参数类型
4. parameterMap 参数类型,不常用
5. 还有一些关联集合查询,这里不做介绍
-->
<!--3.1 单个参数查询 -->
<select id="queryPersonById" resultType="person" parameterType="int" >
select <include refid="baseColumns"/>
from person where id = #{id}
</select>
<!--3.2 多个参数 -->
<select id="queryPersonByIdAndLikeNames" resultMap="baseResult" >
select <include refid="baseColumns"/>
from person where id = #{id} and user_name like '%' || #{userName}
</select>
<!--3.3 多个参数,map集合方式 -->
<select id="queryPersonByIdAndLikeNamesToMap" resultType="person" parameterType="map">
select <include refid="baseColumns"/>
from person where id = #{id} and user_name like '%' || #{userName}
</select>
<!--3.4 多个参数,vo对象参数-常用 -->
<select id="queryPersonByIdAndLikeNamesToVo" resultType="person" parameterType="person">
select <include refid="baseColumns"/>
from person where id = #{id} and user_name like '%' || #{userName}
</select>
1.5 下面就来简单说下测试效果
@Test
public void queryPersonByIdAndLikeNames(){
if(mapper == null)
return ;
Person person = mapper.queryPersonByIdAndLikeNames(1, "张");
System.out.println(person);
/**
2019-07-29 16:36:09 DEBUG mybatis.sql.com.cjy.mybatis.dao.PersonMapper.queryPersonByIdAndLikeNames - <== Total: 1
Person [id=1, userName=张三, marker=测试数据]
*/
}
@Test
public void queryPersonByIdAndLikeNamesToMap(){
if(mapper == null)
return ;
Map<String, Object> map = new HashMap<String,Object>();
map.put("id", 1);
map.put("userName", "张");
Person person = mapper.queryPersonByIdAndLikeNamesToMap(map);
System.out.println(person);
/**
2019-07-29 16:35:14 DEBUG mybatis.sql.com.cjy.mybatis.dao.PersonMapper.queryPersonByIdAndLikeNamesToMap - <== Total: 1
Person [id=1, userName=张三, marker=测试数据]
*/
}
@Test
public void queryPersonByIdAndLikeNamesToVo(){
if(mapper == null)
return ;
Person person = mapper.queryPersonByIdAndLikeNamesToVo(new Person(2,"张",null));
System.out.println(person);
/**
2019-07-29 16:39:01 DEBUG mybatis.sql.com.cjy.mybatis.dao.PersonMapper.queryPersonByIdAndLikeNamesToVo - <== Total: 1
Person [id=1, userName=张三, marker=测试数据]
*/
}
建议:一般按照主键id来查询的直接使用单个参数,如果是多个参数查询的建议封装javaBean查询数据。
-
insert:这个元素相对来说就简单多了,只是用于将数据保存到数据库。遇到的情况可能会有使用新增数据的主键,或自定义主键生成策略
2.1简单的数据保存操作,返回值为受影响的数据行数
//下面代码分别是:mapper接口、xml、测试
public int savePersonInfo(Person person); //接口
<select id="queryPersonById" resultType="person" parameterType="int" > select <include refid="baseColumns"/> from person where id = #{id} </select>
@Test public void savePersonInfo(){ try { if(mapper == null && openSession == null) return ; Person person = new Person(null,"张","插入数据测试"); int count = mapper.savePersonInfo(person); System.out.println(person.getId()); //-- id=null,此时无法获取新增数据的主键 openSession.commit(); } catch (Exception e) { e.printStackTrace(); }finally{ openSession.close(); }
2.2 新增数据后获取其主键
public int savePersonInfoLoadKey(Person person);
<!--4.2 新增数据,并将主键返回 keyProperty:哪个是主键字段 useGeneratedKeys:这个主键是否使用数据库生成策略 --> <insert id="savePersonInfoLoadKey" parameterType="person" useGeneratedKeys="true" keyProperty="id"> insert into person(user_name,marker) values(#{userName},#{marker}) </insert>
@Test public void savePersonInfoLoadKey(){ try { if(mapper == null && openSession == null) return ; Person person = new Person(null,"李四","插入数据测试,获取主键"); int count = mapper.savePersonInfoLoadKey(person); System.out.println(person.getId());//此时在保存数据后会将主键回填到bean中 openSession.commit(); } catch (Exception e) { e.printStackTrace(); }finally{ openSession.close(); } }
2.3 不想使用数据库主键生成策略,如:每次新生成的主键是当前数据库最大主键值加2,当前最大主键为空则默认为1
public int savePersonInfoCreateAndLoadKey(Person person);
<!--4.3 新增数据,自定义主键生成策略,由selectKey来完成 --> <insert id="savePersonInfoCreateAndLoadKey" parameterType="person" useGeneratedKeys="true" keyProperty="id"> <selectKey keyProperty="id" order="BEFORE" resultType="int"> select if(max(id) is null , 1 ,max(id)+2) as newId from person </selectKey> insert into person(id,user_name,marker) values(#{id},#{userName},#{marker}) </insert>
@Test
public void savePersonInfoCreateAndLoadKey(){
try {
if(mapper == null && openSession == null)
return ;
Person person = new Person(null,"李四","插入数据测试,获取主键");
int count = mapper.savePersonInfoCreateAndLoadKey(person);
System.out.println(person.getId());
openSession.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
openSession.close();
}
}
- update:这个就比较简单按照满足条件的数据进行更新操作。
public int updatePersonInfoById(Person person);
<update id="updatePersonInfoById" parameterType="person">
update person set user_name=#{userName} , marker = #{marker} where id=#{id}
</update>
@Test
public void updatePersonInfoById(){
try {
if(mapper == null && openSession == null)
return ;
Person person = new Person(1,"zhangsan__xg","插入数据测试,获取主键_xg");
int count = mapper.updatePersonInfoById(person);
openSession.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
openSession.close();
}
}
- delete:跟update类似,不过这个是满足条件移除数据
public int removePersonInfoById(Integer id);
<delete id="removePersonInfoById" parameterType="int">
delete from person where id=#{id}
</delete>
@Test
public void removePersonInfoById(){
try {
if(mapper == null && openSession == null)
return ;
int count = mapper.removePersonInfoById(1);
System.out.println(count);
openSession.commit();
} catch (Exception e) {
e.printStackTrace();
}finally{
openSession.close();
}
}
-
参数介绍
虽然看着上面参数的使用比较简单,实际 #{id} 这并不是参数的完整显示,每个参数都有对应的javaType、jdbcType、TypeHandler,这些才是一个完整的参数
-
cache:二级缓存标签,这个会单独介绍
-
#与$ 的区别?
#采用预编译:grade = ? 参数值:六年级(String)
$ 直接拼接上字符串 :’%xiaoming%’
2019-07-31 15:53:46 DEBUG mybatis.sql.com.cjy.mybatis.dao.StudentMapper.queryStudentWhere - ==> Preparing: select * from student WHERE email like '%xiaoming%' and grade = ?
2019-07-31 15:53:47 DEBUG mybatis.sql.com.cjy.mybatis.dao.StudentMapper.queryStudentWhere - ==> Parameters: 六年级(String)
总结:常用的一些sql映射器元素都在这里,上面由于不是完整的代码所以看起来费劲,请下载完整demo查看例子效果最佳
demo:https://github.com/chenjy512/mybatis-demo/tree/master/source-mybatis-04mapper