javasetup

Java JDBC And Statement Class


This example covers :
  1. How to Connect database using DriverManager class.
  2. Execute sql query using Statement class.
  3. Along with this we will see how to use Statement class to



1. Prerequisites

Before we start coding let us first tell you the tools we used and the libraries we need for this application.

Tools Used
  Eclipse     [Any version 4.2(Juno) or above . We are using 4.8(oxygen).]
  SqlDeveloper     [We are using version 18.2.0.183]

JDK
  Install any JDK.     [We are using JDK 1.8]

Oracle
  Need to have one oracle DB installed.     [We are using Oracle 10g]

Jars Required
  We need following dependency jars to build the application. [Download link provided within]




	Now we are all set to start building our application.
	




2. Create a Java project


Follow the steps below, read inline instructions.

Step 1
Create Project
Goto File --> New --> Java Project


Step 2
Create Project
Write the project name.
Check the JRE section(Highlighted yellow box). Make sure installed JRE is selected.
Click on Finish button.


  • Create a Package.
Step 3
Create Project
Right click on the src directory.
Go to New --> Package as shown.


Step 4
Create Project
Write Name of the package.
Click on Finish button.



3. Configure Build Path

Steps to configure build path in eclipse. Read inline instructions.

Step 1
Spring Batch
Right-Click on the project
Goto Build Path
Click on Configure Build Path

Step 2
Spring Batch
Click on Add Exsternal Jars... button

Step 3
Spring Batch
Navigate to the directory where the jars has been downloaded.
Select all the downloaded jars and click on Open.

Step 4
Spring Batch
Click on apply and close button.

	
Now we are done with creating a java project. Lets start coding :)

4. Code


  • In this section we are sharing the code.
  • Also sharing the steps to create class in eclipse(Toggle the bar)

1. JDBCExample.java

  • Create a java class JDBCExample.java.
Step 1
Create Project
Right click on the package com.javasetup.jdbc.examples.
Go to New --> Class as shown.


Step 2
Create Project
Write Name of the class.
Click on Finish button.


Step 3
Create Project
This is the default java class structure that eclipse will generate.


  • Review Directory structure of the project just after creating it in below screen.
Project structure in eclipse
Project Structure




This tutorial will cover the whole CRUD funtionality

  1. Create a table.(C)
  2. Insert/Update a row.(U)
  3. Read the row that we have inserted.(R)
  4. Delete the row.(D)
Before executing SQL query, we have to Establising DB Connection. The steps to establish a DB connection are :
  • First Load the Driver -> Class.forName("oracle.jdbc.driver.OracleDriver")
  • Then Establish the connection using DriverManager class -> DriverManager.getConnection(url, username, password)
    where the url = jdbc:oracle:thin:@127.0.0.1:1521:xe.
    The url pattern using Database name => jdbc:oracle:thin:@host:port:database_name
    The url pattern using Service name => jdbc:oracle:thin:username/password@//host:port/servicename

After establishing a DB Connection we are ready to execute query using Statement class.
To do that :
  • First Create a Statement object -> conn.createStatement() where conn is the connection created using DriverManager class.
  • Then Execute the query -> st.executeQuery(query) where st is object of Statement class.


	
Now lets check out the codes for executing different SQL queries.


Create a Table!

In this section we will create a table using JDBC.


A table creation sql script shared below where we are creating a table EMPLOYEE.
Table Creation Script

    CREATE TABLE EMPLOYEE(
           EMPNO VARCHAR2(10),
           NAME VARCHAR2(100),
           EMAIL VARCHAR2(30),
           JOB_TITLE VARCHAR2(50),
           HIRE_DATE DATE
           );
    
	

The code to create table EMPLOYEE
JDBCCreateTableExample.java

package com.javasetup.jdbc.examples;

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

public class JDBCCreateTableExample {

	public static void main(String[] args) {
		Connection conn=null;
		Statement st=null;
		boolean status=false;
		String url="jdbc:oracle:thin:@127.0.0.1:1521:xe";            //Database URL
		String username="java_setup"; 
		String password="java_setup";
		                                                             //Create Table query
		String query="CREATE TABLE EMPLOYEE(EMPNO VARCHAR2(10), NAME VARCHAR2(100), EMAIL VARCHAR2(30), JOB_TITLE VARCHAR2(50), HIRE_DATE DATE)";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");         //Loading the driver
			conn=DriverManager.getConnection(url, username, password);//Creating the connection
			st=conn.createStatement();                                //Creating Statement
			status=st.execute(query);                                 //Executing the query
			System.out.println("Table created successfully");
		}catch(Exception e){
			e.printStackTrace();
		}
		finally	{
			try	{
				if(st!=null)
					st.close();                                     //Closing Statement
				if(conn!=null)
					conn.close();                                   //Closing Connection
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}

}
    
	



5. Run the application

  • Follow the steps below to run the application.
Step 1
Hello Java
Right click on the java class.
Go to Run As --> Java Application.


  • See the output.
Console Output

        Table created successfully

    
	

  • Run a select query on this table like SELECT * FROM EMPLOYEE (from SQLDeveloper tool or any other tool)
Table View

Read a Table!

In this section we will read(basically execute a select query) a table using JDBC.


A SQL select query shared below where we are selecting data from table EMPLOYEE.
Read Table query

    SELECT * FROM EMPLOYEE;
    
	

The code to read data from table EMPLOYEE
JDBCReadExample.java

package com.javasetup.jdbc.examples;

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

public class JDBCReadExample {

	public static void main(String[] args) {
		Connection conn=null;
		Statement st=null;
		ResultSet rst=null;
		String url="jdbc:oracle:thin:@127.0.0.1:1521:xe";             //Database URL
		String username="java_setup"; 
		String password="java_setup";
		String query="SELECT * FROM EMPLOYEE";                        //Select query
		try	{
			Class.forName("oracle.jdbc.driver.OracleDriver");         //Loading the driver
			conn=DriverManager.getConnection(url, username, password);//Creating the connection
			st=conn.createStatement();                                //Creating Statement
			rst=st.executeQuery(query);                               //Executing the query
			while(rst.next()){
				String name=rst.getString("NAME");
				String jobTitle=rst.getString("JOB_TITLE");
				System.out.println("Name :: "+name+", Job Title :: "+jobTitle);				
			}
		}catch(Exception e)	{
			e.printStackTrace();
		}
		finally	{
			try	{
				if(rst!=null)
					rst.close();                                    //Closing ResultSet
				if(st!=null)
					st.close();                                     //Closing Statement
				if(conn!=null)
					conn.close();                                   //Closing Connection
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}

}
    
	



5. Run the application

  • Follow the steps below to run the application.
Step 1
Hello Java
Right click on the java class.
Go to Run As --> Java Application.


  • See the output.
Console Output

        Name :: Rakesh, Job Title :: Manager

    
	

Insert/Update a row!

In this section we will insert a row in the EMPLOYEE table using JDBC.


A SQL insert query shared below where we are inserting a record ito the table EMPLOYEE.
Insert/update into Table query

    1. Insert into EMPLOYEE (EMPNO,NAME,EMAIL,JOB_TITLE,HIRE_DATE) values ('1001','Rakesh','rakesh.job@xxxxx.com','Manager',SYSDATE);
	2. UPDATE TABLE EMPLOYEE SET EMAIL='test.mail@gmail.com' WHERE EMPNO='1001';
    
	

The code to insert data into table EMPLOYEE
JDBCInsertUpdateExample.java

package com.javasetup.jdbc.examples;

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

public class JDBCInsertUpdateExample {

	public static void main(String[] args) {
		Connection conn=null;
		Statement st=null;
		String url="jdbc:oracle:thin:@127.0.0.1:1521:xe";            //Database URL
		String username="java_setup"; 
		String password="java_setup";
		                                                              //Insert query
		String query="Insert into EMPLOYEE (EMPNO,NAME,EMAIL,JOB_TITLE,HIRE_DATE) values ('1001','Rakesh','rakesh.job@xxxxx.com','Manager',SYSDATE)";
        /**To run the update query 
         * Just block the insert query above and unblock the update query below **/
		//Update query
        //String query="UPDATE TABLE EMPLOYEE SET EMAIL='test.mail@gmail.com' WHERE EMPNO='1001'";
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");         //Loading the driver
			conn=DriverManager.getConnection(url, username, password);//Creating the connection
			st=conn.createStatement();                                //Creating Statement
			int i=st.executeUpdate(query);                            //Executing the query
			System.out.println("Inserted rows :: "+i);
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(st!=null)
					st.close();                                     //Closing Statement
				if(conn!=null)
					conn.close();                                   //Closing Connection
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

}
    
	



5. Run the application

  • Follow the steps below to run the application.
Step 1
Hello Java
Right click on the java class.
Go to Run As --> Java Application.


  • See the output.
Console Output

        Inserted rows :: 1
    
	

  • Run a select query on this table like SELECT * FROM EMPLOYEE (from SQLDeveloper tool or any other tool)
Table View

Delete a row!

In this section we will delete a row from the EMPLOYEE table using JDBC.


A SQL delete query shared below where we are deleting a record from the table EMPLOYEE.
Delete from Table query

    DELETE FROM EMPLOYEE WHERE EMPNO='1001';
    
	

The code to delete data from table EMPLOYEE
JDBCDeleteExample.java

package com.javasetup.jdbc.examples;

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

public class JDBCDeleteExample {

	public static void main(String[] args) {
		Connection conn=null;
		Statement st=null;
		String url="jdbc:oracle:thin:@127.0.0.1:1521:xe";            //Database URL
		String username="java_setup"; 
		String password="java_setup";
		                                                              
		String query="DELETE FROM EMPLOYEE WHERE EMPNO='1001'";       //Delete query
		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");         //Loading the driver
			conn=DriverManager.getConnection(url, username, password);//Creating the connection
			st=conn.createStatement();                                //Creating Statement
			int i=st.executeUpdate(query);                            //Executing the query
			System.out.println("Deleted rows :: "+i);
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(st!=null)
					st.close();                                     //Closing Statement
				if(conn!=null)
					conn.close();                                   //Closing Connection
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
	}

}
    
	



5. Run the application

  • Follow the steps below to run the application.
Step 1
Hello Java
Right click on the java class.
Go to Run As --> Java Application.


  • See the output.
Console Output

        Deleted rows :: 1
    
	

  • Run a select query on this table like SELECT * FROM EMPLOYEE (from SQLDeveloper tool or any other tool)
Table View




References