文章详情页
解析Mybatis对sql表的一对多查询问题
Mybatisd对sql表的一对多查询
select * fromprojectrecord prleft join projects poon po.pid=pr.pidleft join emp eon e.empno = pr.empnowhere pr.pid=1
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.msb.mapper.EmpMapper"> <!--resultMap是一个映射表,对应的字段会对应数据表的字段 --> <resultMap id="projectMap" type="projects"><id column="pid" property="pid"></id><result column="pname" property="pname"></result><result column="money" property="money"></result><collection property="projectrecordList" ofType="projectrecord"> <id property="pid" column="pid"></id> <id property="empno" column="empno"></id> <association property="emp" javaType="emp"><id property="empno" column="empno"></id><result property="ename" column="ename"></result><result property="job" column="job"></result><result property="sal" column="sal"></result><result property="hiredate" column="hiredate"></result><result property="mgr" column="mgr"></result><result property="comm" column="comm"></result><result property="deptno" column="deptno"></result> </association></collection> </resultMap> <!--Projects findPiddle(int pid);--> <select id="findPiddle" resultMap="projectMap">select *from emp einner join projectrecord pr on pr.empno=e.empnoinner join projects po on po.pid= pr.pidwhere pr.pid=#{pid} </select></mapper>
//mapper表package com.msb.mapper;import com.msb.pojo.Dept;import com.msb.pojo.Emp;import com.msb.pojo.Projects;import java.util.List;public interface EmpMapper { /** * 实现根据项目编号查看所有参与项目的人员信息 * @param pid 传入项目号 * @return 返回一个project的对象 */ Projects findPiddle(int pid);}
package com.msb.pojo;//一对一关系引入import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;@Data@AllArgsConstructor@NoArgsConstructorpublic class Projectrecord implements Serializable { private int empno; private int pid; //pr和e是一对一的关系 private Emp emp;}
package com.msb.pojo;//一对多关系实体类设计import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;import java.util.List;@NoArgsConstructor@AllArgsConstructor@Datapublic class Projects implements Serializable { private int pid; private String pname; private int money; //po和pr是一对多的关系 List<Projectrecord> projectrecordList;}
package com.msb.pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.sql.Date;@Data@AllArgsConstructor@NoArgsConstructorpublic class Emp { private Integer empno; private String ename; private String job; private Integer mgr; private Date hiredate; private Integer sal; private Integer comm; private Integer deptno; private Dept dept;}
jdbc_driver=com.mysql.cj.jdbc.Driverjdbc_url=jdbc:mysql://127.0.0.1:3306/testtable?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghaijdbc_username=rootjdbc_password=root
log4j.rootLogger=debug,stdoutlog4j.appender.stdout=org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.Target=System.errlog4j.appender.stdout.layout=org.apache.log4j.SimpleLayoutlog4j.appender.logfile=org.apache.log4j.FileAppenderlog4j.appender.logfile.File=d:/msb.loglog4j.appender.logfile.layout=org.apache.log4j.PatternLayoutlog4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %l %F %p %m%n
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <!--引入外部配置文件--> <properties resource="jdbc.properties"></properties> <!--这里是起的别名,前面是名,后面是路径--> <!--<typeAliases><typeAlias alias="dept" type="com.msb.pojo.Dept"/> </typeAliases>--> <!--包别名,用到时候调用名字小写即可,就会扫描msb下的所以实体类,用的时候实体类名小写--> <typeAliases><package name="com.msb"/> </typeAliases> <environments default="development"><environment id="development"> <!-- 简单使用了 JDBC 的提交和回滚设置 --> <transactionManager type="JDBC"/> <dataSource type="POOLED"><property name="driver" value="${jdbc_driver}"/><property name="url" value="${jdbc_url}"/><property name="username" value="${jdbc_username}"/><property name="password" value="${jdbc_password}"/> </dataSource></environment> </environments> <!--加载mapper映射文件--> <mappers><package name="com.msb.mapper"/> </mappers></configuration>
import com.msb.mapper.EmpMapper;import com.msb.pojo.Dept;import com.msb.pojo.Emp;import com.msb.pojo.Projectrecord;import com.msb.pojo.Projects;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.After;import org.junit.Before;import org.junit.Test;import java.io.IOException;import java.io.InputStream;import java.util.List;public class Test1 { SqlSession sqlSession = null; EmpMapper mapper =null; @Before public void test1(){ //首先做一个对象SqlSessionFactoryBuilder建立一个绘话 SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder(); //有一个文本输入的io流进行读取操作 InputStream stream = null; try { //这里的路径直接会定位到配置文件classes下面;所以这个文件在次目录下--编译和 //-图纸;对数据库文件进行读取,获取一个io流,由于配置文件在classes下面,直接写文件名即可 stream = Resources.getResourceAsStream("sqlMapConfig.xml"); } catch (IOException e) { e.printStackTrace(); } //build需要指向一个文件进行读取出来--工厂 SqlSessionFactory factory = ssfb.build(stream);//需要用sqlSession去调用增删改查--工人去获取数据,打开这个绘话 sqlSession = factory.openSession(); } /*多对多的查询*/ @Test public void test7(){mapper = sqlSession.getMapper(EmpMapper.class);Projects piddle = mapper.findPiddle(1);System.out.println("Pid");System.out.println(piddle.getPid());System.out.println("Pname");System.out.println(piddle.getPname());System.out.println("Money");System.out.println(piddle.getMoney());System.out.println("ProjectrecordList");List<Projectrecord> projectrecordList = piddle.getProjectrecordList();projectrecordList.forEach(System.out::println); } @After public void test3(){ if (sqlSession!=null){ sqlSession.close(); } }}
到此这篇关于解析Mybatis对sql表的一对多查询的文章就介绍到这了,更多相关Mybatis一对多查询内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
标签:
MsSQL
排行榜