Web开发之数据库总结

Web开发之数据库总结

mybatis中useGeneratedKeys用法--插入数据库后获取主键值

      1、keyProperty对应的是实体中的属性，不是数据库字段

　　2、对于非自增主键，必须显式写出　　　　　　

　<selectKey resultType="java.lang.String" order="AFTER" keyProperty="uuid">

　　　　 SELECT uuid()

　　</selectKey>

　　 order的设置，在insert sql语句执行前（BEFORE）或者执行后（AFTER），

　　上面注释掉的代码用的before，看测试代码中我并没有对uuidStudent进行uuid set值，但是insert sql中缺用到#{uuid}，理解为在执行前已经将主键id值赋给实体，直接取值就行.经过测试如果把#{uuid}换成uuid()，就发现得到的主键和入库的数据不一致

　　使用after，可以看到使用了数据库的uuid（），那么在插入数据后，把这个值赋给了实体uuidStudent.

Mybatis useGeneratedKeys无法返回主键解决

错误代码分析：

　　使用useGeneratedKeys生成主键时

　　（1）如果在DAO层使用@Param注解传递参数，则 keyProperty 属性 需要通过 “注解”+“主键id”的格式，否则无法返回主键。

　　（2）如果在DAO层只有单个参数传递（不需要使用@Param注解穿传递参数），则 keyProperty 属性可以直接 = “主键id” 来返回主键。

 

 

 

 

mybatis trim标签的使用

https://blog.csdn.net/wt_better/article/details/80992014

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

 

属性    描述

prefix    给sql语句拼接的前缀

suffix    给sql语句拼接的后缀

prefixOverrides    去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"

suffixOverrides    去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

 

注意对应的值的类型进行判断的条件要一一对应

 

 

LIke的模糊查询

SQL提供了四种匹配模式：

1，% ：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'

将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。

另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'

若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%'

虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。

 

2，_ ： 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：

 

比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'

只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的；

再比如 SELECT * FROM [user] WHERE u_name LIKE '三__';

只找出“三脚猫”这样name为三个字且第一个字是“三”的；

 

3，[ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'

将找出“张三”、“李三”、“王三”（而不是“张李王三”）；

如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'

将找出“老1”、“老2”、……、“老9”；

 

4，[^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'

将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';

将排除“老1”到“老4”，寻找“老5”、“老6”、……

 

5，查询内容包含通配符时 

由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。

 

 

$和#的用法区别

 

1. #将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id，则解析成的sql为order by "id".

　　

2. $将传入的数据直接显示生成在sql中。如：order by $user_id$，如果传入的值是111,那么解析成sql时的值为order by user_id,  如果传入的值是id，则解析成的sql为order by id.

　　

3. #方式能够很大程度防止sql注入。

　　

4.$方式无法防止Sql注入。

 

5.$方式一般用于传入数据库对象，例如传入表名.

　　

6.一般能用#的就别用$.

 

MyBatis排序时使用order by 动态参数时需要注意，用$而不是#

 

 

 

sql语句中条件查询in、like、=的效率

 

1、如果条件字段都是非索引字段,那么效率都差不多,就看结果大小。

2、有差别的在于条件字段是索引字段时:

   “=”在索引的情况下都会进行索引扫描,所以效率总是高的。

   “like”当模糊查询为右模糊,比如'abc%'时,扫描索引,高效。当模糊查询含左模糊时,比如'%abc',进行全表扫描,低效。

   “in”的作用等同于or,也是进行索引扫描,高效。

   另外,“in”还可以连接查询结果集,这时往往会和exists做比较。

   a、select * from t1 where f1 in(select f1 from t2 where t2.fx='x'),其中子查询的where里的条件不受外层查询的影响,这类查询一般情况下,自动优化会转成exist语句,也就是效率和exist一样。

   b、select * from t1 where f1 in(select f1 from t2 where t2.fx=t1.fx),其中子查询的where里的条件受外层查询的影响,这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少,一般效率不如exists,数据量大时,效果就更加明显。

 

mybatis中使用in查询时的注意事项

 

1. 当查询的参数只有一个时

findByIds(List<Long> ids)

1.a 如果参数的类型是List, 则在使用时，collection属性要必须指定为 list

<select id="findByIdsMap" resultMap="BaseResultMap">

         Select

         <include refid="Base_Column_List" />

         from jria where ID in

                  <foreach item="item" index="index" collection="list"

                         open="(" separator="," close=")">

                        #{item}

                </foreach>

  </select>

findByIds(Long[] ids)

1.b 如果参数的类型是Array,则在使用时，collection属性要必须指定为 array

<select id="findByIdsMap" resultMap="BaseResultMap">

                 select

                 <include refid="Base_Column_List" />

          from jria where ID in

                  <foreach item="item" index="index" collection="array"

                         open="(" separator="," close=")">

                        #{item}

                </foreach>

  </select>

 

2. 当查询的参数有多个时,例如 findByIds(String name, Long[] ids)

这种情况需要特别注意，在传参数时，一定要改用Map方式, 这样在collection属性可以指定名称

下面是一个示例       

  Map<String, Object> params = new HashMap<String, Object>(2);

         params.put("name", name);

         params.put("ids", ids);

         mapper.findByIdsMap(params);

-----------------------------------------

<select id="findByIdsMap" resultMap="BaseResultMap">

                 select

                 <include refid="Base_Column_List" />

          from jria where ID in

                  <foreach item="item" index="index" collection="ids"

                         open="(" separator="," close=")">

                        #{item}

                </foreach>

   </select>

 

在mybatis中可以使用when otherwise来达到if else 的效果

choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

 

假设有个这样的需求， 实现如下如下逻辑

当参数id有值时优先使用id查询

当没有id时就去判断用户名是否有值，如果有值就根据用户名全配

如果用户名也没有值，就是sql查询为空

<select id="selectSysUserByIdOrByUserName" resultType="com.artisan.mybatis.xml.domain.SysUser">

        SELECT

            a.id,

            a.user_name userName,

            a.user_password userPassword,

            a.user_email userEmail,

            a.user_info userInfo,

            a.head_img headImg,

            a.create_time createTime

        FROM

            sys_user a

        WHERE 1=1

        <choose>

            <when test="id != null ">

                and id = #{id}

            </when>

            <when test="userName!= null ">

                and user_name = #{userName}

            </when>

            <otherwise>

                and 1 = 2

            </otherwise>

        </choose>

    </select>

 

 

case when的使用方法主要有两种：

第一种：

UPDATE USER SETUSERNAME =

CASE

WHEN ID = 1 THEN 'USERNAME1'WHEN ID = 2 THEN 'USERNAME2'WHEN ID = 3 THEN 'USERNAME3'END,PASSWORD =

CASE

WHEN ID = 1 THEN 'PASSWORD1'WHEN ID = 2 THEN 'PASSWORD2'WHEN ID = 3 THEN 'PASSWORD3'END

以上形态中case后未跟任何内容，而是采取了搜索的方式进行更新，也就是说在整张表中搜索ID=1，ID=2，ID=3的数据进行更新。

第二种：

UPDATE USER SETPASSWORD =CASE IDWHEN 1 THEN 'PASSWORD1'WHEN 2 THEN 'PASSWORD2'WHEN 3 THEN 'PASSWORD3'END,USERNAME =

CASE IDWHEN 1 THEN 'USERNAME1'WHEN 2 THEN 'USERNAME2'WHEN 3 THEN 'USERNAME3'END;

case后跟某字段，然后通过判断该字段的值来进行更新，一般情况下两者通用