Java學習筆記--sql與java

1.JDBC
1.1JDBC定義：一套介面，用於連線資料庫和Java
1.2使用JDBC傳送sql語句的條件：
連線mysql資料庫：
資料庫主機
埠
資料庫使用者名稱
資料庫密碼
連線的資料庫
1.3JPDC核心API
a.Driver介面： 資料庫驅動程式的介面，所有具體資料庫廠商需要的驅動程式需要實現此介面。
Connection connect(String url, Properties info) 用於獲取資料庫連線
b.Connection介面：與具體的資料庫的連線物件。
Statement createStatement() 建立一個靜態sql語句物件
PreparedStatement prepareStatement(String sql) 建立預編譯的sql語句物件
CallableStatement prepareCall(String sql) 建立儲存過程的sql語句物件
c.Statement介面：用於執行靜態 SQL 語句
int executeUpdate(String sql) 執行更新操作的sql語句 （create/alter/drop) DDL語句
(insert/update/delete）DML語句
ResultSet executeQuery(String sql) 執行查詢操作的sql語句
(select)（DQL查詢語句）
d.PreparedStatement介面：用於執行預編譯的 SQL 語句（是Statement的子介面）
int executeUpdate() 執行更新操作的sql語句
ResultSet executeQuery() 執行查詢操作的sql語句
e. CallableStatement介面：用於執行 SQL 儲存過程的介面（是PreparedStatement的子 介面）
ResultSet executeQuery() 執行儲存過程的sql語句
f.ResultSet介面：結果集物件。 儲存所有資料庫查詢的結果，用該物件進行資料遍歷。
boolean next() ： 把游標移動到下一行。如果下一行有資料，返回true，如果沒有下一行數 據，返回false。
getXXX（列索引|列欄位名稱）： 獲取欄位的資料

package com.jdbc.a_driver;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DriverDemo {
    private static String url = "jdbc:mysql://localhost:3306/day20";
    //jdbc協議:mysql協議://主機地址:埠號/需要連線的資料庫名稱
    private static String user = "root";
    private static String password="root";

    public static void main(String[] args) throws Exception {
        /**
         * java程式連線mysql資料庫
         * 1.mysql資料庫的主機地址
         * 2.埠號
         * 3.使用者名稱
         * 5.密碼
         * 6.需要連線的資料庫
         * 
         * 需求：先使用java程式連線我們的資料庫，需要一個連線物件Connection
         */
        //conn1();
        //conn2();
        conn3();
    }

    //我們以後就使用這種方式獲取資料庫的連線物件
    private static void conn3() throws Exception {
        //註冊驅動，我們發現mysql驅動程式的Driver實現類已經幫我們在靜態程式碼塊中註冊好了驅動，
        //我們在此時只需要將Driver實現類載入到我們的記憶體中，static程式碼塊就會自動執行，我們的驅動也就自動註冊了
        //註冊驅動
        Class.forName("com.mysql.jdbc.Driver");

        //獲取java連線資料庫的物件
        Connection conn = DriverManager.getConnection(url, user, password);

        //列印這個連線物件
        System.out.println(conn);

    }


    private static void conn2() throws Exception {
        //使用驅動管理類，連管理我們的驅動程式，並獲取連線
        //1.註冊驅動
        //public static void registerDriver(Driver driver)throws SQLException
        Driver driver = new com.mysql.jdbc.Driver();
        DriverManager.registerDriver(driver);

        //獲取連線3
        //public static Connection getConnection(String url,String user,String password)throws SQLException
        Connection conn = DriverManager.getConnection(url, user, password);

        //列印這裡資料庫連線
        System.out.println(conn);
    }


    //直連
    private static void conn1() throws SQLException {
        //建立一個驅動類物件Driver
        Driver driver = new com.mysql.jdbc.Driver();
        //獲取java連線資料庫的連線
        //Connection connect(String url, Properties info)throws SQLException
        //建立一個properties集合
        Properties prop = new Properties();
        prop.setProperty("user", user);
        prop.setProperty("password", password);
        Connection conn = driver.connect(url, prop);
        //列印這個連線物件，如果連線物件不為空，就說明我們已經成功獲取到了連線物件
        System.out.println(conn);
    }

}

2.Statement物件執行SQL操作

public class Demo1 {
    //資料庫的連線的URL
    private static String url = "jdbc:mysql://localhost:3306/day17";
    //資料庫使用者名稱
    private static String user = "root";
    //資料庫密碼
    private static String password = "root";

    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        try {
            //1.驅動驅動程式
            Class.forName("com.mysql.jdbc.Driver");
            //2.從驅動程式管理類獲取連線
            conn = DriverManager.getConnection(url, user, password);
            //3.通過Connection物件獲取Statement物件
            stmt = conn.createStatement();
            //4.準備sql語句
            String sql = "CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),gender VARCHAR(2))";
            //5.執行sql語句，返回結果
            int count = stmt.executeUpdate(sql);

            System.out.println("影響了"+count+"行");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally{
            //6.關閉資源（先關閉statement，再關閉connection）
            if(stmt!=null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
        }
    }
}

執行其他語句的操作與上述執行DDL語句操作大同小異
3.PreparedStatement物件執行SQL操作
PreparedStatement是一個介面，表示預編譯的SQL語句，SQL 語句被預編譯並儲存在 PreparedStatement 物件中。然後可以使用此物件多次高效地執行該語句。

public class Demo1 {

    /**
     * 插入操作
     */
    @Test
    public void test1(){
        Connection conn = null;
        PreparedStatement stmt = null;
        try{
            //獲取連線
            conn = JdbcUtil.getConnection();
            String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)"; //預編譯sql：使用？號代替引數值。一個？號代表一個引數值
            //建立PreparedStatement物件,執行預編譯的sql語句
            stmt = conn.prepareStatement(sql);
            //設定引數
            /**
             * 引數一： 引數位置。從1開始
             * 引數二： 引數實際值
             * 注意： 所有引數必須要賦值
             */
            stmt.setString(1, "rose");
            stmt.setString(2, "女");
            //傳送引數，執行sql語句
            int count = stmt.executeUpdate();
            System.out.println(count);
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            //關閉資源
            JdbcUtil.close(conn, stmt, null);
        }
    }

Statement和PreparedStatement比較–>

一、語法結構不同
            1）Statment執行靜態sql語句，且sql可以拼接。
            2）PreparedStatement可以先執行預編譯的sql語句，在預編譯sql語句中使用？進行引數佔位，後面                    在進行引數賦值
    二、原理不同
            1）Statement不能進行sql快取
            2）而PreparedStatement可以進行sql快取，執行效率會比Statement快！！！

    三、安全性不同
            1）Statement存在sql注入的風險(使用登入註冊講解sql注入)
            2）而PreparedStatement可以有效防止使用者注入。

4.CallableStatement物件執行儲存過程
CallableStatement是用於執行SQL儲存過程的介面。JDBC API 提供了一個儲存過程 SQL 轉義語法，該語法允許對所有 RDBMS 使用標準方式呼叫儲存過程。此轉義語法有一個包含結果引數的形式和一個不包含結果引數的形式。如果使用結果引數，則必須將其註冊為 OUT 引數。其他引數可用於輸入、輸出或同時用於二者。引數是根據編號按順序引用的，第一個引數的編號是 1。

public void test1(){
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;
        try{
            //獲取連線
            conn = JdbcUtil.getConnection();
            //建立CallableStatement物件
            String sql = "CALL pro_findById(?)";//預編譯sql、可以帶？號
            //執行預編譯的sql
            stmt = conn.prepareCall(sql);
            //設定引數
            stmt.setInt(1, 4);
            //傳送引數，執行sql,返回結果
            rs = stmt.executeQuery();// 注意： 執行儲存過程必須使用exeuteQuery！！！！
            //遍歷結果
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(id+"\t"+name+"\t"+gender+"\t");
            }
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            //關閉資源
            JdbcUtil.close(conn, stmt, rs);
        }
    }

5.優化JDBC工具類

private static String url = null;
    private static String user = null;
    private static String password = null;
    private static String className = null;

    static{
        //註冊驅動，註冊一次就可以了
        //註冊驅動
        try {
            //給成員變數賦值，將檔案中的鍵值對載入到properties集合中
            Properties prop = new Properties();
            InputStream in = new FileInputStream("db.properties");
            prop.load(in);
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            className = prop.getProperty("className");
            System.out.println(url);
            System.out.println(user);
            System.out.println(password);
            System.out.println(className);
            Class.forName(className);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //獲取連線
    public static Connection getConnection(){
        try {
            //獲取連線
            Connection conn = DriverManager.getConnection(url, user, password);
            //返回conn
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    //釋放資源
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        //先判空後釋放
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException();
            }
        }

        if (stmt!=null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException();
            }
        }

        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException();
            }
        }

    }