mybatis第五天驼峰规则/自增主键的获取以及SQL注入的演示

Java 码农

已于 2024-01-25 18:11:36 修改

阅读量404

点赞数 9

文章标签： mybatis

于 2024-01-25 15:43:16 首次发布

本文链接：https://blog.csdn.net/weixin_58026642/article/details/135846291

版权

1: 开启驼峰模式

当settings 中没有开启驼峰模式的时候 ，如果数据库中存在user_address这样包含下划线的字段的时候
如果没有使用 resultMap 来手动映射 pojo 与 mysql 字段，那么 查询出来的字段会是空的
这是后可以开启驼峰模式(前提是pojo的属性是符合驼峰规范的)
如:  user_address  ----   private String userAddress;
在核心配置文件中<settings>下添加  
<setting name="mapUnderscoreToCamelCase" value="true"/>

2: 准备表以及验证开启驼峰规则以及别名

准备表:
CREATE TABLE `t_student` (
  `id` bigint AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `user_address` varchar(100) DEFAULT NULL,
  `gradeId` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_grade` (
  `id` int AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


package org.wl.pojo;

import java.io.Serializable;

public class Student implements Serializable {
    private Long id;
    private String name;
    private String stuAddress;
    private Integer age;
    private Integer gradeId;

    public Student() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getStuAddress() {
        return stuAddress;
    }

    public void setStuAddress(String stuAddress) {
        this.stuAddress = stuAddress;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getGradeId() {
        return gradeId;
    }

    public void setGradeId(Integer gradeId) {
        this.gradeId = gradeId;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", stuAddress='" + stuAddress + '\'' +
                ", age=" + age +
                ", gradeId=" + gradeId +
                '}';
    }
}
接口
public interface StudentService {
    Student findStuById(Integer id);
}
实现类
public class StudentServiceImpl implements StudentService {
    @Override
    public Student findStuById(Integer id) {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        return studentMapper.findById(id);
    }
}
mapper接口
public interface StudentMapper {
    Student findById(@Param("id") Integer id);
}
mapper.xml

<select id="findById" resultType="student">
    select * from t_student where id = #{id}
</select>
注意 resultType="student"  是因为开启了别名的配置  <package name="org.wl.pojo"/>  
   <settings>
        <setting name="logImpl" value="SLF4J"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <!-- <typeAlias alias="User" type="org.wl.pojo.User"/>  -->
        <package name="org.wl.pojo"/>  
    </typeAliases>

3 : 验证获取自增主键

useGeneratedKeys 用于在insert的时候返回自增主键
    @Test
    public void insert(){
        StudentService studentService = new StudentServiceImpl();
        Student student = new Student();
        student.setName("lin00i");
        student.setAge(20);
        student.setStuAddress("gz");
        student.setGradeId(2);
        Long id = studentService.insertStudent(student);
        System.out.println(id);
    }
    
     Long insertStudent(Student student);
         public Long insertStudent(Student student) {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        studentMapper.insertStudent(student);
        sqlSession.commit();
        sqlSession.close();
        return student.getId();
    }
    
public interface StudentMapper {
    Student findById(@Param("id") Integer id);

    Integer insertStudent(Student student);
}

<insert id="insertStudent" parameterType="student" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
    insert into t_student(name,age,stu_address,gradeId) values(#{name},#{age},#{stuAddress},#{gradeId});
</insert>

4 #{} 和 ${} 的区别

#{}和${}都可以在MyBatis中用来动态地接收参数
1: #{} ：预编译处理  MyBatis在处理#{}时，会将SQL语句中的#{}替换为?，即占位符，然后使用PreparedStatement的set方法来赋值
在拼接参数时会给参数加上引号  
2:${} ：直接替换  MyBatis在处理${}时，会直接将SQL语句中的${}替换为参数的值，拼接参数时不会加引号
3:${} 可以使用在比如排序 或者 类似于日志表根据每天存的数据分别放在不同的表,当需要查询某一天的数据的时候
动态的拼接表前缀等类似的情况下更适用
4:${} 还会产生sql 注入的问题：如
代码示例
        String id = "'admin'; DROP TABLE t_user;";
        userService.queryUserById(id);
    <select id="queryUserById" parameterType="String" resultType="user">
        select * from t_user where id = ${id}
    </select>     
查询结果
    public void testQuerySqlInject(){
        UserService userService = new UserServiceImpl();
        String id = "'admin'; DROP TABLE t_user;";
        userService.queryUserById(id);
    }   
执行完成之后 会发现数据库的表被删除了    
2024:01:25 17:02:29.750 [main] DEBUG o.wl.mapper.UserMapper.queryUserById -- ==>  Preparing: select * from t_user where id = 'admin'; DROP TABLE t_user;
2024:01:25 17:02:29.781 [main] DEBUG o.wl.mapper.UserMapper.queryUserById -- ==> Parameters: 
2024:01:25 17:02:29.863 [main] DEBUG o.wl.mapper.UserMapper.queryUserById -- <==      Total: 0
2024:01:25 17:02:29.863 [main] DEBUG o.wl.mapper.UserMapper.queryUserById -- <==    Updates: 0
2024:01:25 17:02:29.864 [main] INFO  org.wl.impl.UserServiceImpl -- user is null      
//
-- priceindex.t_user definition
备份删除的表
CREATE TABLE `t_user` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `user_address` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

将${} 变为 #{} 时
    <select id="queryUserById" parameterType="String" resultType="user">
        select * from t_user where id = #{id}
    </select>
执行日志
2024:01:25 17:09:59.522 [main] DEBUG o.wl.mapper.UserMapper.queryUserById -- ==>  Preparing: select * from t_user where id = ?
2024:01:25 17:09:59.551 [main] DEBUG o.wl.mapper.UserMapper.queryUserById -- ==> Parameters: 'admin'; DROP TABLE t_user;(String)
2024:01:25 17:09:59.626 [main] DEBUG o.wl.mapper.UserMapper.queryUserById -- <==      Total: 0
2024:01:25 17:09:59.627 [main] INFO  org.wl.impl.UserServiceImpl -- user is null 

Parameters: 'admin'; DROP TABLE t_user;(String)   这个会被预编译为一个参数 作为参数值而不会变成两个sql
不会出现sql 注入的情况