JDBC Guide

Memorize the following order.

  1. Loading the JDBC driver
  2. Getting a Connection
  3. Execute SQL
  4. [If the SQL statement is a select statement, use a ResultSet.]
  5. Returning Resources

1. Loading the JDBC driver

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

The string in parentheses indicates the start class of the Oracle JDBC driver. In the ojdbc6.jar file, you can find this. Class is not a class that belongs to JDBC and is used to get class information. The forName() method is a static method of the Class class. The forName() method allows the class loader to load the class corresponding to the string input as an argument into memory space. There is a return value for the forName() method but not necessary for JDBC programming. When this method runs, the class loader refers to the classpath and loads it into memory space. If the class loader cannot find the corresponding one, it causes a ClassNotFoundException. In conclusion, the above code responsible for loading the OracleDriver into memory. The loaded OracleDriver class registers itself with java.sql.DriverManager. After that, you call getConnection() of DriverManager and pass appropriate values as arguments to this method, and it will return Oracle Connection implementation instance.

2. Getting a Connection

conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "scott", "tiger");

The JDBC API is mostly an interface.
In our example, they are all interfaces exception DriverManager.
DriverManager's getConnection() method returns a Connection object implemented by a specific database vendor according to values entered as arguments. Obtaining a Connection object means that running your java program has connected to the database.

3. Execute SQL

// Getting a Statement
stmt = conn.createStatement();
// Execute SQL
rs = stmt.executeQuery(query);

Since the Java program had connected to the database, you now need the code to pass the SQL statement to the DBMS. To deliver an SQL statement to a database, you must use either a Statement implementation or a PreparedStatement implementation. You can create a Statement implementation or a PreparedStatement implementation through the Connection implementation. If the SQL statement is a select statement, use the executeQuery() method of the Statement interface. If the SQL statement is for an insert, update, or delete record, use the executeUpdate() method.

4. [If the SQL statement is a select statement, use a ResultSet.]

while (rs.next()) {
  String empno = rs.getString(1);
  String ename = rs.getString(2);
  ...
  ..

If the SQL statement is a select statement, you need an object to store the returned records. ResultSet acts as a container for the returned records from a database. Regard ResultSet as a collection that is only used by JDBC to store table-like data. The returned ResultSet object has a cursor whose initial position is before the first record. Therefore, to let the cursor point to the first record, you have to call the next() method. When the next() method runs, it moves the cursor down one level and returns true if there is a record at the cursor point.

5. Returning Resources

In JDBC programming, returning resources is essential.
The garbage collector works when the instance is no longer in use. However, it does not collect that waiting for data from the user. If you don't return connection instances that no longer in use, they will increasingly take up heap memory, and the server will stop eventually. Therefore, in JDBC Programming, you have to write code that returns used resources. Write the following code in a finally block.

// Close the ResultSet.
rs.close();
// Close the Statement.
stmt.close();
// Close the Connection.
conn.close();

You have to return the resources order in the reverse order of the generated.