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.
import java.sql.*;
class JavaMysqlConnection{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection connect=DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","username","password");
Statement stmt=connect.createStatement();
ResultSet rs=stmt.executeQuery("select * from students");
while(rs.next())
System.out.println(rs.getString(1)+" "+rs.getInt(2)+" "+rs.getString(3));
connect.close();
}catch(Exception e){ System.out.println(e);}
}
}
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
import java.sql.* ; // for standard JDBC programs
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.
import java.math.* ; // these are required for the BigDecimal classes
Loading and Register a driver
Class.forName("com.mysql.jdbc.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)
Connection connect=DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","username","password");
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)
Creation of statement object
Statement stmt=connect.createStatement();
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
ResultSet rs=stmt.executeQuery("select * from students");
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
while(rs.next())
System.out.println(rs.getString(1)+" "+rs.getInt(2)+" "+rs.getString(3));
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.
Closing Connections
connect.close();
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.
try (Statement stmt = con.createStatement()) {
// ...
}
Related Topics