JDBC
1. 什么是JDBC
Java Database Connectivity java数据库连接
Java数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
没有什么是加一层解决不了的
2. 环境准备
CREATE TABLE users(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(20),
email VARCHAR(20),
brithday DATE
);
INSERT INTO users(id,`name`,`password`,email,brithday)
VALUES(1,'张三','1234','1212@qq.com','2020-8-10');
INSERT INTO users(id,`name`,`password`,email,brithday)
VALUES(2,'李四','1234','1212@qq.com','2020-8-10');
INSERT INTO users(id,`name`,`password`,email,brithday)
VALUES(3,'王五','1234','1212@qq.com','2020-8-10');
INSERT INTO users(id,`name`,`password`,email,brithday)
VALUES(4,'张三','1234','1212@qq.com','2020-8-10');
INSERT INTO users(id,`name`,`password`,email,brithday)
VALUES(5,'张三','1234','1212@qq.com','2020-8-10');
3. 测试:
public class TestJdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
String userName = "root";
String password = "root";
//加载驱动
DriverManager.registerDriver(new Driver());
// Class.forName("com.mysql.cj.jdbc.Driver");
// 创建连接
Connection connection = DriverManager.getConnection(url, userName, password);
//向数据库发送SQL的对象statement :CURD
Statement statement = connection.createStatement();
//sql语句
String sql = "select * from users";
//执行SQL语句
ResultSet res = statement.executeQuery(sql);
while (res.next()){
System.out.println(res.getObject("name"));
System.out.println(res.getObject("password"));
System.out.println(res.getObject("email"));
System.out.println(res.getObject("brithday"));
}
//关闭连接
res.close();
statement.close();
connection.close();
}
}
注意:
1. mysql 8 后面要加时区
serverTimezone=UTC
2.statement是执行SQL的对象
ResultSet res = statement.executeQuery(sql);
int i = statement.executeUpdate(sql);
查询语句使用executeQuery 返回 ResultSet (结果集)
ResultSet executeQuery(String sql) throws SQLException;
增删改使用 executeUpdate 返回 int (被影响的行数)
int executeUpdate(String sql) throws SQLException;
3.结果集ResultSet
直接看源码中的注释:
* <code>ResultSet</code> object maintains a cursor pointing
* to its current row of data. Initially the cursor is positioned
* before the first row. The <code>next</code> method moves the
* cursor to the next row, and because it returns <code>false</code>
* when there are no more rows in the <code>ResultSet</code> object,
* it can be used in a <code>while</code> loop to iterate through
* the result set.
翻译:
<code>ResultSet</code> 对象维护游标指向到当前数据行。最初定位光标在第一行之前的一个位置。当<code>next</code>方法后移时会先检查下一行是否还有数据,如果没有,就返回false。
所以我们可使用<code>while</code>循环去遍历结果集。
官方都叫我们使用while去遍历结果集
while (res.next()){
System.out.println(res.getObject("name"));
System.out.println(res.getObject("password"));
System.out.println(res.getObject("email"));
System.out.println(res.getObject("brithday"));
}
res代表一行里面的所有数据
源码:
发现ResultSet其实是一个单向的链表
/** The actual rows */
protected ResultsetRows rowData;
protected Resultset nextResultset = null;
next() 方法执行逻辑
先检查下一个结点是否为空,为空返回false
@Override
public boolean next() throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
boolean b;
if (!hasRows()) {
throw SQLError.createSQLException(Messages.getString("ResultSet.ResultSet_is_from_UPDATE._No_Data_115"),
MysqlErrorNumbers.SQL_STATE_GENERAL_ERROR, getExceptionInterceptor());
}
if (this.rowData.size() == 0) {
b = false;
} else {
this.thisRow = this.rowData.next();
if (this.thisRow == null) {
b = false;
} else {
clearWarnings();
b = true;
}
}
setRowPositionValidity();
return b;
}
}
4. jdbc固定套路
- 加载驱动
- 创建连接,代表数据库
- 向数据库发送SQL的对象statement :CURD
- 编写SQL
- 执行SQL
- 关闭连接
增删改都是Update
5. sql注入问题
举一个栗子:
1.用户登录时,后台执行的数据库查询操作(SQL语句)是:
Select * From users Where id =’用户名’ And password=’密码’
2.由于网站后台在进行数据库查询的时候没有对单引号进行过滤,当输入用户名【admin】和万能密码【1’or’1】时,执行的SQL语句为:
Select * From users Where id = 1 And password= 1 or 1
测试结果:
竟然把所有的数据全部查出来了!!!!!
3.由于SQL语句中逻辑运算符具有优先级,【=】优先于【and】,【and】优先于【or】,且适用传递性。因此,此SQL语句在后台解析时,分成两句:
Select * From users Where id = 1 And password = 1 和 1 ,两句bool值进行逻辑or运算,恒为真。
SQL语句的查询结果为TRUE,就意味着认证成功,也可以登录到系统中。输入用户名【admin】,密码【1’or’1】,即可登录成功。
解决方法:PreparedStatement
预先将sql语句进行处理避免出现上述情况
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
String url = "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
String userName = "root";
String password = "root";
//加载驱动
DriverManager.registerDriver(new Driver());
// Class.forName("com.mysql.cj.jdbc.Driver");
// 创建连接
Connection connection = DriverManager.getConnection(url, userName, password);
String SQL = "Select * From users Where id = ? And password= ?";
//向数据库发送SQL的对象statement :CURD
PreparedStatement statement = connection.prepareStatement(SQL);
statement.setInt(1,1);
statement.setString(2,"1 or 1");
ResultSet res = statement.executeQuery();
while (res.next()){
System.out.println(res.getObject("name"));
System.out.println(res.getObject("password"));
System.out.println(res.getObject("email"));
System.out.println(res.getObject("brithday"));
}
statement.close();
connection.close();
}
结果: