Mybatis的底层是JDBC,关于JDBC的操作,我们都知道有PreparedStatement (预处理)和Statement这两个,PreparedStatement可以防止SQL注入的危险,而在Mybatis中,传入参数的两种方式#{参数}、KaTeX parse error: Expected 'EOF', got '#' at position 6: {参数}。#̲对应的是采用PreparedS…{参数}对应的是Statement的方式。推荐使用#的方式。
相关用法:
使用#{}传入多个参数
如果Mapper接口中有多个参数,无论参数名是什么,在Mapper.xml文件中,参数都是arg0、arg1…或者param1、param2…
如果非要使用自己的参数名,可以通过@Param注解自定义
EmpMapper接口
public Integer addEmp1(String ename,String job) throws IOException;
public Integer addEmp2(String ename,String job) throws IOException;
采用#传入多个参数,在接口中对应的方法内有多个参数,无论参数名是什么,在Mapper.xml文件中,参数都是arg0、arg1…或者param1、param2…
<insert id="addEmp1">
insert into emp(ename,job)values(#{arg0},#{arg1})
</insert>
<insert id="addEmp2">
insert into emp(ename,job)values(#{param1},#{param2})
</insert>
如果非要使用自己的参数名,可以通过@Param注解自定义
public Integer addEmp4(@Param("ename")String ename,@Param("job")String job) throws IOException;
<insert id="addEmp4">
insert into emp(ename,job)values(#{ename},#{job})
</insert>
这边要说明一下,如何接口中对应的方法传入的参数是pojo对象,而映射文件里的是该对象的属性,则可以直接使用属性名
public Integer addEmp(Emp emp) throws IOException;
<insert id="addEmp" parameterType="emp">
insert into emp(ename,job)values(#{ename},#{job})
</insert>
##使用传入多个参数如果使用,需要在Mapper种指定参数名字,如果没有指定@Param注解就会报错
public Emp getEmpById(@Param("empno")Integer empno) throws IOException;
<select id="getEmpById" resultType="emp">
select * from emp where empno=${empno}
</select>
这里如果使用$,只是单纯的指定@Param注解,进行新增和修改等DML操作,还是会报错
##包装类
pojo
public class UserWrapper {
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
映射文件
<!-- 从包装对象中获取信息 包装对象的别名. 取信息 -->
<insert id="addUser4" parameterType="UserWrapper">
insert into t_user(name,age,favorites)values(#{user.name},#{user.age}
,#{user.favorites})
</insert>
不建议使用Map做参数。
##返回数据
###resultType
对于简单数据类型,例如查询总记录数、查询某一个用户名这一类返回值是一个基本数据类型的,直接写Java中的基本数据类型即可。
如果返回的是一个对象或者集合,并且对象中的属性和查询的字段名是一一对应的,那么resultType也可以直接写一个对象。
接口:
public Emp getEmpById(@Param("empno")Integer empno) throws IOException;
public List<Emp> selEmp() throws IOException;
映射文件:
<select id="getEmpById" resultType="emp">
select * from emp where empno=${empno}
</select>
<select id="selEmp" resultType="emp" resultMap="baseMap">
select * from emp
</select>
###resultMap
resultMap主要用来解决属性名和字段名不一致以及一对多、一对一查询等问题 字段名不一致时,首先可以通过取别名解决.
pojo
package com.zsl.pojo;
import java.util.List;
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgrA;//这里与数据库字段名称不同,数据库字段为mgr
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Emp() {
super();
// TODO Auto-generated constructor stub
}
public Integer getMgrA() {
return mgrA;
}
public void setMgrA(Integer mgrA) {
this.mgrA = mgrA;
}
public Emp(Integer empno, String ename, String job, Integer mgrA) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
// this.mgrA = mgrA;
}
@Override
public String toString() {
return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + "]";
//+ ", mgrA=" + mgrA
}
}
Pojo属性名mgrA在数据表中字段名为mgr,
可以发现pojo与数据表出现属性名和字段名不一致的问题
解决办法一:查询时,可以给查询结果取别名
这种方式,可以解决问题,但是有缺陷,最大缺陷是不能复用(可以sql片段解决,但是不完美)。
<select id="selEmp3" resultType="emp">
select empno empno,ename ename,job job,mgr mgrA from emp
</select>
解决办法二:最佳解决方案就是使用resultMap
在映射文件中进行配置:
resultMap
<resultMap type="emp" id="baseMap">
<id column="empno" property="empno" />
<result property="ename" column="ename" />
<result property="job" column="job" />
<result property="mgrA" column="mgr" />
</resultMap>
可以类似这么去使用:
<select id="selEmp" resultType="emp" resultMap="baseMap">
select * from emp
</select>
<select id="getEmpById" parameterType="int" resultType="emp">
select empno,ename,job,pwd from emp where empno=#{empno}
</select>
resultType:返回值类型
parameterType:参数类型
这里使用了别名,取别名可以在全局配置文件中自定义别名
<!-- 自定义别名 -->
<typeAliases>
<typeAlias type="com.zsl.pojo.Emp" alias="emp"/>
</typeAliases>
##主键回写
一般情况下,主键有两种生成方式:
1.主键自增长
2.自定义主键(一般可以使用UUID)
如果是第二种,主键一般是在Java代码中生成,然后传入数据库执行,如果是第一个主键自增长,此时,Java可能需要知道数据添加成功后的主键。 在MyBatis中,可以通过主键回填来解决这个问题(推荐)。
方法一:
该方法不适用于Oracle数据库,只适用于MySQL,因为Oracle数据库不会默认主键自增长,而是使用的是序列,而MySQL默认支持主键自增长
<!-- useGeneratedKeys:使用生成的主键 keyProperty="id":将生成的主键的值保存到对象的id属性中 -->
<insert id="addEmp" parameterType="emp" useGeneratedKeys="true"
keyProperty="empno">
insert into emp(ename,job)values(#{ename},#{job})
</insert>
方法二:可以利用MySQL自带的==last_insert_id()==函数查询刚刚插入的id
<insert id="addEmp6" parameterType="emp">
<selectKey keyProperty="empno" resultType="int">
select
LAST_INSERT_ID()
</selectKey>
insert into emp(ename,job)values(#{ename},#{job})
</insert>
全部代码:
EmpMapper接口
package com.zsl.dao;
import java.io.IOException;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.zsl.pojo.Emp;
public interface EmpMapper {
public Integer addEmp(Emp emp) throws IOException;
public Integer deleteEmpById(Integer empno) throws IOException;
public Integer updateEmprById(Emp emp) throws IOException;
public Emp getEmpById(@Param("empno")Integer empno) throws IOException;
public Integer addEmp1(String ename,String job) throws IOException;
public Integer addEmp2(String ename,String job) throws IOException;
public Integer addEmp3(@Param("ename")String ename,@Param("job")String job) throws IOException;
public Integer addEmp4(@Param("ename")String ename,@Param("job")String job) throws IOException;
public List<Emp> selEmp() throws IOException;
public Emp getEmpById2(@Param("empno")Integer empno) throws IOException;
public Emp getEmpById3(Emp emp) throws IOException;
public Integer updateEmprById2(Emp emp) throws IOException;
public Emp getEmpById4(Emp emp) throws IOException;
public Integer updateEmprById3(Emp emp) throws IOException;
// 如果不指定@Param 默认是array
public List<Emp> getEmpById5(@Param("empnos")List<Integer> empno);
public int addEmp6(@Param("emps")List<Emp> emps);
public List<Emp> selEmp1() throws IOException;
public Emp getEmpById6()throws IOException;
}
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.zsl.dao.EmpMapper">
<!-- <insert id="addEmp" parameterType="emp">
insert into emp(ename,job)values(#{ename},#{job})
</insert> -->
<delete id="deleteEmpById" parameterType="int">
delete from emp where empno=#{empno}
</delete>
<update id="updateEmprById" parameterType="emp">
update emp set name = #{ename} where empno=#{empno}
</update>
<select id="getEmpById" resultType="emp">
select * from emp where empno=${empno}
</select>
<insert id="addEmp1">
insert into emp(ename,job)values(#{arg0},#{arg1})
</insert>
<insert id="addEmp2">
insert into emp(ename,job)values(#{param1},#{param2})
</insert>
<insert id="addEmp3">
insert into emp(ename,job)values(${ename},${job})
</insert>
<insert id="addEmp4">
insert into emp(ename,job)values(#{ename},#{job})
</insert>
<select id="selEmp3" resultType="emp">
select empno empno,ename ename,job job,mgr mgrA from emp
</select>
<resultMap type="emp" id="baseMap">
<id column="empno" property="empno" />
<result property="ename" column="ename" />
<result property="job" column="job" />
<result property="mgrA" column="mgr" />
</resultMap>
<select id="selEmp" resultType="emp" resultMap="baseMap">
select * from emp
</select>
<!-- useGeneratedKeys:使用生成的主键 keyProperty="id":将生成的主键的值保存到对象的id属性中 -->
<insert id="addEmp" parameterType="emp" useGeneratedKeys="true"
keyProperty="empno">
insert into emp(ename,job)values(#{ename},#{job})
</insert>
<insert id="addEmp6" parameterType="emp">
<selectKey keyProperty="empno" resultType="int">
select
LAST_INSERT_ID()
</selectKey>
insert into emp(ename,job)values(#{ename},#{job})
</insert>
<select id="getEmpById2" resultType="emp">
SELECT * FROM emp WHERE 1=1
<if test="empno != null">
AND empno = #{empno}
</if>
</select>
<select id="getEmpById3" resultType="emp" parameterType="emp">
SELECT * FROM EMP
<where>
<choose>
<when test="empno != null">
AND empno like #{empno}
</when>
<when test="ename != null">
AND ename like #{ename}
</when>
<otherwise>
AND job = "zz"
</otherwise>
</choose>
</where>
</select>
<update id="updateEmprById2" parameterType="emp">
UPDATE emp
<set>
<if test="ename!=null"> ename=#{ename},</if>
<if test="job!=null"> job=#{job},</if>
</set>
<where>
<if test="empno!=null">
empno=#{empno};
</if>
</where>
</update>
<!-- 代替where -->
<select id="getEmpById4" resultType="emp" parameterType="emp">
SELECT * FROM emp
<!-- <where> <if test="username!=null"> and name = #{username} </if> </where> -->
<trim prefix="where" prefixOverrides="AND |OR ">
<if test="empno != null">
and empno = #{empno}
</if>
<if test="ename!=null">
AND ename = #{ename}
</if>
</trim>
</select>
<!-- 代替set -->
<update id="updateEmprById3" parameterType="emp">
update emp
<trim prefix="set" suffixOverrides=",">
<if test="ename!=null">
ename = #{ename},
</if>
<if test="job != null">
job = #{job}
</if>
</trim>
<trim prefix="where" prefixOverrides="AND |OR ">
<if test="empno != null">
and empno = #{empno}
</if>
</trim>
</update>
<select id="getEmpById5" resultType="emp">
select * from emp where empno in
<foreach collection="empnos" open="(" close=")" separator=","
item="empno">
#{empno}
</foreach>
</select>
<insert id="addEmp6">
insert into emp(ename,job)values
<foreach collection="emps" item="emp" separator=",">
(#{emp.ename},#{emp.job})
</foreach>
</insert>
<sql id="baseSql">
empno,ename,job
</sql>
<select id="selEmp1" resultType="emp">
select
<include refid="baseSql"/>
from emp
</select>
<select id="getEmpById6" resultType="emp">
<!-- 声明了一个参数empno 在后面就可以使用了 -->
<bind name="empno" value="7975" />
select * from emp where empno=${empno}
</select>
</mapper>
全局配置文件
mybatis-cfg.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 导入外部资源 -->
<properties resource="db.properties"/>
<!-- 自定义别名 -->
<typeAliases>
<typeAlias type="com.zsl.pojo.Emp" alias="emp"/>
</typeAliases>
<!-- 环境 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${userName}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- 注册映射文件 -->
<mappers>
<mapper resource="EmpMapper.xml"/>
</mappers>
</configuration>
DBUtils工具类
package com.zsl.utils;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class DbUtils {
public static SqlSession getSession() throws IOException {
// TODO Auto-generated method stub
// 1.通过Resources对象加载配置文件
InputStream inputStream = Resources.getResourceAsStream("mybatis-cfg.xml");
// 2.获取SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
// 3.通过SqlSessionFactory对象获取SQLSession对象
SqlSession session = factory.openSession();
return session;
}
public static void commit(SqlSession session) {
// TODO Auto-generated method stub
session.commit();
}
public static void close(SqlSession session) {
// TODO Auto-generated method stub
session.close();
}
}
测试类Test
package com.zsl.test;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
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 com.zsl.dao.EmpMapper;
import com.zsl.pojo.Emp;
import com.zsl.utils.DbUtils;
public class Test {
public static void main(String[] args) throws IOException {
Test test = new Test();
System.out.println(test.addUser3());
}
public Integer addUser1() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Integer integer = mapper.addEmp1("zsladd1", "程序员1");
session.commit();
session.close();
return integer;
}
public Integer addUser2() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Integer integer = mapper.addEmp2("zsladd2", "程序员2");
session.commit();
session.close();
return integer;
}
public Integer addUser3() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Integer integer = mapper.addEmp3("zsladd3", "程序员3");
session.commit();
session.close();
return integer;
}
public Integer addUser4() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Integer integer = mapper.addEmp4("zsladd4", "程序员4");
session.commit();
session.close();
return integer;
}
public Emp getEmpById() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpById(7902);
session.close();
return emp;
}
public List<Emp> selEmp() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
List<Emp> list = mapper.selEmp();
return list;
}
public Integer addUser() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setEname("zsl00");
emp.setJob("程序员00");
Integer integer = mapper.addEmp(emp);
System.out.println("该条记录主键:" + emp.getEmpno());
session.commit();
session.close();
return integer;
}
public Emp getEmpById2() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpById2(7900);
session.close();
return emp;
}
public Emp getEmpById3() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp2 = new Emp();
emp2.setEname("JAMES");
Emp emp = mapper.getEmpById3(emp2);
return emp;
}
public Integer updateEmprById2() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setEmpno(7941);
emp.setEname("zsl55");
emp.setJob("程序员55");
Integer integer = mapper.updateEmprById2(emp);
session.commit();
session.close();
return integer;
}
public Integer updateEmprById3() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setEmpno(7941);
emp.setEname("zsl77");
emp.setJob("程序员77");
Integer integer = mapper.updateEmprById2(emp);
session.commit();
session.close();
return integer;
}
public Emp getEmpById4() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp2 = new Emp();
emp2.setEname("JAMES");
Emp emp = mapper.getEmpById3(emp2);
session.close();
return emp;
}
public List<Emp> getEmpById5() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
List<Integer> list = new ArrayList<>();
list.add(7969);
list.add(7970);
list.add(7971);
List<Emp> empList = mapper.getEmpById5(list);
session.close();
return empList;
}
public Integer addEmp6() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
List<Emp> emps = new ArrayList<>();
Emp emp = new Emp();
emp.setEname("zsl88");
emp.setJob("程序员88");
emps.add(emp);
Emp empA = new Emp();
empA.setEname("zsl99");
empA.setJob("程序员99");
emps.add(empA);
int addEmp6 = mapper.addEmp6(emps);
session.commit();
session.close();
return addEmp6;
}
public List<Emp> selEmp1() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
List<Emp> list = mapper.selEmp1();
session.close();
return list;
}
public Emp getEmpById6() throws IOException {
// TODO Auto-generated method stub
SqlSession session = DbUtils.getSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.getEmpById6();
session.close();
return emp;
}
}