How to Save Records in Oracle10g Database Using Java

Introduction

 

This article shows how to save data in a database in a simple program in Java. We use an Oracle 10g database and Java 1.7; for connectivity we use a pure Java (Type 4) driver provided by Oracle.

 

What is a pure Java (Type 4) driver

 

A pure Java (Type 4) driver is a JDBC (Java DataBase Connectivity) Thin Client driver; it is used by Java applications to make a connection with databases. It interacts directly with the database using a DB specific protocol.

 

The main advantage of this driver is that in this driver we don't need "ODBC" or a "Native" driver. It provides better performance as compared to other drivers.

 

The following procedure is necessary to connect a Java application with a database:

1. The DriverClass is required with the DriverManager.
2. A Connection object is created.
3. A Statement object is created.
4. Queries are executed.
5. The Connection is closed.

Save Data in Oracle Database using Java

 

Now for each step for a better understanding of JDBC.

 

1. DriverClass is required

 

Each JDBC driver provides a class called a driver class. This class contains information that is required by the driver manager to manage the driver. The Driver class is a part of the driver.

 

In a Type 4 driver the JDBC Driver of the Oracle Database class is:

• "oracle.jdbc.odbc.oracleDriver"

Note:

 

All the driver classes are defined in such a way that they contain code to register themselves with the DriverManager in their static block, in other words, to register a driver class with the DriverManager. It simply needs to be loaded.

 

2. Connection Object is created

 

The DriverManager class provides the following factory methods for creating connection objects.

• public static Connection getConnection(String URL) throws SQLException;
• public static Connection getConnection(String URL, String user, String password) throws Exception

The URL is "jdbc:oracle:thin:@hostname:port:","user","password".

 

3. Statement Object is created

 

The Connection Interface provides the following methods for creating a statement.

 

public Statement createStatement() throws SQLException;

 

4. Queries are executed

 

The Statement interface provides the following methods for executing queries.

• public ResultSet execute Query (String selectQuery)throws Exception;
• public int executeUpdate(String NonSelectQuery)throws Exception;
• public void execute(String Query)throws Exception; 

ResultSet

 

This object contains the recordlist returned by the query to traverse the records; it has a record pointer initialized at the beginning of the ResultSet.

 

This interface provides a method named next to advanced the record pointer by one record at a time.

 

Syntax

• public boolean next();

To read the value of individual fields of current records, the ResultSet methods provide a getType method. The general signatures are:

• public type getType(int fieldIndex) throws Exception;

Note: fieldIndex always starts with 1.

 

5. Close the connection

 

The Connection interface provides a method name, close, to close the connection.

• public void close() throws Exception;

For our program we need to understand one more term.

 

PreparedStatement

 

This statement is used to execute parameterized queries. A parameterized Statement object is created using the following method of the connection interface.

• public PreparedStatement prepareStatement(String QuerywithParameters) throws Exception;

example-> PrepareStatement stmt=con.preparedStatement (Select * from emp where salary>?);

 

Before executing a parameterized query using PreparedStatement, a value for the parameter must be set.

 

To set the value of parameters, preparedStatment provides a setter method, the general signature of these method is:

• public void setType(int paramIndex, type value) throws Exception;

Now start our program

 

We need to first create a table in the Oracle Database with the name "employees".

 

For creating the table, open the Oracle database Homepage in a web browser. Now login with your user name and password. As I create a new user "sandeep" and set the password as "welcome". As in the following:

 

 

After login, click on "Object Browser" -> "Create" -> "Table", as in the following.

 

 

Now click on table; a window is generated asking for the table details, as in the following.

 

 

Now click on "Next" and leave the details in the next window. In the final window you get an option, create; click on that and your table is generated.

 

Now create a Java file with the following code.

 

InsertTest.java

import java.sql.*;
import java.io.*;
class InsertTest
{
    public static void main(String args[])
    {
        try
        {
            //DriverClass is registered..
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //Connection object is created...
            Connection
            con = DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521:xe", "sandeep", "welcome");
            //Statement Object is created.....
            PreparedStatement stmt = con.prepareStatement("insert into employees values(?,?,?,?)");
            //Query is executed with the helped of input data....BufferedReader is used to get the input from keyboard using the user....
            BufferedReader b = new BufferedReader(new InputStreamReader(System.in));
            //infinite loop is started...
            while (true)
            {
                System.out.println("Enter id");
                int id = Integer.parseInt(b.readLine());
                System.out.println("Enter name:");
                String n = b.readLine();
                System.out.println("Enter job:");
                String j = b.readLine();
                System.out.println("Enter salary:");
                int s = Integer.parseInt(b.readLine());
                stmt.setInt(1, id);
                stmt.setString(2, n);
                stmt.setString(3, j);
                stmt.setInt(4, s);
                stmt.executeUpdate();
                System.out.println("Want to insert more data Yes/No");
                String ans = b.readLine();
                //if user say no then insertion of data stopped...
                if (ans.equalsIgnoreCase("No"))
                    break;
            }
            //Connection is closed.....
            con.close();
            System.out.println("Successfully saved .....");
        } catch (Exception ex)
        {
            System.out.println(ex);
        }
    }
}

Note

 

This driver is provided by Oracle, not by Sun Microsystems so we need the ojdbc.jar file to execute the program.

 

How to get the ojdbc.jar file

 

This jar file exists in the OracleXE folder (as in "C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib").

 

For our convenience, we can copy this file and paste it onto the Desktop in a new folder and set the classpath forit. I placed it in a folder named "jarfiles".

 

Set the classpath

 

Go to the environment variables then click on the "New" tab. In the variable name write the classpath and in the variable value paste the path to the ojdbc14.jar (like file://mcnserver2/UserProfiles/ssharma/Desktop/jarfiles/ojdbc14.jar; ) as in the following.

 

 

Output

 

Now run our program. It will now compile and run fine, without an error.

 

 

Now press Enter and provide data for your table. I provided a three column name there, as in the following.

 

 

Now check in the Oracle server that the data was inserted.

 

Now again open the Oracle Server, then click on "Object Explorer" -> "Browse" -> "Table" -> "employess" then click on "Data" and see that our table contains the three column that we are inserted, as in the following. If you get the same result then that means you have done it.

 

 

Thanks for reading.