该工程采用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>
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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 }
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>
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>
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.logger.java.sql.PreparedStatement=DEBUG
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>
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 }
10、简单测试某个方法
此文章为原创，转载请注明出处！需要本案例源码，理论讲解视频，代码操作视频的，请私信联系作者！
内容来源于网络如有侵权请私信删除
文章来源: 博客园
- 还没有人评论，欢迎说说您的想法！
客服
