程序代碼Class.forName("com.mysql.jdbc.Driver");Connection conn = null;conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "passWord");conn.close();JDBC連接ORACLE相關jar 包下載地址:http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html 程序代碼Class.forName("org.postgresql.Driver");Connection connection = null;connection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:yihaomen","username","password");connection.close();JDBC連接Postgresql需要下載postgresql 的jdbc jar包:http://jdbc.postgresql.org/download.html 程序代碼Class.forName("org.postgresql.Driver");Connection connection = null;connection = DriverManager.getConnection( "jdbc:postgresql://hostname:port/dbname","username", "password");connection.close();上面介紹了 jdbc 連接幾種主流數據庫的方式，下面介紹各種SQL語句的處理方式, 用jdbc寫程序要特別注意的是connection 的關閉等，因此要注意try catch等的作用，一般用如下模板得到connection,并處理SQL語句,以oracle連接為例子 程序代碼import java.sql.DriverManager;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import java.text.DateFormat;import java.text.SimpleDateFormat;public class JDBCStatementInsertExample { private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:yihaomen"; private static final String DB_USER = "user"; private static final String DB_PASSWORD = "password"; private static final DateFormat dateFormat = new SimpleDateFormat( "yyyy/MM/dd HH:mm:ss"); public static void main(String[] argv) { try { insertRecordIntoDbUserTable(); } catch (SQLException e) { System.out.println(e.getMessage()); } } private static void insertRecordIntoDbUserTable() throws SQLException { Connection dbConnection = null; Statement statement = null; String insertTableSQL = "Insert INTO DBUSER" + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) " + "VALUES" + "(1,'mkyong','system', " + "to_date('" + getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))"; try { dbConnection = getDBConnection(); statement = dbConnection.createStatement(); System.out.println(insertTableSQL); // execute insert SQL stetement statement.executeUpdate(insertTableSQL); System.out.println("Record is inserted into DBUSER table!"); } catch (SQLException e) { System.out.println(e.getMessage()); } finally { if (statement != null) { statement.close(); } if (dbConnection != null) { dbConnection.close(); } } } private static Connection getDBConnection() { Connection dbConnection = null; try { Class.forName(DB_DRIVER); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); } try { dbConnection = DriverManager.getConnection( DB_CONNECTION, DB_USER,DB_PASSWORD); return dbConnection; } catch (SQLException e) { System.out.println(e.getMessage()); } return dbConnection; } private static String getCurrentTimeStamp() { java.util.Date today = new java.util.Date(); return dateFormat.format(today.getTime()); }}上面的例子是插入數據庫操作，其他的更新，增加等，都可以采用類似的方式。 程序代碼Statement statement = dbConnection.createStatement();// 執行插入語句statement.executeUpdate(insertTableSQL);或者用如下PreparedStatement方式 程序代碼String insertTableSQL = "Insert INTO DBUSER" + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) VALUES" + "(?,?,?,?)";PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);preparedStatement.setInt(1, 11);preparedStatement.setString(2, "yihaomen");preparedStatement.setString(3, "system");preparedStatement.setTimestamp(4, getCurrentTimeStamp());// execute insert SQL stetementpreparedStatement .executeUpdate();執行刪除語句 程序代碼String deleteSQL = "Delete DBUSER Where USER_ID = ?";PreparedStatement preparedStatement = dbConnection.prepareStatement(deleteSQL);preparedStatement.setInt(1, 1001);preparedStatement.executeUpdate();執行更新語句 程序代碼String updateTableSQL = "Update DBUSER SET USERNAME = ? Where USER_ID = ?";PreparedStatement preparedStatement = dbConnection.prepareStatement(updateTableSQL);preparedStatement.setString(1, "mkyong_new_value");preparedStatement.setInt(2, 1001);preparedStatement .executeUpdate();得到List 記錄,得到多條記錄: 程序代碼String selectSQL = "Select USER_ID, USERNAME FROM DBUSER Where USER_ID = ?";PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);preparedStatement.setInt(1, 1001);ResultSet rs = preparedStatement.executeQuery(selectSQL );while (rs.next()) { String userid = rs.getString("USER_ID"); String username = rs.getString("USERNAME"); }批量執行SQL語句，要啟用 事務 程序代碼dbConnection.setAutoCommit(false); statement = dbConnection.createStatement();statement.addBatch(insertTableSQL1);statement.addBatch(insertTableSQL2);statement.addBatch(insertTableSQL3); statement.executeBatch(); dbConnection.commit();或者采用如下preparedStatement 方式 程序代碼dbConnection.setAutoCommit(false);//commit trasaction manuallyString insertTableSQL = "Insert INTO DBUSER" + "(USER_ID, USERNAME, CreateD_BY, CreateD_DATE) VALUES" + "(?,?,?,?)"; PreparedStatement = dbConnection.prepareStatement(insertTableSQL);preparedStatement.setInt(1, 101);preparedStatement.setString(2, "mkyong101");preparedStatement.setString(3, "system");preparedStatement.setTimestamp(4, getCurrentTimeStamp());preparedStatement.addBatch();preparedStatement.setInt(1, 102);preparedStatement.setString(2, "mkyong102");preparedStatement.setString(3, "system");preparedStatement.setTimestamp(4, getCurrentTimeStamp());preparedStatement.addBatch();preparedStatement.executeBatch();dbConnection.commit();jdbc調用存儲過程1. 只有輸入參數的情況 程序代碼String insertStoreProc = "{call insertDBUSER(?,?,?,?)}";callableStatement = dbConnection.prepareCall(insertStoreProc);callableStatement.setInt(1, 1000);callableStatement.setString(2, "mkyong");callableStatement.setString(3, "system");callableStatement.setDate(4, getCurrentDate());callableStatement.executeUpdate();2.調用存儲過程，存儲過程有返回值的情況 程序代碼//getDBUSERByUserId is a stored procedureString getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);callableStatement.setInt(1, 10);callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);callableStatement.registerOutParameter(4, java.sql.Types.DATE);// execute getDBUSERByUserId store procedurecallableStatement.executeUpdate();String userName = callableStatement.getString(2);String createdBy = callableStatement.getString(3);Date createdDate = callableStatement.getDate(4);需要注意的是：通過 CallableStatement.registerOutParameter(index,sqlType) 注冊返回的參數,最后通過CallableStatement.getDataType(index) 取得結果3. 如果是 oracle 數據庫，而存儲過程返回的是 cursor 游標的處理方式 程序代碼String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);callableStatement.setString(1, "mkyong");callableStatement.registerOutParameter(2, oracleTypes.CURSOR);// execute getDBUSERCursor store procedurecallableStatement.executeUpdate();// get cursor and cast it to ResultSetrs = (ResultSet) callableStatement.getObject(2);// loop it like normalwhile (rs.next()) { String userid = rs.getString("USER_ID"); String userName = rs.getString("USERNAME");}首先通過 CallableStatement.registerOutParameter(index,OracleTypes.CURSOR). 注冊要返回的類型，最后用 callableStatement.getObject(index). 獲取結果.掌握了這些之后，幾乎所有與jdbc操作相關的東西，都懂了，增刪改查到事務，游標，存儲過程的處理都懂了。 新聞熱點
疑難解答
