java database connectivity · 2019-03-26 · •use jdbc = java database connectivity . why use...
TRANSCRIPT
Java DataBase Connectivity
Kainjan M. Sanghavi
Importance of Database
The data in a Java program vanishes when the program terminates
Data
But Most Data must be stored and maintained for years
Relational databases are used for this
SetUp from Java to maintain database
Simplest approach:
• Use JDBC = Java Database Connectivity
Why Use JDBC
Before JDBC what was
used ODBC
Written in ‘C’
Thus , Sun Microsystem
Uses JDBC
Not Platform Indepen
dent And Also Unsecur
ed
Java DataBase Connectivity
JDBC Is a Java API that is used to connect and query to the database
JDBC API
Java Application
JDBC Driver Database
JDBC API uses uses JDBC driver to connect to the database
API
Application Program Interface
Document that contains description of all features of a product or software
It represents classes and interfaces that program can follow to communicate with each other
API can be created for applications, operating system etc
JDBC Drivers
Software Component that enables Java Application to interact with database
• JDBC- ODBC Bridge Driver
• Native API- Driver ( Partially Java Driver)
• Network Protocol Driver
• Thin Driver
Types of Drivers
JDBC – ODBC Bridge
JDBC API
Java Application
JDBC – ODBC Bridge Driver
Vendor Database
Library
ODBC Driver
Client Machine
Database
Native API
JDBC API
Java Application
Native API Driver
Vendor Database
Library
Client Machine
Database
Network Protocol
JDBC API
Java Application
Network Protocol
Driver
Client Machine
Database Middle Ware
Server Side
Thin Driver
JDBC API
Java Application
Thin Driver
Client Machine
Database
Creating a DSN for MS Access
• Click: Start • Control Panel • Administrative Tools • Data Sources (ODBC) • User DSN • Add • MS Access Database • Name : Browse the path of your database file (mdb) -> For eg : C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb
Overview of Connecting Java Application with Database
Query
Close
Connect
Process results
Stage 1 : Connect
Query
Close
Connect
Process results
Register the driver
Connect to the database
Stage 1 : a. Register Driver
The forName( ) of class ‘ Class’ is used to register the driver class . This method dynamically load the driver class
import java.sql.*;
// Load the vendor specific driver Class.forName("oracle.jdbc.driver.OracleDriver");
OR sun.jdbc.odbc.JdbcOdbcDriver ( if Jdbc-odbc Driver) …….and many more drivers are possible
Stage 1 : b. Connect to database
The getConnection ( ) of class ‘ DriverManager’ is used to establish connection with database
Connection con = DriverManager.getConnection ( "jdbc:oracle:thin:@oracle-prod:1521:OPROD", username, passwd);
Connection conn = DriverManager.getConnection (URL, userid, password);
OR (jdbc:odbc:dsn , username,passwd ) ( if Jdbc-odbc Driver) …….and many more drivers are possible
Stage 2 : Query Database
Query
Close
Connect
Process results
Create a statement
Query the database
Stage 2 : a. Create a statement
A Statement object sends your SQL statement to the database.
You need an active connection to create a JDBC statement.
Statement has three methods to execute a SQL statement:
• executeQuery() for SELECT statements
• executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements
• execute() for either type of statement
Stage 2 : a. Create a statement
Create an empty statement object. ;
Stage 2 : b. Query the database
ResultSet rset = stmt.executeQuery(statement);
int count = stmt.executeUpdate(statement);
boolean isquery = stmt.execute(statement);
Querying Database Examples
String createLehigh = "Create table Lehigh " + "(SSN Integer not null, Name VARCHAR(32), " + "Marks Integer)"; stmt.executeUpdate(createLehigh);
Execute a create statement.
Execute a select statement.
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery
("select RENTAL_ID, STATUS from ACME_RENTALS");
Querying Database Examples
Statement stmt = conn.createStatement();
int rowcount = stmt.executeUpdate
("delete from ACME_RENTAL_ITEMS
where rental_id = 1011");
Execute a delete statement.
Execute a insert statement.
String insertLehigh = "Insert into Lehigh values“ + "(123456789,abc,100)"; stmt.executeUpdate(insertLehigh);
Stage 3 : Process Results
Query
Close
Connect
Process results
Step through Results
Assign results to Java variables
Stage 3 : Process Results
while (rset.next()) { … }
1. Step Through the results
Stage 3 : Assign values to Java variables
Use getXXX() to get each column value
Stage 3 : Process Results
String queryLehigh = "select * from Lehigh"; ResultSet rs = Stmt.executeQuery(queryLehigh); //What does this statement do? while (rs.next()) { int ssn = rs.getInt("SSN"); String name = rs.getString("NAME"); int marks = rs.getInt("MARKS"); }
Stage 4 : Close
Query
Close
Connect
Process results
Close the resultset
Close the statement
Close the connection
Stage 4 : Close
rset.close();
1. Close resultset
2. Close Statement
stmt.close();
3. Close Connection
conn.close();
Sample Program
import java.sql.*; class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //dynamic loading of driver Connection con = DriverManager.getConnection( “jdbc:odbc:KMS” ,"",""); Statement s = con.createStatement(); s.execute("create table TEST12345 ( firstcolumn integer )"); s.execute("insert into TEST12345 values(1)"); s.execute("select firstcolumn from TEST12345");
Sample Program
ResultSet rs = s.getResultSet(); if (rs != null) // if rs == null, then there is no ResultSet to view while ( rs.next() ) // this will step through our data row-by-row { /* the next line will get the first column in our current row's ResultSet s a String ( getString( columnNumber) ) and output it to the screen */ System.out.println("Data from column_name: " + rs.getString(1) ); } s.close(); // close Statement con.close(); //close connection } catch (Exception err) { System.out.println("ERROR: " + err); } } }