JDBC API

JDBC API 是資料庫廠商依據JDBC規範的interface所開發出來的類別, 這些API重定了 Date, Time, SQLException等相關的類別, 所以又把這些廠商提供的API稱為驅動程式

jtds

jtds是MSSQL比較穩定的驅動程式, 下載網址如下

https://sourceforge.net/projects/jtds/files/jtds/1.3.0/jtds-1.3.0-dist.zip/download

請注意, 1.3.0版是測試最穩定的版本, 1.3.1測試起來, 有些問題

操作資料庫順序如下

    1. 專案右鍵/Properties/Libraries/Add JAR Folder/再選jdtds.jar
    2. 指定使用的driver :
       Class.forName(“net.sourceforge.jtds.jdbc.Driver");
    3. 設定url字串
       String url="jdbc:jtds:sqlserver://localhost;databaseName=test;user=thomas;password=1234″
    4. 建立連結(戴入driver)
       Connection conn=DriverManager.getConnection(url, name, password);
    5. 建立Statement物件
       connection.createStatement()
    6. 執行Statement取得資料
       statement.executeQuery(查詢命令)
    7. 取得的資料放入ResultSet之中

指定使用的driver

JDBC4.0之前需手動載入驅動程式

    try{
        java.lang.Class.forName(“全路徑");
    }
    catch(ClassNotfoundException c){}

如MSSQL : Class.forName(“net.sourceforge.jtds.jdbc.Driver”);

Vendor’s Driver Class

使用DriverManager類別獲得一個實体的連結物件, 用法如下
String url=”jdbc:<driver>:[subprotocal:][databaseName][;attribute=value]
Connection con=DriverManager.getConnection(url);

Orcale url 語法
jdbc:oracle:thin:@//myhost:1521/orcl

MSSQL url 語法jdbc:jtds:sqlserver://localhost;databaseName=test;user=thomas;password=1234

DriverManager.getConnection()方法的作用是在載入驅動程式

命令列可下達如下指令執行
java -djdbc.drivers=<驅動程式絕對路徑> <class檔案>

JDBC API關鍵元件

java.sql.Connection
Connection con=DriverManager.getConnection(url, username, password);

java.sql.Statement : 用來執行SQL statement的物件
Statement stmt=con.createStatement();
另有二種
PreparedStatement : 繼承Statement, conn.prepareStatement(strCmd)
CallableStatement : 繼承PreparedStatement

java.sql.ResultSet : 資料庫執行後的值
String query=”Select * from employee”;
ResultSet rs=stmt.executeQuery(query);

ps. SQL語法不分大小寫, 但資料庫及資料表就要看系統了

Exam

public class MSSQLTest {
    public static void main(String[] args) {
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            String url="jdbc:jtds:sqlserver://localhost;databaseName=person;user=sa;password=123456";
            Connection conn=DriverManager.getConnection(url);
            Statement stmt=conn.createStatement();
            ResultSet rs=stmt.executeQuery("select * from ContactName");
            while(rs.next()){
                System.out.printf("%d , %s\n",rs.getInt("_id"), rs.getString("name"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(MSSQLTest.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(MSSQLTest.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Statement

由連結物件conn.createStatement()建立Statement stmt, 再由stmt執行相關指令, 如
stmt.executeQuery(sqlString) : 執行Select功能, 返回ResultSet
stmt.executeUpdate(sqlString) : 返回int(引響的列數), 用在insert, update, delete or DDL
stmt.execute(sqlString) : 返回boolean(如果有Resultset, 則為true), 用在任何SQL command

ResultSet物件

ResultSet rs使用cursor來指向資料列, 初始的cursor是指向第一筆資料的前面(BOF). rs.next()會往下一筆移動. 預設的ResultSet是不會更新的, 且只能往下一筆走, 不能回上一筆.

可以使用下面的指令讓cursor可前後移動
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATEABLE);

ResultSet.Type_FORWOARD _ONLY: 只可以往下移動
ResultSet.Type_SCROLL_INSENSITIVE : 可以上下移動, 但切斷與來源的連線, 別人新增刪除修改無法立即檢驗
ResultSet.Type_SCROLL_SENSITIVE : 可以上下移動, 但與來源不切斷連線. 別人新增刪除修改, 會立即顯示在ResultSet上

ResultSet.CONCUR_READ_ONLY : ResultSet唯讀
ResultSet.CONCUR_UPDATABLE : ResultSet可供多人同時修改

可攜式JDBC

JDBC驅動提供了介於Java應用程式及資料庫之間的獨立層. 所以在撰寫資料庫應用程式時需考慮SQL的語法. 大部份的資料庫都支援標準的SQL 語法, 此標準語法被定義在SQL-92裏, 由美國國家標準局(ANSI)所制定. 使用DatabaseMetaData可查詢所使用的資料庫是否支援。

DatabaseMetaData dbm=conn.getMetaData();
if(dbm.supportsANSI92EntrySQL()){}

getString, getDouble

在資料庫中, 就算資料表欄位設計成double, 傳回Java後, 還是以string傳回. 然後再由getDouble()轉換成double. 若傳回的值無法轉換成double, 則傳回失敗.

那資料庫欄位為什麼要設計成int, float 或real呢, 直接設計成varchar不就好了嗎. 資料庫設計成數字欄位的目的, 是為了檢驗存入的資料是否正確. 所以設計資料庫時, 不可以偷懶全設為varchar.

SQL 語法

SQL四大語法, 查詢, 新增, 修改, 刪除, 如下

select * from table where 條件 group by 欄位 order by 欄位
insert into table (field1, field2….) values (value1, value2….)
update table set field1=value1, field2=value2 where 條件
delete table where 條件

執行SQL 預存程序

 String strProc="java_建立";//填入預存程序名稱
 PreparedStatement ps=conn.prepareStatement(strProc);
 //rs=ps.executeQuery(); //如果有傳回值的話
 ps.execute();//沒有傳回值時

CallableStatement

CallableStatement也是可以執行預行程序的, 如下

CallableStatement cs=conn.prepareCall("{call java_建立(?)}");
cs.setString(1, "IN值");
cs.registerOutParameter(1, Types.VARCHAR);
cs.execute();
String outParam = cs.getString(1);

SQLException

SQLException會因如下的錯誤被丟出 : 驅動程式的方法, 存取資料庫的方法, 連結資料庫(connection)時. SQLException實作了Iterable, 所以可以被串連在一起, 並一個一個的丟出. 當連線時的帳號, 密碼錯誤, 或資料庫的離線, 都會丟出SQLException. 同樣的, 若要存取資料表中未存在的欄位, 也會丟出例外

SQLException常用的方法有 getSQLState(), getErrorCode(), getMessage(), getCause(), getNextException();

JDBC物件關閉

關閉連線時, 會自動關閉Statement物件. 關閉Statement並不會自動關閉ResultSet物件, 只是讓ResultSet無效而以, 需要到垃圾回收機制啟動後才會釋放ResultSet裏面的資源. 所以手動關閉ResultSet才是正確的作法. 當ResultSet使用close()方法時, 資源就會被釋放掉

try with resource construct

try(Connection conn=DriverManager.getConnection(url);
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(“select * from ContactName”)){} 這是比較好的寫法

try(ResultSet rs=DriverManager.getConnection(url).createStatement().executeQuery(“select * from ContactName”)){} 此種寫法比較不好, 因為只會關掉ResultSet, Statement及Connection不會自動關閉

ResultSet MetaData

ResultSet MetaData 是取得欄位的資訊
rs.getMetaData().getColumnCount() : 欄位數
rs.getMetaData().getColumnName(i+1) : 欄位名稱
rs.getMetaData().getColumnTypeName(i+1) : 欄位型態

    int column=rs.getMetaData().getColumnCount();
    String[] colNames=new String[column];
    String[] colTypes=new String[column];
    for (int i=0;i<column;i++){
        colNames[i]=rs.getMetaData().getColumnName(i+1);//記得加1
        colTypes[i]=rs.getMetaData().getColumnTypeName(i+1);
    }
    for(int i=0;i<column;i++){
        System.out.println(colNames[i]+":"+colTypes[i]);
    }

Getting a Row Count

ResultSet rs=stmt.executeQuery(“select count(*) from EMPLOYEE”);
rs.next();
int count=rs.getInt(1);

ResultSet cache大小(Controlling ResultSet fetch size)

rs.setFetchSize(25); 一次取得25筆資料, 待超出cache, 自動讀取另25筆資料

PreparedStatement — 防止SQL Injection

PreparedStatement是Statement的子類別, 可以使用參數代替
String query=”select * from employee where salary > ?”;
PreparedStatement pStmt=conn.prepareStatement(query);
pStmt.setInt(1, 100_000); //第一參數的index 是1
ResultSet rs=pStmt.executeQuery();

stmt.executeQuery(queryString);需要字串
pStmt.executeQuery();不需要字串, 因為事先傳入, 且改好了

CallableStatement

用來執行非SQL語法的資料庫
conn.prepareCall(“{CALL EmplAgeCount (?, ?)}”);

交易 Transaction

交易, 是一個機制, 把一群的動作當成是一個, 其中一個動作沒完成就失敗, 需所有的動作都完成才成功, 此機制是可跨資料庫的. 最經典的例子就是多種讀取server時, 若其中一個動作失敗了, 就必需回復到未更改時的狀況.

JDBC的Statement 命令, 預設是自動commit的‧
要取消Auto commit為 conn.setAutoCommit(false);
在任何地方可以使用Savepoint point=conn.setSavepoint(“point”);//用來設定返回點
此時若有執行stmt.executeUpdate(“insert into ……”);資料庫是不會立即變更的
然後執行conn.rollback(point);則上述的update就會被取消掉
然後要手動commit ==> conn.commit();

ACID

Automicity, Consistency, Isolation, Durability四個字的縮寫

RowSet 1.1

RowSet是另一種操作資料庫的方式, 繼承自ResultSet. RowSet其實就是把連線物件, 命令, 產生的結果, 全都集合在一起, 然後結果由RowSet取出. RowSet默認就是cursor可上下移動、可更新、可序列化（方便網路傳輸），可當作JavaBeans使用(JSP)

它下面主要有5個子介面JdbcRowSet、CachedRowSet、WebRowSet、JoinRowSet、FilteredRowSet

JdbcRowSet：封裝ResultSet，使得它能可以當作JavaBeans使用，但是它必須保持與資料庫的連接(非離線)

CachedRowSet：擁有JdbcRowSet所有功能，同時還能斷開資料庫連接進行離線操作

WebRowSet：擁有所有的CachedRowSet功能，同時還能通過xml來描述自己（把自己寫成xml，通過xml來配置自己）

JoinRowSet：擁有所有WebRowSet的功能，同時它能在斷開資料來源的情況下做一個sql join（感覺就是java版的表連接，比如2個結果集我通過什麼欄位將其關聯成一個表，sql中的join（聯表）操作）

FilteredRowSet：有所有WebRowSet的功能，如其名，可以做過濾操作（在不使用查詢語句和斷開資料來源的情況下）

先由RowSetProvider.newFactory() 取得RowSetFactory, 再由RowSetFactory.createXXXRowSet取得RowSet

注意 : RowSetFactory不是AutoCloseable的喔

try(JdbcRowSet jrs=RowSetProvider.newFactory().createJdbcRowSet();){            
    //String url="jdbc:jtds:sqlserver://localhost;databaseName=contact;user=thomas;password=123456";
    String url="jdbc:jtds:sqlserver://localhost/contact";
    jrs.setUrl(url);
    jrs.setUsername("thomas");
    jrs.setPassword("123456");
    jrs.setCommand("select * from ContactName");
    jrs.execute();
    while(jrs.next()){
        System.out.println(jrs.getString(2));
    }
} catch (SQLException ex) {
    Logger.getLogger(MSSQLTest.class.getName()).log(Level.SEVERE, null, ex);
}

CacheRowSet可封裝ResultSet(JDBCRowSet也可以封裝, 但沒試過)

ResultSet rs=pstmt.executeQuery();  
CachedRowSet crs=rsf.createCachedRowSet();  
crs.populate(rs); //封装rs成CachedRowSet  
con.close();
此時rs.next()是不可運作的, 因為斷線了
但crs.next()是沒問題的, 可離線作業

DAO

    Which group of method is moved to a new class when implementing the DAO pattern?
    A.  public in getId()
        public String getContractDetails()
        public void setContractDetails(String contractDetails)
        public String getName()
        public void setName(String name)

    B.  public int etId()
        public String getContractDetails()
        public String getName()
        public Person getPerson(int id) throws Exception

    C.  public void setContractDetails(String contractDetails)
        public void setName(String name)

    D.  public Person getPerson(int id)throws Exception
        public void createPerson(Person p)throws Exception
        public void deletePerson(int id)throws Exception
        public void updatePerson(Person p)throws Exception

    Answer : D

JDBC API

jtds

操作資料庫順序如下

指定使用的driver

Vendor’s Driver Class

JDBC API關鍵元件

Exam

Statement

ResultSet物件

可攜式JDBC

getString, getDouble

SQL 語法

執行SQL 預存程序

CallableStatement

SQLException

JDBC物件關閉

try with resource construct

ResultSet MetaData

Getting a Row Count

PreparedStatement — 防止SQL Injection

CallableStatement

交易 Transaction

ACID

RowSet 1.1

DAO

發佈留言