JDBC基礎(chǔ)結(jié)構(gòu)知識(shí)

第十三章JDBC基礎(chǔ)知識(shí)回顧JTable的用途如何使用JTableJTree的用途如何使用JTree2本章目標(biāo)了解JDBC的概念及必要性了解JDBC驅(qū)動(dòng)程序類型理解JDBC程序的結(jié)構(gòu)運(yùn)用JDBC進(jìn)行數(shù)據(jù)庫(kù)編程

3數(shù)據(jù)庫(kù)訪問(wèn)技術(shù)簡(jiǎn)介

客戶機(jī)/服務(wù)器應(yīng)用程序數(shù)據(jù)庫(kù)編程ODBCJDBC兩個(gè)常用的API數(shù)據(jù)庫(kù)執(zhí)行SQL語(yǔ)句檢索查詢結(jié)果4數(shù)據(jù)庫(kù)ODBC客戶機(jī)/服務(wù)器GUI應(yīng)用程序ODBC（開(kāi)放式數(shù)據(jù)庫(kù)連接）（Microsoft提供）插入刪除修改應(yīng)用程序編程接口查詢5JDBCJDBC（Java數(shù)據(jù)庫(kù)連接）（sun公司提供）Java應(yīng)用程序編程接口Java應(yīng)用程序數(shù)據(jù)庫(kù)插入修改刪除查詢6JDBC驅(qū)動(dòng)程序的類型JDBC驅(qū)動(dòng)程序的類型JDBC-ODBC橋驅(qū)動(dòng)程序及ODBC驅(qū)動(dòng)程序本地API部分Java驅(qū)動(dòng)程序JDBC-Net純Java驅(qū)動(dòng)程序本地協(xié)議純Java驅(qū)動(dòng)程序7

JDBC體系結(jié)構(gòu)2-1Java程序JDBC驅(qū)動(dòng)程序數(shù)據(jù)庫(kù)SQL命令結(jié)果8JDBC體系結(jié)構(gòu)2-2應(yīng)用層Driver

StatementResultSet

Connection

各接口驅(qū)動(dòng)層9java.sql包3-1接口名說(shuō)明

Connection此接口表示與數(shù)據(jù)的連接PreparedStatement此接口用于執(zhí)行預(yù)編譯的SQL語(yǔ)句

ResultSet此接口表示了查詢出來(lái)的數(shù)據(jù)庫(kù)數(shù)據(jù)結(jié)果集Statement此接口用于執(zhí)行SQL語(yǔ)句并將數(shù)據(jù)檢索到ResultSet中

10java.sql包包3-2類名說(shuō)明

DriverManager此類用于加載和卸載各種驅(qū)動(dòng)程序并建立與數(shù)據(jù)庫(kù)的連接Date此類包含將SQL日期格式轉(zhuǎn)換成Java日期格式的各種方法Time此類用于表示時(shí)間TimeStamp此類通過(guò)添加納秒字段為時(shí)間提供更高的精確度

11java.sql包包3-3SQLException/***fooBar*/publicvoidfoobar()throwsSQLException{……thrownewSQLException(“剛引引發(fā)了一個(gè)SQLException”);}……try{fooBar();}catch(SQLExceptionex){System.out.println(“已已捕獲一個(gè)SQLException異常常!”);System.out.println(“消消息:“+ex.getMessage());System.out.println(“錯(cuò)錯(cuò)誤代碼:““+ex.getErrorCode());}…調(diào)用fooBar12JDBC程程序訪問(wèn)數(shù)據(jù)庫(kù)的的步驟2-1開(kāi)始導(dǎo)入java.sql包加載并注冊(cè)驅(qū)驅(qū)動(dòng)程序創(chuàng)建一個(gè)Connection對(duì)對(duì)象創(chuàng)建一個(gè)Statement對(duì)象象執(zhí)行語(yǔ)句關(guān)閉ResultSet對(duì)對(duì)象關(guān)閉Statement對(duì)象象關(guān)閉連接結(jié)束使用ResultSet對(duì)象象13Friends表的結(jié)結(jié)構(gòu)JDBC程程序訪問(wèn)數(shù)據(jù)庫(kù)的的步驟2-2它演示訪問(wèn)數(shù)數(shù)據(jù)庫(kù)的各個(gè)個(gè)步驟在執(zhí)行示例1中的程程序之前，SQLServer中中應(yīng)該存在在一個(gè)名為friends的的表演示：示例例1列名稱數(shù)據(jù)類型名稱

Varchar(50)地址Varchar(50)電話Numeric入職日期Datetime工資Numeric/**??2005AptechLimited*版權(quán)所所有*/importjava.sql.SQLException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Statement;importjava.sql.ResultSet;/**這這個(gè)類演示示訪問(wèn)數(shù)據(jù)據(jù)庫(kù)需遵循循的各個(gè)步步驟.*@version1.0,2005年年8月月26日*@authorBen*/classJdbctest{/**構(gòu)構(gòu)造方法*/protectedJdbctest(){}/**這是是main方法法.*/publicstaticvoidmain(String[]args){try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}catch(ClassNotFoundExceptionce){System.out.println(ce);}try{Stringurl="jdbc:odbc:test";Connectioncon=DriverManager.getConnection(url);Statements=con.createStatement();ResultSetrs=s.executeQuery("select*fromfriends");while(rs.next()){System.out.print(rs.getString(1)+"\t");System.out.print(rs.getString(2)+"\t");System.out.print(rs.getInt(3)+"\t");System.out.print(rs.getDate(4)+"\t");System.out.println("");}rs.close();s.close();con.close();}catch(SQLExceptionce){System.out.println(ce);}}}14JDBC查查詢2-1SQL查詢?cè)冏址甧xecuteQuery()方方法作為參數(shù)傳遞遞ResultSet返回查詢數(shù)據(jù)據(jù)SELECTname,email,phoneFROMcolleagues;使用SQL語(yǔ)句，查查詢可編寫為為：Stringstr="SELECTemp_id,lname,fnameFROMcolleagues";Statementstmt=con.createStatement();ResultSetrset=stmt.executeQuery(str);使用JDBC編寫此此查詢，則代代碼為：Statement接口口15JDBC查查詢2-2它演示SQL中sum()方法的用用法演示：示例2/***Jdbctest2*/importjava.sql.SQLException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Statement;importjava.sql.ResultSet;/**這個(gè)個(gè)類演示SQL中sum()方法的用用法*@version1.0,2005年年8月月26日日*@authorBen*/classJdbctest2{/**構(gòu)造造方法*/protectedJdbctest2(){}/**這是是main方法*/publicstaticvoidmain(String[]args){try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}catch(ClassNotFoundExceptionce){System.out.println(ce);}try{Stringurl="jdbc:odbc:test";Connectioncon=DriverManager.getConnection(url);Statements=con.createStatement();ResultSetrs=s.executeQuery("selecthiredate,sum(salary)fromfriendsgroupbyhiredate");while(rs.next()){System.out.print(rs.getDate(1)+"\t");System.out.print(rs.getInt(2)+"\t");System.out.println("");}rs.close;s.close();con.close();}catch(SQLExceptionce){System.out.println(ce);}}}16演示：示例3它演示SQL中INSERT語(yǔ)句的用用法/***Jdbctest3*/importjava.sql.SQLException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Statement;importjava.sql.ResultSet;/**這個(gè)個(gè)類演示INSERT語(yǔ)句的用用法.*@version1.0,2005年年8月月26日日*@authorBen*/classJdbctest3{/**構(gòu)造造方法*/protectedJdbctest3(){}JDBC插插入數(shù)據(jù)實(shí)現(xiàn)現(xiàn)/**這是是main方法*/publicstaticvoidmain(String[]args){try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}catch(ClassNotFoundExceptionce){System.out.println(ce);}try{Stringurl="jdbc:odbc:test";Stringstr="INSERTINTO““+friends(name,address,salary)“+"VALUES(‘‘朱八‘,’’深圳‘,25690)";Connectioncon=DriverManager.getConnection(url);Statements=con.createStatement();introwcount=s.executeUpdate(str);Stringstr1="selectname,sum(salary)““+fromfriends"+"groupbyname";ResultSetrs=s.executeQuery(str1);while(rs.next()){System.out.print(rs.getString(1)+"\t");System.out.print(rs.getInt(2)+"\t");System.out.println("");}rs.close();s.close();con.close();}catch(SQLExceptionce){System.out.println(ce);}}}17JDBC刪刪除和修改實(shí)實(shí)現(xiàn)它演示SQL中各種種命令的用法法演示：示例4/***Jdbc2*/importjava.sql.SQLException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Statement;/**這這這個(gè)類演示SQL中命令的用法法.*@version1.0,2005年8月26日*@authorBen*/classJdbc2{/**構(gòu)造造方法.*/protectedJdbc2(){}publicstaticvoidmain(String[]args){Connectioncon;Statementstmt;Stringurl;Stringsql;try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}catch(ClassNotFoundExceptionce){System.out.println(ce);}try{url="jdbc:odbc:test";con=DriverManager.getConnection(url);sql="Deletefromfriendswherertrim(name)like\'張三\';";System.out.println("");stmt=con.createStatement();stmt.executeUpdate(sql);System.out.println("張三的記錄錄已刪除“);stmt.close();con.close();con=DriverManager.getConnection(url);sql="Updatefriendssetaddress=\'青島\'where“+"rtrim(name)like\'李四\';";System.out.println("");stmt=con.createStatement();stmt.executeUpdate(sql);stmt.close();con.close();System.out.println("李李四的記錄已已更新");}catch(SQLExceptionce){System.out.println(ce);}}}18PreparedStatement接口3-1PreparedStatement接口（預(yù)編譯的SQL語(yǔ)語(yǔ)句）PreparedStatement用于提高運(yùn)行行時(shí)效率執(zhí)行PreparedStatement對(duì)象比執(zhí)行Statement對(duì)對(duì)象快Statement接接口19PreparedStatement接口3-2它演示了PreparedStatement的用法演示：示例5/**?*CourseAppl*/importjava.sql.SQLException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Statement;importjava.sql.ResultSet;importjava.sql.PreparedStatement;/**這個(gè)個(gè)類演示SQL中PreparedStatement的用用法*/classCourseAppl{privateConnectioncon;privateStringurl;privateStringserverName;privateStringportNumber;privateStringdatabaseName;privateStringuserName;privateStringpassword;privateStringsql;CourseAppl(){url="jdbc:microsoft:sqlserver://";serverName=“l(fā)ocalhost";portNumber="1433";databaseName=““test";userName=““sa";password=““sa";}privateStringgetConnectionUrl(){returnurl+serverName+":"+portNumber+";databaseName="+databaseName+";";}privatejava.sql.ConnectiongetConnection(){try{Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");con=DriverManager.getConnection(getConnectionUrl(),userName,password);if(con!=null){System.out.println("連連接成功!");}}catch(Exceptione){e.printStackTrace();System.out.println("getConnection()內(nèi)內(nèi)的錯(cuò)誤跟跟蹤:"+e.getMessage());}returncon;}publicvoiddisplay(){try{con=getConnection();PreparedStatementpstmt=con.prepareStatement("UPDATEfriendsSETsalary=?WHEREnamelike?");pstmt.setInt(1,10000);pstmt.setString(2,"李李四");pstmt.executeUpdate();System.out.println(“記記錄已更新!");Statements=con.createStatement();Stringsql="SELECT*FROMfriends";ResultSetrs=s.executeQuery(sql);while(rs.next()){System.out.println("");System.out.print(rs.getInt(1)+"");System.out.println(rs.getInt(5));}}catch(SQLExceptionce){System.out.println(ce);}}publicstaticvoidmain(String[]args){CourseApplretObj=newCourseAppl();retObj.display();}}20PreparedStatement

接口口3-3它演示在基于于條件的SQL查詢?cè)冎腥绾问褂糜肞reparedStatement，其其中條件在IN參數(shù)數(shù)中給出演示：示例6importjava.sql.SQLException;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.PreparedStatement;/**這個(gè)個(gè)類演示在基基于條件的SQL查查詢中使用PreparedStatement*/classRetrieveRecords{privateConnectioncon;privateStringurl;privateStringserverName,portNumber,databaseName,userName;privateStringsql;RetrieveRecords(){url="jdbc:microsoft:sqlserver://";serverName=“l(fā)ocalhost";portNumber="1433";databaseName=““test";userName=““sa";password=““sa";}privateStringgetConnectionUrl(){returnurl+serverName+":"+portNumber+";databaseName="+databaseName+";";}privatejava.sql.ConnectiongetConnection(){try{Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");con=DriverManager.getConnection(getConnectionUrl(),userName,password);if(con!=null){System.out.println("連連接成功!");}}catch(Exceptione){e.printStackTrace();System.out.println(“getConnection()內(nèi)內(nèi)的錯(cuò)誤跟跟蹤："+e.getMessage());}returncon;}publicvoiddisplay(){try{con=getConnection();sql="select*fromFriendswhereSalary>?";PreparedStatementpstmt=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);pstmt.setInt(1,5000);ResultSetrs=pstmt.executeQuery();while(rs.next()){System.out.print(rs.getString(1)+"\t");System.out.print(rs.getString(2)+"\t");System.out.print(rs.getInt(3)+"\t");System.out.print(rs.getDate(4)+"\t");System.out.print(rs.getInt(5)+"\t");System.out.println("");}rs.close();pstmt.close();con.close();}catch(SQLExceptionce){System.out.println(ce);}}/**這這是是main方方法法*/publicstaticvoidmain(String[]args){RetrieveRecordsretRec=newRetrieveRecords();retRec.display();}}21使用用結(jié)結(jié)果果集集3-1ResultSet對(duì)對(duì)象象完完全全依依賴賴于于Statement對(duì)對(duì)象象和和Connection對(duì)象象每次次執(zhí)執(zhí)行行SQL語(yǔ)語(yǔ)句句時(shí)時(shí)，，都都會(huì)會(huì)用用新新的的結(jié)結(jié)果果重重寫寫結(jié)結(jié)果果集集當(dāng)相相關(guān)關(guān)的的Statement關(guān)關(guān)閉閉時(shí)時(shí)，，ResultSet對(duì)對(duì)象象會(huì)會(huì)自自動(dòng)動(dòng)關(guān)關(guān)閉閉Next()get<Type>()此方方法法將將光光標(biāo)標(biāo)從從當(dāng)前前位位置置下下移移一一行行從ResultSet對(duì)象象返返回回?cái)?shù)數(shù)據(jù)據(jù)22使用用結(jié)結(jié)果果集集3-2它演演示示對(duì)對(duì)當(dāng)當(dāng)前前行行的的處處理理使用用next()方方法法時(shí)時(shí)，，記記錄錄是是按按順順序序處處理理的的必須須按按照照數(shù)數(shù)據(jù)據(jù)返返回回的的順順序序處處理理每每行行中中的的數(shù)數(shù)據(jù)據(jù)…ResultSetrset=stmt.getResultSet();while(rset.next()){Stringdept_name=rset.getString(1);}…23使用用結(jié)結(jié)果果集集3-3演示示對(duì)對(duì)當(dāng)當(dāng)前前列列的的處處理理使用用get<Type>()方法法可可以以直直接接訪訪問(wèn)問(wèn)列