myBatis功能详解

一、myatis开发环境搭建 1.新建Java项目 将mybatis的jar包和mysql的jar包复制到src目录下 然后选中这2个包右击build path—>add to build path 这个2个包会自动转到 mybatis的jar包下载

https://github.com/mybatis/mybatis-3/releases

2.建立数据库users，建立表user(id,userName,userPwd)

3.引入mybatis的主配置文件mybatis-config.xml（名字在代码中要用到）

百度解决

http://mybatis.github.io/mybatis-3/getting-started.html

<?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> <!-- 对事务的管理和连接池的配置 --> <environments default="development"> <environment id="users"> <!—users为数据库的名称 --> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost/users" /> <property name="username" value="root" /> <property name="password" value="" /> </dataSource> </environment> </environments> <!-- mapping 文件路径配置 --> <mappers> <!-- <mapper class="model.UserMapper" /> --> <mapper resource="model/userMapper.xml"/> --> </mappers> </configuration>

4.将数据库连接信息写入单独的properties文件

（1）主配置文件中将连接参数修改为变量

<property name="driver" value="${db.driver}" /> <property name="url" value="${db.url}" /> <property name="username" value="${db.user}" /> <property name="password" value="${db.password}" />

(2)引入db.properties文件

<properties resource="db.properties"></properties>

(3)在src目录下添加db.properties文件

db.driver=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:3306/users db.user=root db.password=123456

5.在model包下面编写实体类User User(id,userName,userPwd)

6.在model包下面，编写userMapper.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="model.userMapper"> <!—parameterType定义传入参数id的数据类型， resultType 定义返回值的数据类型--> <select id="selectUser" parameterType="int" resultType="model.User"> select * from User where id = #{id} </select> </mapper>

注：写好map文件之后，要去mybatis-config.xml中去配置文件路径

<mappers> <mapper resource="model/userMapper.xml"/> </mappers>

7.编写测试程序

(1)编写MyBatis的工具类

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 MybatisUtils { public static SqlSession openSession() throws IOException { String resource = "mybatis-config.xml"; InputStream in = Resources.getResourceAsStream(resource); //名称必须必须与mybatis-config.xml文件中写的数据库名称一致 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in,"users"); return sessionFactory.openSession(); } }

（2）新建一个Junit test case类（在测试前请先在数据库中对应表中新建一个user）

package test; import java.io.IOException; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import common.MybatisUtils; import model.User; public class Mytest { @Test public void testSelect() throws IOException { SqlSession session=MybatisUtils.openSession(); String statement="model.userMapper.selectUser"; User user=session.selectOne(statement, 1); System.out.println(user); session.close(); } @Test public void testSelectAll() throws IOException { SqlSession session=MybatisUtils.openSession(); String statement="model.userMapper.selectAll"; List<User> list=session.selectList(statement); System.out.println(list); session.close(); } @Test public void testInsertUser() throws IOException { SqlSession session=MybatisUtils.openSession(); String statement="model.userMapper.addUser"; User user=new User(); user.setName("巨"); user.setPwd("654321"); session.insert(statement, user); session.commit(); session.close(); } @Test public void testUpdateUser() throws IOException { SqlSession session=MybatisUtils.openSession(); String statement="model.userMapper.updateUser"; User user=new User(); user.setId(3); user.setName("巨"); user.setPwd("juligang"); session.update(statement, user); session.commit(); session.close(); } @Test public void testDeleteUser() throws IOException { SqlSession session=MybatisUtils.openSession(); String statement="model.userMapper.deleteUser"; session.delete(statement, 3); session.commit(); session.close(); } }

搭建好的项目最小目录 二、通过接口的方式访问增删改查方法 （1）新建一个UserMapper接口，接口中的方法与userMapper.xml中的sql命令标签的id一致。接口类和.xml文件默认在同一个包内，如果不在需要定义.xml的路径。 否则会报：Invalid bound statement (not found) 如：<select id="selectUser" parameterType="int" resultType="model.User"> select * from user where id = #{id} </select> id为selectUser，所以接口中的方法名称也是selectUser

package model; import java.util.List; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; public interface UserMapper { public User selectUser(int id); public List<User> selectAll(); public void addUser(User user); public void updateUser(User user); public void deleteUser(int id); }

（2）修改mybatis-config.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> <!-- 对事务的管理和连接池的配置 --> <environments default="development"> <environment id="booksystem"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${db.driver}" /> <property name="url" value="${db.url}" /> <property name="username" value="${db.user}" /> <property name="password" value="${db.password}" /> </dataSource> </environment> </environments> <!-- mapping 文件路径配置 --> <mappers> <!— 指明接口的路径 --> <mapper class="model.UserMapper"/> </mappers> </configuration>

（3）userMapper.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="model.UserMapper"> <select id="selectUser" parameterType="int" resultType="model.User"> select * from user where id = #{id} </select> <select id="selectAll" resultType="model.User"> select * from user </select> <insert id="addUser" parameterType="model.User"> insert into user (name,pwd) values (#{name},#{pwd}) </insert> <update id="updateUser" parameterType="model.User"> update user set name=#{name},pwd=#{pwd} where id=#{id} </update> <delete id="deleteUser" parameterType="int"> delete from user where id=#{id} </delete> </mapper>

（4）新建一个Junit test case类

package test; import java.io.IOException; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import common.MybatisUtils; import model.User; import model.UserMapper; public class MapperTest { @Test public void testSelectUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); User user=mapper.selectUser(1); session.close(); System.out.println(user); } @Test public void testSelectAll() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); List<User> list=mapper.selectAll(); session.close(); System.out.println(list); } @Test public void testInsertUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); User user=new User(); user.setName("巨"); user.setPwd("1234"); mapper.addUser(user); session.commit(); session.close(); } @Test public void testUpdateUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); User user=new User(); user.setId(5); user.setName("巨"); user.setPwd("juligang"); mapper.updateUser(user); session.commit(); session.close(); } @Test public void testDeleteUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); mapper.deleteUser(5); session.commit(); session.close(); } }

这样就实现了通过接口访问的形式进行增删改查操作

三、通过注解的方式实现增删改查操作 （1）修改UserMapper接口类

package model; import java.util.List; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; public interface UserMapper { @Select("select * from user where id = #{id}") public User selectUser(int id); @Select("select * from user") public List<User> selectAll(); @Insert("insert into user (name,pwd) values (#{name},#{pwd})") public void addUser(User user); @Update("update user set name=#{name},pwd=#{pwd} where id=#{id}") public void updateUser(User user); @Delete("delete from user where id=#{id}") public void deleteUser(int id); }

（2）mybtis-config.xml文件不用变 （3）修改userMapper.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="model.UserMapper"> </mapper>

说明：这里的配置不能与注解方式冲突，2者只能选其一 （4）利用之前建立的Junit test case类进行测试

package test; import java.io.IOException; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import common.MybatisUtils; import model.User; import model.UserMapper; public class MapperTest { @Test public void testSelectUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); User user=mapper.selectUser(1); session.close(); System.out.println(user); } @Test public void testSelectAll() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); List<User> list=mapper.selectAll(); session.close(); System.out.println(list); } @Test public void testInsertUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); User user=new User(); user.setName("巨"); user.setPwd("1234"); mapper.addUser(user); session.commit(); session.close(); } @Test public void testUpdateUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); User user=new User(); user.setId(5); user.setName("巨"); user.setPwd("juligang"); mapper.updateUser(user); session.commit(); session.close(); } @Test public void testDeleteUser() throws IOException { SqlSession session=MybatisUtils.openSession(); UserMapper mapper=session.getMapper(UserMapper.class); mapper.deleteUser(5); session.commit(); session.close(); } }

四、将SQL命令写入一个专门类中通过注解引入 在上面的基础上稍作修改 （1）新建一个Sqlprovider类

package model; public class Sqlprovider { public String selectUser(){ return "select * from user where id = #{id}"; } public String selectAll(){ return "select * from user"; } public String addUser(){ return "insert into user (name,pwd) values (#{name},#{pwd})"; } public String updateUser(){ return "update user set name=#{name},pwd=#{pwd} where id=#{id}"; } public String deleteUser(){ return "delete from user where id=#{id}"; } }

（2）修改UserMapper类中的注解

package model; import java.util.List; import org.apache.ibatis.annotations.SelectProvider; public interface UserMapper { @SelectProvider(type=Sqlprovider.class,method="selectUser") public User selectUser(int id); @SelectProvider(type=Sqlprovider.class,method="selectAll") public List<User> selectAll(); @SelectProvider(type=Sqlprovider.class,method="addUser") public void addUser(User user); @SelectProvider(type=Sqlprovider.class,method="updateUser") public void updateUser(User user); @SelectProvider(type=Sqlprovider.class,method="deleteUser") public void deleteUser(int id); }

（3）类似于第三章的一样去测试

四、一对一关系的增删改查 （1）在数据库中新建student 和contact数据表 学生表 Student(id,name,sex,pwd,phone,grade,photo)

紧急联系人 Contact(id,name,sex,relation,phone)

两个表的连接关系

在Contact表中增加外键sid，指向Student的主键id 学生 id name 1 张三

联系人 id sid name 5 1 李四

外键(foreign key),主键(primary key)

参照完整性约束（外键约束）

外键会自动建立索引，设置该索引为唯一索引，即可实现一对一关联关系

学生-紧急联系人（一对一）

站在学生角度：getContact(),站在联系人角度：getStudent()

学生可以没有联系人，联系人不可能没有对应的学生。

准备工作：

1.建立项目Student&Contact,拷贝mybatis主配置文件，properties文件，MyBatisUtils工具类 2.建立实体类Student和Contact，注意在student类中要把Contact作为属性，反之Contact也要把student作为属性，如果需要测试时打印，需要在类中重写toString方法

Student.java package model; public class Student { private int id; private String name; private String sex; private String pwd; private String phone; private String grade; private String photo; private Contact contact; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getGrade() { return grade; } public void setGrade(String grade) { this.grade = grade; } public String getPhoto() { return photo; } public void setPhoto(String photo) { this.photo = photo; } public Contact getContact() { return contact; } public void setContact(Contact contact) { this.contact = contact; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", pwd=" + pwd + ", phone=" + phone + ", grade=" + grade + ", photo=" + photo + ", contact=" + contact + "]"; } } Contact.java package model; public class Contact { private int id; private String name; private String sex; private String relation; private String phone; private Student student; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getRelation() { return relation; } public void setRelation(String relation) { this.relation = relation; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public Student getStudent() { return student; } public void setStudent(Student student) { this.student = student; } @Override public String toString() { return "Contact [id=" + id + ", name=" + name + ", sex=" + sex + ", relation=" + relation + ", phone=" + phone + ", student=" + student + "]"; } }

3.建立StudentMapper.xml和ContactMapper.xml映射文件模板 StudentMapper.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="mapper.StudentMapper"> <!-- useGeneratedKeys="true" keyProperty="id"在student对象存入数据库之后，会把数据库分配的ID直接返回给student对象 --> <insert id="add" parameterType="model.Student" useGeneratedKeys="true" keyProperty="id"> insert into student ( name, sex, pwd, phone, grade, photo ) values ( #{name}, #{sex}, #{pwd}, #{phone}, #{grade}, #{photo} ) </insert> <!—由于返回的student对象中包含了联系人信息，所以要使用resultMap，而不是resulttype --> <select id="select" parameterType="int" resultMap="student"> select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, c.id cid, c.name cname, c.sex csex, c.relation cralation, c.phone cphone from student s left join contact c ON c.sid=s.id WHERE s.id=#{id} </select> <!-- 对上面的resultMap做一个说明，注意id要和上面的result名对应 --> <resultMap type="model.Student" id="student"> <id property="id" column="sid"/> <result property="name" column="sname"/> <result property="sex" column="ssex"/> <result property="pwd" column="spwd"/> <result property="phone" column="sphone"/> <result property="grade" column="sgrade"/> <result property="photo" column="sphoto"/> <!-- 由于student和contact是一对一的关系，所以使用association --> <association property="contact" javaType="model.Contact"> <id property="id" column="cid"/> <result property="name" column="cname"/> <result property="sex" column="csex"/> <result property="relation" column="crelation"/> <result property="phone" column="cphone"/> </association> </resultMap> <delete id="delete" parameterType="int"> delete from student where id=#{id} </delete> <update id="update1" parameterType="model.Student"> update student set name=#{name},sex=#{sex} where id=#{id} </update> </mapper>

ContactMapper.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="mapper.ContactMapper"> <insert id="add" parameterType="model.Contact"> insert into contact ( name, sex, relation, phone, sid ) values ( #{name}, #{sex}, #{relation}, #{phone}, #{student.id} ) </insert> <select id="select" parameterType="int" resultMap="contact"> select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, c.id cid, c.name cname, c.sex csex, c.relation cralation, c.phone cphone from contact c left join student s on c.sid=s.id where c.id=#{id} </select> <!-- 对上面的resultMap做一个说明，注意id要和上面的result名对应 --> <resultMap type="model.Contact" id="contact"> <id property="id" column="cid"/> <result property="name" column="cname"/> <result property="sex" column="csex"/> <result property="relation" column="crelation"/> <result property="phone" column="cphone"/> <association property="student" javaType="model.Student"> <id property="id" column="sid"/> <result property="name" column="sname"/> <result property="sex" column="ssex"/> <result property="pwd" column="spwd"/> <result property="phone" column="sphone"/> <result property="grade" column="sgrade"/> <result property="photo" column="sphoto"/> </association> </resultMap> <delete id="delete" parameterType="int"> delete from contact where id=#{id} </delete> <update id="update1" parameterType="model.Contact"> update contact set name=#{name},sex=#{sex} where id=#{id} </update> <update id="update2" parameterType="model.Contact"> update contact set sid=#{student.id} where id=#{id} </update> </mapper>

4.建立StudentMapper和ContactMapper接口

StudentMapper.java package mapper; import model.Student; public interface StudentMapper { public void add(Student student); public Student select(int id); public void delete(int id); public void update1(Student student); } package mapper; import model.Contact; public interface ContactMapper { public void add(Contact contact); public Contact select(int id); public void delete(int id); //修改非关联字段 public void update1(Contact contact); //修改关联字段 public void update2(Contact contact); }

5.在mybatis-config.xml中添加接口声明

<mapper class="mapper.ContactMapper"/> <mapper class="mapper.StudentMapper"/>

6.建表student和contact，设定其一对一的对应关系：设置外键为唯一（unique） 注：先建立好数据库，设置好映射关系，然后再去操作数据库

以上所有都已经准备好，接下来分别讲述增删改查功能 （1）增加数据 同时保存学生信息和紧急联系人的信息

1.学生信息单独保存。

2.保存联系人信息的时候，指定该联系人对应的学生，实际上指定联系人对应学生的ID。所以学生 信息保存之后，要想办法拿到赋给紧急联系人，否则会报错。

返回插入后生成的自增长属性值的办法，在insert标签上增加

useGeneratedKeys=”true” keyProperty=”id”

当Student对象写入到数据库之后，分配的ID值会立即返回给Student对象 //增加一组学生和联系人

@Test public void testInsert() throws IOException { SqlSession session=MybatisUtils.openSession(); StudentMapper sMapper=session.getMapper(StudentMapper.class); Student student=new Student(); student.setName("ccc"); student.setSex("男"); student.setPwd("123456"); student.setPhone("15201916370"); student.setGrade("一年级"); sMapper.add(student); session.commit(); ContactMapper cMapper=session.getMapper(ContactMapper.class); Contact contact=new Contact(); contact.setName("ddd"); contact.setPhone("13636709659"); contact.setRelation("夫妻"); contact.setSex("女"); contact.setStudent(student); cMapper.add(contact); session.commit(); session.close(); }

（2）查询数据 1.StudentMapper中，实现getContact的功能(在Student中，实现getContact的功能)

分析：如果sql语句写成: select * from student where id=#{id}

这样获得的Student对象肯定是得不到联系人信息的。SQL语句中一定要包含联系人信息。

2.ContactMapper中，实现getStudent的功能（在Contact中，实现getStudent的功能）

<select id="select" parameterType="int" resultMap="student"> select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, c.id cid, c.name cname, c.sex csex, c.relation cralation, c.phone cphone <!-- 以上的信息会产生一个新表，新表的信息提取自下面的t和c表 --> from student s left join contact c <!-- teacher左连接course会以提取teacher信息为主，如果没有course信息也会提取teacher信息 --> ON c.sid=s.id WHERE s.id=#{id} </select> <!-- 对上面的resultMap做一个说明，注意id要和上面的result名对应 --> <resultMap type="model.Student" id="student"> <id property="id" column="sid"/> <result property="name" column="sname"/> <result property="sex" column="ssex"/> <result property="pwd" column="spwd"/> <result property="phone" column="sphone"/> <result property="grade" column="sgrade"/> <result property="photo" column="sphoto"/> <!-- 由于student和contact是一对一的关系，所以使用association --> <association property="contact" javaType="model.Contact"> <id property="id" column="cid"/> <result property="name" column="cname"/> <result property="sex" column="csex"/> <result property="relation" column="crelation"/> <result property="phone" column="cphone"/> </association> </resultMap>

查询结果：

//查询ID为2的学生信息（包含联系人），查询ID为2的联系人信息（包含对应的学生信息）

@Test public void testSelect() throws IOException { SqlSession session=MybatisUtils.openSession(); StudentMapper sMapper=session.getMapper(StudentMapper.class); Student student=sMapper.select(2); System.out.println(student); ContactMapper cMapper=session.getMapper(ContactMapper.class); Contact contact=cMapper.select(2); System.out.println(contact); session.close(); }

（3）删除数据 contact表通过sid，与student的id进行关联

sid是否可以为空，决定了是否允许没有对应学生的联系人存在

删除时有以下几种情况

1.删除联系人（从表：包含外键字段的表）

sid可能为空，sid可能是学生表中一个学生的id

2.删除学生（主表：包含主键字段的表）

a.学生没有对应的联系人 可以直接删除

b.学生有对应的联系人，有以下三种情况 外键设置为“删除时”No action 必须先删除与其相关联的联系人后，才能删除该学生

级联删除 在数据库中，外键设置为“删除时”cascade 1）删除学生时，自动删除其联系人（不会通知，比较危险） 在数据库中，外键设置为“删除时”set null 2）删除学生时，其联系人的sid都被设置为null。 如果设置了级联删除，那么可以直接删除该学生，其对应的联系人也会被删除，这种操作比较危险，一般不采用级联删除 @Test public void testDelete() throws IOException { //要删除Student，必须要先删除出其对应的联系人，因为Student被Contact表引用 SqlSession session=MybatisUtils.openSession(); //ContactMapper cMapper=session.getMapper(ContactMapper.class); //cMapper.delete(1); StudentMapper sMapper=session.getMapper(StudentMapper.class); sMapper.delete(5); session.commit(); session.close(); }

（4）修改数据 1.修改从表数据 非关联字段可以随便修改， 关联字段sid必须修改为student表中存在的（而且没有联系人的）学生的id或者修改为null（前提是contact表的sid已经设置为可空）。 2.修改主表数据 非主键字段可以随便修改 主键字段不能随便修改，一般也不需要修改 如果一定要修改， （1）可以先切断其和关联数据的连接关系（比较麻烦），然后进行修改 （2）另外，还以采用级联更新的方式 主键字段的修改可以采用级联的方式 1）修改主键后，其对应的联系人的sid被设置为空 修改外键的属性“更新时”cascade 2) 修改主键后，其对应的联系人的sid被同时修改。

//修改非关联字段 @Test public void testUpdate1() throws IOException { SqlSession session=MybatisUtils.openSession(); StudentMapper sMapper=session.getMapper(StudentMapper.class); Student student=new Student(); student.setId(6); student.setName("康熙"); student.setSex("男"); sMapper.update1(student); ContactMapper cMapper=session.getMapper(ContactMapper.class); Contact contact=new Contact(); contact.setId(4); contact.setName("华妃"); contact.setSex("女"); cMapper.update1(contact); session.commit(); session.close(); } //修改关联字段 @Test public void testUpdate2() throws IOException { SqlSession session=MybatisUtils.openSession(); StudentMapper sMapper=session.getMapper(StudentMapper.class); ContactMapper cMapper=session.getMapper(ContactMapper.class); Contact contact=new Contact(); contact.setId(4); contact.setStudent(sMapper.select(6)); cMapper.update2(contact); session.commit(); session.close(); }

（5）增加mybatis日志功能

（1）新建log4j.properties文件复制到src目录下

# Global logging configuration log4j.rootLogger=ERROR, stdout # MyBatis logging configuration... log4j.logger.javastudy=DEBUG # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

注意这行代码 log4j.logger.javastudy=DEBUG

（2）加入log4j的jar包 添加到Build path中去

五、一对多的关系 一对多关系

教师-课程：一个教师，可以上多门课程，一门课程只能一个教师上。 站在教师角度:getClasses() 站在课程角度:getTeacher()

外键在多方

课程 course(id,name,type,hours，) 教师 teacher(id,name,sex,phone)

1.建表（和一对一建表完全相同，只是没有设置外键索引的唯一性） 建立teacher和course数据表 2.新建Teacher和Course类

Teacher.java package model; import java.util.List; public class Teacher { private int id; private String name; private String sex; private String phone; //因为一个老师可能对应多个课程，所以课程要定义为一个List private List<Course> courses; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public List<Course> getCourses() { return courses; } public void setCourses(List<Course> courses) { this.courses = courses; } @Override public String toString() { return "Teacher [id=" + id + ", name=" + name + ", sex=" + sex + ", phone=" + phone + ", courses=" + courses + "]"; } } Course.java package model; public class Course { private int id; private String name; private String type; private String hours; private Teacher teacher; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getHours() { return hours; } public void setHours(String hours) { this.hours = hours; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } @Override public String toString() { return "Course [id=" + id + ", name=" + name + ", type=" + type + ", hours=" + hours + ", techer=" + teacher + "]"; } }

3.新建TeacherMapper和CourseMapper接口

TeacherMapper.Java package mapper; import model.Teacher; public interface TeacherMapper { public void add(Teacher teacher); public Teacher select(int id); public void delete(int id); public void update1(Teacher teacher); } CourseMapper.java package mapper; import model.Course; public interface CourseMapper { public void add(Course course); public Course select(int id); public void delete(int id); public void update(Course course); }

4.编写TeacherMapper.xml和CourseMapper.xml文件 TeacherMapper.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="mapper.TeacherMapper"> <insert id="add" parameterType="model.Teacher" useGeneratedKeys="true" keyProperty="id"> insert into teacher ( name, sex, phone ) values ( #{name}, #{sex}, #{phone} ) </insert> <!-- 提取老师要是连课程一起提取，课程是List类型 --> <select id="select" parameterType="int" resultMap="teacher"> select c.id cid, c.name cname, c.type ctype, c.hours chours, t.id tid, t.name tname, t.sex tsex, t.phone tphone from teacher t left join course c on c.tid=t.id WHERE t.id=#{id} </select> <!-- 对上面的resultMap做一个说明，注意id要和上面的result名对应 --> <resultMap type="model.Teacher" id="teacher"> <id property="id" column="tid"/> <result property="name" column="tname"/> <result property="sex" column="tsex"/> <result property="phone" column="tphone"/> <!-- courses为Course类的属性 --> <collection property="courses" ofType="model.Course"> <id property="id" column="cid"/> <result property="name" column="cname"/> <result property="type" column="ctype"/> <result property="hours" column="chours"/> </collection> </resultMap> <delete id="delete" parameterType="int"> delete from teacher where id=#{id} </delete> <update id="update" parameterType="model.Teacher"> update teacher set name=#{name},sex=#{sex} where id=#{id} </update> </mapper>

CourseMapper.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="mapper.CourseMapper"> <insert id="add" parameterType="model.Course"> insert into course ( name, type, hours, tid ) values ( #{name}, #{type}, #{hours}, #{teacher.id} ) </insert> <select id="select" parameterType="int" resultMap="course"> select c.id cid, c.name cname, c.type ctype, c.hours chours, t.id tid, t.name tname, t.sex tsex, t.phone tphone from course c left join teacher t on c.tid=t.id WHERE c.id=#{id} </select> <!-- 对上面的resultMap做一个说明，注意id要和上面的result名对应 --> <resultMap type="model.Course" id="course"> <id property="id" column="cid"/> <result property="name" column="cname"/> <result property="type" column="ctype"/> <result property="hours" column="chours"/> <association property="teacher" javaType="model.Teacher"> <id property="id" column="tid"/> <result property="name" column="tname"/> <result property="sex" column="tsex"/> <result property="phone" column="sphone"/> </association> </resultMap> <delete id="delete" parameterType="int"> delete from course where id=#{id} </delete> <update id="update" parameterType="model.Course"> update course set name=#{name},type=#{type} where id=#{id} </update> </mapper>

5.在mybatis-config.xml文件中添加

<mapper class="mapper.CourseMapper"/> <mapper class="mapper.TeacherMapper"/>

（1）插入数据 一个老师：谭浩强，男，13901012345

两门课程 C语言入门教程，考查课，30 精通Java基础， 考试课，50

老师单独添加 添加课程的时候，把课程对应的老师添加进去

返回插入后生成的自增长属性值的办法

useGeneratedKeys="true" keyProperty="id" //插入一名老师和2门课程 @Test public void testInsert() throws IOException { SqlSession session=MybatisUtils.openSession(); TeacherMapper tMapper=session.getMapper(TeacherMapper.class); Teacher teacher=new Teacher(); teacher.setName("谭浩强"); teacher.setSex("男"); teacher.setPhone("13876548861"); tMapper.add(teacher); CourseMapper cMapper=session.getMapper(CourseMapper.class); Course course=new Course(); course.setName("C语言入门与提高"); course.setType("必修"); course.setHours("60"); course.setTeacher(teacher); cMapper.add(course); Course course2=new Course(); course2.setName("精通Java基础"); course2.setType("选修"); course2.setHours("30"); course2.setTeacher(teacher); cMapper.add(course2); session.commit(); session.close(); }

（2）查询数据 比较查询老师信息和课程信息的select语句 A如果查询老师信息，那么左连接时老师的表要放在左边，反之查询课程信息，课程表左连接时放在左边 B老师对应得课程是多个，所以在定义返回值resultMap的时候，课程要使用Collection标签，并且返回类型为javaType C课程对应的老师只有一个，所以在定义返回值resultMap的时候，老师使用的是association标签,并且返回类型为ofType //查询ID为15的老师信息和对应的课程信息，以及查询Id为12的课程信息及对应的老师信息

@Test public void testSelect() throws IOException { SqlSession session=MybatisUtils.openSession(); TeacherMapper tMapper=session.getMapper(TeacherMapper.class); Teacher teacher=tMapper.select(15); System.out.println(teacher); CourseMapper cMapper=session.getMapper(CourseMapper.class); Course course=cMapper.select(12); System.out.println(course); session.close(); }

六、多对多关系 学生 和 课程

一个学生可以选择多门课程:getCourses() 一门课程，也可以被多个学生选择:getStudents()

学生 Student(id,name,sex,pwd,phone,grade,photo)

课程 Course(id,name,type,hours)

数据库的设计：

中间表 student_course(sid,cid）

数据库表关系 sid cid 2 3 2 3(x) 2 4 13 4

sid和cid的组合不能重复

primary key , 复合主键,联合主键(composite key)

提取学生的时候，要把学生选择的所有课程也提取出来。(三个表的左连接查询，相对比较复杂)

left jion ：学生即使没有选择课程，也能把学生的信息提取出来。

from student,course where （=内连接:inner join） … 如果学生没有选择课程，学生的信息也提取不出来

1、新建student、course和Student_course数据表 Student_course表中的sid和cid要分别设置为student表和course表的外键 2、新建Student、Course和StudentCourse类

Student.java package model; import java.util.List; public class Student { private int id; private String name; private String sex; private String pwd; private String phone; private String grade; private String photo; //多对多的关系中一个学生对应多个课程 private List<Course> courses; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getGrade() { return grade; } public void setGrade(String grade) { this.grade = grade; } public String getPhoto() { return photo; } public void setPhoto(String photo) { this.photo = photo; } public List<Course> getCourses() { return courses; } public void setCourses(List<Course> courses) { this.courses = courses; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", pwd=" + pwd + ", phone=" + phone + ", grade=" + grade + ", photo=" + photo + ", courses=" + courses + "]"; } } Course.java package model; import java.util.List; public class Course { private int id; private String name; private String type; private String hours; //一个课程也对应多个 private List<Student> students; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getHours() { return hours; } public void setHours(String hours) { this.hours = hours; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } @Override public String toString() { return "Course [id=" + id + ", name=" + name + ", type=" + type + ", hours=" + hours + ", students=" + students + "]"; } } //建立StudentCourse类用来和student_course表对应 StudentCourse.java package model; public class StudentCourse { private Student student; private Course course; public Student getStudent() { return student; } public void setStudent(Student student) { this.student = student; } public Course getCourse() { return course; } public void setCourse(Course course) { this.course = course; } }

3、新建StudentMapper、CourseMapper和StudentCourseMapper接口

StudentMapper.java package mapper; import model.Student; public interface StudentMapper { public void add(Student student); public Student get(int id); public Student select(int id); } CourseMapper.java package mapper; import model.Course; public interface CourseMapper { public void add(Course course); public Course get(int id); public Course select(int id); } StudentCourseMapper.java package mapper; import model.StudentCourse; public interface StudentCourseMapper { public void setMap(StudentCourse studentCourse); }

4、新建StudentMapper.xml、CourseMapper.xml和StudentCourseMapper.xml StudentMapper.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="mapper.StudentMapper"> <insert id="add" parameterType="model.Student"> insert into student ( name, sex, pwd, phone, grade, photo ) values ( #{name}, #{sex}, #{pwd}, #{phone}, #{grade}, #{photo} ) </insert> <select id="get" parameterType="int" resultType="model.Student"> select * from student where id=#{id} </select> <select id="select" parameterType="int" resultMap="student"> select sid,sname,ssex,spwd,sphone,sgrade,sphoto, c.id cid, c.name cname, c.type ctype, c.hours chours from ( select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student s left join student_course sc on s.id=sc.sid )remp left join course c on sccid=c.id where sid=#{id} </select> <resultMap type="model.Student" id="student"> <id property="id" column="sid"/> <result property="name" column="sname"/> <result property="sex" column="ssex"/> <result property="pwd" column="spwd"/> <result property="phone" column="sphone"/> <result property="grade" column="sgrade"/> <result property="photo" column="sphoto"/> <collection property="courses" ofType="model.Course"> <id property="id" column="cid"/> <result property="name" column="cname"/> <result property="type" column="ctype"/> <result property="hours" column="chours"/> </collection> </resultMap> </mapper>

CourseMapper.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="mapper.CourseMapper"> <insert id="add" parameterType="model.Course"> insert into course ( name, type, hours ) values ( #{name}, #{type}, #{hours} ) </insert> <select id="get" parameterType="int" resultType="model.Course"> select * from course where id=#{id} </select> <select id="select" parameterType="int" resultMap="course"> select c.id cid, c.name cname, c.type ctype, c.hours chours, sid,sname,ssex,spwd,sphone,sgrade,sphoto from course c left join ( select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student_course sc left join student s on s.id=sc.sid )remp on c.id=sccid where c.id=#{id} </select> <resultMap type="model.Course" id="course"> <id property="id" column="cid"/> <result property="name" column="cname"/> <result property="type" column="ctype"/> <result property="hours" column="chours"/> <collection property="students" ofType="model.Student"> <id property="id" column="sid"/> <result property="name" column="sname"/> <result property="sex" column="ssex"/> <result property="pwd" column="spwd"/> <result property="phone" column="sphone"/> <result property="grade" column="sgrade"/> <result property="photo" column="sphoto"/> </collection> </resultMap> </mapper>

StudentCourseMapper.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="mapper.StudentCourseMapper"> <insert id="setMap" parameterType="model.StudentCourse"> insert into student_course (sid,cid) values (#{student.id},#{course.id}) </insert> </mapper>

5、在mybatis-config.xml文件中增加

<mapper class="mapper.StudentCourseMapper"/> <mapper class="mapper.StudentMapper"/> <mapper class="mapper.CourseMapper"/>

（1）增加数据 //插入2个学生和2门课程

@Test public void testInsert() throws IOException { SqlSession session=MybatisUtils.openSession(); StudentMapper sMapper=session.getMapper(StudentMapper.class); Student student1=new Student(); student1.setName("刘德华"); student1.setSex("男"); student1.setPwd("123456"); student1.setPhone("15201916370"); student1.setGrade("二年级"); sMapper.add(student1); Student student2=new Student(); student2.setName("朱丽倩"); student2.setSex("女"); student2.setPwd("123456"); student2.setPhone("13636709659"); student2.setGrade("一年级"); sMapper.add(student2); CourseMapper cMapper=session.getMapper(CourseMapper.class); Course course1=new Course(); course1.setName("C语言入门与提高"); course1.setType("必修"); course1.setHours("60"); cMapper.add(course1); Course course2=new Course(); course2.setName("精通Java基础"); course2.setType("选修"); course2.setHours("30"); cMapper.add(course2); session.commit(); session.close(); }

//插入中间表信息，把学生和课程对应起来

@Test public void testSetMap() throws IOException { SqlSession session=MybatisUtils.openSession(); StudentMapper sMapper=session.getMapper(StudentMapper.class); CourseMapper cMapper=session.getMapper(CourseMapper.class); StudentCourseMapper scMapper=session.getMapper(StudentCourseMapper.class); StudentCourse sc=new StudentCourse(); sc.setStudent(sMapper.get(9)); sc.setCourse(cMapper.get(16)); scMapper.setMap(sc);; StudentCourse sc1=new StudentCourse(); sc1.setStudent(sMapper.get(9)); sc1.setCourse(cMapper.get(17)); scMapper.setMap(sc1);; StudentCourse sc2=new StudentCourse(); sc2.setStudent(sMapper.get(10)); sc2.setCourse(cMapper.get(16)); scMapper.setMap(sc2);; StudentCourse sc3=new StudentCourse(); sc3.setStudent(sMapper.get(10)); sc3.setCourse(cMapper.get(17)); scMapper.setMap(sc3);; session.commit(); session.close(); }

（2）查询数据 //查询学生信息及对应的课程信息

@Test public void testSelectStudent() throws IOException { SqlSession session=MybatisUtils.openSession(); StudentMapper sMapper=session.getMapper(StudentMapper.class); //CourseMapper cMapper=session.getMapper(CourseMapper.class); //StudentCourseMapper scMapper=session.getMapper(StudentCourseMapper.class); Student student=sMapper.select(9); System.out.println(student); } //查询课程信息及对应的学生信息 @Test public void testSelectCourse() throws IOException { SqlSession session=MybatisUtils.openSession(); //StudentMapper sMapper=session.getMapper(StudentMapper.class); CourseMapper cMapper=session.getMapper(CourseMapper.class); //StudentCourseMapper scMapper=session.getMapper(StudentCourseMapper.class); Course course=cMapper.select(16); System.out.println(course); }

七、三表左连接的思路

(一) 提取学生信息的时候，能够把学生的所选课程都提取出来

A表-学生表(id,...) B表-学生课程表(sid,cid),C表（id,...）-课程表

（1）前两个表进行左连接，将连接后的结果表，再与后面一个表左连接

(A - B) - C

(2) 后两个表进行左连接，然后在让第一个表和前面连接后的结果表左连接

A - (B - C)

A - B 连接的结果

select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student s left join student_course sc on s.id=sc.sid

(A - B) - C

select sid,sname,ssex,spwd,sphone,sgrade,sphoto, c.id cid, c.name cname, c.type ctype, c.hours chours from ( select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student s left join student_course sc on s.id=sc.sid ) temp left join course c on temp.sccid=c.id <!—如果sccid在几个表中没有重复，那么可以把temp.省掉 --> where sid=#{id}

(二) 在提取课程信息的时候，能够把选择该课程的学生的信息也提取出来

A - (B - C)

课程表course - （学生课程表 student_course - 学生表student）

B - C 左连接的结果

select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student_course sc left join student s on sc.sid=s.id

A - (B - C)

select sid,sname,ssex,spwd,sphone,sgrade,sphoto, c.id cid, c.name cname, c.type ctype, c.hours chours from course c left join ( select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student_course sc left join student s on sc.sid=s.id ) temp on c.id=temp.sccid where c.id=#{id}

模型提升： 查询A对象的信息：A( AB B) 查询B对象的信息：B (BA A) AB（BA）外（键）表可以抽象为各种表之间的链接点 四表左连： 课程 - 学生 - 老师 之间的关系（四表）

course student student_course teacher

从前面课程-学生关系查询的SQL开始

A - (B - C)

select sid,sname,ssex,spwd,sphone,sgrade,sphoto, c.id cid, c.name cname, c.type ctype, c.hours chours from course c left join ( select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student_course sc left join student s on sc.sid=s.id ) temp on c.id=temp.sccid where c.id=#{id}

(A - B) - (C -D)

select cid,cname,ctype,chours, tid,tname,tsex,tphone, sid,sname,ssex,spwd,sphone,sgrade,sphoto from ( select c.id cid, c.name cname, c.type ctype, c.hours chours, t.id tid, t.name tname, t.sex tsex, t.phone tphone from course c left JOIN teacher t on c.id=t.id ) t1 left join ( select s.id sid, s.name sname, s.sex ssex, s.pwd spwd, s.phone sphone, s.grade sgrade, s.photo sphoto, sc.cid sccid from student_course sc left join student s on sc.sid=s.id ) t2 on t1.cid=t2.sccid where t1.cid=3