Java之JDBC

JDBC的全称为：Java DataBase Connectivity(Java数据库连接)。
是一种执行SQL语句的Java API，可以为多种关系型数据库提供统一的访问。

1.JDBC的API

（1）JDBC入门

package com.imooc.jdbc.demo1;

import com.mysql.cj.jdbc.Driver;
import java.sql.*;

public class JDBCDemo1 {
    public void demo1() throws SQLException {  //这里需要接受异常
        //1.加载驱动
        DriverManager.registerDriver(new Driver());
        //2.获取连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://18.0.51.10:3306/jdbctest","root","123456");
        //3.创建执行SQL语句的对象
        String sql = "select * from user";
        Statement stmt = conn.createStatement();
        //4.执行sql语句
        ResultSet resultSet = stmt.executeQuery(sql);
        while (resultSet.next()){
            int uid = resultSet.getInt("uid");
            String name = resultSet.getString("name");
            String mobile = resultSet.getString("mobile");
            String addr = resultSet.getString("addr");
            System.out.println(uid+"  "+name+"  "+mobile+"  "+addr);
        }
        //5.释放资源
        resultSet.close();
        stmt.close();
        conn.close();
    }
}

如果数据库中文变成？？？，需要注意编码方式：
"jdbc:mysql://18.0.251.10:3306/jdbctest?useUnicode=yes&characterEncoding=utf8"

（2）JDBC的API——DriverManager的使用

DriverManager属于驱动管理类。
主要有两个作用：
a.注册驱动
DriverManager.registerDriver(new Driver());
但是这种方式会导致驱动注册两次。
因为在这个驱动中有一个静态代码块会注册驱动。所以更好的做法是加载静态代码块。
实际中会使用如下方式注册：
Class.forName("com.mysql.cj.jdbc.Driver");

b.获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://148.70.251.10:3306/jdbctest","root","123456");
jdbc：协议
mysql：子协议

（3）JDBC的API——Connection的使用

Connection主要用来连接对象。
主要作用：
a.创建执行SQL语句的对象
　　createStatement()：创建一个Statement对象来将SQL语句发送到数据库。（执行SQL语句）
　　prepareStatement(String sql)： 创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库。（预编译SQL语句）
　　prepareCall(String sql)：创建一个CallableStatement对象来调用数据库存储过程。（执行SQL中的存储过程）

b.进行事务的管理
　　setAutoCommit()：将此连接的自动提交模式设置为给定状态。
　　commit()：提交事务。
　　rollback()：回滚

（4）JDBC的API——Statement的使用

Statement主要用来执行SQL语句。
a.执行SQL语句
　　execute(String sql)：执行SQL，执行select返回true，否则返回false
　　executeQuery(String sql)：执行SQL中的select语句，返回一个结果集
　　executeUpdate(String sql)：执行SQL中的insert/update/delete语句，返回一个int类型的值
b.执行批处理操作
　　addBatch(String sql)：添加到批处理
　　executeBatch()：执行批处理
　　clearBatch()：清空批处理

（5）JDBC的API——ResultSet的使用

ResultSet，结果集，就是查询语句接口的封装。
next():将光标从当前位置向前移动，也就是下一个值。
针对不同的类型的数据可以使用getXXX()获取数据。通用获取数据的方法getObject()。

（6）JDBC的资源释放

jdbc程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象。
这些对象通常是ResultSet，Statement和Connection对象。

每个数据库都有最大连接数，如果没有释放，可能导致无法建立新的数据库连接。
特别是Connection对象，它是非常稀有的资源，用完必须马上释放，
如果COnnection不能及时、正确的关闭，极易导致系统宕机。
Connection的使用原则是尽量晚创建，尽量早的释放。

常规释放方式：

　　resultSet.close();
　　stmt.close();
　　conn.close();

但是这种释放方式并不彻底，如果前面代码异常，那么可能无法得到释放。可以使用try-finally关键字：

package com.imooc.jdbc.demo1;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;

public class JDBCDemo1 {
    public void demo1() {  //这里需要接受异常
        Connection conn = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
//          DriverManager.registerDriver(new Driver());
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://148.70.251.10:3306/jdbctest", "root", "123456");
            //3.创建执行SQL语句的对象
            String sql = "select * from user";
            stmt = conn.createStatement();
            //4.执行sql语句
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                int uid = resultSet.getInt("uid");
                String name = resultSet.getString("name");
                String mobile = resultSet.getString("mobile");
                String addr = resultSet.getString("addr");
                System.out.println(uid + "  " + name + "  " + mobile + "  " + addr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {  //不论结果如何都会释放连接
            //5.释放资源
            if (resultSet != null){  //需要对情况进行判断，如果可能没有创建连接，当然也无法释放连接
                try {
                    resultSet.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            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;  //垃圾回收机制更早回收对象，提前回收
            }
        }
    }
}

2.JDBC的CRUD操作

所谓的CRUD就是数据库的增删改查。

（1）增删改

package com.imooc.jdbc.demo1;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCDemo2 {
    @Test
    public void demo2(){
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://148.70.251.10:3306/jdbctest?useUnicode=yes&characterEncoding=utf8", "root", "123456");
            //增
            //String sql = "insert into user value(null,'hao','18972068500','湖北安陆')";
            //改
            //String sql = "update user set mobile='15302723629' where name='ming'";
            //删
            String sql = "delete from user where name='xing'";
            stmt = conn.createStatement();
            int i = stmt.executeUpdate(sql);
            if (i > 0){
                System.out.println("删除成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            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;
            }
        }
    }
}

（2）查

a.查询多条记录

package com.imooc.jdbc.demo1;

import org.junit.Test;

import java.sql.*;

public class JDBCDemo2 {
    @Test
    public void demo2(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://148.70.251.10:3306/jdbctest?useUnicode=yes&characterEncoding=utf8", "root", "123456");
            String sql = "select * from user";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                int uid = rs.getInt("uid");
                String name = rs.getString("name");
                String mobile = rs.getString("mobile");
                String addr = rs.getString("addr");
                System.out.println(uid+"  "+name+"  "+mobile+"  "+addr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            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;
            }
        }
    }
}

b.查询单条记录

if (rs.next()){
    int uid = rs.getInt("uid");
    String name = rs.getString("name");
    String mobile = rs.getString("mobile");
    String addr = rs.getString("addr");
    System.out.println(uid+"  "+name+"  "+mobile+"  "+addr);
}

3.JDBC工具类的抽取

为了简化JDBC的开发，可以将一些重复的代码进行提取。

（1）抽象工具类

package com.imooc.jdbc.utils;

import java.sql.*;

public class JDBCUtils {
    private static final String driverClass;
    private static final String ip;
    private static final String port;
    private static final String database;
    private static final String user;
    private static final String password;
    private static final String character;
    private static final String url;

    static {  //静态加载
        driverClass = "com.mysql.cj.jdbc.Driver";
        ip = "148.70.251.10";
        port = "3306";
        database = "jdbctest";
        user = "root";
        password = "123456";
        character = "useUnicode=yes&characterEncoding=utf8";
        url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?" + character;
    }
　　//加载驱动器
    public static void loadDriver() throws ClassNotFoundException {
        Class.forName(driverClass);
    }
　　//获取连接
    public static Connection getConnection() throws Exception{
        loadDriver();
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
　　//释放连接
    public static void release(Statement stmt,Connection conn){
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
    public static void release(ResultSet rs,Statement stmt,Connection conn){
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        release(stmt,conn);
    }
}

（2）简单使用

package com.imooc.jdbc.test;

import com.imooc.jdbc.utils.JDBCUtils;
import org.omg.Messaging.SYNC_WITH_TRANSPORT;

import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;

public class test {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnection();  //获取连接
            stmt = conn.createStatement();
            String sql = "select * from user";
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                int uid = rs.getInt("uid");
                String name = rs.getString("name");
                String mobile = rs.getString("mobile");
                String addr = rs.getString("addr");
                System.out.println(uid+"  "+name+"  "+mobile+"  "+addr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs,stmt,conn);  //一次释放
        }
    }
}

（3）配置文件优化

如果我们想在一个配置文件中配置参数，可以将其剥离出来。
创建jdbc.properties，然后使用类加载器加载配置：

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");
    ip = props.getProperty("ip");
    port = props.getProperty("prot");
    database = props.getProperty("database");
    user = props.getProperty("user");
    password = props.getProperty("password");
    character = props.getProperty("character");
    url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?" + character;
}

4.SQL注入

SQL注入的实质是修改了SQL语句。输入了SQL语句的关键字，从而修改了逻辑。
select * from user where username = “xxx” and password = “xxx”;

如果我们在输入username的时候进行注入，比如：
username = “xxx ‘or ‘1=1”

结果就变成这样了：
select * from user where username = “xxx ‘or ‘1=1” and password = “xxx”;

无形之间，就修改SQL语句的逻辑。

js校验能够拦截一些初级注入，但是可以修改url来突破。
上述问题还是在于，SQL语句采用的字符串拼接方式。
在python中采用字符串拼接或者%替换都无法解决SQL注入的问题，一般解决方法有二：
　　a.对传入的参数进行编码转义
　　b.使用python的MySQLdb模块的execute方法。
　　　　cursor.execute(query, params)
　　　　第一个是参数化的sql语句，第二个是对应的实际的参数值。
　　　　函数内部escape_string方法会对传入的参数值进行相应的处理（主要是转化为字符串）防止sql注入。

在Java中解决SQL注入漏洞可以使用PreparedStatement。
PreparedStatement是Statement的子接口，
它的实例对象可以通过调用Connection.preparedStatement(sql)方法获得，
相对于Statement对象而言：
　　PreperedStatement可以避免SQL注入的问题。
　　Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。
　　PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。
　　并且PreparedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化SQL语句的编写。
String sql = "select * from user";
stmt = conn.createStatement(); //将其类型改为PreparedStatement即可
//执行sql语句
resultSet = stmt.executeQuery(sql);

采用预编译的方式，会将SQL语句的结构固定，你再传入关键字也不会改变SQL语句的结构。
String sql = “select * from user where username = ? and password = ?”
pstmt = conn.prepareStatement(sql)
pstmt.setString(1,username)
pstmt.setString(2,password)
pstmt.executeQuery()