1. java database connectivity (jdbc)

1. java database connectivity (jdbc)

• 1.
  2.1 JAVA DATABASE CONNECTIVITY(JDBC)
• 2.
  2.1.1 JDBC ANDODBC DATABASE ACCESS Java Database Connectivity (JDBC) Open Database Connectivity (ODBC) • is the trademark name of a Java API that supports java program that access relational databases. • Using JDBC you can send SQL, PL/SQL statements to almost any relational databases. • JDBC is a Java API for executing SQL statements and supports basic SQL functionality. • It provides methods for querying and updating data in a database. • is a standard programming language middleware API for accessing database management systems (DBMS). • The designers of ODBC aimed to make it independent of database systems and operating systems. • An application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.
• 3.
  2.1.2 JDBC DRIVERTYPES JDBC technology drivers fit into one of four categories. 1) JDBC-ODBC bridge 2) Native-API Driver 3) Network-Protocol Driver(MiddleWare Driver) 4) Database-Protocol Driver(Pure Java Driver)
• 4.
  Type 1 :JDBC- ODBC bridge Type 2 : Native-API Driver Type 3 : Network- Protocol Driver (MiddleWare Driver) Type 4 : Database- Protocol Driver (Pure Java Driver) • used to access ODBC drivers installed on each client machine. • Using ODBC requires configuring on your system a Data Source Name (DSN) that represents the target database. • JDBC API calls are converted into native C/C++ API calls which are unique to the database. • . These drivers typically provided by the database vendors and used in the same manner as the JDBC-ODBC Bridge, the vendor-specific driver must be • a three-tier approach is used to accessing databases. • The JDBC clients use standard network sockets to communicate with an middleware application server. • communicates directly with vendor's database through socket connection. • This is the highest performance driver available for the database and is usually provided by the vendor itself.
• 5.
  2.1.3 JDBC STATEMENTOBJECTS INTERFACES RECOMMENDED USE Statement Use for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters. Prepare Statement Use when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime. Callable Statement Use when you want to access database stored procedures. The CallableStatement interface can also accept runtime input parameters.
• 6.
  2.1.4 STEP INVOLVEIN JDBC a) Load the driver b) Define the connection c) Establish the connection d) Create a statement object e) Execute a query f) Process the result g) Close the connection
• 7.
  1. Loading Drivers Class.forName(“JDBCDriverClass”); DatabaseDriver Class Source Ms Access sun.jdbc.odbc.JdbcOdbcDriver Already in JDK MySQL com.mysql.jdbc.Driver Companion Website Oracle oracle.jdbc.driver.OracleDriver Companion Website
• 8.
  2. Establishing connections Connectioncon = DriverManager.getConnection(databaseURL); Database URL Pattern Example Ms Access jdbc:odbc:dataSource Connection connection = DriverManager.getConnection(“jdbc:od bc:DatabaseSaya”); MySQL jdbc:mysql://hostname/dbname Connection connection = DriverManager.getConnection(“jdbc:m ysql://localhost/Data baseSaya”); Oracle jdbc:oracle:thin:@hostname: port#:oracleDBSID Connection connection = DriverManager.getConnection(“jdbc:or acle:thin:@hawariyah. psp.edu.my:1521:ora91”,”hawariyah”,” psp”);
• 9.
  3. Creating Statements Statementstmt = con.createStatement(); 4. Executing statements //untuk create table baru/update data dalam table stmt.executeUpdate(“SQL statement”); //utk papar data dalam table stmt.executeQuery(“SQL statement”); Example : ResultSet rs = stmt.executeQuery (“SELECT firstname,lastname FROM student WHERE lastname” + “= ‘smith’ “);
• 10.
  5. Process theresult // Iterate through the result and print the student names while (rs.next()) System.out.println (rs.getString(1) + “ “ + rs.getString(2) + “ “ + rs.getString(3)); 6. Close connection to database Close the ResultSet object  rs.close(); Close the Statement object  stmt.close(); Close the connection  conn.close();