Java Database Connectivity

To connect any Java application to a database using JDBC, follow these five steps. These are the steps to take:

• Register the Driver class
• Create connection
• Create statement
• Execute queries
• Close connection

Register the driver class:

The Class class’s forName() method is used to register the driver class. This method is used to load the driver class dynamically.

Syntax:

public static void forName(String className)throws ClassNotFoundException  

For instance:

Class.forName("oracle.jdbc.driver.OracleDriver");  
OR
Class.forName ("com.mysql.jdbc.Driver");

Create a connection object:

The DriverManager class’s getConnection() method is used to connect to the database. This method has several overloaded methods that can be used depending on the situation. To establish a connection, the database name, username, and password are required.

Syntax:

public static Connection getConnection(String url)throws SQLException  

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

Example:

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

OR

Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost/test", "username", "password");

Create a Statement object:

To create a statement, use the Connection interface’s createStatement() method. The statement’s object is in charge of running queries against the database.

Syntax:

public Statement createStatement()throws SQLException 

For instance:

 stmt = conn.createStatement();

Execute the query:

The executeQuery() method of the Statement interface is used to execute database queries. This method returns a ResultSet object that can be used to retrieve all the records in a table.

Syntax:

public ResultSet executeQuery(String query) throws SQLException

Example:

 stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT foo FROM bar");
if (stmt.execute("SELECT foo FROM bar")) {
        rs = stmt.getResultSet();
    }

Close the connection object:

By closing the connection object statement, the ResultSet will be automatically closed. The Connection interface’s close() method is used to close the connection.

Syntax:

public void close() throws SQLException

Example:

con.close();  

Simple example with all steps:

import java.sql.*;

public class FirstExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(QUERY);) {
         // Extract data from result set
         while (rs.next()) {
            // Retrieve by column name
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Note: also read about the JDBC API(java.sql Package)

Follow Me

If you like my post, please follow me to read my latest post on programming and technology.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py