MyBatis映射
1.查询——根据id查用户
项目 | 解释 |
<select> | 表示是查询SQL |
id | 在相同映射文件中SQL的唯一标识(名称不允许包含点【.】) |
parameterType | 传入参数的类型(当没有参数时可以省略) |
resultType | SQL返回给java的结果类型 |
#{userId} | #{xxx}叫做占位符。Xxx是参数的变量名。 占位符作用:根据占位符中的变量名接收参数,并根据参数类型判断是否需要加单引号。 如果只写#{}会报错。有了名称就不用考虑参数赋值的先后顺序了,比jdbc编程要方便很多。 |
<SQL示例>
<?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="user">
<!-- SQL -->
<!-- 根据id查询用户信息 -->
<select id="findUserById" parameterType="int" resultType="cn.it.pojo.User">
SELECT
userId, name, mobile, sex, age, address
FROM user
WHERE
userId = #{userId}
</select>
</mapper>
用字符串拼接符接收参数映射
项目 | 解释 |
${value} | ${xxx}叫做字符串拼接符。 拼接符特征:把SQL语句和参数值当成字符串进行字符串的原样拼接,所谓原样就是不做任何jdbc类型转换,原来什么样就拼成什么样。所以SQL配置中必须人为加单引号才行。 |
<SQL示例>
<!-- 根据用户名查询用户信息(方式二:用拼接符接收参数) -->
<select id="findUserByUserName2" parameterType="String" resultType="cn.it.pojo.User">
SELECT
userId, name,mobile,sex,age,address
FROM user
WHERE
name LIKE '%${value}%'
</select>
占位符与拼接符区别:
- 类型处理:
占位符#{}传递参数时会做参数类型处理,
拼接符${}传递参数时不会做类型处理只进行字符串原样拼接
- 安全性:
${}的原样拼接导致它存在安全漏洞,容易产生SQL注入风险
#{}的类型处理会对参数中存在的SQL敏感字符先转义然后再映射给SQL,这就不会影响原先的SQL,因此可以有效防止SQL注入。
- 工作中的应用:
由于拼接符${}存在安全隐患,因此在实际项目尽量使用占位符#{}
SQL注入的一个示例
1 映射文件中的配置 | |
<!-- 用拼接符接收参数 --> <select id="selectUserByUserName3" parameterType="String" resultType="cn.it.pojo.User"> SELECT u.userId, u.name, u.age, u.address FROM user u WHERE u.name LIKE '${value}' </select> | <!-- 用占位符接收参数 --> <select id="selectUserByUserName4" parameterType="String" resultType="cn.it.pojo.User"> SELECT u.userId, u.name, u.age, u.address FROM user u WHERE u.name LIKE #{name} </select> |
2 传递参数是一致的,左边拼接符最外面的单引号已经在映射文件中写上了;右边占位符按照预想由于传入的是String字符串类型的参数,所以会做类型处理自动的在参数外面加上一对单引号。但事情会想我们想象的那样进行吗? | |
List<User> userInfoList = sqlSession.selectList("user. selectUserByUserName3", "' OR 1=1 OR 1='"); | List<User> userInfoList = sqlSession.selectList("user. selectUserByUserName4", "' OR 1=1 OR 1='"); |
3 结果发现左边确实发生了sql注入,右边没有发生: | |
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@462d5aee] DEBUG [main] - ==> Preparing: SELECT u.userId, u.name, u.age, u.address FROM user u WHERE u.name LIKE '' OR 1=1 OR 1='' DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 14 [[1001, 王小一, null, 56, null, 南京], [1002, 王小二, null, 48, null, 青岛], [1003, 王小三, null, 32, null, 大连], [1004, 张三, null, 23, null, 广州], [1005, 王小五, null, 34, null, 重庆], [1006, 王小六, null, 31, null, 石家庄], [1007, 迎春, null, 28, null, 苏州], [1008, 张三, null, 23, null, 广州], [1009, 迎秋, null, 20, null, 长沙], [1010, 迎冬, null, 18, null, 佛山], [1011, 张三, null, 30, null, 广州], [1013, 张三, null, 30, null, 广州], [1014, 张三, null, 30, null, 广州], [1015, 张三, null, 30, null, 广州]] DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@462d5aee] DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@462d5aee] DEBUG [main] - Returned connection 1177377518 to pool. | DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@58c1670b] DEBUG [main] - ==> Preparing: SELECT u.userId, u.name, u.age, u.address FROM user u WHERE u.name LIKE ? DEBUG [main] - ==> Parameters: ' OR 1=1 OR 1='(String) DEBUG [main] - <== Total: 0 [] DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@58c1670b] DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@58c1670b] DEBUG [main] - Returned connection 1489069835 to pool. |
左边拼接是原样拼接因此出现了漏洞,形成的SQL相当于mysql的工具中左边的样子: 右边占位符由于做类型处理,首先后把校验传入的参数是否有敏感字符,这里单引号就是一个敏感字符,其次如果有敏感字符需要进行转义,上面的参数转义为:\' OR 1=1 OR 1=\',再次再把转义完的参数映射给SQL并在参数外面加一对单引号,转义后的参数就不会对原先的SQL产生影响,仅仅被当作普通参数值进行处理。形成的SQL相当于mysql的工具中右边的样子: |
如何防止SQL注入
1. 尽量使用占位符,如果是模糊查询可以在java程序中拼接%
2. 如果是MySQL数据库,映射文件中的SQL可以像下面这样写:
<!-- 根据用户名查询用户信息 -->
<select id="selectUserByName" parameterType="String" resultType="cn.it.pojo.User">
SELECT
userId,name,mobile,sex,age,address
FROM
User
WHERE
name LIKE "%"#{value}"%"
</select>
这样SQL可以解释成两个%常量和传入的参数值拼接成一个整体,再判断是否需要加单引号,这也能防止SQL注入,但是这个只限于MySQL好用,如果是Oracle,是不允”%”这样的写法的。
3. 在前端自定义SQL敏感字符集,然后进行提交前的参数校验。
2.查询——记录查询
项目 | 解释 |
<select> | 同上 |
id | 同上 |
parameterType | 没有参数时可以省略 |
resultType | SQL返回给程序的java结果的类型 |
<SQL示例>
<!-- 取得用户表的记录数 -->
<select id="countUserRecord" resultType="int">
SELECT COUNT(userId) FROM user
</select>
注意:不要使用count(*),因为count(*)效率低,可以count(1)或count(字段名)都可以。
3.新增
项目 | 解释 |
<insert> | 表示插入SQL的标签。 |
id | 同查询 |
parameterType | 同查询 |
|
|
<SQL示例>
<!-- 插入用户信息 -->
<insert id="addUserInfo" parameterType="cn.it.pojo.User">
INSERT INTO USER
(name,mobile,sex,age,address)
VALUES
(#{name},#{mobile},#{sex},#{age},#{address})
</insert>
4.修改——根据id修改用户信息
项目 | 解释 |
<update> | 用于更新SQL的标签。 |
id | 同查询 |
parameterType | 同查询 |
|
|
<SQL示例>
<!-- 根据id修改用户信息 -->
<update id="updateUserById" parameterType="cn.it.pojo.User">
UPDATE user
SET
name = #{name},
mobile = #{mobile},
sex = #{sex},
age = #{age},
address = #{address}
WHERE
userId = #{userId}
</update>
5.删除——根据id删除用户
项目 | 解释 |
<delete> | 用于删除SQL的标签。 |
id | 同查询 |
parameterType | 同查询 |
|
|
<SQL示例>
<!-- 根据id删除用户信息 -->
<delete id="deleteUserById" parameterType="int">
DELETE FROM user WHERE userId = #{id}
</delete>
简单增删改查总结:
<SQL映射规范>
·参数映射规范
传单个参数时,parameterType="简单类型",占位符的变量名可以任意,拼接符的变量名必须是value,但不能没有。
传多个参数时,parameterType="pojo类型",占位符或拼接符的变量名必须等于参数对象的属性名。
·返回值映射规范
返回单值时,resultType="简单类型",值直接返回给java程序。
返回单/多条记录时 | 单:resultType="pojo类型" 多:resultType="pojo类型(集合泛型)" | 结果集列名必须等于pojo的属性名 |
<增删改查对应的标签和java客户端调用的方法>
区分 | 标签 | 客户端调用方法 |
增 | <insert> | insert(namespace名+.+sql id, sql的参数变量) |
删 | <delete> | delete(参数同上) |
改 | <update> | update(参数同上) |
查 | <select> | 单值或单条selectOne(参数同上) 多条selectList(参数同上) |
插入标签中的子查询
1.取得插入数据的自增主键
项目 | 解释 |
<selectKey> | 用于<insert>操作的子查询。 |
order | 子查询相对于insert SQL的执行顺序(AFTER:在插入之后执行 BEFORE:在插入之前执行) |
keyProperty | 传入的java对象参数的某个属性名,用于将子查询结果赋值给参数对象的指定属性。这样在java程序中只要执行完insert()方法,就可以从参数对象中指定的属性上取得这个子查询的结果。 |
resultType | 子查询的值按照什么类型返回结果 |
LAST_INSERT_ID() | mysql的函数,可以返回最新插入记录的主键,要和insert语句配合使用,否则单独执行的值就是0 |
<SQL示例>
<!-- 插入一条用户信息并返回新插入记录的主键 -->
<insert id="addUserInfo" parameterType="cn.it.pojo.User">
<!-- 插入操作中的子查询 -->
<selectKey order="AFTER" keyProperty="userId" resultType="int">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO USER
(name,mobile,sex,age,address)
VALUES
(#{name},#{mobile},#{sex},#{age},#{address})
</insert>
2.使用UUID实现主键
selectKey + UUID(),可以解决非自增型主键的数据表中在插入数据前先创建主键的问题。
<说明>
项目 | 解释 |
<selectKey> | 同上 |
order | 同上(这里指定为BEFORE) |
keyProperty | 同上 |
resultType | 同上 |
UUID() | mysql的函数,可以返回随机的UUID,可以作为主键用。 |
<!-- 取得插入数据的主键后插入数据 -->
<insert id="insertOrderData" parameterType="cn.it.pojo.Order">
<selectKey order="BEFORE" keyProperty="orderId" resultType="String">
SELECT UUID()
</selectKey>
INSERT INTO order1
(orderId, userId, orderStatus, goodsId, createDateTime)
VALUES
(#{orderId}, #{userId}, #{orderStatus}, #{goodsId}, now());
</insert>
二.高级输入映射
因为查询条件是多方面的业务条件,所以我们需要定义一个专门用来传递条件参数的实体类,它可以包含各种各样的查询条件,比如可以包含简单类型、对象、数组、List等。
通常我们把保存查询条件的包装类称为QueryVo.java,其实就是普通的java bean。
本示例中,我们需要订单基本信息和用户信息作为查询条件,所以包括了用户信息和订单信息。
【QueryVo.java】
public class QueryVo {
// 用户信息
private User user;
// 订单信息
private Order order;
setter/getter。。。。。
}
思考一个问题:上面的Vo定义我们为什么不将用户的属性和订单的属性混合到一起放到这个Vo中呢?
混合到一起是没有错的,而且参数映射时也简单了,但是会让Vo变得很混乱,分不清属性谁是谁的,这就容易出现bug,因此我们应该采用面向对象的设计思想——封装,即封装成一个User对象和一个Order对象,这样就不混乱了,避免了出错的可能。
- SQL映射文件
【OrderMapper.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="cn.it.dao.OrderMapper">
<!-- SQL -->
<!-- 根据QueryVo查询订单信息 -->
<select id="findOrderByQueryVo" parameterType="cn.it.pojo.QueryVo"
resultType="cn.it.pojo.Order">
SELECT
o.orderId,
o.userId,
o.orderStatus,
o.goodsId,
o.createDateTime
FROM
order1 o,
user u
WHERE
u.name LIKE #{user.name}
AND u.mobile LIKE #{user.mobile}
AND o.orderStatus = #{order.orderStatus}
AND o.userId = u.userId
</select>
</mapper>
三.高级输出映射
Mybatis的返回值映射能将结果集映射成Java对象的关键是:列名==属性名
但如果列名≠属性名怎么办?确切点说如果列名不等于属性名时我们如何来做映射呢?
解决的办法:就是手动定义返回值映射。
项目 | 解释 |
<resultMap> | 用于自定义返回值映射的规则,即自定义哪个列名对应哪个属性名。 |
id | 返回值映射的唯一标识 |
type | 返回值映射中java对象的类型 |
<result> | 用于定义一个返回值映射规范的标签,一个<resultMap>可以包含多个<result> |
column | 返回值映射中的列名 |
property | 返回值映射中的属性名 |
<id> | 用于定义返回值映射中主键列名与字段名的映射关系。用法和<result>一模一样,只是增加可读性。 |
<SQL映射示例>
<!-- 自定义返回值映射的规范 -->
<resultMap type="cn.it.pojo.Order" id="order2ResultMap">
<id column="order_id" property="orderId"/>
<!-- <result column="order_id" property="orderId"/> -->
<result column="user_id" property="userId"/>
<result column="order_status" property="orderStatus"/>
<result column="goods_id" property="goodsId"/>
<result column="create_date_time" property="createDateTime"/>
</resultMap>
有了这个自定义了规范,即使列名≠属性名,MyBatis也可以利用这个自定义的规范进行返回值映射了。
- SQL
【OrderMapper.xml】
<说明>
项目 | 解释 |
resultMap | 引用返回值映射的自定义规范 |
<SQL映射示例>
<!-- 根据id查询order2表的订单信息 -->
<select id="findOrder2ById" parameterType="String" resultMap="order2ResultMap">
SELECT
order_id,
user_id,
order_status,
goods_id,
create_date_time
FROM
order2
WHERE
order_id = #{orderId}
</select>
2.自定义返回值映射
<!-- 根据id查询order2表的订单信息2 -->
<select id="findOrder2ById2" parameterType="String" resultType="cn.it.pojo.Order">
SELECT
order_id as orderId,
user_id as userId,
order_status as orderStatus,
goods_id as goodsId,
create_date_time as createDateTime
FROM
order2
WHERE
order_id = #{orderId}
</select>
四.动态sql
项目 | 解释 |
<if> | 用于判断它包含的SQL语句是否需要添加。 |
test | 判断的逻辑条件,and表示与,or表示或, true时添加,false时忽略。 |
<SQL映射示例>
<!-- 根据动态条件查询订单信息 -->
<select id="findOrderByQueryVo2" parameterType="cn.it.pojo.QueryVo"
resultType="cn.it.pojo.Order">
SELECT
o.orderId,
o.userId,
o.orderStatus,
o.goodsId,
o.createDateTime
FROM
order1 o,
user u
WHERE 1 = 1
<if test="user.name != null and user.name != ''">
AND u.name LIKE #{user.name}
</if>
<if test="user.mobile != null and user.mobile != ''">
AND u.mobile LIKE #{user.mobile}
</if>
<if test="order.orderStatus != null and order.orderStatus != ''">
AND o.orderStatus = #{order.orderStatus}
</if>
and o.userId = u.userId
</select>