一、环境准备

1.1 依赖管理（Maven）

<!-- 方案1：JdbcTemplate -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<!-- 方案2：MyBatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.5</version>
</dependency>

<!-- 方案3：Spring Data JPA -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- 通用依赖 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

二、核心配置

2.1 application.yml 配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springboot_demo?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8
    username: springboot_user
    password: SecurePass123!
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 30000
      connection-timeout: 30000
      max-lifetime: 1800000

  # JPA配置（方案3适用）
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update  # options: create/create-drop/update/validate
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect

三、三种实现方案

3.1 使用 JdbcTemplate（基础方案）

3.1.1 DAO 层实现

@Repository
public class UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<User> findAll() {
        return jdbcTemplate.query("SELECT * FROM users", 
            (rs, rowNum) -> new User(
                rs.getLong("id"),
                rs.getString("username"),
                rs.getString("email")
            ));
    }

    public User save(User user) {
        jdbcTemplate.update(
            "INSERT INTO users (username, email) VALUES (?, ?)",
            user.getUsername(), user.getEmail()
        );
        return user;
    }
}

3.2 集成 MyBatis（推荐方案）

3.2.1 Mapper 接口

@Mapper
public interface UserMapper {
    @Select("SELECT * FROM users WHERE id = #{id}")
    User findById(@Param("id") Long id);

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

3.2.2 XML 映射（复杂查询）

<!-- src/main/resources/mappers/UserMapper.xml -->
<mapper namespace="com.example.demo.mapper.UserMapper">
    <resultMap id="UserResultMap" type="User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="email" property="email"/>
    </resultMap>

    <select id="findByEmail" resultMap="UserResultMap">
        SELECT * FROM users WHERE email = #{email}
    </select>
</mapper>

3.3 Spring Data JPA（ORM方案）

3.3.1 实体类定义

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    @Column(nullable = false, length = 100)
    private String email;

    // Getters/Setters
}

3.3.2 Repository 接口

public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByEmail(String email);
    
    @Query("SELECT u FROM User u WHERE u.username LIKE %:keyword%")
    List<User> searchByUsername(@Param("keyword") String keyword);
}

四、服务层与控制器

4.1 服务层实现

@Service
@Transactional
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public Page<User> findAll(Pageable pageable) {
        return userRepository.findAll(pageable);
    }

    @Transactional
    public User createUser(User user) {
        return userRepository.save(user);
    }
}

4.2 REST 控制器

@RestController
@RequestMapping("/api/users")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping
    public ResponseEntity<Page<User>> getAllUsers(
        @PageableDefault(size = 20, sort = "id", direction = Sort.Direction.ASC) Pageable pageable
    ) {
        return ResponseEntity.ok(userService.findAll(pageable));
    }

    @PostMapping
    public ResponseEntity<User> createUser(@RequestBody User user) {
        return ResponseEntity.status(HttpStatus.CREATED).body(userService.createUser(user));
    }
}

五、高级配置

5.1 连接池监控（HikariCP）

management:
  endpoint:
    health:
      show-details: always
  endpoints:
    web:
      exposure:
        include: health,info,metrics,hikaricp

5.2 事务管理

@Transactional(
    propagation = Propagation.REQUIRED,
    isolation = Isolation.READ_COMMITTED,
    timeout = 30,
    rollbackFor = {SQLException.class, RuntimeException.class}
)
public void batchOperation() {
    // 批量操作
}

Tags：hikari连接池