jdbc的用法

2019-11-14 10:16:40

字體：大中小

供稿：網(wǎng)友

一，JDBC基本操作　　為了簡(jiǎn)單起見(jiàn)，與數(shù)據(jù)庫(kù)相關(guān)的操作、命令、參數(shù)都被硬編碼了。有興趣的讀者可以對(duì)這些進(jìn)行探索，降低數(shù)據(jù)與操作的耦合性。　　先看具體代碼并實(shí)踐，本文第五部分對(duì)用到的API稍作了研究。　　下面的所有方法和數(shù)據(jù)成員都在public class JDBCOperation內(nèi)部。(1)定義記錄的類(lèi)（可選）　　這樣做主要是為了便于操作和接口定義，是非必須的。復(fù)制代碼static class Student { PRivate String Id; private String Name; private String Sex; private String Age; Student(String Name, String Sex, String Age) { this.Id = null; //default this.Name = Name; this.Sex = Sex; this.Age = Age; } public String getId() { return Id; } public void setId(String Id) { this.Id = Id; } public String getName() { return Name; } public void setName(String Name) { this.Name = Name; } public String getSex() { return Sex; } public void setSex(String Sex) { this.Sex = Sex; } public String getAge() { return Age; } public void setage(String Age) { this.Age = Age; }}復(fù)制代碼 (2)連接的獲取　　在操作前必須先獲取與數(shù)據(jù)庫(kù)的連接。　　driver、url的格式同樣可以參考各種數(shù)據(jù)庫(kù)對(duì)應(yīng)的jar包、驅(qū)動(dòng)類(lèi)名和URL格式。復(fù)制代碼private static Connection getConn() { String driver = "com.MySQL.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/samp_db"; String username = "root"; String passWord = ""; Connection conn = null; try { Class.forName(driver); //classLoader,加載對(duì)應(yīng)驅(qū)動(dòng) conn = (Connection) DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn;}復(fù)制代碼 (3)insert復(fù)制代碼private static int insert(Student student) { Connection conn = getConn(); int i = 0; String sql = "insert into students (Name,Sex,Age) values(?,?,?)"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); pstmt.setString(1, student.getName()); pstmt.setString(2, student.getSex()); pstmt.setString(3, student.getAge()); i = pstmt.executeUpdate(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i;}復(fù)制代碼 (4)update復(fù)制代碼private static int update(Student student) { Connection conn = getConn(); int i = 0; String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i;}復(fù)制代碼 (5)select　　以select * from XXX為例。復(fù)制代碼private static Integer getAll() { Connection conn = getConn(); String sql = "select * from students"; PreparedStatement pstmt; try { pstmt = (PreparedStatement)conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); int col = rs.getMetaData().getColumnCount(); System.out.println("============================"); while (rs.next()) { for (int i = 1; i <= col; i++) { System.out.print(rs.getString(i) + "/t"); if ((i == 2) && (rs.getString(i).length() < 8)) { System.out.print("/t"); } } System.out.println(""); } System.out.println("============================"); } catch (SQLException e) { e.printStackTrace(); } return null;}復(fù)制代碼 (6)delete復(fù)制代碼private static int delete(String name) { Connection conn = getConn(); int i = 0; String sql = "delete from students where Name='" + name + "'"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i;}復(fù)制代碼二、測(cè)試　　在測(cè)試前，需要在系統(tǒng)中打開(kāi)對(duì)應(yīng)數(shù)據(jù)庫(kù)的服務(wù)。MySQL在Windows下的啟動(dòng)命令為net start mysql 　　測(cè)試代碼復(fù)制代碼public static void main(String args[]) { JDBCOperation.getAll(); JDBCOperation.insert(new Student("Achilles", "Male", "14")); JDBCOperation.getAll(); JDBCOperation.update(new Student("Bean", "", "7")); JDBCOperation.delete("Achilles"); JDBCOperation.getAll();}復(fù)制代碼　　Eclipse中的輸出復(fù)制代碼============================1 Ender male 8 2 Bean male 6 3 Petra fema 9 4 Peter male 9 5 _Graff male 40 6 GOD fema 255 ========================================================1 Ender male 8 2 Bean male 6 3 Petra fema 9 4 Peter male 9 5 _Graff male 40 6 GOD fema 255 7 Achilles Male 14 ============================resutl: 1resutl: 1============================1 Ender male 8 2 Bean male 7 3 Petra fema 9 4 Peter male 9 5 _Graff male 40 6 GOD fema 255 ============================復(fù)制代碼三、代碼分析　　在上述對(duì)數(shù)據(jù)庫(kù)進(jìn)行增刪改查的過(guò)程中，可以發(fā)現(xiàn)其共性部分，即通用的流程：　　(1)創(chuàng)建Connection對(duì)象、SQL查詢(xún)命令字符串；　　(2)對(duì)Connection對(duì)象傳入SQL查詢(xún)命令，獲得PreparedStatement對(duì)象；　　(3)對(duì)PreparedStatement對(duì)象執(zhí)行executeUpdate()或executeQurey()獲得結(jié)果；　　(4)先后關(guān)閉PreparedStatement對(duì)象和Connection對(duì)象。　　可見(jiàn)，使用JDBC時(shí)，最常打交道的是Connection、PreparedStatement這兩個(gè)類(lèi)，以及select中的ResultSet類(lèi)。查閱java API手冊(cè)可以了解其具體的意義和方法。　　下面引用的Java API的資料出自http://download.Oracle.com/technetwork/java/javase/6/docs/zh/api/index.html。　　　　Connectionjava.sql 接口 Connection所有超級(jí)接口：Wrapperpublic interface Connectionextends Wrapper 與特定數(shù)據(jù)庫(kù)的連接（會(huì)話(huà)）。在連接上下文中執(zhí)行 SQL 語(yǔ)句并返回結(jié)果。Connection 對(duì)象的數(shù)據(jù)庫(kù)能夠提供描述其表、所支持的 SQL 語(yǔ)法、存儲(chǔ)過(guò)程、此連接功能等等的信息。此信息是使用 getMetaData 方法獲得的。　　PreparedStatemntjava.sql 接口 PreparedStatement所有超級(jí)接口：Statement, Wrapper所有已知子接口：CallableStatementpublic interface PreparedStatementextends Statement表示預(yù)編譯的 SQL 語(yǔ)句的對(duì)象。SQL 語(yǔ)句被預(yù)編譯并存儲(chǔ)在 PreparedStatement 對(duì)象中。然后可以使用此對(duì)象多次高效地執(zhí)行該語(yǔ)句。常用方法 boolean execute() 在此 PreparedStatement 對(duì)象中執(zhí)行 SQL 語(yǔ)句，該語(yǔ)句可以是任何種類(lèi)的 SQL 語(yǔ)句。 ResultSet executeQuery() 在此 PreparedStatement 對(duì)象中執(zhí)行 SQL 查詢(xún)，并返回該查詢(xún)生成的 ResultSet 對(duì)象。 int executeUpdate() 在此 PreparedStatement 對(duì)象中執(zhí)行 SQL 語(yǔ)句，該語(yǔ)句必須是一個(gè) SQL 數(shù)據(jù)操作語(yǔ)言（Data Manipulation Language，DML）語(yǔ)句，比如 INSERT、UPDATE 或 DELETE 語(yǔ)句；或者是無(wú)返回內(nèi)容的 SQL 語(yǔ)句，比如 DDL 語(yǔ)句。　　　　ResultSet　　java.sql 接口 ResultSet所有超級(jí)接口：Wrapper所有已知子接口：CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, RowSet, SyncResolver, WebRowSetpublic interface ResultSetextends Wrapper表示數(shù)據(jù)庫(kù)結(jié)果集的數(shù)據(jù)表，通常通過(guò)執(zhí)行查詢(xún)數(shù)據(jù)庫(kù)的語(yǔ)句生成。

上一篇：{開(kāi)源項(xiàng)目}之wireshark插件開(kāi)發(fā)

下一篇：zmodem transfer canceled by remote side問(wèn)題解決