网站开发和软件开发有什么区别,一站式做网站价格,一起装修网官方网站,如何做超一个电子商务网站目录
一、项目框架搭建
二、在实体类中添加额外属性实现多表查询
1、mybatis两表关联查询
#xff08;1#xff09;实体类类型映射规则
#xff08;2#xff09;代码演示
2、分步查询
#xff08;1#xff09;autoMapping开启自动映射
#xff08;2#xff09;…目录
一、项目框架搭建
二、在实体类中添加额外属性实现多表查询
1、mybatis两表关联查询
1实体类类型映射规则
2代码演示
2、分步查询
1autoMapping开启自动映射
2封装SQL语句
2懒加载
三、MyBatis对一对多关系的处理
1、collection配置集合映射
2、代码演示
3、规范mapper映射文件 一、项目框架搭建 1准备两个数据库表员工表和部门表 CREATE DATABASE mybatisdatabase;
USE mybatisdatabase;CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 员工编号,
ename VARCHAR(20) NOT NULL COMMENT 员工姓名,
age INT NOT NULL COMMENT 年龄,
deptno INT NOT NULL COMMENT 部门编号
);INSERT INTO emp(ename,age,deptno) VALUES
(tom,18,1),
(jack,20,1),
(小黑,19,2),
(老默,31,2),
(启强,24,2);CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 部门编号,
dept_name VARCHAR(20) NOT NULL COMMENT 部门名称,
local VARCHAR(20) NOT NULL COMMENT 部门地址
);INSERT INTO dept(dept_name,local) VALUES
(市场部,安徽合肥),
(财务部,江苏南京),
(生产部,安徽芜湖);2新建module---java框架Maven工程---完善工程目录 3在pom.xml中添加需要使用的依赖 project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersiongroupIdcom.mybatis/groupIdartifactIdmybatis04/artifactIdversion1.0-SNAPSHOT/versionpackagingjar/packagingnamemybatis04/nameurlhttp://maven.apache.org/urlpropertiesproject.build.sourceEncodingUTF-8/project.build.sourceEncoding/propertiesdependenciesdependencygroupIdjunit/groupIdartifactIdjunit/artifactIdversion4.12/versionscopetest/scope/dependencydependencygroupIdorg.mybatis/groupIdartifactIdmybatis/artifactIdversion3.5.11/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion5.1.48/version/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdversion1.18.24/version/dependencydependencygroupIdlog4j/groupIdartifactIdlog4j/artifactIdversion1.2.17/version/dependency/dependencies
/project4创建实体类和Mapper接口 package com.mybatis.entity;import lombok.Data;Data
public class Emp {private long id;private String ename;private long age;private long deptno;}package com.mybatis.entity;import lombok.Data;Data
public class Dept {private long id;private String deptName;private String local;}5在resources目录下新建config文件存放mybatis全局配置文件和外部数据源 ?xml version1.0 encodingUTF-8 ?
!DOCTYPE configurationPUBLIC -//mybatis.org//DTD Config 3.0//ENhttp://mybatis.org/dtd/mybatis-3-config.dtd
configuration!-- 引入外部数据源参数--properties resourceconfig/jdbc.properties/propertiessettings!-- 开启驼峰映射--setting namemapUnderscoreToCamelCase valuetrue/!-- 开启日志打印--setting namelogImpl valueLOG4J//settings!-- 给表起别名--typeAliasespackage namecom.mybatis.entity//typeAliasesenvironments defaultdevelopmentenvironment iddevelopmenttransactionManager typeJDBC/dataSource typePOOLEDproperty namedriver value${jdbc.driver}/property nameurl value${jdbc.url}/property nameusername value${jdbc.username}/property namepassword value${jdbc.password}//dataSource/environment/environmentsmapperspackage namecom.mybatis.mapper//mappers
/configuration
jdbc.drivercom.mysql.jdbc.Driver
jdbc.urljdbc:mysql://127.0.0.1:3306/mybatisdatabase
jdbc.usernameroot
jdbc.password123456 6在resources目录下新建与Mapper接口层级相同的文件夹存放Mapper映射文件 ?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.EmpMapper/mapper
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.DeptMapper/mapper 7在resources目录下添加日志配置文件log4j.properties #打印日志的级别可控制打印信息哪些打印哪些不打印
#Console打印窗口
log4j.rootLoggerDEBUG,Console
#Console
log4j.appender.Consoleorg.apache.log4j.ConsoleAppender
log4j.appender.console.TargetSystem.out
#设置打印格式
log4j.appender.Console.layoutorg.apache.log4j.PatternLayout
#设置打印信息
log4j.appender.Console.layout.ConversionPattern%d [%t] %-5p [%c] - %m%n
#打印日志级别设置打印级别只要不是ERROR级别就不打印
log4j.logger.org.apacheERROR
log4j.logger.org.mybatisERROR
log4j.logger.org.springframeworkERROR
#这个需要
log4j.logger.log4jdbc.debugERROR
log4j.logger.com.gk.mapperERROR
log4j.logger.jdbc.auditERROR
log4j.logger.jdbc.resultsetERROR
#这个打印SQL语句非常重要
log4j.logger.jdbc.sqlonlyDEBUG
log4j.logger.jdbc.sqltimingERROR
log4j.logger.jdbc.connectionFATAL
二、在实体类中添加额外属性实现多表查询
package com.mybatis.entity;import lombok.Data;Data
public class Emp {private long id;private String ename;private long age;private long deptno;//添加额外属性private Dept dept;}?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.EmpMapperselect idselect resultTypeempselect * from emp/select
/mapper
package com.mybatis.mapper;import com.mybatis.entity.Emp;
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.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class EmpMapperTest {SqlSessionFactory sqlSessionFactory null;Beforepublic void init(){System.out.println(init()被执行);InputStream resourceAsStream null;try {resourceAsStream Resources.getResourceAsStream(config/mybatis-config.xml);} catch (IOException e) {e.printStackTrace();}sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream);}Testpublic void select() {//创建SqlSession会话SqlSession sqlSession sqlSessionFactory.openSession();//获取EmpMapper接口的动态代理对象EmpMapper empMapper sqlSession.getMapper(EmpMapper.class);//通过接口调用方法ListEmp select empMapper.select();for (Emp emp:select){System.out.println(emp emp);}//关闭资源sqlSession.close();}
} dept是空的因为目前只查询了员工表emp 1、mybatis两表关联查询 查询的结果只有员工表(emp)的数据deft依旧为空dept为实体类对象mybatis目前还不能自动赋值 1实体类类型映射规则 association标签连接两个表获取多个表中的信息以及在一对一、多对多的关系中获取相关数据 property实体类对象 resultMap idempMap typeempid propertyid columnid/idresult columnename propertyename/resultresult propertyage columnage/resultresult columndeptno propertydeptno/resultassociation propertydeptid columndid propertyid/idresult propertydeptName columndept_name/resultresult propertylocal columnlocal/result/association/resultMap
2代码演示
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.EmpMapperresultMap idempMap typeempid propertyid columnid/idresult columnename propertyename/resultresult propertyage columnage/resultresult columndeptno propertydeptno/resultassociation propertydeptid columndid propertyid/idresult propertydeptName columndept_name/resultresult propertylocal columnlocal/result/association/resultMapselect idselectByEmpJoinDept resultMapempMapSELECT emp.*,dept.id AS did,dept.dept_name,dept.local FROM emp JOIN dept ON emp.deptno dept.id/select
/mapper
package com.mybatis.mapper;import com.mybatis.entity.Emp;
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.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class EmpMapperTest {//创建SqlSessionFactory工厂对象SqlSessionFactory sqlSessionFactory null;Beforepublic void init(){System.out.println(init());InputStream resourceAsStream;try {resourceAsStream Resources.getResourceAsStream(config/mybatis-config.xml);} catch (IOException e) {throw new RuntimeException(e);}sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream);}Testpublic void selectByEmpJoinDept() {//创建SqlSession会话SqlSession sqlSession sqlSessionFactory.openSession();//获取EmpMapper动态代理对象EmpMapper empMapper sqlSession.getMapper(EmpMapper.class);//通过接口调用方法ListEmp empList empMapper.selectByEmpJoinDept();for (Emp emp:empList){System.out.println(emp emp);}//关闭资源sqlSession.close();}
} 2、分步查询 在做多表查询时有时我们不需要所有表的数据但一条SQL语句会查询出所有表的数据,大大降低了数据库的性能我们可根据分步查询解决这个弊端 1autoMapping开启自动映射 自动映射默认是关闭的但代码运行时也会自动开启可以通过设置autoMapping的属性值为“true”开启自动映射不能够完成自动映射的字段会按照已设置的映射规则进行映射 resultMap idempMap typeemp autoMappingtrueassociation propertydeptid columndid propertyid/idresult propertydeptName columndept_name/resultresult propertylocal columnlocal/result/association/resultMap
2封装SQL语句 我们可通过封装从表数据的方式在需要从表的数据时查询从表以此来实现分步查询。 在association标签中使用select属性和column属性 select指定一条SQL语句 column指定主表的哪一字段作为参数传递 2懒加载
上面我们使用的SQL语句无论你是否需要关联表dept中的数据都会去查询关联表中的数据当我们只需要emp表中的数据时也会去查询dept表降低了数据库的性能 按需加载先从表单查询需要时再从关联表去关联查询能大大提升数据库性能 在association标签中设置fetchType属性值为lazy开启懒加载分步查询正式完成 package com.mybatis.mapper;import com.mybatis.entity.Emp;import java.util.List;public interface EmpMapper {ListEmp select();}?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.EmpMapperresultMap idempMap typeemp autoMappingtrueassociation propertydept selectdeptById columndeptno fetchTypelazyid columndid propertyid/idresult propertydeptName columndept_name/resultresult propertylocal columnlocal/result/association/resultMapselect idselect resultMapempMapselect * from emp/selectselect iddeptById resultTypedeptselect * from dept where id #{deptno}/select/mapper
package com.mybatis.mapper;import com.mybatis.entity.Emp;
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.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class EmpMapperTest {//创建SqlSessionFactory工厂对象SqlSessionFactory sqlSessionFactory null;Beforepublic void init(){System.out.println(init());InputStream resourceAsStream;try {resourceAsStream Resources.getResourceAsStream(config/mybatis-config.xml);} catch (IOException e) {throw new RuntimeException(e);}sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream);}Testpublic void select() {//创建SqlSession会话SqlSession sqlSession sqlSessionFactory.openSession();//获取EmpMapper动态代理对象EmpMapper empMapper sqlSession.getMapper(EmpMapper.class);//通过接口调用方法ListEmp select empMapper.select();for (Emp emp:select){System.out.println(ename:emp.getEname());}//关闭资源sqlSession.close();}
} 三、MyBatis对一对多关系的处理 一对一关系一个员工只属于一个部门 一对多关系一个部门有多个员工 以dept为主表查询每个部门中的所有员工 1、collection配置集合映射 一个部门对应的员工查询结果是一个Emp对象的集合MyBatis中提供了对集合配置映射的标签collection ---ofType:指定集合中的数据类型 2、代码演示
package com.mybatis.entity;import lombok.Data;import java.util.List;Data
public class Dept {private long id;private String deptName;private String local;//添加额外属性private ListEmp emps;}package com.mybatis.mapper;import com.mybatis.entity.Dept;import java.util.List;public interface DeptMapper {ListDept select();
}?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.DeptMapperresultMap iddeptMap typedept autoMappingtruecollection propertyemps ofTypeemp selectselectEmp columnid fetchTypelazyid propertyid columnid/idresult columnename propertyename/resultresult propertyage columnage/resultresult columndeptno propertydeptno/result/collection/resultMapselect idselect resultMapdeptMapselect * from dept/selectselect idselectEmp resultTypeempselect * from emp where deptno #{id}/select
/mapper
package com.mybatis.mapper;import com.mybatis.entity.Dept;
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.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class DeptMapperTest {//创建SqlSessionFactory工厂对象SqlSessionFactory sqlSessionFactory null;Beforepublic void init(){System.out.println(init());InputStream resourceAsStream;try {resourceAsStream Resources.getResourceAsStream(config/mybatis-config.xml);} catch (IOException e) {throw new RuntimeException(e);}sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream);}Testpublic void select() {//创建SqlSession会话SqlSession sqlSession sqlSessionFactory.openSession();//获取DeptMapper动态代理对象DeptMapper deptMapper sqlSession.getMapper(DeptMapper.class);//通过接口调用方法ListDept select deptMapper.select();for (Dept dept:select){System.out.println(dept dept.getDeptName());}sqlSession.close();}
}
3、规范mapper映射文件 每个表的查询语句应该在自己的mapper文件下我们以上使用的对两个表查询的SQL语句定义在了一个mapper文件中 两个mapper文件的SQL语句可通过nameSpace属性值调用 ?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.EmpMapperselect idselectEmp resultTypeempselect * from emp where deptno #{id}/select/mapper
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.mybatis.mapper.DeptMapperresultMap iddeptMap typedept autoMappingtruecollection propertyemps ofTypeemp selectcom.mybatis.mapper.EmpMapper.selectEmp columnid fetchTypelazyid propertyid columnid/idresult columnename propertyename/resultresult propertyage columnage/resultresult columndeptno propertydeptno/result/collection/resultMapselect idselect resultMapdeptMapselect * from dept/select/mapper
