一、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