JDBC Java Data Base Connectivity Accessing a Database

JDBC – Java Data. Base Connectivity Accessing a Database in Java

What is JDBC? l JDBC helps us to connect to a database and execute SQL statements against a database. JDBC API provides set of interfaces and there are different implementations respective to different databases l What’s an API? l l “An API that lets you access virtually any tabular data source from the Java programming language” What’s a tabular data source? l “… access virtually any data source, from relational databases to spreadsheets and flat files. ” 2

General Architecture 3

JDBC Architecture Oracle Driver Oracle Java Application JDBC DB 2 Driver DB 2 Network 4 We will use this one… My. SQL Driver My. SQL

What is JDBC Driver ? l l JDBC drivers implement the defined interfaces in the JDBC API for interacting with your database server. For example, using JDBC drivers enable you to open database connections and to interact with it by sending SQL or database commands then receiving results with Java. The Java. sql package that ships with JDK contains various classes with their behaviors defined and their actual implementations are done in third-party drivers. Third party vendors implements the java. sql. Driver interface in their database driver. A driver is nothing but software required to connect to a database from Java program.

JDBC Drivers Types: l JDBC driver implementations vary because of the wide variety of operating systems and hardware platforms in which Java operates. Sun has divided the implementation types into four categories Type 1: JDBC-ODBC Bridge l Type 2: Native-API/partly Java driver l Type 3: Net-protocol/all-Java driver l Type 4: Native-protocol/all-Java driver l

Two-tier and three-tier architecture l The JDBC API supports a two-tier and a three-tier architecture for database access. two-tier model three-tier model In a two-tier model, a Java application/applet communicates directly with the database, via the JDBC driver. The Java application/applet and the database can be on the same machine, or the database can be on a server and the Java application/applet can be on a client machine using any network protocol. In a three-tier model, i. e. client, server and database. It is a Java application /applet communicates with a middle tier component that functions as an application server. The application server talks to a given database using JDBC.

Type 1: JDBC-ODBC Bridge Driver l l In a Type 1 driver, a JDBC bridge is used to access ODBC(Object Database connectivity) drivers installed on each client machine. Using ODBC requires configuring on your system a Data Source Name (DSN) that represents the target database. When Java first came out, this was a useful driver because most databases only supported ODBC access but now this type of driver is recommended only for experimental use or when no other alternative is available. The JDBC-ODBC bridge that comes with JDK 1. 2 is a good example of this kind of driver.

Type 2: JDBC-Native API: l l l In a Type 2 driver, 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 installed on each client machine. If we change the Database we have to change the native API as it is specific to a database and they are mostly obsolete now but you may realize some speed increase with a Type 2 driver, because it eliminates ODBC's overhead. The Oracle Call Interface (OCI) driver is an example of a Type 2 driver.

Type 3: JDBC-Net pure Java: l l In a Type 3 driver, a three-tier approach i. e. client, server and database is used. The JDBC clients use standard network sockets to communicate with an middleware application server. The socket information is then translated by the middleware application server into the call format required by the DBMS, and forwarded to the database server. This kind of driver is extremely flexible, since it requires no code installed on the client and a single driver can actually provide access to multiple databases.

Type 4: 100% pure Java: l l l In a Type 4 driver, a pure Java-based driver that 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. This kind of driver is extremely flexible, you don't need to install special software on the client or server. Further, these drivers can be downloaded dynamically. My. SQL's Connector/J driver is a Type 4 driver. Because of the proprietary nature of their network protocols, database vendors usually supply type 4 drivers.

Which Driver should be used? l l If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4. If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver. Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet for your database. The type 1 driver is not considered a deployment-level driver and is typically used for development and testing purposes only.

JDBC Driver for My. SQL (Connector/J) l l l 13 Download Connector/J using binary distribution from : http: //dev. mysql. com/downloads/connector/j/5. 0. html To install simply unzip (or untar) and put mysqlconnector-java-[version]-bin. jar (I have installed mysql-connector-java-5. 0. 4 -bin. jar) in the class path For online documentation, see : http: //dev. mysql. com/doc/refman/5. 0/en/connect or-j. html

Basic steps to use a database in Java l l l 1. Establish a connection 2. Create JDBC Statements 3. Execute SQL Statements 4. GET Result. Set 5. Close connections 14

1. Establish a connection l l import java. sql. *; Load the vendor specific driver l Class. for. Name("com. mysql. jdbc. Driver "); l l l What do you think this statement does, and how? Dynamically loads a driver class, for MYSQL database Make the connection l Connection con = Driver. Manager. get. Connection( “jdbc: mysql: //localhost: 3306/ “+dbname, username, passwd); l Establishes connection to database by obtaining a Connection object 15

Database URL The format of a database URL is: String DB_URL = "jdbc: mysql: //dbserver: 3306/mydb"; Protocol Sub-protocol Hostname Port Database. Name q Port is the TCP port number where the database server is listening. § 3306 is the default port for My. SQL q Use hostname "localhost" for the local machine.

Database URL The hostname and port are optional. For My. SQL driver: defaults are localhost and port 3306 Example: These 4 URL refer to the same database "jdbc: mysql: //localhost: 3306/world" "jdbc: mysql: //localhost/world" "jdbc: mysql: ///world" "jdbc: mysql: /world"

Driver class names l l l Oracle: oracle. jdbc. driver. Oracle. Driver My. SQL: com. mysql. jdbc. Driver MS SQL Server: com. microsoft. jdbc. sqlserver. SQLServer. Driver 18

Create JDBC statement(s) and Executing SQL Statements l l Statement stmt = con. create. Statement() ; Creates a Statement object for sending SQL statements to the database String createtab = "Create table stud " + "(USN VARCHAR(10), Name VARCHAR(32), " + "Marks Integer)"; stmt. execute. Update(createtab); String insertstud = "Insert into stud values“ + “(cs-01, aaa, 89)"; stmt. execute. Update(insertstud); 19

Get Result. Set String querystud = "select * from stud"; Result. Set rs = Stmt. execute. Query(querystud); while (rs. next()) { String usn = rs. get. String(“USN"); String name = rs. get. String("NAME"); int marks = rs. get. Int("MARKS"); } 20

Result. Set Methods for Getting Data Result. Set "get" methods return column data: get. Long( 3 ) : get by column index (most efficient) get. Long( "population" ) : get by field name (safest) get. Int( ), get. Long( ) - get Integer field value get. Float( ), get. Double() - get floating pt. value get. String( ) - get Char or Varchar field value get. Date( ) - get Date or Timestamp field value get. Boolean( ) - get a Bit field value get. Bytes( ) - get Binary data get. Big. Decimal( ) - get Decimal field as Big. Decimal get. Blob( ) - get Binary Large Object get. Object( ) - get any field value

JDBC 2 – Scrollable Result Set JDBC 2. 0 includes scrollable result sets. Additional methods included are : ‘first’, ‘last’, ‘previous’, and other methods Statement stmt = con. create. Statement(Result. Set. TYPE_SCROLL_INSENSITIVE, Result. Set. CONCUR_READ_ONLY); l String query = “select name from stud where type=‘not sleeping’ “; Result. Set rs = stmt. execute. Query( query ); rs. previous(); / / go back in the RS (not possible in JDBC 1…) rs. relative(-5); / / go 5 records back rs. relative(7); / / go 7 records forward rs. absolute(100); / / go to 100 th record … 22

Handling Errors with Exceptions l l Programs should recover and leave the database in a consistent state. If a statement in the try block throws an exception or warning, it can be caught in one of the corresponding catch statements How might a finally {…} block be helpful here? E. g. , you could rollback your transaction in a catch { …} block or close database connection and free database related resources in finally {…} block l Close connection stmt. close(); pstmt. close(); l rs. close(); con. close(); l 23

Creating mysql database, table and inserting data l l l l sql>show databases; sql>create database mydb; sql>use mydb; sql>create table admin (username varchar(15), password varchar(15), type varchar(5)); sql>show tables; sql>insert into admin values (‘aa 1’, ’Abc@123’, ’user 1’); sql>commit; sql>select * from admin; 24

Sample program for DB connection import java. sql. Connection; import java. sql. Driver. Manager; import java. sql. SQLException; public class Jdbc. Example 1 { } public static void main(String args[]) { Connection con = null; try { Class. for. Name("com. mysql. jdbc. Driver"). new. Instance(); con = Driver. Manager. get. Connection("jdbc: mysql: //localhost: 3306/mydb", "root", ""); if (!con. is. Closed()) System. out. println("Successfully connected to My. SQL server. . . "); } catch(Exception e) { System. err. println("Exception: " + e. get. Message()); } finally { try { if (con != null) con. close(); } catch(SQLException e) {} } } 25

How to Execute SQL Commands The Statement interface defines many execute methods: Resultset rs = statement. execute. Query("SELECT. . . "); n use for statements that return data values (SELECT) n Returns results as a Result. Set object int count = statement. execute. Update("UPDATE. . . "); n Use for DROP TABLE, CREATE TABLE n use for INSERT, UPDATE, and DELETE boolean b = statement. execute("DROP TABLE test"); n use to execute any SQL statement(s)

Sample program for querying the table import java. sql. *; public class mysql_demo{ public static void main(String[] args) { System. out. println("My. SQL Connect Example. "); Connection conn = null; String url = "jdbc: mysql: //localhost: 3306/"; String db. Name = "mydb"; String driver = "com. mysql. jdbc. Driver"; String user. Name = "root"; String password = ""; String f 1, f 2, f 3; try { Class. for. Name(driver). new. Instance(); conn = Driver. Manager. get. Connection(url+db. Name, user. Name, password); String query = "Select * FROM admin; System. out. println("Connected to the database"); Statement stmt = conn. create. Statement(); Result. Set rs = stmt. execute. Query(query); while (rs. next()) { f 1 = rs. get. String(1); f 2=rs. get. String(2); F 3=rs. get. String(3) System. out. println(f 1+" "+f 2+" "+f 3); } //end while conn. close(); System. out. println("Disconnected from database"); } //end try catch(Class. Not. Found. Exception e) { e. print. Stack. Trace(); }} 27

JDBC 2 – Updateable Result. Set Statement stmt = conn. create. Statement(Result. Set. TYPE_SCROLL_INSENSITIVE, Result. Set. CONCU R_UPDATABLE); String sql; sql = "SELECT username, password, type from admin"; Result. Set rs = stmt. execute. Query(sql); while (rs. next()) { uname = rs. get. String(1); mypass=rs. get. String(2); mytype=rs. get. String(3); System. out. println(uname+" "+mypass+" "+mytype); } //end while rs. before. First(); while(rs. next()){ //Retrieve by column name String new. Type = "0"; rs. update. String( "type", new. Type ); 28 rs. update. Row(); }

Using a Prepared. Statement uses placeholders for data values. Prepared. Statement pstmt = connection. prepare. Statement( "SELECT * FROM Country where name = ? " ); Prepared. Statement will quote the string for you. // get data for Thailand Don't use '? ' pstmt. set. String( 1, “Inida"); Substitute “India" for placeholder #1 Result. Set rs = pstmt. execute. Query( ); save. Result. Set. As. Object( rs, country 1 );

Prepared statement to insert data into the table. l Prepared statements are precompiled, hence much more efficient to use. l Prepared. Statement s; s = conn. prepare. Statement ( "INSERT INTO admin (username, password, type VALUES(? , ? )"); s. set. String (1, uname); s. set. String (2, mypass); s. set. String(3, mytype); int count = s. execute. Update (); s. close (); System. out. println (count + " rows were inserted");

JDBC prepared statement Class. for. Name(driver). new. Instance(); conn = Driver. Manager. get. Connection(url+db. Name, user. Name, password); System. out. println("Connected to the database"); String qry="SELECT password FROM admin where username=? "; Prepared. Statement pstmt = conn. prepare. Statement(qry); uname="cs-01"; pstmt. set. String(1, uname); Result. Set rs = pstmt. execute. Query (); while (rs. next()) { f 1 = rs. get. String(1); System. out. println("The password for "+uname+" is "+f 1); } //end while conn. close(); System. out. println("Disconnected from database"); 31

JDBC l JDBC Data Access API – JDBC Technology Homepage l l JDBC Database Access – The Java Tutorial l l http: //java. sun. com/j 2 se/1. 4. 2/docs/api/java/sql/package-summary. html JDBC Technology Guide: Getting Started l l http: //java. sun. com/j 2 se/1. 4. 2/docs/guide/jdbc/index. html java. sql package l l http: //java. sun. com/docs/books/tutorial/jdbc/index. html JDBC Documentation l l http: //java. sun. com/products/jdbc/index. html http: //java. sun. com/j 2 se/1. 4. 2/docs/guide/jdbc/getstart/Getting. Started. TOC. fm. html JDBC API Tutorial and Reference (book) l http: //java. sun. com/docs/books/jdbc/ 32

JDBC references l JDBC Data Access API – JDBC Technology Homepage l l JDBC Database Access – The Java Tutorial l l http: //java. sun. com/j 2 se/1. 4. 2/docs/api/java/sql/package-summary. html JDBC Technology Guide: Getting Started l l http: //java. sun. com/j 2 se/1. 4. 2/docs/guide/jdbc/index. html java. sql package l l http: //java. sun. com/docs/books/tutorial/jdbc/index. html JDBC Documentation l l http: //java. sun. com/products/jdbc/index. html http: //java. sun. com/j 2 se/1. 4. 2/docs/guide/jdbc/getstart/Getting. Started. TOC. fm. html JDBC API Tutorial and Reference (book) l http: //java. sun. com/docs/books/jdbc/ 33

Exercises l l Develop a simple JDBC application to store the details of an employee in a table and display it on the console. Create a database called student with table name as ‘studinfo’ having the attributes name, USN branch, quota (CET or COMED_K). Write a JDBC program to extract the students who are admitted through CET and branch is CSE. Display the results in a proper format. 34