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);
		}
	}
	

}