spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  mapper-locations: classpath:mappers/*.xml
  type-aliases-package: com.example.entity
  configuration:
    map-underscore-to-camel-case: true  # 開啟駝峰映射

@Mapper
public interface UserMapper {
    @Select("SELECT * FROM user WHERE id = #{id}")
    User findById(@Param("id") Long id);
    @Insert("INSERT INTO user(name, age) VALUES(#{name}, #{age})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(User user);
    @Update("UPDATE user SET name=#{name}, age=#{age} WHERE id=#{id}")
    void update(User user);
    @Delete("DELETE FROM user WHERE id=#{id}")
    void deleteById(@Param("id") Long id);
}

<mapper namespace="com.example.mapper.UserMapper">
    <resultMap id="UserMap" type="User">
        <id property="id" column="id"/>
        <result property="userName" column="name"/>
        <result property="age" column="age"/>
    </resultMap>
    <select id="findById" resultMap="UserMap">
        SELECT * FROM user WHERE id = #{id}
    </select>
    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user (name, age) VALUES (#{userName}, #{age})
    </insert>
</mapper>

<select id="findUsers" resultMap="UserMap">
    SELECT * FROM user
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="minAge != null">
            AND age >= #{minAge}
        </if>
        <if test="maxAge != null">
            AND age <![CDATA[ <= ]]> #{maxAge}
        </if>
        <if test="statusList != null and !statusList.isEmpty()">
            AND status IN
            <foreach collection="statusList" item="status" open="(" separator="," close=")">
                #{status}
            </foreach>
        </if>
    </where>
    ORDER BY id DESC
</select>

@Select({
    "<script>",
    "SELECT * FROM user",
    "<where>",
    "<if test='name != null'>AND name LIKE CONCAT('%', #{name}, '%')</if>",
    "</where>",
    "</script>"
})
List<User> findUsers(@Param("name") String name);

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update  # 開發(fā)環(huán)境可用，生產(chǎn)慎用
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        dialect: org.hibernate.dialect.MySQL8Dialect

@Entity
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name", nullable = false)
    private String name;
    @Column(name = "age")
    private Integer age;
    // 構(gòu)造函數(shù)、getter、setter
}

public interface UserRepository extends JpaRepository<User, Long> {
    // 派生查詢
    List<User> findByNameContaining(String name);
    List<User> findByAgeGreaterThan(Integer age);
    List<User> findByNameAndAge(String name, Integer age);
    // 排序
    List<User> findByNameOrderByAgeDesc(String name);
    // 分頁
    Page<User> findByNameContaining(String name, Pageable pageable);
}

@Query("SELECT u FROM User u WHERE u.name LIKE %:name% AND u.age > :age")
List<User> findByCustomJPQL(@Param("name") String name, @Param("age") int age);
@Query(value = "SELECT * FROM user u WHERE u.name LIKE CONCAT('%', :name, '%')", nativeQuery = true)
List<User> findByCustomNative(@Param("name") String name);

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}
@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    public List<User> searchUsers(String name, Integer minAge, Integer maxAge) {
        Specification<User> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (name != null && !name.trim().isEmpty()) {
                predicates.add(cb.like(root.get("name"), "%" + name + "%"));
            }
            if (minAge != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("age"), minAge));
            }
            if (maxAge != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("age"), maxAge));
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        };
        return userRepository.findAll(spec);
    }
}

// 分頁
Pageable pageable = PageRequest.of(0, 10);
Page<User> page = userRepository.findAll(pageable);
// 排序
Sort sort = Sort.by(Sort.Direction.DESC, "id");
List<User> users = userRepository.findAll(sort);
// 分頁 + 排序
PageRequest pageRequest = PageRequest.of(0, 10, Sort.by("id").descending());