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