JavaPersistenceWithMyBatis3笔记-第3章SQL Mappers Using XMLs-001

一、

1.Mapper

2.Service

3.Domain

 1 package com.mybatis3.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.ArrayList;
 5 import java.util.Date;
 6 import java.util.List;
 7 
 8 /**
 9  * @author Siva
10  *
11  */
12 public class Course implements Serializable
13 {
14     private static final long serialVersionUID = 1L;
15     
16     private Integer courseId;
17     private String name;
18     private String description;
19     private Date startDate;
20     private Date endDate;
21     private Tutor tutor;
22     private List<Student> students;
23     
24     @Override
25     public String toString() {
26         return "Course [courseId=" + courseId + ", name=" + name + ", description="
27                 + description + ", startDate=" + startDate + ", endDate="
28                 + endDate + ", tutor=" + tutor + ", students=" + students + "]";
29     }
30     public Integer getCourseId()
31     {
32         return courseId;
33     }
34     public void setCourseId(Integer id)
35     {
36         this.courseId = id;
37     }
38     public String getName()
39     {
40         return name;
41     }
42     public void setName(String name)
43     {
44         this.name = name;
45     }
46     public String getDescription()
47     {
48         return description;
49     }
50     public void setDescription(String description)
51     {
52         this.description = description;
53     }
54     public Date getStartDate()
55     {
56         return startDate;
57     }
58     public void setStartDate(Date startDate)
59     {
60         this.startDate = startDate;
61     }
62     public Date getEndDate()
63     {
64         return endDate;
65     }
66     public void setEndDate(Date endDate)
67     {
68         this.endDate = endDate;
69     }
70     public List<Student> getStudents()
71     {
72         if(students == null){
73             students = new ArrayList<Student>(0);
74         }
75         return students;
76     }
77     public void setStudents(List<Student> students)
78     {
79         this.students = students;
80     }
81     public Tutor getTutor() {
82         return tutor;
83     }
84     public void setTutor(Tutor tutor) {
85         this.tutor = tutor;
86     }
87     
88 }

 

 1 package com.mybatis3.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 /**
 7  * @author Siva
 8  *
 9  */
10 public class Tutor implements Serializable
11 {
12     private static final long serialVersionUID = 1L;
13     
14     private Integer tutorId;
15     private String name;
16     private String email;
17     private Address address;
18     private List<Course> courses;
19     
20     @Override
21     public String toString() {
22         return "Tutor [tutorId=" + tutorId + ", name=" + name + ", email=" + email
23                 + ", address=" + address + ", courses=" + courses + "]";
24     }
25     public Tutor()
26     {
27     }
28     public Tutor(Integer id)
29     {
30         this.tutorId = id;
31     }
32     public Integer getTutorId()
33     {
34         return tutorId;
35     }
36     public void setTutorId(Integer id)
37     {
38         this.tutorId = id;
39     }
40     public String getName()
41     {
42         return name;
43     }
44     public void setName(String name)
45     {
46         this.name = name;
47     }
48     public String getEmail()
49     {
50         return email;
51     }
52     public void setEmail(String email)
53     {
54         this.email = email;
55     }
56     public Address getAddress()
57     {
58         return address;
59     }
60     public void setAddress(Address address)
61     {
62         this.address = address;
63     }
64     public List<Course> getCourses() {
65         return courses;
66     }
67     public void setCourses(List<Course> courses) {
68         this.courses = courses;
69     }
70     
71 }

 

4.辅助类

5.配置及资源文件

(1)AddressMapper.xml

 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   
 6 <mapper namespace="com.mybatis3.mappers.AddressMapper">
 7     
 8       <resultMap type="Address" id="AddressResult">
 9           <id property="addrId" column="addr_id"/>
10         <result property="street" column="street"/>
11         <result property="city" column="city"/>
12         <result property="state" column="state"/>
13         <result property="zip" column="zip"/>
14         <result property="country" column="country"/>
15       </resultMap>
16       
17       <select id="selectAddressById" parameterType="int" resultMap="AddressResult">
18           select * from addresses where addr_id=#{addrId}
19       </select>
20       
21 </mapper>

 

(2)CourseMapper.xml

 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   
 6 <mapper namespace="com.mybatis3.mappers.CourseMapper">
 7     
 8     <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="false"/>
 9     
10       <resultMap type="Course" id="CourseResult">
11           <id     column="course_id" property="courseId"/>
12           <result column="name" property="name"/>
13           <result column="description" property="description"/>
14           <result column="start_date" property="startDate"/>
15           <result column="end_date" property="endDate"/>
16       </resultMap>
17       
18       <select id="selectCoursesByTutor" parameterType="int" resultMap="CourseResult">
19           select * from courses where tutor_id=#{tutorId}
20       </select>
21       
22       <select id="searchCourses" parameterType="hashmap" resultMap="CourseResult" useCache="false">
23           SELECT * FROM COURSES
24           WHERE TUTOR_ID= #{tutorId}
25           <if test="courseName != null">
26               AND name like #{courseName}
27           </if>
28           <if test="startDate != null">
29               AND start_date  &gt;= #{startDate}
30           </if>
31           <if test="endDate != null">
32               AND end_date  &lt;= #{endDate}
33           </if>
34           
35       </select>
36       
37       <select id="searchCoursesByTutors" parameterType="hashmap" resultMap="CourseResult">
38           SELECT * FROM COURSES
39           <if test="tutorIds != null">
40           <where>
41           tutor_id IN
42           <foreach item="tutorId" collection="tutorIds"
43           open="(" separator="," close=")">
44             #{tutorId}
45           </foreach>
46            </where>          
47            </if>
48       </select>
49       
50 </mapper>

 

(3)StudentMapper.xml

 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   
 6 <mapper namespace="com.mybatis3.mappers.StudentMapper">
 7     
 8     <resultMap type="Student" id="StudentResult">
 9         <id     property="studId" column="stud_id"/>
10         <result property="name" column="name" />
11         <result property="email" column="email"/>
12         <result property="phone" column="phone"/>
13     </resultMap>
14       
15       <resultMap type="Student" id="StudentWithAddressExtResult" extends="StudentResult">
16         <result property="address.addrId" column="addr_id"/>
17         <result property="address.street" column="street"/>
18         <result property="address.city" column="city"/>
19         <result property="address.state" column="state"/>
20         <result property="address.zip" column="zip"/>
21         <result property="address.country" column="country"/>
22     </resultMap>
23     
24       <resultMap type="Student" id="StudentWithAddressNestedSelect">
25         <id     property="studId" column="stud_id"/>
26         <result property="name" column="name"/>
27         <result property="email" column="email"/>
28         <association property="address" column="addr_id" select="com.mybatis3.mappers.AddressMapper.selectAddressById"/>
29     </resultMap>
30     
31     <resultMap type="Student" id="StudentWithAddressNestedResultMap">
32         <id     property="studId" column="stud_id"/>
33         <result property="name" column="name"/>
34         <result property="email" column="email"/>
35         <association property="address" javaType="Address">
36             <id property="addrId" column="addr_id"/>
37             <result property="street" column="street"/>
38             <result property="city" column="city"/>
39             <result property="state" column="state"/>
40             <result property="zip" column="zip"/>
41             <result property="country" column="country"/>
42         </association>
43     </resultMap>
44     
45     <select id="findAllStudents" resultMap="StudentResult">
46         select * from Students
47       </select>
48       
49       <select id="findStudentById" parameterType="int" resultMap="StudentWithAddressNestedSelect">
50         select * from Students where stud_id=#{studId}
51       </select>
52                   
53       <select id="selectStudentWithAddress" parameterType="int" resultMap="StudentWithAddressNestedResultMap">
54           select stud_id, name, email,phone, a.addr_id, street, city, state, zip, country
55           FROM students s left outer join addresses a on s.addr_id=a.addr_id
56         where stud_id=#{studId}
57       </select>
58       
59       <insert id="insertStudent" parameterType="Student" useGeneratedKeys="true" keyProperty="studId">
60           insert into students(name,email,addr_id, phone)
61           values(#{name},#{email},#{address.addrId},#{phone})
62       </insert>
63       
64       <insert id="insertStudentWithMap" parameterType="hashmap" useGeneratedKeys="true" keyProperty="studId">
65           insert into students(name,email,addr_id,phone)
66           values(#{name},#{email},#{address.addrId},#{phone})
67       </insert>
68       
69       <update id="updateStudent" parameterType="Student">
70           update students 
71           <!-- set 
72           name=#{name}, 
73           email=#{email}, 
74           phone=#{phone}
75           where stud_id=#{studId} -->
76           
77           <set>
78               <if test="name != null">name=#{name},</if>
79               <if test="email != null">email=#{email},</if>
80               <if test="phone != null">phone=#{phone},</if>
81           </set>
82           where stud_id=#{studId}
83       </update>
84       
85       <delete id="deleteStudent" parameterType="int">
86           delete from students where stud_id=#{studId}
87       </delete>
88       
89 </mapper>

 

(4)TutorMapper.xml

 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   
 6 <mapper namespace="com.mybatis3.mappers.TutorMapper">
 7     
 8     
 9       <resultMap type="Tutor" id="TutorWithCoursesNestedResult">
10           <id     column="tutor_id" property="tutorId"/>
11           <result column="tutor_name" property="name"/>
12           <result column="email" property="email"/>
13           <association property="address" resultMap="com.mybatis3.mappers.AddressMapper.AddressResult"/>
14           <collection property="courses"  resultMap="com.mybatis3.mappers.CourseMapper.CourseResult"  />
15       </resultMap>
16       
17       <resultMap type="Tutor" id="TutorWithCoursesNestedSelect">
18           <id     column="tutor_id" property="tutorId"/>
19           <result column="tutor_name" property="name"/>
20           <result column="email" property="email"/>
21           <association property="address" resultMap="com.mybatis3.mappers.AddressMapper.AddressResult"/>
22           <collection property="courses"  column="tutor_id" select="com.mybatis3.mappers.CourseMapper.selectCoursesByTutor"/>
23       </resultMap>
24       
25       <select id="selectTutorById" parameterType="int" resultMap="TutorWithCoursesNestedResult">
26           SELECT t.tutor_id, t.name as tutor_name, email, a.addr_id, street, city, state, zip, country,
27                    course_id, c.name, description, start_date, end_date
28         FROM tutors t left outer join addresses a on t.addr_id=a.addr_id
29           left outer join courses c on t.tutor_id=c.tutor_id
30         where t.tutor_id=#{tutorId}
31       </select>
32       
33       <select id="selectTutorWithCourses" parameterType="int" resultMap="TutorWithCoursesNestedSelect">
34           SELECT t.tutor_id, t.name as tutor_name, email, a.addr_id, street, city, state, zip, country
35         FROM tutors t left outer join addresses a on t.addr_id=a.addr_id
36         where t.tutor_id=#{tutorId}
37       </select>
38       
39 </mapper>

 

(5)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="application.properties" />
 8 
 9     <typeAliases>
10         <package name="com.mybatis3.domain" />
11     </typeAliases>
12 
13     <typeHandlers>
14         <typeHandler handler="com.mybatis3.typehandlers.PhoneTypeHandler" />
15     </typeHandlers>
16 
17     <environments default="development">
18         <environment id="development">
19             <transactionManager type="JDBC" />
20             <dataSource type="POOLED">
21                 <property name="driver" value="${jdbc.driverClassName}" />
22                 <property name="url" value="${jdbc.url}" />
23                 <property name="username" value="${jdbc.username}" />
24                 <property name="password" value="${jdbc.password}" />
25             </dataSource>
26         </environment>
27 
28     </environments>
29 
30     <mappers>
31         <package name="com.mybatis3.mappers"/>
32     </mappers>
33 
34 </configuration>

 

6.测试文件

posted @ 2016-04-28 10:33  shamgod  阅读(179)  评论(0)  编辑  收藏  举报
haha