【MyBatis】(二)MyBatis的SQL操作(操作各种SQL语句,动态SQL语句查询,Mapper映射器映射规则)
四、Mybatis操作各种SQL语句
1.查询
查询的标准模板<select id="" parameterType="" resultType="">SQL语句</select>
id:当前SQL定义的id,方便在代码中查找当前SQL语句
parameterType:传入SQL语句中占位符的参数类型:int,string,map,double,object对象类型
resultType:SQL语句执行完成后封装的结果类型:int,string,map,double,object对象类型
SQL语句字段引用模板<!-- 作用:提取多个SQL定义中相同的SQL片段 -->
<sql id="selectAll">SELECT * FROM user</sql>
详解基本案例:
1>根据id查询一个对象.?
<!-- 根据id查询一个User对象
id:代表当前这个SQL定义
#{id}:代表占位符,大括号中的id和属性名一致
parameterType:传给占位符的参数类型
resultType:SQL语句执行完成后的返回结果
include:对相同Sql语句片段的提取 -->
<select id="findUserById" parameterType="int" resultType="com.hekaikai666.bean.User">SELECT * FROM user WHERE id=#{id}</select>
<select id="findUserById" parameterType="int" resultType="user"><include refid="selectAll"/>WHERE id=#{id}</select>
// 第一个参数 Sql定义的id 第二个参数 占位符的参数值
// 通过Session工厂获取的session对象调用selectOne方法获取到一个User对象,即为查询到的对象
User user = session.selectOne("findUserById", 1);
2>查询所有数据.?
<!-- 查询所有User数据
返回list集合时,resultType写集合元素类型,mybatis会自动将其封装为集合 -->
<select id="findAll" resultType="user">SELECT * FROM user</select>
<select id="findAll" resultType="user"><include refid="selectAll"/></select>
// 如果多个映射文件有相同的sql定义id可以使用namespace.id名来
List<User> users = session.selectList("user.findAll");
3>根据名字进行模糊查询?
<!-- 根据名字进行模糊查询 -->
<select id="findThem" parameterType="java.lang.String" resultType="user"><include refid="selectAll"/>WHERE username like #{username}</select>
<!-- $:代表0到多个任意字符
_:代表1个任意字符 -->
// 通过session工厂创建的session对象调用selectList方法查询,注意%与_返回一个List集合
List<User> users = session.selectList("findThem", "%张%");
4>根据账号密码查询一个账户?
<!-- 根据账号密码查询一个账户:user对象传入 -->
<select id="findOneByUAP" parameterType="user" resultType="user"><include refid="selectAll"/>WHERE username=#{username} and password=#{password}</select>
<!-- 根据账号密码查询一个账户:map对象传入 -->
<select id="findOneByUAP" parameterType="map" resultType="user"><include refid="selectAll"/>WHERE username=#{username} and password=#{password}</select>
// 传入的对象不同,所以参数的类型和查询的方式也不同
// 传入一个Bean对象
User user = new User("张三", "123", 0, "");
List<User> u = session.selectList("findOneByUAP", user);
System.out.println(u);
session.close();
// 传入一个map集合
Map<String, String> map = new HashMap<String, String>();
map.put("username", "张三");
map.put("password", "123");
User user = session.selectOne("findOneByUAP2", map);
System.out.println(user);
session.close();
5>根据id只查询账号和密码?(封装成Map集合)
<!-- 根据id只查询账号和密码 -->
<select id="findOneById" parameterType="int" resultType="map">SELECT username,password FROM user WHERE id=#{id}</select>
// 返回值的类型为Map则需要将返回Object数据封装成一个Map对象,存放在list数组中
List<Map<String, String>> user = session.selectList("findOneById", 1);
for (Map<String, String> map : user) {
System.out.println(map);
}
session.close();
6>查询所有用户的账号和密码?
<!-- 查询所有用户的账号和密码 -->
<select id="findAllUAP" resultType="map">SELECT username,password FROM user</select>
// 不用传入参数获取到所有的对象封装成Map存入list集合中
List<Map<String, String>> users = session.selectList("findAllUAP");
for (Map<String, String> map : users) {
System.out.println(map);
}
session.close();
7>当实体类中属性名和表中字段名不一致时怎么查询?
方案1:给列起别名,别名的名字和实体类属性名相同
<select id="findAllUser1" resultType="com.hekaikai666.bean.User1">
SELECT
id u_id,
username u_username,
password u_password,
age u_age,
gender u_gender
FROM user
</select>
方案2:借助resultType标签做实体类中属性名和表中字段之间映射关系
1.实体类属性名与表中字段之间关系映射.
2.查询结果类型上resultMap="映射标签id"
<!-- type:表中每一行记录封装成什么类型对象 id:当前resultMap映射结果id -->
<resultMap type="com.hekaikai666.bean.User1" id="user1Bean">
<!-- 主键映射:property:实体类中属性名称 column:表中字段名称 -->
<id property="u_id" column="id"/>
<!-- 非主键映射: -->
<result property="u_username" column="username"/>
<result property="u_password" column="password"/>
<result property="u_age" column="age"/>
<result property="u_gender" column="gender"/>
</resultMap>
<!-- 查询所有数据封装成User1对象类型集合 -->
<select id="findUser1" resultMap="user1Bean">SELECT * FROM user</select>
8>分页查询 (分页查询还有一种拦截器查询,也是比较好的查询方式,建议参考)
<!-- 分页查询所有用户的信息 -->
<select id="getUserByLimit" parameterType="map" resultType="user">SELECT * FROM user LIMIT #{page},#{pageSize}</select>
// 获取到一个链表对象,向其中存入页码和页容值,然后进行查询
HashMap<String, Object> maps = new LinkedHashMap<String,Object>();
maps.put("page", 2);
maps.put("pageSize", 3);
List<User> lists = session.selectList("user.getUserByLimit", maps);
2.增加
<insert id="" parameterType="">SQL语句</insert>
<!-- 插入一条数据 -->
<insert id="saveUser" parameterType="user">INSERT INTO user
(username,password,age,gender) VALUES
(#{username},#{password},#{age},#{gender})
</insert>
User user = new User("宋豪", "123456", 80, "男");
int rows = session.insert("saveUser", user);
// 提交事务
session.commit();
System.out.println("成功插入数据的行数:" + rows);
// 关闭对象
session.close();
3.删除
<delete id="" parameterType="">SQL语句</delete>
<!-- 删除一条数据 -->
<delete id="deleteUserById" parameterType="int">DELETE FROM user WHERE id=#{id}</delete>
// 直接调用delete方法并传入值
int rows = session.delete("deleteUserById", 6);
System.out.println("删除记录行数:" + rows);
session.commit();
session.close();
4.修改
<update id="" parameterType="">SQL语句</update>
<!-- 根据id修改一行记录 -->
<update id="updateUser" parameterType="user">UPDATE user SET username=#{username},password=#{password},age=#{age},gender=#{gender} WHERE id=#{id}</update>
// 先根据id获取一个user对象
User user = session.selectOne("findUserById", 3);
user.setUsername("哈哈哈");
user.setPassword("呵呵呵");
user.setAge(18);
user.setGender("女");
int rows = session.update("user.updateUser", user);
System.out.println("修改记录行数:" + rows);
session.commit();
session.close();
5.其他配置
片段提取:对多条SQL定义中相同SQL片段的提取
1>.提取定义
<sql id="sqlId"></sql>
2>.引用定义
<select><include refid="sqlId"></select>
SqlMapConfig主配置文件
1>引入配置文件
a.<properties resource=""></properties>
b.使用${key}值进行使用
2>给类起别名
<typeAliases>
<typeAlias type="类型全限定名" alias="类型别名"/>
</typeAliases>
3.调用Log4j打印SQL语句
<!-- 全局系统设置:使用log4j框架打印Sql语句 -->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING" />
<!-- 开启二级缓存..延迟加载之类的. -->
</settings>
五、$和#的区别(面试重点)
1.$和#都可以充当占位符,$底层是由Statement实现的;#底层是由PrepareStatement实现的.
2.#是先编译SQL语句然后再传值,给传入的值都加上双引号;$传值是直接拼接字符串,不会给传入的值加双引号.
3.$会造成SQL注入的问题,#不会
4.只有在传入数据库对象时才使用$
4.1>按照某个表名进行查询时传入表名
4.2>按照某个列名进行查询时传入列名
5.传入非数据库参数时最好使用#不使用$符号
案例:
<!-- $和#的区别 -->
<mapper namespace="u">
<!-- 根据名字查找一个User对象 -->
<select id="findUserByName1" parameterType="string" resultType="user">SELECT * FROM user WHERE username=#{username}</select>
<select id="findUserByName" parameterType="string" resultType="user">SELECT * FROM user WHERE username=${value}</select>
<!-- 根据账号和密码查询一个账户 -->
<select id="findUserByNameAndPsw" parameterType="map" resultType="user" >SELECT * FROM user WHERE username=${username} AND password=${password}</select>
<select id="findAll" parameterType="string" resultType="user">SELECT * FROM ${user} ORDER BY age</select>
</mapper>
六、MyBatis动态SQL语句
1.什么是动态SQL
动态SQL是MyBatis特性之一,可以根据用户传入的条件,借助xml标签对SQL语句进行动态拼接,生成符合条件的SQL语句
2.动态SQL语句的用法
2.1>分支判断
<if test=""></if><!-- test中内容为True,拼接上标签体中的SQL语句,内容为false,则不再拼接 -->
<choose>
<when test=""></when>
<otherwise></otherwise>
</choose>
2.2>关键字标签
where关键字:1.充当SQL语句中的一个where关键字;2.去掉拼接完SQL语句后多余的and,or关键字.
set关键字:1.充当SQL语句中的一个set关键字;2.去掉拼接完SQL语句后多余的逗号.
2.3>案例
a.员工信息查询:如果传入了部门编号,就查询该部门下所有员工信息;如果没有传入员工部门编号,就查询所有员工信息
<!-- 如果传入了部门编号,就查询该部门下所有员工信息;如果没有传入员工部门编号,就查询所有员工信息 -->
<!-- SELECT * FROM emp [where deptno=#{deptno}] -->
<select id="findEmps" parameterType="emp" resultType="emp">SELECT * FROM emp
<!-- 判断有没有传入部门编号 -->
<if test="deptno!=0">WHERE deptno=#{deptno}</if>
</select>
b.根据薪资来查询员工信息
<!-- 根据薪资来查询员工信息,如果传入了薪资,就查询大于此薪资的员工信息,如果没有传入薪资,就查询员工薪资大于 6000 的员工信息 -->
<select id="findEmpsBySalary" parameterType="emp" resultType="emp">SELECT * FROM emp WHERE salary >
<if test="salary!=0">
#{salary}
</if>
<if test="salary==0">
6000
</if>
</select>
c.根据性别查询员工信息
<!-- 根据性别查询员工信息:如果传入了性别,就查询此性别员工的信息,如果没有传入就查询所有元工信息 -->
<select id="findEmpsBySex" parameterType="emp" resultType="emp">SELECT * FROM emp WHERE sex=
<choose>
<when test="sex!=null">#{sex}</when>
<otherwise>'男'</otherwise>
</choose>
</select>
d.根据job和salary两个条件查询员工信息(两种方式)
<!-- 根据job和salary两个条件查询员工信息:1.如果只传入job,就查询该职位下员工的信息 2.如果只传入salary,就查询薪水大于此salary员工信息 3.如果两个条件都传入了,就查询该职位下薪资大于此salary的员工信息 4.如果两个条件都没有传入,就查询所有员工的信息 -->
<select id="findEmpsByJobAndSalary" parameterType="emp" resultType="emp">SELECT * FROM emp
<if test="salary==0">
<if test="job!=null">WHERE job=#{job}</if>
<if test="job==null"></if>
</if>
<if test="job==null">
<if test="salary!=0">WHERE salary>#{salary}</if>
<if test="salary==0"></if>
</if>
<if test="salary!=0">
<if test="job!=null">WHERE salary>#{salary} AND job=#{job}</if>
</if>
</select>
<select id="findEmpsByJobAndSalary1" parameterType="emp" resultType="emp">SELECT * FROM emp WHERE 1=1
<if test="job!=null">AND job=#{job}</if>
<if test="salary!=0">AND salary=#{salary}</if>
</select>
<!-- where标签作用:1.充当一个SQL语句中的关键字.2.去掉SQL语句拼接完成后多余的and,or关键字 -->
<select id="findEmpsByJobAndSalary2" parameterType="emp" resultType="emp">SELECT * FROM emp
<where>
<if test="job!=null">AND job=#{job}</if>
<if test="salary!=0">AND salary=#{salary}</if>
</where>
</select>
e.修改Emp员工信息
<!-- 修改Emp员工信息,判断传入了几项数据:传入了几项数据就修改几项数据 -->
<!-- set标签作用:1充当SQL语句中关键字.2.可以去掉拼接完成后多余的逗号 -->
<update id="updateEmp" parameterType="emp">UPDATE emp
<set>
<if test="name!=null">name=#{name},</if>
<if test="sex!=null">sex=#{sex},</if>
<if test="job!=null">job=#{job},</if>
<if test="salary!=0">salary=#{salary},</if>
<if test="hiredate!=null">hiredate=#{hiredate},</if>
<if test="deptno!=0">deptno=#{deptno},</if></set>
WHERE id=#{id}
</update>
<!-- 根据id查询一个对象 -->
<select id="findEmpById" parameterType="int" resultType="emp">SELECT * FROM emp WHERE id=#{id}</select>
总:
<mapper namespace="com.hekaikai666.dao.i.EmpMapper">
<!-- 员工信息查询 -->
<!-- 如果传入了部门编号,就查询该部门下所有员工信息;如果没有传入员工部门编号,就查询所有员工信息 -->
<!-- SELECT * FROM emp [where deptno=#{deptno}] -->
<select id="findEmps" parameterType="emp" resultType="emp">SELECT * FROM emp
<!-- 判断有没有传入部门编号 -->
<if test="deptno!=0">WHERE deptno=#{deptno}</if>
</select>
<!-- 根据薪资来查询员工信息,如果传入了薪资,就查询大于此薪资的员工信息,如果没有传入薪资,就查询员工薪资大于 6000 的员工信息 -->
<select id="findEmpsBySalary" parameterType="emp" resultType="emp">SELECT * FROM emp WHERE salary >
<if test="salary!=0">
#{salary}
</if>
<if test="salary==0">
6000
</if>
</select>
<!-- 根据性别查询员工信息:如果传入了性别,就查询此性别员工的信息,如果没有传入就查询所有元工信息 -->
<select id="findEmpsBySex" parameterType="emp" resultType="emp">SELECT * FROM emp WHERE sex=
<choose>
<when test="sex!=null">#{sex}</when>
<otherwise>'男'</otherwise>
</choose>
</select>
<!-- 根据job和salary两个条件查询员工信息:1.如果只传入job,就查询该职位下员工的信息 2.如果只传入salary,就查询薪水大于此salary员工信息 3.如果两个条件都传入了,就查询该职位下薪资大于此salary的员工信息 4.如果两个条件都没有传入,就查询所有员工的信息 -->
<select id="findEmpsByJobAndSalary" parameterType="emp" resultType="emp">SELECT * FROM emp
<if test="salary==0">
<if test="job!=null">WHERE job=#{job}</if>
<if test="job==null"></if>
</if>
<if test="job==null">
<if test="salary!=0">WHERE salary>#{salary}</if>
<if test="salary==0"></if>
</if>
<if test="salary!=0">
<if test="job!=null">WHERE salary>#{salary} AND job=#{job}</if>
</if>
</select>
<select id="findEmpsByJobAndSalary1" parameterType="emp" resultType="emp">SELECT * FROM emp WHERE 1=1
<if test="job!=null">AND job=#{job}</if>
<if test="salary!=0">AND salary=#{salary}</if>
</select>
<!-- where标签作用:1.充当一个SQL语句中的关键字.2.去掉SQL语句拼接完成后多余的and,or关键字 -->
<select id="findEmpsByJobAndSalary2" parameterType="emp" resultType="emp">SELECT * FROM emp
<where>
<if test="job!=null">AND job=#{job}</if>
<if test="salary!=0">AND salary=#{salary}</if>
</where>
</select>
<!-- 修改Emp员工信息,判断传入了几项数据:传入了几项数据就修改几项数据 -->
<!-- set标签作用:1充当SQL语句中关键字.2.可以去掉拼接完成后多余的逗号 -->
<update id="updateEmp1" parameterType="emp">UPDATE emp
<set>
<if test="name!=null">name=#{name},</if>
<if test="sex!=null">sex=#{sex},</if>
<if test="job!=null">job=#{job},</if>
<if test="salary!=0">salary=#{salary},</if>
<if test="hiredate!=null">hiredate=#{hiredate},</if>
<if test="deptno!=0">deptno=#{deptno},</if></set>
WHERE id=#{id}
</update>
<!-- 根据id查询一个对象 -->
<select id="findEmpById" parameterType="int" resultType="emp">SELECT * FROM emp WHERE id=#{id}</select>
<!-- 查询所有对象 -->
<select id="findAll" resultType="emp">SELECT * FROM emp</select>
<!-- 添加一个对象 -->
<insert id="addEmp" parameterType="emp">INSERT INTO emp (name,sex,job,salary,hiredate,deptno) VALUES
(#{name},#{sex},#{job},#{salary},#{hiredate},#{deptno})</insert>
<!-- 删除一个对象 -->
<delete id="deleteEmpById" parameterType="int">DELETE FROM emp WHERE id=#{id}</delete>
<!-- 修改一个对象 -->
<update id="updateEmp" parameterType="emp">UPDATE emp
<set>
<if test="name!=null">name=#{name},</if>
<if test="sex!=null">sex=#{sex},</if>
<if test="job!=null">job=#{job},</if>
<if test="salary!=0">salary=#{salary},</if>
<if test="hiredate!=null">hiredate=#{hiredate},</if>
<if test="deptno!=0">deptno=#{deptno},</if></set>
WHERE id=#{id}
</update>
</mapper>
测试结果:
package com.hekaikai666.test;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.lanou.bean.Emp;
import com.lanou.dao.EmpMapper;
import com.lanou.util.MyBatisUtil;
/**
*
*@author sh
*@time 2018年9月18日上午11:40:19
**/
public class Test2 {
SqlSessionFactory factory;
SqlSession session;
@Before
public void init() {
factory=MyBatisUtil.getFactory();
session = factory.openSession();
}
@Test
public void test1() {
Emp emp=new Emp();
emp.setDeptno(30);
List<Emp> emps = session.selectList("emp.findEmps", emp);
System.out.println(emps);
}
@Test
public void test2() {
Emp emp=new Emp();
emp.setSalary(5000);
List<Emp> emps = session.selectList("emp.findEmpBySalary", emp);
System.out.println(emps);
}
@Test
public void test3() {
Emp emp=new Emp();
List<Emp> emps = session.selectList("emp.findEmpsBySex", emp);
System.out.println(emps);
}
@Test
public void test4() {
Emp emp=new Emp();
emp.setSalary(5000);
emp.setJob("销售");
List<Emp> emps = session.selectList("emp.findEmpBySalaryAndJob", emp);
System.out.println(emps);
}
@Test
public void test5() {
Emp emp = session.selectOne("emp.findEmpById", 7);
emp.setName("pop");
emp.setSex("男");
int update = session.update("emp.updateEmpById", emp);
System.out.println("修改了"+update+"条数据");
}
@Test
public void test6() {
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp findEmpById = mapper.findEmpById(7);
System.out.println(findEmpById);
}
@Test
public void test7() {
EmpMapper mapper = session.getMapper(EmpMapper.class);
mapper.addEmp(new Emp("氢气", "男", "教学", 5000, new Date(), 10));
}
@Test
public void test8() {
EmpMapper mapper = session.getMapper(EmpMapper.class);
System.out.println(mapper.findAll());
//mapper.deleteEmpById(8);
Emp emp = mapper.findEmpById(1);
emp.setName("杰克");
mapper.updateEmp(emp);
}
@After
public void end() {
session.commit();
session.close();
}
}
七、Mapper映射器映射规则(动态代理模式)
定义:通过一定的映射规则,根据我们定义的接口,可以自动生成对应的实现类,然后重写接口满足映射规则的方法,那么满足这个规则的接口一般被称为映射接口.
映射规则:
1.SQL映射文件的namespace的值必须和映射接口的全限定名(包名.接口名)一致;
2.SQL定义的ID名称必须和映射接口中的方法名一致;
3.SQL定义的parameterType类型必须和映射接口方法中的参数类型,个数一致;
4.SQL定义的resultType和映射接口方法的返回值类型一致,增删改SQL语句没有resultType,映射接口的返回值类型写void.
建议规范:
1>SQL映射文件放在和映射接口同一包下;
2>映射接口名以Mapper结尾便于区分其他接口
映射接口.java
// 查找
public Emp findEmpById(int id);
// 查找所有
public List<Emp> findAll();
// 添加
public void addEmp(Emp emp);
// 删除
public void deleteEmpById(int id);
// 修改
public void updateEmp(Emp emp);
SQL定义映射文件.xml
<!-- 根据id查询一个对象 -->
<select id="findEmpById" parameterType="int" resultType="emp">SELECT * FROM emp WHERE id=#{id}</select>
<!-- 查询所有对象 -->
<select id="findAll" resultType="emp">SELECT * FROM emp</select>
<!-- 添加一个对象 -->
<insert id="addEmp" parameterType="emp">INSERT INTO emp (name,sex,job,salary,hiredate,deptno) VALUES
(#{name},#{sex},#{job},#{salary},#{hiredate},#{deptno})</insert>
<!-- 删除一个对象 -->
<delete id="deleteEmpById" parameterType="emp">DELETE FROM emp WHERE id=#{id}</delete>
<!-- 修改一个对象 -->
<update id="updateEmp" parameterType="emp">UPDATE emp
<set>
<if test="name!=null">name=#{name},</if>
<if test="sex!=null">sex=#{sex},</if>
<if test="job!=null">job=#{job},</if>
<if test="salary!=0">salary=#{salary},</if>
<if test="hiredate!=null">hiredate=#{hiredate},</if>
<if test="deptno!=0">deptno=#{deptno},</if></set>
WHERE id=#{id}
</update>
SQL映射接口的实现类(测试类).java
* 按照id查询一个员工
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.findEmpById(1);
* 查询所有员工对象
EmpMapper mapper = session.getMapper(EmpMapper.class);
List<Emp> emps = mapper.findAll();
for (Emp emp : emps) {
System.out.println(emp);
}
* 添加一个员工
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = new Emp("宋豪", "男", "前台", 8000, null, 8);
mapper.addEmp(emp);
* 按照id删除一个员工
EmpMapper mapper = session.getMapper(EmpMapper.class);
mapper.deleteEmpById(8);
* 按照学生id修改员工信息
EmpMapper mapper = session.getMapper(EmpMapper.class);
Emp emp = mapper.findEmpById(9);
emp.setJob("油漆工");
emp.setSex("女");
mapper.updateEmp(emp);