JDBC(Java Data Base Connectivity)
DBC 就是由 java提供的一套访问数据库的统一api. 使用这套api , 我们在 切换库时 十分方便. 并且切换库不会改变代码.学习成本也降低了.
开发一个jdbc程序的流程
1、导包 ==> 导入厂商提供的数据库驱动. ==> 我目前使用的mysql-connector-java-5.1.24-bin.jar
> Project Structure里选择Modules，然后选择Denpendencies，点击绿色"+"号，选择Jars。
2、注册驱动
3、连接数据库
4、操作数据库(执行sql)
5、关闭资源
一个简单的例子
这里使用了jUnit5来做单元测试。写成public void 方法名，每一个方法都相当于main主函数，可以单独运行，也可以同时运行多个。
package JDBC;
import org.junit.jupiter.api.Test;
import java.sql.*;
public class Demo {
@Test
public void fun1() throws SQLException {
// 导入驱动类库
// 注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 连接数据库
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/book", "root", "admin");
// 操作数据库
Statement statement = connection.createStatement();
String sql = "INSERT INTO `stu` " +
"VALUES('S_1120', 'sunhaiyu', '13', 'male');";
// 执行sql语句
statement.executeUpdate(sql);
// 关闭资源
statement.close();
connection.close();
}
@Test
public void fun2() throws SQLException {
// 导入驱动类库
// 注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 连接数据库
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/book", "root", "admin");
// 操作数据库
Statement statement = connection.createStatement();
String sql = "select * from stu;";
// 执行sql语句
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString("sname");
int age = resultSet.getInt("age");
String gender = resultSet.getString("gender");
System.out.println("学生姓名：" + name + " | 年龄：" + age + " | 性别：" + gender);
}
// 关闭资源
resultSet.close();
statement.close();
connection.close();
}
}
JDBC中的类
DriverManager 用于注册驱动,获得连接
Connection 代表连接 , 获得Statement对象
Statement 运送sql语句
ResultSet 将运行结果从数据库运回java端
注册
上面的注册方式DriverManager.registerDriver(new com.mysql.jdbc.Driver());并不推荐。使用反射机制动态加载更好。
public class JDBCTest {
@Test
public void fun1() {
// 注册方式1，并不推荐,因为Driver类中的静态代码块里已经执行过,如下
// java.sql.DriverManager.registerDriver(new Driver());
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 注册方式2，动态加载类。在Driver类的代码中,有一个静态代码块。静态代码块中已经做了注册驱动的事情。 所以我们只需要加载驱动类，就相当于调用了 registDriver 方法。
Class.forName("com.mysql.jdbc.Driver");
}
}
使用 Class.forName有什么好处?
如果调用registDriver 方法, 那么相当于创建了两个Driver对象,浪费资源。
使用forName的方式. 因为Driver类的名称是以字符串的形式填写，那么我们把该名称放到配置文件中，每次从配置文件中读取。那么切换驱动类就非常方便. 也就意味着切换数据库方便。
获得Connection
@Test
public void fun2() throws SQLException {
String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf-8";
Connection connection = DriverManager.getConnection(url, "root", "admin");
}
DriverManager.getConnection("url","用户名","密码");
url 填写格式:
外层协议:内部协议://主机名称[ip地址]:端口号/库名?参数键1=参数值&参数键2=参数值
jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf-8
结合上面说的方便切换数据库。我们在书写时,也可以把上面3个参数,写到配置文件中。
功能
- 代表数据库的链接
- 可以根据该对象创建运送sql语句的Statement对象
方法
-
Statement createStatement()创建statement对象 -
PreparedStatement prepareStatement(String sql)创建 PreparedStatement 对象
Statement对象
将sql语句运送给数据库
@Test
// execute() 增删改查都可以，返回boolean值，true有查询集合（查），false表示没有查询集合（增删改）
// executeBatch() 批量
// executeUpdate() 增删改
// executeQuery() 查询，可以返回查询集，使用方便
public void fun2() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf-8";
Connection connection = DriverManager.getConnection(url, "root", "admin");
Statement statement = connection.createStatement();
String sql = "select * from stu;";
// execute()返回布尔值，需要先判断后再获取，和下面注释的写法一样
// ResultSet resultSet = statement.executeQuery(sql);
boolean result = statement.execute(sql);
if (result) {
ResultSet resultSet = statement.getResultSet();
}
System.out.println(result); // 查询有结果，是true
// 关闭资源
statement.close();
connection.close();
}
ResultSet结果集
getInt() getString()等方法，可以传入两种参数。
- 列的索引，从1开始。表示第一行。
- 传入键
public class ResultSetTest {
@Test
public void fun() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf-8";
Connection connection = DriverManager.getConnection(url, "root", "admin");
Statement statement = connection.createStatement();
String sql = "select * from stu;";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
// 第一列是id，所以从第二行开始
String name = resultSet.getString(2); // 可以传入列的索引，1代表第一行，索引不是从0开始
// 上面的语句等同于 String name = resultSet.getString("sname");
int age = resultSet.getInt(3);
String gender = resultSet.getString(4);
System.out.println("学生姓名：" + name + " | 年龄：" + age + " | 性别：" + gender);
}
// 关闭资源
resultSet.close();
statement.close();
connection.close();
}
}
获取不同类型的数据库数据
| 数据库数据类型 | 对应的getxx()方法 |
|---|---|
| char/varchar | getString |
| int | getInt |
| bigint | getLong |
| float/double | getFloat/getDouble |
| datetime/timestamp | getDate就行 |
一个JDBC工具类
具有获得连接和释放资源的功能
package jdbc;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtil {
private static String driver;
private static String url;
private static String user;
private static String password;
// 静态代码块，随着类的加载而加载，只加载一次
static {
try {
Properties prop = new Properties();
// load()接收InputStream，所以向上转型
InputStream is = new FileInputStream("src/jdbc/jdbc_setting.properties");
prop.load(is);
driver = prop.getProperty("ClassName");
url = prop.getProperty("url");
user = prop.getProperty("user");
password = prop.getProperty("password");
Class.forName(driver);
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("创建连接失败！");
}
return connection;
}
// 释放资源
// 参数可能为空
// 调用close要抛出异常，即使出现异常也能关闭
// Connection获得了Statemenet，又由Statement获得了ResuyltSet。关闭的顺序得相反
// 调用的顺序,从小到大释放. resultSet < Statement < Connection
public void close(Connection conn, Statement state, ResultSet result) {
try {
if (result != null) {
result.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (state != null) {
state.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}
对应的jdbc_setting.properties如下
ClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/book
user=root
password=admin
SQL注入
早年登录逻辑,就是把用户在表单中输入的用户名和密码 带入如下sql语句。如果查询出结果,那么 认为登录成功.
SELECT * FROM USER WHERE NAME='' AND PASSWORD='xxx';
sql注入: 请尝试以下 用户名和密码.
/* 用户名:
密码: xxx
*/
-- 将用户名和密码带入sql语句, 如下:
SELECT * FROM USER WHERE NAME='xxx' OR 1=1 -- ' and password='xxx';
-- 发现sql语句失去了判断效果,条件部分成为了恒等式.
-- 导致网站可以被非法登录, 以上问题就是sql注入的问题.
思考会出现什么问题?
-- 将用户名密码带入sql语句,发现sql语句变成了如下形式:
SELECT * FROM t_student WHERE NAME='abcd'OR 1=1;-- ' AND PASSWORD='1234';
-- 该sql语句就是一个恒等条件。所以一定会查询出记录。造成匿名登陆.有安全隐患
如上问题，是如何解决呢?
1、解决办法: 在运送sql时,我们使用的是Statement对象。如果换成prepareStatement对象,那么就不会出现该问题.
2、sql语句不再直接拼写.而要采用预编译的方式来做.
完成如上两步.即可解决问题。
PrepareStatement
为什么使用PrepareStatement对象能解决问题?
sql的执行需要编译，注入问题之所以出现，是因为用户填写 sql语句 参与了编译。使用PrepareStatement对象在执行sql语句时，会分为两步.，第一步将sql语句 "运送" 到mysql上预编译，再回到java端拿到参数运送到mysql端。预先编译好，也就是SQL引擎会预先进行语法分析，产生语法树，生成执行计划，也就是说，后面你输入的参数，无论你输入的是什么，都不会影响该sql语句的 语法结构了用户填写的 sql语句，就不会参与编译. 只会当做参数来看. 避免了sql注入问题。
PrepareStatement 在执行母句相同，参数不同的批量执行时。因为只会编译一次，节省了大量编译时间，效率会高。
下面这个例子无论输入什么都会登录成功。
package jdbc;
import java.sql.*;
public class Mytest {
public static void main(String[] args) throws SQLException {
// String username = "bob";
// 这里是完整的sql语句，需要单引号
String username = "xxx' OR 1=1 -- "; // sql注入，
String password = "123456";
Connection connection = JDBCUtil.getConnection();
Statement statement = connection.createStatement();
String sql = "SELECT * FROM login WHERE username='"+username+"' AND password='"+password+"';";
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
}
}
使用PreparedStatement可以防止sql注入，运用的是绑定变量。
用户自己输入的语句，会被当成一种参数一个字段的属性而不会被当成SQL语句，而且预编译可提高效率。
package jdbc;
import java.sql.*;
public class Mytest {
public static void main(String[] args) throws SQLException {
// String username = "bob";
// 这里无需单引号
String username = "xxx OR 1=1 -- "; // sql注入，
String password = "123456";
Connection connection = JDBCUtil.getConnection();
// 这里改成通配符?
String sql = "SELECT * FROM login WHERE username=? AND password=?;";
// 先送过去预编译
PreparedStatement ps = connection.prepareStatement(sql);
// 设置参数给ps对象
// 这里再次回来拿参数，用户自己输入的语句，会被当成一种参数一个字段的属性而不会被当成SQL语句
ps.setString(1, username);
ps.setString(2, password);
// executeQuery()里不带参数，因为事先预编译过了
ResultSet resultSet = ps.executeQuery();
if (resultSet.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
}
}
向mysql保存大文本
测试的文本11k，远远超过255字节，数据类型使用text。
package jdbc;
import org.junit.jupiter.api.Test;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class BigText {
// 向mysql存放大文本（255字节以上）
// 必须使用PreparedStatement
@Test
public void fun() throws Exception {
Connection connection = JDBCUtil.getConnection();
// id自增所以设为NULL
String sql = "INSERT INTO mytext VALUES(NULL, ?)";
PreparedStatement ps = connection.prepareStatement(sql);
File f = new File("src/jdbc/big_text.txt");
BufferedReader reader = new BufferedReader(new FileReader(f));
System.out.println(f.length());
/* 设置参数，参数1 --> 索引（看问号）
* 参数2 --> 需要保存的文本
* 参数3 --> 文件长度,f.length指定为保存全部内容*/
ps.setCharacterStream(1, reader, f.length());
int result = ps.executeUpdate();
// 看看几行被影响，应该是1行
System.out.println(result);
// 关闭资源
reader.close();
JDBCUtil.close(connection, ps, null);
}
}
批量执行
先打包多条sql语句，然后一并执行。
package jdbc;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
public class BatchProcessing {
// 1、使用Statement批量执行
@Test
public void fun1() throws Exception {
Connection connection = JDBCUtil.getConnection();
Statement st = connection.createStatement();
// 添加多条语句到st
st.addBatch("CREATE TABLE haha (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));");
st.addBatch("INSERT INTO haha VALUES(NULL, 'Tom')");
st.addBatch("INSERT INTO haha VALUES(NULL, 'Jerry')");
st.addBatch("INSERT INTO haha VALUES(NULL, 'Jack')");
st.addBatch("INSERT INTO haha VALUES(NULL, 'Rose')");
// 打包了几条语句，数组里面就有几个，每一个数代表改变的行数
int[] results = st.executeBatch();
System.out.println(Arrays.toString(results));
JDBCUtil.close(connection, st, null);
}
// 2、使用PreparedStatement批量执行
@Test
public void fun2() throws Exception {
Connection connection = JDBCUtil.getConnection();
String sql = "INSERT INTO haha VALUES(NULL, ?)";
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < 20; i++) {
ps.setString(1, "用户"+i);
ps.addBatch();
}
int[] results = ps.executeBatch();
System.out.println(Arrays.toString(results));
JDBCUtil.close(connection, ps, null);
}
}
Statement批量执行和PreparedStatement批量执行哪个好？
- PreparedStatement只编译一次，多次执行。而且Statement编译多次，执行多次。效率上来说PreparedStatement完胜。
- Statement使用更方便，可以插入不同类型的sql语句。而PreparedStatement只能将一种类型的sql语句，执行多次。
2017.3.13
by @sunhaiyu
