Java Database Connectivity

---

• JDBC is a technology created by Sun-MicroSystem to Connect Java Program as a frontend with oracle database as backend
• JDBC provide us a package Java.sql.* which contain all the classes and interfaces usefull for database connectivity.

Types of Driver

• Type -1 (JDBC ODBC bridge driver)
• Type -2 OCI Driver
• Type -3 Server Driver (postgrace , Ingrace both are databases provided by type-3)
• Type -4 Thin Driver

---

Type -1 :-Sun Mycrosystem has officially said that type-1 driver should be used for testing and learning purpose and not for commertial application development & the driver Provided by Jdk ( java Development Kit )

Drawback of Type-1 driver are :-

• Plateform dependent , work for Windos only
• It has many layers therefore it takes longer time to process data means it has low performance

---

• Type 2 and Type -4 driver given by Oracle.

---

Steps required to connect java program with Database ( Oracle)

1. Import the java.sql Package.
2. load the driver class in our program
3. pass URL ( location ) , username , password , database Portno. and get Connection object.
4. Using connection object get the Statement object.
5. Make SQL Query { String q ="create table Bank ( emid number , ename varchar2(20) ) }"
6. execute the query
7. read the request depending on the Query
8. Close connection , Close statement , Close resultset .

---

Query Format

---

Table in Oracle Database :- Create , Insert , Update , Delete , Select

Select( Ctrl+a) , Copy ( Ctrl+c ) and Run the code for practice

import java.sql.*; public class Createtabledemo { public static void main(String[] args) { try{ Class.forName("oracle.jdbc.driver.OracleDriver");//fully qualified class name Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "myresult", "myresult"); Statement stmt=con.createStatement(); String q="create table emp(eid number, ename varchar2(20),edate date)"; stmt.execute(q); System.out.println("table Created"); } catch(Exception e) { System.out.println("Some Error"); } }} import java.sql.*; public class InsertTableDemo { public static void main(String[] args) { try{ Class.forName("oracle.jdbc.driver.OracleDriver");//fully qualified class name Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "myresult", "myresult"); Statement stmt=con.createStatement(); String q="insert into emp values(1329310017, 'sasi','01-jan-2016')"; stmt.execute(q); System.out.println("data Inserted"); } catch(Exception e) { System.out.println("Some Error"); } } } import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class UpdateTableDemo { public static void main(String[] args) { try{ Class.forName("oracle.jdbc.driver.OracleDriver");//fully qualified class name Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "myresult", "myresult"); Statement stmt=con.createStatement(); String q="update emp set ename='Suraj' where eid=1329310022"; stmt.execute(q); System.out.println("data updated"); } catch(Exception e) { System.out.println("Some Error"); } } } import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class DeleteTableDemo { public static void main(String[] args) { try{ Class.forName("oracle.jdbc.driver.OracleDriver");//fully qualified class name Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "myresult", "myresult"); Statement stmt=con.createStatement(); String q="delete from emp where eid=1329310022"; stmt.execute(q); System.out.println("data deleted"); } catch(Exception e) { System.out.println("Some Error"); } } } import java.sql.*; public class SelectTableDemo { public static void main(String[] args) { try{ Class.forName("oracle.jdbc.driver.OracleDriver");//fully qualified class name Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "myresult", "myresult"); Statement stmt=con.createStatement(); String q="Select *from emp "; ResultSet rs = stmt.executeQuery(q); System.out.println("DATA OF EMP TABLE ");//assume that EMP is already created; while(rs.next() ) { int a= rs.getInt(1); System.out.print(a+ " " ); String b=rs.getString(2); System.out.print(b + " "); String c=rs.getString(3); System.out.print(c + " "); String d=rs.getString(4); System.out.print(d +" " ); System.out.println("\n"); } } catch(Exception e) { System.out.println("Some Error"); } } }

---

Explain Query

---

Select Query

Assume that we already had a table like this ( in oracle schema myresult ):-

Before using select commond take a look on java Sql Mapping ( for conversion of sql ( orale ) Datatypes to java Datatypes .)

JAVA SQL MAPPING :-


Take a look on Example with Select .



Output of above code