Monday, March 3, 2014

Statement, PreparedStatement and CallableStatement ~JDBC~ "Java Code"

Below are sample code used for database connection. In this case SQL Server database is used. 
~~~~~~~~~~~~~~~~~~
Using Statement Interface
~~~~~~~~~~~~~~~~~~

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

public class DBase
{
     public static void main(String [] args) throws SQLException, ClassNotFoundException
     {
        try
        {
           // load driver
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        }
        catch (ClassNotFoundException cnfe)
        {
            System.out.println(" ClassNotFoundException thrown");
        }

        try
        {
        // my Connection_String, which will definitely be different from yours' 
        String ConnString =  "jdbc:sqlserver://localhost:1433; databaseName= clems; username=Douby;password=clems";

        // creating the connection
        Connection conn = (DriverManager.getConnection(ConnString));
        Statement stmt = conn.createStatement();

        /* Using ResultSet.... using executeQuery() without ResultSet would throw a                  SQLException. REASON ~> executeQuery() returns result  */

        String sql_stmt = "Select First_Name from Employee";
       ResultSet rs = stmt.executeQuery(sql_stmt);

        while (rs.next())
        {
             //print all names in the First_Name column
            String a = rs.getString("First_Name");
            System.out.println(a);  
        } 
        }        
        catch (SQLException   sql)
        {
            System.out.println("SQLException thrown");
        }
    }
  }

~~~~~~~~~~~~~~~~~~~~~~~~
Using PreparedStatement Interface
~~~~~~~~~~~~~~~~~~~~~~~~
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class DBase
{
     public static void main(String [] args) throws SQLException, ClassNotFoundException
     {
        try
        {
         // loading sql server driver
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        catch (ClassNotFoundException cnfe)
        {
            System.out.println(" ClassNotFoundException thrown");
        }

        try
        {
           // 1433 = port number; Dont know how to get your port number? 
           // click here
        String ConnString =  "jdbc:sqlserver://localhost:1433; databaseName= clems; username=Douby;password=clems";
        Connection conn = (DriverManager.getConnection(ConnString));
        Statement stmt = conn.createStatement();

        String [] Fname = {" Wale", " Shina", " David"};
        String [] Lname = {" Ishegun", " Olowo", " Almond"};
        int [] Age = {42, 32, 53};

        String sql = "Insert into Employee values(?, ?, ?) ";

        PreparedStatement pstmt = conn.prepareStatement(sql);

        for (int i = 0; i< Fname.length; i++)
        {
            pstmt.setString(1, Fname[i]);
            pstmt.setString(2, Lname[i]);
            pstmt.setInt(3, Age[i]);

            pstmt.executeUpdate();
        }
        }                
        catch (SQLException   sql)
        {
            System.out.println("SQLException thrown");
        }
    }
  }
   
~~~~~~~~~~~~~~~~~~~~~~~
Using CallableStatement Interface
~~~~~~~~~~~~~~~~~~~~~~~


   
  

No comments: