Java Jdbc Pro

Write an example code for JDBC prepared statement

package com.java2novice.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class MyPreparedStatement

{

public static void main(String a[])

{

        Connection con = null;

        PreparedStatement prSt = null;

        try

      {

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

            con = DriverManager.

                getConnection("jdbc:oracle:thin:@::","user","password");

            String query = "insert into emp(name,salary) values(?,?)";

            prSt = con.prepareStatement(query);

            prSt.setString(1, "John");

            prSt.setInt(2, 10000);

            int count = prSt.executeUpdate();

            prSt.setString(1, "Cric");

            prSt.setInt(2, 5000);

            count = prSt.executeUpdate();

        }

        catch (ClassNotFoundException e)

       {

            e.printStackTrace();

        }

        catch (SQLException e)

       {

            e.printStackTrace();

        }

          finally

        {

            try

             {

                if(prSt != null) prSt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}

Write an example for JDBC prepared statement with Result Set

package com.java2novice.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class MyPrepStmtResltSet

{

public static void main(String a[])

{

        Connection con = null;

        PreparedStatement prSt = null;

        ResultSet rs = null;

        try

      {

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

            con = DriverManager.

             getConnection("jdbc:oracle:thin: ::" ,"user","password");

            String query = "select * from emp where empid=?";

            prSt = con.prepareStatement(query);

            prSt.setInt(1, 1016);

            rs = prSt.executeQuery();

            while(rs.next()){

                System.out.println(rs.getString("name")+" -- "+rs.getInt("salary"));

            }

            rs.close();

            prSt.setInt(1, 1416);

            rs = prSt.executeQuery();

            while(rs.next())

            {

                System.out.println(rs.getString("name")+" -- "+rs.getInt("salary"));

            }

        }

        catch (ClassNotFoundException e)

        {

            e.printStackTrace();

        }

        catch (SQLException e)

        {

              e.printStackTrace();

        }

           finally

           {

            try

            {

                if(rs != null) rs.close();

                if(prSt != null) prSt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}

How to get primary key value auto generated keys from inserted queries using JDBC

package com.java2novice.jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class MyAutoGeneratedKeys

{

public static void main(String a[])

{

        Connection con = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        try

         {

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

            con = DriverManager.

    getConnection("jdbc:oracle:thin:@::"

   ,"user","password");

            String query = "insert into emps (name, dept, salary) values (?,?,?)";

            pstmt = con.prepareStatement(query,Statement.return_generated_keys);

            pstmt.setString(1, "John");

            pstmt.setString(2, "Acc Dept");

            pstmt.setInt(3, 10000);

            pstmt.executeUpdate();

            rs = pstmt.getGeneratedKeys();

            if(rs != null && rs.next())

            {

                System.out.println("Generated Emp Id: "+rs.getInt(1));

            }

          }

            catch (ClassNotFoundException e)

            {

                 e.printStackTrace();

             }

             catch (SQLException e)

            {

               e.printStackTrace();

             }

             finally

             {

            try

             {

                if(rs != null) rs.close();

                if(pstmt != null) pstmt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}

Write a simple program for Callable Statement to execute stored procedure

package com.java2novice.jdbc;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class MySimpleCallableStatement {

     

    public static void main(String a[]){

         

        Connection con = null;

        CallableStatement callSt = null;

        try

       {

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

            con = DriverManager.

                getConnection("jdbc:oracle:thin: ::" ,"user","password");

            callSt = con.prepareCall("{call myprocedure(?,?)}");

            callSt.setInt(1,200);

            callSt.setDouble(2, 3000);

            callSt.execute();

            System.out.println("Executed stored procedure!!!");

        }

         catch (ClassNotFoundException e)

         {

            e.printStackTrace();

         }

          catch (SQLException e)

        {

            e.printStackTrace();

        }

          finally

          {

            try        

             {

                if(callSt != null) callSt.close();

                if(con != null) con.close();

            }

            catch(Exception ex){}

        }

    }

}