MyBatis基础知识

1 JDBC基础知识

1.1 JDBC简介

JDBC是使用Java语言操作关系型数据库的一套API，全称Java DataBase Connectivity，Java数据库连接。JDBC定义了操作所有关系型数据库的规则，同一套Java代码可以操作不同的关系型数据库。也就是JDBC是Java语言操作数据库的接口规范，MySQL、Oracle、DB2等数据库厂商实现JDBC接口，使开发者可以通过JDBC接口操作自己家的数据库。数据库自己的JDBC接口实现类叫作驱动（以jar包形式提供）。

1.2 JDBC使用

1. 导入MySQL驱动jar包

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>6.0.2</version>
</dependency>

2、入门使用

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {
    public static void main(String[] args) {
        try {
            String url = "jdbc:mysql://localhost:3306/db1";
            String user = "root";
            String password = "1234";
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            Connection connection = DriverManager.getConnection(url,user,password);
            //定义SQL语句
            String sql = "update account set monkey = 2000 where id = 1";
            //获取执行SQL的Statement
            Statement statement = connection.createStatement();
            //执行SQL，返回值代表受影响的行数
            int rowCount = statement.executeUpdate(sql);
            //释放资源
            statement.close();
            connection.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

1.3 JDBC API详解

1.3.1 DriverManager

1、注册驱动对象
public static synchronized void registerDriver(java.sql.Driver driver)
    throws SQLException {

    registerDriver(driver, null);
}



2、获取数据库连接
public static Connection getConnection(String url,
    String user, String password) throws SQLException {
    java.util.Properties info = new java.util.Properties();

    if (user != null) {
        info.put("user", user);
    }
    if (password != null) {
        info.put("password", password);
    }

    return (getConnection(url, info, Reflection.getCallerClass()));
}

1.3.2 Connection

1、获取执行SQL的Statement

//普通执行SQL对象，存在SQL注入风险
Statement createStatement() throws SQLException;
//预编译SQL的执行SQL对象，可预防SQL注入
PreparedStatement prepareStatement(String sql) throws SQLException;

2、事务管理

void setAutoCommit(boolean autoCommit) throws SQLException;
boolean getAutoCommit() throws SQLException;
void commit() throws SQLException;
void rollback() throws SQLException;

事务使用如下：

String url = "jdbc:mysql://localhost:3306/db1";
String user = "root";
String password = "1234";
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection = DriverManager.getConnection(url,user,password);
//定义SQL语句
String sql1 = "update account set monkey = 2000 where id = 1";
String sql2 = "update account set monkey = 3000 where id = 2";
//获取执行SQL的Statement
Statement statement = connection.createStatement();
try {
    //我们希望sql1和sql2同成功同失败，因此在执行SQL前开启事务
    connection.setAutoCommit(false);
    //执行SQL，返回值代表受影响的行数
    int rowCount1 = statement.executeUpdate(sql1);
    int rowCount2 = statement.executeUpdate(sql2);
    //手动提交事务
    connection.commit();
}catch (Exception e) {
    //如果执行SQL的过程中发生异常，回滚事务
    connection.rollback();
}

//释放资源
statement.close();
connection.close();

1.3.3 Statement

1. 执行DDL、DML语句

/**
 * Executes the given SQL statement, which may be an <code>INSERT</code>,
 * <code>UPDATE</code>, or <code>DELETE</code> statement or an
 * SQL statement that returns nothing, such as an SQL DDL statement.
 *<p>
 * <strong>Note:</strong>This method cannot be called on a
 * <code>PreparedStatement</code> or <code>CallableStatement</code>.
 * @param sql an SQL Data Manipulation Language (DML) statement, such as <code>INSERT</code>, <code>UPDATE</code> or
 * <code>DELETE</code>; or an SQL statement that returns nothing,
 * such as a DDL statement.
 *
 * @return either (1) the row count for SQL Data Manipulation Language (DML) statements
 *         or (2) 0 for SQL statements that return nothing
 *
 * @exception SQLException if a database access error occurs,
 * this method is called on a closed <code>Statement</code>, the given
 * SQL statement produces a <code>ResultSet</code> object, the method is called on a
 * <code>PreparedStatement</code> or <code>CallableStatement</code>
 * @throws SQLTimeoutException when the driver has determined that the
 * timeout value that was specified by the {@code setQueryTimeout}
 * method has been exceeded and has at least attempted to cancel
 * the currently running {@code Statement}
 */
int executeUpdate(String sql) throws SQLException;

返回值：1、DML语句返回影响的行数 2、DDL语句执行成功后返回0

2. 执行DQL语句

/**
 * Executes the given SQL statement, which returns a single
 * <code>ResultSet</code> object.
 *<p>
 * <strong>Note:</strong>This method cannot be called on a
 * <code>PreparedStatement</code> or <code>CallableStatement</code>.
 * @param sql an SQL statement to be sent to the database, typically a
 *        static SQL <code>SELECT</code> statement
 * @return a <code>ResultSet</code> object that contains the data produced
 *         by the given query; never <code>null</code>
 * @exception SQLException if a database access error occurs,
 * this method is called on a closed <code>Statement</code>, the given
 *            SQL statement produces anything other than a single
 *            <code>ResultSet</code> object, the method is called on a
 * <code>PreparedStatement</code> or <code>CallableStatement</code>
 * @throws SQLTimeoutException when the driver has determined that the
 * timeout value that was specified by the {@code setQueryTimeout}
 * method has been exceeded and has at least attempted to cancel
 * the currently running {@code Statement}
 */
ResultSet executeQuery(String sql) throws SQLException;

1.3.3 SQL注入问题

SQL注入是通过输入特定字符来修改事先已经定义好的SQL语句，用来达到执行代码对服务器进行攻击的方式。下面以输入用户名、 密码来登录为例说明。

正常情况，登录成功：

try {
    String url = "jdbc:mysql://localhost:3306/db1";
    String user = "root";
    String password = "1234";
    //注册驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获取连接
    Connection connection = DriverManager.getConnection(url, user, password);
    String name = "zhangsan";
    String pwd = "123";
    //定义SQL语句
    String sql = "select * from user where username='" + name + "' and password='" + pwd + "'";
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    if (resultSet.next()) {
        System.out.println("登录成功");
    } else {
        System.out.println("登录失败");
    }
    //释放资源
    resultSet.close();
    statement.close();
    connection.close();
} catch (Exception e) {
    throw new RuntimeException(e);
}

输入敏感字符，存在注入情况，也可以登录成功：

try {
    String url = "jdbc:mysql://localhost:3306/db1";
    String user = "root";
    String pwd = "' or '1' ='1";
    //注册驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获取连接
    Connection connection = DriverManager.getConnection(url, user, password);
    String name = “用户名随便写” ;
    String pwd = "123";
    //定义SQL语句
    String sql = "select * from user where username='" +