introduction to jdbc
DESCRIPTION
Introduction to JDBC. Instructor: Mohamed Eltabakh [email protected]. Ways to Access DB. PL/SQL. Direct SQL. External language connected to DB. What is JDBC. JDBC: Java Database Connectivity JDBC is a standard interface for connecting to relational databases from Java. Java code. - PowerPoint PPT PresentationTRANSCRIPT
![Page 2: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/2.jpg)
Ways to Access DB
2
Direct SQL
PL/SQL
External language connected to DB
![Page 3: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/3.jpg)
What is JDBC
JDBC: Java Database Connectivity
JDBC is a standard interface for connecting to relational databases from Java
3
Java codeDB
![Page 4: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/4.jpg)
4
Overview of Querying a Database With JDBC
![Page 5: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/5.jpg)
Stage 1: Connect
5
![Page 6: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/6.jpg)
JDBC Driver
6
DatabaseDatabaseJDBCdriver
Java app DatabaseJDBC calls
Database commands
JDBCdriver
JDBCdriver
Inside the code, you make calls using JDBC APIs
Software module that translates JDBC calls to SQL commands
![Page 7: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/7.jpg)
JDBC Driver
Is an interpreter that translates JDBC method calls to vendor-specific database commands
Implements interfaces in java.sql
Can also provide a vendor’s extensions to the JDBC standard
7
![Page 8: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/8.jpg)
How to Make the Connection
8
1. Register the driver.
DriverManager.registerDriver (neworacle.jdbc.driver.OracleDriver());
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Or
![Page 9: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/9.jpg)
How to Make the Connection
9
2. Connect to the DB
Connection conn = DriverManager.getConnection
(URL, userid, password);
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@oracle.wpi.edu:1521:WPI11grxx",
"userid", "password");
In our Oracle DB
Your Oracle username and pw
![Page 10: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/10.jpg)
10
Import java.sql package
Register the driver
Establish connection
![Page 11: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/11.jpg)
Stage 2: Query the DB
11
![Page 12: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/12.jpg)
JDBC Statement Object
A Statement object sends your SQL command to the database
You need an active connection to create a JDBC statement
Statement has methods to execute a SQL statement: executeQuery() for QUERY statements
executeUpdate() for INSERT, UPDATE, DELETE
12
![Page 13: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/13.jpg)
How to Query the Database
13
Output relation Number of affected tuples
The query string
![Page 14: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/14.jpg)
Querying the Database: Example I
14
![Page 15: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/15.jpg)
Querying the Database: Example II
15
Execute a select statement
Statement stmt = conn.createStatement();String str = "SELECT * FROM users”;Resultset rset = stmt.executeQuery(str);
Build your SQL command in a separate string and then pass it for execution
![Page 16: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/16.jpg)
Stage 3: Process Results
16
![Page 17: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/17.jpg)
Resultset Object JDBC returns the results of a query in a
ResultSet object.
A ResultSet maintains a cursor pointing to its current row of data.
Use next() to step through the result set row by row.
getString(), getInt(), and so on assign each value to a Java variable.
17
![Page 18: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/18.jpg)
How to Process Results
18
![Page 19: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/19.jpg)
Example
19
Statement stmt = conn.createStatement();String str = "SELECT branch_name, amount FROM R”;Resultset rset = stmt.executeQuery(str);
While ( rset.next()) { String bName = rset.getString(“branch_name”); int amt = rset.getInt(“amount”);
… System.out.println(”Name:” + bName + ” Amount: ” + amt); }
![Page 20: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/20.jpg)
Getxxx Function over Resultset
20
And many more:http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html
![Page 21: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/21.jpg)
Stage 4: Closing
21
![Page 22: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/22.jpg)
How to Close
22
![Page 23: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/23.jpg)
JDBC PrepareStatement
If execute the statement multiple times Use a PrepareStatement object It is compiled once and used multiple times
PrepareStatement may contain variables Placeholder for actual values supplied at
execution time
23
![Page 24: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/24.jpg)
How to create PrepareStatement
24
“?” Is the placeholder for variables
![Page 25: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/25.jpg)
How to Execute
25
For SQL queries
For Insert/Update/ Delete
With each execution set the values and then execute…
![Page 26: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/26.jpg)
How to Connect to WPI Oracle1- Log in to CCC machine
2- Set environment variables> source /usr/local/bin/oraenv
3- Set CLASSPATH for java> export CLASSPATH=./:/usr/local/oracle11gr203/product/11.2.0/ db_1/jdbc/lib/ojdbc6.jar
4- Write your java code (say file name is OracleTest.java) and then compile it > Javac OracleTest.java
5- Run it > Java OracleTest
26
![Page 27: Introduction to JDBC](https://reader033.vdocuments.us/reader033/viewer/2022061511/56814015550346895dab5e78/html5/thumbnails/27.jpg)
Sources
Some links with examples
http://www.cs.ubc.ca/~ramesh/cpsc304/tutorial/JDBC/jdbc1.html
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-jdbc.html
27