• Oracle JDBC

    1、CONNECT

    1 Class.forName("org.postgresql.Driver");
2 Connection connection = null;
3 connection = DriverManager.getConnection(
4     "jdbc:oracle:thin:@localhost:1521:mkyong","username","password");
5 connection.close();

    JDBC & Statement

    2、STATAMENT

    The “Statement” interface is used to execute a simple SQL statement with no parameters. For create, insert, update or delete statement, uses “Statement.executeUpdate(sql)“; select query, uses “Statement.executeQuery(sql)“.

    3、 To issue a create statement, calls the Statement.execute() method like this :

    1 Statement statement = dbConnection.createStatement();
2 // execute create SQL stetement
3 statement.execute(createTableSQL);

    4、To issue a insert statement, calls the Statement.executeUpdate() method like this :

    1 Statement statement = dbConnection.createStatement();
2 // execute the insert SQL stetement
3 statement.executeUpdate(insertTableSQL);

    5、To issue a update statement, calls the Statement.executeUpdate() method like this :

    1 Statement statement = dbConnection.createStatement();
2 // execute the update SQL stetement
3 statement.executeUpdate(updateTableSQL);

    6、 To issue a delete statement, calls the Statement.executeUpdate() method like this :

    1 Statement statement = dbConnection.createStatement();
2 // execute the delete SQL stetement
3 statement.executeUpdate(deleteTableSQL);

    7、 To issue a select query, calls the Statement.executeQuery method like this :

    1 String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";
2 Statement statement = dbConnection.createStatement();
3 ResultSet rs = statement.executeQuery(selectTableSQL);
4 while (rs.next()) {
5     String userid = rs.getString("USER_ID");
6     String username = rs.getString("USERNAME");    
7 }

    8、批量处理数据；

    Here’s an example to show you how to insert few records in batch process, via JDBC Statement.
    Batch Update is not limit to Insert statement, it’s apply for Update and Delete statement as well.

    insertSQL

    1 String insertTableSQL1 = "INSERT INTO DBUSER"
2                 + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
3                 + "(101,'mkyong','system', " + "to_date('"
4                 + getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";

    JAVA:

     1 dbConnection.setAutoCommit(false);
 2  
 3 statement = dbConnection.createStatement();
 4 statement.addBatch(insertTableSQL1);
 5 statement.addBatch(insertTableSQL2);
 6 statement.addBatch(insertTableSQL3);
 7  
 8 statement.executeBatch();
 9  
10 dbConnection.commit();

     JDBC & PreparedStatement

    The “PreparedStatement” interface is extended “Statement”, with extra feature to send a pre-compiled SQL statement with parameters. For create, insert, update or delete statement, uses “PreparedStatement.executeUpdate(sql)“; select query, uses “PreparedStatement.executeQuery(sql)“.

     1、To issue a create statement, calls the PrepareStatement.executeUpdate() method like this :

    1 PreparedStatement preparedStatement = dbConnection.prepareStatement(createTableSQL);
2 // EXECUTE CREATE SQL stetement
3 preparedStatement.executeUpdate();

    2、To issue an insert statement, calls the PreparedStatement.executeUpdate() method like this :

     1 String insertTableSQL = "INSERT INTO DBUSER"
 2         + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
 3         + "(?,?,?,?)";
 4 PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);
 5 preparedStatement.setInt(1, 11);
 6 preparedStatement.setString(2, "mkyong");
 7 preparedStatement.setString(3, "system");
 8 preparedStatement.setTimestamp(4, getCurrentTimeStamp());
 9 // execute insert SQL stetement
10 preparedStatement .executeUpdate();

    3、To issue a update statement, calls the PreparedStatement.executeUpdate() method like this :

    1 String updateTableSQL = "UPDATE DBUSER SET USERNAME = ? WHERE USER_ID = ?";
2 PreparedStatement preparedStatement = dbConnection.prepareStatement(updateTableSQL);
3 preparedStatement.setString(1, "mkyong_new_value");
4 preparedStatement.setInt(2, 1001);
5 // execute update SQL stetement
6 preparedStatement .executeUpdate();

    4、To issue a delete statement, calls the PreparedStatement.executeUpdate() method like this :

    1 String deleteSQL = "DELETE DBUSER WHERE USER_ID = ?";
2 PreparedStatement preparedStatement = dbConnection.prepareStatement(deleteSQL);
3 preparedStatement.setInt(1, 1001);
4 // execute delete SQL stetement
5 preparedStatement.executeUpdate();

    5、display the records via a ResultSet object. To issue a select query, calls the PreparedStatement.executeQuery() method like this 

    1 String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
2 PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
3 preparedStatement.setInt(1, 1001);
4 ResultSet rs = preparedStatement.executeQuery(selectSQL );
5 while (rs.next()) {
6     String userid = rs.getString("USER_ID");
7     String username = rs.getString("USERNAME");    
8 }

    6、批量处理数据；Batch Update is not limit to Insert statement, it’s apply for Update and Delete statement as well.（适用于增删改）

     1 dbConnection.setAutoCommit(false);//commit trasaction manually
 2  
 3 String insertTableSQL = "INSERT INTO DBUSER"
 4             + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
 5             + "(?,?,?,?)";                
 6 PreparedStatement = dbConnection.prepareStatement(insertTableSQL);
 7  
 8 preparedStatement.setInt(1, 101);
 9 preparedStatement.setString(2, "mkyong101");
10 preparedStatement.setString(3, "system");
11 preparedStatement.setTimestamp(4, getCurrentTimeStamp());
12 preparedStatement.addBatch();
13  
14 preparedStatement.setInt(1, 102);
15 preparedStatement.setString(2, "mkyong102");
16 preparedStatement.setString(3, "system");
17 preparedStatement.setTimestamp(4, getCurrentTimeStamp());
18 preparedStatement.addBatch();
19 preparedStatement.executeBatch();
20  
21 dbConnection.commit();

     JDBC事务管理：

    JDBC Transaction let you control how and when a transaction should commit into database.

     

    1 //transaction block start
2  
3 //SQL insert statement
4 //SQL update statement 
5 //SQL delete statement
6  
7 //transaction block end

    In simple, JDBC transaction make sure SQL statements within a transaction block are all executed successful, if either one of the SQL statement within transaction block is failed, abort and rollback everything within the transaction block.

    (JDBC确保了在事务块中的所有语句都执行成功，如果它们之中的一个执行失败，那么在事务中执行的所有操作都会进行回滚)

    1. Without JDBC Transaction

    By default, data will be committed into database when executeUpdate() is called.
    (默认情况下，数据将会在执行executeUpdate()方法的时候，提交到数据库中)：

     1 String insertTableSQL = "INSERT INTO DBUSER"
 2             + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
 3             + "(?,?,?,?)";
 4  
 5 String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
 6             + "WHERE USER_ID = ?";
 7  
 8 preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
 9 preparedStatementInsert.setInt(1, 999);
10 preparedStatementInsert.setString(2, "mkyong101");
11 preparedStatementInsert.setString(3, "system");
12 preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
13 preparedStatementInsert.executeUpdate(); //data COMMITTED into database.
14  
15 preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
16 preparedStatementUpdate.setString(1, "A very very long string caused DATABASE ERROR"); 
17 preparedStatementUpdate.setInt(2, 999);
18  
19 preparedStatementUpdate.executeUpdate(); //Error, value too big,  ignore this update statement, 
20                                                 //but user_id=999 is inserted(出错)

    When this code is executed, the USER_ID = ’999′ is inserted but the username is not update.

    (数据提交到数据库中去，但是后面的更新语句失败)

    2. With JDBC Transaction

    To put this in a transaction, you can use

       1、dbConnection.setAutoCommit(false); to start a transaction block.（开始事务）

       2、dbConnection.commit(); to end a transaction block.（结束事务）

     1 dbConnection.setAutoCommit(false); //transaction block start(手动的控制事务)
 2  
 3 String insertTableSQL = "INSERT INTO DBUSER"
 4             + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
 5             + "(?,?,?,?)";
 6  
 7 String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
 8             + "WHERE USER_ID = ?";
 9  
10 preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
11 preparedStatementInsert.setInt(1, 999);
12 preparedStatementInsert.setString(2, "mkyong101");
13 preparedStatementInsert.setString(3, "system");
14 preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
15 preparedStatementInsert.executeUpdate(); //data IS NOT commit yet(数据当前还没有提交)
16  
17 preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
18 preparedStatementUpdate.setString(1, "A very very long string caused DATABASE ERROR"); 
19 preparedStatementUpdate.setInt(2, 999);
20 preparedStatementUpdate.executeUpdate(); //Error, rollback, including the first insert statement.
21  (出现错误，所有的数据操作都进行回滚)
22 dbConnection.commit(); //transaction block end(手动的提交事务)

    When this code is executed, update statement is hits error, and make both insert and update statements rollback together.(所有的操作都会进行回滚)
  • 相关阅读:
    spring的了解以及简单框架的搭建
    动态代理
    struts2标签（转）
    使用OGNL表达式
    struts2 ValueStack
    struts2框架xml验证
    struts2 validate手动验证
    struts2自定义拦截器
    struts2文件上传
    当findById（Integer id）变成String类型
  • 原文地址：https://www.cnblogs.com/caroline/p/JDBC.html
Copyright © 2020-2023  润新知