数据库访问技术¶

import java.sql.*;

public class JdbcBasicDemo {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root123";

        // ===== 基础CRUD =====
        // try-with-resources 自动关闭资源
        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            // 查询 — 使用PreparedStatement防SQL注入
            String querySql = "SELECT id, name, email FROM users WHERE status = ? AND age > ?";
            try (PreparedStatement ps = conn.prepareStatement(querySql)) {
                ps.setString(1, "active");
                ps.setInt(2, 18);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        long id = rs.getLong("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");
                        System.out.printf("id=%d, name=%s, email=%s%n", id, name, email);
                    }
                }
            }

            // 插入
            String insertSql = "INSERT INTO users(name, email, age) VALUES(?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSql,
                    Statement.RETURN_GENERATED_KEYS)) {
                ps.setString(1, "Alice");
                ps.setString(2, "alice@example.com");
                ps.setInt(3, 25);
                int rows = ps.executeUpdate();
                System.out.println("插入行数: " + rows);

                // 获取自增主键
                try (ResultSet keys = ps.getGeneratedKeys()) {
                    if (keys.next()) {
                        System.out.println("新增ID: " + keys.getLong(1));
                    }
                }
            }

            // 批量插入
            String batchSql = "INSERT INTO users(name, email, age) VALUES(?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(batchSql)) {
                conn.setAutoCommit(false);  // 手动事务
                for (int i = 0; i < 1000; i++) {
                    ps.setString(1, "User" + i);
                    ps.setString(2, "user" + i + "@example.com");
                    ps.setInt(3, 20 + i % 30);
                    ps.addBatch();
                    if (i % 100 == 0) {
                        ps.executeBatch();  // 每100条提交一次
                        ps.clearBatch();
                    }
                }
                ps.executeBatch();
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();  // 异常回滚
                throw e;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// ========== HikariCP（Spring Boot默认，性能最佳） ==========
// application.yml:
// spring:
//   datasource:
//     url: jdbc:mysql://localhost:3306/mydb
//     username: root
//     password: root123
//     driver-class-name: com.mysql.cj.jdbc.Driver
//     hikari:
//       minimum-idle: 5          # 最小空闲连接
//       maximum-pool-size: 20    # 最大连接数
//       idle-timeout: 300000     # 空闲超时（5分钟）
//       max-lifetime: 1800000    # 连接最大存活时间（30分钟）
//       connection-timeout: 30000 # 获取连接超时（30秒）
//       pool-name: MyHikariPool

// 手动配置HikariCP
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("root");
config.setPassword("root123");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.addDataSourceProperty("cachePrepStmts", "true");       // 缓存PreparedStatement
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

HikariDataSource dataSource = new HikariDataSource(config);

// ========== Druid（阿里巴巴，功能丰富：监控、防SQL注入） ==========
// 引入: druid-spring-boot-starter
// spring:
//   datasource:
//     type: com.alibaba.druid.pool.DruidDataSource
//     druid:
//       initial-size: 5
//       max-active: 20
//       min-idle: 5
//       max-wait: 60000
//       stat-view-servlet:
//         enabled: true    # 开启Druid监控页面
//         url-pattern: /druid/*
//       filter:
//         stat:
//           enabled: true  # SQL统计
//         wall:
//           enabled: true  # SQL防火墙

// Spring Boot整合MyBatis
// 引入: mybatis-spring-boot-starter

// application.yml
// mybatis:
//   mapper-locations: classpath:mapper/**/*.xml
//   type-aliases-package: com.example.entity
//   configuration:
//     map-underscore-to-camel-case: true   # 下划线转驼峰
//     cache-enabled: true                  # 开启二级缓存
//     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  # 打印SQL

// 实体类
public class User {
    private Long id;
    private String userName;   // 对应数据库 user_name（自动转换）
    private String email;
    private Integer age;
    private String status;
    private LocalDateTime createdAt;
    // getter/setter...
}

// Mapper接口（注解方式 — 适合简单SQL）
@Mapper
public interface UserMapper {

    @Select("SELECT * FROM users WHERE id = #{id}")
    User findById(Long id);

    @Insert("INSERT INTO users(user_name, email, age) VALUES(#{userName}, #{email}, #{age})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);

    @Update("UPDATE users SET user_name = #{userName}, email = #{email} WHERE id = #{id}")
    int update(User user);

    @Delete("DELETE FROM users WHERE id = #{id}")
    int deleteById(Long id);

    // 复杂SQL用XML
    List<User> findByCondition(UserQueryParam param);
    int batchInsert(@Param("list") List<User> users);
}

// #{} — 预编译占位符（PreparedStatement），防SQL注入 ✅
@Select("SELECT * FROM users WHERE id = #{id}")
User findById(Long id);
// 实际SQL: SELECT * FROM users WHERE id = ?

// ${} — 字符串拼接（Statement），有SQL注入风险 ⚠️
// 应用场景：动态表名、动态ORDER BY（不是用户输入时可用）
@Select("SELECT * FROM ${tableName} ORDER BY ${orderBy}")
List<User> findFromTable(@Param("tableName") String tableName,
                         @Param("orderBy") String orderBy);
// 实际SQL: SELECT * FROM users ORDER BY created_at

// 规则：值用#{}，列名/表名用${}（需确保来源安全）

// 一级缓存（SqlSession级别，默认开启）
// 同一个SqlSession中，相同查询直接返回缓存（执行update/insert/delete后自动清除）
// Spring整合后每次请求新SqlSession → 一级缓存作用有限

// 二级缓存（Mapper/namespace级别，需手动开启）
// 配置: mybatis.configuration.cache-enabled=true
// XML中加: <cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>
// ⚠️ 多表联查时二级缓存可能导致脏读 → 生产环境慎用，推荐Redis缓存

// PageHelper分页插件
// 引入: pagehelper-spring-boot-starter
// PageHelper.startPage(1, 10);  // 第1页，每页10条
// List<User> users = userMapper.findAll();
// PageInfo<User> pageInfo = new PageInfo<>(users);
// pageInfo.getTotal();     // 总记录数
// pageInfo.getPages();     // 总页数
// pageInfo.getList();      // 当前页数据

// MyBatis-Plus = MyBatis增强版（只做增强不做改变）
// 引入: mybatis-plus-spring-boot3-starter (Spring Boot 3.x)

// 实体类
@Data
@TableName("users")          // 对应表名
public class User {
    @TableId(type = IdType.AUTO) // 主键自增
    private Long id;

    @TableField("user_name")  // 指定列名（不指定时自动驼峰转下划线）
    private String userName;

    private String email;
    private Integer age;

    @TableField(fill = FieldFill.INSERT)       // 自动填充（插入时）
    private LocalDateTime createdAt;

    @TableField(fill = FieldFill.INSERT_UPDATE) // 自动填充（插入+更新）
    private LocalDateTime updatedAt;

    @TableLogic      // 逻辑删除（0=未删除, 1=已删除）
    private Integer deleted;

    @Version          // 乐观锁版本号
    private Integer version;
}

// Mapper — 继承BaseMapper即可获得CRUD能力
@Mapper
public interface UserMapper extends BaseMapper<User> {
    // 无需编写任何方法！BaseMapper已提供：
    // insert(entity)
    // deleteById(id) / deleteBatchIds(ids)
    // updateById(entity)
    // selectById(id) / selectBatchIds(ids)
    // selectList(Wrapper) / selectPage(Page, Wrapper)
    // selectCount(Wrapper)

    // 需要自定义SQL时仍然可以写XML或注解
}

// Service层 — 继承IService获得更丰富的方法
public interface UserService extends IService<User> {
    // 额外的自定义业务方法
}

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
        implements UserService {
    // ServiceImpl已实现IService的所有方法：
    // save / saveBatch / saveOrUpdate
    // remove / removeById
    // update / updateById
    // getById / list / page / count
}

@RestController
@RequestMapping("/api/users")
public class UserController {

    @Autowired
    private UserService userService;

    // ===== QueryWrapper（字符串字段名） =====
    @GetMapping("/search")
    public List<User> search(@RequestParam String keyword) {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("user_name", keyword)
               .or()
               .like("email", keyword)
               .orderByDesc("created_at");
        return userService.list(wrapper);
    }

    // ===== LambdaQueryWrapper（Lambda字段引用，编译期安全 ✅） =====
    @GetMapping("/filter")
    public List<User> filter(
            @RequestParam(required = false) String name,
            @RequestParam(required = false) Integer minAge,
            @RequestParam(required = false) String status) {

        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper
            .like(name != null, User::getUserName, name)        // 条件成立才拼接
            .ge(minAge != null, User::getAge, minAge)           // >=
            .eq(status != null, User::getStatus, status)        // =
            .select(User::getId, User::getUserName, User::getEmail) // 只查指定字段
            .orderByDesc(User::getCreatedAt);
        return userService.list(wrapper);
    }

    // ===== 分页查询 =====
    @GetMapping("/page")
    public IPage<User> page(
            @RequestParam(defaultValue = "1") int current,
            @RequestParam(defaultValue = "10") int size) {
        Page<User> page = new Page<>(current, size);
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(User::getDeleted, 0)
               .orderByDesc(User::getCreatedAt);
        return userService.page(page, wrapper);
    }

    // ===== LambdaUpdateWrapper =====
    @PutMapping("/deactivate/{id}")
    public boolean deactivate(@PathVariable Long id) {
        LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
        wrapper.eq(User::getId, id)
               .set(User::getStatus, "inactive")
               .set(User::getUpdatedAt, LocalDateTime.now());
        return userService.update(wrapper);
    }
}

// 分页拦截器配置（必须配置！）
@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); // 乐观锁
        return interceptor;
    }
}

// 自动填充处理器
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createdAt", LocalDateTime.class, LocalDateTime.now());
        this.strictInsertFill(metaObject, "updatedAt", LocalDateTime.class, LocalDateTime.now());
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.strictUpdateFill(metaObject, "updatedAt", LocalDateTime.class, LocalDateTime.now());
    }
}

// 逻辑删除配置 — application.yml
// mybatis-plus:
//   global-config:
//     db-config:
//       logic-delete-field: deleted
//       logic-delete-value: 1
//       logic-not-delete-value: 0

// 调用deleteById后实际执行：UPDATE users SET deleted = 1 WHERE id = ?
// 调用selectList后自动追加：WHERE deleted = 0

// 引入: mybatis-plus-generator + freemarker
// 一键生成Entity、Mapper、Service、Controller

import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;

public class CodeGenerator {
    public static void main(String[] args) {
        FastAutoGenerator.create(
                "jdbc:mysql://localhost:3306/mydb", "root", "root123")
            .globalConfig(builder -> builder
                .author("your-name")
                .outputDir("src/main/java")
                .commentDate("yyyy-MM-dd")
            )
            .packageConfig(builder -> builder
                .parent("com.example")
                .entity("entity")
                .mapper("mapper")
                .service("service")
                .controller("controller")
                .xml("mapper.xml")
            )
            .strategyConfig(builder -> builder
                .addInclude("users", "orders", "products")  // 表名
                .entityBuilder()
                    .enableLombok()
                    .enableTableFieldAnnotation()
                    .logicDeleteColumnName("deleted")
                .controllerBuilder()
                    .enableRestStyle()
                .mapperBuilder()
                    .enableBaseResultMap()
            )
            .templateEngine(new FreemarkerTemplateEngine())
            .execute();
    }
}

// 引入: spring-boot-starter-data-jpa

// 实体类
@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_name", nullable = false, length = 50)
    private String userName;

    @Column(nullable = false, unique = true)
    private String email;

    private Integer age;

    @Enumerated(EnumType.STRING)
    private UserStatus status;

    @CreationTimestamp
    private LocalDateTime createdAt;

    @UpdateTimestamp
    private LocalDateTime updatedAt;
}

// Repository — 方法命名约定自动生成SQL
public interface UserRepository extends JpaRepository<User, Long> {

    // 根据方法名自动推导查询
    Optional<User> findByEmail(String email);                     // WHERE email = ?
    List<User> findByUserNameContaining(String keyword);          // WHERE user_name LIKE %?%
    List<User> findByAgeBetween(int min, int max);                // WHERE age BETWEEN ? AND ?
    List<User> findByStatusAndAgeGreaterThan(UserStatus s, int age); // WHERE status=? AND age>?
    List<User> findByUserNameInOrderByCreatedAtDesc(List<String> names); // WHERE name IN (?) ORDER BY...
    long countByStatus(UserStatus status);                        // SELECT COUNT(*) WHERE status=?
    boolean existsByEmail(String email);                          // 是否存在
    void deleteByStatus(UserStatus status);                       // DELETE WHERE status=?

    // Top/First
    List<User> findTop10ByOrderByCreatedAtDesc();                 // 最新10条

    // @Query — 自定义JPQL或原生SQL
    @Query("SELECT u FROM User u WHERE u.userName LIKE %:keyword% OR u.email LIKE %:keyword%")
    Page<User> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);

    @Query(value = "SELECT * FROM users WHERE status = :status", nativeQuery = true)
    List<User> findByStatusNative(@Param("status") String status);

    // @Modifying — 修改/删除操作
    @Modifying
    @Query("UPDATE User u SET u.status = :status WHERE u.id IN :ids")
    int batchUpdateStatus(@Param("ids") List<Long> ids, @Param("status") UserStatus status);
}

// 类似MyBatis的动态SQL，适合复杂动态条件
import org.springframework.data.jpa.domain.Specification;

public class UserSpecifications {

    public static Specification<User> hasName(String name) {
        return (root, query, cb) ->
            name == null ? null : cb.like(root.get("userName"), "%" + name + "%");
    }

    public static Specification<User> hasStatus(UserStatus status) {
        return (root, query, cb) ->
            status == null ? null : cb.equal(root.get("status"), status);
    }

    public static Specification<User> ageRange(Integer min, Integer max) {
        return (root, query, cb) -> {
            if (min == null && max == null) return null;
            if (min != null && max != null) return cb.between(root.get("age"), min, max);
            if (min != null) return cb.greaterThanOrEqualTo(root.get("age"), min);
            return cb.lessThanOrEqualTo(root.get("age"), max);
        };
    }
}

// 使用（Repository需继承JpaSpecificationExecutor<User>）
@Service
public class UserService {

    @Autowired
    private UserRepository userRepo;

    public Page<User> search(String name, UserStatus status,
                             Integer minAge, Integer maxAge, Pageable pageable) {
        Specification<User> spec = Specification
            .where(UserSpecifications.hasName(name))
            .and(UserSpecifications.hasStatus(status))
            .and(UserSpecifications.ageRange(minAge, maxAge));
        return userRepo.findAll(spec, pageable);
    }
}

// 自动记录创建人、修改人、创建时间、修改时间

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@Data
public abstract class BaseEntity {

    @CreatedBy
    @Column(updatable = false)
    private String createdBy;

    @LastModifiedBy
    private String updatedBy;

    @CreatedDate
    @Column(updatable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    private LocalDateTime updatedAt;
}

// 实体继承BaseEntity
@Entity
@Table(name = "users")
public class User extends BaseEntity {
    // ...
}

// 审计人提供者
@Configuration
@EnableJpaAuditing
public class JpaConfig {
    @Bean
    public AuditorAware<String> auditorProvider() {
        return () -> Optional.ofNullable(
            SecurityContextHolder.getContext().getAuthentication())
            .map(auth -> auth.getName())
            .or(() -> Optional.of("system"));
    }
}

// 何时需要分库分表？
// - 单表数据量 > 2000万行 或 单表大小 > 2GB
// - 读写QPS持续高峰

// 分片策略：
// 1. 水平分表：按规则将一张表的数据分散到多张结构相同的表
//    - 取模：user_id % 4 → orders_0, orders_1, orders_2, orders_3
//    - 范围：按时间范围分（orders_202501, orders_202502）
//    - Hash：一致性Hash
// 2. 水平分库：数据分散到不同数据库实例
// 3. 垂直拆分：按字段拆分（如用户基本信息 和 用户扩展信息分表）

// 分布式ID方案（分表后自增ID不可用）：
// 1. 雪花算法（Snowflake）— 64位ID = 时间戳 + 机器ID + 序列号
// 2. UUID — 无序，不适合作为聚簇索引主键
// 3. 数据库号段 — 美团Leaf、百度UidGenerator
// 4. Redis自增 — INCR命令

// MyBatis-Plus雪花ID配置
@TableId(type = IdType.ASSIGN_ID)  // 使用雪花算法生成ID
private Long id;