Java Database Connectivity with MySQL

By: Rajesh P.S.

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.

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");

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)

Connection connect=DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","username","password");

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

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.

Conclusion

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()) { // ... }

Next > Can you overload main() method in Java?

Related Topics

More Related Topics.....