Java JDBC连接数据库实战案例

原创桃扇骨 2024-09-21 20:06 93阅读 0赞

以下是一个简单的Java JDBC连接MySQL数据库的实战案例。我们将创建一个用户管理系统，用户可以通过界面添加、删除和查询用户。

1. 首先，在你的项目中加入JDBC相关的依赖，例如Maven:

```xml
<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version> 
    </dependency>
</dependencies>
```

2. 创建User类，用于存储用户数据：

```java
import java.sql.Date;

public class User {
    private int id;
    private String name;
    private Date registrationDate;

// getters and setters
}
```

3. 编写数据库连接、插入、删除和查询的Java代码：

```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DatabaseManagementSystem {
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/user-management";
    private static final String USER_DATABASE = "user_db";

public static void main(String[] args) {
        Connection connection = connectToDatabase(DATABASE_URL);
        if (connection == null) {
            System.out.println("Failed to connect to database.");
            return;
        }

// Insert user
        insertUser(connection, new User("John Doe", "john.doe@example.com", Date.valueOf(System.currentTimeMillis()))));

// Query all users
        List<User> allUsers = queryAllUsers(connection);
        for (User user : allUsers) {
            System.out.println(user.getName() + " - " + user.getEmail()));
        }

// Delete a user
        deleteUser(connection, "John Doe"));

// Close connection
        if (connection != null) {
            connection.close();
        }
    }

private static Connection connectToDatabase(String databaseUrl) {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(databaseUrl, "username", "password"));
            System.out.println("Connected to the database.");
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Failed to connect to the database: " + e.getMessage());
        }
        return connection;
    }

private static void insertUser(Connection connection, User user) throws SQLException {
        String sql = "INSERT INTO " + USER_DATABASE + " (name, email, registration_date) VALUES (?, ?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, user.getName()));
        statement.setString(2, user.getEmail()));
        statement.setDate(3, Date.valueOf(user.getRegistrationDate())));

int rowsInserted = statement.executeUpdate();
        System.out.println("User inserted successfully. Rows inserted: " + rowsInserted));
    }

private static List<User> queryAllUsers(Connection connection) throws SQLException {
        String sql = "SELECT * FROM " + USER_DATABASE;
        ResultSet resultSet = connection.createStatement().executeQuery(sql);
        List<User> usersList = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt("id")));
            user.setName(resultSet.getString("name"));
            user.setEmail(resultSet.getString("email"));
            user.setRegistrationDate(Date.valueOf(resultSet.getDate("registration_date")))));

usersList.add(user);
        }
        return usersList;
    }

private static void deleteUser(Connection connection, String username) throws SQLException {
        String sql = "DELETE FROM " + USER_DATABASE + " WHERE name = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, username));

int rowsDeleted = statement.executeUpdate();
        System.out.println("User deleted successfully. Rows deleted: " + rowsDeleted));
    }
}
```

这个例子中，我们创建了一个User类来存储用户数据，并在DatabaseManagementSystem类中实现了连接数据库、插入用户、查询所有用户、删除用户的功能。