测试代码结构图:
①db.sql建表语句
/*辅导员信息表*/
create table t_counsellor (
co_id int primary key auto_increment,
co_name varchar(20)
);
/*班级信息表*/
create table t_classes (
cl_id int primary key auto_increment,
cl_name varchar(20),
co_id int,
foreign key(co_id) references t_counsellor(co_id)
);
②实体类
package com.geekymv.model;
/**
* 班级类
*/
public class Classes {
private Integer id;
private String name;
private Counsellor counsellor;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Counsellor getCounsellor() {
return counsellor;
}
public void setCounsellor(Counsellor counsellor) {
this.counsellor = counsellor;
}
@Override
public String toString() {
return "Class [id=" + id + ", name=" + name + ", counsellor="
+ counsellor + "]";
}
}
package com.geekymv.model;
/**
* 辅导员
*/
public class Counsellor {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Counsellor [id=" + id + ", name=" + name + "]";
}
}
③SQL映射文件ClassesMapper.xml
<?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.geekymv.model.ClassesMapper">
<!--
一对一映射方式一:联表查询,使用嵌套结果映射来处理重复的联合结果的子集封装联表查询的数据(去除重复的数据)
select * from t_classes cl, t_counsellor co where cl.co_id=co.co_id and cl.cl_id = 1;
-->
<select id="getClasses" parameterType="int" resultMap="ClassResultMap">
select * from t_classes cl, t_counsellor co where cl.co_id=co.co_id and cl.cl_id = #{id};
</select>
<resultMap type="Classes" id="ClassResultMap">
<id property="id" column="cl_id"/>
<result property="name" column="cl_name"/>
<!--
association:用于一对一的关联查询
javaType:对象属性的类型
-->
<association property="counsellor" javaType="Counsellor">
<id property="id" column="co_id"/>
<result property="name" column="co_name"/>
</association>
</resultMap>
<!--
一对一映射方式二:两次查询
-->
<select id="getClasses2" parameterType="int" resultMap="ClassResultMap2">
select * from t_classes where cl_id=#{id}
</select>
<resultMap type="Classes" id="ClassResultMap2">
<id property="id" column="cl_id"/>
<result property="name" column="cl_name"/>
<association property="counsellor" column="co_id" select="getCounsellor" >
</association>
</resultMap>
<!--
resultType:SQL语句需要使用别名
-->
<select id="getCounsellor" parameterType="int" resultType="Counsellor">
select co_id id, co_name name from t_counsellor where co_id=#{id}
</select>
</mapper>
④配置文件
conf.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>
<package name="com.geekymv.model"/>
</typeAliases>
<environments default="development">
<environment id="development">
<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="com/geekymv/model/ClassesMapper.xml" />
</mappers>
</configuration>
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=root
log4j.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="debug" />
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
⑤工具类MyBatisUtil.java
package com.geekymv.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;
public class MyBatisUtil {
public static SqlSession getSqlSession() {
try {
//InputStream inputStream = MybatisUtil.class.getClassLoader().getResourceAsStream("conf.xml");
InputStream inputStream = Resources.getResourceAsStream("conf.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = factory.openSession();
return session;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
⑥测试类
package com.geekymv.test;
import org.apache.ibatis.session.SqlSession;
import com.geekymv.model.Classes;
import com.geekymv.util.MyBatisUtil;
/**
* 班级和辅导员是一对一的关联关系
*/
public class MyTest {
public static void main(String[] args) {
SqlSession session = MyBatisUtil.getSqlSession();
// String statement = "com.geekymv.model.ClassesMapper.getClasses";
String statement = "com.geekymv.model.ClassesMapper.getClasses2";
Classes cl = session.selectOne(statement , 2);
session.close();
System.out.println(cl);
}
}