Setting Access Database

JAVA & DATABASE
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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.
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC Driver
1.
2.
3.
4.
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.
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC Architecture
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC API for Database
• Interfaces :
–
–
–
–
–
–
–
Connection
Statement
Result set
Prepared statement
Result set Meta data
Callable statement
D/B Meta data
DEPARTMENT OF ELECTRICAL ENGINEERING
• Classes :
–
–
–
–
–
–
–
Driver manager
Date Time
Types
Date
Driver property
information
Time stamp
GADJAHMADA UNIVERSITY
JDBC
• Ada 7 langkah untuk mengakses
database:
1. Load Driver JDBC
2. Mendefinisikan Koneksi URL yang dipakai
3. Menghubungkan Koneksi tsb.
4. Menciptakan statement object
5. Mengeksekusi query atau update
6. Memproses Hasil
7. Memutuskan Koneksi
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC
try {
con =
DriverManager.getConnection("jdbc:odbc:Datab
ase1");//,"root",""); // step 2 and 3
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC
stmt = con.createStatement(); // step 4
rs = stmt.executeQuery("SQL Query'"); // step 5
while (rs.next()) { // step 6
:
:
} // while
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC
stmt.close(); // step7
con.close();
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Access Database
• Microsoft Acess Database : db1
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Setting Access Database
• Menset Database yang dipakai sebagai acuan dalam
Program
• Start -> Control Panel -> ODBC Data Source
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Setting Access Database
• Click OK
• Data base db1 siap diakses oleh Program
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC Program
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.Jd
bcOdbcDriver"); // step1
} catch (ClassNotFoundException
cnfe) {
System.err.println(cnfe);
} catch (Exception E) {
E.printStackTrace();
} // end catch
DEPARTMENT
OF ELECTRICAL ENGINEERING
try {
conn =
DriverManager.getConnection("jdbc:odbc:bukuJDB
C");
// 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
}
}}
GADJAHMADA UNIVERSITY
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
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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.
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Mysql Database
• Mysql dapat
didownload di :
www.mysql.com/do
wnloads
• Extract dan
lakukan instalasi
dengan memilih:
Setup.exe
• Mysql terletak di
C:\mysql
• Aktifkan mysql
admin dengan
double click di :
C:\mysql\bin\winmysql
admin
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Mysql Database
• Inilah tampilannya
• Atau tampak di pojok kanan bawah
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Mysql Database
• Mysql database : test002
• Tabel : tabel02
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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 mysqlconnector-java, click Add...
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Java to mysql
• Pilih subdirectory C:\mysql-connector-java-..., di
mana JDBC driver untuk mysql diletakkan, click OK
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Java to mysql
• Library yang
baru akan
tampak seperti
pada gambar,
click OK
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
JDBC Program
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.Drive
r"); // Step 1
} catch (ClassNotFoundException cnfe)
{
System.err.println(cnfe);
} catch (Exception E) {
E.printStackTrace();
} // end catch
DEPARTMENT
OF ELECTRICAL ENGINEERING
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
}
}
GADJAHMADA UNIVERSITY
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
3
J2EE Unleashed Bambara Allen Sams 600000 Oke
•
Bandingkan
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Tambahan
con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/pasiendrerit
ta","root","");
stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO catatanpasien VALUES('"+
navn[0] +"','"+ navn[1] +"','"+ navn[2] +"','"+ navn[3] +"','"+ navn[4] +"','"+
navn[5] +"');");
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Mysql tools
• Dbtools
• Mysql-front
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
Java to mysql
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY
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
DEPARTMENT OF ELECTRICAL ENGINEERING
GADJAHMADA UNIVERSITY