Java Database Connectivity with MySQL
There are several approaches to establish a connection between Java and a MySQL database. MySQL is a widely used Open Source RDBMS (Relational Database Management System) renowned for its speed, versatility, and dependability, making it a popular choice for web applications. Java developers can utilize various methods to interact with MySQL databases, enabling seamless integration and efficient data retrieval and manipulation.
To connect and interact with a MySQL database from Java, the Java Database Connectivity (JDBC) API comes into play. This powerful API enables developers to establish connections to various databases, including Oracle, Microsoft SQL Server, and MySQL, as long as they have the appropriate JDBC driver implementation from the database vendor. The JDBC driver acts as a bridge between the Java application and the MySQL database, allowing seamless communication and data manipulation.
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
To register a JDBC driver in your Java program, you can use the Class.forName() method from the java.lang.Class class. This method loads the JDBC driver class and registers it with the DriverManager, allowing you to establish a connection to the MySQL database.
However, The Class.forName() method is compatible only with JDK-compliant Java virtual machines and cannot be used with Microsoft Java virtual machines. For the latter case, you can utilize the DriverManager.registerDriver() method. The DriverManager class offers a fundamental service for handling a collection of JDBC drivers, allowing you to manage and load drivers for database connectivity in your Java program.
Establishing a connection between java and database (URL Formulation)
Registering the driver is essential to ensure that the Java program can create a correctly formatted address that directs to the desired database for connection. After loading the driver, you can establish the actual connection to the database using the static getConnection() method of the JDBC DriverManager class. This method facilitates the establishment of a connection to the specified database, allowing the program to interact with the data stored within.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.