JDBC的学习总结
最近在学习JDBC，所以总结了一下，与大家相互交流，欢迎拍砖~http://www.agoit.com/images/smiles/icon_biggrin.gif以MySQL数据库为例，建立连接如下：
Connection conn = null;Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_test", "root", "1234");conn.close();
[*]采用Statement时，编码如下（注：此代码为总结的结果，不保证copy后能正常运行！）：
Statement stmt = null;//此处分为了三种情况String strSQL1 = "CREATE TABLE SQL";String strSQL2 = "INSERT/UPDATE/DELETE SQL";String strSQL3 = "SELECT SQL";stmt = conn.createStatement();// 1. 执行建表语句时stmt.execute(strSQL1);// 2. 执行插入、更新、删除语句时stmt.executeUpdate(strSQL2);// 3. 执行查询语句时ResultSet rs = stmt.executeQuery(strSQL3);while(rs.next()){ String col0 = rs.getString("COL0"); String col1 = rs.getString("COL1"); String col2 = rs.getString("COL2"); System.out.println(col0 + "\t" + col1 + "\t" + col2);}stmt.close();
[*]采用PreparedStatement时，编码如下（注：此代码为总结的结果，不保证copy后能正常运行！）：
PreparedStatement prepStmt = null;// 此处分为了三种情况String strSQL1 = "CREATE TABLE SQL";String strSQL2 = "INSERT/UPDATE/DELETE ... (?,?,?) ... SQL";String strSQL3 = "SELECT ... ? ... SQL";// 1. 执行建表语句时prepStmt = conn.prepareStatement(strSQL1);prepStmt.executeUpdate();// 2. 执行插入、更新、删除语句时prepStmt = conn.prepareStatement(strSQL2);prepStmt.setInt(1, "1001");prepStmt.setString(2, "AngryBird");prepStmt.setDate(3, "2012-7-8");prepStmt.executeUpdate();// 3. 执行查询语句时prepStmt = conn.prepareStatement(strSQL3);ResultSet rs = prepStmt.executeQuery();while (rs.next()) { String col0 = rs.getString("COL0"); String col1 = rs.getString("COL1"); String col2 = rs.getString("COL2"); System.out.println(col0 + "\t" + col1 + "\t" +col2);}prepStmt.close();
JDBC中的事务的处理：
JDBC中的事务默认是自动提交的，在执行insert语句和update语句，当update语句出错时，insert语句还能正常插入；当把setAutoCommit()设为false，update语句出错时，整个事务就会rollback，insert语句也就不能正常的插入。
PreparedStatement prepStmt1 = null;PreparedStatement prepStmt2 = null;String strSQL1 = "INSERT SQL";String strSQL2 = "UPDATE SQL";conn.setAutoCommit(false); // 开启事务prepStmt1 = conn.prepareStatement(strSQL1);prepStmt1.setInt(1, "1001");prepStmt1.setString(2, "AngryBird");prepStmt1.SetDate(3, "2012-7-8");prepStmt1.executeUpdate();prepStmt2 = conn.prepareStatement(strSQL2);prepStmt2.setString(1, "UFO");prepStmt2.setInt(2, "1001");prepStmt2.executeUpdate();conn.commit(); // 关闭事务
JDBC中与不同数据库连接的Driver和URL，如下表所示:
注：后面的圆括号中为各数据库所默认的端口号。
DB Driver & URLOracleDriver: oracle.jdbc.driver.OracleDriver
URL: jdbc:oracle:thin:@<server_ip>:<port>:<dbname> (port=1521)
MySQLDriver: com.mysql.jdbc.Driver
URL: jdbc:mysql://<host_name>:<port>/<dbname> (port=3306)
DB2Driver: com.ibm.db2.jdbc.app.DB2Driver
UR: jdbc:db2://<host_name>:<port>/<dbname> (port=5000)
SQL ServerDriver: com.microsoft.jdbc.sqlserver.SQLServerDriver
URL: jdbc:microsoft:sqlserver://<host_name>:<port>;DatabaseName=<dbname> (port=1433)
页:
[1]