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

 

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");
  

  

• For MySql,

  Connection connection = DriverManager
    .getConnection("jdbc:mysql://localhost:3306/brndb","mysql","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:

• JDBC Tutorial index
• Introduction to JDBC
• Different types of JDBC drivers
• JDBC Driver list for all leading databases
• Basic components of JDBC
• MySQL database connection steps
• Oracle database connection steps
• MS Access database connection steps

 

References:

• https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
• https://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28025
• http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
• https://en.wikipedia.org/wiki/JDBC_driver
• http://www.devx.com/tips/Tip/28818
• https://docs.oracle.com/javase/7/docs/api/java/lang/Class.html#forName(java.lang.String)

 

Happy Coding !!
Happy Learning !!