# Java JDBC CRUD Operations Guide
JDBC (Java Database Connectivity) is an API that allows Java programs to interact with databases. Here's a comprehensive guide to performing CRUD (Create, Read, Update, Delete) operations using JDBC.
## 1. Basic Setup
First, you need to establish a database connection:
```java
import java.sql.*;
public class JDBCExample {
// Database credentials
static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
try {
// Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// Perform CRUD operations here
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
} finally {
// Close connection
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
```
## 2. CRUD Operations
### CREATE (Insert)
```java
public static void createRecord(Connection conn) throws SQLException {
String sql = "INSERT INTO employees (id, name, age, salary) VALUES (?, ?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setInt(1, 101);
stmt.setString(2, "John Doe");
stmt.setInt(3, 30);
stmt.setDouble(4, 50000.0);
int rowsInserted = stmt.executeUpdate();
System.out.println(rowsInserted + " row(s) inserted.");
}
}
```
### READ (Select)
```java
public static void readRecords(Connection conn) throws SQLException {
String sql = "SELECT id, name, age, salary FROM employees";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
double salary = rs.getDouble("salary");
System.out.println("ID: " + id + ", Name: " + name +
", Age: " + age + ", Salary: " + salary);
}
}
}
```
### UPDATE
```java
public static void updateRecord(Connection conn) throws SQLException {
String sql = "UPDATE employees SET salary=? WHERE id=?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setDouble(1, 55000.0); // new salary
stmt.setInt(2, 101); // employee ID
int rowsUpdated = stmt.executeUpdate();
System.out.println(rowsUpdated + " row(s) updated.");
}
}
```
### DELETE
```java
public static void deleteRecord(Connection conn) throws SQLException {
String sql = "DELETE FROM employees WHERE id=?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setInt(1, 101); // employee ID to delete
int rowsDeleted = stmt.executeUpdate();
System.out.println(rowsDeleted + " row(s) deleted.");
}
}
```
## 3. Transaction Management
To ensure data integrity, you can use transactions:
```java
public static void performTransaction(Connection conn) throws SQLException {
try {
// Disable auto-commit
conn.setAutoCommit(false);
// Perform multiple operations
createRecord(conn);
updateRecord(conn);
// Commit transaction if all operations succeed
conn.commit();
System.out.println("Transaction completed successfully.");
} catch (SQLException e) {
// Rollback if any operation fails
conn.rollback();
System.out.println("Transaction rolled back due to error: " + e.getMessage());
} finally {
// Restore auto-commit
conn.setAutoCommit(true);
}
}
```
## 4. Closing Resources
Always close resources properly using try-with-resources:
```java
public static void readWithResources(Connection conn) throws SQLException {
String sql = "SELECT * FROM employees";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
// Process results
}
} // Resources automatically closed here
}
```
## 5. Best Practices
1. Always use PreparedStatement to prevent SQL injection
2. Close resources in reverse order of their creation
3. Use try-with-resources for automatic resource management
4. Handle exceptions properly
5. Use connection pooling in production applications
Remember to add the JDBC driver to your project's classpath. For MySQL, you would typically add the MySQL Connector/J JAR file.
Would you like me to explain any specific part in more detail or provide examples for a different database system?
No comments:
Post a Comment