Database

1. Database By: Greg Java 9 - GUI

2. JTable • The JTable is used to display and edit regular two-dimensional tables of cells. • Use DefaultTableModel object to manipulate data • Import: • javax.swing.table.DefaultTableModel; Java 9 - GUI

3. Database (JDBC) • Java programs communicate with databases and manipulate their data using the JDBC™API. • A JDBC driver enables Java applications to connect to a database in a DBMS and manipulate it using the JDBC API. • JDBC is almost always used with a relational database. • However, it can be used with any table-based data source. Java 9 - GUI

4. Manipulating Databases • Manipulating Databases with JDBC • JDBC supports four categories of drivers: • JDBC-to-ODBC bridge driver (Type 1) • connects Java programs to Microsoft ODBC (Open Database Connectivity) data sources. • Windows 64bit ?? • Native-API, partly Java driver (Type 2) • almost similar to ODBC • Pure Java client to server driver (Type 3) • take JDBC requests and translate them into a network protocol that is not database specific. The requests are sent to a server, which translates it into a database-specific protocol. • Pure Java driver (Type 4) • implement database-specific network protocols, so that Java programs can connect directly to a database. Java 9 - GUI

5. Java driver Java 9 - GUI

6. JavaDB (Java/Netbean Database)ApacheDerby • Library: • derbyclient.jar (client-server) • derby.jar (direct connection) • default location: • C:\Program Files\Java\jdk1.7.0_25\db\lib • Add the library to the project before we used derby database server Java 9 - GUI

7. Create Connection • Import • java.sql.Connection • java.sql.DriverManager • java.sql.SQLException • Example: Connection cn = null; //String nmClass = "sun.jdbc.odbc.JdbcOdbcDriver"; //String cs = "jdbc:odbc:Data Barang"; String nmClass = "org.apache.derby.jdbc.ClientDriver"; String cs = "jdbc:derby://localhost:1527/Siswa SDN"; try { Class.forName(nmClass); cn = DriverManager.getConnection(cs,”app","admin"); … … } catch(ClassNotFoundException cnfe){…} catch(SQLException ex) {…} Java 9 - GUI

8. Create Connection(direct connection) • Import • java.sql.Connection • java.sql.DriverManager • java.sql.SQLException • Example: Connection cn = null; String nmClass = "org.apache.derby.jdbc.EmbeddedDriver"; String cs = "jdbc:derby:c:/java2/Siswa SDN"; try { Class.forName(nmClass); cn = DriverManager.getConnection(cs,”app","admin"); … … } catch(ClassNotFoundException cnfe){…} catch(SQLException ex) {…} Java 9 - GUI

9. Close Connection • Import • java.sql.Connection • java.sql.DriverManager • java.sql.SQLException • Example: … … try{ if(cn != null) cn.close(); } catch(SQLException ex){…} Java 9 - GUI

10. Create a statement • The program uses the Statement object to submit SQL to the database. • Import • java.sql.Statement • java.sql.SQLException • Example: Statement stmt = null; … try{ … … // stmt = cn.createStatement(); type scroll is TYPE_FORWARD_ONLY stmt = cn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); … … } catch(SQLException ex){…} Java 9 - GUI

11. Execute Query • Insert, Update, Delete Query • Import • java.sql.SQLException • Example: try{ … String qry = “…”; stmt = cn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); stmt. execute(qry); //stmt.executeUpdate(qry); … } catch(SQLException ex){…} Java 9 - GUI

12. Execute Query • Select / Join Query • Import • java.sql.ResultSet • java.sql.SQLException • Example: Statement stmt = null; private ResultSet rs; try{ … String qry = “…”; stmt = cn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); rs = stmt.executeQuery(qry); … } catch(SQLException ex){…} Java 9 - GUI

13. Query Examples • Query Statement insert into datasiswa (nama,nrp, aktif, nilai1, nilai2, nilai3) values (‘Paijo’, ‘34311’, ‘Ya’, 60, 70, 85) • In the code: String nma = “Paijo”; String nrp = “34311”; String akt = “Ya”; int nil1 = 60, nil2 = 70, nil3 = 85; String qry = "insert into datasiswa " + "(nama,nrp, aktif, nilai1, nilai2, nilai3) "+ "values (‘”+ nma +”’, ‘”+ nrp + “’, ‘”+ akt +”’, “+ nil1 +”, “+ nil2 +”, “+ nil3+”)”; Java 9 - GUI

14. Query Examples • Query Statement update datasiswa set aktif = ‘Tidak’, nilai1 = 60, nilai2 = 70, nilai3 = 85 where nama = ‘Paijo’ and nrp = ‘34311’ • In the code: String name = “Paijo”; String id = “34311”; String active = “Tidak”; int score1 = 60, score2 = 70, score3 = 85; String qry = “update datasiswa set aktif = ‘”+ active + “’, nilai1 = “ + score1 + “, nilai2 = “ + score2 + “, nilai3 = “ + score3 +” where “ + “nama = ‘” + name +”’ and nrp = ‘” + id + “’”; Java 9 - GUI

15. Query Examples • Query Statement delete from datasiswa where nama = ‘Paijo’ and nrp = ‘34311’ • In the code: String name = “Paijo”; String id = “34311”; String qry = “delete from datasiswa “+ “where nama = ‘” + name + “’ ” + “and nrp = ‘” + id + “’ ”; Java 9 - GUI

16. Query Examples • Query Statement Select * from datasiswa where nama = ‘Paijo’ and nrp = ‘34311’ • In the code: String name = “Paijo”; String id = “34311”; String qry = “Select * from datasiswa “ + “where nama = ‘” + name + “’ and nrp = ‘” + id + “’”; Java 9 - GUI

17. Get the query result rs = stmt.executeQuery("Select * from datasiswa"); int i = 0; while(rs.next()){ name[i]= rs.getString("NAMA"); id[i]= rs.getString(2); activ[i]= rs.getString("AKTIF"); score1[i]= rs.getDouble(4); score2[i]= rs.getDouble("NILAI2"); score3[i]= rs.getDouble(6); i++; } Java 9 - GUI

18. Count, AVG, Sum, Max, Min of the records Example: //Count record dalam datasiswa rs = stmt.executeQuery("Select count(*) from datasiswa"); rs.next(); int a = rs.getInt(1); //Hitung average dari field nilai1 untuk semua record rs = stmt.executeQuery("Select avg(nilai1) from datasiswa"); rs.next(); float b = rs.getFloat(1); Java 9 - GUI

19. Join 2 Table Example: rs = stmt.executeQuery( “SELECT idbarang,namabarang,namajenisbarang,” + “masterbarang.deskripsi,kuantitas,harga " + “FROM masterbarang, jenisbarang “ + “WHERE masterbarang.idjenisbarang = “ + “jenisbarang.idjenisbarang”); Java 9 - GUI