输出映射的两种形式:resultType | resultMap
它俩分别对应在什么时候使用?
- resultType可以指定pojo将查询结果映射为pojo,但需要pojo的属性名和sql查询的列名一致方可映射成功。
- sql查询字段名和pojo的属性名不一致时,可通过resultMap将字段名和属性名作一个对应关系 ,resultMap实质上还需要将查询结果映射到pojo对象中。
- resultMap可以实现将查询结果映射为复杂类型的pojo,比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。
一、resultType
(1)、输出简单类型
看下边的例子输出整型
1、数据库数据与已经创建好了的JavaBean
package Domain;
import java.io.Serializable;
public class UserDomain implements Serializable {
private int id;
private String name;
private String password;
private String sex;
private int age;
//无参构造函数
public UserDomain() {
}
//get|set
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
//toString
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password + ", sex=" + sex + ", age=" + age + "]";
}
}
2、我这里先在接口里添加一个方法
package Mapper;
import java.util.List;
import Domain.QueryUser;
import Domain.UserDomain;
public interface UserMapper {
//输出映射演示
int findUserByCount(UserDomain user);
}
3、然后在映射文件里添加查询语句
<?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">
<!-- namespace和mapper接口同名 -->
<mapper namespace="Mapper.UserMapper">
<!-- P.11 (查询性别为女的总记录条数)-->
<select id="findUserByCount" parameterType="UserDomain" resultType="int">
select count(*) from test001 where sex=#{sex};
</select>
</mapper>
4、测试
package test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import Domain.QueryUser;
import Domain.UserDomain;
import Mapper.UserMapper;
public class Test01 {
private SqlSessionFactory ssf;
//获得会话工厂
@Before
public void creatSqlSessionFactory() {
InputStream in = null;
try {
in = Resources.getResourceAsStream("SqlMapConfig.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ssf = new SqlSessionFactoryBuilder().build(in);
}
/* 输出映射 */
@Test
public void findUserByCount() {
//打开一个会话
SqlSession s = ssf.openSession();
//获得代理对象
UserMapper mapper = s.getMapper(UserMapper.class);
//new对象
UserDomain user = new UserDomain();
user.setSex("女");
//调用代理对象
int number = mapper.findUserByCount(user);
System.out.println("性别为女的总记录数:"+number);
//关闭
s.close();
}
}
- 输出简单类型必须查询出来的结果集有一条记录,最终将第一个字段的值转换为输出类型。
- 使用session的selectOne可查询单条记录。
(2)、输出pojo对象
参考前面已经写过的笔记
(3)、输出pojo列表
参考前面已经写过的笔记
二、resultMap
1、注意查看数据库字段名与JavaBean定义好的属性名
package Domain;
import java.io.Serializable;
public class UserDomain2 implements Serializable {
private int user_id;
private String user_name;
private String user_password;
private String user_sex;
private int user_age;
//无参构造函数
public UserDomain2() {
}
//get|set
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getUser_password() {
return user_password;
}
public void setUser_password(String user_password) {
this.user_password = user_password;
}
public String getUser_sex() {
return user_sex;
}
public void setUser_sex(String user_sex) {
this.user_sex = user_sex;
}
public int getUser_age() {
return user_age;
}
public void setUser_age(int user_age) {
this.user_age = user_age;
}
//toString
@Override
public String toString() {
return "UserDomain2 [user_id=" + user_id + ", user_name=" + user_name + ", user_password=" + user_password
+ ", user_sex=" + user_sex + ", user_age=" + user_age + "]";
}
}
由于user2.xml中sql查询列和UserDomain2.java类属性不一致,这时我们需要定义resultMap——>user将sql查询列和UserDomain2.java类属性对应起来
1、创建接口
package Mapper;
import java.util.List;
import Domain.QueryUser;
import Domain.UserDomain;
import Domain.UserDomain2;
/**
* 代理
* @author LUO
*
*/
public interface UserMapper2 {
UserDomain2 findUser2ById(int user_id);
}
2、需要在映射文件中写入
<?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">
<!-- namespace和mapper接口同名 -->
<mapper namespace="Mapper.UserMapper2">
<resultMap type="UserDomain2" id="user">
<id column="id" property="user_id" />
<result column="name" property="user_name" />
<result column="password" property="user_password" />
<result column="sex" property="user_sex" />
<result column="age" property="user_age" />
</resultMap>
<select id="findUser2ById" parameterType="int" resultMap="user">
select * from test001 where id=#{value}
</select>
</mapper>
- id:此属性表示查询结果集的唯一标识,非常重要。如果是多个字段为复合唯一约束则定义多个id
- result:普通结果,即pojo的属性。
- Column:表示sql查询出来的字段名。
- Property:表示UserDomain2类的属性。
- Column和property放在一块儿表示将sql查询出来的字段映射到指定的pojo类属性上。
3、需要将映射文件(User2.xml)添加进SqlMapConfig.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="db.properties"></properties>
<!-- 相对于定义别名 -->
<typeAliases>
<!-- 单个别名定义 (使用时,不区分别名的首字母大小写)-->
<!-- <typeAlias alias="user" type="Domain.UserDomain"/> -->
<!-- 批量别名定义,扫描整个包下的类,别名为类名(首字母大写或小写都可以) -->
<package name="Domain"/>
</typeAliases>
<!-- 和spring整合后 environments配置将废除-->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理-->
<transactionManager type="JDBC" />
<!-- 数据库连接池-->
<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>
<mapper resource="User.xml"/>
<mapper resource="User2.xml"/>
</mappers>
</configuration>
4、测试
package test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import Domain.QueryUser;
import Domain.UserDomain;
import Domain.UserDomain2;
import Mapper.UserMapper;
import Mapper.UserMapper2;
public class Test2 {
private SqlSessionFactory ssf;
//获得会话工厂
@Before
public void creatSqlSessionFactory() {
InputStream in = null;
try {
in = Resources.getResourceAsStream("SqlMapConfig.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ssf = new SqlSessionFactoryBuilder().build(in);
}
@Test
public void findUser2ById() {
//打开一个会话
SqlSession s = ssf.openSession();
//获得代理对象
UserMapper2 mapper = s.getMapper(UserMapper2.class);
//调用代理对象
UserDomain2 user = mapper.findUser2ById(11);
System.out.println("名字:"+user.getUser_name());
//关闭资源
s.close();
}
}