jdbc rowset

https://drive.google.com/open?id=0B7JJxBS2bMdoX3ZFMm8wVkRtYjg

How to get primary key value (auto-generated keys) from inserted queries using 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:@<hostname>:<port num>:<DB name>"
                    ,"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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            try{
                if(rs != null) rs.close();
                if(pstmt != null) pstmt.close();
                if(con != null) con.close();
            } catch(Exception ex){}
        }
    }

Write an example for JDBC prepared statement with ResultSet

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:@<hostname>:<port num>:<DB name>"
                        ,"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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            try{
                if(rs != null) rs.close();
                if(prSt != null) prSt.close();
                if(con != null) con.close();
            } catch(Exception ex){}
        }
    }
}

Prepared Statements

JDBC PreparedStatement can be used when you plan to use the same SQL statement many times. It is used to handle precompiled query. If we want to execute same query with different values for more than one time then precompiled queries will reduce the no of compilations. Connection.prepareStatement() method can provide you PreparedStatment object. This object provides setXXX() methods to provide query values. Below example shows how to use PreparedStatement.

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:@<hostname>:<port num>:<DB name>"
                    ,"user","password");
            String query = "insert into emp(name,salary) values(?,?)";
            prSt = con.prepareStatement(query);
            prSt.setString(1, "John");
            prSt.setInt(2, 10000);
            //count will give you how many records got updated
            int count = prSt.executeUpdate();
            //Run the same query with different values
            prSt.setString(1, "Cric");
            prSt.setInt(2, 5000);
            count = prSt.executeUpdate();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            try{
                if(prSt != null) prSt.close();
                if(con != null) con.close();
            } catch(Exception ex){}
        }
    }
}

statements

There are 3 types of Statements, as given below:

Statement:

It can be used for general-purpose access to the database. It is useful when you are using static SQL statements at runtime.

PreparedStatement:

It can be used when you plan to use the same SQL statement many times. The PreparedStatement interface accepts input parameters at runtime.

CallableStatement:

CallableStatement can be used when you want to access database stored procedures. - See more at: http://java2novice.com/jdbc/statement-types/#sthash.871NQImH.dpuf

Fetching data from table

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

public class MyResultSetEx {

    public static void main(String a[]){
        
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager
                .getConnection("jdbc:oracle:thin:@<hostname>:<port num>:<DB name>"
                    ,"user","password");
            Statement stmt = con.createStatement();
            System.out.println("Created DB Connection....");
            ResultSet rs = stmt.executeQuery("select name, salary from emp");
            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getInt("salary"));
            }
            rs.close();
            con.close();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }