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

Related Articles :

• Java – Introduction to JDBC
• Java – JDBC Driver types
• Java – Core JDBC components
• Java – JDBC Driver list for all leading database
• Java – JDBC connection steps
• Java – An example to connect MySQL database
• Java – An example to connect Oracle database
• Java – An example to connect MS Access database
• Java 8 – An example to connect MS Access database in Java 8
• Java – JDBC program to connect IBM DB2 database running on Mainframe z/OS system
• Java – Creating database using JDBC Statement interface
• Java – Droping database using JDBC Statement interface
• Java – Creating a table using JDBC Statement interface
• Java – Inserting a record using JDBC Statement interface
• Java – Getting all list of records using JDBC Statement interface
• Java – Getting single record using JDBC Statement interface
• Java – Updating a record using JDBC Statement interface
• Java – Deleting a record using JDBC Statement interface
• Java – Dropping a table using JDBC Statement interface
• Java – Batch update using JDBC Statement interface
• Java – Batch insert using JDBC Statement interface
• Java – Creating a table using JDBC PreparedStatement interface
• Java – Inserting a record using JDBC PreparedStatement interface
• Java – Getting all list of records using JDBC PreparedStatement interface
• Java – Getting single record using JDBC PreparedStatement interface
• Java – Updating a record using JDBC PreparedStatement interface
• Java – Deleting a record using JDBC PreparedStatement interface
• Java – Batch update using JDBC PreparedStatement interface
• Java – Batch insert using JDBC PreparedStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface
• Java – Calling Stored Function using JDBC CallableStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution
• Java – Transaction handling using JDBC Statement interface
• Java – Transaction handling using JDBC PreparedStatement interface
• Java – Integration with Spring framework (Spring JDBC)
• Java – Where clause example using JDBC Statement interface
• Java – Like clause example using JDBC Statement interface
• Java – Order by clause example using JDBC Statement interface
• Java – Metadata of database using JDBC DatabaseMetaData interface
• Java – Metadata of Resultset using JDBC ResultSetMetaData interface
• Java – Interview question and answer on JDBC

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 !!