环境与 user 表

笔记/Java/Java 持久层/JDBC/环境与 user 表

上一篇：JDBC 概述 · 下一篇：user 表 CRUD

本地任意目录新建 Maven 工程（下文以 jdbc-user-lab 为例），把下面 SQL 与 Java 原样复制进去即可运行后续 Demo。

1. 建库建表 schema.sql#

CREATE DATABASE IF NOT EXISTS jdbc_lab
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;


USE jdbc_lab;


DROP TABLE IF EXISTS `user`;


CREATE TABLE `user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(64) NOT NULL,
  `email` VARCHAR(128) DEFAULT NULL,
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常 0=禁用',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 种子数据 seed.sql#

USE jdbc_lab;


INSERT INTO `user` (`user_name`, `email`, `status`) VALUES
  ('alice', 'alice@example.com', 1),
  ('bob', 'bob@example.com', 1);

执行：

Terminal window
mysql -u root -p < schema.sql
mysql -u root -p < seed.sql

3. pom.xml#

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>


    <groupId>com.blog.demo</groupId>
    <artifactId>jdbc-user-lab</artifactId>
    <version>1.0.0</version>


    <properties>
        <maven.compiler.release>17</maven.compiler.release>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <mysql.version>8.3.0</mysql.version>
        <hikaricp.version>5.1.0</hikaricp.version>
        <spring.version>6.1.14</spring.version>
    </properties>


    <dependencies>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>${hikaricp.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>3.5.0</version>
            </plugin>
        </plugins>
    </build>
</project>

4. 目录结构#

jdbc-user-lab/
├── pom.xml
└── src/main/java/demo/jdbc/
    ├── Db.java
    ├── User.java
    └── UserDao.java

5. Db.java — 连接与连接池#

package demo.jdbc;


import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;


import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public final class Db {
    private static final String URL = env("JDBC_URL",
            "jdbc:mysql://127.0.0.1:3306/jdbc_lab?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai");
    private static final String USER = env("JDBC_USER", "root");
    private static final String PASSWORD = env("JDBC_PASSWORD", "root");


    private Db() {}


    public static Connection open() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }


    public static DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.setMaximumPoolSize(5);
        return new HikariDataSource(config);
    }


    private static String env(String key, String fallback) {
        String value = System.getenv(key);
        return value == null || value.isBlank() ? fallback : value.trim();
    }
}

密码不是 root 时（PowerShell）：

Terminal window
$env:JDBC_PASSWORD = "你的密码"

6. User.java — 实体#

package demo.jdbc;


import java.time.LocalDateTime;


public class User {
    private Long id;
    private String userName;
    private String email;
    private int status;
    private LocalDateTime createdAt;


    public User() {}


    public User(String userName, String email, int status) {
        this.userName = userName;
        this.email = email;
        this.status = status;
    }


    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getUserName() { return userName; }
    public void setUserName(String userName) { this.userName = userName; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public int getStatus() { return status; }
    public void setStatus(int status) { this.status = status; }
    public LocalDateTime getCreatedAt() { return createdAt; }
    public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }


    @Override
    public String toString() {
        return "User{id=" + id + ", userName='" + userName + "', email='" + email
                + "', status=" + status + ", createdAt=" + createdAt + "}";
    }
}

7. UserDao.java — 增删改查#

package demo.jdbc;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;


public class UserDao {


    public long insert(Connection conn, User user) throws SQLException {
        String sql = "INSERT INTO user (user_name, email, status) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getEmail());
            ps.setInt(3, user.getStatus());
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) {
                    return keys.getLong(1);
                }
            }
        }
        throw new SQLException("No generated key for insert");
    }


    public Optional<User> findById(Connection conn, long id) throws SQLException {
        String sql = "SELECT id, user_name, email, status, created_at FROM user WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRow(rs));
                }
            }
        }
        return Optional.empty();
    }


    public List<User> findByStatus(Connection conn, int status) throws SQLException {
        String sql = "SELECT id, user_name, email, status, created_at FROM user WHERE status = ? ORDER BY id";
        List<User> list = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, status);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapRow(rs));
                }
            }
        }
        return list;
    }


    public int updateEmail(Connection conn, long id, String email) throws SQLException {
        String sql = "UPDATE user SET email = ? WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, email);
            ps.setLong(2, id);
            return ps.executeUpdate();
        }
    }


    public int deleteById(Connection conn, long id) throws SQLException {
        String sql = "DELETE FROM user WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, id);
            return ps.executeUpdate();
        }
    }


    private static User mapRow(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUserName(rs.getString("user_name"));
        user.setEmail(rs.getString("email"));
        user.setStatus(rs.getInt("status"));
        Timestamp ts = rs.getTimestamp("created_at");
        if (ts != null) {
            user.setCreatedAt(ts.toLocalDateTime());
        }
        return user;
    }
}

8. 编译验证#

在 jdbc-user-lab 目录：

Terminal window
mvn -q compile

下一篇加入 CrudDemo.java 并运行完整 CRUD 流程。

文章目录
© 2026 栖云. All Rights Reserved. / RSS / Sitemap
Powered by Astro & Firefly

文章目录