该工程采用spring+mybatis的关联映射，动态sql，sql片段实现
1、搭建项目结构如下
 
2、配置项目的pom.xml文件中的依赖
  pom.xml3、在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 }
- 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 }
- 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 }
- 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
- 5
- 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
- 21
- 22
- 23
- 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
- 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
- 51
- 52
- 53
- 54 <association property="dept" javaType="Dept" column="DEPTNO">
- 55
- 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
- 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
- 102 <sql id="wherecondition">
- 103 <where>
- 104 <if test="empno!=null">
- 105 empno=#{empno}
- 106 </if>
- 107 </where>
- 108 </sql>
- 109
- 110 </mapper>
- 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
- 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
- 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
- 27 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
- 28 <property name="dataSource" ref="dataSource"/>
- 29 </bean>
- 30
- 31 <tx:annotation-driven transaction-manager="transactionManager"/>
- 32
- 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
- 39 <property name="mapperLocations" value="classpath:mapper/*.xml"/>
- 40
- 41
- 42
- 43 </bean>
- 44
- 45
- 46
- 47 <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
- 48 <constructor-arg ref="sqlSessionFactory"/>
- 49 </bean>
- 50
- 51
- 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>
- 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 }
此文章为原创，转载请注明出处！需要本案例源码，理论讲解视频，代码操作视频的，请私信联系作者！
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
