JDBC是什么?

JDBC代表Java數(shù)據(jù)庫連接，這對Java編程語言和廣泛的數(shù)據(jù)庫之間獨立于數(shù)據(jù)庫的連接標準的Java API避诽。

JDBC 的實現(xiàn)包括三個部分
1.JDBC 驅(qū)動管理器：負責注冊特定的JDBC驅(qū)動器，主要通過java.sql.DriverManager類實現(xiàn)
2.JDBC驅(qū)動API：最主要的接口是java.sql.Driver接口
3.JDBC驅(qū)動器:他是一種數(shù)據(jù)驅(qū)動由數(shù)據(jù)庫廠商創(chuàng)建拇泣，也稱jdbc驅(qū)動程序
JDBC驅(qū)動器實現(xiàn)了JDBC驅(qū)動器API性置，負責與特定的數(shù)據(jù)庫連接以及處理通信細節(jié)

JDBC常用API

1.Driver接口
是所有JDBC驅(qū)動程序必須實現(xiàn)的接口該接口專門提供給數(shù)據(jù)庫廠商使用，在編寫JDBC程序時必須要
把指定數(shù)據(jù)庫驅(qū)動程序或類庫加載到項目的CLASSPATH中

2.DriverManager類
用于加載JDBC驅(qū)動并且創(chuàng)建與數(shù)據(jù)庫的鏈接蹦渣。定義了兩個重要的靜態(tài)方法
registerDriver（Driver driver）向DriverManager中注冊給定的JDBC驅(qū)動程序
getConnection（String url,String user,String pwd）建立與數(shù)據(jù)庫的鏈接，并返 回表示
連接的Connection對象 

3.Connection接口
代表java程序和數(shù)據(jù)庫的鏈接
getMetaData（）返回表示數(shù)據(jù)庫的元數(shù)據(jù)的DataBaseMetaData對象
createstatement()創(chuàng)建一個statement對象來將SQL語句發(fā)送到數(shù)據(jù)庫
prepareStatement（String sql）創(chuàng)建一個prepareStatement對象來講參數(shù)化的 
                              sql語句發(fā)送到數(shù)據(jù)庫 貌亭。
prepareCall（String sql）創(chuàng)建一個callableStatement對象來調(diào)節(jié)數(shù)據(jù)庫存儲過程

4.statement接口
用于向數(shù)據(jù)庫發(fā)送SQL語句
execute（String sql）返回一個Boolean類型的值
executeUpdate（String sql）執(zhí)行SQL中的insert柬唯，update和delete語句，返回一個int類型的值圃庭，表示數(shù)據(jù)庫受該SQL語句影響的記錄數(shù)目锄奢；
executeQuery（String sql）執(zhí)行SQL中select語句返回一個表示查詢結(jié)果的ResultSet對象

5.preparedStatement接口
是statement的子接口用于執(zhí)行預編譯的SQL語句

executeUpdate（）執(zhí)行SQL中的insert失晴，update和delete語句，返回一個int類型的值拘央，表示數(shù)據(jù)庫受該SQL語句影響的記錄數(shù)目涂屁，SQL操縱DML或者無返回內(nèi)容，如DDL堪滨；
executeQuery（）執(zhí)行SQL中select語句返回一個表示查詢結(jié)果的ResultSet對象

6.callablestatement接口
是preparedStatement的子接口用于執(zhí)行SQL的存儲過程
7.result的接口
表示select查詢語句得到的結(jié)果集胯陋，接口內(nèi)部有一個指向表格數(shù)據(jù)行的游標

實現(xiàn)第一個JDBC程序

1.加載并注冊數(shù)據(jù)庫驅(qū)動
    DriverManager.registerDriver(new com.mysql.jdbc.Driver());
2.通過DriverManager獲取數(shù)據(jù)庫連接     
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";
    Connection conn = DriverManager.getConnection(url, user, password);
3.通過Connection對象獲取Statement對象
        Statement stmt = conn.createStatement();
        String sql = "select * from person";
4.使用Statement執(zhí)行SQL語句
        ResultSet rs = stmt.executeQuery(sql);
5.操作ResultSet結(jié)果集
        while(rs.next()) {
            System.out.println(rs.getInt(1)+rs.getString(2)+rs.getString(3)+rs.getString(4)+rs.getString(5)+rs.getFloat(6)+rs.getDate(7)+rs.getString(8));          
}
6.回收數(shù)據(jù)庫資源蕊温，關(guān)閉數(shù)據(jù)庫連接袱箱，釋放資源
        rs.close();
        stmt.close();
        conn.close();

JDBC 的改進

1.注冊驅(qū)動
在注冊數(shù)據(jù)庫驅(qū)動時雖然DriverManager.registerDriver（new com.jdbc.mysql.Driver）方
法可以完成但是驅(qū)動會被執(zhí)行兩次，因為Driver類源碼中已經(jīng)在靜態(tài)代碼塊中完成了數(shù)據(jù)庫驅(qū)動
的注冊义矛。所以只需在程序中加載驅(qū)動類即可    
Class.forName("com.mysql.jdbc.Driver")  
2.釋放資源
由于數(shù)據(jù)庫資源寶貴发笔，數(shù)據(jù)庫允許的并發(fā)訪問連接數(shù)量有限，所以一定要釋放資源凉翻，應該將最終必
須要執(zhí)行的操作放finally代碼塊中

PreparedStatement對象

statement對象每次執(zhí)行SQL語句時了讨，都會對其進行編譯，當相同的語句執(zhí)行多次制轰，statement就會
使數(shù)據(jù)庫頻繁的編譯相同的SQL語句前计，會降低數(shù)據(jù)庫的訪問效率。為解決上述問題Statement提供了
一個子類PreparedStatement垃杖。PreparedStatement男杈。可以對SQL語句進行預編譯调俘，也就是說當相
同的SQL語句伶棒，數(shù)據(jù)庫只需使用緩沖區(qū)的數(shù)據(jù)，而不需對SQL語句進行再次編譯

Connection conn = null;
    PreparedStatement pstt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "";
        conn = DriverManager.getConnection(url, user, password);
        String sql = "insert into person values(?,?,?)";
        pstt = conn.prepareStatement(sql);
        pstt.setString(1, "呵呵");
        pstt.setInt(2, 10);
        pstt.setString(3,"哈哈");
        pstt.executeUpdate();
    } catch (SQLException e) {
        
        e.printStackTrace();
    }finally {//釋放資源
        if(pstt!=null){
            pstt.close();
        }
        if(conn!=null) {
            conn.close();
        }       
}

resultset對象

resultSet主要存儲結(jié)果集彩库，并通過next()方法由前向后逐個獲取結(jié)果集中的數(shù)據(jù)肤无，如果想獲取任意位置的數(shù)據(jù)需在創(chuàng)建Statement之前，設置兩個Result定義的常量

Connection conn = null;
    Statement smtt = null;
    ResultSet rs = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";
        conn = DriverManager.getConnection(url, user, password);
        String sql = "select * from person";
        smtt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        rs = smtt.executeQuery(sql);
        
        System.out.println("第三條數(shù)據(jù)name的值為：");
        rs.absolute(3);                         //定位指針
        System.out.println(rs.getString("name"));
        System.out.println("第一條數(shù)據(jù)name的值為：");    
        rs.beforeFirst();                       //定位到第一行之前
        rs.next();                              //指針向后滾動
        System.out.println(rs.getString("name"));
        System.out.println("最后一條數(shù)據(jù)name的值為：");   
        rs.afterLast();                         //定位到最后一行之后
        rs.previous();                          //指針向前滾動
        System.out.println(rs.getString("name"));
    
    } catch (SQLException e) {
        
        e.printStackTrace();
    }finally {//釋放資源
        
        if(rs!=null){
            rs.close();
        }
        if(smtt!=null){
            smtt.close();
        }
        if(conn!=null) {
            conn.close();
        }   
}

JDBC批處理

1.statement 批處理
Statement通過addBatch()方法添加一條SQL語句骇钦，通過executeBatch實現(xiàn)批處理

        String sql = "";
        String sql2 ="insert into ....";
        String sql3 = "update person set id=1";
        smtt.addBatch(sql);
        smtt.addBatch(sql2);
        smtt.addBatch(sql3);
        smtt.executeBatch();

2.PreparedStatement批處理
String sql = "insert into person values(?,?,?)";
    pstt = conn.prepareStatement(sql);
      for(int i=0;i<5;i++){
        pstt.setString(1," ");
          pstt.setString(2," ");
            pstt.setString(3," ");
        pstt.addBatch();
}
        pstt.executeBatch();

JDBCUtils

public static Connection getConnection() throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "root";
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;

    }
    
    public static void release(Statement stmt,Connection conn){
        
            try {
                if(stmt!=null){stmt.close();}
                if(conn!=null){conn.close();}
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
    }
    public static void release(ResultSet rs,Statement stmt,Connection conn){
        
        try {
            if(rs!=null){rs.close();}
            if(stmt!=null){stmt.close();}
            if(conn!=null){conn.close();}
        } catch (SQLException e) {
            
            e.printStackTrace();
        }
}

//在properties文件中
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day15
user=root
password=sorry
-----------------------------------------------------------------------
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    
    static{//代碼塊隨著類而加載一次
        try {
            ClassLoader cl = JdbcUtil.class.getClassLoader();//類加載器
            InputStream in = cl.getResourceAsStream("dbcfg.properties");
            Properties props = new Properties();
            props.load(in);
            driverClass = props.getProperty("driverClass");
            url = props.getProperty("url");
            user = props.getProperty("user");
            password = props.getProperty("password");
            
            Class.forName(driverClass);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }
    
    
    public static Connection getConnection() throws Exception{
        Connection conn = DriverManager.getConnection(url,user, password);
        return conn;
    }
    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的連接及增刪改查的方法

    private Connection conn = null;
    private PreparedStatement pstt = null;
    private ResultSet rs = null;
    public static final String url = "jdbc:mysql://localhost:3306/test?useUnicode=ture&characterEncoding=utf-8";
    public static final String user = "root";
    public static final String password = "root";
    public static final String driverclass = "com.mysql.jdbc.Driver";

    public Connection getConnection() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {

            e.printStackTrace();
        }
        return conn;

    }

    public int executeUpdate(String sql, Object... para) {//可變數(shù)組
        getConnection();
        int a = 0;
        try {
            pstt = conn.prepareStatement(sql);
            for (int i = 0; i < para.length; i++) {
                pstt.setObject((i + 1), para[i]);
            }
            a = pstt.executeUpdate();
        } catch (SQLException e) {

            e.printStackTrace();
        }

        return a;

    }
    public ResultSet executeQuery(String sql,Object ...para){//可變數(shù)組
        getConnection();
        try {
            pstt = conn.prepareStatement(sql);
            for (int i = 0; i < para.length; i++) {
                pstt.setObject((i + 1), para[i]);
            }
            rs = pstt.executeQuery();
            
        } catch (SQLException e) {
            
            e.printStackTrace();
        }
        
        return rs;
    }

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class jdbcUtil {
    private static Connection conn = null;
    private static PreparedStatement pstt = null;
    private static ResultSet rs = null;
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    static {
        try {
            ClassLoader cl = jdbcUtil.class.getClassLoader();
            InputStream in = cl.getResourceAsStream("a.properties");
            Properties pro = new Properties();

    pro.load(in);
    driverClass = pro.getProperty("driverClass");
    url = pro.getProperty("url");
    user = pro.getProperty("user");
    password = pro.getProperty("password");
    Class.forName(driverClass);
    } catch (Exception e) {
        e.printStackTrace();
    }

    }
    

    public static int executeUpdate(String sql, Object... para) {
        int a = 0;
        try {
             conn = DriverManager.getConnection(url,user, password);
            pstt = conn.prepareStatement(sql);
            for (int i = 0; i < para.length; i++) {
                pstt.setObject((i + 1), para[i]);
            }
            a = pstt.executeUpdate();
        } catch (SQLException e) {

    e.printStackTrace();
    }
    return a;
    }
    public static ResultSet executeQuer(String sql, Object... para) {// 可變數(shù)組
        try {
             conn = DriverManager.getConnection(url,user, password);
            pstt = conn.prepareStatement(sql);
            for (int i = 0; i < para.length; i++) {
                pstt.setObject((i + 1), para[i]);
            }
            rs = pstt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public static void close() {

    try {
        if (rs != null) {
            rs.close();
        }
        if (pstt != null) {
            pstt.close();
        }
        if (conn != null) {
            conn.close();
        }
    } catch (SQLException e) {

    e.printStackTrace();
    }
    }
}

大數(shù)據(jù)處理

大數(shù)據(jù)處理主要指的是對CLOB和BLOB類型數(shù)據(jù)的操作
在應用操作中這兩個數(shù)據(jù)類型必須使用PreparedStatement完成宛渐，并且操作形式都要以IO流的形式進行存放和讀取
1.處理CLOB數(shù)據(jù)
寫入大文本

讀取