首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >猿蜕变12——一文搞定mybatis花式玩法

猿蜕变12——一文搞定mybatis花式玩法

作者头像
山旮旯的胖子
发布2020-07-28 16:59:53
2670
发布2020-07-28 16:59:53
举报
文章被收录于专栏:猿人工厂猿人工厂

看过之前的蜕变系列文章,相信你对mybatis有了初步的认识。但是这些还不够,我们今天进一步来了解下mybatis的一些用法。

在上一个章节,我们学会了单表的插入操作,接下来我们实现一个根据主键travel_route_id删除travel_route表记录的例子。

1.在TravelRouteMapper.xml中增加按id删除travel_route表记录的SQL语句:

<delete id="deleteById"parameterType="Long">
      delete from   travel_route where travel_route_id=#{travelRouteId}
   </delete>

2.在接口TravelRouteDao中增加deleteById的方法,然后在现类TravelRouteDaoImpl中实现该方法:

@Override
       public  void  deleteById(LongtravelRouteId) {
              try {
                 
            sqlSession = MyBatisUtil.getSqlSession();
            //新增数据操作
            sqlSession.delete("com.pz.route.dao.TravelRouteDao.deleteById", travelRouteId);
           
            //提交SqlSession
            sqlSession.commit();
           
            //无需再做关闭,SqlSessionFactory会自动关闭sqlSession
 
        } catch (Exception e) {
            e.printStackTrace();
        }
             
       }

3.在测试类TestTravelRouteDao中,编写测试方法调用编写之前编写的方法:

@Test
    public void testDeleteTravelRouteById(){
              TravelRouteDao travelRouteDao = new TravelRouteDaoImpl();
              travelRouteDao.deleteById(514L);
   }

运行测试类,我们可以发现travel_route_id为514的记录被删除了。

接下来我们在做一个根据主键travel_route_id更新travel_route表记录的例子。

1.在TravelRouteMapper.xml中增加按id更新travel_route表记录的SQL语句:

update id="updateById">
      update   
      travel_route
       settravel_route_name=#{travelRouteName},
       travel_route_price=#{travelRoutePrice},
      travel_route_introduce=#{travelRouteIntroduce},
       travel_route_flag=#{travelRouteFlag},
       travel_route_date=#{travelRouteDate},
       isThemeTour=#{isThemeTour},
       travel_route_count=#{travelRouteCount},
       travel_route_cid=#{travelRouteCid},
       travel_route_image=#{travelRouteImage},
      travel_route_seller_id=#{travelRouteSellerId}
      where travel_route_id =#{travelRouteId}
   
   
   </update>

2.在TravelRouteDao中编写updateById方法并在TravelRouteDaoImpl中实现updateById方法:

@Override
       public void updateById(TravelRoute travelRoute) {
              try {
                 
            sqlSession = MyBatisUtil.getSqlSession();
            //新增数据操作
            sqlSession.update("com.pz.route.dao.TravelRouteDao.updateById", travelRoute);
           
                 System.out.println(travelRoute);
           
            //提交SqlSession
            sqlSession.commit();
           
            //无需再做关闭,SqlSessionFactory会自动关闭sqlSession
 
        } catch (Exception e) {
            e.printStackTrace();
        }
             
       }

3.在测试类TestTravelRouteDao中编写测试方法:

@Test
    public void testUpdateTravelRouteById(){
              TravelRouteDao travelRouteDao = new TravelRouteDaoImpl();
              TravelRoute travelRoute = new TravelRoute();
             
              travelRoute.setTravelRouteId(515L);
             
              travelRoute.setTravelRouteName("测试修改线路");
              travelRoute.setTravelRoutePrice(999d);
              travelRoute.setTravelRouteDate("2019-10-26");
              travelRoute.setTravelRouteFlag(1);
              travelRoute.setIsThemeTour("1");
              travelRoute.setTravelRouteCount(0);
              travelRoute.setTravelRouteCid(1);
              travelRoute.setTravelRouteIntroduce("双导游服务,免收服务小费,周全照顾贴心服务随心出游!品尝越南特色国宝美食,升级一餐越式炸鸡火锅宴!");
              travelRoute.setTravelRouteImage("img/product/small/m3db4d2277b5df3d98597f79082ef92d6d.jpg");
              travelRoute.setTravelRouteSellerId(1L);
              System.out.println("before insert===");
              System.out.println(travelRoute);
             
              travelRouteDao.updateById(travelRoute);
             
    }

运行测试程序我们看到travel_route_id为515的记录被修改了。

我们先看一个列表查询的例子:

1.在TravelRouteMapper.xml中编写查询的sql语句:

<select id="queryTravelByPage"resultType="TravelRoute" parameterType="java.util.Map">
   
     select
       travel_route_id as travelRouteId,
       travel_route_name as travelRouteName,
       travel_route_price as travelRoutePrice,
       travel_route_introduce astravelRouteIntroduce,
       travel_route_flag as travelRouteFlag,
       travel_route_date as travelRouteDate,
       isThemeTour as isThemeTour,
       travel_route_count as travelRouteCount,
       travel_route_cid as travelRouteCid,
       travel_route_image as travelRouteImage,
       travel_route_seller_id astravelRouteSellerId
    
      from   travel_route order by travel_route_id desc limit #{startRow},#{endRow}
     
     
    
   
   </select>

其中resultType表示接收SQL语句记录的数据类型,因为我们已经在在mybatis.xml文件中增加了类的别名,所以resultType的值可以使用别名TravelRoute。

<typeAliases>
              <typeAlias type="com.pz.route.domain.TravelRoute"alias="TravelRoute" />
       </typeAliases>

2.在TravelRouteDao编写queryTravelByPage方法,并且在TravelRouteDaoImpl中实现方法:

@Override
       public List<TravelRoute>queryTravelByPage(Long startRow, Long endRow) {
              try {
               
                     Map map = new HashMap();
                     map.put("startRow", startRow);
                     map.put("endRow", endRow);
            sqlSession = MyBatisUtil.getSqlSession();
            //新增数据操作
            List<TravelRoute> list= sqlSession.selectList("com.pz.route.dao.TravelRouteDao.queryTravelByPage", map);
           
           
           return list;
           
            //无需再做关闭,SqlSessionFactory会自动关闭sqlSession
 
        } catch (Exception e) {
            e.printStackTrace();
        }
              return null;
       }

在测试类TestTravelRouteDao中编写测试方法:

@Test
       public void testQueryTravelByPage(){
             
              TravelRouteDao travelRouteDao = new TravelRouteDaoImpl();
             
              List<TravelRoute> list=travelRouteDao.queryTravelByPage(0L, 10L);
              System.out.println(list);
             
             
             
       }

运行测试方法,可以看到程序正确返回了记录。

我们已经学会分页返回List类型的查询结果了,但是在实际应用中由很多只需要查询某一条记录的场景。接下来,我们完成一个小功能,根据主键travel_route_id查询travel_route表记录

1.在TravelRouteMapper.xml中编写根据travel_route_id查询travel_route的SQL语句:

<select id="queryTravelById"resultType="TravelRoute" parameterType="Long">
   
     select
       travel_route_id as travelRouteId,
       travel_route_name as travelRouteName,
       travel_route_price as travelRoutePrice,
       travel_route_introduce astravelRouteIntroduce,
       travel_route_flag as travelRouteFlag,
       travel_route_date as travelRouteDate,
       isThemeTour as isThemeTour,
       travel_route_count as travelRouteCount,
       travel_route_cid as travelRouteCid,
       travel_route_image as travelRouteImage,
       travel_route_seller_id astravelRouteSellerId
    
      from   travel_route where travel_route_id =#{travelRouteId}
   
   </select>

2.在TtavelRouteDao中编写queryTravelById方法并在TtavelRouteDaoImpl中实现方法:

@Override
       public TravelRoute queryTravelById(Long travelRouteId) {
              try {
               
            sqlSession = MyBatisUtil.getSqlSession();
            //新增数据操作
            TravelRoute travelRoute= sqlSession.selectOne("com.pz.route.dao.TravelRouteDao.queryTravelById", travelRouteId);
           
           return travelRoute;
           
        } catch (Exception e) {
            e.printStackTrace();
        }
              returnnull;
       }

3.在测试类TestTravelRouteDao中编写测试方法:

@Test
       publicvoid testQueryTravelById(){
             
              TravelRouteDao travelRouteDao = new TravelRouteDaoImpl();
             
              TravelRoute travelRoute=travelRouteDao.queryTravelById(515L);
              System.out.println(travelRoute);
             
       }
      

运行测试方法,发现travel_route_id为515的记录被查询出来了。

在猿人进化系列中我们的搜索功能是根据线路名检索线路,之前我们的dao是使用jdbc templete来实现的。现在我们用MyBatis来实现线路的模糊查询功能。

1.在TravelRouteMapper.xml中编写根据travel_route_name模糊查询travel_route的SQL语句:

<select id="queryTravelByName"resultType="TravelRoute" parameterType="String">
   
     select
       travel_route_id as travelRouteId,
       travel_route_name as travelRouteName,
       travel_route_price as travelRoutePrice,
       travel_route_introduce astravelRouteIntroduce,
       travel_route_flag as travelRouteFlag,
       travel_route_date as travelRouteDate,
       isThemeTour as isThemeTour,
       travel_route_count as travelRouteCount,
       travel_route_cid as travelRouteCid,
       travel_route_image as travelRouteImage,
       travel_route_seller_id astravelRouteSellerId
    
      from   travel_route where travel_route_name like  '%' #{travelRouteName} '%'
   
   </select>

需要注意的是,这里面的’%’ #{name} ‘%’之间是没有+号的,在mybatis中编写sql语句,和在文本中编写sql语句大体上是一致的,直接使用空格就好,不需要使用+号,使用空格就好。

2. 在TtavelRouteDao中编写queryTravelByName方法并在TtavelRouteDaoImpl中实现方法

@Override
       public List<TravelRoute> queryTravelByName(String travelRouteName) {
              try {
               
            sqlSession = MyBatisUtil.getSqlSession();
            //新增数据操作
            List<TravelRoute> list= sqlSession.selectList("com.pz.route.dao.TravelRouteDao.queryTravelByName", travelRouteName);
           
           return list;
           
        } catch (Exception e) {
            e.printStackTrace();
        }
              returnnull;
       }

3.在测试类TestTravelRouteDao中编写测试方法:

  @Override
       public List<TravelRoute>queryTravelByName(String travelRouteName) {
              try {
               
            sqlSession = MyBatisUtil.getSqlSession();
            //新增数据操作
            List<TravelRoute> list= sqlSession.selectList("com.pz.route.dao.TravelRouteDao.queryTravelByName", travelRouteName);
           
           return list;
           
        } catch (Exception e) {
            e.printStackTrace();
        }
              return null;
       }

运行测试程序发现只要是travle_route_name包含春节二字的数据都被查询出来了。

其实要实现模糊查询,我们还可以使用$来实现,我们可以把语句改为下方的写法,也是可以执行的。

<select id="queryTravelByName"resultType="TravelRoute" parameterType="String">
   
     select
       travel_route_id as travelRouteId,
       travel_route_name as travelRouteName,
       travel_route_price as travelRoutePrice,
       travel_route_introduce astravelRouteIntroduce,
       travel_route_flag as travelRouteFlag,
       travel_route_date as travelRouteDate,
       isThemeTour as isThemeTour,
       travel_route_count as travelRouteCount,
       travel_route_cid as travelRouteCid,
       travel_route_image as travelRouteImage,
       travel_route_seller_id astravelRouteSellerId
    
      from   travel_route where travel_route_name like  '% ${travelRouteName} %'
   
   </select>

这样写会有一个问题,传入的参数name是由页面传入的,可以对传入满足sql语法的参数进行拼接,引发sql注入的安全性问题。

#其实是占位符,是字符串拼接,使用#传递参数,Mybatis使用的是PreparedStatement来操作数据库,PreparedStatement有预编译处理,可以防止SQL注入问题。而通过控制台打印的sql语句可以看出,他是以?进行占位的,类似JDBC的PreparedStatement,可以防止SQL注入的问题,是字符串拼接,Mybatis使用的是Statement来操作数据库,Statement不会对SQL做预编译处理存在SQL注入的问题! 所以很多公司的安全编程规范里会规定:只能使用#!

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2020-06-03,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 猿人工厂 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档