导包,
配置mybatis的总配置文件: mybatis-config.xml,
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE configuration
3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-config.dtd">
5 <configuration>
6 <!-- 引入数据库的信息的属性文件 -->
7 <properties resource="db.properties"></properties>
8
9 <typeAliases>
10 <package name="model"/>
11 </typeAliases>
12
13 <environments default="hanqi">
14 <environment id="hanqi">
15 <!--
16 JDBC:
17 MANAGED:托管
18 -->
19 <transactionManager type="JDBC" />
20 <!--
21 配置数据库源
22 POOLED: 连接池
23 UNPOOLED: 非连接池
24 JNDI: 使用应用服务器上的数据库连接
25 -->
26 <dataSource type="POOLED">
27 <property name="username" value="${jdbc.username}"/>
28 <property name="password" value="${jdbc.password}"/>
29 <property name="url" value="${jdbc.url}"/>
30 <property name="driver" value="${jdbc.driver}"/>
31 </dataSource>
32 </environment>
33
34 </environments>
35
36 <mappers>
37 <!--
38 <mapper resource="mapper/StudentMapper.xml" />
39 <mapper class=""></mapper>
40 <mapper url="f:/test/StudentMapper.xml"></mapper>
41 -->
42 <!--
43 如果有多个,用包名,自动在包下面搜索
44 -->
45 <package name="com.hanqi.maya.mapper" />
46 </mappers>
47
48 </configuration>
新建每个实体类的接口和映射文件,并在xml映射文件中引入接口
1 package mapper;
2
3 import java.util.List;
4 import java.util.Map;
5
6 import model.Student;
7
8 public interface StudentMapper {
9 public List<Student> selectAllStudent();
10
11 public int insertStudent(Student s);
12
13 public int updateStudent(Map m);
14
15 public int deleteStudent(Map m);
16
17 }
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="mapper.StudentMapper">
6
7 <select id="selectAllStudent" resultType="Student">
8 select * from student
9 </select>
10
11 <insert id="insertStudent" parameterType="Student" useGeneratedKeys="true" keyProperty="sno" keyColumn="SNO">
12 insert into student values(test1.nextval, #{name}, #{sex}, #{sbirthday}, #{sclass})
13 </insert>
14
15 <update id="updateStudent" parameterType="Map">
16 update student s set s.sname=#{stuname},s.sbirthday=#{newDate}
17 where s.sno=#{stusno}
18 </update>
19
20 <delete id="deleteStudent" parameterType="Map">
21 delete student s where s.sno=#{stusno}
22 </delete>
23
24
25 </mapper>
namespace中如果是ID,可以随便写,需要唯一,如果是引入接口,需要接口和本文件名一致。
测试:
1 package test;
2
3 import static org.junit.Assert.*;
4
5 import java.util.Date;
6 import java.util.HashMap;
7 import java.util.List;
8 import java.util.Map;
9
10 import org.apache.ibatis.session.SqlSession;
11 import org.junit.After;
12 import org.junit.Before;
13 import org.junit.Test;
14 import mapper.StudentMapper;
15 import model.Student;
16 import util.MyBatisUtil;
17
18 public class JUTest {
19
20 private SqlSession ss;
21 //定义接口类,拿到接口
22 private StudentMapper sm;
23
24 @Before
25 public void setUp() throws Exception {
26 ss=MyBatisUtil.getSession();
27 sm=ss.getMapper(StudentMapper.class);
28 }
29
30 @After
31 public void tearDown() throws Exception {
32 MyBatisUtil.destory(ss);
33 }
34
35 @Test
36 public void test() {
37 List<Student> slist=sm.selectAllStudent();
38
39 /*Student s=new Student(null,"name","sex",new Date(),95053);
40 int a=sm.insertStudent(s);
41 System.out.println(a);*/
42
43 /*Map m=new HashMap();
44 m.put("stusno", "101");
45 m.put("stuname", "修改1");
46 m.put("newDate",new Date());
47 int a=sm.updateStudent(m);
48 System.out.println(a);*/
49
50 Map m1=new HashMap();
51 m1.put("stusno", "s");
52 int b=sm.deleteStudent(m1);
53
54 for(Student s:slist){
55 System.out.println(s);
56 }
57
58
59 }
60
61 }
关联查询:
一对一查询(三种方式),
一对多查询
部门表P_Dept和员工表P_Emp
员工表中有部门编号
员工类中有部门属性
部门类中有员工集合属性
通过联合查询,查询员工时得到员工属性和部门,查询部门时通过联合查询得出员工列表
结构:
model包:
1 packa1ge model;
2
3 import java.util.Date;
4
5 public class Emp {
6 private int empno;
7 private String ename;
8 private String job;
9 private int mgr;
10 private Date hiredate;
11 private int sal;
12 private int comm;
13 private int deptno;
14 private int sex;
15 private Dept dept;
16 public Emp() {
17 super();
18 // TODO Auto-generated constructor stub
19 }
20 public Emp(int empno, String ename, String job, int mgr, Date hiredate, int sal, int comm, int deptno, int sex,
21 Dept dept) {
22 super();
23 this.empno = empno;
24 this.ename = ename;
25 this.job = job;
26 this.mgr = mgr;
27 this.hiredate = hiredate;
28 this.sal = sal;
29 this.comm = comm;
30 this.deptno = deptno;
31 this.sex = sex;
32 this.dept = dept;
33 }
34 public int getEmpno() {
35 return empno;
36 }
37 public void setEmpno(int empno) {
38 this.empno = empno;
39 }
40 public String getEname() {
41 return ename;
42 }
43 public void setEname(String ename) {
44 this.ename = ename;
45 }
46 public String getJob() {
47 return job;
48 }
49 public void setJob(String job) {
50 this.job = job;
51 }
52 public int getMgr() {
53 return mgr;
54 }
55 public void setMgr(int mgr) {
56 this.mgr = mgr;
57 }
58 public Date getHiredate() {
59 return hiredate;
60 }
61 public void setHiredate(Date hiredate) {
62 this.hiredate = hiredate;
63 }
64 public int getSal() {
65 return sal;
66 }
67 public void setSal(int sal) {
68 this.sal = sal;
69 }
70 public int getComm() {
71 return comm;
72 }
73 public void setComm(int comm) {
74 this.comm = comm;
75 }
76 public int getDeptno() {
77 return deptno;
78 }
79 public void setDeptno(int deptno) {
80 this.deptno = deptno;
81 }
82 public int getSex() {
83 return sex;
84 }
85 public void setSex(int sex) {
86 this.sex = sex;
87 }
88 public Dept getDept() {
89 return dept;
90 }
91 public void setDept(Dept dept) {
92 this.dept = dept;
93 }
94 @Override
95 public String toString() {
96 return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
97 + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + ", sex=" + sex + ", dept=" + dept + "]";
98 }
99
100
101
102 }
1 package model;
2
3 import java.util.List;
4
5 public class Dept {
6 private int deptno;
7 private String dname;
8 private String loc;
9 private List<Emp> elist;
10 public Dept() {
11 super();
12 // TODO Auto-generated constructor stub
13 }
14 public Dept(int deptno, String dname, String loc) {
15 super();
16 this.deptno = deptno;
17 this.dname = dname;
18 this.loc = loc;
19 }
20
21 public Dept(int deptno, String dname, String loc, List<Emp> elist) {
22 super();
23 this.deptno = deptno;
24 this.dname = dname;
25 this.loc = loc;
26 this.elist = elist;
27 }
28
29
30 public List<Emp> getElist() {
31 return elist;
32 }
33 public void setElist(List<Emp> elist) {
34 this.elist = elist;
35 }
36 public int getDeptno() {
37 return deptno;
38 }
39 public void setDeptno(int deptno) {
40 this.deptno = deptno;
41 }
42 public String getDname() {
43 return dname;
44 }
45 public void setDname(String dname) {
46 this.dname = dname;
47 }
48 public String getLoc() {
49 return loc;
50 }
51 public void setLoc(String loc) {
52 this.loc = loc;
53 }
54 @Override
55 public String toString() {
56 return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + ", elist=" + elist + "]";
57 }
58
59
60 }
mapper包:
1 package mapper;
2
3 import java.util.List;
4
5 import model.Dept;
6 import model.Emp;
7
8 public interface DeptMapper {
9 Dept selectDeptByDeptno(Integer id);
10 }
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="mapper.DeptMapper">
6
7 <resultMap type="Dept" id="abc">
8 <collection property="elist" column="deptno"
9 select="mapper.EmpMapper.selectEmpByDeptno" />
10 </resultMap>
11
12 <select id="selectDeptByDeptno" parameterType="Integer" resultMap="abc">
13 select * from p_dept d where d.deptno=#{deptno}
14 </select>
15
16 </mapper>
1 package mapper;
2
3 import java.util.List;
4
5 import model.Emp;
6
7 public interface EmpMapper {
8 //查询所有信息
9 List<Emp> selectAllEmp();
10 // 根据部门id查询员工
11 List<Emp> selectEmpByDeptno(Integer deptno);
12 }
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <mapper namespace="mapper.EmpMapper">
6
7 <!-- 对象及联 -->
8 <resultMap type="Emp" id="empList">
9 <id property="empno" column="empno" />
10 <result property="ename" column="ename" />
11 <result property="job" column="job" />
12 <result property="mgr" column="mgr" />
13 <result property="hiredate" column="hiredate" />
14 <result property="sal" column="sal" />
15 <result property="comm" column="comm" />
16 <result property="deptno" column="deptno" />
17 <result property="sex" column="sex"/>
18 <result property="dept.deptno" column="deptno"/>
19 <result property="dept.dname" column="dname"/>
20 <result property="dept.loc" column="loc"/>
21 </resultMap>
22
23 <select id="selectAllEmp" resultMap="empList">
24 select * from p_Emp e left join p_Dept d on d.deptno=e.deptno
25
26 </select>
27
28 <!-- 关联 -->
29 <!-- <resultMap type="Emp" id="empList1">
30 <id property="empno" column="empno" />
31 <result property="ename" column="ename" />
32 <result property="job" column="job" />
33 <result property="mgr" column="mgr" />
34 <result property="hiredate" column="hiredate" />
35 <result property="sal" column="sal" />
36 <result property="comm" column="comm" />
37 <result property="deptno" column="deptno" />
38 <result property="sex" column="sex"/>
39 <association property="dept" resultMap="deptlist"></association>
40 </resultMap>
41 <resultMap type="Dept" id="deptlist">
42 <result property="deptno" column="deptno"/>
43 <result property="dname" column="dname"/>
44 <result property="loc" column="loc"/>
45 </resultMap>
46
47 <select id="selectAllEmp" resultMap="empList1">
48 select * from p_Emp e left join p_Dept d on d.deptno=e.deptno
49 </select> -->
50 <!-- 关联查询 -->
51 <!-- <resultMap type="Emp" id="empList3">
52 <association property="dept" column="deptno"
53 select="mapper.DeptMapper.selectDeptById" />
54 </resultMap>
55
56 <select id="selectAllEmp" resultMap="empList3">
57 select * from p_EMP t
58 </select> -->
59 <!-- 一对多 -->
60 <select id="selectEmpByDeptno" parameterType="Integer" resultType="Emp">
61 SELECT * FROM p_emp e WHERE e.deptno=#{deptno}
62 </select>
63
64 </mapper>
测试:
1 package test;
2
3 import static org.junit.Assert.*;
4
5 import java.util.List;
6
7 import org.apache.ibatis.session.SqlSession;
8 import org.junit.After;
9 import org.junit.Before;
10 import org.junit.Test;
11
12 import mapper.DeptMapper;
13 import mapper.EmpMapper;
14 import model.Dept;
15 import model.Emp;
16 import util.MyBatisUtil;
17
18 public class JUTest {
19 private SqlSession ss;
20 private EmpMapper em;
21 private DeptMapper dm;
22
23 @Before
24 public void setUp() throws Exception {
25 ss = MyBatisUtil.getSession();
26 em = ss.getMapper(EmpMapper.class);
27 dm = ss.getMapper(DeptMapper.class);
28 }
29
30 @After
31 public void tearDown() throws Exception {
32 MyBatisUtil.destory(ss);
33 }
34
35 @Test
36 public void test() {
37 /*List<Emp> slist=em.selectAllEmp();
38 for(Emp e:slist){
39 System.out.println(e);
40 }*/
41
42 /*List<Emp> elist = em.selectEmpByDeptno(10);
43 System.out.println(elist);*/
44
45 Dept d = dm.selectDeptByDeptno(10);
46 System.out.println(d);
47 }
48
49 }