JDBC基础语法、statement对象和PreparedStatement对象、SQL注入及解决、数据库连接池等详解

程序员文章站 2022-05-09 10:37:19

...

一、JDBC

1、数据库驱动

数据库驱动是应用程序与数据库之间沟通的桥梁，我们的程序通过数据库驱动与数据库打交道。

JDBC基础语法、statement对象和PreparedStatement对象、SQL注入及解决、数据库连接池等详解

2、JDBC

JDBC(java database connectivity)驱动程序是对JDBC规范完整的实现，它的存在在JAVA程序与数据库系统之间建立了一条通信的渠道。由数据库厂家负责实现

需要导入数据库驱动包 mysql-connector-java-5.1.47.jar

JDBC基础语法、statement对象和PreparedStatement对象、SQL注入及解决、数据库连接池等详解

3、第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE 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,'zhansan','123456','aaa@qq.com','1980-12-04'),
(2,'lisi','123456','aaa@qq.com','1981-12-04'), 
(3,'wangwu','123456','aaa@qq.com','1979-12-04');

导入数据库驱动包

创建一个项目
导入数据库驱动包
添加依赖（add as library）

编写测试代码

import java.sql.*;

// 我的第一个JDBC程序 
public class JdbcFirstDemo {   
    public static void main(String[] args) throws ClassNotFoundException, SQLException {    
        //1. 加载驱动    
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动
        //2. 用户信息和url    
        // useUnicode=true&characterEncoding=utf8&useSSL=true  
        String url = "jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&characterEncoding=utf8&useSSL=true";     
        String username = "root";
        String password = "123456";    
        
        //3. 连接成功，数据库对象  Connection 代表数据库   
        Connection connection = DriverManager.getConnection(url, username, password);
        
        //4. 执行SQL的对象 Statement 执行sql的对象     
        Statement statement = connection.createStatement();
        
        //5. 执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果  
        String sql = "SELECT * FROM users";
        
        ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,结果 集中封装了我们全部的查询出来的结果
        
        while (resultSet.next()){   
            System.out.println("id=" + resultSet.getObject("id"));          
            System.out.println("name=" + resultSet.getObject("NAME"));   
            System.out.println("pwd=" + resultSet.getObject("PASSWORD"));   
            System.out.println("email=" + resultSet.getObject("email"));    
            System.out.println("birth=" + resultSet.getObject("birthday"));   
       }
        
        //6、释放连接   
        resultSet.close();   
        statement.close();   
        connection.close();
    } 
}

步骤

加载驱动
连接数据库 DriverManager
获得执行sql的对象 Statement
获得返回的结果集
释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 
Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动 
Connection connection = DriverManager.getConnection(url, username, password);

// connection 代表数据库
// 数据库设置自动提交
// 事务提交 
// 事务滚回 
connection.rollback(); 
connection.commit();
connection.setAutoCommit();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&characterEncoding=utf8&useSSL=true";

// mysql -- 3306 
// 协议 ://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce -- 1521 
//jdbc:oracle:thin:@localhost:1521:sid

Statement 执行SQL 的对象 PrepareStatement 执行SQL 的对象

String sql = "SELECT * FROM users"; // 编写SQL

statement.executeQuery(); //查询操作返回 ResultSet 
statement.execute(); // 执行任何SQL
statement.executeUpdate();  // 更新、插入、删除。都是用这个，返回一个受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getObject(); // 在不知道列类型的情况下使用 
// 如果知道列的类型就使用指定的类型
resultSet.getString(); 
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject(); 
....

遍历、指针

resultSet.beforeFirst();  // 移动到前面 
resultSet.afterLast();   // 移动到后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行

释放资源

resultSet.close(); 
statement.close();
connection.close(); // 耗资源，用完关掉！

4、statement对象

Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。

Statement对象的Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的 ResultSet对象。

create操作

使用executeUpdate(String sql)方法完成数据添加操作

Statement st = conn.createStatement(); 
String sql = "insert into user(….) values(…..) ";
int num = st.executeUpdate(sql); 
if(num>0){   
    System.out.println("插入成功！！！"); 
}

delete操作

使用executeUpdate(String sql)方法完成数据删除操作

Statement st = conn.createStatement(); 
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){  
    System.out.println(“删除成功！！！"); 
}

update操作

使用executeUpdate(String sql)方法完成数据修改操作

Statement st = conn.createStatement(); 
String sql = "update user set name='' where name=''"; 
int num = st.executeUpdate(sql); 
if(num>0){   
    System.out.println(“修改成功！！！");
}

read操作

使用executeQuery(String sql)方法完成数据查询操作

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql); 
while(rs.next()){   
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

代码实现

在 src 下建立资源文件 db.properties

driver=com.mysql.jdbc.Driver 
url=jdbc:mysql://localhost:3306/jdbcstudy? useUnicode=true&characterEncoding=utf8&useSSL=true 
username=root 
password=123456

提取连接工具类

import java.io.IOException;
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");
            
            //1.驱动只用加载一次        
            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 conn, Statement st, ResultSet rs){   
        if (rs!=null){   
            try {       
                rs.close();     
            } catch (SQLException e) {  
                e.printStackTrace();   
            }    
        }     
        if (st!=null){    
            try {      
                st.close();  
            } catch (SQLException e) {    
                e.printStackTrace();    
            }      
        }       
        if (conn!=null){    
            try {       
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();   
            }      
        }  
    }
}

编写增删改的方法， executeUpdate

//导入上面的连接工具类
//插入类

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 conn = null;  
        Statement st = null;      
        ResultSet rs = null;
        
        try {
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement(); //获得SQL的执行对象
            
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +                    "VALUES(4,'kuangshen','123456','aaa@qq.com','202001-01')";
            
            int i = st.executeUpdate(sql);   
            if (i>0){          
                System.out.println("插入成功！");   
            }
        } catch (SQLException e) {    
            e.printStackTrace();    
        } finally {        
            JdbcUtils.release(conn,st,rs);    
        }
    }
}

//导入上面的连接工具类
//删除类

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 conn = null;    
        Statement st = null;     
        ResultSet rs = null;
        
        try {        
            conn = JdbcUtils.getConnection(); //获取数据库连接  
            st = conn.createStatement(); //获得SQL的执行对象
            
            String sql = "DELETE FROM users WHERE id = 4";
            
            int i = st.executeUpdate(sql);    
            if (i>0){           
                System.out.println("删除成功！");    
            }
        } catch (SQLException e) {    
            e.printStackTrace();   
        } finally {         
            JdbcUtils.release(conn,st,rs);   
        }
    }
}

//导入上面的连接工具类
//更新类

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 conn = null;     
        Statement st = null;    
        ResultSet rs = null;
        
        try {          
            conn = JdbcUtils.getConnection(); //获取数据库连接  
            st = conn.createStatement(); //获得SQL的执行对象
            
            String sql = "UPDATE users SET `NAME`='kuangshen',`email`='aaa@qq.com' WHERE id=1";
            
            int i = st.executeUpdate(sql);     
            if (i>0){         
                System.out.println("更新成功！"); 
            }
        } catch (SQLException e) {    
            e.printStackTrace();    
        } finally {        
            JdbcUtils.release(conn,st,rs);  
        }
    } 
}

查询executeQuery

//导入上面的连接工具类
//查询类

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 conn =null;     
        Statement st = null;     
        ResultSet rs = null; 
        try {           
            conn = JdbcUtils.getConnection();  
            st = conn.createStatement();
            //SQL         
            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql); //查询完毕会返回一个结果集 
            while (rs.next()){     
                System.out.println(rs.getString("NAME"));  
            }                  
        } catch (SQLException e) {    
            e.printStackTrace();    
        } finally {        
            JdbcUtils.release(conn,st,rs); 
        }
    } 
}

SQL注入问题

sql 存在漏洞，会被攻击导致数据泄露，SQL会被拼接 or

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("kuangshen","123456");    
        login(" 'or '1=1"," 'or'1=1"); // 技巧
    }
    
    // 登录业务   
    public static void login(String username,String password){
        
        Connection conn =null;    
        Statement st = null;   
        ResultSet rs = null; 
        try {        
            conn = JdbcUtils.getConnection(); 
            st = conn.createStatement();
            
            // SELECT * FROM users WHERE `Name` = 'kuangshen' AND `password` = '123456';
            // 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 {        
            JdbcUtils.release(conn,st,rs);   
        }
    }
}

5、PreparedStatement对象

PreparedStatement 可以防止SQL注入。效率更高

import java.sql.Connection; 
import java.util.Date;
import java.sql.PreparedStatement; 
import java.sql.SQLException;

public class TestInsert {  
    public static void main(String[] args) {  
        Connection conn = null; 
        PreparedStatement st = null;
        
        try {          
            conn = JdbcUtils.getConnection();         
            // 区别        
            // 使用？ 占位符代替参数       
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            
            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行
            
            // 手动给参数赋值     
            st.setInt(1,4); //id
            st.setString(2,"zhangsan"); 
            st.setString(3,"1232112"); 
            st.setString(4,"aaa@qq.com");  
            // 注意点： sql.Date   数据库   java.sql.Date()  
            // util.Date  Java   new Date().getTime() 获得时间戳 
            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 {       
            JdbcUtils.release(conn,st,null);  
        }  
    } 
}

import java.sql.Connection; 
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestDelete { 
    public static void main(String[] args) {  
        Connection conn = null;   
        PreparedStatement st = null;
        
        try {         
            conn = JdbcUtils.getConnection();    
            // 区别        
            // 使用？ 占位符代替参数      
            String sql = "delete from users where id=?";
            
            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行
            
            // 手动给参数赋值         
            st.setInt(1,4);
            
            //执行         
            int i = st.executeUpdate();    
            if (i>0){      
                System.out.println("删除成功！");  
            }
        } catch (SQLException e) {
             e.printStackTrace();   
        } finally {      
            JdbcUtils.release(conn,st,null);  
        }  
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestUpdate { 
    public static void main(String[] args) {  
        Connection conn = null;
        PreparedStatement st = null;
        
        try {       
            conn = JdbcUtils.getConnection();  
            // 区别       
            // 使用？ 占位符代替参数      
            String sql = "update users set `NAME`=?  where id=?;";
            
            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行
            
            // 手动给参数赋值  
            st.setString(1,"zhangsan");    
            st.setInt(2,1);
            
            //执行     
            int i = st.executeUpdate();    
            if (i>0){       
                System.out.println("更新成功！");   
            }
        } catch (SQLException e) {   
            e.printStackTrace();   
        } finally {     
            JdbcUtils.release(conn,st,null); 
        }
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet; 
import java.sql.SQLException;

public class TestSelect { 
    public static void main(String[] args) {
        
        Connection conn = null;    
        PreparedStatement st = null;  
        ResultSet rs = null;
        
        try {         
            conn = JdbcUtils.getConnection();
            
            String sql = "select * from users where id = ?"; // 编写SQL
            
            st = conn.prepareStatement(sql); // 预编译
            st.setInt(1,2); //传递参数
            rs = st.executeQuery(); //执行
            
            if (rs.next()){   
                
                System.out.println(rs.getString("NAME"));                    }
       
        } catch (SQLException e) {   
            e.printStackTrace();
        } finally {      
            JdbcUtils.release(conn,st,rs); 
        }  
    }
}

防止SQL注入

import java.sql.*;

public class SQLInjection {    
    public static void main(String[] args) {
        
        // login("lisi","123456");   
        login("'' or 1=1","123456"); 
    }
    
    // 登录业务
 public static void login(String username,String password){
     
     Connection conn =null;    
     PreparedStatement st = null;  
     ResultSet rs = null;  
     try {         
         conn = JdbcUtils.getConnection();   
         // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符          
         // 假设其中存在转义字符，比如说 ' 会被直接转义 
         String sql = "select * from users where `NAME`=? and `PASSWORD`=?"; // Mybatis
         
         st = conn.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 {     
         JdbcUtils.release(conn,st,rs);   
     }
  }
}

6、使用IDEA连接数据库

点击右侧边栏的database
点击MySQL
连接成功后可以选择数据库
可以印行可视化操作或使用SQL命令操作

7、事务

要么都成功、要么都失败

事务原则

原子性：要么全部完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题

脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致

代码实现

开启事务 conn.setAutoCommit(false);
一组业务执行完毕，提交事务
可以在catch 语句中显示的定义回滚语句，默认失败就会回滚

import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction2 { 
    public static void main(String[] args) {  
        Connection conn = null;  
        PreparedStatement st = null;   
        ResultSet rs = null;      
        
        try {      
            conn = JdbcUtils.getConnection();  
            // 关闭数据库的自动提交，自动会开启事务           
            conn.setAutoCommit(false); // 开启事务
            
            String sql1 = "update account set money = money-100 where name = 'A'";       
            st = conn.prepareStatement(sql1);  
            st.executeUpdate();
            
            int x = 1/0; // 报错
            
            String sql2 = "update account set money = money+100 where name = 'B'";        
            st = conn.prepareStatement(sql2);  
            st.executeUpdate();
            
            //业务完毕，提交事务    
            conn.commit();      
            System.out.println("成功！");
            
        } catch (SQLException e) {          
   
            // 若果失败，则默认回滚 
            try { 
                conn.rollback();  // 如果失败则回滚事务
            } catch (SQLException e1) {
                e1.printStackTrace(); 
            }        
            e.printStackTrace();  
        } finally {      
            JdbcUtils.release(conn,st,rs); 
        }
    } 
}

8、数据库连接池

数据库连接——>数据库连接——>执行完毕——>释放

连接——>释放十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

最小连接数： 10

最大连接数： 15

等待超时：100ms

编写连接池：实现接口 DataSource

开源数据源实现

DBCP
C3P0
Druid：阿里巴巴

使用了这些数据库连接池后，在开发中就不需要编写连接数据库的代码了

DBCP

需要的Jar包

commons-dbcp-1.4 、 commons-pool-1.6

C3P0

需要的Jar 包
c3p0-0.9.5.5、mchange-commons-java-0.2.19

不管什么数据源，本质都是实现DataSource接口，方法一样