该工程采用spring+mybatis的关联映射，动态sql，sql片段实现
1、搭建项目结构如下
2、配置项目的pom.xml文件中的依赖
1 <?xml version="1.0" encoding="UTF-8"?>
2
3 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5 <modelVersion>4.0.0</modelVersion>
6
7 <groupId>com</groupId>
8 <artifactId>springmybatis_day44_03</artifactId>
9 <version>1.0-SNAPSHOT</version>
10 <packaging>war</packaging>
11
12 <name>springmybatis_day44_03 Maven Webapp</name>
13 <!-- FIXME change it to the project's website -->
14 <url>http://www.example.com</url>
15
16 <properties>
17 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
18 <maven.compiler.source>1.7</maven.compiler.source>
19 <maven.compiler.target>1.7</maven.compiler.target>
20 </properties>
21
22
23 <dependencies>
24
25 <dependency>
26 <groupId>junit</groupId>
27 <artifactId>junit</artifactId>
28 <version>4.11</version>
29 </dependency>
30
31 <!--1 https://mvnrepository.com/artifact/org.springframework/spring-beans -->
32 <dependency>
33 <groupId>org.springframework</groupId>
34 <artifactId>spring-beans</artifactId>
35 <version>4.3.6.RELEASE</version>
36 </dependency>
37
38
39 <!--2 https://mvnrepository.com/artifact/org.springframework/spring-context -->
40 <dependency>
41 <groupId>org.springframework</groupId>
42 <artifactId>spring-context</artifactId>
43 <version>4.3.6.RELEASE</version>
44 </dependency>
45
46
47 <!-- 3https://mvnrepository.com/artifact/org.springframework/spring-core -->
48 <dependency>
49 <groupId>org.springframework</groupId>
50 <artifactId>spring-core</artifactId>
51 <version>4.3.6.RELEASE</version>
52 </dependency>
53
54
55 <!--4 https://mvnrepository.com/artifact/org.springframework/spring-expression -->
56 <dependency>
57 <groupId>org.springframework</groupId>
58 <artifactId>spring-expression</artifactId>
59 <version>4.3.6.RELEASE</version>
60 </dependency>
61
62
63 <!--5 https://mvnrepository.com/artifact/org.springframework/spring-aop -->
64 <dependency>
65 <groupId>org.springframework</groupId>
66 <artifactId>spring-aop</artifactId>
67 <version>4.3.6.RELEASE</version>
68 </dependency>
69
70
71 <!--6 https://mvnrepository.com/artifact/aopalliance/aopalliance -->
72 <dependency>
73 <groupId>aopalliance</groupId>
74 <artifactId>aopalliance</artifactId>
75 <version>1.0</version>
76 </dependency>
77
78
79 <!--7 https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
80 <dependency>
81 <groupId>org.aspectj</groupId>
82 <artifactId>aspectjweaver</artifactId>
83 <version>1.8.10</version>
84 </dependency>
85
86
87 <!--8 https://mvnrepository.com/artifact/log4j/log4j -->
88 <dependency>
89 <groupId>log4j</groupId>
90 <artifactId>log4j</artifactId>
91 <version>1.2.17</version>
92 </dependency>
93
94
95 <!--9 https://mvnrepository.com/artifact/commons-logging/commons-logging -->
96 <dependency>
97 <groupId>commons-logging</groupId>
98 <artifactId>commons-logging</artifactId>
99 <version>1.2</version>
100 </dependency>
101
102
103 <!--10 https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
104 <dependency>
105 <groupId>org.springframework</groupId>
106 <artifactId>spring-jdbc</artifactId>
107 <version>4.3.6.RELEASE</version>
108 </dependency>
109
110
111 <!--11 https://mvnrepository.com/artifact/org.springframework/spring-orm -->
112 <dependency>
113 <groupId>org.springframework</groupId>
114 <artifactId>spring-orm</artifactId>
115 <version>4.3.6.RELEASE</version>
116 </dependency>
117
118
119 <!--12 https://mvnrepository.com/artifact/org.springframework/spring-tx -->
120 <dependency>
121 <groupId>org.springframework</groupId>
122 <artifactId>spring-tx</artifactId>
123 <version>4.3.6.RELEASE</version>
124 </dependency>
125
126
127 <!--13 https://mvnrepository.com/artifact/org.springframework/spring-web -->
128 <dependency>
129 <groupId>org.springframework</groupId>
130 <artifactId>spring-web</artifactId>
131 <version>4.3.6.RELEASE</version>
132 </dependency>
133
134
135 <!--14 https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
136 <dependency>
137 <groupId>org.springframework</groupId>
138 <artifactId>spring-webmvc</artifactId>
139 <version>4.3.6.RELEASE</version>
140 </dependency>
141
142
143 <!--15 https://mvnrepository.com/artifact/org.mybatis/mybatis -->
144 <dependency>
145 <groupId>org.mybatis</groupId>
146 <artifactId>mybatis</artifactId>
147 <version>3.4.6</version>
148 </dependency>
149
150 <!--17 https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
151 <dependency>
152 <groupId>org.mybatis</groupId>
153 <artifactId>mybatis-spring</artifactId>
154 <version>1.3.2</version>
155 </dependency>
156
157
158 <!--16 https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
159 <dependency>
160 <groupId>mysql</groupId>
161 <artifactId>mysql-connector-java</artifactId>
162 <version>5.1.38</version>
163 </dependency>
164
165
166 <!--18 https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
167 <dependency>
168 <groupId>javax.servlet</groupId>
169 <artifactId>javax.servlet-api</artifactId>
170 <version>3.1.0</version>
171 </dependency>
172
173 <!--19 https://mvnrepository.com/artifact/javax.servlet/jstl -->
174 <dependency>
175 <groupId>javax.servlet</groupId>
176 <artifactId>jstl</artifactId>
177 <version>1.2</version>
178 </dependency>
179
180
181 <!--20 https://mvnrepository.com/artifact/taglibs/standard -->
182 <dependency>
183 <groupId>taglibs</groupId>
184 <artifactId>standard</artifactId>
185 <version>1.1.2</version>
186 </dependency>
187
188 </dependencies>
189
190 </project>
pom.xml
3、在java目录下的com.pojo包下构建2个实体类，即Dept.java 和Emp.java
1 package com.pojo;
2 import java.util.List;
3 /**
4 * 一方引用多方list集合
5 */
6 public class Dept {
7 private Integer deptno ;
8 private String dname ;
9 private String loc ;
10
11 public Dept() {
12 }
13
14 public Dept(Integer deptno, String dname, String loc) {
15 this.deptno = deptno;
16 this.dname = dname;
17 this.loc = loc;
18 }
19
20 public Integer getDeptno() {
21 return deptno;
22 }
23
24 public void setDeptno(Integer deptno) {
25 this.deptno = deptno;
26 }
27
28
29 public String getDname() {
30 return dname;
31 }
32
33 public void setDname(String dname) {
34 this.dname = dname;
35 }
36
37
38 public String getLoc() {
39 return loc;
40 }
41
42 public void setLoc(String loc) {
43 this.loc = loc;
44 }
45
46 @Override
47 public String toString() {
48 return "Dept{" +
49 "deptno=" + deptno +
50 ", dname='" + dname + '\'' +
51 ", loc='" + loc + '\'' +
52 '}';
53 }
54
Dept.java
1 package com.pojo;
2
3 /**
4 * 多方引用一方对象
5 */
6 public class Emp {
7 private Integer empno ;
8 private String ename ;
9 private String job ;
10 private Integer mgr ;
11 private String hiredate ;
12 private Double sal ;
13 private Double comm ;
14 private Dept dept;
15
16 public Emp() {
17 }
18
19 public Emp(Integer empno, String ename, String job, Integer mgr, String hiredate, Double sal, Double comm, Dept dept) {
20 this.empno = empno;
21 this.ename = ename;
22 this.job = job;
23 this.mgr = mgr;
24 this.hiredate = hiredate;
25 this.sal = sal;
26 this.comm = comm;
27 this.dept = dept;
28 }
29
30 public Integer getEmpno() {
31 return empno;
32 }
33
34 public void setEmpno(Integer empno) {
35 this.empno = empno;
36 }
37
38
39 public String getEname() {
40 return ename;
41 }
42
43 public void setEname(String ename) {
44 this.ename = ename;
45 }
46
47
48 public String getJob() {
49 return job;
50 }
51
52 public void setJob(String job) {
53 this.job = job;
54 }
55
56
57 public Integer getMgr() {
58 return mgr;
59 }
60
61 public void setMgr(Integer mgr) {
62 this.mgr = mgr;
63 }
64
65
66 public String getHiredate() {
67 return hiredate;
68 }
69
70 public void setHiredate(String hiredate) {
71 this.hiredate = hiredate;
72 }
73
74
75 public Double getSal() {
76 return sal;
77 }
78
79 public void setSal(Double sal) {
80 this.sal = sal;
81 }
82
83
84 public Double getComm() {
85 return comm;
86 }
87
88 public void setComm(Double comm) {
89 this.comm = comm;
90 }
91
92 public Dept getDept() {
93 return dept;
94 }
95
96 public void setDept(Dept dept) {
97 this.dept = dept;
98 }
99
100 @Override
101 public String toString() {
102 return "Emp{" +
103 "empno=" + empno +
104 ", ename='" + ename + '\'' +
105 ", job='" + job + '\'' +
106 ", mgr=" + mgr +
107 ", hiredate='" + hiredate + '\'' +
108 ", sal=" + sal +
109 ", comm=" + comm +
110 ", dept=" + dept +
111 '}';
112 }
113
Emp.java
4、在java目录下的com.mapper包下构建2个映射接口，即DeptMapppr.java 和EmpMapper.java
1 package com.mapper;
2 import com.pojo.Dept;
3 import java.util.List;
4 public interface DeptMapper {
5 List<Dept> selectAllDept();
6
DeptMapper.java
1 package com.mapper;
2
3 import com.pojo.Emp;
4
5 import java.util.List;
6
7 public interface EmpMapper {
8 List<Emp> selectEmp(Emp emp);
9 int insertEmp(Emp emp);
10 int updateEmp(Emp emp);
11 int deleteEmp(Emp emp);
12
EmpMapper.java
5、在java目录下的com.servicer包下构建2个业务层接口，即DeptService.java 和EmpService.java，和2个业务层接口实现类，即DeptServiceImpl.java 和EmpServiceImpl.java
1 package com.service;
2
3 import com.pojo.Dept;
4
5 import java.util.List;
6
7 public interface DeptService {
8 List<Dept> downOption();
9
DeptService.java
1 package com.service;
2
3 import com.pojo.Emp;
4
5 import java.util.List;
6
7 public interface EmpService {
8 List<Emp> show(Emp emp);
9 int add(Emp emp);
10 int edit(Emp emp);
11 int del(Emp emp);
12
EmpService.java
1 package com.service;
2
3 import com.mapper.DeptMapper;
4 import com.pojo.Dept;
5
6 import java.util.List;
7
8 public class DeptServiceImpl implements DeptService{
9 private DeptMapper deptMapper;
10
11 public DeptMapper getDeptMapper() {
12 return deptMapper;
13 }
14
15 public void setDeptMapper(DeptMapper deptMapper) {
16 this.deptMapper = deptMapper;
17 }
18
19 @Override
20 public List<Dept> downOption() {
21 return deptMapper.selectAllDept();
22 }
23
DeptServiceImpl.java
1 package com.service;
2
3 import com.mapper.EmpMapper;
4 import com.pojo.Emp;
5
6 import java.util.List;
7
8 public class EmpServiceImpl implements EmpService {
9 private EmpMapper empMapper;
10
11 public EmpMapper getEmpMapper() {
12 return empMapper;
13 }
14
15 public void setEmpMapper(EmpMapper empMapper) {
16 this.empMapper = empMapper;
17 }
18
19 @Override
20 public List<Emp> show(Emp emp) {
21 return empMapper.selectEmp(emp);
22 }
23
24 @Override
25 public int add(Emp emp) {
26 return empMapper.insertEmp(emp);
27 }
28
29 @Override
30 public int edit(Emp emp) {
31 return empMapper.updateEmp(emp);
32 }
33
34 @Override
35 public int del(Emp emp) {
36 return empMapper.deleteEmp(emp);
37 }
38
EmpServiceImpl.java
6、在resources目录下的mapper包下构建2个映射xml文件，即DeptMapppr.xml和EmpMapper.xml
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <!--配置规则：-->
4 <!--有代理的namespace值必须是映射接口全路径，sql节点的id值必须是映射接口中的方法名-->
5 <!--无代理的namespace值和sql节点的id值是任意的，但一般情况下和和有代理的原则一样-->
6 <mapper namespace="com.mapper.DeptMapper">
7 <select id="selectAllDept" resultType="Dept">
8 select * from dept
9 </select>
10 </mapper>
DeptMapppr.xml
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 <mapper namespace="com.mapper.EmpMapper">
4 <!-- 案例：select * from emp where empno=#{empno} or ename=#{ename} and job=#{job}
5 如果select节点中是以上sql语句，parameterType是Emp类型，则此情况可以使用where标签,
6 where标签的作用是如果if条件不满足则执行，如果满足if条件将会忽略where关键字后的
7 第一个or或and
8 select * from emp
9 <where>
10 <if test="empno!=null">
11 empno=#{empno}
12 </if>
13 <if test="ename!=null">
14 or ename=#{ename}
15 </if>
16 <if test="job!=null">
17 and job=#{job}
18 </if>
19 </where>
20 -->
21 <!--if或when的test条件中的属性判断，必须从参数对象的第一层属性判断起，-->
22 <!--如果Emp类中有empno属性，有dept对象属性，则要判断empno值和dept中deptno的值，-->
23 <!--则写法是empno！=null，dept！=null && dept.deptno!=null-->
24 <select id="selectEmp" resultMap="empmap" parameterType="Emp">
25 select d.dname,d.loc,e.* from emp e,dept d where e.deptno=d.deptno
26 <if test="empno!=null">
27 and e.empno=#{empno}
28 </if>
29 <if test="ename!=null">
30 and e.ename=#{ename}
31 </if>
32 <if test="job!=null">
33 and e.job=#{job}
34 </if>
35 <if test="dept!=null and dept.deptno!=null">
36 and e.deptno=#{dept.deptno}
37 </if>
38 </select>
39
40 <resultMap id="empmap" type="Emp">
41 <!--因为当前多方配置，因此在resultMap中先配置多方的主键和普通列的映射-->
42 <id column="EMPNO" property="empno"/>
43 <result column="ENAME" property="ename"/>
44 <result column="JOB " property="job"/>
45 <result column="MGR" property="mgr"/>
46 <result column="HIREDATE" property="hiredate"/>
47 <result column="SAL" property="sal"/>
48 <result column="COMM" property="comm"/>
49
50 <!--因为在多方引用了一方的对象，因此在引用一方对象属性的地方配置association节点-->
51 <!--association节点的property属性对应的多方引用一方的对象属性名,-->
52 <!--javaType是多方引用一方的类类型即配置类的全路径-->
53 <!--column是多方关联一方的外键列的列名-->
54 <association property="dept" javaType="Dept" column="DEPTNO">
55 <!--由于多方引用的了一方对象，因此在association对象节点下配置一方的主键和普通字段-->
56 <id column="DEPTNO" property="deptno"/>
57 <result column="DNAME" property="dname"/>
58 <result column="LOC" property="loc"/>
59 </association>
60 </resultMap>
61
62 <insert id="insertEmp" parameterType="Emp">
63 INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
64 (#{empno},#{ename},#{job},#{mgr},#{hiredate},#{sal},#{comm},#{dept.deptno})
65 </insert>
66
67 <!--set标签的结束标签要在where条件之前-->
68 <update id="updateEmp" parameterType="Emp">
69 UPDATE emp
70 <set>
71 <if test="ename!=null">
72 ename=#{ename} ,
73 </if>
74 <if test="job!=null">
75 job=#{job} ,
76 </if>
77 <if test="mgr!=null">
78 mgr=#{mgr} ,
79 </if>
80 <if test="hiredate!=null">
81 hiredate=#{hiredate} ,
82 </if>
83 <if test="sal!=null">
84 sal=#{sal},
85 </if>
86 <if test="comm!=null">
87 comm=#{comm},
88 </if>
89 <if test="dept!=null and dept.deptno!=null">
90 deptno=#{dept.deptno} ,
91 </if>
92 </set>
93 <include refid="wherecondition"/>
94 </update>
95
96 <delete id="deleteEmp" parameterType="Emp">
97 delete from emp
98 <include refid="wherecondition"/>
99 </delete>
100
101 <!--定义公共的sql片段-->
102 <sql id="wherecondition">
103 <where>
104 <if test="empno!=null">
105 empno=#{empno}
106 </if>
107 </where>
108 </sql>
109
110 </mapper>
EmpMapper.xml
7、在resources根目录下编辑log4j.properties日志文件
1 log4j.rootLogger=DEBUG, Console
2 #Console
3 log4j.appender.Console=org.apache.log4j.ConsoleAppender
4 log4j.appender.Console.layout=org.apache.log4j.PatternLayout
5 log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
6
7 log4j.logger.org.apache=INFO
8 log4j.logger.java.sql.ResultSet=INFO
9 log4j.logger.java.sql.Connection=DEBUG
10 log4j.logger.java.sql.Statement=DEBUG
11
log4j.properties
8、在resources根目录下构建spring的xml文件，即applicationContext.xml日志文件
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xmlns:aop="http://www.springframework.org/schema/aop"
5 xmlns:context="http://www.springframework.org/schema/context"
6 xmlns:tx="http://www.springframework.org/schema/tx"
7 xmlns:mvc="http://www.springframework.org/schema/mvc"
8 xsi:schemaLocation="http://www.springframework.org/schema/beans
9 http://www.springframework.org/schema/beans/spring-beans.xsd
10 http://www.springframework.org/schema/aop
11 http://www.springframework.org/schema/aop/spring-aop.xsd
12 http://www.springframework.org/schema/context
13 http://www.springframework.org/schema/context/spring-context.xsd
14 http://www.springframework.org/schema/tx
15 http://www.springframework.org/schema/tx/spring-tx.xsd
16 http://www.springframework.org/schema/mvc
17 http://www.springframework.org/schema/mvc/spring-mvc.xsd
18 ">
19 <!--1.驱动管理数据源-->
20 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
21 <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
22 <property name="url" value="jdbc:mysql://localhost:3306/ar"/>
23 <property name="username" value="root"/>
24 <property name="password" value="123456"/>
25 </bean>
26 <!--2.数据源事务管理，注意id的必须是transactionManager-->
27 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
28 <property name="dataSource" ref="dataSource"/>
29 </bean>
30 <!--3.事务注解驱动-->
31 <tx:annotation-driven transaction-manager="transactionManager"/>
32 <!--4.sqlsessionfactorybean-->
33 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
34 <property name="dataSource" ref="dataSource"/>
35 <!--配置某个包下的类全路径的别名,别名是类名-->
36 <property name="typeAliasesPackage" value="com.pojo"/>
37
38 <!--引用mybatis映射文件的写法-->
39 <property name="mapperLocations" value="classpath:mapper/*.xml"/>
40
41 <!--引用mybatis主配置文件的写法-->
42 <!--<property name="configLocation" value="mybatisConfig.xml"/>-->
43 </bean>
44
45 <!--5.SqlSessionTemplate-->
46 <!--采用构造注入-->
47 <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
48 <constructor-arg ref="sqlSessionFactory"/>
49 </bean>
50
51 <!--6.映射工厂bean，引用映射接口，底层代理会自动帮我们完成映射接口实现类的部分-->
52 <bean id="deptMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
53 <property name="sqlSessionTemplate" ref="sqlSessionTemplate"/>
54 <property name="mapperInterface" value="com.mapper.DeptMapper"/>
55 </bean>
56
57 <bean id="empMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
58 <property name="sqlSessionTemplate" ref="sqlSessionTemplate"/>
59 <property name="mapperInterface" value="com.mapper.EmpMapper"/>
60 </bean>
61
62 <bean id="deptService" class="com.service.DeptServiceImpl">
63 <property name="deptMapper" ref="deptMapper"/>
64 </bean>
65 <bean id="empService" class="com.service.EmpServiceImpl">
66 <property name="empMapper" ref="empMapper"/>
67 </bean>
68 </beans>
applicationContext.xml
9、在java目录下的com.test包下构建测试类，即ScottTest.java
1 package com.test;
2 import com.mapper.DeptMapper;
3 import com.mapper.EmpMapper;
4 import com.pojo.Dept;
5 import com.pojo.Emp;
6 import com.service.DeptService;
7 import com.service.EmpService;
8 import org.junit.Test;
9 import org.springframework.context.ApplicationContext;
10 import org.springframework.context.support.ClassPathXmlApplicationContext;
11 import java.util.List;
12 public class ScottTest {
13 ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext.xml");
14 DeptService deptService= (DeptService)ac.getBean("deptService");
15 EmpService empService= (EmpService)ac.getBean("empService");
16
17 @Test
18 public void selectDept(){
19
20 List<Dept> deptList=deptService.downOption();
21 for (Dept d:deptList) {
22 System.out.println(d);
23 }
24 }
25
26 @Test
27 public void selectEmp(){
28 Emp emp=new Emp();
29 // emp.setEmpno(7788);
30 // emp.setEname("SCOTT");
31 // emp.setJob("SALESMAN");
32 emp.setDept(new Dept(20,null,null));
33 List<Emp> emplist=empService.show(emp);
34 for (Emp e:emplist ) {
35 System.out.println(e);
36 }
37 }
38
39 @Test
40 public void insertEmp(){
41 Dept dept= new Dept(10,null,null);
42 Emp emp=new Emp(1,"holly","teacher",7788,
43 "2015-05-05",1200.00,15.00,dept);
44 System.out.println(empService.add(emp)>0?"add success":"add fail");
45 }
46 @Test
47 public void updateEmp(){
48 Dept dept= new Dept(20,null,null);
49 Emp emp=new Emp(1,"holly","teacher",7788,
50 "2015-05-05",1200.00,15.00,dept);
51 System.out.println(empService.edit(emp)>0?"update success":"update fail");
52 }
53 @Test
54 public void deleteEmp(){
55 Emp emp=new Emp();
56 emp.setEmpno(1);
57 System.out.println(empService.del(emp)>0?"delete success":"delete fail");
58 }
59
ScottTest.java
10、简单测试某个方法
此文章为原创，转载请注明出处！需要本案例源码，理论讲解视频，代码操作视频的，请私信联系作者！