Java JDBC: Connection Steps

In this article, we will learn and understand the steps to connect database using JDBC API from Java application

 

JDBC connection steps

Step 1: Loading or registering driver class
Step 2: Opening database connection

  • Step 2.A: Creating and getting connection
  • Step 2.B: Creating JDBC Statement (or PreparedStatement or CallableStatment)
  • Step 2.C: Executing SQL and MySql queries

Step 3: Closing database connection

 

3-JDBC-connection-steps

Let us see each steps briefly with their syntax and code examples

Step 1: Loading or registering driver class

  • static forName() method of Class class is used to load/register respective driver class for JDBC
  • Syntax:
    Class.forName(“driverClassName”);
    
  • Example for Oracle
    Class.forName(“oracle.jdbc.driver.OracleDriver”);
    
  • Example for MySql
    Class.forName(“com.mysql.jdbc.Driver”);
    

Step 2: Opening database connection

  • This is again split into 3 steps for better understanding purpose

Step 2.A: Creating and getting connection

  • Once after loading / registering required driver, we need to get connection to database before actually executing any SQL queries
  • Syntax: 
    Connection connection = DriverManager
      .getConnection(“dbURL”, ”username”, “password”);
    
  • For Oracle,
    Connection connection = DriverManager
      .getConnection("jdbc:oracle:thin:@localhost:1521:xe","scott","tiger");
    

    4-Java-JDBC-Database-URL-explanation-diagrammetrically-oracle

  • For MySql,
    Connection connection = DriverManager
      .getConnection("jdbc:mysql://localhost:3306/brndb","mysql","mysql");
    

    5-Java-JDBC-Database-URL-explanation-diagrammetrically-mysql

Step 2.B: Creating JDBC Statement/PreparedStatement/CallableStatment

  • There are 3 types of Statement available namely Statement, PreparedStatement, CallableStatement
  • Each one used for different purpose like,
  • Statement : For creating statement, we can createStatement(); method of Connection interface
    Statement statement = connection.createStatement();
    
  • PreparedStatement : Similarly, prepareStatement(); method of Connection interface for pre-compiled statement
    PreparedStatement preparedStatement = connection
      .prepareStatement("INSERT INTO EMPLOYEE(Id, Name) VALUES(?,?)");
    
  • Callable Statement : prepareCall(); method of Connection interface for calling stored procedure or stored function
  • For calling stored procedure,
    CallableStatement callableStatement = connection
      .prepareCall("{call Employee_procedure(?,?,?)}");
    
  • For calling stored function,
    CallableStatement callableStatement = connection
      .prepareCall("{ ? = call Employee_function(?,?,?)}");
    
  • LHS question mark (?) is used to get return value from stored function
  • Note: Syntax between calling a stored procedure and stored function is bit different

Step 2.C: Executing SQL and MySql queries

  • To execute queries, use executeUpdate() or executeQuery() methods of Statement interface
  • Syntax:
    ResultSet rs = statement.executeQuery("SELECT * FROM EMPLOYEE");
    

Step 3: Closing database connection

  • Finally close connection after executing and completing all database operations
  • Closing connection, implicitly closes resultset and statement too
  • Note: It’s a good practice to explicitly close ResultSet and Statement in finally block of try-catch-finally sequence
  • Syntax:
    connection.close();
    

 

Read Also:

 

References:

 

Happy Coding !!
Happy Learning !!

Java JDBC: An example to connect MySQL database
Java JDBC: Core Components