JaiswalTraining

Get the online training



Corejava Servlet Jsp  Php  Hibernate  Ajax Web Service   Ejb2.1 Ejb3.0 Struts Struts2  JPA Spring Ibatis   JSF    JSF2.0  CoreJavaDesignPattern    Jquery  Flex J2EE-Design-Patterns  Jboss7  Maven  Contact Me                                                                                                                                                                        
            IGNOU SMU PTU Project                                           Training                                                                                                                              
              

Contact Us  0091- 9210721663         jaiswaltraining@gmail.com





Get Best Tutorial on all major programing language

Corejava
Servlet
Jsp
Php
Hibernate
Ajax
Web Service
Ejb2.1
Ejb3.0
Struts  
Struts2
JPA
Spring
Ibatis
JSF
JSF2.0
CoreJavaDesignPattern
Jquery
Flex
J2EE-Design-Patterns
Jboss7
Maven







JDBC (Java Database Connectivity)

  • It is the api to connect  differrent databases with java code
  • A standard Java interface for connecting from Java to relational databases.
  • The JDBC standard was defined by Sun Microsystems, allowing individual providers
    to implement and extend the standard with their own JDBC drivers.
  • It is used for connecting databases with standloane and server side java code
    • java.sql for standalone 
    • javax.sql for server side


Understand the JDBC

Understand the steps of the JDBC:
  •     Importing packages
  •     Opening a connection to a database
  •     Working with different types of database drivers
  •     Querying the database
Creating a Statement object to perform a query ResultSet or create a PreparedStatement
Executing a query and return a ResultSet object
Differentiating between a Statement and a PreparedStatement


  •     Processing a ResultSet
  •     Closing the ResultSet and Statement
  •     Importance of closing the connection
  •     Understand a JBDC row set.


Steps in JDBC

Import Packages
    Import statements at the beginning of the  program:
        import java.sql.Connection;
        import java.sql.SQLException;
        or
        import java.sql.*;   

    Import the ff. Oracle packages when you want to access the extended
functionality provided by the Oracle drivers.
        import oracle.jdbc.pool.OracleOCIConnectionPool;   
        or
        import oracle.jdbc.*;


Loading the Driver

Class.forName("com.mysql.jdbc.Driver");

Class.forName("oracle.jdbc.driver.OracleDriver");

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Class.forName("org.hsqldb.jdbcDriver");
Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”)



Open a Connection to a Database
    A call to this method creates an object instance of the java.sql.Connection class.

 The getConnection() method is an overloaded method that takes
Three parameters, one each for the URL, username, and password
Only one parameter for the database URL. In this case, the URL contains the username and password

   The ff. lines of code illustrate using the getConnection() method:
    Connection conn = DriverManager.getConnection(URL, username, passwd); Or
    Connection conn = DriverManager.getConnetion(URL);
    where URL, username and password are of String data types.

Opening a Connection using the Oracle JDBC OCI:
   
When using the OCI driver, the database can be specified using the TNSNAMES entry
in the tnsnames.ora.file.
 For example, to connect to a database on a particular host as user oratest with password
oratest that has a TNSNAMES entry of oracle.world, use the following code:

Connection con=DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:XE""system""manager");

con=DriverManager.getConnection("jdbc:odbc:dsn1","system""manager");
  con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/");

 con = DriverManager.getConnection(
          "jdbc:mysql://localhost:3306/test""root""mysql");

   
Querying the Database
     Querying the database involves the following steps:

Creating a Statement Object
  •  This is to instantiate objects that run the query against the database to which they are connected.
  •  This is done by the createStatement() method of the conn Connection object created above.
  • A call to this method creates an object instance of the Statement class.
  •  The ff. line of code illustrates this:

             Statement sql_stmt = conn.createStatement();
   


   Creating a PreparedStatement
  • A PreparedStatement is associated as a channel with a connection and a compiled SQL statement.
  • PreparedStatements are also created with a Connection method.
  • The following snippet shows how to create a parameterized SQL statement with three input parameters:

    PreparedStatement prepareUpdatePrice = conn.prepareStatement( "UPDATE Sells SET price = ? WHERE bar = ? AND beer = ?");
   
Executing the Query and Returning a ResultSet
  • This is done by using the executeQuery() method of the Statement object.
  • A call to this method takes as parameter a SQL SELECT statement and
    returns a JDBC ResultSet object
  • The ff. line of code illustrates this using the sql_stmt object created above:
    ResultSet rset = sql_stmt.executeQuery
        (“SELECT empno, ename, sal, deptno FROM emp ORDER BY ename”);



Process the Result Set

    Once the query has been executed, there are two steps to be carried out:
Processing the output resultSet to fetch the rows
            next() method of the ResultSet object
Retrieving the column values of the current row
            getXXX() methods of the JDBC rset object
    Here getXXX() corresponds to the getInt(), getString() etc with XXX being replaced by a Java datatype

    while (rset.next())
        System.out.println (rset.getString(“ename”));


  Closing the ResultSet and Statement

  •  Once the ResultSet and Statement objects have been used, they must be closed explicitly.
  •  This is done by calls to the close() method of the ResultSet and Statement classes.
  •  The ff. code illustrates this:
        rset.close();
        sql_stmt.close();

    If not closed explicitly, there are two disadvantages:
Memory leaks can occur
Maximum Open cursors can be exceeded


   Closing the Connection
  •     The last step is to close the database connection after importing the packages
    and loading the JDBC drivers.
  • This is done by a call to the close() method of the Connection class.
  • The ff. line of code does this:
        conn.close();


Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class data1 {

  public static void main(String[] args) {
    Statement st;
    Connection con;
    ResultSet rs;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:
mysql://localhost/test"
,
          "root""");
      st = con.createStatement();
      rs = st.executeQuery("select * from stud");

      while (rs.next()) {
        System.out.println(rs.getInt(1"  " +
rs.getString
("name"));
      }

    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }

    catch (ClassNotFoundException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }
}

Output:

java data1

101  shankar
102  javed
Downlaod code

  • Statement
    • Statement is the interface present in the java.sql package.
    • Its object is created with the createStatement() method
      present in the   Connection interface.
    • It is used for executing  query .
    • Query  is executed  with the three methods present in the
      Statement interrface
          Query execution method
    • executeQuery()
    • executeUpdate()
    • execute()
executeQuery()
  •  Executes the SQL query and returns the data in a table
  • (ResultSet)
  •  The resulting table may be empty but never null
ResultSet results =statement.executeQuery("SELECT a, b FROM table");
executeUpdate()

  •  Used to execute for INSERT, UPDATE, or DELETE
    SQL statements
  •  The return is the number of rows that were affected in the
    database
  • Supports Data Definition Language (DDL) statements
    CREATE TABLE, DROP TABLE and ALTER TABLE
execute()
  • Generic method for executing stored procedures and prepared statements
  • Rarely used (for multiple return result sets)
  • The statement execution may or may not return a
    ResultSet (use statement.getResultSet). If the return value
    is true, two or more result sets were produced.
executeQuery()
Statement with parameters

  • statement is used for query execution.
  • It can be with parameters and without parameters.
Examples:-

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class data2 {

  public static void main(String[] args) {
    Statement st;
    Connection con;
    ResultSet rs;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql:
//localhost/test"
,
          "root""");
      st = con.createStatement();
      int x=st.executeUpdate("insert into stud
values(103,'Geeta')"
);
          
      rs = st.executeQuery("select * from stud");
      
      while (rs.next()) {
        System.out.println(rs.getInt(1"  " +
rs.getString
("name"));
      }

    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }

    catch (ClassNotFoundException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }
}
Output:-


java data2
101  shankar
102  javed
103  Geeta
\
Downlaod Code


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class data3 {

  public static void main(String[] args) {
    Statement st;
    Connection con;
    ResultSet rs;
    int roll=104;
    String name="Rita";
    try {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection
(
"jdbc:mysql://localhost/test",
          "root""");
      st = con.createStatement();
      st.executeUpdate("insert
 into stud values("
+roll+",'"+name+"')");
          
      rs = st.executeQuery("select * from stud");
      
      while (rs.next()) {
        System.out.println(rs.getInt(1)
"  " + rs.getString("name"));
      }

    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }

    catch (ClassNotFoundException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }
}

Output:-


java data3
101  shankar
102  javed
103  Geeta
104  Rita
Downlaod code


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ora_data {

  public static void main(String[] args) {
    Statement st;
    Connection con;
    ResultSet rs;
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      con = DriverManager.getConnection("jdbc:oracle:
thin:@localhost:1521:XE"
,
              "system""manager");
      st = con.createStatement();
      rs = st.executeQuery("select * from stud");

      while (rs.next()) {
        System.out.println(rs.getInt(1"  " +
 rs.getString
("name"));
      }

    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }

    catch (ClassNotFoundException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }
}
Output:

java ora_data
1  ram
2  shyam
3  Geeta
4  Mohan
5  Kishan
Downlaod code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class call1 {
  public static void main(String[] args) {
    CallableStatement cst;
    Connection con;
    ResultSet rs;
    try {

      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql:
//localhost/test"
,
          "root""");
      cst = con.prepareCall("call p1()");
      
      rs = cst.executeQuery();
      while (rs.next()) {
        System.out.println(rs.getInt(1"  " +
rs.getString
(2"  "
            + rs.getInt(3));

      }
    }

    catch (Exception e) {
      System.err.println(e);
    }
  }
}


jdbc Examples using  Statement
  • Inserting data using Statement Interface
  • In the given example we are inserting data using mysql driver


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class data4 {

  public static void main(String[] args) {
    Statement st;
    Connection con;
    ResultSet rs;
    Scanner sc = new Scanner(System.in);

    String ch = "y";

    try {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:
mysql://localhost/test"
,
          "root""");
      st = con.createStatement();

      while (ch.equals("y"|| ch.equals("Y")) {

        System.out.println("enter roll");
        int roll = sc.nextInt();
        System.out.println("enter name");
        String name = sc.next();

        st.executeUpdate("insert into stud
values(" 
+ roll + ",'"
            + name + "')");
        System.out.println("record inserted");
        System.out.println("Enter another record y\n ");
        ch = sc.next();
      }

      rs = st.executeQuery("select * from stud");

      while (rs.next()) {
        
        System.out.println(rs.getInt(1"  "
+ rs.getString
("name"));
      }

    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }

    catch (ClassNotFoundException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }
}

Output:

C:\wamp\www\corejava\jdbc\code>java ora_data
1  ram
2  shyam
3  Geeta
4  Mohan
5  Kishan
ava data4

enter roll
106
enter name
Ram
record inserted
Enter another record y
n
101  shankar
102  javed
106  Ram
104  Rita
103  Geeta
Downlaod code

Create method                                                                                                                                    
  • Creating table in mysql using jdbc

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class data7 {

  public static void main(String[] args) {
    Statement st;
    Connection con;
    ResultSet rs;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql:
//localhost/test"
,
          "root""");
      st = con.createStatement();
      st.execute("create table cust(id int)");
      System.out.println("Table Created Successfully");
      int a=st.executeUpdate("update stud set name='mohan'");
          System.out.println(a+" records updated");     
      rs = st.executeQuery("select * from stud");

      while (rs.next()) {
        System.out.println(rs.getInt(1"  " +
rs.getString
("name"));
      }

    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }

    catch (ClassNotFoundException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }
}

Output:-
java data7
Table Created Successfully
5 records updated
101  mohan
102  mohan
106  mohan
104  mohan
103  mohan
Download Code

Alter table using  execute method
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class data9 {
  public static void main(String[] args) {
    Connection con;
    Statement st;

    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      con = DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:XE""system""manager");
      st = con.createStatement();

      st.execute("alter table stud drop column age ");

      System.out.println("table stud altered. ");

    }

    catch (ClassNotFoundException e) {
      System.err.println(e);
      e.printStackTrace();
    catch (SQLException e) {
      System.err.println(e);
      e.printStackTrace();
    }
  }

}
Output:-

java data9
table stud altered.
Downlaod code