Java JDBC Overview

Contents Introduction. JDBC Architecture Common JDBC Components Connecting Java applications with Databases. Querying and manipulating databases with JDBC.

Introduction JDBC stands for J Java D Datab base C Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage. -- Making a connection to a database. -- Creating SQL or MySQL statements. -- Executing SQL or MySQL queries in the database. -- Viewing & Modifying the resulting records.

Cont.. Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database.

JDBC Architecture Java code calls JDBC library JDBC loads a driver Driver talks to a particular database An application can work with several databases by using all corresponding drivers

Common JDBC Components DriverManager: DriverManager: This class manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication sub protocol. Driver: Driver: This interface handles the communications with the database server. Connection: Connection: This interface with all methods for contacting a database.

Common JDBC Components Statement: Statement: You use objects created from this interface to submit the SQL statements to the database. ResultSet: ResultSet: These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data. SQLException: SQLException: This class handles any errors that occur in a database application.

Connection interface Connection interface holds a connection with Database. Methods of this class are used to manage connection live as long as JDBC application wants to connect with Database. It also provides methods to execute various SQL statements on the Database. Ex: DDL (create, alter, drop) DML (insert, select, update, delete) DCL (commit, rollback) Methods Methods 1. 1. createStatement createStatement(): (): It creates SQL statement. 2. 2. prepareStatement prepareStatement(): (): It make SQL statements to execute fast.

Statement interface Statement Statement interface use a connection and executes SQL statements. Methods Methods 1. 1. executeQuery() executeQuery() This method is used for SQL statements which retrieve some data from the database. For example is SELECT SELECT statement. This method is meant to be used for select queries which fetch some data from the database. 2. 2. executeUpdate() executeUpdate() This method is used for SQL statements which update the database in some way. For example INSERT INSERT, UPDATE UPDATE and DELETE DELETE statements. All these statements are DML statements. This method can also be used for DDL statements which return nothing. For example CREATE CREATE and ALTER ALTER statements.

ResultSet interface ResultSet interface stores records retrieved by a SQL statement. Methods Methods 1. 1. next next(): (): Indicates next row. 2. 2. getRow getRow(): (): Returns row number. 3. 3. getString getString(): (): Retrieves String value of a row. 4. 4. getInt getInt(): (): Retrieves Integer value of a row. 5. 5. getFloat getFloat(): (): Retrieves Float value of a row. 6. 6. getDouble getDouble(): (): Retrieves Double value of a row. 7. 7. getDate getDate(): (): Retrieves Date value of a row.

Steps to interact with the Database 1. Register a driver 2. Specify JDBC URL 3. Connect to the Database 4. Create SQL Statements 5. Execute SQL Statements 6. Process Results 7. Close the connection

Cont.. To add MySQL library RC on Libraries Under your project [left pane of NetBeans] Add Library Select MySQL JDBC Driver To connect to your database that has been created on MySQL. Go to Service tab [left pane of NetBeans] RC on Database New connection Select MySQL (connector /J Driver) from the Drop down list and Next Give the database name, username and password and Test connection Finish

Example Sample codes

Inserting Data import java.sql.*; public class InsertData { public static void main(String args[]) { Connection conn = null; try { String userName = "admin"; String password = "1234"; String url = "jdbc:mysql://localhost:3306/student"; Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url,userName,password); Statement stmt=conn.createStatement();

Cont. stmt.executeUpdate("insert into user values( John', 12345 )"); conn.close(); System.out.println(" Record Added!"); } catch(Exception e) { System.out.println(" Error inserting record:"); } } }

Updating Data import java.sql.*; import java.io.*; public class UpdateData { public static void main(String args[]) { Connection conn = null; BufferedReader input = null; try { String userName = "admin"; String password = "1234"; String url = "jdbc:mysql://localhost:3306/student"; Class.forName("com.mysql.jdbc.Driver").newInstance() ; conn = DriverManager.getConnection(url,userName,password); Statement stmt=conn.createStatement();

Cont. input = new BufferedReader(new InputStreamReader(System.in)); System.out.print(" Enter record to update(isbn):"); String isbn = input.readLine(); System.out.print(" Enter field to update:"); String field = input.readLine(); System.out.print("Enter Data:"); String data = input.readLine(); String updateString = "update books set " + field + "='" + data + "'where isbn="+isbn; stmt.executeUpdate(updateString); conn.close(); System.out.println(" Record Updated!"); } catch(Exception e) { System.out.println(" Error updating record:"+e.toString()); } }}

Retrieving Data import java.sql.*; public class RetriveData { Connection con; Statement st; ResultSet res; public static void main(String[] args) { RetriveData ret=new RetriveData(); ret.retrieve(); }

Cont. private void retrieve(){ try { Class.forName("com.mysql.jdbc.Driver");//load the driver con=DriverManager.getConnection("jdbc:mysql://localhost:3306/FRID AY","root",""); String sql="select * from fri where ID=58"; st=con.createStatement(); res=st.executeQuery(sql); while(res.next()) { System.out.println(res.getString("Fname")); } } catch (ClassNotFoundException ex) { System.out.println("ClassNotFound Error:"+ex.getMessage()); } catch (SQLException ex) { System.out.println("SQL Error:"+ex.getMessage()); } }}