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使用
导入MySQL驱动jar包
mysql mysql-connector-java 6.0.2
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的StatementStatement 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
执行DDL、DML语句
/*** Executes the given SQL statement, which may be an INSERT,* UPDATE, or DELETE statement or an* SQL statement that returns nothing, such as an SQL DDL statement.** Note:This method cannot be called on a* PreparedStatement or CallableStatement.* @param sql an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or* DELETE; 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 Statement, the given* SQL statement produces a ResultSet object, the method is called on a* PreparedStatement or CallableStatement* @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
执行DQL语句
/*** Executes the given SQL statement, which returns a single* ResultSet object.** Note:This method cannot be called on a* PreparedStatement or CallableStatement.* @param sql an SQL statement to be sent to the database, typically a* static SQL SELECT statement* @return a ResultSet object that contains the data produced* by the given query; never null* @exception SQLException if a database access error occurs,* this method is called on a closed Statement, the given* SQL statement produces anything other than a single* ResultSet object, the method is called on a* PreparedStatement or CallableStatement* @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='" + 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);
}注入后的SQL语句如下：
select * from user where username =‘用户名随便写’ and password =‘ ’ or ‘1’=‘1’
1.3.4 PreparedStatement
String url = "jdbc:mysql://localhost:3306/db1?userServerPrepStmts=true";
String user = "root";
String password = "1234";
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection = DriverManager.getConnection(url, user, password);
//定义SQL语句
String sql = "select * from user where username=? and password=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"zhangsan");
preparedStatement.setString(2,"1234");
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {System.out.println("登录成功");
} else {System.out.println("登录失败");
}
//释放资源
resultSet.close();
preparedStatement.close();
connection.close();PreparedStatement在setXXX设置参数时会将传入敏感字符进行转义，从而解决SQL注入问题。
预编译功能默认是关闭的，通过配置URL可以打开预编译功能，jdbc:mysql://localhost:3306/db1?userServerPrepStmts=true
1.3.5 ResultSet
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 = "select * from user";
//获取执行SQL的Statement
Statement statement = connection.createStatement();
ResultSet set = statement.executeQuery(sql);
while (set.next()) {int id = set.getInt("id”);//传入列的字段名String name = set.getString("name”);//传入列的字段名String pd = set.getString(3);//传入列的编号
}
//释放资源
set.close();
statement.close();
connection.close();1.4 数据库连接池
1.4.1 数据库连接池简介
建立数据库连接很耗时、关闭数据库连接也耗时
1.4.2 Druid数据库连接池
1、导入jar包
com.alibaba druid 1.1.16
mysql mysql-connector-java 6.0.2
2、定义配置文件
新建druid.properties文件，并配置如下：
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1?userServerPrepStmts=true
username=root
password=1234
initialSize=5
maxActive=10
#最大等待时间，单位毫秒
maxWait=3000注意健的名称固定
3、加载配置文件、获取数据库连接池对象、获取连接
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;public class Main {public static void main(String[] args) {try {Properties properties = new Properties();properties.load(new FileInputStream("druid.properties"));DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);Connection connection = dataSource.getConnection();} catch (Exception e) {throw new RuntimeException(e);}}
}2 MyBatis基础知识
2.1 简介
官网：https://mybatis.org/mybatis-3/zh/index.html
