Introduction to JDBC for Database Access in Java

1. JAVA & DATABASE

2. JDBC Introduction • JDBC is basically used because it can access any database. • JDBC uses drivers to connect to the database, which are categorized into four types. • Jdbc has a set of exclusive interfaces and classes. • Few steps to perform JDBC application.

3. JDBC Driver • Jdbc-Odbc bridge driver : This bridge driver can be used to connect to any existing database, I.e, ODBC compliant. • Native-API/partly Java driver : Converts JDBC calls into database specific calls for database such as SQl , Oracle etc. Driver is partly written in “Java” and partly in “C” . • Net-protocol/all-Java driver : This driver is used in Internet to connect to the database at different locations. This is supposed to be the most flexible driver. • Native-protocol/all-Java driver : Converts the JDBC calls into the network protocol used by DBMS’s directly. This is very much used in intranet access.

4. JDBC Architecture

5. Interfaces : Connection Statement Result set Prepared statement Result set Meta data Callable statement D/B Meta data Classes : Driver manager Date Time Types Date Driver property information Time stamp JDBC API for Database

6. JDBC • Ada 7 langkah untuk mengakses database: • Load Driver JDBC • Mendefinisikan Koneksi URL yang dipakai • Menghubungkan Koneksi tsb. • Menciptakan statement object • Mengeksekusi query atau update • Memproses Hasil • Memutuskan Koneksi

7. JDBC try { // Class.forName("org.gjt.mm.mysql.Driver"); // Step 1 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // step1 } catch (Exception E) { E.printStackTrace(); } // end catch

8. JDBC try { con = DriverManager.getConnection("jdbc:odbc:Database1");//,"root",""); // step 2 and 3

9. JDBC stmt = con.createStatement(); // step 4 rs = stmt.executeQuery("SQL Query'"); // step 5 while (rs.next()) { // step 6 : : } // while

10. JDBC stmt.close(); // step7 con.close();

11. Access Database • Microsoft Acess Database : db1

12. Setting Access Database • Menset Database yang dipakai sebagai acuan dalam Program • Start -> Control Panel -> ODBC Data Source

13. Setting Access Database • Tampil jendela ODBC Data Source Administrator • Click System DSN-> Add-> pilih Driver (Microsoft Access Driver) -> Finish. Dalam hal ini kita memakai Access Database. • Tentukan Letak File Database yang digunakan

14. Setting Access Database • Click OK • Data base db1 siap diakses oleh Program

15. JDBC Program try { conn = DriverManager.getConnection("jdbc:odbc:bukuJDBC"); // step 2 and 3 stmt = conn.createStatement(); // step 4 rs = stmt.executeQuery("SELECT * FROM buku"); while (rs.next()) { // step 6 System.out.print(rs.getString("No") + " "); System.out.print(rs.getString("Judul") + " "); System.out.print(rs.getString("Pengarang") + " "); System.out.print(rs.getString("Penerbit") + " "); System.out.print(rs.getString("harga") + " "); System.out.println(""); } // while stmt.close(); conn.close(); } catch(SQLException sqle) { System.err.println("SQLException : " + sqle.getMessage()); } // end cacth } }} package jdbc; import java.sql.*; public class JDBCApp { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // step1 } catch (ClassNotFoundException cnfe) { System.err.println(cnfe); } catch (Exception E) { E.printStackTrace(); } // end catch

16. JDBC Program • Hasil Program 1 Java 2 Complete Reference Patrick Naughton McGraw Hill 600000 2 Distributed Systems Tanenbaum Prentice Hall 500000 3 Home Networking Bible Plumley IDG Books 375000 • Bandingkan

17. JDBC Program dengan Access • Setting perlu dilakukan pada Control Panel, untuk menset Database yang dipakai • Pada Java Program tidak diperlukan setting apapun • Sun telah menyediakan driver secara otomatis untuk Access • Hal ini tidak berlaku untuk Sistem Database yang lain, misal: mysql, oracle, dll.

18. Mysql Database • Mysql dapat didownload di : www.mysql.com/downloads • Extract dan lakukan instalasi dengan memilih: Setup.exe • Mysql terletak di C:\mysql • Aktifkan mysql admin dengan double click di : C:\mysql\bin\winmysqladmin

19. Mysql Database • Inilah tampilannya • Atau tampak di pojok kanan bawah

20. Mysql Database • Mysql database : test002 • Tabel : tabel02

21. Java to mysql • JDBC Driver for MySQL dapat di-download di : www.mysql.com/downloads dengan nama : MySQL Connector/J 2.0.14 • Extract ke C:\ • JDBC Driver akan terletak di subdirectory C:\mysql-connector-java-2.0.14

22. Java to mysql • Dengan JBUILDER, buat program (class) Java misalnya : JDBCTest01.java • Project -> Project Properties -> Path -> Required Libraries -> Add... -> New, tuliskan nama library-nya, misal mysql-connector-java, click Add...

23. Java to mysql • Pilih subdirectory C:\mysql-connector-java-..., di mana JDBC driver untuk mysql diletakkan, click OK

24. Java to mysql • Library yang baru akan tampak seperti pada gambar, click OK

25. JDBC Program try { conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test002","rsn","risanuri"); stmt = conn.createStatement(); // step 4 rs = stmt.executeQuery("SELECT * FROM tabel02"); while (rs.next()) { // step 6 System.out.print(rs.getString("Nomer") + " "); System.out.print(rs.getString("Judul") + " "); System.out.print(rs.getString("Pengarang") + " "); System.out.print(rs.getString("Penerbit") + " "); System.out.print(rs.getString("harga") + " "); System.out.println("Oke"); } // while stmt.close(); conn.close(); } catch(SQLException sqle) { System.err.println("SQLException : " + sqle.getMessage()); } // end cacth } } package jdbc01; import java.sql.*; public class JDBCTest01 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("org.gjt.mm.mysql.Driver"); // Step 1 } catch (ClassNotFoundException cnfe) { System.err.println(cnfe); } catch (Exception E) { E.printStackTrace(); } // end catch

26. Hasil JDBC mysql 1 Core Servlets and JSP Marty Hall Prentice Hall 350000 Oke 2 Java 2 Complete Reference Patrick Naughton McGraw Hill 600000 Oke • J2EE Unleashed Bambara Allen Sams 600000 Oke • Bandingkan

27. JDBC Program dengan mysql • Setting tidak perlu dilakukan pada Control Panel • Perlu dilakukan setting pada Java Program • Driver JDBC disediakan oleh mysql dan harus di-download secara terpisah • Mysql memungkinkan memanggil Database lewat jaringan

28. Tambahan con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pasiendreritta","root",""); stmt = con.createStatement(); stmt.executeUpdate("INSERT INTO catatanpasien VALUES('"+ navn[0] +"','"+ navn[1] +"','"+ navn[2] +"','"+ navn[3] +"','"+ navn[4] +"','"+ navn[5] +"');");

29. Mysql tools • Dbtools • Mysql-front

30. Java to mysql • Setting Classpath, prosesnya sama dengan setting classpath yang lain • Pada Win98, Start ->Run (sysedit), kemudian pada autoexec.bat tambahkan C:\mysql-connector-java-2.0.14, yang merupakan letak subdir JDBC Driver

31. Java to mysql

32. Java to mysql • Compile • myjava>javac JDBCTest01.java • Buat subdir jdbc01 di dalam :\myjava • Letakkan JDBCTest01.class pada subdir :\myjava\jdbc01 • Eksekusi • \myjava>java jdbc01.JDBCTest01 1 Routing TCP/IP Jeff Doyle null 900000 Oke 2 Top Down Network Design Oppenheimer 0 600000 Oke 3 Java Security Jess Gams 0 500000 Oke