JDBC Statement Interface in Java with Example

Statement Interface in JDBC

In this jdbc tutorial, We are gonna to discuss jdbc Statement interface in java with example so that you can understand what is the roll of Statement interface in jdbc.

Jdbc Statement interface is used to execute queries with the database by using some methods which is provided by Statement interface.

After making connection with the database by using Connection interface we can get the object of a Statement interface by using createStatement() method of Connection interface e.g...

Statement stm = con.createStatement();

By using Statement interface methods, You can retrieve, insert, delete and update data into the database.

Mostly Used Methods of Statement Interface

There are some methods of Statement interface which is used to execute queries with the databse.

1) public ResultSet executeQuery(String sql)

It is used to execute the select query and returns the object of ResultSet.

2) public int executeUpdate(String sql)

It is used to execute the query like create, insert, delete and update.

3) public boolean execute(String sql)

It is used to execute query that may return multiple results.

4) public int[] executeBatch()

It is used to execute batch of commands.

Let's understand Statement interface by simple example.

Read this : Database Connectivity in Java with MySql

JDBC Statement Interface Example

In this example, we are going to fetch all the record from database table by executing select query with the help of executeQuery() method. You can execute query like create, insert, update, delete etc. But here we will execute only select query.

Suppose there is a table(student table with age and name) in the oracle database with some records.

import java.sql.*;

class Demo

{

public static void main(String args[])

{

try

{

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

Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe", "system", "bca");

Statement stm = con.createStatement();

ResultSet rs = stm.executeQuery("select * from student");

While(rs.next())

{

System.out.println(rs.getInt(1)+" "+rs.getString(2));

}

}

catch(Exception e)

{

System.out.println(e);

}

}

}

Here we saw a simple example of java jdbc Statement interface.

Read More

Steps To Connect To Database in Java

JDBC Connection Steps

Here we are going to discuss some steps to connect to database in java jdbc. If we want to connect java application with any database then we have to follow 5 steps using jdbc.

Let's jdbc connection in java with database using some 5 steps.

There are 5 Steps to Java Database Connectivity

1. Register or load the driver class.
2. Establish the connection.
3. Create the statement.
4. Execute the queries.
5. Close the connection.

Let's understand above mentioned jdbc database connection steps one-by-one.

1) Register or load the driver class

The first steps is to register or load the driver class by using forName()  method of Class class.

The forName() method is used to register or load the class dynamically in java.

Syntax of forName() :

public static void forName(String className)throws ClassNotFoundException

Example to load the OracleDriver class

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

2) Establish the connection 

In this step, we will create the connection object. By using getConnection() method of DriverManager class we can establish the connection with the database.

Syntax of getConnection() :

public static Connection getConnection(String url)throws SQLException

public static Connection getConnection(String url,String name,String password)throws SQLException

Example to establishing connection with oracle database


Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe","system","password");

3) Create the statement


The object of statement is used to execute the queries with the database. By using createStatement() method of Connection interface we can create statement in jdbc.

Syntax of createStatement() :

public Statement createStatement()throws SQLException

Example to create the statement object


Statement stm = con.createStatement();

4) Execute the queries

The executeQuery() method of Statement interface is used to execute the queries to the database and this method return the object of ResultSet that can be used to get all the records from the database table.

Syntax of executeQuery() :

public ResultSet executeQuery(String sql)throws SQLException

Example to execute query

ResultSet rs = stm.executeQuery("select * from student");

while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

5) Close the connection 

The close() method of Connection interface is used to close the connection.


Syntax of close() :

public void close()throws SQLException

Example to close() connection 

con.close();

Read More:

Java JDBC Example with Oracle Database.
Java JDBC Example with MySql Database.
Java ResultSet Interface in JDBC.

Here we saw all the steps for connection java application with database by using jdbc .

Read More

Database Connectivity in Java with MySQL

Jdbc Connection in Java with MySql

Now here, We are going to perform database connectivity in java with mysql line-by-line with simple example .

Here we will see first mysql jdbc example and then explanation of this example.

If you want to perform java jdbc connectivity with mysql database then you have to download mysql and install in your computer first and after installing mysql db. 

You have to download mysqlconnector.jar file or it can be comes with mysql package and then you have to set path(temporary or permanent path) of mysql connector java jar.

There are 3 ways to load the jar file

1) First way

paste the mysqlconnector.jar file into jre/lib/ext folder.

2) Second way - set temporary classpath

Open cmd and type : 

C:>set classpath =c:\mysql\mysql-connector-java-5.1.45-bin.jar;.;

3) Third way - set permanent path

Go to in MyComputer properties and then environment variables, click new tab and type classpath in variable name box and in variable value paste the mysqlconnector.jar file e.g c:\mysql\mysql-connector-java-5.1.45-bin.jar;.;

Let's start simple jdbc program in java using mysql database.

Let's create database and table in mysql.

create database anurag;//create db in mysql

use anurag;//select database

create table student(rollno int(10), name varchar(40));//create table

Java MySql Connection Example

In this mysql jdbc example we will fetch all the records from the student table.

import java.sql.*;

class MySqlJDBCExample

{

public static void main(String args[])

{

try

{

//Step 1, load driver class

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

//Step 2, create connection object

Connection con = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/anurag","root","root");

//Step 3, create statement object

Statement stm = con.createStatement();

//Step 4, execute query

ResultSet rs = stm.executeQuery("select * from student");

while(rs.next())

{

System.out.println(rs.getInt(1)+" "+rs.getString(2));

}

//Step 5, close connection object

con.close();

}

catch(Exception e)

{

System.out.println(e);

}

}

}

Explanation of above java jdbc mysql example

If you want to connect java program with mysql database using jdbc then you have to follow 5 steps e.g...

Driver Class : com.mysql.jdbc.Driver

Load the driver class.

Connection Url : jdbc:mysql://localhost:3306/anurag

Where jdbc is an api and mysql is the database and localhost is the server name in which mysql is running, we an use IP address also and 3306 is the port number and anurag is the database name, you can also select other databses.

Username : By default the "root" is the username of mysql database.

Password : User can give any password at the time of mysql instalation but in the above program we used root as the password.

You can check - How to connect java application with oracle database using jdbc.

Here we learned database connectivity in java with mysql using jdbc API.

Read More

Oracle Database Connection in Java

How To Connect Oracle Database in Java

Now here, we are going to see the program of Oracle database connection in java through JDBC(Java Database Connectivity) concept.

Here we will use Oracle10g database version for writing java JDBC example. We can connect our java application to any database such as MySQL, MSOffice, DB2, etc but here we will take only Oracle database example for connectivity to the java program.

5 Steps To Database Connectivity

As we know, we are going to use Oracle database here so we have to follow 5 steps for the oracle database connective. 5 steps are given below.

Driver Class : oracle.jdbc.driver.OracleDriver.

Where oracle is a package and jdbc is a sub package of oracle package and driver is a sub package of jdbc package and OracleDriver is a class which is defined in the driver package.

Connection Url : jdbc:oracle:thin:@localhost:1521:xe.

Where jdbc is an API and oracle is a database and thin is a driver and @localhost is the name of the server in which oracle is running we can also use IP address and 1521 is the port number and xe is the service name of oracle database.

Username : The default user name of Oracle db is system.

Password : At the time of installing Oracle database, the password is given by the user. You can give the desired password for your database.

Let's see how to connect java application with oracle database with a simple example.

First we have to create the table in oracle database for fetching all the records from the oracle database. 

create table student(rollno number(10), name varchar2(20));


by using above command you can create the table in oracle db.

Java Program For Oracle Database Connectivity

This is simple oracle jdbc connection example in java where we will use some steps to connect to the oracle database and fetch all the records from the particular table.

Here "system" is the default user name of oracle and "mca" is the password for oracle.

import java.sql.*;

class FetchRecords

{

public static void main(String args[])

{
try

{

//Step First, load the driver class

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

//Step Second, create the object of connection

Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe", "system", "mca");

//Step Third, create the statement object
Statement stm = con.createStatement();

//Step Four, execute query
ResultSet rs = stm.executeQuery("select * from student");

while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

//Step Five, close the connection object
con.close();
}

catch(Exception e)
{
System.out.println(e);
}
}
}

The above java jdbc oracle example will fetch all the records from the student table.

This is simple java program to connect oracle database to fetch all the records from the table.

We will see leter how to create table and insert, update and delete data from the table through java jdbc program.

In this post we saw java jdbc oracle connection example with step-by-step.

Read More