JDBC的概述:
JDBC案例:
关于二次加载驱动:在 com.mysql.jdbc.Driver中有一段静态代码块,是向 DriverManager注册一个Driver实例。这样在Class.forName(“com.mysql.jdbc.Driver”)的时候,就会首先去执行这个静态代码块,于是和DriverManager.registerDriver(new Driver())有了相同的效果。 所以静态代码块已经自动加载了,而自己又手动的话便加载了两次。所以一般都是使用 Class.forName();
JDBC的API
DriverManager: 驱动管理类
Connection: 连接对象
Statement: 执行sql语句
ResultSet: 结果集
JDBC的资源释放:
@Test
/**
* JDBC资源的释放
*/
public void demo2(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1.加载驱动
//DriverManager.registerDriver(new Driver());// 会导致驱动注册两次。
Class.forName("com.mysql.jdbc.Driver");
// 2.获得连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest", "root", "abc");
// 3.创建执行SQL语句的对象,并且执行SQL
// 3.1创建执行sql的对象
String sql = "select * from user";
stmt = conn.createStatement();
// 3.2执行sql
rs = stmt.executeQuery(sql);
while(rs.next()){
int uid = rs.getInt("uid");
String username = rs.getString("username");
String password = rs.getString("password");
String name = rs.getString("name");
System.out.println(uid+" "+username+" "+password+" "+name);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
// 4.释放资源
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) { // ignore
}
rs = null;
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;// 垃圾回收机制更早回收对象。
}
}
}
JDBC工具类的使用:简化
JDBCUtils.java
package com.imooc.jdbc.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* JDBC的工具类
* @author jt
*
*/
public class JDBCUtils {
private static final String driverClass;
private static final String url;
private static final String username;
private static final String password;
static{
// 加载属性文件并解析:
Properties props = new Properties();
// 如何获得属性文件的输入流?
// 通常情况下使用类的加载器的方式进行获取:
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
props.load(is);
} catch (IOException e) {
e.printStackTrace();
}
driverClass = props.getProperty("driverClass");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
}
/**
* 注册驱动的方法
* @throws ClassNotFoundException
*/
public static void loadDriver() throws ClassNotFoundException{
Class.forName(driverClass);
}
/**
* 获得连接的方法:
* @throws SQLException
*/
public static Connection getConnection() throws Exception{
loadDriver();
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
/**
* 资源释放
*/
public static void release(Statement stmt,Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(ResultSet rs,Statement stmt,Connection conn){
if(rs!= null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
jdbc.properties
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:///jdbctest
username=root
password=abc
JDBCDemo3.java
package com.imooc.jdbc.demo1;
import java.sql.Connection;
import java.sql.Statement;
import org.junit.Test;
import com.imooc.jdbc.utils.JDBCUtils;
public class JDBCDemo3 {
@Test
// 保存记录
public void demo1(){
Connection conn = null;
Statement stmt = null;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
// 创建执行SQL语句的对象
stmt = conn.createStatement();
// 编写SQL:
String sql = "insert into user values (null,'ggg','123','小六')";
// 执行SQL:
int num = stmt.executeUpdate(sql);
if(num > 0){
System.out.println("保存成功!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
// 释放资源:
JDBCUtils.release(stmt, conn);
}
}
}
JDBC的SQL注入漏洞:
如上图,假如输入错误的账号密码则登录失败
如上图,假如在输入账号的时候输入 or ‘1=1’ 这种类似sql语句的符号 然后直接运行的话可以绕过密码校验 等
后台SQL语句会变成下图的样子 无论对错都能登录成功。
SQL注入漏洞的解决:
案例:
package com.imooc.jdbc.demo2;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
import com.imooc.jdbc.utils.JDBCUtils;
/**
* 演示JDBC的注入的漏洞
* @author jt
*
*/
public class JDBCDemo4 {
@Test
/**
* 测试SQL注入漏洞的方法
*/
public void demo1(){
boolean flag = JDBCDemo4.login2("aaa' or '1=1", "1fsdsdfsdf");
if(flag == true){
System.out.println("登录成功!");
}else{
System.out.println("登录失败!");
}
}
/**
* 避免SQL注入漏洞的方法
*/
public static boolean login2(String username,String password){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
boolean flag = false;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
// 编写SQL:
String sql = "select * from user where username = ? and password = ?";
// 预处理SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数:
pstmt.setString(1, username);
pstmt.setString(2, password);
// 执行SQL:
rs = pstmt.executeQuery();
// 判断结果街
if(rs.next()){
flag = true;
}else{
flag = false;
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
return flag;
}
/**
* 产生SQL注入漏洞的方法
* @param username
* @param password
* @return
*/
public static boolean login(String username,String password){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
boolean flag = false;
try{
conn = JDBCUtils.getConnection();
// 创建执行SQL语句的对象:
stmt = conn.createStatement();
// 编写SQL:
String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
// 执行SQL:
rs = stmt.executeQuery(sql);
// 判断结果集中是否有数据。
if(rs.next()){
flag = true;
}else{
flag = false;
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, stmt, conn);
}
return flag;
}
}
C3P0连接池:
应用程序直接获取连接的缺点:
C3P0连接池:
一开始便会创建一个容器或者说内存的一个地址,里面会放很多连接,以后很多用户来访问的话,就不需要先去创建连接了,而是从连接池拿一个连接出来,用完再归还。这样的话效率会比传统的效率高很多。
案例:
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///jdbctest</property>
<property name="user">root</property>
<property name="password">abc</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
JDBCUtils2.java
package com.imooc.jdbc.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* JDBC的工具类
* @author jt
*
*/
public class JDBCUtils2 {
private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
/**
* 获得连接的方法:
* @throws SQLException
*/
public static Connection getConnection() throws Exception{
Connection conn = dataSource.getConnection();
return conn;
}
/**
* 资源释放
*/
public static void release(Statement stmt,Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(ResultSet rs,Statement stmt,Connection conn){
if(rs!= null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
DataSourceDemo1.java
package com.imooc.jdbc.demo3;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.imooc.jdbc.utils.JDBCUtils;
import com.imooc.jdbc.utils.JDBCUtils2;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DataSourceDemo1 {
@Test
/**
* 使用配置文件的方式
*/
public void demo2(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
/*// 获得连接:
ComboPooledDataSource dataSource = new ComboPooledDataSource();*/
// 获得连接:
// conn = dataSource.getConnection();
conn = JDBCUtils2.getConnection();
// 编写Sql:
String sql = "select * from user";
// 预编译SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数
// 执行SQL:
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("uid")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils2.release(rs, pstmt, conn);
}
}
@Test
/**
* 手动设置连接池
*/
public void demo1(){
// 获得连接:
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
// 创建连接池:
ComboPooledDataSource dataSource = new ComboPooledDataSource();
// 设置连接池的参数:
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///jdbctest");
dataSource.setUser("root");
dataSource.setPassword("abc");
dataSource.setMaxPoolSize(20);
dataSource.setInitialPoolSize(3);
// 获得连接:
conn = dataSource.getConnection();
// 编写Sql:
String sql = "select * from user";
// 预编译SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数
// 执行SQL:
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("uid")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
}
}
注意:
关于C3P0配置文件:C3P0在类路径下面 自动查找一个名为 "/c3p0-config.xml"的xml文件,所以只需要把配置文件放在类路径下即可。