八、JDBC
8.1 数据库驱动
应用程序 (JDBC)——>数据库驱动——>数据库
’
8.2 JDBC
mysql-connector-java详解_毛惜时的博客-CSDN博客_mysql-connector-java
8.3 JDBC程序
创建数据库
CREATE DATABASE `jdbcstudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcstudy
CREATE TABLE IF NOT EXISTS `users`(
`id` INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40) ,
`email` VARCHAR(60),
`birthday` DATE
);
INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) VALUES(1,'张三','aaaaaa','as@sina.com','1980-12-04'),(2,'李四','bbbbb','wqe@qq.com','1990-05-01'),(3,'王五','ccccccc','aszxc@163.com','2000-01-04')
导入JDBC
package com.lxf.lesson01;
import com.mysql.jdbc.Driver;
import java.sql.*;
public class JdbcFirstDemo {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//连接数据库
String url = "jdbc:mysql://localhost:13306/jdbcstudy?useUnicode=true&characterEnconding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//连接成功,返回数据库对象
Connection connection = DriverManager.getConnection(url,username,password);
//执行sql命令
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
//查看返回结果
while (resultSet.next()){
System.out.println("=======================");
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("password="+resultSet.getObject("password"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
}
//释放连接
resultSet.close();
statement.close();
connection.close();
}
}
输出:
=======================
id=1
name=张三
password=aaaaaa
email=as@sina.com
birthday=1980-12-04
=======================
id=2
name=李四
password=bbbbb
email=wqe@qq.com
birthday=1990-05-01
=======================
id=3
name=王五
password=ccccccc
email=aszxc@163.com
birthday=2000-01-04
进程已结束,退出代码0
DriverManager
URL
Statement 执行SQL对象
ResultSet 查询的结果集:封装了所有的查询结果
释放连接
8.4 Statement对象
配置文件
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:13306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456
工具类
package com.lxf.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null ;
private static String url = null ;
private static String username = null ;
private static String password = null ;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
public static void release(Connection connection, Statement statement,ResultSet resultSet) throws SQLException {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Delete
package com.lxf.lesson02;
import com.lxf.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection con = null ;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
String sql = "DELETE FROM users where id = 6";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(con,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Select
package com.lxf.lesson02;
import com.lxf.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection con = null ;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
String sql = "SElECT * FROM users WHERE id = 5";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(con,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Update
package com.lxf.lesson02;
import com.lxf.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection con = null ;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
String sql = "UPDATE users SET `name` = '李晓峰' WHERE id = 5";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(con,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Insert
package com.lxf.lesson02;
import com.lxf.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection con = null ;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) VALUES( 6,'撒','aaaaaa','as@sina.com','1980-12-04')";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(con,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
sql 注入
package com.lxf.lesson02;
import com.lxf.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlInjection {
public static void main(String[] args) {
login("lxf","aaaaaa");
login(" 'or '1=1"," 'or '1=1");
}
public static void login(String username , String password){
Connection con = null ;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
// SElECT * FROM users WHERE `name` = ''or '1=1' AND `password` = ''or '1=1'
String sql = "SElECT * FROM users WHERE `name` = '"+username+"' AND `password` = '"+password+"'";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(con,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
输出
lxf
aaaaaa
张三
aaaaaa
李四
bbbbb
王五
ccccccc
??
aaaaaa
lxf
aaaaaa
SQL是操作数据库数据的结构化查询语言,网页的应用数据和后台数据库中的数据进行交互时会采用SQL。而SQL注入是将Web页面的原URL、表单域或数据包输入的参数,修改拼接成SQL语句,传递给Web服务器,进而传给数据库服务器以执行数据库命令。如Web应用程序的开发人员对用户所输入的数据或cookie等内容不进行过滤或验证(即存在注入点)就直接传输给数据库,就可能导致拼接的SQL被执行,获取对数据库的信息以及提权,发生SQL注入攻击。
避免SQL注入
对于 SQL 注入,我们可以采取适当的预防措施来保护数据安全。下面是避免 SQL 注入的一些方法。
- 过滤输入内容,校验字符串
过滤输入内容就是在数据提交到数据库之前,就把用户输入中的不合法字符剔除掉。可以使用编程语言提供的处理函数或自己的处理函数来进行过滤,还可以使用正则表达式匹配安全的字符串。
如果值属于特定的类型或有具体的格式,那么在拼接 SQL 语句之前就要进行校验,验证其有效性。比如对于某个传入的值,如果可以确定是整型,则要判断它是否为整型,在浏览器端(客户端)和服务器端都需要进行验证。
- 参数化查询
参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时,在需要填入数值或数据的地方,使用参数(Parameter)来给值。
MySQL 的参数格式是以“?”字符加上参数名称而成,如下所示:
UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4
在使用参数化查询的情况下,数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理,而是在数据库完成 SQL 语句的编译之后,才套用参数运行。因此就算参数中含有破坏性的指令,也不会被数据库所运行。
- 安全测试、安全审计
除了开发规范,还需要合适的工具来确保代码的安全。我们应该在开发过程中应对代码进行审查,在测试环节使用工具进行扫描,上线后定期扫描安全漏洞。通过多个环节的检查,一般是可以避免 SQL 注入的。
有些人认为存储过程可以避免 SQL 注入,存储过程在传统行业里用得比较多,对于权限的控制是有一定用处的,但如果存储过程用到了动态查询,拼接 SQL,一样会存在安全隐患。
下面是在开发过程中可以避免 SQL 注入的一些方法。
- 避免使用动态SQL
避免将用户的输入数据直接放入 SQL 语句中,最好使用准备好的语句和参数化查询,这样更安全。
- 不要将敏感数据保留在纯文本中
加密存储在数据库中的私有/机密数据,这样可以提供了另一级保护,以防攻击者成功地排出敏感数据。
- 限制数据库权限和特权
将数据库用户的功能设置为最低要求;这将限制攻击者在设法获取访问权限时可以执行的操作。
- 避免直接向用户显示数据库错误
攻击者可以使用这些错误消息来获取有关数据库的信息。
8.5 PreparedStatement
package com.lxf.lesson02;
import com.lxf.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SqlInjectionDemo {
public static void main(String[] args) {
//login("lxf","aaaaaa");
login("' 'or '1=1","' 'or '1=1");
}
public static void login(String username , String password){
Connection con = null ;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
// SElECT * FROM users WHERE `name` = ''or '1=1' AND `password` = ''or '1=1'
String sql = "SElECT * FROM users WHERE `name` =? AND `password` =?";
st = con.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();
while (rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(con,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
8.6 IDEA 连接数据库
8.7 事务
package com.lxf.lesson02;
import com.lxf.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestTransaction {
public static void main(String[] args) {
Connection con = null ;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
con.setAutoCommit(false);//关闭数据自动提交,自动会开启事务
String sql1 = "UPDATE account SET money = money - 100 WHERE name = 'A'";
st = con.prepareStatement(sql1);
st.executeUpdate();
int x = 1/0;
String sql2 = "UPDATE account SET money = money + 100 WHERE name = 'B'";
st = con.prepareStatement(sql2);
st.executeUpdate();
con.commit();
System.out.println("success");
} catch (SQLException e) {
try {
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
try {
JdbcUtils.release(con, st, rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
8.9 数据库连接池
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
DBCP
commons-dbcp-1.4、common-pool-1.6
//配置文件
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:13306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456
//工具
package com.lxf.lesson03.utils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
System.out.println("11111111");
return dataSource.getConnection();
}
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//测试类
package com.lxf.lesson03;
import com.lxf.lesson03.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestDBCP {
public static void main(String[] args) {
Connection con = null ;
PreparedStatement st = null;
try {
con = JdbcUtils_DBCP.getConnection();
String sql = "insert into users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
st = con.prepareStatement(sql);
st.setInt(1,7);
st.setString(2,"asdqw");
st.setString(3,"asdasd");
st.setString(4,"asdasd@qq.com");
st.setDate(5,new java.sql.Date(new Date().getTime()));
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("操作失败");
}finally {
try {
JdbcUtils_DBCP.release(con,st,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
C3P0
c3p0-0.9.5.5、mchange-commons-java-0.2.19
//配置文件 c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- This is default config! -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:13306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquireIncrement">10</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
<property name="minPoolSize">5</property>
</default-config>
</c3p0-config>
//工具
package com.lxf.lesson03.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
try {
// dataSource.setDriverClass("com.mysql.jdbc.Driver");
// dataSource.setUser("root");
// dataSource.setPassword("123456");
// dataSource.setJdbcUrl("jdbc:mysql://localhost:13306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true");
// dataSource.setMaxPoolSize(15);
// dataSource.setMinPoolSize(5);
//可以传参数
dataSource =new ComboPooledDataSource();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//测试
package com.lxf.lesson03;
import com.lxf.lesson03.utils.JdbcUtils_C3P0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestC3P0 {
public static void main(String[] args) {
Connection con = null ;
PreparedStatement st = null;
try {
con = JdbcUtils_C3P0.getConnection();
String sql = "INSERT INTO `users`(id,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";
st = con.prepareStatement(sql);
st.setInt(1,12);
st.setString(2,"asdqw");
st.setString(3,"asdasd");
st.setString(4,"asdasd@qq.com");
st.setDate(5,new java.sql.Date(new Date().getTime()));
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils_C3P0.release(con,st,null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}