DAT602 Database Application Development

1. DAT602 Database Application Development Lecture 10 Manipulate Data by Using JDBC

2. Database Application Development - Lecture 10 • In this lecture, we’ll discuss about how to manipulate data by using JDBC in Java application. - insert - update - delete

3. Database Application Development - Lecture 10 • INSERT The basic form of the INSERT statement looks like this: INSERT INTO tableName (colName1, colName2, ...) VALUES (value1, value2, ...); Example: INSERT INTO Contact_Info (Email, LName, FName, MI) VALUES (‘offers@cosa_nostra.com’,‘Corleone’,‘Michael’,‘X’);

4. Database Application Development - Lecture 10 • Example of a INSERT statement (no column names) INSERT INTO Contact_Info VALUES ('Michael','X','Corleone','offers@cosa.com'); NOTE: String data is specified in single quotes ('), as shown in the examples. Numeric values are specified without quotes.

5. Database Application Development - Lecture 10 • Rule of INSERT 1. The column names you use must match the names defined for the column. 2. The values you insert must match the data type defined for the column they are being inserted into. 3. The data size must not exceed the column width. 4. The data you insert into a column must comply with the column's data constraints.

6. Database Application Development - Lecture 10 • Simple INSERT public class SimpleInsert { static String url = "jdbc:sqlserver://localhost:1433; DatabaseName=EPLMS"; static String userName = "sa"; static String password = "123456"; public static void main(String[] args) { try { String insertString = "INSERT INTO UserInfo (ID,Name,Balance) " + "VALUES ('4','Dave',123.45)"; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection con = DriverManager.getConnection(url, userName, password); Statement stmt = con.createStatement(); stmt.execute(insertString); stmt.close(); con.close(); } catch (Exception e) { System.err.println(e.getMessage()); } } }

7. Database Application Development - Lecture 10 • Prepared statement for insert public class PreparedInsert { static String url = "jdbc:sqlserver://localhost:1433; DatabaseName=EPLMS"; static String userName = "sa"; static String password = "123456"; public static void main(String[] args) { try { String psString = "INSERT INTO UserInfo (ID,Name,Balance) " + "VALUES (?,?,?)"; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection con = DriverManager.getConnection(url, userName, password); PreparedStatementpsstmt = con.prepareStatement(psString); psstmt.setString(1, args[0]); psstmt.setString(2, args[1]); psstmt.setFloat(3,Float.valueOf(args[2])); psstmt.execute(); psstmt.close(); con.close(); } catch (Exception e) { System.err.println(e.getMessage()); } } }

8. Database Application Development - Lecture 10 • Batch Processing Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection con = DriverManager.getConnection(url, userName, password); // prepared statement String psString = "INSERT INTO UserInfo (ID,Name,Balance) " + "VALUES (?,?,?)"; PreparedStatementpsstmt = con.prepareStatement(psString); System.out.println("Record List:"); for (String id : recordList.keySet()) { Record record = recordList.get(id); System.out.println("ID:" + id + " Name:" + record.getName() + " Balance:" + record.getBalance()); // assign parament psstmt.setString(1, id); psstmt.setString(2, record.getName()); psstmt.setFloat(3, record.getBalance()); // add to batch psstmt.addBatch(); }

9. Database Application Development - Lecture 10 • The UPDATE Statement Example: UPDATE UserInfo SET ID = '212‘, Name = 'Superman', Balance = 1111 WHERE Name = 'Amy' Caution 1. If you omit the WHERE clause from the UPDATE statement, all records in the given table are updated. 2. if you try to modify the primary key or foreign key value, you should modify multiple tables to ensure data consistence.

10. Database Application Development - Lecture 10 • Using Calculated Values with UPDATE UPDATE UserInfo SET Balance = Balance * 1.03 WHERE Name = 'Corn Flakes'; if you remove the WHERE clause, all records will be updated.

11. Database Application Development - Lecture 10 Transaction Management with COMMIT and ROLLBACK • A transaction is a group or sequence of commands, all of which must be executed in order and must complete successfully. Client A orders a transfer of funds to Client B, at least two database-access commands must be executed: - Client A's account must be debited. - Client B‘s account must be credited.

12. Database Application Development - Lecture 10 Two different commands • COMMIT command commits changes made from the beginning of the transaction to the point at which the command is issued • ROLLBACK command undoes all commands.

13. Database Application Development - Lecture 10 • AUTOCOMMIT Most databases support the AUTOCOMMIT option , RDBMS to commit all commands individually, as they are executed. SET AUTOCOMMIT [ON | OFF] • In JDBC, By default, the SET AUTOCOMMIT ON command is executed at startup, telling the RDBMS to commit all statements automatically as they are executed.

14. Database Application Development - Lecture 10 Example with AutoCommit on (SimpleInsert) public void execute(String SQLCommand){ String url = urlRoot+dbName; try { Connection con = DriverManager.getConnection(url); con.setAutoCommit(true); Statement stmt = con.createStatement(); stmt.execute(SQLCommand); con.close(); }catch(SQLException e){ System.err.print(e.getMessage()); } }

15. Database Application Development - Lecture 10 Turn off the AutoCommit off • When you start to work with a transaction, turn Autocommit off. con.setAutoCommit(false); Then issue the commands required by the transaction, and, assuming that everything executes correctly, commit the transaction using this command: COMMIT;

16. Database Application Development - Lecture 10 Example of turn AutoCommit off, and no commit (Transaction_Commit). public void execute(String SQLCommand){ String url = urlRoot+dbName; try { Connection con = DriverManager.getConnection(url); con.setAutoCommit(false); Statement stmt = con.createStatement(); stmt.execute(SQLCommand); con.close(); }catch(SQLException e){ System.err.print(e.getMessage()); } } No changes will be written into the database.

17. Database Application Development - Lecture 10 Example of turn AutoCommit off, and with commit (Transaction_Commit). public void execute(String SQLCommand){ String url = urlRoot+dbName; try { Connection con = DriverManager.getConnection(url); con.setAutoCommit(false); Statement stmt = con.createStatement(); stmt.execute(SQLCommand); con.commit(); con.close(); }catch(SQLException e){ System.err.print(e.getMessage()); } } All changes will be written into the database.

18. Database Application Development - Lecture 10 Rollback try { Connection con = DriverManager.getConnection(url); con.setAutoCommit(false); Statement stmt = con.createStatement(); stmt.execute(SQLCommand); String query = "SELECT Street FROM Contact_Info "+ "WHERE First_Name = 'Michael' AND Last_Name ='Corleone'"; ResultSetrs = stmt.executeQuery(query); rs.next(); System.out.println("Street = "+rs.getString(1)); con.rollback(); con.close(); }

19. Database Application Development - Lecture 10 • DELETE Statement Use of the DELETE command is very straightforward. DELETE FROM UserInfo WHERE Name = “Amy”

20. Database Application Development - Lecture 10 • Reference: Java Database Programming Bible by John O'Donahue ISBN:0764549243 Chapter 7.