列表及分页
1.index.jsp(请求)
......
<a href="emps/1">展示员工信息</a>
......
MyBatis | 分页插件PageHelper及PageHelper模拟分页
分页插件PageHelper及PageHelper模拟分页
1.导入相关包
1)pagehelper-x.x.x.jar
2)jsqlparser-0.9.5.jar
2)在MyBatis全局配置文件mybatis-config.xml中配置分页插件
3)使用PageHelper提供的方法进行分页
4)可以使用更强大的PageInfo封装返回结果
模拟分页
PageUtil.java
package com.atguigu.test;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import com.atguigu.bean.Emp;
import com.github.pagehelper.PageInfo;
/**
* 首页 上一页 1 2 3 4 5 下一页 末页
*/
public class PageUtil {
public static String getPageInfo(PageInfo<Emp> pageInfo, HttpServletRequest request){
//String path = request.getContextPath() + "/";
String path = "/SSM/";
StringBuilder builder = new StringBuilder();
//拼接首页
builder.append("<a href='"+path+"emps/1'>首页</a>");
builder.append(" ");
//拼接上一页
if(pageInfo.isHasPreviousPage()){
builder.append("<a href='"+path+"emps/"+pageInfo.getPrePage()+"'>上一页 </a>");
builder.append(" ");
}else{
builder.append("上一页");
builder.append(" ");
}
//拼接页码
int[] nums = pageInfo.getNavigatepageNums();
for(int i : nums){
if(i == pageInfo.getPageNum()){
builder.append("<a style='color:red;' href='"+path+"emps/"+i+"'>"+i+"</a>");
builder.append(" ");
}else{
builder.append("<a href='"+path+"emps/"+i+"'>"+i+"</a>");
builder.append(" ");
}
}
//拼接下一页
if(pageInfo.isHasNextPage()){
builder.append("<a href='"+path+"emps/"+pageInfo.getNextPage()+"'>下一页</a>");
builder.append(" ");
}else{
builder.append("下一页");
builder.append(" ");
}
//拼接尾页
builder.append("<a href='"+path+"emps/"+pageInfo.getPages()+"'>尾页</a>");
builder.append(" ");
return builder.toString();
}
}
TestPage.java
package com.atguigu.test;
import java.io.InputStream;
import java.util.Arrays;
import java.util.List;
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.Test;
import com.atguigu.bean.Emp;
import com.atguigu.mapper.EmpMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
public class TestPage {
@Test
public void testPage() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
PageHelper.startPage(2, 2);
List<Emp> list = mapper.getAllEmp();
PageInfo<Emp> pageInfo = new PageInfo<>(list, 5);
// System.out.println(pageInfo);
// System.out.println(Arrays.toString(pageInfo.getNavigatepageNums()));
System.out.println(PageUtil.getPageInfo(pageInfo, null));
for (Emp emp : list) {
System.out.println(emp);
}
}
public SqlSessionFactory getSqlSessionFactory() throws Exception{
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
return sqlSessionFactory;
}
}
返回结果
字符串：
<a href='/SSM/emps/1'>首页</a> <a href='/SSM/emps/1'>上一页 </a> <a href='/SSM/emps/1'>1</a> <a style='color:red;' href='/SSM/emps/2'>2</a> <a href='/SSM/emps/3'>3</a> <a href='/SSM/emps/4'>4</a> <a href='/SSM/emps/5'>5</a> <a href='/SSM/emps/3'>下一页</a> <a href='/SSM/emps/6'>尾页</a>
浏览器显示：
MyBatis | 逆向工程
逆向工程
1.项目目录
2.导入逆向工程的jar包
mybatis-generator-core-1.3.2.jar
3.编写MBG的配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="DB2Tables" targetRuntime="MyBatis3">
<!-- 设置连接数据库的信息 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/ssm"
userId="root"
password="root">
</jdbcConnection>
<javaTypeResolver >
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- javabean的生成策略 -->
<javaModelGenerator targetPackage="com.atguigu.bean" targetProject=".\src">
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- 映射文件的生成策略 -->
<sqlMapGenerator targetPackage="com.atguigu.mapper" targetProject=".\conf">
<property name="enableSubPackages" value="true" />
</sqlMapGenerator>
<!-- mapper接口的生成策略 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.atguigu.mapper" targetProject=".\src">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
<!-- 设置要将数据库中的哪张表逆向生成哪一个javabean -->
<table tableName="emp" domainObjectName="Emp"></table>
<table tableName="dept" domainObjectName="Dept"></table>
</context>
</generatorConfiguration>
4.运行代码生成器生成代码
@Test
public void testMBG() throws Exception {
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
File configFile = new File("mbg.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
}
5.逆向工程的使用
基本查询与带条件查询测试
@Test
public void testCRUD() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
// Emp emp = mapper.selectByPrimaryKey(2);
// System.out.println(emp);
EmpExample example = new EmpExample();
Criteria c1 = example.createCriteria();
c1.andEnameLike("%a%");
c1.andSexEqualTo("1");
Criteria c2 = example.createCriteria();
c2.andDidEqualTo(2);
example.or(c2);
List<Emp> list = mapper.selectByExample(example);
for (Emp emp : list) {
System.out.println(emp);
}
//查询全部
// List<Emp> list1 = mapper.selectByExample(null);
// for (Emp emp : list1) {
// System.out.println(emp);
// }
}
MyBatis | 缓存
缓存
一级缓存
mybatis中的一级缓存默认开启，是SqlSession级别的，即同一个SqlSession对于一个SQL语句执行之后就会存储在缓存中，
下次执行相同的sql，直接从缓存中取。
一级缓存失效的几种情况：
（1）不同的SqlSession对应不同的一级缓存
（2）同一个SqlSession但是查询条件不同
（3）同一个SqlSession两次查询期间执行了任何一次增删改操作，会自动将缓存清空
（4）同一个SqlSession两次查询期间手动清空了缓存
二级缓存
mybatis的二级缓存默认不开启，需要设置：
1）全局配置文件中开启二级缓存
2）需要使用二级缓存的映射文件处使用cache配置缓存
3）POJO需要实现Serializable接口
注意：二级缓存在 SqlSession 关闭或提交之后才会生效
1)全局setting的cacheEnable：
配置二级缓存的开关，一级缓存一直是打开的。
2)select标签的useCache属性：
配置这个select是否使用二级缓存。一级缓存一直是使用的
3)sql标签的flushCache属性：
增删改默认flushCache=true。sql执行以后，会同时清空一级和二级缓存。
查询默认 flushCache=false。
4)sqlSession.clearCache()：只是用来清除一级缓存。
MyBatis | 动态SQL
动态SQL
1.项目目录
2.EmpMapper.java
package com.atguigu.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atguigu.bean.Emp;
public interface EmpMapper {
//根据eid,ename,age,sex多条件查询员工信息
List<Emp> getEmpListByMoreTJ(Emp emp);
//根据eid,ename,age,sex其中一个查询员工信息
List<Emp> getEmpListByChoose(Emp emp);
//添加员工信息，将0|1-->女|男
void insertEmp(Emp emp);
//根据eid所组成的字符串进行批量删除
void deleteMoreEmp(String eids);
//通过list集合实现批量删除
//void deleteMoreByList(List<Integer> eids);
void deleteMoreByList(@Param("eids")List<Integer> eids);
//批量添加
void insertMoreByArray(Emp[] emps);
//批量修改
void updateMoreByArray(@Param("emps")Emp[] emps);
}
3.EmpMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.EmpMapper">
<!--
<sql id=""></sql>:设置一段SQL片段，即公共SQL，可以被当前映射文件中所有的SQL语句所访问
-->
<sql id="empColumns">
select eid,ename,age,sex,did from emp
</sql>
<!-- List<Emp> getEmpListByMoreTJ(); -->
<!--
多条件查询：若页面中没有设置此条件，SQL语句中一定不能有该条件
-->
<!--
<if test=""></if>:通过test表达式拼接SQL
<where>:添加where关键字并去掉多余的and
<trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">:截取并拼接
prefix:在操作的SQL语句前加入某些内容
suffix:在操作的SQL语句后加入某些内容
prefixOverrides:把操作的SQL语句前的某些内容去掉
suffixOverrides:把操作的SQL语句后的某些内容去掉
-->
<select id="getEmpListByMoreTJ" resultType="Emp">
<!-- select eid,ename,age,sex,did from emp
where 1 = 1
<if test="eid != null">
and eid=#{eid}
</if>
<if test="ename != null and ename != ''">
and ename=#{ename}
</if>
<if test="age != null">
and age=#{age}
</if>
<if test="sex == '1' or sex == '0'">
and sex=#{sex}
</if> -->
<!-- select eid,ename,age,sex,did from emp
<where>
<if test="eid != null">
and eid=#{eid}
</if>
<if test="ename != null and ename != ''">
and ename=#{ename}
</if>
<if test="age != null">
and age=#{age}
</if>
<if test="sex == '1' or sex == '0'">
and sex=#{sex}
</if>
</where> -->
<!-- select eid,ename,age,sex,did from emp -->
<include refid="empColumns"></include>
<trim prefix="where" suffixOverrides="and|or">
<if test="eid != null">
eid=#{eid} and
</if>
<if test="ename != null and ename != ''">
ename=#{ename} and
</if>
<if test="age != null">
age=#{age} or
</if>
<if test="sex == '1' or sex == '0'">
sex=#{sex}
</if>
</trim>
</select>
<!-- List<Emp> getEmpListByChoose(Emp emp); -->
<!--
<choose>选择某一个when或otherwise执行
<when test=""></when>：通过test表达式拼接SQL
.
.
.
<otherwise></otherwise>:当when都不符合条件，就会选择otherwise拼接SQL语句
</choose>
-->
<select id="getEmpListByChoose" resultType="Emp">
select eid,ename,age,sex from emp
where
<choose>
<when test="eid != null">
eid = #{eid}
</when>
<when test="ename != null and ename != ''">
ename = #{ename}
</when>
<when test="age != null">
age = #{age}
</when>
<otherwise>
sex = #{sex}
</otherwise>
</choose>
</select>
<!-- void insertEmp(Emp emp); -->
<insert id="insertEmp">
insert into emp(eid,ename,age,sex) values(
null,
#{ename},
#{age},
<choose>
<when test="sex == 0">'女'</when>
<when test="sex == 1">'男'</when>
<otherwise>'不详'</otherwise>
</choose>
)
</insert>
<!-- void deleteMoreEmp(String eids); -->
<delete id="deleteMoreEmp">
delete from emp where eid in (${value})
</delete>
<!-- void deleteMoreByList(List<Integer> eids); -->
<!--
<foreach collection="eids" item="" close="" open="" separator="" index=""></foreach>
对一个数组或集合进行遍历
collection:指定要遍历的集合或数组
item:设置别名
close:设置循环体的结束内容
open:设置循环体的开始内容
separator:设置每一次循环之间的分隔符
index:若遍历的是list集合，index代表下标；若遍历的是map结合，index代表键
-->
<!--
delete from emp where eid in ();
delete from emp where eid = 1 or eid = 2 or eid = 3
-->
<delete id="deleteMoreByList">
<!-- delete from emp where eid in
<foreach collection="list" item="eid" separator="," open="(" close=")">
#{eid}
</foreach> -->
delete from emp where
<!-- <foreach collection="list" item="eid" separator="or">
eid = #{eid}
</foreach> -->
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
<!--
delete:
delete from emp where eid in ();
delete from emp where eid = 1 or eid = 2 or eid = 3
select:
select * from emp where eid in ();
select * from emp where eid = 1 or eid = 2 or eid = 3
update:
把每条数据修改成相同内容
update emp set ... where eid in ();
update emp set ... where eid = 1 or eid = 2 or eid = 3
把每条数据修改成对应内容,注意必须在链接地址（url）后添加?allowMultiQueries=true
update emp set ... where eid=1;
update emp set ... where eid=2;
update emp set ... where eid=3;
insert:
insert into emp values(),(),()
-->
<!-- void insertMoreByArray(Emp[] emps); -->
<insert id="insertMoreByArray">
insert into emp values
<foreach collection="array" item="emp" separator=",">
(null,#{emp.ename},#{emp.age},#{emp.sex},1)
</foreach>
</insert>
<!-- void updateMoreByArray(@Param("emps")Emp[] emps); -->
<update id="updateMoreByArray">
<foreach collection="emps" item="emp">
update emp set ename=#{emp.ename}, age=#{emp.age}, sex=#{emp.sex} where eid = #{emp.eid};
</foreach>
</update>
</mapper>
4.TestDynamicSQl.java
package com.atguigu.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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.Test;
import com.atguigu.bean.Emp;
import com.atguigu.mapper.EmpMapper;
public class TestDynamicSQL {
@Test
public void TestMore() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
//批量删除
// List<Integer> eids = new ArrayList<>();
// eids.add(10);
// eids.add(16);
// eids.add(17);
// mapper.deleteMoreByList(eids);//只能删除id为1的员工
//批量添加
//Emp[] emps = new Emp[3];
//Emp[] emps = new Emp[]{};
//Emp[] emps = {};
// Emp emp1 = new Emp(null, "a",23,"男");
// Emp emp2 = new Emp(null, "aa",23,"男");
// Emp emp3 = new Emp(null, "aaa",23,"男");
// Emp[] emps = {emp1, emp2, emp3};
// mapper.insertMoreByArray(emps);
//批量修改(需要在jdbc.url后加?allowMultiQueries=true)
//jdbc.url=jdbc:mysql://localhost:3306/ssm?allowMultiQueries=true
Emp emp1 = new Emp(20, "a1",233,"女");
Emp emp2 = new Emp(21, "aa1",233,"女");
Emp emp3 = new Emp(22, "aaa1",233,"女");
Emp[] emps = {emp1, emp2, emp3};
mapper.updateMoreByArray(emps);
}
@Test
public void TestChoose() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
//测试1
//Emp emp = new Emp();
// emp.setEid(1);
// emp.setEname("张三");
// emp.setAge(12);
//emp.setSex("1");
// List<Emp> list = mapper.getEmpListByChoose(emp);
// for (Emp e : list) {
// System.out.println(e);
// }
//测试2
// Emp emp = new Emp();
// emp.setEname("张三");
// emp.setAge(12);
// emp.setSex("1");
// mapper.insertEmp(emp);
Emp emp = new Emp();
emp.setEname("张三三");
emp.setAge(12);
emp.setSex("123");
mapper.insertEmp(emp);
}
@Test
public void TestIf() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp();
//emp.setEid(1);
emp.setEname("张三");
emp.setAge(12);
//emp.setSex("1");
List<Emp> list = mapper.getEmpListByMoreTJ(emp);
for (Emp e : list) {
System.out.println(e);
}
}
public SqlSessionFactory getSqlSessionFactory() throws Exception{
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
return sqlSessionFactory;
}
}
MyBatis | 一对多自定义映射、分步查询和延迟加载
一对多自定义映射、分步查询和延迟加载
1.项目目录
2.Dept.java
在”一”方添加List<多>
package com.atguigu.bean;
import java.util.List;
public class Dept {
private Integer did;
private String dname;
private List<Emp> emps;
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
@Override
public String toString() {
return "Dept [did=" + did + ", dname=" + dname + ", emps=" + emps + "]";
}
}
3.EmpDeptMapper.java
package com.atguigu.mapper;
import java.util.List;
import com.atguigu.bean.Dept;
import com.atguigu.bean.Emp;
public interface EmpDeptMapper {
List<Emp> getAllEmp();
Emp getEmpStep(String eid);
Dept getDeptEmpsByDid(String did);
Dept getOnlyDeptByDid(String did);//测试一对多
List<Emp> getEmpListByDid(String did);//测试一对多
}
4.EmpDeptMapper.xml
在映射文件中做以下配置：
<resultMap type="Dept" id="deptMapStep">
<id column="did" property="did" />
<result column="dname" property="dname" />
<!-- <collection property="emps" select="com.atguigu.mapper.EmpDeptMapper.getEmpListByDid" column="did"></collection> -->
<!--
fetchType:
lazy 延迟加载
eager 不进行延迟加载
-->
<collection property="emps" select="com.atguigu.mapper.EmpDeptMapper.getEmpListByDid" column="{did=did}" fetchType="lazy"></collection>
</resultMap>
<!-- Dept getOnlyDeptByDid(String did); -->
<select id="getOnlyDeptByDid" resultMap="deptMapStep">
select did,dname from dept where did=#{did}
</select>
<!-- List<Emp> getEmpListByDid(String did); -->
<select id="getEmpListByDid" resultType="Emp">
select eid,ename,age,sex from emp where did = #{did}
</select>
5.TestEmpDept.java
package com.atguigu.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.atguigu.bean.Dept;
import com.atguigu.bean.Emp;
import com.atguigu.mapper.EmpDeptMapper;
public class TestEmpDept {
public static void main(String[] args) throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpDeptMapper mapper = sqlSession.getMapper(EmpDeptMapper.class);
// List<Emp> empList = mapper.getAllEmp();
// System.out.println(empList);
// Emp emp = mapper.getEmpStep("3");
// System.out.println(emp.getEname());
// System.out.println(emp.getDept());
// Dept dept = mapper.getDeptEmpsByDid("2");
// System.out.println(dept);
Dept dept = mapper.getOnlyDeptByDid("3");
//System.out.println(dept);
System.out.println(dept.getDname());
System.out.println(dept.getEmps());
}
}
MyBatis | 分步查询、分步查询延迟加载
分步查询、分步查询延迟加载
项目目录
1.mybatis-config.xml
分步查询延迟加载需要在MyBatis核心配置文件中做以下配置：
<settings>
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"></setting>
<!-- 是否查询所有数据 -->
<setting name="aggressiveLazyLoading" value="false"></setting>
</settings>
2.DeptMapper.java
public interface DeptMapper {
Dept getDeptByDid(String did);
}
3.DeptMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.DeptMapper">
<select id="getDeptByDid" resultType="Dept">
select did,dname from dept where did=#{did}
</select>
</mapper>
4.EmpDeptMapper.java
public interface EmpDeptMapper {
List<Emp> getAllEmp();
Emp getEmpStep(String eid);//分步查询
}
5.EmpDeptMapper.xml
添加查询语句
<resultMap type="Emp" id="empMapStep">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<!--
select:分步查询的SQL的id，即接口全限定名.方法名或者namespace.SQL的id
column:分步查询的条件，注意：此条件必须是从数据库查询过的
-->
<association property="dept" select="com.atguigu.mapper.DeptMapper.getDeptByDid" column="did"></association>
</resultMap>
<!-- Emp getEmpStep(String eid); -->
<select id="getEmpStep" resultMap="empMapStep">
select eid,ename,age,sex,did from emp where eid = #{eid}
</select>
6.TestEmpDept.java
public class TestEmpDept {
public static void main(String[] args) throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpDeptMapper mapper = sqlSession.getMapper(EmpDeptMapper.class);
Emp emp = mapper.getEmpStep("3");
System.out.println(emp.getEname());
System.out.println(emp.getDept());
}
}
7.运行结果
执行emp.getEname()运行结果为前四行
再执行emp.getDept()运行结果为八行
DEBUG 04-06 09:48:10,087 ==> Preparing: select eid,ename,age,sex,did from emp where eid = ? (BaseJdbcLogger.java:145)
DEBUG 04-06 09:48:10,102 ==> Parameters: 3(String) (BaseJdbcLogger.java:145)
DEBUG 04-06 09:48:10,147 <== Total: 1 (BaseJdbcLogger.java:145)
王五
DEBUG 04-06 09:48:10,147 ==> Preparing: select did,dname from dept where did=? (BaseJdbcLogger.java:145)
DEBUG 04-06 09:48:10,148 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:145)
DEBUG 04-06 09:48:10,149 <== Total: 1 (BaseJdbcLogger.java:145)
Dept [did=3, dname=研发部]
python | 统计字符串中各个字符出现的次数并返回出现次数最多的字符
MyBatis | 多对一查询自定义映射
多对一查询自定义映射
1.项目目录
2.Emp.java
package com.atguigu.bean;
public class Emp {
private Integer eid;
private String ename;
private Integer age;
private String sex;
private Dept dept;
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public Integer getEid() {
return eid;
}
public void setEid(Integer eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Emp [eid=" + eid + ", ename=" + ename + ", age=" + age + ", sex=" + sex + ", dept=" + dept + "]";
}
public Emp(Integer eid, String ename, Integer age, String sex) {
super();
this.eid = eid;
this.ename = ename;
this.age = age;
this.sex = sex;
}
public Emp() {
super();
// TODO Auto-generated constructor stub
}
}
3.Dept.java
package com.atguigu.bean;
public class Dept {
private Integer did;
private String dname;
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
@Override
public String toString() {
return "Dept [did=" + did + ", dname=" + dname + "]";
}
}
4.EmpDeptMapper.java
package com.atguigu.mapper;
import java.util.List;
import com.atguigu.bean.Emp;
public interface EmpDeptMapper {
List<Emp> getAllEmp();
}
5.EmpDeptMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.atguigu.mapper.EmpDeptMapper">
<!-- <select id="getAllEmp" resultType="Emp">
select e.eid,e.ename,e.age,e.sex,e.did,d.dname from emp e left join dept d on e.did=d.did
查询结果：
[Emp [eid=1, ename=张三, age=12, sex=男, dept=null],
不能够查询出员工所对应的部门，需要进行自定义映射
</select> -->
<!-- 自定义映射-第一种方式 -->
<!-- <resultMap type="Emp" id="empMap">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<result column="did" property="dept.did"/>
<result column="dname" property="dept.dname"/>
</resultMap> -->
<!--
<resultMap>:自定义映射，处理复杂的表关系
<id/>:设置主键的映射关系，column设置字段名，property设置属性名
<result/>:设置非主键的映射关系， column设置字段名，property设置属性名
-->
<!-- 自定义映射-第二种方式 -->
<resultMap type="Emp" id="empMap">
<id column="eid" property="eid"/>
<result column="ename" property="ename"/>
<result column="age" property="age"/>
<result column="sex" property="sex"/>
<association property="dept" javaType="Dept">
<id column="did" property="did"/>
<result column="dname" property="dname"/>
</association>
</resultMap>
<!-- List<Emp> getAllEmp(); -->
<select id="getAllEmp" resultMap="empMap">
<!-- select * from emp e, dept d where e.did = d.did -->
select e.eid,e.ename,e.age,e.sex,e.did,d.dname from emp e left join dept d on e.did=d.did
</select>
</mapper>
6.TestEmpDept.java
package com.atguigu.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.atguigu.bean.Emp;
import com.atguigu.mapper.EmpDeptMapper;
public class TestEmpDept {
public static void main(String[] args) throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
EmpDeptMapper mapper = sqlSession.getMapper(EmpDeptMapper.class);
List<Emp> empList = mapper.getAllEmp();
System.out.println(empList);
}
}
MyBatis | 获取参数值的两种方式
MyBatis获取参数值的两种方式
1.${}:insert into emp values(null,admin,23,男)
Statement:
必须使用字符串拼接的方式操作SQL，一定要注意单引号问题
不能防止SQL注入
2.#{}:insert into emp values(null,?,?,?)
PreparedStatement：
可以使用通配符操作SQL，因为在为String赋值时可以自动加单引号，因此不需要注意单引号问题
可以防止SQL注入
使用建议：**建议使用#{}**，在特殊情况下，需要使用${}，例如模糊查询和批量删除
不同的参数类型，${}和#{}的不同取值方式：
1、当传输参数为单个String或基本数据类型和其包装类型
#{}：可以以任意的名字获取参数值
${}：只能以${value}或${_parameter}获取
2、当传输参数为JavaBean时
#{}和${}都可以通过属性名直接获取属性值，但是要注意${}的单引号问题
3、当传输多个参数时，mybatis会默认将这些参数放在map集合中
两种方式：
（1）键为0，1，2，3…N-1,以参数为值
（2）键为param1,param2,param3…paramN，以参数为值
#{}:#{0}、#{1}；#{param1}、#{param2}
${}:#{param1}、{param2}，但是要注意${}的单引号问题
4、当传输Map参数时
#{}和${}都可以通过键的名字直接获取值，但是要注意${}的单引号问题
5、命名参数
可以通过@Param(“key”)为map集合指定键的名字
Emp getEmpByEidAndEnameByParam(@Param(“eid”)String eid, @Param(“ename”)String ename);
6、当传输参数为List或Array,mybatis会将List或Array放在map中
List以list为键，Array以array为键
Statement与PreparedStatement
package com.atguigu.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import com.atguigu.bean.Emp;
public class TestJDBC {
public static void main(String[] args) throws Exception {
Class.forName("");
Connection conn = DriverManager.getConnection("","","");
//可以使用通配符赋值
PreparedStatement ps = conn.prepareStatement("insert into emp values(null, ?, ?,?");
ps.setString(1, "root");
ps.setString(2, "23");
ps.setString(3, "男");
ps.executeUpdate();
Statement statement = conn.createStatement();
Emp emp = new Emp(null, "a", 1, "b");
//不可以使用通配符赋值
statement.executeUpdate("insert into emp values(null, '"+emp.getEname()+"', "+emp.getAge()+", '"+emp.getSex()+"')");
}
}