mybatis小技巧 11.28

最新推荐文章于 2023-05-15 15:51:52 发布

StickJuvenile

最新推荐文章于 2023-05-15 15:51:52 发布

阅读量332

点赞数

文章标签： mybatis

本文链接：https://blog.csdn.net/m0_55895362/article/details/128080127

版权

1.#{}和${}区别(#{}占位符‘XXX’ ${}拼接XXX)

2.什么时候用${}（升序降序拼接XXX）

3.拼接表名（log_${date}）

4.批量删除(or/in(x,x,x)/in($(x))

5.模糊查询(#{brand} |'%${brand}%')

6.别名机制(typeAlias/package)

7.mapper配置(package)

8.配置模板文件

9.自动生成主键值(useGenerateKeys/keyProperty)

1.#{}和${}区别(#{}占位符‘XXX’ ${}拼接XXX)

#{}是占位符： preparestatement 先预编译，后插入值（？占位符） 传入 'xxx'

${}是字符串：statement 先sql语句拼接，后进行编译（存在数据注入问题） 拼接xxx

出错一：不要在mapper.xml配置的sql语句里面注释会报错！！！

    <select id="SelectByCarType" resultType="com.jiang.pojo.T_Car">
-- 1.#{}与${}
-- 底层: #{}   preparestatement 先预编译，后插入值（？占位符）
-- Preparing: select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where car_type =?
-- ==> Parameters: motuo(String)
--      ${}   statement  先sql语句拼接，后进行编译

        select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where car_type =#{car_type}
    </select>

测试：

    @org.junit.Test
    public void TestSelectByCarType() {
            SqlSession sqlSession = MybatisTool.getSession();
            CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
            List<T_Car> t_carList = carMapper.SelectByCarType("燃油车");
            for (T_Car t_car : t_carList) {
                System.out.println(t_car);
            }
            sqlSession.commit();
            MybatisTool.close(sqlSession);
    }

为#{}时

 <select id="SelectByCarType" resultType="com.jiang.pojo.T_Car">
        select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where car_type =#{car_type}
    </select>

结果：

Preparing: select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where car_type =?
==> Parameters: 燃油车(String)

Created connection 1073763441.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@40005471]
==>  Preparing: select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where car_type =? 
==> Parameters: 燃油车(String)
<==      Total: 12
T_Car{id=5, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=6, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=7, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=12, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=13, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=14, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=15, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=16, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=17, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=32, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=38, num='9991', brand='凯美瑞', guide_price='30.0', produce_time='2020-11-11', car_type='燃油车'}
T_Car{id=39, num='9991', brand='凯美瑞', guide_price='30.0', produce_time='2020-11-11', car_type='燃油车'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@40005471]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@40005471]
Returned connection 1073763441 to pool.

Process finished with exit code 0

为${}时

报错

 SQL: select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where car_type =燃油车
### Cause: java.sql.SQLSyntaxErrorException: Unknown column '燃油车' in 'where clause'

2.什么时候用${}（升序降序拼接XXX）

升序降序（desc/asc）

当为#{}时

  <select id="SelectaLL" resultType="com.jiang.pojo.T_Car" >
        select id,car_num as num,brand,guide_price,produce_time,car_type from t_car     order by produce_time #{ascOrDesc}
    </select>

测试

   @org.junit.Test
    //测试${}
    public void TestSelectAll() {
        SqlSession session = MybatisTool.getSession();
        List<T_Car> cars = session.getMapper(CarMapper.class).SelectaLL("desc");
        cars.forEach(car -> System.out.println(car));
        session.close();
    }

报错

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@40005471]
==>  Preparing: select id,car_num as num,brand,guide_price,produce_time,car_type from t_car order by produce_time ? 
==> Parameters: desc(String)

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''desc'' at line 1
### The error may exist in Mapper.xml

当为${}时

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@69453e37]
==>  Preparing: select id,car_num as num,brand,guide_price,produce_time,car_type from t_car order by produce_time desc 
==> Parameters: 
<==      Total: 29
T_Car{id=5, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=6, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=7, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=12, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=13, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=14, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=15, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=16, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=17, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=32, num='1004', brand='丰田', guide_price='30.0', produce_time='2020-11-2', car_type='燃油车'}
T_Car{id=38, num='9991', brand='凯美瑞', guide_price='30.0', produce_time='2020-11-11', car_type='燃油车'}
T_Car{id=39, num='9991', brand='凯美瑞', guide_price='30.0', produce_time='2020-11-11', car_type='燃油车'}
T_Car{id=18, num='1005', brand='迈凯轮', guide_price='10.0', produce_time='2020-10-4', car_type='燃油机'}
T_Car{id=19, num='1005', brand='迈凯轮', guide_price='10.0', produce_time='2020-10-4', car_type='燃油机'}
T_Car{id=21, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=22, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=23, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=24, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=25, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=26, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=27, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=28, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=29, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=30, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=31, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=33, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=34, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=35, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
T_Car{id=36, num='111', brand='宝马', guide_price='30.0', produce_time='2020-10-1', car_type='小飞棍来咯！'}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@69453e37]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@69453e37]

不知道为什么这里运行不出来 mybatis的版本太低引用3.5.4就可以了

3.拼接表名（log_${date}）

使用${} 传值（“XXX”）呈现 select * from log_XXX

<mapper namespace="com.jiang.mapper.LogMapper">
    <select id="SelectByDate" resultType="com.jiang.pojo.Log">
        select * from log_${date}
    </select>

</mapper>

测试（以时间日期命名的多个log文件，找某个时期的文件）


public class TestLog {
    @Test
    public void  TestLog(){
        SqlSession sqlSession= MybatisTool.getSession();
        LogMapper logMapper=sqlSession.getMapper(LogMapper.class);
        List<Log> logs = logMapper.SelectByDate("20221122");
        for (Log log : logs) {
            System.out.println(log);
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

4.批量删除(or/in(x,x,x)/in($(x))

三种方式：

delete from t_car where id=1 or id=2 or id=3

delete from t_car where id in(1,2,3) 删除id为1,2,3的

```
delete from t_car where id in(${range})
```

    <delete id="DeleteBulk">
   delete from t_car where id in(1,2,3)
    </delete>
    <delete id="DeleteBulk2">
            delete from t_car where id in(${range})
    </delete>

public class Test03delbulk {
    @Test
    public void testdeletelot(){
        SqlSession sqlSession= MybatisTool.getSession();
        CarMapper carMapper=sqlSession.getMapper(CarMapper.class);
        carMapper.DeleteBulk();
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    //使用${}
    public void TestDeleteBulk(){
        SqlSession sqlSession= MybatisTool.getSession();
        CarMapper carMapper=sqlSession.getMapper(CarMapper.class);
        carMapper.DeleteBulk2("9,10,11");
        sqlSession.commit();
        sqlSession.close();
    }
}

5.模糊查询(#{brand} |'%${brand}%')

出错点： '?' 当占位符处在单引号之间时，将会失去占位功能

普通的查询语句：

  select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where brand like '%丰田%'

两种方式如下：

 #{brand}   传入参数得为 carMapper.Select_LikeByBrand("%丰田%");  放入sql中为 '%丰田%'

'%${which}%' 传入参数为 carMapper.Select_LikeByBrand("丰田")  放入sql中为   '%丰田%'

   <select id="Select_LikeByBrand" resultType="com.jiang.pojo.T_Car">
select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where brand like '%${which}%'
    </select>

    @Test
    //用${}
    public void TestLike2(){
        SqlSession sqlSession = MybatisTool.getSession();
        CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
        List<T_Car> t_carList = carMapper.Select_LikeByBrand("丰田");
        for (T_Car t_car : t_carList) {
            System.out.println(t_car);
        }
        sqlSession.commit();
        MybatisTool.close(sqlSession);
    }

6.别名机制(typeAlias/package)

<select id="Select_LikeByBrand" resultType="com.jiang.pojo.T_Car">

resultType太长了，有什么办法没？

<typeAliases>
    <typeAlias type="com.jiang.pojo.T_Car" alias="aaa"/>
别名为aaa
    <typeAlias type="com.jiang.pojo.T_Car" />
别名为T_Car
    <!--默认为其class名字， 即T_Car-->
    <package name="com.jiang.pojo"/>
    <!--给包下所有类取别名-->
在pojo包下，所有都以类名为别名

</typeAliases>

7.mapper配置(package)

在核心配置文件中，需要匹配SQL对应的Mapper,xml文件，当有多个时，非常不方便，怎么办？

直接引入某个包，该包下所有的Mapper.xml会被自动引入

 <mappers>
        <mapper resource="Mapper.xml"/>
        <mapper resource="LogMapper.xml"/>
        <!--当有多个mapper.xml文件就可以使用一下方式-->
        <package name="com.jiang.mapper"/>
    </mappers>

8.配置模板文件

位置：File | Settings | Editor | File and Code Templates

调用

9.自动生成主键值(useGenerateKeys/keyProperty)

当插入数据时，因为id是主键自增，所以一般赋值。但是我现在有个需求。

获取当前插入数据在表中的id

useGeneratedKeys="true"  使用自动生成的主键值
keyProperty="id"  指定主键值赋值给对象的哪个属性。这个属性就标书将主键值赋值给Car对象的id属性。
输出t_car对象就能够看到其id属性

    <insert id="InsertCar" useGeneratedKeys="true" keyProperty="id">
        insert into t_car values (null ,${num},#{brand},#{guide_price},#{produce_time},#{car_type})
    </insert>


public class Test05Keys {
    @Test
    public void TestKey(){
        SqlSession sqlSession= MybatisTool.getSession();
        CarMapper carMapper=sqlSession.getMapper(CarMapper.class);
        T_Car t_car=new T_Car(null,"9991","凯美瑞",30.0,"2020-11-11","燃油车");
        carMapper.InsertCar(t_car);
        System.out.println(t_car);
        sqlSession.commit();
        sqlSession.close();

    }
}

Opening JDBC Connection
Created connection 537066525.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2002fc1d]
==>  Preparing: insert into t_car values (null ,9991,?,?,?,?) 
==> Parameters: 凯美瑞(String), 30.0(Double), 2020-11-11(String), 燃油车(String)
<==    Updates: 1
T_Car{id=40, num='9991', brand='凯美瑞', guide_price='30.0', produce_time='2020-11-11', car_type='燃油车'}
Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2002fc1d]
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2002fc1d]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2002fc1d]
Returned connection 537066525 to pool.

StickJuvenile

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mybatis小技巧 11.28

select id,car_num as num,brand,guide_price,produce_time,car_type from t_car where brand like '%丰田%''%${which}%' 传入参数为 carMapper.Select_LikeByBrand("丰田") 放入sql中为 '%丰田%'在核心配置文件中，需要匹配SQL对应的Mapper,xml文件，当有多个时，非常不方便，怎么办？：不要在mapper.xml配置的sql语句里面注释会报错！
复制链接

扫一扫