In the previous couple of posts What is Java Database Connectivity (JDBC)? and Types of JDBC drivers in java?
We got to know some Jdbc basics.
In this post, we will learn how to connect a java program with a database. Here I am using the MYSQL database but you are free to choose any database you like to use. Steps would be similar for another database as well.
JDBC connection steps for MySQL database.
- Register JDBC driver class
- Creating Jdbc connection object
- Creating Jdbc statement object
- Executing SQL queries
- Closing connection object
1. Register JDBC driver class
The forName() method of java.lang.Class class is used to register the JDBC driver class. This method is used to dynamically load the JDBC driver class. Note that forName method is static so directly we can refer this method my class name.
Syntax of forName() method
public static Class<?> forName(String className) throws ClassNotFoundException
Note: From JDBC 4.0 onwards explicitly registering the Jdbc driver is optional. We just need to put database vender’s provided Jar in the project classpath, and then JDBC driver manager can detect and load the driver automatically.
Blow code snippet shows how we load MySQL database driver to establish database connection
Class.forName(“com.mysql.jdbc.Driver”);
2. Creating Jdbc connection object
We have three Jdbc methods available in java.sql.DriverManager class to connect jdbc program with any database.
- public static Connection getConnection(String url,String user, String password) throws SQLException
- public static Connection getConnection(String url,java.util.Properties info) throws SQLException
- public static Connection getConnection(String url) throws SQLException
These all methods are static so we can refer it directly by Class Name.
All above three variants of getConnection() methods you can easily understand by looking at blow example covered.
3. Creating Jdbc statement object
Once we get Connection object using any one of method mentioned in step 2 we can create an object of jdbc statement.
We have hands full of methods available in Connection interface
Like below code snippet shows how we can create java.sql.Statement object using createStatement() method.
Statement st=con.createStatement();
Where con is an object or reference of java.sql.Connection Interface
4. Executing SQL queries
We have three execute(),executeUpdate() and executeQuery methods available in Jdbc to execute SQL query.
To learn how to make use of these method you can refer CRUD(Create,Read,Update and Delete) Operation using JDBC Statement and CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement
5.Closing connection object
If we close connection object statement then Statement and ResultSet objects will be closed automatically.The java.sql. Connection interface is used to close the connection by calling close() method.
Syntax of close() method
void close() throws SQLException;
Code snippet to close connection
conn.close();
Where conn is an object of java.sql.Connection Interface.
Note: Fron Java 7, JDBC can be used to try-with-resources statement to automatically close resources of type java.sql .Connection, java.sql.ResultSet, and java.sql.Statement.
As in this program, we are going to connect the java program with the MySQL database and not going to execute any SQL query so steps 3 and 4 are not involved.
Approach 1:
In this example, we have used below getConnection method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException
This method takes three parameters database URL,username and password.
Here in all examples I have used MySQL database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL = “jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
package com.infotech.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver"; private static final String DB_USERNAME="root"; private static final String DB_PASSWORD="root"; private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb"; private static Connection connection = null; static{ try { Class.forName(DB_DRIVER_CLASS); connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return connection; } } |
Approach 2:
In this approach, we have used below getConnection method of java.sql.DriverManager class
public static Connection getConnection(String url,java.util.Properties info) throws SQLException
This approach is very much similar to approach 1 except we have used here getConnection() method which takes two parameters where first parameter is the database URL and the second parameter is java.util.Properties reference where we are just setting database username & password
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
package com.infotech.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class DBUtil2 { private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver"; private static final String DB_USERNAME="root"; private static final String DB_PASSWORD="root"; private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb"; private static Connection connection = null; static{ try { Class.forName(DB_DRIVER_CLASS); Properties properties = new Properties(); properties.put("user", DB_USERNAME); properties.put("password", DB_PASSWORD); connection = DriverManager.getConnection(DB_URL, properties); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return connection; } } |
Approach 3:
In this approach, we have used the below getConnection method of java.sql.DriverManager class
public static Connection getConnection(String url) throws SQLException
This approach is very much similar to the above two approaches except we have used here getConnection() method which takes one String parameter in which we have concatenated database URL, username, and password as below and passed to getConnection(String url) method.
jdbc:mysql://localhost:3306/jdbcdb?user=root&password=root
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
package com.infotech.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class DBUtil3 { private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver"; private static final String DB_USERNAME="root"; private static final String DB_PASSWORD="root"; private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb"; private static Connection connection = null; static{ try { Class.forName(DB_DRIVER_CLASS); Properties properties = new Properties(); properties.put("user", DB_USERNAME); properties.put("password", DB_PASSWORD); connection = DriverManager.getConnection(DB_URL+"?"+"user="+DB_USERNAME+"&"+"password="+DB_PASSWORD); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return connection; } } |
Now We have Client program which makes use of above DBUtil classes to retrieve database connection.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
package com.infotech.client; import java.sql.Connection; import com.infotech.util.DBUtil; import com.infotech.util.DBUtil2; import com.infotech.util.DBUtil3; public class ClientTest { public static void main(String[] args) { Connection connection = DBUtil.getConnection(); if(connection != null) System.out.println("JDBC:connection is taken.."); Connection connection2 = DBUtil2.getConnection(); if(connection2 != null) System.out.println("JDBC:connection is taken.."); Connection connection3 = DBUtil3.getConnection(); if(connection3 != null) System.out.println("JDBC:connection is taken.."); } } |
When you run ClientTest .java in your favorite IDE then you will get below out.
1 2 3 |
JDBC:connection is taken.. JDBC:connection is taken.. JDBC:connection is taken.. |
This shows we successfully connected our java program with MYSQL database.
That’s all about this topic JDBC MySQL database Connection example
You May Also Like:
What is Java Database Connectivity (JDBC)?
Types of JDBC drivers in java?
JDBC database connection using property resource(property file)
CRUD(Create,Read,Update and Delete) Operation using JDBC Statement
Reading data from database using JDBC Statement
CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement
If you have any feedback or suggestion please feel free to drop them in below comment box.