Mybatis通过接口实现一对一及一对多的查询
实现一对一是采用association方法：
<resultMap type="testId" id="users"> <association property="major" column="major" select="com.dao.MajorMapper.selectbyid"></association> </resultMap>
实现一对多是采用collection方法：
<resultMap type="major" id="major"> <id property="id" column="id"/> <collection property="testids" column="id" select="com.dao.UserMapper.selectid"></collection> </resultMap>
下面进行实例：
定义三个表及数据：
可以看到testinfo表里定义一个id列对应test表里的id主键，是一对一的关系。并且这里为了测试，只给81和141的id设置testinfo信息。major表里是专业名称，是个一对多的关系。
1.定义mybatis配置文件
<?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>
<package name="com.model"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="url" value="${jdbc.url}"/>
<property name="driver" value="${jdbc.driverClass}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- <mapper resource="com/dao/UserMapper.xml"/> -->
<package name="com.dao"/>
</mappers>
</configuration>
jdbc.username=root jdbc.password=123 jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl jdbc.driverClass=oracle.jdbc.OracleDriver
2.定义实体类：
package com.model;
// Generated 2017-4-19 10:19:42 by Hibernate Tools 5.2.0.CR1
import java.math.BigDecimal;
import org.apache.ibatis.type.Alias;
/**
* TestId generated by hbm2java
*/
public class TestId {
private BigDecimal id;
private String username;
private String password;
private Major major;
public Major getMajor() {
return major;
}
public void setMajor(Major major) {
this.major = major;
}
public TestId() {
}
public BigDecimal getId() {
return this.id;
}
public void setId(BigDecimal id) {
this.id = id;
}
public String getUsername() {
return this.username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
public boolean equals(Object other) {
if ((this == other))
return true;
if ((other == null))
return false;
if (!(other instanceof TestId))
return false;
TestId castOther = (TestId) other;
return ((this.getId() == castOther.getId())
|| (this.getId() != null && castOther.getId() != null && this.getId().equals(castOther.getId())))
&& ((this.getUsername() == castOther.getUsername()) || (this.getUsername() != null
&& castOther.getUsername() != null && this.getUsername().equals(castOther.getUsername())))
&& ((this.getPassword() == castOther.getPassword()) || (this.getPassword() != null
&& castOther.getPassword() != null && this.getPassword().equals(castOther.getPassword())));
}
public int hashCode() {
int result = 17;
result = 37 * result + (getId() == null ? 0 : this.getId().hashCode());
result = 37 * result + (getUsername() == null ? 0 : this.getUsername().hashCode());
result = 37 * result + (getPassword() == null ? 0 : this.getPassword().hashCode());
return result;
}
@Override
public String toString() {
return "TestId [id=" + id + ", username=" + username + ", password=" + password + ", major=" + major + "]";
}
public TestId(BigDecimal id, String username, String password, Major major) {
super();
this.id = id;
this.username = username;
this.password = password;
this.major = major;
}
}
package com.model;
import java.util.Date;
public class TestInfo {
private Integer ids;
private TestId testId;
private String address;
private Date birthday;
public Integer getIds() {
return ids;
}
public void setIds(Integer ids) {
this.ids = ids;
}
public TestId getTestId() {
return testId;
}
public void setTestId(TestId testId) {
this.testId = testId;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public TestInfo(Integer ids, TestId testId, String address, Date birthday) {
super();
this.ids = ids;
this.testId = testId;
this.address = address;
this.birthday = birthday;
}
public TestInfo() {
super();
}
@Override
public String toString() {
return "TestInfo [ids=" + ids + ", testId=" + testId + ", address=" + address + ", birthday=" + birthday + "]";
}
}
package com.model;
import java.util.List;
public class Major {
private Integer id;
private String code;
private String name;
private List<TestId> testids;
public List<TestId> getTestids() {
return testids;
}
public void setTestids(List<TestId> testids) {
this.testids = testids;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Major(Integer id, String code, String name, List<TestId> testids) {
super();
this.id = id;
this.code = code;
this.name = name;
this.testids = testids;
}
public Major() {
super();
}
@Override
public String toString() {
return "Major [id=" + id + ", code=" + code + ", name=" + name + ", testids=" + testids.size() + "]";
}
}
3.定义一个mybatis的工具类，获取mybatis核心sqlsessionfactory
package com.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;
/**
* mybatis工具类
* @author Administrator
*
*/
public class MybatisUtil {
private static SqlSessionFactory ssf;
private static SqlSession ss;
/**
* 获取mybatis核心sqlsessionfactory
* @return
*/
private static SqlSessionFactory getSqlSessionFctory(){
InputStream it = null;
try {
it = Resources.getResourceAsStream("mybatis-config.xml");
ssf= new SqlSessionFactoryBuilder().build(it);
ss=ssf.openSession();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ssf;
}
/**
* 获取sqlsession
* @return
*/
public static SqlSession getSqlSession(){
ss= getSqlSessionFctory().openSession();
return ss;
}
public static void main(String[] args){
System.out.println(getSqlSession());
}
}
4.定义接口
package com.dao;
import java.util.List;
import java.util.Map;
import com.model.Major;
import com.model.TestId;
public interface UserMapper {
/*public Integer add(TestId ti);
public Integer delete(Integer id);
public Integer update(TestId ti);
*/
public TestId select(Integer id);
public List<TestId> selectlist(Map<String, Object> map);
public List<TestId> selectid(Integer major);
}
package com.dao;
import java.util.List;
import com.model.TestInfo;
public interface UserInfoMapper {
public List<TestInfo> select();
}
package com.dao;
import java.util.List;
import com.model.Major;
/**
* 专业表操作
* @author Administrator
*
*/
public interface MajorMapper {
public List<Major> selectAll();
public Major selectbyid(Integer id);
}
5.定义每个实体类的映射方法
<?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.dao.UserMapper">
<resultMap type="testId" id="users">
<association property="major" column="major" select="com.dao.MajorMapper.selectbyid"></association>
</resultMap>
<!-- <insert id="add" parameterType="testId">
insert into test values(sq_mybatis.nextval,#{username},#{password})
</insert>
<delete id="delete" parameterType="Integer">
delete test t where t.id=#{id}
</delete>
<update id="update" parameterType="testId">
update test t set t.username=#{username},t.password=#{password} where t.id=#{id}
</update> -->
<select id="select" parameterType="Integer" resultMap="users">
select * from test t where t.id=#{id}
</select>
<select id="selectlist" parameterType="Map" resultMap="users">
select * from test t where t.username like #{username} and t.password like #{password}
</select>
<select id="selectid" parameterType="Integer" resultMap="users">
select * from test t where t.major=#{major}
</select>
</mapper>
<?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.dao.UserInfoMapper"> <!-- 一对一级连查询方法1 --> <resultMap type="testInfo" id="userslist"> <id property="ids" column="ids"/> <result property="testId.id" column="id"/> <result property="testId.username" column="username"/> <result property="testId.password" column="password"/> <result property="address" column="address"/> <result property="birthday" column="birthday"/> </resultMap> <!-- 一对一级连查询方法2 --> <resultMap type="testInfo" id="userlist"> <association property="testId" column="id" select="com.dao.UserMapper.select"></association> </resultMap> <select id="select" resultMap="userslist"> select * from testinfo ti left join test t on ti.id=t.id </select> </mapper>
<?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.dao.MajorMapper">
<!-- 一对多的查询方法 -->
<resultMap type="major" id="major">
<id property="id" column="id"/>
<collection property="testids" column="id" select="com.dao.UserMapper.selectid"></collection>
</resultMap>
<select id="selectAll" resultMap="major">
select * from major
</select>
<select id="selectbyid" parameterType="Integer" resultMap="major" >
select * from major m where m.id=#{id}
</select>
</mapper>
6.定义junit测试文件来进行测试
package com.util;
import static org.junit.Assert.*;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.dao.UserMapper;
import com.model.TestId;
public class Junit {
private SqlSession ss;
private UserMapper um;
@Before
public void setUp() throws Exception {
ss=MybatisUtil.getSqlSession();
um=ss.getMapper(UserMapper.class);
}
@After
public void tearDown() throws Exception {
ss.commit();
ss.close();
}
/*public void test() {
TestId ti = new TestId();
ti.setUsername("张张柳");
ti.setPassword("443221");
//int i =ss.insert("com.dao.UserMapper.add",ti);
int i=um.add(ti);
System.out.println(i);
}
public void test1(){
int i = um.delete(401);
System.out.println(i);
}
public void test2(){
TestId ti = new TestId();
ti.setId(new BigDecimal(441));
ti.setUsername("张张柳2");
ti.setPassword("443221");
um.update(ti);
}*/
/**
* 查询id为441的所有信息
*/
@Test
public void test3(){
TestId ti =um.select(441);
System.out.println(ti);
}
public void tes4(){
Map<String, Object> map = new HashMap<String, Object>();
map.put("username", "张%");
map.put("password", "%2%");
List<TestId> list =um.selectlist(map);
for(TestId ti:list){
System.out.println(ti);
}
}
/**
* 查询major为2的所有数据
*/
public void test5(){
List<TestId> list = um.selectid(2);
for(TestId ti:list){
System.out.println(ti);
}
}
}
package com.util;
import static org.junit.Assert.*;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.dao.UserInfoMapper;
import com.dao.UserMapper;
import com.model.TestId;
import com.model.TestInfo;
public class Junit2 {
private SqlSession ss;
private UserInfoMapper um;
@Before
public void setUp() throws Exception {
ss=MybatisUtil.getSqlSession();
um=ss.getMapper(UserInfoMapper.class);
}
@After
public void tearDown() throws Exception {
ss.commit();
ss.close();
}
@Test
public void test() {
List<TestInfo> list = um.select();
for(TestInfo ti :list){
System.out.println(ti);
}
}
}
package com.util;
import static org.junit.Assert.*;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.dao.MajorMapper;
import com.model.Major;
import com.model.TestInfo;
public class Junit3 {
private SqlSession ss;
private MajorMapper mm;
@Before
public void setUp() throws Exception {
ss=MybatisUtil.getSqlSession();
mm=ss.getMapper(MajorMapper.class);
}
@After
public void tearDown() throws Exception {
ss.commit();
ss.close();
}
@Test
public void test2(){
Major m = mm.selectbyid(2);
System.out.println(m);
}
public void test() {
List<Major> list = mm.selectAll();
for(Major ti :list){
System.out.println(ti);
}
}
}
