Executing SQL Queries
In this section, we will explore how to execute SQL queries using Java Database Connectivity (JDBC). JDBC provides a standard interface to connect to databases, send SQL commands, and retrieve results.
Overview of JDBC
JDBC is an API in Java that enables programmers to interact with databases using SQL. It provides methods to connect to a database, execute SQL statements, and manage the results.Key Components of JDBC:
1. DriverManager: Manages a list of database drivers. 2. Connection: Represents a connection to a specific database. 3. Statement: Used to execute SQL queries. 4. ResultSet: Represents the result set of a query.Setting Up JDBC
Before executing queries, ensure you have: - A JDBC driver for your database (e.g., MySQL Connector for MySQL). - Added the driver to your project’s classpath.Executing SQL Queries
To execute SQL queries, follow these steps: 1. Load the JDBC driver. 2. Establish a connection to the database. 3. Create a statement object. 4. Execute the SQL query. 5. Process the results. 6. Close the connection.Example: Executing a SELECT Query
Here’s an example of how to execute a SELECT query:`java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class JDBCExample { public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null;
try { // Step 1: Load the JDBC driver Class.forName("com.mysql.cj.jdbc.Driver");
// Step 2: Establish a connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");
// Step 3: Create a statement object statement = connection.createStatement();
// Step 4: Execute a SELECT query String sql = "SELECT id, name FROM users"; resultSet = statement.executeQuery(sql);
// Step 5: Process the results
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println("ID: " + id + ", Name: " + name);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// Step 6: Close the resources
try {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
`
Example: Executing an INSERT Query
Inserting data into the database is just as straightforward. Here’s how you can execute an INSERT statement:`java
try {
// Assuming the connection is already established
String sqlInsert = "INSERT INTO users (name) VALUES ('John Doe')";
int rowsAffected = statement.executeUpdate(sqlInsert);
System.out.println(rowsAffected + " row(s) inserted.");
} catch (Exception e) {
e.printStackTrace();
}
`Summary
Executing SQL queries in Java using JDBC involves loading the driver, connecting to the database, creating a statement, executing the SQL, and processing the results. Always remember to close your resources to prevent memory leaks.---