Basic steps involved in execution of SQL query Using JDBC - With sample code

Basic steps involved in execution of a SQL query using JDBC

1. Establish a connection 
2. Create a statement 
3. Execute the Query 
4. Close the connection

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

1. Establish connection with Database

• To load JDBC Driver into JVM we use Class.forName(String).
• DriverManager: This fully implemented class connects an application to a data source, which is specified by a database URL. JDBC 4.0 automatically loads Drivers.

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

               Connection  con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root", "pas");

2. Create statement

A Statement is an interface that represents a SQL statement. Statement allows you to perform following activities.

1. Insert records into DB
2. Retrieve records from DB
3. Update records from DB
4. delete records from DB

There are 3 flavors of using statement

• Statement: Implement simple SQL statements with no parameters.
• PreparedStatement: (Extends Statement) Used for precompiling SQL statements that can contain input parameters.
• CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameter   

                Statement stmt = con.createStatement();

3. Execute SQL queries 

• executeUpdate: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.
• executeQuery: Returns one ResultSet object - Used for executing select query.
• execute: Use this method if the query could return one or more ResultSet objects. Can be used to create table as well.

               int count = stmt.executeUpdate("insert into Employee values (1,'Sun')");

4. Closing Connection

we call Statement.close to immediately release the resources it is using. When you call this method, its ResultSet objects are closed.
We will also call Connection.close() to close connection with DB.

     finally { 

          if (stmt != null) { stmt.close(); }
          if(connection!=null){connection.close();}
     }

------------------------------------------------------------------------------------------------------------

Example Code 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.DriverManager;

public class Test{

public static void main(String [] args) throws SQLException{

Connection con=null;

Statement statement=null;

try{

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

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

statement=con.createStatement();

int rows=statement.executeUpdate("insert into employee values(1,'Sanjay')");

}

catch(ClassNotFoundException ce){

ce.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

finally{

if(statement!=null){

statement.close();
}
   if(connection!=null){
    connection.close();

   }

}

}

}