Java 数据库访问 Convert query resultS
2018-11-26 本文已影响0人
金1746
这种操作主要用于复杂sql的处理，简单的操作都可以用jpa接口，更方便
Spring jdbcTemplate
Spring 提供了一个便利的RowMapper实现-----BeanPropertyRowMapper
它可自动将一行数据映射到指定类的实例中 它首先将这个类实例化，然后通过名称匹配的方式，映射到属性中去。
例如：属性名称（vehicleNo）匹配到同名列或带下划线的同名列（VEHICLE_NO）。如果某个属性不匹配则返回属性值为Null
@Override
public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException {
return query(sql, new RowMapperResultSetExtractor<T>(rowMapper));
}
即可以映射自定义dto，也可以映射entity class。
JOPO：
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SendLeads {
private String source;
private String subSource;
@JSONField(serializeUsing = ToStringSerializer.class)
private Long id;
private String name;
private String sex;
private String mobile;
@JSONField(format = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
private String dealerID;
private String provinceID;
private String cityID;
private String seriesID;
private String specID;
private String visitSource;
}
访问方法：
public List<SendLeads> findClientRoughLeads(LocalDateTime startTime, LocalDateTime endTime) {
List<SendLeads> query = jdbcTemplate.query(SELECT_SQL,
new Object[]{convertToString(startTime), convertToString(endTime)},
new BeanPropertyRowMapper<>(SendLeads.class));
return query;
}
如果自定义实体某个字段需要特殊处理，可以自定义RowMapper，实现MapRow方法
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SendLeads implements RowMapper {
private String source;
private String subSource;
@JSONField(serializeUsing = ToStringSerializer.class)
private Long id;
private String name;
private String sex;
private String mobile;
@JSONField(format = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
private String dealerID;
private String provinceID;
private String cityID;
private String seriesID;
private String specID;
private String visitSource;
public SendLeads(String source, String mobile, String dealerID) {
this.source = source;
this.subSource = "";
this.id = 22L;
this.name = "";
this.sex = "";
this.mobile = mobile;
this.createTime = null;
this.dealerID = dealerID;
this.provinceID = "";
this.cityID = "";
this.seriesID = "";
this.specID = "";
this.visitSource = "";
}
@Override
public SendLeads mapRow(ResultSet rs, int rowNum) throws SQLException {
SendLeads sendLeads = new SendLeads();
source = rs.getString("source");
subSource = rs.getString("subSource");
id = rs.getLong("id");
name = rs.getString("name");
sex = rs.getString("sex");
mobile = rs.getString("mobile");
createTime = rs.getTimestamp("createTime").toLocalDateTime();
/**
* 复杂计算
*/
//.....
dealerID = rs.getString("dealerID");
provinceID = rs.getString("provinceID");
cityID = rs.getString("cityID");
seriesID = rs.getString("seriesID");
specID = rs.getString("specID");
visitSource = rs.getString("visitSource");
return sendLeads;
}
}
Spring Data Jpa
一、使用原生Sql
1.映射Entity class
@Entity
@Table(name = "user")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column
private Long id;
@Column
private String name;
@Column
private String phone;
@Column
private LocalDateTime createdDate;
@Column
private LocalDateTime modifiedDate;
}
Query q = entityManager.createNativeQuery("SELECT * FROM User", User.class);
List<User> users = q.getResultList();
2.映射自定义Dto
使用@SqlResultSetMapping，@ConstructorResult 结合 @NamedNativeQueries 做映射
自定义Dto
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserDto {
private Long id;
private String name;
private String phone;
private LocalDateTime createdDate;
private LocalDateTime modifiedDate;
}
配置SqlResultSetMapping，@NamedNativeQueries
@SqlResultSetMapping(
name = "UserDtoMapping",
classes = @ConstructorResult(
targetClass = UserDto.class,
columns = {
@ColumnResult(name = "id", type = Long.class),
@ColumnResult(name = "name"),
@ColumnResult(name = "phone"),
@ColumnResult(name = "created_date", type = LocalDateTime.class),
@ColumnResult(name = "modified_date", type = LocalDateTime.class)}))
@NamedNativeQueries({
@NamedNativeQuery(
name = "findUser",
query = "SELECT * FROM User",
resultSetMapping = "UserDtoMapping"
)
})
@Entity
@Table(name = "user")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
public static final String FIND_USER = "findUser";
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column
private Long id;
@Column
private String name;
@Column
private String phone;
@Column
private LocalDateTime createdDate;
@Column
private LocalDateTime modifiedDate;
}
注意事项：
Where to place @SqlResultSetMapping in case of @ConstructorResult ?
@SqlResultSetMapping can be placed at any entity class (don't annotate POJOs - it won't work). Mapping to POJO class with @ConstructorResult was added in version 2.1 of JPA. POJO used with the mapping has to have correct constructor.
@SqlResultSetMapping 与所放置的Entity class 没有任何关系，只是因为@SqlResultSetMapping 需要放置在Entity Class 上，任意找了一个Entity class
使用：
Query q = entityManager.createNamedQuery(User.FIND_USER);
List<UserDto> users = q.getResultList();
更进一步优化：
The suggestion of putting the @SqlResultSetMapping and @NamedNativeQuery (or @NamedQuery) inside the @Entity class definition is not elegant and evidently does not follow the separation of concerns principle.
The more proper solution is the usage of the @MappedSuperclass annotation as the following:
UserDtoExtend.java (the class must be abstract):
@MappedSuperclass
@SqlResultSetMapping(
name = "UserDtoMapping",
classes = @ConstructorResult(
targetClass = UserDto.class,
columns = {
@ColumnResult(name = "id", type = Long.class),
@ColumnResult(name = "name"),
@ColumnResult(name = "phone"),
@ColumnResult(name = "created_date", type = LocalDateTime.class),
@ColumnResult(name = "modified_date", type = LocalDateTime.class)}))
@NamedNativeQueries({
@NamedNativeQuery(
name = "findUser",
query = "SELECT * FROM User",
resultSetMapping = "UserDtoMapping"
)
})
public abstract class UserDtoExtend {
public static final String FIND_USER = "findUser";
}
二、使用 JPQL @Query
自定义Dto:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserDto {
private Long id;
private String name;
private String phone;
private LocalDateTime createdDate;
private LocalDateTime modifiedDate;
public UserDto(Long id, String name, String phone) {
this.id = id;
this.name = name;
this.phone = phone;
}
}
Entity class :
@Entity
@Table(name = "user")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column
private Long id;
@Column
private String name;
@Column
private String phone;
@Column
private LocalDateTime createdDate;
@Column
private LocalDateTime modifiedDate;
}
声明：
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
/**
* @return
*/
@Query(value = "select new com.test.dto.UserDto(a.id,a.name,a.phone,a.createdDate,a.modifiedDate) from User as a")
List<UserDto> findUserDto();
}
使用：
List<UserDto> userDtos = userRepository.findUserDto();
参考：
