mybatis关联查询
场景：一个人有多个手机号，一个手机号对应一个人
CREATE TABLE test.mobile ( mid INT NOT NULL auto_increment, tel VARCHAR (50), pid INT, PRIMARY KEY (mid) ); CREATE TABLE test.person ( pid INT NOT NULL auto_increment, name VARCHAR (50), PRIMARY KEY (pid) ); INSERT INTO test.person (name) VALUES ('wjf'); INSERT INTO test.mobile (tel, pid) VALUES ('13564089643', 1); INSERT INTO test.mobile (tel, pid) VALUES ('13511111111', 1);
先上个配置文件
<?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="wjf.dao.MobileMapper"> <resultMap type="wjf.Obj.Mobile" id="resultMap"> <id property="mid" column="mid" /> <result property="tel" column="tel" /> <association property="person" javaType="wjf.Obj.Person" column="pid"> <id property="pid" column="pid" /> <result property="name" column="name" /> </association> </resultMap> <select id="getMobile" parameterType="int" resultMap="resultMap"> select p.pid,p.name ,m.mid ,m.tel from person p ,mobile m where m.pid=p.pid AND m.mid=#{id} </select> <select id="getALLMobile" resultMap="resultMap"> select p.pid,p.name ,m.mid ,m.tel from person p ,mobile m where m.pid=p.pid </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="wjf.dao.PersonMapper"> <resultMap type="wjf.Obj.Person" id="resultMap"> <id property="pid" column="pid" /> <result property="name" column="name" /> <collection property="mobiles" column="pid" ofType="wjf.Obj.Mobile"> <id property="id" column="mid" /> <result property="tel" column="tel" /> </collection> </resultMap> <select id="getPerson" parameterType="int" resultMap="resultMap"> select p.pid,p.name ,m.mid ,m.tel from person p ,mobile m where m.pid=p.pid AND p.pid=#{id} </select> <select id="getALLPerson" resultMap="resultMap"> select p.pid,p.name ,m.mid ,m.tel from person p ,mobile m where m.pid=p.pid </select> </mapper>
再上个对象
package wjf.Obj; import java.util.List; public class Person { private int pid; private String name; private List<Mobile> mobiles; public List<Mobile> getMobiles() { return mobiles; } public void setMobiles(List<Mobile> mobiles) { this.mobiles = mobiles; } public int getId() { return pid; } public void setId(int id) { this.pid = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Person [id=" + pid + ", name=" + name + "]"; } }
package wjf.Obj;
public class Mobile {
private int mid;
private String tel;
private Person person;
/**
* @return the mid
*/
public int getMid() {
return mid;
}
/**
* @param mid the mid to set
*/
public void setMid(int mid) {
this.mid = mid;
}
/**
* @return the person
*/
public Person getPerson() {
return person;
}
/**
* @param person the person to set
*/
public void setPerson(Person person) {
this.person = person;
}
public int getId() {
return mid;
}
public void setId(int id) {
this.mid = id;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
@Override
public String toString() {
return "Mobile [id=" + mid + ", tel=" + tel + "]";
}
}
上个映射类
package wjf.dao; import java.util.List; import wjf.Obj.Mobile; public interface MobileMapper { public Mobile getMobile(int id); public List<Mobile> getALLMobile(); }
package wjf.dao; import java.util.List; import wjf.Obj.Person; public interface PersonMapper { public Person getPerson(int id); public List<Person> getALLPerson(); }
最后就是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> <typeAliases> <typeAlias alias="UserInfo" type="wjf.orm.UserInfo"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <!-- 配置数据库连接信息 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/test" /> <property name="username" value="root" /> <property name="password" value="123" /> </dataSource> </environment> </environments> <mappers> <!-- <mapper resource="wjf/orm/userMapper.xml"/> --> <!-- <mapper resource="wjf/orm/userMapper2.xml"/> --> <mapper resource="wjf/config/MobileMapper.xml"/> <mapper resource="wjf/config/PersonMapper.xml"/> </mappers> </configuration>
测试程序如下
@Test public void testRelation() { SqlSessionFactory sqlSessionFactory = MybatisUtil .getSqlsessionfactory(); SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); // PersonMapper pmapper = sqlSession.getMapper(PersonMapper.class); // List<Person> plist = pmapper.getALLPerson(); // System.out.println(plist.size()); // Person p = pmapper.getPerson(1); // System.out.println(p.getMobiles().size()); MobileMapper mmapper = sqlSession.getMapper(MobileMapper.class); List<Mobile> pMobile = mmapper.getALLMobile(); System.out.println(pMobile.size()); Mobile m = mmapper.getMobile(1); System.out.println(m.getPerson().getName()); }
补上一个mybatis封装类
package wjf.orm; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; //工具类 public class MybatisUtil { public static SqlSessionFactory sqlSessionFactory() { String resource = "config.xml";// 获取mybatis-cofig.xml配置文件的路径 Reader reader = null;// 创建reader try { reader = Resources.getResourceAsReader(resource);// 获得reader } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } SqlSessionFactory SqlSessionFactory = new SqlSessionFactoryBuilder() .build(reader);// 获得sqlsessionfactory return SqlSessionFactory; } // 获取getter public static SqlSessionFactory getSqlsessionfactory() { return sqlSessionFactory(); } }
