數(shù)據(jù)庫和JDBC編程

JDBC是一種數(shù)據(jù)庫連接，它是一種可執(zhí)行SQL語句的Java API。程序可以通過JDBC API連接到關系型數(shù)據(jù)庫，并使用結構化查詢語言來完成對數(shù)據(jù)庫的查詢，更新。通過使用JDBC，就可以使用同一種API訪問不同的數(shù)據(jù)庫系統(tǒng)，開發(fā)人員面向JDBC API編寫應用程序，然后根據(jù)不同的數(shù)據(jù)庫，安裝不同數(shù)據(jù)庫的驅(qū)動程序即可。數(shù)據(jù)庫驅(qū)動程序是JDBC程序和數(shù)據(jù)庫之間的轉(zhuǎn)換層，數(shù)據(jù)庫驅(qū)動程序負責將JDBC調(diào)用映射成特定的數(shù)據(jù)庫調(diào)用。當需要連接某個特定的數(shù)據(jù)庫時，必須有相應的數(shù)據(jù)庫驅(qū)動程序。JDBC驅(qū)動的常見類型：直接與數(shù)據(jù)庫實例交互。這種驅(qū)動是智能的，它知道數(shù)據(jù)庫使用的底層協(xié)議。這種驅(qū)動避開了本地代碼，減少了應用開發(fā)的復雜性，也減少了產(chǎn)生沖突和出錯的可能性。

程序可以使用JDBC API以統(tǒng)一的方式來連接不同的數(shù)據(jù)庫，然后通過Statement對象來執(zhí)行標準的SQL語句，并可以獲得SQL語句訪問數(shù)據(jù)庫的結果。通過使用JDBC，java程序可以非常方便的操作各種主流數(shù)據(jù)庫，由于java語言的跨平臺性，可以使得JDBC編寫的程序不僅可以實現(xiàn)跨數(shù)據(jù)庫，還可以垮平臺，具有很好的移植性。

SQL是一種結構化查詢語言，是操作和檢索關系數(shù)據(jù)庫的標準語言。DML指數(shù)據(jù)庫操作語言，主要由insert 、update 和delete三個關鍵字完成。DDL是指數(shù)據(jù)定義語言，操作數(shù)據(jù)庫對象的語句，主要由create、alter、drop和Truncate四個關鍵字完成，最基本的數(shù)據(jù)庫對象是數(shù)據(jù)表，數(shù)據(jù)表表示存儲數(shù)據(jù)的邏輯單元。DCL指數(shù)據(jù)控制語言，主要由grant和revoke兩個關鍵字完成，DCL語句為數(shù)據(jù)庫用戶授權，或收回指定用戶權限。

Mysql數(shù)據(jù)庫一般的約束類型：not null；UNIQUE約束（當建立唯一約束時，Mysql在唯一約束所在列或列組合上建立對應的唯一索引）。Primary key:主鍵約束相當于非空約束和唯一約束的結合，每一個表中最多允許有一個主鍵，但這個主鍵約束可由多個數(shù)據(jù)列組合而成。

FOREIGN KEY約束：主要用于保證一個或者兩個數(shù)據(jù)表之間的參照完整性，外鍵是構建于一個表的兩個字段或者兩個表的兩個字段之間的參照關系。當主表的記錄被從表記錄參照時，主表記錄不允許被刪除。必須先把從表里所有參照該記錄的所有記錄全部刪除后，才可以刪除主表的記錄。建立外鍵約束時，Mysql也會為該列建立索引。外建約束通常用于定義兩個實體之間的一對一和一對多的關系。如果要使MySql中的外鍵約束生效，則應該使用表級約束語法。如果需要顯示地指定外鍵約束的名字，則可以使用constraint來指定名字。

如果想定義刪除主表記錄時，從表記錄也會隨之刪除，則需要建立外鍵約束后添加 on delete cascade 或添加on delete set null，第一種是指刪除主表記錄時，參照主表記錄的從表記錄全部級聯(lián)刪除。第二種是指定當刪除主表記錄時，把參照該主表記錄的從表記錄設為null

值得指出的是，外鍵約束不僅可以參照其他表，而且可以參照自身，這種參照自身的情況通常被稱為自關聯(lián)。

索引：索引是存放在模式（schema）中的一個數(shù)據(jù)庫對象，雖然索引總是從屬于數(shù)據(jù)表，但它也和數(shù)據(jù)表一樣屬于數(shù)據(jù)庫對象。創(chuàng)建索引的唯一作用就是加快對表的查詢，索引通過使用快速路徑訪問方法來快速定位數(shù)據(jù)，從而減少磁盤的I/O;

創(chuàng)建索引的方法：

（1） 自動：當在表上定義主鍵約束，唯一約束和外檢約束時，自動創(chuàng)建索引；

（2  Create index…語句來創(chuàng)建索引。Create index index_name on

Table_name(column);

（3)Drop index來刪除索引；

     數(shù)據(jù)庫維護索引需要一定的系統(tǒng)開銷,因此創(chuàng)建索引的時候要特別注意，一般對需要經(jīng)常查詢的表字段創(chuàng)建索引。

注意：delete from語句可以一次刪除多行，采用where子句來限定。

Select 查詢時，后面不僅可以是數(shù)據(jù)列，也可以是表達式，還可以是變量，常量等。對于Mysql而言，如果算術表達式中出現(xiàn)null，將會導致整個算術表達式的返回值為null；因此可以使用如下方法，將為null的記錄設為0，來進行算術運算：

Select avg(ifnull(java_teacher,0)) fromstudent_table;

注意：

   如果需要對分組進行過濾，則應該使用having子句。Having子句和where有些區(qū)別:

   不能在where子句中過濾組，where子句僅用于過濾行。過濾組必須用having子句；

   不能在where子句中使用函數(shù)，having子句才可以使用函數(shù)。

如下語句：

       Select* from student_table group by java_teacher having count(*)>2;

外連接： 所謂外連接就是在連接條件的列名后增加括號包起來的外連接符，當外連接符出現(xiàn)在左邊時稱為左外連接，出現(xiàn)在右邊時稱為右外連接。外連接就是在外連接符所在的表中增加一個“萬能行”，這行記錄的所有數(shù)據(jù)都是null，而且該行可以與另一個表中不滿足條件的記錄進行匹配，通過這種方式就可以把另一個表中的所有記錄選出來，不管這些記錄是否滿足連接條件。

所謂自連接就是把一個表當成兩個表來用，這就需要為一個表起兩個別名，而且查詢中用的所有數(shù)據(jù)列都要加表別名前綴，因為兩個表的數(shù)據(jù)列完全一樣。

左，右，全外連接：這三種分別使用left join，right join和full join，這三種外連接的連接條件一樣通過on 子句來指定，既可以是等值連接條件，也可以是非等值連接條件。Sql99左外連接將會把左邊表中所有不滿足連接條件的記錄全部列出，右外連接會把右邊不滿足連接條件的記錄全部列出。全外連接會把兩個表中所有不滿足連接條件的記錄全部列出。

   JDBC的典型用法

   DriverManager：用于管理JDBC的驅(qū)動服務類，通過使用該類來獲取Connection 對象。Connection ：代表數(shù)據(jù)庫連接對象，每個Connection代表一個物理連結會話。Statement：用于執(zhí)行SQL語句的工具接口。該對象可以執(zhí)行DDL和DML語句。PreparedStatement ：預編譯的Statement對象。該方法返回預編譯的Statement對象，即將SQL語句提交到數(shù)據(jù)庫進行預編譯。它允許數(shù)據(jù)庫預編譯SQL語句，以后每次改變SQL命定的參數(shù)，避免數(shù)據(jù)庫每次都要編譯SQL語句，因此性能更好。相對statement而言，它執(zhí)行SQL語句時，無需再傳入sql語句，只要為預編譯的SQL語句傳入?yún)?shù)即可。ResultSet：結果集對象。

JDBC編程的一般步驟：

（1）   加載數(shù)據(jù)庫驅(qū)動

Class.forName(driverClass)

Class.forName(“com.mysql.jdbc.Driver”);//加載mysql的驅(qū)動代碼：

Class.forName(“oracle.jdbc.driver.oracleDriver”);//加載oracle的驅(qū)動代碼

(2) 通過DriverManager獲取數(shù)據(jù)庫連接。

DriverManager.getConnection(String url,String user,String pass);//MySql的URL寫法如下：jdbc:mysql://hostname:port/databasename;

使用PreparedStatement相對于Statement來說，除了能提高執(zhí)行效率外，還有一個優(yōu)勢，當SQL語句中要使用參數(shù)時，無需拼接sql字符串，使用PreparedStatement則只需要使用問號占位符來替代這些參數(shù)即可，降低了編程的復雜度，另外還有一個好處---用于防止SQL注入。

比如如下validate（）方法使用PreparedStatement來執(zhí)行驗證：

Private Boolean validate(String username,String userpass){
try{

Connection conn=DriverManager.getConnection(url,user,pass);

PreparedStatement pstmt=conn.prepareStatement(

“select * from jdbc_test where jdbc_name=?and jdbc_desc=?”))

{

  psmt.setString(1,username);

  psmt.setString(2,password);

   try(

       ResultSet rs=pstmt.executeUpdate())

{

     if(rs.next()){

        return true;

      }

}

   Catch(Exceptione){

     e.printStackTrace();

}

   Returnfalse;

}

Blob:是二進制長對象的意思，通常用于存儲大文件。典型的Blob內(nèi)容是一張圖片或者聲音文件。

可以使用CachedRowSet實現(xiàn)分頁。

Mysql中的事物處理：

事物是由一步或幾步數(shù)據(jù)庫操作序列所組成的邏輯執(zhí)行單元，這些系列操作要么全部執(zhí)行，要么全部放棄，一般而言，一段程序中可能包含多個事務。對于任何數(shù)據(jù)庫而言，事物都是非常重要的，事務是保證底層數(shù)據(jù)完整的主要手段，沒有事物支持的數(shù)據(jù)庫應用，那將非常脆弱。

事物具備四個特性：

原子性：事物是程序中最小的執(zhí)行單元，不可再分。

一致性：事物執(zhí)行的結果，必須使數(shù)據(jù)庫從一個一致性狀態(tài)，變到另一個一致性狀態(tài)。

隔離性：各個事物的執(zhí)行互不干擾，任意一個事物的內(nèi)部操作對其它并發(fā)的事務都是隔離的。

持續(xù)性：指事務一旦提交，對數(shù)據(jù)所做的任何改變都要記錄到永久存儲器中。通常就是保存進物理數(shù)據(jù)庫中；

這幾個特性簡稱為ACID性。

Mysql默認關閉事務（即打開自動提交），為了開啟Mysql事務，可以顯示調(diào)用如下命定：set AUTOCOMMIT={0|1} ,0為關閉自動提交。自動提交和開啟事務正好相反，如果開啟自動提交就是關閉事務，關閉自動提交就是開啟事務。

提交，不管是顯示提交還是隱示提交，都會結束當前事務；回滾，不管是顯示回滾還是隱式回滾，都會結束當前事務。

可以調(diào)用Connection提供的setAutoCommit（）方法來關閉自動提交，開啟事務。如：conn.setAutoCommit(false);

如果所有的sql語句都執(zhí)行成功，程序可以調(diào)用conn.commit()語法來提交事務。如果任意一條sql語句執(zhí)行失敗，則通過 conn.rollback()方法來回滾事務。

大部分時候，只需要對指定的數(shù)據(jù)表進行插入（C），查詢（R），修改（U），刪除（D）等CRUD操作。在sql里的模式字符串，用百分號（%）表示任意多個字符，使用下劃線（_）代表一個字符。

使用連接池管理連接：

數(shù)據(jù)庫的連接的建立和關閉是很消耗系統(tǒng)資源的操作，頻繁的打開關閉連接將導致系統(tǒng)性能低下。數(shù)據(jù)庫連接池的解決方案是：當應用程序啟動時，系統(tǒng)自動建立足夠的數(shù)據(jù)庫連接，并將這些連接組成一個連接池，每次應用程序請求數(shù)據(jù)庫連接時，無須重新打開連接，而是從連接池中取出已有的連接使用，使用完后不再關閉數(shù)據(jù)庫連接，而是直接將連接歸還給連接池，這樣可以極大的提高程序的運行效率。

在多個連接池程序里，相比之下，C3P0的性能更好，它不僅可以自動清理不再使用的Connection，還可以自動清理Statement和ResultSet對象。

（1）C3P0連接池的操作實例如下：

  publicclass C3P0Test{

    public static void main(String[] args) {

        Connection conn=null;

        PreparedStatement pstm=null;

        ResultSet rs=null;

        ComboPooledDataSource cpds=new ComboPooledDataSource();

        try{

            conn=cpds.getConnection();

            pstm=conn.prepareStatement("select * from account wherename=?");

            pstm.setString(1, "a");

            rs=pstm.executeQuery();

            rs.next();

            System.out.println(rs.getDouble("money"));

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JDBCUtils.close(conn, pstm,rs);

        }

    }

}

（2）JDBC還提供了一個批量更新的功能，使用批量更新時，多條sql語句將被作為一批操作被同時收集，并同時提交。如下實例：

 public class StatementBatchTest {

    publicstaticvoid main(String[] args) {

        Connection conn=null;

        Statement stat=null;

        ResultSet rs=null;

        try{

            conn=JDBCUtils.getConnection();

            stat=conn.createStatement();

            stat.addBatch("create databasedb_batch");

            stat.addBatch("use db_batch");

            stat.addBatch("create table tb_batch"+"(id int primary key  auto_increment, name      varchar(20))");

            stat.addBatch("insert into tb_batchvalues(null,'a')");

            stat.addBatch("insert into tb_batchvalues(null,'b')");

            stat.addBatch("insert into tb_batchvalues(null,'c')");

            stat.addBatch("insert into tb_batchvalues(null,'d')");

            stat.executeBatch();

            System.out.println("執(zhí)行成功");

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JDBCUtils.close(conn, stat,rs);

        }

    }

}

（3）在preparedStatement中進行批量更新的例子如下：

   publicclass PreparedStatementBatch {

     public static void main(String[] args) {

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        try {

//          conn =JDBCUtils.getConnection();

            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql:///db_batch","root", "root");

            ps = conn.prepareStatement("insert into tb_batch values(null, ?)");

            for(int i=0; i<10000; i++){

                ps.setString(1, "name"+i);

                ps.addBatch();

            }

            ps.executeBatch();

            System.out.println("執(zhí)行成功~!");

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            if(rs != null){

                try {

                    rs.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }finally{

                    rs = null;

                }

            }

            if(ps != null){

                try {

                    ps.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }finally{

                    ps = null;

                }

            }

            if(conn != null){

                try {

                    conn.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }finally{

                    conn = null;

                }

            }

        }

    }

}

（4）翻頁是JDBC中的常用技術，本代碼中利用執(zhí)行計劃處理分頁，數(shù)據(jù)庫的連接和關閉用已經(jīng)另外一個類DBUtil實現(xiàn)，代碼如下：

public class PageDemo {

   public static void main(String[] args) {

       PageDemo demo = new PageDemo();

       demo.printPage(5, 3);

    }

   public void printPage(int pageSize,int page){

       int begin = (page - 1)*pageSize + 1;

       int end = page*pageSize ;

       String query = "select * from dept";

       String sql = "select * from (select a.*, rownum rn from ("+query+")a where rownum<= ?) where rn>= ?";

       System.out.println(sql);

       Connection conn = null;

       try {

           conn = DBUtil.getConnection();

           PreparedStatement ps = conn.prepareStatement(sql);

           ps.setInt(1, end);

           ps.setInt(2, begin);

           ResultSet rs = ps.executeQuery();

           while(rs.next()){

               System.out.println(rs.getString("dname"));

           }

           rs.close();

           ps.close();

       }catch(Exception e){

           e.printStackTrace();

       }finally{

           DBUtil.close(conn);

       }

    }

}