Db

Agenda • Overview of JDBC technology • JDBC drivers • Seven basic steps in using JDBC • Example of Retrieving data from ResultSet • Example of Executing DML statements • Using prepared Statement

1

JDBC

JDBC Introduction • JDBC provides a standard library for accessing relational databases – API standardizes • Way to establish connection to database • Approach to initiate queries • Method to create stored (parameterized) queries • The data structure of query result (table) – Determining the number of columns – Looking up metadata, etc. 2

JDBC

JDBC Introduction – API does not standardize SQL syntax • JDBC is not embedded SQL

– JDBC classes located in java.sql package

3

JDBC

Connecting Microsoft Access Example • Create PersonInfo database • Create Person table

4

JDBC

Connecting MS Access Example: Setup System DSN • settingsControlPanelAdministrativeTools data sources(ODBC)

5

JDBC

Basic Steps in Using JDBC 2. Import required packages 4. Load driver 6. Define Connection URL 8. Establish Connection 10. Create a Statement object 6

JDBC

Basic Steps in Using JDBC (cont.) 2. Execute query / DML 4. Process results 6. Close connection

7

JDBC

JDBC: Details of Process 2. Import package  Import java.sql package  import java.sql.*;

8

JDBC

JDBC: Details of Process 1. Loading driver  Need to load suitable driver for underlying database  Different drivers for different databases are available 

For MS Access Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver");



For Oracle Class.forName(“oracle.jdbc.driver.OracleDriver ");

9

JDBC

JDBC: Details of Process 1. Define Connection URL • To get a connection, we need to specify URL of database. • If you are using a JDBC-ODBC driver you need to create a DSN. DSN is the name of your DataSource • If the name of your DSN is “personDSN” then the url of the database will be • String conURL = “jdbc:odbc:personDSN” 10

JDBC

JDBC: Details of Process, cont. 2. Establish Connection • Connection con = null; • Use driver manager to get the connection object con = DriverManager.getConnection(conURL);

– If the Db requires username and password you can use overloaded version • • •

String usr = “mudassar"; String pswd = “12345678"; Connection con = null;

con = 11

JDBC

DriverManager.getConnection(conURL,usr,pswd);

JDBC: Details of Process, cont. 2. Create Statement 

A statement is obtained from a Connection object. Statement statement = con.createStatement();



12

JDBC

Once you have a statement, you can use it for various kind of SQL queries

JDBC: Details of Process, cont. 6(a) Execute Query / DML –

executeQuery(sql) method 

Used for SQL SELECT queries



Returns the ResultSet object which is used to access the rows of the query results String sql = "SELECT * FROM sometable"; ResultSet rs = statement.executeQuery(sql);

13

JDBC

JDBC: Details of Process, cont. 6(b) Execute Query / DML –

executeUpdate(sql) method 

Used for an update statement ( INSERT, UPDATE or DELETE)



Returns an integer value representing the number of rows updated. String sql = “INSERT INTO tableName “ + “(columnNames) Values (values)”; int count = statement.executeUpdate(sql);

14

JDBC

JDBC: Details of Process, cont. 2. Process Results – ResultSet provides various getXxx methods that take a column index or name and returns the data – First column has index 1, not 0 while(resultSet.next()) { //by using column name String name = rs.getString(“columnName”);

} 15

JDBC

//or by using index String name = rs.getString(1);

JDBC: Details of Process, cont. 2. Close Connection connection.close();

–

16

JDBC

As opening a connection is expensive, postpone this step if additional database operations are expected

In a nut shell • Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); • Connection con = null; con = DriverManager.getConnection(url, usr, pwd); • Statement st = con.createStatement(); • ResultSet rs = st.exectuteQuery(“Select * from Person” );

17

JDBC

JDBC Architecture 3

1

Driver Manager

creates

Connection

creates

2 Establish Link To DB

6 Statement

SQL

creates

ResultSet

4

Data

Driver

Database

18

JDBC

5

Example Code 14.1

Retrieving Data from ResultSet //Step 1: import package import java.sql.*; public class JdbcEx { public static void main (String args [ ]){ try { //Step 2: Load driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Step 3: Defie the connection URL String url = "jdbc:odbc:personDSN"; //Step 4: Establish the connection Connection con = null; con = DriverManager.getConnection (url , "“ ,""); 19

JDBC

Example Code 14.1

Retrieving Data from ResultSet (cont.) //Step 5: create the statement Statement st = con.createStatement(); //Step 6: Execute the query String sql = "SELECT * FROM Person"; ResultSet rs = st.executeQuery(sql); //Step 7: Process the results while ( rs.next() ) { String name = rs.getString("name"); String add = rs.getString("address"); String pNum = rs.getString("phoneNum"); System.out.println(name + " " +add +" "+pNum); } // end while 20

JDBC

Example Code 14.1

Retrieving Data from ResultSet (cont.) //Step 8: close the connection

con.close();

}catch (Exception sqlEx) { System.out.println(sqlEx); } } //end main }//end class

21

JDBC

Compile & Execute

22

JDBC