Mybatis通过接口实现一对一及一对多的查询
2024-10-03 03:46:52
实现一对一是采用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);
}
} }
最新文章
- RunTimeException包括：
- CentOS6.4x64_安装Qt5
- swift 学习（一）基础知识 （基本数据类型，操作符，流控制，集合）
- 有意思的字符串反转（JavaScript）
- 从源码看Android中sqlite是怎么通过cursorwindow读DB的
- BestCoder Round #89
- python_12 模块
- Redis之主从复制
- redis 连接池 hiredis
- SpringMVC集成Redis
- GoldenGate 12.3 MA架构介绍系列(2) - 数据同步测试
- GCD之后台程序运行
- art-template模板应用
- vue - vue-cli脚手架安装和webpack-simple模板项目生成
- sliva数据库简介--转载
- ASP.NET MVC下Bundle的使用
- MVC扩展Filter,通过继承ActionFilterAttribute为登录密码加密
- leetcode-686-Repeated String Match（重复多少次A能够找到B）
- jquery resize监听dom
- mac下自定义伪协议配置