Connecting Java with a Database (MySQL/PostgreSQL)
Introduction
Java provides excellent support for database connectivity through JDBC (Java Database Connectivity). With JDBC, Java can interact with a variety of databases, including MySQL and PostgreSQL. JDBC allows Java applications to connect to a database, execute SQL queries, and retrieve results.
In this tutorial, we will cover how to:
- Set up a connection between Java and MySQL/PostgreSQL.
- Execute SQL queries.
- Retrieve and process data from the database.
Step 1: Set Up the Database
Before connecting Java to a database, you need to have a MySQL or PostgreSQL database set up. Here's how to do it:
- Install MySQL/PostgreSQL if not already installed.
- Create a new database, for example,
testdb. - Create a table, for example,
employees, with some sample data:CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50) ); INSERT INTO employees VALUES (1, 'John Doe', 'HR'); INSERT INTO employees VALUES (2, 'Jane Smith', 'Finance');
Step 2: Add JDBC Driver
Java communicates with databases via JDBC, and you need to include the JDBC driver for your database. For MySQL, you need the mysql-connector-java library, and for PostgreSQL, you need the postgresql
- For MySQL, download the connector from MySQL Connector/J.
- For PostgreSQL, download the driver from PostgreSQL JDBC Driver.
- Add the JDBC driver JAR file to your project’s classpath.
Step 3: Establish a Connection to the Database
To connect Java to a MySQL or PostgreSQL database, you use the DriverManager.getConnection() method. Below is an example of how to do this.
Example 1: Connecting to MySQL Database
import java.sql.*;
public class MySQLConnection {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a connection to the MySQL database
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
System.out.println("Connected to MySQL database!");
// Close the connection
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- The
Class.forName("com.mysql.cj.jdbc.Driver")method loads the MySQL JDBC driver. - The
DriverManager.getConnection()method is used to establish the connection to the MySQL database by specifying the database URL, username, and password. - Once the connection is successful, a message is printed.
Example 2: Connecting to PostgreSQL Database
import java.sql.*;
public class PostgreSQLConnection {
public static void main(String[] args) {
try {
// Load the PostgreSQL JDBC driver
Class.forName("org.postgresql.Driver");
// Establish a connection to the PostgreSQL database
Connection connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/testdb", "postgres", "password");
System.out.println("Connected to PostgreSQL database!");
// Close the connection
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- The
Class.forName("org.postgresql.Driver")method loads the PostgreSQL JDBC driver. - The
DriverManager.getConnection()method establishes the connection to the PostgreSQL database.
Step 4: Executing SQL Queries
Once the connection is established, you can execute SQL queries using the Statement or PreparedStatement objects. Below is an example that demonstrates how to execute a SELECT query to retrieve data from the database.
Example 3: Executing a SELECT Query
import java.sql.*;
public class DatabaseQueryExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create a statement
Statement statement = connection.createStatement();
// Execute a SELECT query
String query = "SELECT * FROM employees";
ResultSet resultSet = statement.executeQuery(query);
// Process the result
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String department = resultSet.getString("department");
System.out.println("ID: " + id + ", Name: " + name + ", Department: " + department);
}
// Close the result set, statement, and connection
resultSet.close();
statement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use the
createStatement()method to create aStatementobject. - We execute the SELECT query with the
executeQuery()method. - The
ResultSetobject is used to iterate over the results and retrieve data.
Step 5: Handling Prepared Statements
A PreparedStatement is used when executing SQL queries that include user input, as it helps prevent SQL injection attacks.
Example 4: Using PreparedStatement to Insert Data
import java.sql.*;
public class PreparedStatementExample {
public static void main(String[] args) {
try {
// Load the MySQL JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
// Establish a connection
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/testdb", "root", "password");
// Create a PreparedStatement for inserting data
String insertQuery = "INSERT INTO employees (id, name, department) VALUES (?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
// Set parameters for the PreparedStatement
preparedStatement.setInt(1, 3);
preparedStatement.setString(2, "Mark Johnson");
preparedStatement.setString(3, "Engineering");
// Execute the query
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows affected: " + rowsAffected);
// Close the prepared statement and connection
preparedStatement.close();
connection.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
In this example:
- We use a
PreparedStatementto insert data into theemployeestable. - We use the
setXXX()methods to set parameters for the query. - We execute the query using the
executeUpdate()method.
Step 6: Closing the Connection
After executing your queries and processing the results, always close the Connection, Statement, and ResultSet objects to release database resources.
Conclusion
In this tutorial, we covered the steps required to connect Java to a MySQL/PostgreSQL database using JDBC. We discussed how to:
- Establish a connection with the database.
- Execute SQL queries to retrieve and manipulate data.
- Use prepared statements to insert data securely.