Pertemuan 7 Java Database Connectivity (JDBC)

1. Matakuliah : T0053/Web Programming Tahun : 2006 Versi : 2 Pertemuan 7Java Database Connectivity (JDBC)

2. Learning Outcomes Pada akhir pertemuan ini, diharapkan mahasiswa akan mampu : • Menjelaskan Teknik Akses Database • Mengakes database menggunakan JDBC • Membuat aplikasi Database menggunakan Java

3. Outline Materi • RDBMS • JDBC • Transaksi menggunakan JDBC API • Aplikasi database

4. Relational Database Model • Database models • Hierarchal, network, relational (most popular) • Focus on relational • Relational Database Model • Logical representation of data • Consider relationships between data • Not concerned with implementation

5. Relational Database Model • Relational database • Composed of tables • Rows called records • Columns are fields (attributes) • First field usually primary key • Unique for each record • Primary key can be more than one field (column) • Primary key not required

6. Table: Employee Number Name Department Salary Location 23603 JONES, A. 413 1100 NEW JERSEY 24568 KERWIN, R. 413 2000 NEW JERSEY A record 34589 LARSON, P. 642 1800 LOS ANGELES 35761 MYERS, B. 611 1400 ORLANDO 47132 NEUMANN, C. 413 9000 NEW JERSEY 78321 STEPHENS, T. 611 8000 ORLANDO Primary Key A column Relational Database Model Relational Database Structure

7. Relational Database Overview • Books.mdb database Structure: • Primary key in bold • Rule of Entity Integrity • Every record has unique entry in primary key field

8. Structured Query Language • Overview of SQL • Context of Books.mdb database • SQL keywords discussed in context of complete queries • Some keywords beyond scope of text • Used to • Query a database • Insert records into a database • Update existing records in a database • SQL keywords • SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY • INSERT, UPDATE, DELETE, etc

9. Structured Query Language • Example SQL Query: SELECT * FROM Titles WHERE Title LIKE '*How to Program' ORDER BY Title ASC • SQL Result:

10. JDBC – API Overview • JDBC API makes it possible to do three things: • Establish a connection with a database or access anytabular data source • Send SQL statements • Process the results

11. JDBC Architecture for Java Application Type 2 Type 1

12. JDBC Architecture for Java Applet Type 4 Type 3

13. JDBC Features • Leverage Existing Enterprise Data • Simplified Enterprise Development : Easy to use API • Zero Configuration for Network Computers • Full Access to Metadata • No Installation • Database Connection Identified by URL • Supported by many industry

14. JDBC Features • From java.sql.*: (standard) • Scrollable result set: MoveNext, MovePrev • Updatable result set • Supported BLOB and CLOB • Batch updates: performance improvement, sending multiple updated instead of single updated to DB • Savepoints: Ability to roll transactions back to where a savepoint is set • From javax.sql.* (optional): • Connection pooling • Distributed transactions • JNDI support

15. Using JDBC API • Setup Database and JDBC Driver • Ex: Books.mdb using JdbcOcbd bridge • Loading Driver • Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); • Making Connection • Connection con = DriverManager.getConnection(“jdbc:odbc:books”, “userName”, “password”); • Send/Execute Query • Statement stmt = con.createStatement(); • ResultSet rs = stmt.executeQuery(“SELECT * from Authors”);

16. Setup dialog appears. Enter name used to reference database and description (optional). Use Select... to choose database file. Use Advanced... to create a username (anonymous) and password (guest). When done, click OK ODBC Data Source Administrator now has Books. We can now access ODBC data source using JDBC to ODBC driver. This allows us to register our User Data Source Name. Go to the User DSN tab and click Add... We are using Access, so select Microsoft Access Driver, then Finish Setup Books.mdb Database The data source must be registered with system. Go to Control Panel -> ODBC Data Source Administrator.

17. Statement Class Kelas ini biasa digunakan untuk query standar tanpa parameter: Contoh: String url=”jdbc:odbc:Kopi”; String userID=”sa”; String password=””; Statement DataRequest; ResultSet Results; Connection Db; try { String query=”select * from customers”; DataRequest =Db.createStatement(); Results=DataRequest.executeQuery(query); DataRequest.close();

18. Statement for SELECT Query • For Forward Only and Read Only ResultSet • Statement stmf = con.createStatement(); • For Updateable ResultSet (JDBC 2.0) • Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

19. ResultSet Record Navigation • Record Navigation • MoveNext  next(); • MovePrev  previous(); • Last Record  afterLast(); isAfterLast() • First Record  beforeFirst(); isBeforeFirst() • Go to N position  absolute(n), relative(n) • Record Count  int recCount = rs.getRow();

20. Example try { //koneksi ke Ms.Access XP con = DriverManager.getConnection(url, "", ""); //mengizinkan cursor maju mundur //tidak sensitive terhadap perubahan oleh komponen J2EE lainnya //mengizinkan resultset dapat diupdate stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("Select * from mhs"); rs.first(); } catch(SQLException ex){ System.err.println("SQLException : " + ex.getMessage()); }

21. Navigation class Previous implements ActionListener { public void actionPerformed(ActionEvent e) { try{ if (!(rs.isBeforeFirst())) //jika belum di awal { rs.previous(); //ke data sebelumnya isi(); } if ((rs.isBeforeFirst()) && (rs != null)) { rs.first(); //Menampilkan pesan di status bar showStatus("Aduh udah mentok"); } } catch(SQLException ex) { System.err.println("SQLException : " + ex.getMessage()); }}}

22. Navigation class Last implements ActionListener { public void actionPerformed(ActionEvent e) { try { rs.last(); //ke data terakhir isi(); } catch(SQLException ex) { System.err.println("SQLException : " + ex.getMessage()); }}} }

23. PreparedStatement Class Sebuah query SQL dapat diprecompilasi dan dieksekusi menggunakan objek PreparedStatement. Tanda Tanya (?) dapat digunakan sebagai tempat untuk memasukkan nilai. try { String query=”Select * from Customers where CustNumber=?”; PreparedStatement pstatement=Db.preparedStatement(query); pstatement.setString(1,”123”); Results=pstatement.executeQuery(); pstatement.close();

24. CallableStatement Class Objek CallableStatement digunakan untuk memanggil stored prosedure dari objek java. Stored procedure ialah sekumpulan sintaks sql yang mempunyai 1 buah nama dan dapat dipanggil oleh program. Contoh : String ordernumber; try { String query=”{CALL StoredProcSaya (?)}”; CallableStatement cstatement =Db.prepareCall(query); sstatement.registerOutParameter(1,Types.VARCHAR(); sstatement.execute(); ordernumber=cstatement.getString(1); cstatement.close();

25. Displaying database in Applet At c:\j2se\jre\lib\security\java.policy, please add 2 lines: grant{ permission java.lang.RuntimePermission "accessClassInPackage.sun.jdbc.odbc"; permission java.util.PropertyPermission "file.encoding", "read"; };

26. First Sample • Perform query on Books.mdb database • Connect to database • Query to Table “Authors” • Display results

27. First Sample • Authors table • Four fields • AuthorID - ID number • FirstName • LastName • YearBorn

28. 1 // Fig. 18.24: TableDisplay.java 2 // This program displays the contents of the Authors table Import the sql package. 3 // in the Books database. 4import java.sql.*; Specify url, username, and password. The database has password protection (next section). 5 import javax.swing.*; 6 import java.awt.*; 7 import java.awt.event.*; 8 import java.util.*; Load class definition for database driver (static method Class.forName). 9 Attempt to connect to database. Use static method getConnection, of class DriverManager (java.sql). 10 public class TableDisplay extends JFrame { 11 private Connection connection; 12 private JTable table; 13 14 public TableDisplay() 15 { 16 // The URL specifying the Books database to which 17 // this program connects using JDBC to connect to a 18 // Microsoft ODBC database. 19 String url = "jdbc:odbc:Books"; 20 String username = "anonymous"; 21 String password = "guest"; 22 23 // Load the driver to allow connection to the database 24 try { 25 Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); 26 27 connection = DriverManager.getConnection( 28 url, username, password );

29. 29 } 30 catch ( ClassNotFoundException cnfex ) { 31 System.err.println( 32 "Failed to load JDBC/ODBC driver." ); 33 cnfex.printStackTrace(); 34 System.exit( 1 ); // terminate program 35 } 36 catch ( SQLException sqlex ) { 37 System.err.println( "Unable to connect" ); Create a Statement object that will query the database. 38 sqlex.printStackTrace(); Returns a ResultSet object containing results. 39 } 40 41 getTable(); 42 43 setSize( 450, 150 ); 44 show(); 45 } 46 47 private void getTable() 48 { 49 Statement statement; 50 ResultSet resultSet; 51 52 try { 53 String query = "SELECT * FROM Authors"; 54 55 statement = connection.createStatement(); 56 resultSet = statement.executeQuery( query );

30. 57 displayResultSet( resultSet ); statement closed when not needed. 58 statement.close(); 59 } 60 catch ( SQLException sqlex ) { 61 sqlex.printStackTrace(); Positions to first record in ResultSet (initially before first record). 62 } 63 } Create new Vectors, similar to dynamic arrays. 64 65 private void displayResultSet( ResultSet rs ) Get meta data, which describes contents of ResultSet. 66 throws SQLException 67 { 68 // position to first record 69 boolean moreRecords = rs.next(); 70 71 // If there are no records, display a message 72 if ( ! moreRecords ) { 73 JOptionPane.showMessageDialog( this, 74 "ResultSet contained no records" ); 75 setTitle( "No records to display" ); 76 return; 77 } 78 79 setTitle( "Authors table from Books" ); 80 81 Vector columnHeads = new Vector(); 82 Vector rows = new Vector(); 83 84 try { 85 // get column heads 86 ResultSetMetaData rsmd = rs.getMetaData();

31. 87 88 for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) Get names of column heads, add to Vector. 89 columnHeads.addElement( rsmd.getColumnName( i ) ); 90 Utility method getNextRow returns a Vector with row data. Creates a Vector of Vectors (like double scripted array). Create a JTable, takes Vector of Vectors and Vector of column heads. 91 // get row data 92 do { 93 rows.addElement( getNextRow( rs, rsmd ) ); 94 } while ( rs.next() ); Create Vector to hold one row of data. 95 96 // display table with ResultSet contents 97 table = new JTable( rows, columnHeads ); 98 JScrollPane scroller = new JScrollPane( table ); 99 getContentPane().add( 100 scroller, BorderLayout.CENTER ); 101 validate(); 102 } 103 catch ( SQLException sqlex ) { 104 sqlex.printStackTrace(); 105 } 106 } 107 108 private Vector getNextRow( ResultSet rs, 109 ResultSetMetaData rsmd ) 110 throws SQLException 111 { 112 Vector currentRow = new Vector(); 113

32. 114 for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) 115 switch( rsmd.getColumnType( i ) ) { 116 case Types.VARCHAR: Test for column type, add appropriate type of element to Vector. 117 currentRow.addElement( rs.getString( i ) ); 118 break; 119 case Types.INTEGER: 120 currentRow.addElement( 121 new Long( rs.getLong( i ) ) ); 122 break; 123 default: 124 System.out.println( "Type was: " + 125 rsmd.getColumnTypeName( i ) ); 126 } 127 128 return currentRow; 129 } 130 131 public void shutDown() 132 { 133 try { 134 connection.close(); 135 } 136 catch ( SQLException sqlex ) { 137 System.err.println( "Unable to disconnect" ); 138 sqlex.printStackTrace(); 139 } 140 } 141

33. 142 public static void main( String args[] ) 143 { 144 final TableDisplay app = new TableDisplay(); 145 146 app.addWindowListener( 147 new WindowAdapter() { 148 public void windowClosing( WindowEvent e ) 149 { 150 app.shutDown(); 151 System.exit( 0 ); 152 } 153 } 154 ); 155 } 156 }

34. Update Query • For INSERT, DELETE, UPDATE use: • stmt.executeUpdate(“DELETE from Authors where YearBorn=1946”); • Bacth Update (JDBC 2.0): Statement stmt = con.createStatement(); con.setAutoCommit(false); stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')"); stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')"); int [] updateCounts = stmt.executeBatch();

35. Using Transaction • Transaction processing • Changes can be undone • Interface Connection • Method setAutoCommit • true - each SQL statements performed individually • false - several statements grouped as a transaction • Terminating Statement that executes SQL statements • Method commit - commit changes to database • Method rollback - return database to previous state • Method getAutoCommit • Returns auto commit state

36. Using Transaction con.setAutoCommit(false); PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?"); updateSales.setInt(1, 50); updateSales.setString(2, "Colombian"); updateSales.executeUpdate(); PreparedStatement updateTotal = con.prepareStatement( "UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?"); updateTotal.setInt(1, 50); updateTotal.setString(2, "Colombian"); updateTotal.executeUpdate(); con.commit(); con.setAutoCommit(true);

37. Database Error Processing catch (SQLException se) { do { System.out.println (“SQL Error:” + se.getMessage() + “Code:” + se.getErrorCode() + “ SQL State::” + se.getSQLState()); se.getNextException(); } while (se!null); }

38. References www.java.sun.com/jdbc Deithel, “Java How To Program”, 5th ed, 2006 Widodo Budiharto, “Panduan Lengkap Pemrograman J2EE”, Andi Offset Yogyakarta, 2006 www.apache.org www.struts.org www.netbeans.org