Setup MySQL DB in Eclipse

1. Overview

Connecting to a MySQL database from Eclipse can offer several benefits, especially if we’re working on database-driven applications. For example, we can use a local MySQL database to test our application’s database interactions and configuration settings (like JDBC URLs, connection pools, and other parameters) to ensure database operations work correctly before deployment. This helps us identify and fix issues early.

In this tutorial, we’ll go through the process of setting up a MySQL database in Eclipse, from downloading the necessary connectors to executing sample code.

2. Prerequisites

We need Eclipse IDE installed on our computer. We can download it from the Eclipse official website.

Next, we also need MySQL server installed to interact with the database. We can download it from the MySQL official website. If we’re using Docker, we can run a MySQL container with it. This allows us to quickly set up and manage a MySQL database in an isolated, consistent environment.

Finally, we’ll need MySQL Workbench or any other MySQL client. We can download it from the MySQL official website.

3. Steps to Setup MySQL DB in Eclipse

We’ll now take a look at setting up a MySQL database in Eclipse, from downloading and adding the MySQL connector to Eclipse IDE, to running a sample code.

3.1. Download MySQL Connector

The MySQL Connector is a driver that allows Java applications to communicate with MySQL databases. Let’s follow these steps to download it:

1. Visit the MySQL Connector/J download page.
2. Under Select Operating System drop down, choose Platform Independent.
3. Download and extract the zip file to a location on our computer.

3.2. Add MySQL Connector/J to Our Eclipse Project

To interact with a database from Eclipse IDE, we need to add the MySQL Connector/J JAR to our Eclipse project’s build path. Below are the steps we need to follow:

1. Open Eclipse and navigate to our project.
2. Right-click on our project and select Build Path -> Configure Build Path.
3. Go to the Libraries tab and click Add External JARs.
4. Browse to the location where we extracted the MySQL Connector/J zip file and select the mysql-connector-j-x.x.x.jar file.
5. Finally, click Open and then Apply and Close.

3.3. Create a MySQL Database and Table

Let’s create a sample database and table to work with. First, we open MySQL Workbench or any other MySQL client and connect to our MySQL Server. Then, we execute the following SQL commands to create a database and a table:

CREATE DATABASE sampledb;

USE sampledb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

In the above script, we create a database named sampledb, switch to it, and then create a users table with columns for id, username, and email.

3.4. Sample Code

Now, let’s write some Java code to connect to our MySQL database and perform basic operations:

@BeforeAll
public void setUp() throws Exception {
    String url = "jdbc:mysql://localhost:3306/sampledb";
    String user = "root";
    String password = "<YOUR-DB-PASSWORD>";

    Class.forName("com.mysql.cj.jdbc.Driver");

    conn = DriverManager.getConnection(url, user, password);
    stmt = conn.createStatement();
}

Methods annotated with @BeforeAll are executed once before all test methods in the test class. Here, we’re trying to initialize a connection to a MySQL database before any tests are run. It ensures that the database connection is established and a Statement object is created for executing SQL queries during the tests.

It’s worth noting that conn and stmt here are class variables, and YOUR-DB-PASSWORD is the database password we created.

Let’s now take a look at the JUnit test method that inserts a user record into the users table, retrieves and verifies the inserted user’s details, and then deletes the user to clean up the test data:

@Test
public void givenNewUser_whenInsertingUser_thenUserIsInsertedAndRetrieved() throws Exception {
    String insertSQL = "INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')";
    stmt.executeUpdate(insertSQL);

    ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE username = 'john_doe'");

    assertTrue(rs.next());
    assertEquals("john_doe", rs.getString("username"));
    assertEquals("[email protected]", rs.getString("email"));

    String deleteSQL = "DELETE FROM users WHERE username = 'john_doe'";
    stmt.executeUpdate(deleteSQL);
}

4. Connecting to MySQL DB From Eclipse IDE

Having database access within our development environment means we don’t have to switch between different tools. This makes it easy to manage database schemas, tables, and other objects directly from Eclipse.

Let’s see the steps to connect to MySQL DB from Eclipse IDE:

1. Open Data Source Explorer by navigating to Window > Show View > Other > Data Management > Data Source Explorer.
2. Inside Data Source Explorer, right-click Database Connections > New.
3. Select MySQL (or any other database that we want to connect to). Give it a meaningful name and click Next.
4. On the next popup, click on the + icon to create a new driver definition.
5. On the next popup window, under Name/Type Tab, select the Database version.
6. Click on Jar List tab and click on Clear All to clear any existing jar (we’ll add one, on our own).
7. Click Add Jar/Zip… and choose the connector Jar that we downloaded.
8. Click on Properties tab to give database connection properties, click OK

Finally, we can click on Test Connection to verify we’re able to connect to the database:

5. Conclusion

Setting up a MySQL database in Eclipse is a straightforward process that involves downloading the MySQL Connector, configuring Eclipse, creating a database, and writing Java code to interact with the database.

By following the steps outlined in this article, we can efficiently manage and utilize MySQL databases within our Eclipse environment, enhancing our development workflow.