PPT ON JAVA DATABASE CONNECTIVITY

CLICK HERE TO DOWNLOAD  PPT ON JAVA DATABASE CONNECTIVITY

JAVA DATABASE CONNECTIVITY  Presentation Transcript

1.JAVA DATABASE CONNECTIVITY

2.What is JDBC?
“An API that lets you access virtually any tabular data source from the Java programming language”
JDBC Data Access API – JDBC Technology Homepage
What’s an API?  
See J2SE documentation
What’s a tabular data source?
“… access virtually any data source, from relational databases to spreadsheets and flat files.”
JDBC Documentation
We’ll focus on accessing Oracle databases

3.General Architecture
What design pattern is implied in this architecture?
What does it buy for us?
Why is this architecture also multi-tiered?
4.Basic steps to use a database in Java

1.Establish a connection

2.Create JDBC Statements

3.Execute SQL Statements

4.GET ResultSet

5.Close connections
5. Establish a connection
import java.sql.*;
Load the vendor specific driver
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver");
What do you think this statement does, and how?
Dynamically loads a driver class, for MS SQL database
Make the connection
Connection con = DriverManager.getConnection(“jdbc:odbc:ada”);
What do you think this statement does?
Establishes connection to database by obtaining a Connection object

6.Create JDBC statement(s)
Statement stmt = con.createStatement() ;
Creates a Statement object for sending SQL statements to the database

7.Executing SQL Statements
String create = "Create table dh " +
     "(SSN Integer not null, Name VARCHAR(50), " + "Marks Integer)";

    stmt.executeUpdate(create);
    //execute Update method updates the table in the database

String str=“INSERT INTO dh VALUES(2,’dhruv’,300)”;
    stmt.executeUpdate(str);

8.Get ResultSet
String select = “SELECT * FROM dh";

ResultSet rs = stmt.executeQuery(select);
//What does this statement do?
//Executes the Query

while (rs.next()) {    
    int ssn = rs.getInt("SSN");
    String name = rs.getString("NAME");
    int marks = rs.getInt("MARKS");
}

9.Close connection
stmt.close();
con.close();

10.Transactions and JDBC
JDBC allows SQL statements to be grouped together into a single transaction
Transaction control is performed by the Connection object, default mode is auto-commit, I.e., each sql statement is treated as a transaction
We can turn off the auto-commit mode with con.setAutoCommit(false);
And turn it back on with con.setAutoCommit(true);
Once auto-commit is off, no SQL statement will be committed until an explicit is invoked con.commit();
At this point all changes done by the SQL statements will be made permanent in the database.

11.Handling Errors with Exceptions
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

12.Another way to access database (JDBC-ODBC)

13. Database To Show The entry Of An Table

14.Sample program
import java.sql.*;   //Package For Implementation
class Test  {
    public static void main(String[] args)  {
        try {    //for exception Handling
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //dynamic loading of driver
            
   Connection con = DriverManager.getConnection(“jdbc:odbc:ada”); //for windows authentication
 // Connection con = DriverManager.getConnection(“jdbc:odbc:ada,” ”,” ”); //for sqlserver authentication
            Statement stmt = con.createStatement();

15.          ResultSet rs = stmt.executeQuery(“SELECT * FROM  dh”);
          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
              as a String ( getString( columnNumber) ) and output it to the screen */

              System.out.println("Data from column_name: " + rs.getString(1) );
    //System.out.println("Data from column_name: " + rs.getString(“u_name”) );

    System.out.println("Data from column_name: " + rs.getString(2) );
   //System.out.println("Data from column_name: " + rs.getString(“u_id”) );

          }
       stmt.close(); // close Statement to let the database know we're done with it
          con.close(); //close connection
       }
      catch (Exception err) { System.out.println("ERROR: " + err);  }
    }
}

16.Mapping types JDBC - Java

17.JDBC 2 – Scrollable Result Set

18.JDBC and beyond

19.(JNDI) Java Naming and Directory Interface
API for network-wide sharing of information about users, machines, networks, services, and applications
Preserves Java’s object model
(JDO) Java Data Object
Models persistence of objects, using RDBMS as repository
Save, load objects from RDBMS
(SQLJ) Embedded SQL in Java
Standardized and optimized by Sybase, Oracle and IBM
Java extended with directives:  # sql
SQL routines can invoke Java methods
Maps SQL types to Java classes

20.JDBC references
JDBC Data Access API – JDBC Technology Homepage
http://java.sun.com/products/jdbc/index.html
JDBC Database Access – The Java Tutorial
http://java.sun.com/docs/books/tutorial/jdbc/index.html
JDBC Documentation
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/index.html
java.sql package
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html
JDBC Technology Guide: Getting Started
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html
JDBC API Tutorial and Reference (book)
http://java.sun.com/docs/books/jdbc/

21.JDBC
JDBC Data Access API – JDBC Technology Homepage
http://java.sun.com/products/jdbc/index.html
JDBC Database Access – The Java Tutorial
http://java.sun.com/docs/books/tutorial/jdbc/index.html
JDBC Documentation
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/index.html
java.sql package
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html
JDBC Technology Guide: Getting Started
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html
JDBC API Tutorial and Reference (book)
http://java.sun.com/docs/books/jdbc/