Java Database Java Database Oleh : Agus Priyanto, M.Kom
SEKOLAH TINGGI TEKNOLOGI TELEMATIKA TELKOM Smart, Trustworthy, And Teamwork
Outline Materi 1. Koneksi Aplikasi Java ke Database 2. Studi Kasus Aplikasi Java Database
Koneksi Aplikasi Java ke D b Database
Tahapan Akses Database dengan JDBC Driver MySQL
PostgreSQL
Oracle
DriverManager Connection user
password
host
database
url
Statement select
update
create
ResultSet
insert
JJDBC (Java (J DB Connectivity) y) Java application { ... "SELECT ... FROM ... WHERE" ... }
DBMS
JDBC Drivers Java application pp
JDBC-API
JDBCDriver manager Native Protocol driver
JDBCNet-driver
JDBC-ODBC bridge
Native API-driver
DBMiddleware
ODBC
Client library
Client library
Running a JDBC Application Phase
Task
Initialisation
Load driver Create connection
Processing
Termination
Relevant java java.sql sql classes
DriverManager Connection
Generate SQL statements Process result data
Statement ResultSet etc.
Terminate connection Release data structures
Connection Statement etc.
A Simple p JJDBC Application pp loadDriver getConnection createStatement execute(SQL) Result handling yes More results ? no closeStatment closeConnection
import java.sql.*; public class jdbctest { public static void main(String args[]){ try{ Class.forName("org.postgresql.Driver"); Connection con = DriverManager.getConnection ("jdbc:postgresql://lsir-cis-pc8:5401/pcmdb", "user", "passwd"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery (("select name,, number from p pcmtable where number < 2"); ); while(rs.next()) System.out.println(rs.getString(1) + " (" + rs.getInt(2) + ")"); stmt.close() con close(); con.close(); } catch(Exception e){ System.err.println(e); } }}
Loading g of Driver Creates an instance of the driver Registers driver in the driver manager Explicit loading String l_driver l driver = "org.postgresql.Driver"; org.postgresql.Driver ; Class.forName(l_driver); Several drivers can be loaded and registered
Implicit Driver Loading Setting system property: jdbc.drivers
A colon-separated p list of driver classnames
Can be set when starting the application
java -Djdbc.drivers=org.postgresql.Driver Djdbc drivers=org postgresql Driver application
Can also be set from within the Java application Properties prp = System.getProperties(); P S P () prp.put("jdbc.drivers" "com.mimer.jdbc.Driver:org.postgresql.Driver"); y p (p p); System.setProperties(prp);
The DriverManager class attempts to load all the classes specified in jdbc.drivers jdbc drivers when the DriverManager class is initialized
Addressing g Database A connection is a session with one database Databases D t b are addressed dd d using i a URL off the th form f "jdbc:<subprotocol>:<subname>" Examples • jdbc:postgresql:database • jdbc:postgresql://host/database • jdbc:postgresql://host:port/database Defaults: host=localhost,, pport=5432
Connecting g to Database Connection is established Connection con = DriverManager.getConnection(URL,USERID,PWD);
Connection properties (class Properties) Close the connection con.close();
Simple p SQL Statements Statement object for invocation stmt = con.createStatement(); con createStatement(); ResultSet rset= stmt.executeQuery( "SELECT SELECT address,script,type FROM worklist"); ResultSet object for result processing
Studi Kasus Aplikasi Database
Aplikasi p Database 1. Aplikasi Telepon 2. Aplikasi p Guru 3 Aplikasi Bank 3. 4. Aplikasi Penjualan Barang
Aplikasi p Telepon p 1. Buka browser, arahkan url ke http://localhost dan klik link ke phpMyAdmin 2. Buat database telepon 3 Buat satu table bukutelepon, 3. bukutelepon yang berisi field dengan id sebagai primary key (PK): id nama alamat telepon l handphone
integer g (auto ( increment)) varchar(20) varchar(50) varchar(20) h (20) varchar(20)
Autoincrement di PostgreSQL CREATE SEQUENCE bukutelepon_id_seq; ALTER TABLE bukutelepon ALTER COLUMN id SET DEFAULT NEXTVAL('bukutelepon_id_seq'); UPDATE bukutelepon b k l SET id = NEXTVAL('bukutelepon_id_seq');
Cek Koneksi ke Database MySQL String user="root"; String pswd =""; p ; Stringg url="";; Stringg host="localhost";; Stringg db="telepon"; try { Class.forName("com.mysql.jdbc.Driver"); url="jdbc:mysql://"+ url jdbc:mysql:// host +"/"+ / db +"?user=" ?user + user + "&password="+ &password pswd; Connection conn=DriverManager.getConnection(urlValue); System.out.println("koneksi sukses"); conn close(); conn.close(); } catch (SQLException e){ System.out.println("koneksi gagal " + e.toString()); } catch(ClassNotFoundException t h(Cl N tF dE ti e)) { System.out.println("jdbc.Driver tidak ditemukan"); }
Cek Koneksi ke Database PostgreSQL String user="root"; String pswd =""; String host="localhost"; String db="telepon"; String url=""; try { Class.forName(“org.postgresql.Driver"); url="jdbc:postgresql://"+ host +"/"+ db +"?user=" + user + "&password="+ pswd; Connection conn=DriverManager.getConnection(urlValue); y p ( sukses"); ) System.out.println("koneksi conn.close(); } catch (SQLException e){ System.out.println("koneksi System.out.println( koneksi gagal " + e.toString()); } catch(ClassNotFoundException e) { System.out.println("jdbc.Driver tidak ditemukan"); }
Aplikasi Guru
Aplikasi Guru 1. Buat database Guru 2. Buat satu table dataguru, yang berisi field dengan nip sebagai primary key (PK). Field yang lain adalah seperti di bawah: 1. 1 2. 3. 4. 5. 6. 7. 8.
nip nama status institusi kota handphone p jeniskelamin bidangstudi
integer (auto increment) varchar(30) varchar(20) varchar(30) varchar(30) varchar(20) ( ) varchar(20) varchar(30)
3 Buat 4 class java yang melakukan query ke 3. database Guru: 1. 1 2. 3 3. 4. 5 5.
GuruConnection.java GuruConnection java GuruInsert.java GuruRead java GuruRead.java GuruUpdate.java GuruDelete java GuruDelete.java
3. Buat 1 class MenuUtama dan 4 class java GUI yang melakukan l k k query ke k database d b G Guru: 1. 2 2. 3. 4 4. 5.
GuruInsertUI.java G R dUI j GuruReadUI.java GuruUpdateUI.java G D l t UI j GuruDeleteUI.java MenuUtama.java
Terimakasih