Mybatis快速入门

Mybatis简介

MyBatis是支持普通SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO（Plain Old Java Objects，普通的Java对象）映射成数库中的记录.

Mybatis环境搭建

• 添加依赖

<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis</artifactId>
	<version>3.4.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.21</version>
</dependency>

• mybatis配置文件

<configuration>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/test" />
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
</configuration>

• 定义表的实体类

public class User {
	private int id;
	private String name;
	private int age;
    //get,set方法
}

• 定义userMapper接口

public interface UserMapper {
	public User getUser(int id);
}

• 定义操作表的sql映射文件userMapper.xml

<?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.link.mapper.UserMapper">
	<select id="getUser" parameterType="int" resultType="com.link.entity.User">
		SELECT *
		FROM users where id =#{id}
	</select>
    <insert id="addUser" parameterType="com.link.entity.User" > 
    INSERT INTO users(NAME, age) VALUES(#{name}, #{age});
    </insert>
	<delete id="delUser" parameterType="int" >
      delete from users where id=#{id}
    </delete>
</mapper>

String resource = "mybatis.xml";
// 读取配置文件
Reader reader = Resources.getResourceAsReader(resource);
// 获取会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession openSession = sqlSessionFactory.openSession();
String sql = "com.link.mapper.UserMapper.getUser";
// 调用api查询
User user = openSession.selectOne(sql, 1);
// 调用api查询
String sql = "com.itmayiedu.mapper.UserMapper.addUser";
User userPa = new User();
userPa.setAge(19);
userPa.setName("张三");
int reuslt = openSession.insert(sql, userPa);
// 删除
String sql = "com.itmayiedu.mapper.UserMapper.delUser";
int reuslt = openSession.delete(sql,1);

sql注入案例

create table user_table(  
    id      int Primary key,  
    username    varchar(30),  
    password    varchar(30)  
);  

• jdbc进行加载

String username = "link-1";
String password = "12345";
String sql = "SELECT id,username FROM user_table WHERE " + "username='" + username + "'AND " + "password='"
				+ password + "'";
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
PreparedStatement stat = con.prepareStatement(sql);
System.out.println(stat.toString());
ResultSet rs = stat.executeQuery();
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
System.out.println("id:" + id + "---name:" + name);
}

将username的值设置为
username='  OR 1=1 --  或者username or 1='1
因为--表示SQL注释，因此后面语句忽略；
因为1=1恒成立，因此 username='' OR 1=1  恒成立

• 解决办法
  使用预编译，而其后注入的参数将不会再进行SQL编译。也就是说其后注入进来的参数系统将不会认为它会是一条SQL语句，而默认其是一个参数，参数中的or或者and 等就不是SQL语法保留字了。

String username = "username='  OR 1=1 -- ";
String password = "12345";
String sql = "SELECT id,username FROM user_table WHERE username=? AND password=?";
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
PreparedStatement stat = con.prepareStatement(sql);
stat.setString(1, username);
stat.setString(2, password);
System.out.println(stat.toString());
ResultSet rs = stat.executeQuery();
while (rs.next()) {
	String id = rs.getString(1);
	String name = rs.getString(2);
	System.out.println("id:" + id + "---name:" + name);
}

mybatis中#与$区别

动态 sql 是 mybatis 的主要特性之一，在 mapper 中定义的参数传到 xml 中之后，在查询之前 mybatis 会对其进行动态解析。mybatis 为我们提供了两种支持动态 sql 的语法：#{} 以及 ${}。

#{} 在预处理时，会把参数部分用一个占位符 ? 代替
${} 则只是简单的字符串替换

优先使用 #{}。因为 ${} 会导致 sql 注入的问题

Mybatis 注解使用

@Select("select * from users where id = ${id};")
public User getUser(@Param("id") String id);