MySQL Java Connection and Usage

MySQL is one of the most popular open-source relational databases, and Java is the most commonly used programming language in enterprise application development. Combining Java with MySQL allows you to build powerful data-driven applications.

This article will detail how to connect to and use a MySQL database in a Java program, including:

This article introduces the basic steps for connecting to and using a MySQL database in Java, including:

  • Loading the JDBC driver
  • Establishing database connections
  • Executing SQL queries and updates
  • Using PreparedStatement to prevent SQL injection
  • Transaction management
  • Resource closing
  • Best practices

Preparation

Download MySQL JDBC Driver

Java interacts with databases through JDBC (Java Database Connectivity) technology. To connect to MySQL, you need to download the MySQL Connector/J driver:

  1. VisitMySQL Connector/J download page
  2. Select a driver version compatible with your Java version
  3. Download the JAR file (e.g., mysql-connector-java-8.0.xx.jar)

Add the Driver to Your Project

Depending on the development tool or build tool you use (such as Eclipse, IntelliJ IDEA, Maven, or Gradle), add the downloaded JAR file to your project's classpath.

Establish Database Connection

Load the Driver

In Java code, you first need to load the MySQL JDBC driver:

Example

try {
    Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
    System.out.println("MySQL JDBC driver not found");
    e.printStackTrace();
}

Create Connection

Use theDriverManager.getConnection()method to establish a connection to the database:

Example

String url = "jdbc:mysql://localhost:3306/your_database_name?useSSL=false&serverTimezone=UTC";
String username = "your_username";
String password = "your_password";

try {
    Connection connection = DriverManager.getConnection(url, username, password);
    System.out.println("Database connection successful!");
} catch (SQLException e) {
    System.out.println("Database connection failed");
    e.printStackTrace();
}

3.2.1 Connection Parameter Description

  • jdbc:mysql://localhost:3306/: Default address and port of the MySQL server
  • useSSL=false: Disable SSL connection (development environment)
  • serverTimezone=UTC: Set the server timezone to avoid timezone issues

Execute SQL Operations

Create Statement Object

The Statement object is used to execute static SQL statements:

Example

Statement statement = connection.createStatement();

Execute Query

Use theexecuteQuery()method to execute a SELECT statement:

Example

String sql = "SELECT * FROM table_name";
ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    System.out.println("ID: " + id + ", Name: " + name);
}

Execute Update

Use theexecuteUpdate()method to execute an INSERT, UPDATE, or DELETE statement:

Example

String insertSQL = "INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')";
int rowsAffected = statement.executeUpdate(insertSQL);
System.out.println("Rows affected: " + rowsAffected);

Use PreparedStatement

PreparedStatement can prevent SQL injection and improve performance:

Example

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1, "Zhang San");
preparedStatement.setString(2, "zhangsan@example.com");

int rowsInserted = preparedStatement.executeUpdate();
System.out.println("Rows inserted: " + rowsInserted);

Transaction Management

Basic Concepts

A transaction is a set of SQL operations that either all succeed or all fail.

Transaction Operation Example

Example

try {
    // Disable auto-commit
    connection.setAutoCommit(false);
   
    // Execute multiple SQL operations
    statement.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    statement.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
   
    // Commit the transaction
    connection.commit();
    System.out.println("Transaction executed successfully");
} catch (SQLException e) {
    // Roll back if an error occurs
    connection.rollback();
    System.out.println("Transaction execution failed, rolled back");
    e.printStackTrace();
} finally {
    // Restore auto-commit
    connection.setAutoCommit(true);
}

Closing Resources

Why Closing Resources Is Necessary

Database connections are limited resources and must be closed after use to avoid resource leaks.

How to Close Resources Correctly

Example

try {
    if (resultSet != null) resultSet.close();
    if (statement != null) statement.close();
    if (connection != null) connection.close();
} catch (SQLException e) {
    e.printStackTrace();
}

6.3 Using try-with-resources

Java 7+ can use try-with-resources to automatically close resources:

Example

try (Connection conn = DriverManager.getConnection(url, username, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
   
    while (rs.next()) {
        // Process the result
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Best Practices

Use Connection Pool

In production environments, use a connection pool (such as HikariCP, c3p0) to manage database connections:

Example

// HikariCP example
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/your_database");
config.setUsername("username");
config.setPassword("password");

HikariDataSource dataSource = new HikariDataSource(config);
Connection connection = dataSource.getConnection();

Handling Exceptions

Handle SQL exceptions correctly and provide meaningful error messages:

Example

try {
    // Database operation
} catch (SQLException e) {
    System.err.println("SQL error: " + e.getMessage());
    System.err.println("SQL state: " + e.getSQLState());
    System.err.println("Error code: " + e.getErrorCode());
}

7.3 Use DAO Pattern

Encapsulate data access logic in a Data Access Object (DAO) to improve code maintainability:

Example

public class UserDao {
    private Connection connection;
   
    public UserDao(Connection connection) {
        this.connection = connection;
    }
   
    public User getUserById(int id) throws SQLException {
        String sql = "SELECT * FROM users WHERE id = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return new User(rs.getInt("id"), rs.getString("name"));
            }
        }
        return null;
    }
}

By mastering these basics, you can begin using MySQL databases in your Java applications. As you gain experience, you can further study more advanced topics such as connection pools, ORM frameworks (e.g., Hibernate, MyBatis), and more.

Other Extensions