Mybatis(三)

回顾SQL99中的连接查询 

1）内连接（等值连接）：查询客户姓名，订单编号，订单价格

   ---------------------------------------------------

    selectc.name,o.isbn,o.price

    from customers c innerjoin orders o

    where c.id =o.customers_id;

   ---------------------------------------------------

    selectc.name,o.isbn,o.price

    from customers c joinorders o

    where c.id =o.customers_id; 

   ---------------------------------------------------

    selectc.name,o.isbn,o.price

    from customers c,orderso

    where c.id =o.customers_id;

   ---------------------------------------------------

    selectc.name,o.isbn,o.price

    from customers c joinorders o

    on c.id =o.customers_id;

   ---------------------------------------------------

   注意：内连接（等值连接）只能查询出多张表中，连接字段相同的记录

2）外连接：按客户分组，查询每个客户的姓名和订单数

   ---------------------------------------------------

    左外连接：

    selectc.name,count(o.isbn)

    from customers c left outer join orders o

    on c.id =o.customers_id

    group byc.name; 

   ---------------------------------------------------

    右外连接：

    selectc.name,count(o.isbn)

    from orders o right outer join customers c  

    on c.id =o.customers_id

    group byc.name; 

   ---------------------------------------------------

   注意：外连接既能查询出多张表中，连接字段相同的记录；又能根据一方，将另一方不符合相同记录强行查询出来

3）自连接：求出AA的老板是EE

   ---------------------------------------------------

    内自连接：

    selectusers.ename,boss.ename

    from emps users innerjoin emps boss 

    on users.mgr =boss.empno;

   ---------------------------------------------------

    外自连接：

    selectusers.ename,boss.ename

    from emps users leftouter join emps boss 

    on users.mgr =boss.empno;

   ---------------------------------------------------

   注意：自连接是将一张表，通过别名的方式，看作多张表后，再进行连接。

 这时的连接即可以采用内连接，又可以采用外连接

 mybatis一对一映射【学生与身份证】

1）创建students.sql和cards.sql 

drop table students;

drop table cards;

create table cards(

id int(5) primary key,

num varchar(20)

);

create table students(

id int(5) primary key,

name varchar(10),

cid int(5),

constraint cid_fk foreign key(cid) references cards(id)

);

insert into cards(id,num) values(1,'111');

insert into students(id,name,cid) values(1,'哈哈',1);

2）创建Students.java和Card.java 

public class Card {

private Integer id;

private String num;

private Student student;

public Card(){}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getNum() {

return num;

}

public void setNum(String num) {

this.num = num;

}

public Student getStudent() {

return student;

}

public void setStudent(Student student) {

this.student = student;

}

}

public class Student {

private Integer id;

private String name;

private Card card;

public Student(){}

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 Card getCard() {

return card;

}

public void setCard(Card card) {

this.card = card;

}

}

3）创建StudentMapper.xml和CardMapper.xml 

   CardMapper.xml

《?xml version="1.0" encoding="UTF-8" ?》

《!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd"》

《mapper namespace="cardNamespace"》

《resultMap type="cn.itcast.javaee.mybatis.one2one.Card"id="cardMap"》

《id property="id" column="id" /》

《result property="num" column="num" /》

《/resultMap》

《/mapper》

   StudentMapper.xml

《?xml version="1.0" encoding="UTF-8" ?》

《!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd"》

《mapper namespace="studentNamespace"》

《resultMap type="cn.itcast.javaee.mybatis.one2one.Student"id="studentMap"》

《id property="id" column="id" /》

《result property="name" column="name"/》

《association property="card"resultMap="cardNamespace.cardMap"/》

《/resultMap》

《select id="findById" parameterType="int"resultMap="studentMap"》

select s.id,s.name,c.id,c.num

from students s inner join cards c

on s.cid = c.id 

and s.id = #{id}

《/select》

《/mapper》

4）创建StudentCardDao.java 

public class StudentCardDao {

public Student findById(int id) throws Exception{

SqlSession sqlSession = null;

try{

sqlSession = MybatisUtil.getSqlSession();

returnsqlSession.selectOne("studentNamespace.findById",id);

}catch(Exception e){

e.printStackTrace();

throw e;

}finally{

MybatisUtil.closeSqlSession();

}

}

public static void main(String[] args) throws Exception{

StudentCardDao dao = new StudentCardDao();

Student student = dao.findById(1);

System.out.println(student.getId()+":"+student.getName());

System.out.println(student.getCard().getId()+":"+student.getCard().getNum());

}

}

mybatis一对多映射【班级与学生】

1）创建grades.sql和students.sql

drop table students;

drop table grades;

create table grades(

gid int(5) primary key,

gname varchar(10)

 );

create table students(

sid int(5) primary key,

sname varchar(10),

    sgid int(5),

    constraint sgid_fkforeign key(sgid) references grades(gid)

);

insert into grades(gid,gname) values(1,'java');

insert into students(sid,sname,sgid) values(1,'哈哈',1);

insert into students(sid,sname,sgid) values(2,'呵呵',1);

2）创建Grade.java和Student.sql

public class Grade {

private Integer id;

private String name;

private List《Student》 studentList = newArrayList《Student》();

public Grade(){}

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》 getStudentList() {

return studentList;

}

public void setStudentList(List《Student》 studentList) {

this.studentList = studentList;

}

}

public class Student {

private Integer id;

private String name;

private Grade grade;

public Student(){}

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 Grade getGrade() {

return grade;

}

public void setGrade(Grade grade) {

this.grade = grade;

}

}

3）创建GradeMapper.xml和StudentMapper.xml

   GradeMapper.xml

《?xml version="1.0" encoding="UTF-8" ?》

《!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd"》

《mapper namespace="gradeNamespace"》

《resultMap type="cn.itcast.javaee.mybatis.one2many.Grade"id="gradeMap"》

《id property="id" column="gid" /》

《result property="name" column="gname"/》

《collection property="studentList"resultMap="studentNamespace.studentMap"/》

《/resultMap》

《select id="findGradeByName" parameterType="string"resultMap="gradeMap"》

select g.gid,g.gname,s.sid,s.sname

from grades g,students s

where g.gid = s.sgid

and s.sname = #{name}

《/select》

《/mapper》

   StudentMapper.xml

《?xml version="1.0" encoding="UTF-8" ?》

《!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd"》

《mapper namespace="studentNamespace"》

《resultMap type="cn.itcast.javaee.mybatis.one2many.Student"id="studentMap"》

《id property="id" column="sid" /》

《result property="name" column="sname"/》

《association property="grade"resultMap="gradeNamespace.gradeMap"/》

《/resultMap》

《select id="findAllByName" parameterType="string"resultMap="studentMap"》

select s.sid,s.sname,g.gid,g.gname

from grades g,students s

where g.gid = s.sgid

and g.gname = #{name}

《/select》

《/mapper》

4）创建GradeStudentDao.java

public class GradeStudentDao {

public List《Student》 findAllByName(String name) throwsException{

SqlSession sqlSession = null;

try{

sqlSession = MybatisUtil.getSqlSession();

returnsqlSession.selectList("studentNamespace.findAllByName",name);

}catch(Exception e){

e.printStackTrace();

throw e;

}finally{

MybatisUtil.closeSqlSession();

}

}

public Grade findGradeByName(String name) throwsException{

SqlSession sqlSession = null;

try{

sqlSession = MybatisUtil.getSqlSession();

returnsqlSession.selectOne("gradeNamespace.findGradeByName",name);

}catch(Exception e){

e.printStackTrace();

throw e;

}finally{

MybatisUtil.closeSqlSession();

}

}

public static void main(String[] args) throws Exception{

GradeStudentDao dao = new GradeStudentDao();

List《Student》 studentList = dao.findAllByName("java");

for(Student s  : studentList){

System.out.println(s.getId()+":"+s.getName()+":"+s.getGrade().getId()+":"+s.getGrade().getName());

}

Grade grade = dao.findGradeByName("哈哈");

System.out.println(grade.getId()+":"+grade.getName());

}

}

mybatis多对多映射【学生与课程】

1）创建students.sql和courses.sql和middles.sql

drop table middles;

drop table students;

drop table courses;

create table students(

sid int(5) primary key,

sname varchar(10)

);

create table courses(

cid int(5) primary key,

cname varchar(10)

);

create table middles(

sid int(5),

cid int(5),

primary key(sid,cid)

);

insert into students(sid,sname) values(1,'哈哈');

insert into students(sid,sname) values(2,'呵呵');

insert into courses(cid,cname) values(1,'java');

insert into courses(cid,cname) values(2,'net');

insert into middles(sid,cid) values(1,1);

insert into middles(sid,cid) values(1,2);

insert into middles(sid,cid) values(2,1);

insert into middles(sid,cid) values(2,2);

select * from students;

select * from courses;

select * from middles;

2）创建Student.java和Course.java

public class Student {

private Integer id;

private String name;

private List《Course》 courseList = newArrayList《Course》();

public Student(){}

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《Course》 getCourseList() {

return courseList;

}

public void setCourseList(List《Course》 courseList) {

this.courseList = courseList;

}

}

public class Course {

private Integer id;

private String name;

private List《Student》 studentList = newArrayList《Student》();

public Course(){}

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》 getStudentList() {

return studentList;

}

public void setStudentList(List《Student》 studentList) {

this.studentList = studentList;

}

}

3）创建StudentMapper.xml和CourseMapper.xml

   StudentMapper.xml

《?xml version="1.0" encoding="UTF-8" ?》

《!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd"》

《mapper namespace="studentNamespace"》

《resultMap type="cn.itcast.javaee.mybatis.many2many.Student"id="studentMap"》

《id property="id" column="sid" /》

《result property="name" column="sname"/》

《/resultMap》

《select id="findStudentByName" parameterType="string"resultMap="studentMap"》

select s.sid,s.sname

from students s,middles m,courses c

where s.sid = m.sid 

and m.cid = c.cid

and c.cname = #{name}

《/select》

《/mapper》

   CourseMapper.xml

《?xml version="1.0" encoding="UTF-8" ?》

《!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd"》

《mapper namespace="courseNamespace"》

《resultMap type="cn.itcast.javaee.mybatis.many2many.Course"id="courseMap"》

《id property="id" column="cid" /》

《result property="name" column="cname"/》

《/resultMap》

《select id="findCourseByName" parameterType="string"resultMap="courseMap"》

select c.cid,c.cname

from students s,middles m,courses c

where s.sid = m.sid 

and m.cid = c.cid

and s.sname = #{name}

《/select》

《/mapper》

4）创建students.sql和courses.sql和middles.sql

public class StudentCourseDao {

public List《Course》 findCourseByName(String name) throwsException{

SqlSession sqlSession = null;

try{

sqlSession = MybatisUtil.getSqlSession();

returnsqlSession.selectList("courseNamespace.findCourseByName",name);

}catch(Exception e){

e.printStackTrace();

throw e;

}finally{

MybatisUtil.closeSqlSession();

}

}

public List《Student》 findStudentByName(String name) throwsException{

SqlSession sqlSession = null;

try{

sqlSession = MybatisUtil.getSqlSession();

returnsqlSession.selectList("studentNamespace.findStudentByName",name);

}catch(Exception e){

e.printStackTrace();

throw e;

}finally{

MybatisUtil.closeSqlSession();

}

}

public static void main(String[] args) throws Exception{

StudentCourseDao dao = new StudentCourseDao();

//List《Course》 courseList = dao.findCourseByName("哈哈");

//for(Course c : courseList){

// System.out.println(c.getId()+":"+c.getName());

//}

List《Student》 studentList =dao.findStudentByName("java");

for(Student s : studentList){

System.out.println(s.getId()+":"+s.getName());

}

}  

}