一对一操作
数据准备
-
card表
CREATE TABLE card( id INT PRIMARY KEY AUTO_INCREMENT, number VARCHAR(30), pid INT, CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id) );
-
person表
CREATE TABLE person( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT );
-
Card类
package com.cmy.bean; /** * @author chenmingyong */ public class Card { private Integer id; private String number; private Person p; public Card() { } public Card(Integer id, String number, Person p) { this.id = id; this.number = number; this.p = p; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getNumber() { return number; } public void setNumber(String number) { this.number = number; } public Person getP() { return p; } public void setP(Person p) { this.p = p; } @Override public String toString() { return "Card{" + "id=" + id + ", number='" + number + '\'' + ", p=" + p + '}'; } }
-
Person类
package com.cmy.bean; /** * @author chenmingyong */ public class Person { private Integer id; private String name; private Integer age; public Person() { } public Person(Integer id, String name, Integer age) { this.id = id; this.name = name; this.age = age; } 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 Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "Person{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + '}'; } }
-
核心配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!--MyBitis的DTD约束,定义xml标签约束,使开发者按照定义书写--> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <!--configuration 根标签--> <configuration> <!--引入数据库配置文件--> <properties resource="jdbc.properties" /> <!--配置LOG4J--> <settings> <setting name="logImpl" value="log4j"/> </settings> <!--起别名--> <typeAliases> <package name="com.cmy.bean" /> </typeAliases> <!--environments配置数据库环境,环境可以有很多个,而default属性则是指定某个环境--> <environments default="mysql1"> <!-- environment数据库环境,id属性:唯一标识 --> <environment id="mysql1"> <!--transactionManager事务管理,type 采用JDBC默认的事务管理--> <transactionManager type="JDBC" /> <!--dataSource数据库源信息 type属性 连接池--> <dataSource type="POOLED"> <!--property 获取数据库连接的配置信息--> <property name="driver" value="${driver}" /> <property name="url" value="${url}" /> <property name="username" value="${username}" /> <property name="password" value="${password}" /> </dataSource> </environment> </environments> <!--mappers配置映射关系--> <mappers> <package name="com.cmy.mapper"/> </mappers> </configuration>
实现步骤
-
创建映射接口并编写注解代码
-
CardMapper接口
package com.cmy.mapper; import com.cmy.bean.Card; import com.cmy.bean.Person; import org.apache.ibatis.annotations.One; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import java.util.List; /** * @author chenmingyong */ public interface CardMapper { /** * 查询全部信息 * @return Card 集合对象 */ @Select("SELECT * FROM card") @Results({ @Result(column = "id", property = "id"), @Result(column = "number", property = "number"), @Result( property = "p", // 被包含对象的属性 javaType = Person.class, // 被包含对象的实际数据类型 column = "pid", // 根据查询出来的pid字段来查询person表 /* one、@one:一对一固定写法 select属性:指定调用哪个接口中的哪个方法 */ one = @One(select = "com.cmy.mapper.PersonMapper.selectById") ) }) public abstract List<Card> selectAll(); }
-
Results
:封装映射关系的父注解Result[] value()
:定义了Result数组 -
Result
:封装映射关系的子注解- column属性:查询出的表中字段的名称
- property属性:实体对象中的属性名称
- javaType属性:被包含对象的数据类型
- one属性:一对一查询固定属性
- @One属性:一对一查询的注解
- select属性:指定调用某个接口中的方法
-
-
PersonMapper接口
package com.cmy.mapper; import com.cmy.bean.Person; import org.apache.ibatis.annotations.Select; /** * @author chenmingyong */ public interface PersonMapper { /** * 通过person表的id编号查询数据 * @param id person表的id编号 * @return Person 对象 */ @Select("SELECT * FROM person WHERE id=#{id}") public abstract Person selectById(Integer id); }
-
-
编写测试代码
package com.cmy.ono_to_one; import com.cmy.bean.Card; import com.cmy.mapper.CardMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.InputStream; import java.util.List; /** * @author chenmingyong */ public class Test01 { @Test public void selectAll(){ InputStream is = null; SqlSession sqlSession = null; try{ is = Resources.getResourceAsStream("MyBatisConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); sqlSession = sqlSessionFactory.openSession(true); CardMapper mapper = sqlSession.getMapper(CardMapper.class); List<Card> cards = mapper.selectAll(); for (Card card : cards) { System.out.println(card); } } catch (Exception e){ e.printStackTrace(); } finally { if(sqlSession != null){ sqlSession.close(); } if(is != null){ try { is.close(); } catch (Exception e){ e.printStackTrace(); } } } } }
一对多操作
数据准备
-
classes表
CREATE TABLE classes( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) );
-
student表
CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), age INT, cid INT, CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id) );
-
Classes类
package com.cmy.bean; import java.util.List; /** * @author chenmingyong */ public class Classes { private Integer id; private String name; private List<Student> students; public Classes() { } public Classes(Integer id, String name, List<Student> students) { this.id = id; this.name = name; this.students = students; } 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 List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } @Override public String toString() { return "Classes{" + "id=" + id + ", name='" + name + '\'' + ", students=" + students + '}'; } }
-
Student类
package com.cmy.bean; import java.util.List; /** * @author chenmingyong */ public class Student { private Integer id; private String name; private Integer age; public Student() { } public Student(Integer id, String name, Integer age, List<Course> courses) { this.id = id; this.name = name; this.age = age; this.courses = courses; } 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 Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + '}'; } }
实现步骤
-
创建映射接口并编写注解代码
-
ClassesMapper接口
package com.cmy.mapper; import com.cmy.bean.Classes; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import java.util.List; /** * @author 陈明勇 */ public interface ClassesMapper { /** * 查询全部数据 * @return 班级集合对象 */ @Select("SELECT * FROM classes") @Results({ @Result(column = "id", property = "id"), @Result(column = "name", property = "name"), @Result( property = "students", // 被包含对象的变量名 javaType = List.class, // 被包含对象的实际数据类型 column = "id", // 根据查询出的classes表的id字段来查询学生表的数据 /* many、@many 一对多的固定写法 select:指定调用哪个接口中的哪个方法 */ many = @Many(select = "com.cmy.mapper.StudentMapper.selectByCid") // 根据查询出的classes表的id字段来查询学生表的数据 ) }) public abstract List<Classes> selectAll(); }
-
Results
:封装映射关系的父注解Result[] value()
:定义了Result数组 -
Result
:封装映射关系的子注解- column属性:查询出的表中字段的名称
- property属性:实体对象中的属性名称
- javaType属性:被包含对象的数据类型
- many属性:一对多查询固定属性
- @Many属性:一对多查询的注解
- select属性:指定调用某个接口中的方法
-
-
StudentMapper接口
package com.cmy.mapper; import com.cmy.bean.Student; import org.apache.ibatis.annotations.Select; import java.util.List; /** * @author chenmingyong */ public interface StudentMapper { /** * 通过班级的cid查询数据 * @param cid 班级编号 * @return 学生集合对象 */ @Select("SELECT * FROM student WHERE cid=#{cid}") public abstract List<Student> selectByCid(Integer cid); }
-
-
编写测试代码
package com.cmy.one_to_many; import com.cmy.bean.Card; import com.cmy.bean.Classes; import com.cmy.mapper.ClassesMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.InputStream; import java.util.List; /** * @author chenmingyong */ public class Test01 { @Test public void selectAll(){ InputStream is = null; SqlSession sqlSession = null; try{ is = Resources.getResourceAsStream("MyBatisConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); sqlSession = sqlSessionFactory.openSession(true); ClassesMapper mapper = sqlSession.getMapper(ClassesMapper.class); List<Classes> list = mapper.selectAll(); for (Classes cls : list) { System.out.println(cls); } } catch (Exception e){ e.printStackTrace(); } finally { if(sqlSession != null){ sqlSession.close(); } if(is != null){ try { is.close(); } catch (Exception e){ e.printStackTrace(); } } } } }
多对多操作
数据准备
-
course表
CREATE TABLE course( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) );
-
student表
CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), age INT, cid INT, CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id) );
-
stu_cr表
CREATE TABLE stu_cr( id INT PRIMARY KEY AUTO_INCREMENT, sid INT, cid INT, CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES classes(id) );
-
Course类
package com.cmy.bean; /** * @author chenmingyong */ public class Course { private Integer id; private String name; public Course() { } public Course(Integer id, String name) { this.id = id; this.name = 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 "Course{" + "id=" + id + ", name='" + name + '\'' + '}'; } }
-
Student类
package com.cmy.bean; import java.util.List; /** * @author chenmingyong */ public class Student { private Integer id; private String name; private Integer age; public Student() { } public Student(Integer id, String name, Integer age, List<Course> courses) { this.id = id; this.name = name; this.age = age; this.courses = courses; } 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 Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + '}'; } }
实现步骤
-
创建映射接口并编写注解代码
-
StudentMapper接口
package com.cmy.mapper; import com.cmy.bean.Student; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import java.util.List; /** * @author chenmingyong */ public interface StudentMapper { /** * 查询所有学生信息 * @return 学生集合对象 */ @Select("SELECT DISTINCT s.id,s.name,s.age FROM student s,stu_cr sc WHERE sc.sid=s.id") @Results({ @Result(column = "id", property = "id"), @Result(column = "name", property = "name"), @Result(column = "age", property = "age"), @Result( property = "courses", // 被包含对象的变量名 javaType = List.class, // 被包含对象的实际数据类型 column = "id", // 根据查询出来的id,去查询中间表和课程表 many = @Many(select = "com.cmy.mapper.CourseMapper.selectBySid") ) }) public abstract List<Student> selectAll(); }
-
Results
:封装映射关系的父注解Result[] value()
:定义了Result数组 -
Result
:封装映射关系的子注解- column属性:查询出的表中字段的名称
- property属性:实体对象中的属性名称
- javaType属性:被包含对象的数据类型
- many属性:一对多查询固定属性
- @Many属性:一对多查询的注解
- select属性:指定调用某个接口中的方法
-
-
CourseMapper接口
package com.cmy.mapper; import com.cmy.bean.Course; import org.apache.ibatis.annotations.Select; import java.util.List; /** * @author chenmingyong */ public interface CourseMapper { /** * 根据学生id查询所选课程 * @param sid 学号 * @return 课程集合 */ @Select("SELECT c.id,c.name FROM stu_cr sc,course c WHERE sc.cid=c.id AND sc.sid=#{id}") public abstract List<Course> selectBySid(Integer sid); }
-
-
编写测试代码
package com.cmy.many_to_many; import com.cmy.bean.Course; import com.cmy.bean.Student; import com.cmy.mapper.StudentMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.InputStream; import java.util.List; /** * @author chenmingyong */ public class Test01 { @Test public void selectAll(){ SqlSession sqlSession = null; InputStream is = null; try { // 1.加载配置文件 is = Resources.getResourceAsStream("MyBatisConfig.xml"); // 2.获取Session工厂对象 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is); // 3.通过工厂对象获取SqlSession对象 sqlSession = sessionFactory.openSession(true); // 4.获取OneToOneMapper接口的实现类对象 StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); // 5.通过实现类对象调用方法,并接收结果 List<Student> students = mapper.selectAll(); // 6.遍历对象 for (Student student : students) { System.out.println(student.getId() + "," + student.getName() + "," + student.getAge()); for (Course cours : student.getCourses()) { System.out.println("\t" + cours); } } } catch (Exception e) { e.printStackTrace(); } finally { // 6.释放资源 if (sqlSession != null) { sqlSession.close(); } if (is != null) { try { is.close(); } catch (Exception e) { e.printStackTrace(); } } } } }