Java Database Connectivity with MySQL
There are many ways you can connect to a MySQL database from Java. MySQL is a popular Open Source RDBMS (Relational Database Management System) commonly used in web applications due to its speed, flexibility and reliability.
In order to connect and access the MySQL database from Java, you can use Java Database Connectivity (JDBC) API. It allows you to connect to any database like Oracle, Microsoft SQL Server, or MySQL, provided you have the vendor's implementation of the JDBC driver interface, which is required to connect the database.
Steps to develop JDBC application
- Import JDBC Packages
- Loading and Register a driver
- Establishing a connection between java and database
- Creation of statement object
- Send and Execute SQL Query
- Processing the Result Set
- Closing Connections
Import JDBC Packages
You must include the import statements at the beginning of your program, which allows you to select, insert, update, and delete data in SQL tables.
Loading and Register a driver
You have to provide the code to register your installed driver with your program. You can register a driver in one of two ways. The most common approach to register a driver is to use Java's Class.forName() method of the java.lang.Class class to load the JDBC drivers directly.
However, Class.forName() method is valid only for JDK-compliant Java virtual machines. It is not valid for Microsoft Java virtual machines. In this case you can use DriverManager.registerDriver() method. This class provides a basic service for managing a set of JDBC drivers.
Establishing a connection between java and database (URL Formulation)
This step is necessary to create a properly formatted address that points to the database to which you want to connect. Once you loaded the driver, you can establish a connection to the database with the static getConnection() method of the JDBC DriverManager class. Java provide three overloaded DriverManager.getConnection() methods:
- getConnection(String url)
- getConnection(String url, Properties prop)
- getConnection(String url, String username, String password)
The getConnection() method method returns an object of the JDBC Connection class which needs as input a userid, password, connect string that identifies the JDBC driver to use, and the name of the database to which you want to connect.
Creation of statement object
The createStatement() method of your JDBC Connection object returns an object of the JDBC Statement class. When you execute Statement objects , it generate ResultSet objects, which is a table of data representing a database result set.
Send and Execute SQL Query
To query the MySql database , use the executeQuery() method of your Statement object. This method takes a SQL statement as input and returns an object of the JDBC ResultSet class.
Processing the Result Set
If you want to process the result set, to pull data out of the result set and iterate through it. You can use the next() method of your ResultSet object to loop through the results. This method iterate through the result set row by row, detecting the end of the result set when it is reached.
Finally, to end the database session , you need to close all the database resources which immediately release the resources it's using.
Alternatively, you can use a try-with-resources statement to automatically close the resources regardless of whether an SQLException has been thrown.