java.sql Package

Before jumping into the database operation let us know about java.sql package which we will be using in our Java program for connecting to the database.

java.sql package provides APIs for data access and data processing in a relational database using Java programming language. There are some important interfaces and classes that come under java.sql package.

Interfaces

• Connection
• Statement
• PreparedStatement
• CallableStatement
• ResultSet
• ResultSetMetaData

Class

• DriverManager class

Creating a user in Oracle Database and granting required permissions : 

• Open oracle using cmd. For that type sqlplus in cmd and press Enter.
• Create a user-id protected by a password. This user-id is called child user. 

create user  identified by ;

• Grant required permissions to child user. For simplicity we grant database administrator privilege to child user. 

conn / as sysdba;
grant dba to ;

Create a sample table with blank fields : 

CREATE TABLE userid(
    id varchar2(30) NOT NULL PRIMARY KEY,
    pwd varchar2(30) NOT NULL,
    fullname varchar2(50),
    email varchar2(50)
);

Principal JDBC interfaces and classes

Let’s take an overview look at the JDBC’s main interfaces and classes which we’ll use in this article. They are all available under the java.sql package: 

• Class.forName() : Here we load the driver’s class file into memory at the runtime. No need of using new or creation of object. 

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

• DriverManager: This class is used to register driver for a specific database type (e.g. Oracle Database in this tutorial) and to establish a database connection with the server via its getConnection() method.
• Connection: This interface represents an established database connection (session) from which we can create statements to execute queries and retrieve results, get metadata about the database, close connection, etc. 

Connection con = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");

• Statement and PreparedStatement: These interfaces are used to execute static SQL query and parameterized SQL query, respectively. Statement is the super interface of the PreparedStatement interface. Their commonly used methods are:
  1. boolean execute(String sql): executes a general SQL statement. It returns true if the query returns a ResultSet, false if the query returns an update count or returns nothing. This method can be used with a Statement only.
  2. int executeUpdate(String sql): executes an INSERT, UPDATE or DELETE statement and returns an update account indicating number of rows affected (e.g. 1 row inserted, or 2 rows updated, or 0 rows affected). 

Statement stmt = con.createStatement();
      String q1 = "insert into userid values
      ('" +id+ "', '" +pwd+ "', '" +fullname+ "', '" +email+ "')";
      int x = stmt.executeUpdate(q1);

• ResultSet executeQuery(String sql): executes a SELECT statement and returns a ResultSet object which contains results returned by the query. 

Statement stmt = con.createStatement();
      String q1 = "select * from userid WHERE id = '" + id + "' 
      AND pwd = '" + pwd + "'";
      ResultSet rs = stmt.executeQuery(q1);

• ResultSet: contains table data returned by a SELECT query. Use this object to iterate over rows in the result set using next() method.
• SQLException: this checked exception is declared to be thrown by all the above methods, so we have to catch this exception explicitly when calling the above classes’ methods.

In this article, we will be learning about how to do basic database operations using JDBC (Java Database Connectivity) API in Java programming language. These basic operations are INSERT, SELECT, UPDATE, and DELETE statements in SQL language. Although the target database system is Oracle Database, the same techniques can be applied to other database systems as well because the query syntax used is standard SQL and is generally supported by all relational database systems.

Prerequisites:

• JDK
• Oracle Database (Download Oracle Database Express Edition 11g release 2)
• JDBC driver for Oracle Database (Download Oracle Database 11g release 2 JDBC drivers). You need to add ojdbc6.jar to project library.

You need to go through this article before continuing for a better understanding.