Mybatis使用${}和#{}的区别详解和解析-----Mybatis框架

一时失言乱红尘 2024-03-04 01:46 94阅读 0赞

>     mybatis小技巧
>     #{}和${}的区别
>     ${}的效果
>     org.apache.ibatis.exceptions.PersistenceException:
>     ### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column '新能源' in 'where clause'
>     ### The error may exist in CarMapper.xml
>     ### The error may involve defaultParameterMap
>     ### The error occurred while setting parameters
>     ### SQL: select             id as id,             car_num as carNum,             brand as brand,             guide_price as guidePrice,             produce_time as produceTime,             car_type as carType         from             t_car         where             car_type = 新能源
>     ### Cause: java.sql.SQLSyntaxErrorException: Unknown column '新能源' in 'where clause'
>     因为${}不会生成?然后直接以字段的形式填入了Sql语句
>     区别:
>     #{}底层使用prepareStatement,先进行编译,然后用?传值
>     ${}底层使用statement,先进行拼接语句,再对语句进行编译(存在SQL注入风险)
>     可以扭曲原SQL语句的含义
>     如果向SQL中拼接表名,就需要使用${}
>     现实业务中,可能存在分表储存的情况,因为一张表存的话,数据量过大,查询效率比较低
>     可以将这些数据有规律的分表储存,这样在查询的时候效率比较高,因为扫描的数据量变少了
>     日志表,专门存储日志信息的,如果t_log,这个表中每天都会产生很多log,慢慢这个表内容会爆炸
>     可以每天生成一张表,每张表以当天日期作为名称
>     如果我们需要知道某天的日志信息怎么办
>     假设今天20230901,那么直接查询t_log_20230901即可
> 
>     mybatis小技巧
>     #{}和${}的区别
>     ${}的效果
>     org.apache.ibatis.exceptions.PersistenceException:
>     ### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column '新能源' in 'where clause'
>     ### The error may exist in CarMapper.xml
>     ### The error may involve defaultParameterMap
>     ### The error occurred while setting parameters
>     ### SQL: select             id as id,             car_num as carNum,             brand as brand,             guide_price as guidePrice,             produce_time as produceTime,             car_type as carType         from             t_car         where             car_type = 新能源
>     ### Cause: java.sql.SQLSyntaxErrorException: Unknown column '新能源' in 'where clause'
>     因为${}不会生成?然后直接以字段的形式填入了Sql语句
>     区别:
>     #{}底层使用prepareStatement,先进行编译,然后用?传值
>     ${}底层使用statement,先进行拼接语句,再对语句进行编译(存在SQL注入风险)
>     可以扭曲原SQL语句的含义
>     如果向SQL中拼接表名,就需要使用${}
>     现实业务中,可能存在分表储存的情况,因为一张表存的话,数据量过大,查询效率比较低
>     可以将这些数据有规律的分表储存,这样在查询的时候效率比较高,因为扫描的数据量变少了
>     日志表,专门存储日志信息的,如果t_log,这个表中每天都会产生很多log,慢慢这个表内容会爆炸
>     可以每天生成一张表,每张表以当天日期作为名称
>     如果我们需要知道某天的日志信息怎么办
>     假设今天20230901,那么直接查询t_log_20230901即可

>     <?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="com.powernode.mybatis.Mapper.LogMapper">
>         <select id="selectAllByTable" resultType="com.powernode.mybatis.POJO.Log">
>             select
>                 *
>             from
>                 t_log_${date};
>         </select>
>     </mapper>
> 
>     <?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="com.powernode.mybatis.Mapper.LogMapper">
>         <select id="selectAllByTable" resultType="com.powernode.mybatis.POJO.Log">
>             select
>                 *
>             from
>                 t_log_${date};
>         </select>
>     </mapper>

>     <?xml version="1.0" encoding="UTF-8" ?>
>     <!DOCTYPE configuration
>             PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
>             "http://mybatis.org/dtd/mybatis-3-config.dtd">
>     <configuration>
>         
>         <properties resource="jdbc.properties"/>
>         <settings>
>             <setting name="logImpl" value="SLF4J"/>
>         </settings>
>         <environments default="development">
>             <environment id="development">
>                 <transactionManager type="JDBC" />
>                 <dataSource type="POOLED">
>                     <property name="driver" value="${jdbc.driver}" />
>                     <property name="url" value="${jdbc.url}" />
>                     <property name="username" value="${jdbc.username}" />
>                     <property name="password" value="${jdbc.password}" />
>                 </dataSource>
>             </environment>
>         </environments>
>         <mappers>
>             
>             <mapper resource="CarMapper.xml"></mapper>
>             <mapper resource="LogMapper.xml"></mapper>
>             
>             
>         </mappers>
>     </configuration>
> 
>     <?xml version="1.0" encoding="UTF-8" ?>
>     <!DOCTYPE configuration
>             PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
>             "http://mybatis.org/dtd/mybatis-3-config.dtd">
>     <configuration>
>         
>         <properties resource="jdbc.properties"/>
>         <settings>
>             <setting name="logImpl" value="SLF4J"/>
>         </settings>
>         <environments default="development">
>             <environment id="development">
>                 <transactionManager type="JDBC" />
>                 <dataSource type="POOLED">
>                     <property name="driver" value="${jdbc.driver}" />
>                     <property name="url" value="${jdbc.url}" />
>                     <property name="username" value="${jdbc.username}" />
>                     <property name="password" value="${jdbc.password}" />
>                 </dataSource>
>             </environment>
>         </environments>
>         <mappers>
>             
>             <mapper resource="CarMapper.xml"></mapper>
>             <mapper resource="LogMapper.xml"></mapper>
>             
>             
>         </mappers>
>     </configuration>

>     package com.powernode.mybatis.POJO;
>     
>     public class Log
>     {
>         private Long id;
>         private String log;
>         private String time;
>     
>         @Override
>         public String toString() {
>             return "Log{" +
>                     "id=" + id +
>                     ", log='" + log + '\'' +
>                     ", time='" + time + '\'' +
>                     '}';
>         }
>     
>         public Long getId() {
>             return id;
>         }
>     
>         public void setId(Long id) {
>             this.id = id;
>         }
>     
>         public String getLog() {
>             return log;
>         }
>     
>         public void setLog(String log) {
>             this.log = log;
>         }
>     
>         public String getTime() {
>             return time;
>         }
>     
>         public void setTime(String time) {
>             this.time = time;
>         }
>     
>         public Log(Long id, String log, String time) {
>             this.id = id;
>             this.log = log;
>             this.time = time;
>         }
>     
>         public Log() {
>         }
>     }
> 
>     package com.powernode.mybatis.POJO;
>     
>     public class Log
>     {
>         private Long id;
>         private String log;
>         private String time;
>     
>         @Override
>         public String toString() {
>             return "Log{" +
>                     "id=" + id +
>                     ", log='" + log + '\'' +
>                     ", time='" + time + '\'' +
>                     '}';
>         }
>     
>         public Long getId() {
>             return id;
>         }
>     
>         public void setId(Long id) {
>             this.id = id;
>         }
>     
>         public String getLog() {
>             return log;
>         }
>     
>         public void setLog(String log) {
>             this.log = log;
>         }
>     
>         public String getTime() {
>             return time;
>         }
>     
>         public void setTime(String time) {
>             this.time = time;
>         }
>     
>         public Log(Long id, String log, String time) {
>             this.id = id;
>             this.log = log;
>             this.time = time;
>         }
>     
>         public Log() {
>         }
>     }

>     package com.powernode.mybatis.Mapper;
>     
>     import com.powernode.mybatis.POJO.Log;
>     
>     import java.util.List;
>     
>     public interface LogMapper
>     {
>         //根据日期查询不同的表,获取表中所有的日志
>         List<Log> selectAllByTable(String time);
>     }
> 
>     package com.powernode.mybatis.Mapper;
>     
>     import com.powernode.mybatis.POJO.Log;
>     
>     import java.util.List;
>     
>     public interface LogMapper
>     {
>         //根据日期查询不同的表,获取表中所有的日志
>         List<Log> selectAllByTable(String time);
>     }

>     package com.powernode.mybatis.Test;
>     
>     import com.powernode.mybatis.Mapper.CarMapper;
>     import com.powernode.mybatis.Mapper.LogMapper;
>     import com.powernode.mybatis.POJO.Car;
>     import com.powernode.mybatis.POJO.Log;
>     import com.powernode.mybatis.utils.SqlSessionUtils;
>     import org.apache.ibatis.session.SqlSession;
>     import org.junit.Test;
>     
>     import java.util.Iterator;
>     import java.util.List;
>     
>     public class CarMapperTest
>     {
>         @Test
>         public void TestMapper()
>         {
>             SqlSession sqlSession = SqlSessionUtils.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             List<Car> carList = mapper.selectByCarType("新能源");
>             carList.forEach(car -> {
>                 System.out.println(car);
>             });
>             SqlSessionUtils.close(sqlSession);
>         }
>         @Test
>         public void TestDescOrAsc()
>         {
>             SqlSession sqlSession = SqlSessionUtils.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             List<Car> carList = mapper.selectAllByAscOrDesc("DESC");
>             carList.forEach(car -> {
>                 System.out.println(car);
>             });
>             SqlSessionUtils.close(sqlSession);
>         }
>         @Test
>         public void TestLog()
>         {
>             SqlSession sqlSession = SqlSessionUtils.openSession();
>             LogMapper mapper = sqlSession.getMapper(LogMapper.class);
>             List<Log> logs = mapper.selectAllByTable("20230902");
>             Iterator<Log> iterator = logs.iterator();
>             while(iterator.hasNext())
>             {
>                 System.out.println((Log)iterator.next());
>             }
>         }
>     }
> 
>     package com.powernode.mybatis.Test;
>     
>     import com.powernode.mybatis.Mapper.CarMapper;
>     import com.powernode.mybatis.Mapper.LogMapper;
>     import com.powernode.mybatis.POJO.Car;
>     import com.powernode.mybatis.POJO.Log;
>     import com.powernode.mybatis.utils.SqlSessionUtils;
>     import org.apache.ibatis.session.SqlSession;
>     import org.junit.Test;
>     
>     import java.util.Iterator;
>     import java.util.List;
>     
>     public class CarMapperTest
>     {
>         @Test
>         public void TestMapper()
>         {
>             SqlSession sqlSession = SqlSessionUtils.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             List<Car> carList = mapper.selectByCarType("新能源");
>             carList.forEach(car -> {
>                 System.out.println(car);
>             });
>             SqlSessionUtils.close(sqlSession);
>         }
>         @Test
>         public void TestDescOrAsc()
>         {
>             SqlSession sqlSession = SqlSessionUtils.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             List<Car> carList = mapper.selectAllByAscOrDesc("DESC");
>             carList.forEach(car -> {
>                 System.out.println(car);
>             });
>             SqlSessionUtils.close(sqlSession);
>         }
>         @Test
>         public void TestLog()
>         {
>             SqlSession sqlSession = SqlSessionUtils.openSession();
>             LogMapper mapper = sqlSession.getMapper(LogMapper.class);
>             List<Log> logs = mapper.selectAllByTable("20230902");
>             Iterator<Log> iterator = logs.iterator();
>             while(iterator.hasNext())
>             {
>                 System.out.println((Log)iterator.next());
>             }
>         }
>     }