Feb 10, 2024

Spring Boot数据库访问全解析

Spring Boot极大地简化了数据库访问的配置和使用，使开发者能够专注于业务逻辑而非基础设施。本文将全面介绍Spring Boot中几种主要数据库访问技术的使用方法与最佳实践。

Spring Data JPA：对象关系映射的最佳选择

Spring Data JPA是基于JPA规范的强大ORM解决方案，它能够显著减少数据访问层的代码量。

配置与依赖

首先在pom.xml中添加相关依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

在application.yml中配置数据源：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update  # 自动更新表结构
    show-sql: true      # 显示SQL语句
    properties:
      hibernate:
        format_sql: true  # 格式化SQL

实体类设计

创建JPA实体类：

@Entity
@Table(name = "products")
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false, length = 100)
    private String name;
    
    @Column(precision = 10, scale = 2)
    private BigDecimal price;
    
    @Column(length = 500)
    private String description;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "category_id")
    private Category category;
    
    @CreatedDate
    @Column(updatable = false)
    private LocalDateTime createdAt;
    
    @LastModifiedDate
    private LocalDateTime updatedAt;
    
    // 构造函数、getter和setter方法省略
}

创建Repository

Spring Data JPA的核心是Repository接口，它提供了强大的查询功能：

public interface ProductRepository extends JpaRepository<Product, Long> {
    // 根据名称查找产品
    List<Product> findByNameContaining(String name);
    
    // 根据价格区间查找
    List<Product> findByPriceBetween(BigDecimal minPrice, BigDecimal maxPrice);
    
    // 使用JPQL查询
    @Query("SELECT p FROM Product p WHERE p.price > ?1 AND p.category.id = ?2")
    List<Product> findExpensiveProductsByCategory(BigDecimal price, Long categoryId);
    
    // 原生SQL查询
    @Query(value = "SELECT * FROM products WHERE created_at > ?1", nativeQuery = true)
    List<Product> findRecentProducts(LocalDateTime date);
    
    // 自定义更新操作
    @Modifying
    @Transactional
    @Query("UPDATE Product p SET p.price = p.price * :rate WHERE p.category.id = :categoryId")
    int updatePriceForCategory(@Param("rate") double rate, @Param("categoryId") Long categoryId);
}

MyBatis：灵活的SQL映射框架

MyBatis提供了更多对SQL的控制，特别适合复杂查询和历史系统的集成。

配置与依赖

添加依赖：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>

配置MyBatis：

mybatis:
  mapper-locations: classpath:mappers/*.xml
  type-aliases-package: com.fashioncool.domain
  configuration:
    map-underscore-to-camel-case: true  # 开启驼峰命名转换
    cache-enabled: true                 # 启用缓存

XML映射文件

在src/main/resources/mappers目录下创建SQL映射文件OrderMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<mapper namespace="com.fashioncool.mapper.OrderMapper">
  <!-- 结果映射 -->
  <resultMap id="OrderResultMap" type="Order">
    <id property="id" column="id"/>
    <result property="orderNumber" column="order_number"/>
    <result property="totalAmount" column="total_amount"/>
    <result property="status" column="status"/>
    <result property="createdAt" column="created_at"/>
    <!-- 一对多关系映射 -->
    <collection property="items" ofType="OrderItem">
      <id property="id" column="item_id"/>
      <result property="productId" column="product_id"/>
      <result property="quantity" column="quantity"/>
      <result property="price" column="item_price"/>
    </collection>
  </resultMap>
  
  <!-- 复杂查询示例 -->
  <select id="findOrderWithItems" resultMap="OrderResultMap">
    SELECT o.id, o.order_number, o.total_amount, o.status, o.created_at,
           i.id as item_id, i.product_id, i.quantity, i.price as item_price
    FROM orders o
    LEFT JOIN order_items i ON o.id = i.order_id
    WHERE o.id = #{orderId}
  </select>
  
  <!-- 动态SQL示例 -->
  <select id="searchOrders" resultType="Order">
    SELECT * FROM orders
    <where>
      <if test="status != null">
        status = #{status}
      </if>
      <if test="minAmount != null">
        AND total_amount >= #{minAmount}
      </if>
      <if test="maxAmount != null">
        AND total_amount <= #{maxAmount}
      </if>
      <if test="startDate != null">
        AND created_at >= #{startDate}
      </if>
    </where>
    ORDER BY created_at DESC
  </select>
</mapper>

Mapper接口

@Mapper
public interface OrderMapper {
    // 查询订单及其明细
    Order findOrderWithItems(Long orderId);
    
    // 动态条件搜索
    List<Order> searchOrders(OrderSearchCriteria criteria);
    
    // 使用注解定义简单查询
    @Select("SELECT * FROM orders WHERE user_id = #{userId}")
    List<Order> findByUserId(Long userId);
    
    // 插入操作
    @Insert("INSERT INTO orders(order_number, user_id, total_amount, status) " +
            "VALUES(#{orderNumber}, #{userId}, #{totalAmount}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Order order);
}

Spring Data MongoDB：文档数据库的无缝集成

对于非关系型数据，MongoDB是一个流行的选择。Spring Boot提供了出色的MongoDB集成。

配置与依赖

添加依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>

配置连接：

spring:
  data:
    mongodb:
      uri: mongodb://localhost:27017/myapp

文档类设计

@Document(collection = "articles")
public class Article {
    @Id
    private String id;
    
    private String title;
    
    private String content;
    
    @Field("author_name")
    private String authorName;
    
    @DBRef
    private User author;
    
    private List<String> tags;
    
    @Indexed
    private LocalDateTime publishDate;
    
    // 构造函数、getter和setter方法省略
}

创建Repository

public interface ArticleRepository extends MongoRepository<Article, String> {
    // 根据标题查找
    List<Article> findByTitleContaining(String title);
    
    // 根据标签查找
    List<Article> findByTagsContaining(String tag);
    
    // 根据作者查找
    List<Article> findByAuthor(User author);
    
    // 自定义查询
    @Query("{'publishDate': {$gte: ?0, $lte: ?1}}")
    List<Article> findArticlesPublishedBetween(LocalDateTime start, LocalDateTime end);
    
    // 更新操作
    @Update("{'$addToSet': {'tags': ?1}}")
    void addTag(String articleId, String tag);
}

事务管理

无论使用哪种数据库访问技术，正确的事务管理都至关重要：

@Service
public class OrderService {
    private final OrderRepository orderRepository;
    private final ProductRepository productRepository;
    
    // 构造函数注入
    
    @Transactional  // 声明式事务
    public Order createOrder(OrderRequest request) {
        // 检查库存
        Product product = productRepository.findById(request.getProductId())
            .orElseThrow(() -> new ProductNotFoundException(request.getProductId()));
            
        if (product.getStock() < request.getQuantity()) {
            throw new InsufficientStockException(product.getId());
        }
        
        // 创建订单
        Order order = new Order();
        // 设置订单属性...
        
        // 减少库存
        product.setStock(product.getStock() - request.getQuantity());
        
        // 保存更改
        productRepository.save(product);
        return orderRepository.save(order);
    }
}

多数据源配置

在某些应用中，需要连接多个数据库：

@Configuration
public class MultipleDataSourceConfig {
    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    // 为不同数据源配置EntityManagerFactory
    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.fashioncool.domain.primary")
                .persistenceUnit("primary")
                .build();
    }
    
    // 配置事务管理器...
}

总结

Spring Boot为数据库访问提供了丰富的选择，可以根据项目需求选择最合适的技术：

Spring Data JPA：适合绿地项目，通过ORM简化数据访问
MyBatis：适合对SQL有更多控制需求的场景
Spring Data MongoDB：适合处理非结构化或半结构化数据

掌握这些技术，将帮助我们构建高效、可维护的数据访问层，为应用提供坚实的基础。