MySQL - Java Syntax

Table of content

To communicate with databases Java provides a library known as JDBC (Java Database Connectivity). JDBC provides a set of classes and methods specifically designed for database connectivity, enabling Java developers to perform tasks such as establishing connections, executing queries, and managing data in MySQL databases.

JDBC Installation

To use MySQL with Java, you need to use a JDBC (Java Database Connectivity) driver to connect your Java application to a MySQL database. Below are the general steps for installing and using the MySQL Connector/J, which is the official MySQL JDBC driver for Java −

Step 1: Download MySQL Connector/J

Visit the official MySQL Connector/J download page: MySQL Connector/J Downloads.

Step 2: Select the Appropriate Version

Choose the appropriate version based on your MySQL server version and Java version. Download the ZIP or TAR archive containing the JDBC driver.

Step 3: Extract the Archive

Extract the contents of the downloaded archive to a location on your computer.

Step 4: Add Connector/J to Your Java Project

In your Java project, add the Connector/J JAR file to your classpath. You can do this in your IDE or by manually copying the JAR file into your project.

Step 5: Connect to MySQL Database in Java

In your Java code, use the JDBC API to connect to the MySQL database.

Java Functions to Access MySQL

Following are the major functions involved in accessing MySQL from Java −

S.No Function & Description
1

DriverManager.getConnection(String url, String user, String password)

Establishes a connection to the database using the specified URL, username, and password.
2

createStatement()

Creates a Statement object for executing SQL queries.
3

executeQuery(String sql)

Executes a SQL SELECT query and returns a ResultSet object containing the result set.
4

executeUpdate(String sql)

Executes a SQL INSERT, UPDATE, DELETE, or other non-query statement.
5

next()

Moves the cursor to the next row in the result set. Returns true if there is a next row, false otherwise.
6

getInt(String columnLabel)

Retrieves the value of the specified column in the current row of the result set.
7

prepareStatement(String sql)

Creates a PreparedStatement object for executing parameterized SQL queries.
8

setXXX(int parameterIndex, XXX value)

Sets the value of a specified parameter in the prepared statement.
9

executeQuery(), executeUpdate()

Execute the prepared statement as a query or update.
10

setAutoCommit(boolean autoCommit)

Enables or disables auto-commit mode.
11

commit()

Commits the current transaction.
12

rollback()

Rolls back the current transaction.

Basic Example

To connect and communicate with a MySQL database using Java, you can follow these steps −

  • Load the JDBC driver specific to your database.
  • Create a connection to the database using "DriverManager.getConnection()".
  • Create a "Statement" or "PreparedStatement" for executing SQL queries.
  • Use "executeQuery()" for SELECT queries, or "executeUpdate()" for other statements.
  • Iterate through the "ResultSet" to process the retrieved data.
  • Close "ResultSet", "Statement", and "Connection" to release resources.
  • Wrap database code in try-catch blocks to handle exceptions.
  • Use transactions if performing multiple operations as a single unit.

The following example shows a generic syntax of a Java program to call any MySQL query −

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

public class DatabaseInteractionExample {

    public static void main(String[] args) {
        try {
            // Load JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Connect to Database
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "your_username", "your_password");

            // Execute Query
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("Your SQL Query");

            // Process Results
            while (resultSet.next()) {
                // Process data
            }

            // Close Resources
            resultSet.close();
            statement.close();
            connection.close();

            // Handle Exceptions
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}
Advertisements