How to Connect MySQL Database in Java

Connect MySQL Database in Java

Since Java is one of the more popular programming languages, it naturally offers strong support for databases. Using JDBC (Java Database Connectivity), we are able to establish connections to numerous databases. Here, you'll learn how to use JDBC to communicate with a database while running queries.

Introduction

JDBC is the default Java API for establishing connections to common relational databases. Here, you'll learn how to utilize Java as well as JDBC to connect to MySQL databases hosted on Azure.
 

In this post, we'll cover two different kinds of authentication: those based on Azure Active Directory (Azure AD) as well as those based on MySQL. To see how Azure Active Directory authentication works, click the Passwordless tab, while the Password tab displays MySQL authentication.
 

Linking to Azure Database for MySQL with an Azure Active Directory account is possible via the Azure AD authentication mechanism. Using Azure Active Directory (AD) authentication, databases as well as other Microsoft services can have their user identities and permissions managed in one place.

MySQL logs in users with MySQL accounts. 

Image source google

Table of Contents:

• Introduction to JDBC
• Common JDBC Components
• Steps to create JDBC Application
• JDBC Connections

Introduction to JDBC

When it comes to connecting the Java programming language as well as various databases, JDBC makes up one of the mainstream Java APIs. You can use SQL to encode your access request statements with this API. The primary steps here are connection establishment, database creation, SQL query execution, and finally output generation.

 

Every tabular data saved in a relational database is accessible using the JDBC API. You can use this to make changes to, save to, retrieve from, as well as delete information from your database. It's very much like Microsoft's ODBC (Open Database Connectivity) service.

Let's delve deeper into the issue and examine the framework underlying Java Database Connectivity in order to get a better feel for how JDBC operates.
 

Want to become a Certified Java Professional? Go through the MindMajix’s Java Spring Training.

Common JDBC Components

Interfaces as well as classes provided by the JDBC API are as follows:

• DriverManager: 

Primarily, this is where you'll take care of your database driver inventory. When connecting to a database, the sub-protocol-aware driver can be the one to do the heavy lifting.

• The Operator: 

It's a front end for talking to a remote database server. It additionally hides the implementation details whereas dealing with Driver instances.

• A connection:

It's an API that includes all the techniques for interacting with a database. The connection object handles all of the database's messaging operations. context.

Moving on to another section, we'll examine the necessary actions for developing a JDBC Application.

Steps to establish JDBC Application

There are a few measures you must take to make a JDBC Application. What are they, exactly?

• Import packages:

In order to use JDBC classes for programming databases, you must import all the relevant libraries. In most cases, import java.sql.* will do the trick.

• Prepare the JDBC driver for use:

It is necessary to set up a driver here in order to establish a connection to the data source.

• Create a link:

Build a Connection object, that represents the physical link to the database, by calling its getConnection() function.

• To conduct a search:

For this to work, you'll need to construct and execute a SQL statement to the database using an object of kind Statement.

• Data mining the final set of results:

To find the data from the end result set, you need to utilize the proper getXXX() method.

• Clean up environment:

Here, you can't rely on the JVM's garbage collection and must instead explicitly delete all database resources.

 
Now that you know what's required to make a JDBC Application, let's look at some sample code to set up a database as well as connect to it.

 

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class RetrieveDataFromDatabase {
    public static void main(String[] args) {
        // Database connection information
        String jdbcUrl = "jdbc:mysql://localhost:3306/your_database";
        String username = "your_username";
        String password = "your_password";

        // SQL query to retrieve data
        String sqlQuery = "SELECT id, first_name, last_name, age FROM your_table";

        try (
            // Establish a database connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Create a prepared statement with the SQL query
            PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery);

            // Execute the query and retrieve the ResultSet
            ResultSet resultSet = preparedStatement.executeQuery()) {

                // Iterate through the ResultSet and print the data
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String firstName = resultSet.getString("firstName");
                    String lastName = resultSet.getString("lastName");
                    int age = resultSet.getInt("age");

                    System.out.println("ID: " + id);
                    System.out.println("First Name: " + firstName);
                    System.out.println("Last Name: " + lastName);
                    System.out.println("Age: " + age);
                    System.out.println("--------------------");
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }
                
    }
}

 

 

This is the way you connect to the database and update the tables with new data. Let's take this a step further by learning about the many kinds of JDBC drivers.

Image source Google

Types of JDBC Driver 

Connecting to a database server requires a JDBC driver, which is software that implements the JDBC API's predefined interfaces. There are three primary functions of a JDBC driver.

• Connects to a data source in the first place.
• It will communicate with the data source by issuing queries as well as update statements.
• The third and last step is processing the data.

Using JDBC drivers, you can connect to a database plus issue SQL queries and other database operations. This article about JDBC Drivers might help you learn more regarding the many options available to you.

The next step is to learn about JDBC Connections.

JDBC Connections

• JDBC Package Importation:

Include import lines in the Java code to bring in the necessary classes.

• Installing a JDBC Driver:

To process JDBC requests, JVM now loads the appropriate driver code into RAM. A motorist may enroll in one of two ways.

• Using Java's forName() method to dynamically load the driver's class file into memory and enroll it is the best practice for driver registration. The flexibility and portability of the driver registration system make this approach a good fit. Read the following piece of code:
  

try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException ex) {
    System.out.println("Error: unable to load driver class!");
    System.exit(1);
}

• The static registerDriver()method is another option for driver registration. 

try {
    Driver myDriver = new oracle.jdbc.driver.OracleDriver();
    DriverManager.registerDriver( myDriver );
} catch (ClassNotFoundException ex) {
    System.out.println("Error: unable to load driver class!");
    System.exit(1);
}

 

If you're using a JVM that isn't JDK compliant, like Microsoft's, you need to call registerDriver(). Each of the forms here necessitates a unique database address.

• Database URL Formulation:

In order to establish a connection to the database of your choosing, you will need to formulate a valid Uniform Resource Locator (URL). The DriverManager.getConnection() method allows you to connect once you've loaded the driver. Techniques for the DriverManager.getConnection() are−

• getConnection(String url)
• getConnection(String url, Properties prop)
• getConnection(String url, String user, String password)

• Build a connection object

Connecting to a database is as simple as entering the database's URL, username, as well as password, or by utilizing the properties object.

• Close

Finally, disconnect all database connections to finish the session. In the event that you do forget, the Java garbage collectors will terminate the connection as part of its routine maintenance of unused resources.

conn.close(); // Used to close the connection.

Conclusion

The user table is where you'll keep track of the passwords you utilize for the accounts. Since MySQL is where you'll keep these passwords, you're going to be responsible for rotating them on your own.