mybatis orm解决方案
1 时区具体做法:
1.1
public class TimezoneTypeHandler extends BaseTypeHandler<Date> {
protected int dbZone;
public TimezoneTypeHandler(int dbZone) {
this.dbZone = dbZone;
}
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date dateJvm, JdbcType jdbcType) throws SQLException {
if(dateJvm != null) {
Date dateDb = TimezoneManager.timezoneJvmToDbWhenInsertAndUpdate(dateJvm, this.dbZone);
preparedStatement.setDate(i, new java.sql.Date(dateDb.getTime()));
}
}
@Override
public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
java.sql.Date dateDb = resultSet.getDate(s);
return getJvmDateByDbDate(dateDb);
}
【下省略这2个方法】
@Override
public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
java.sql.Date dateDb = resultSet.getDate(i);
return getJvmDateByDbDate(dateDb);
}
@Override
public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
java.sql.Date dateDb = callableStatement.getDate(i);
return getJvmDateByDbDate(dateDb);
}
private Date getJvmDateByDbDate(java.sql.Date dateDb) {
if(dateDb == null) return null;
return TimezoneManager.timezoneDbToJvmWhenQuery(new Date(dateDb.getTime()), this.dbZone);
}
}
public class TimezoneNewYorkTypeHandler extends TimezoneTypeHandler {
private static final int DB_ZONE_NEW_YORK = -4;
public TimezoneNewYorkTypeHandler() {
super(DB_ZONE_NEW_YORK);
}
}
@Result(column = "LOAD_TIME", property = "loadTime", typeHandler = TimezoneNewYorkTypeHandler.class),
LOAD_TIME = #{attachmentDto.loadTime,typeHandler = com.xxx.config.typehandler.TimezoneNewYorkTypeHandler},
1.2 bug
1.2.1 重大bug，这里应用java.sql.Timestamp，而不是java.sql.Date，虽然用Date时有时也能取到时分秒
1.2.2 插入/更新时，对于输入为null的Date，jdbcType=TIMESTAMP没用，需要
@Override
public void setParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
if (parameter == null) {
if (jdbcType == null) {
throw new TypeException("JDBC requires that the JdbcType must be specified for all nullable parameters.");
}
try {
ps.setDate(i, null);
} catch (SQLException e) {
throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . " +
"Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. " +
"Cause: " + e, e);
}
} else {
try {
setNonNullParameter(ps, i, parameter, jdbcType);
} catch (Exception e) {
throw new TypeException("Error setting non null for parameter #" + i + " with JdbcType " + jdbcType + " . " +
"Try setting a different JdbcType for this parameter or a different configuration property. " +
"Cause: " + e, e);
}
}
}
BaseTypeHandler中的setParameter方法：
ps.setNull(i, jdbcType.TYPE_CODE);
OTHER(1111),
怪不得老是报1111
1.2.3
setNonNullParameter
中，可以不做非null判断，因为看方法名就知道了，传进来的肯定是非null
2 orm具体做法：
RequestDto
long requestId
UserDto user
Set<CommunicationDto> setCommunication
Set<Attach> setAttach
CommunicationDto
ccommunicationId
UserDto User
requestId
Set<AttahcmentDto>
AttachmentDto
communicationId
request detailId userid ... commiunication communicationId userId requestid ... attach communicationId
requestId
request
detail 级联插入，更新request时，可插入detail、可更新detail，看detailid
set<communication> 级联插入，分离更新
user 插入request时，认为user永远已存在
Set<Attach>
communication
Set<attach> 级联插入，分离更新
user 插入communication时，认为user永远已存在
2.1 communication中查询user，插入/更新user
（request中查询user，request中查询detail）
2.1.1
@Result(column = "UPDATE_USER", property = "User", typeHandler = UserDtoOneOneTypeHandler.class),
"#{RequestCommunicationDto.User,typeHandler = com.xxx.config.typehandler.UserDtoOneOneTypeHandler},\n" +
public class UserDtoOneOneTypeHandler extends BaseTypeHandler<UserDto> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, UserDto UserDto, JdbcType jdbcType) throws SQLException {
if(UserDto != null) {
String id = UserDto.getUserId();
preparedStatement.setString(i, id);
}
}
@Override
public UserDto getNullableResult(ResultSet resultSet, String s) throws SQLException {
String id = resultSet.getString(s);
return getUserById(id);
}
private UserDto getUserById(String id) {
Injector injector = CRFGuiceContext.getInjector();
UserMapper userMapper = injector.getInstance(UserMapper.class);
UserDto user = userMapper.queryForObject(id);
return user;
}
}
2.1.2 bug
2.1.2.1 setPara时，加入communication中userDto为null，则在userId这个字段插入null，否则插入userId
可不做非null判断，同1.2.3
2.1.2.2 要考虑join字段db查出来为null情况：禁止用基本数据类型接收resultSet.getid，Integer-》int直接报错
private UserDto getUserById(String id) {
if(id == null || "".equals(id))
return null;
Injector injector = CRFGuiceContext.getInjector();
UserMapper userMapper = injector.getInstance(UserMapper.class);
UserDto user = userMapper.queryForObject(id);
return user;
}
2.1.2.3 要考虑插入/更新时，无关联对象mybatis报错
"#{RequestCommunicationDto.User,jdbcType=NUMERIC,typeHandler = com.xxx.config.typehandler.UserDtoOneOneTypeHandler},\n" +
否则报错：同1.2.2
Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='RequestCommunicationDto.User', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #3 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type: 1111
at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:85)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:63)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:77)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)
... 52 common frames omitted
Caused by: org.apache.ibatis.type.TypeException: Error setting null for parameter #3 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type: 1111
at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:47)
at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87)
... 59 common frames omitted
Caused by: java.sql.SQLException: Invalid column type: 1111
2.2 communication中查询Set<Attach>
（Request中查询Set<Communication>，Request中查询Set<Attach>
2.2.1
@Result(column = "_UPDATE_ID", property = "UpdateId"),
@Result(column = "_UPDATE_ID", property = "attachment", typeHandler = AttachmentInCommunicationOneManyTypeHandler.class),
updateid===communicationId，主键建2个Result，一个主键field，一个利用communication主键查询attach表，set<Attach>
public class AttachmentInCommunicationOneManyTypeHandler extends AbstractAttachmentOneManyTypeHandler {
@Override
protected Set<AttachmentDto> getAttachment(Long communicationId) {
Injector injector = CRFGuiceContext.getInjector();
AttachmentMapper attachmentMapper = injector.getInstance(AttachmentMapper.class);
Set<AttachmentDto> set = attachmentMapper.queryForSetByCommunicationId(communicationId);
return set;
}
}
因为request中也有Set<Attach>，getAttachment拉出来，对Commnication就是根据CommunicationId查询Set，对Request就是根据RequestId查询Set
abstract public class AbstractAttachmentOneManyTypeHandler extends BaseTypeHandler<Set<AttachmentDto>> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Set<AttachmentDto> attachmentDtoSet, JdbcType jdbcType) throws SQLException {
// TODO: 8/2/2020 insert set attach when insert communication【but not here】
}
@Override
public Set<AttachmentDto> getNullableResult(ResultSet resultSet, String s) throws SQLException {
Long communicationId = resultSet.getLong(s);
return getAttachment(communicationId);
}
abstract protected Set<AttachmentDto> getAttachment(Long communicationId);
}
2.2.2 bug
2.2.2.1
select输入非空判断：虽然输入的是主键
@Override
protected Set<AttachmentDto> getAttachment(Long communicationId) {
if(communicationId == null)
return Collections.emptySet();
Injector injector = CRFGuiceContext.getInjector();
AttachmentMapper attachmentMapper = injector.getInstance(AttachmentMapper.class);
Set<AttachmentDto> set = attachmentMapper.queryForSetByCommunicationId(communicationId);
return set;
}
**********************************
2.3 插入request，插入detail
先插入/更新（取决于detail有无id）detail，取得sequence，set request detailid，插入request
2.3.1 插入，插入detail，忽视已存在的detail
"REQUEST_ID,\n" +
"_DETAILS_ID,\n" +
"#{RequestDto,typeHandler = com.xxx.config.typehandler.RequestInsertTypeHandler},\n" +
"<if test=\"RequestDto.Details != null \">#{RequestDto.Details.DetailsId, jdbcType=NUMERIC},</if>\n" +
"<if test=\"RequestDto.Details == null \">null,</if>\n" +
public class RequestInsertTypeHandler extends BaseTypeHandler<RequestDto> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, RequestDto requestDto, JdbcType jdbcType) throws SQLException {
Injector injector = CRFGuiceContext.getInjector();
DetailsDto detailsDto = requestDto.getDetails();
DetailsCUDMapper detailsCUDMapper = injector.getInstance(DetailsCUDMapper.class);
if(detailsDto != null && detailsDto.getDetailsId() == null) {
detailsCUDMapper.insert(detailsDto);
}
// when insert request, if request.detail has id, do nothing
if(detailsDto != null && detailsDto.getDetailsId() != null) {
;
}
2.3.2 更新request，插入/更新detail
"REQUEST_ID = #{RequestDto, typeHandler = com.xxx.config.typehandler.RequestUpdateTypeHandler}, \n" +
"<if test=\"RequestDto.Details != null \">_DETAILS_ID = #{RequestDto.Details.DetailsId, jdbcType=NUMERIC},</if>" +
"<if test=\"RequestDto.Details == null \">null,</if>\n" +
public class RequestUpdateTypeHandler extends BaseTypeHandler<RequestDto> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, RequestDto requestDto, JdbcType jdbcType) throws SQLException {
Injector injector = CRFGuiceContext.getInjector();
DetailsDto detailsDto = requestDto.getDetails();
DetailsCUDMapper detailsCUDMapper = injector.getInstance(DetailsCUDMapper.class);
if(detailsDto != null && detailsDto.getDetailsId() == null) {
detailsCUDMapper.insert(detailsDto);
}
if(detailsDto != null && detailsDto.getDetailsId() != null) {
detailsCUDMapper.update(detailsDto);
}
2.4 插入communication时，插入Set<Attach>
（插入request，插入Set<Communication>，Set<Attach>）
2.4.1
取得request sequence，set communication中requestId，插入set<communication>，插入request
"_UPDATE_ID,\n" + "UPDATE_TIME,\n" + "UPDATE_USER,\n" +
"#{RequestCommunicationDto,typeHandler = com.xxx.config.typehandler.CommunicationInsertTypeHandler},\n" +
"#{RequestCommunicationDto.updateTime,typeHandler = com.xxx.config.typehandler.TimezoneNewYorkTypeHandler},\n" +
"#{RequestCommunicationDto.User,typeHandler = com.xxx.config.typehandler.UserDtoOneOneTypeHandler},\n" +【要 jdbcType=NUMERIC】
务必注意末字段尾部不能有逗号
CommunicationInsertTypeHandler.setNonNullParameter 解决Set<Attach>
TimezoneTypeHandler.setNonNullParameter 解决插入时间时区
UserDtoOneOneTypeHandler.setNonNullParameter 解决Communication下UserDto对象取出主键，db插入，若没有，为null，则走jdbcType=NUMERIC
public class CommunicationInsertTypeHandler extends BaseTypeHandler<RequestCommunicationDto> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, RequestCommunicationDto communicationDto, JdbcType jdbcType) throws SQLException {
String sequenceName = communicationDto.getSequenceName();
Injector injector = CRFGuiceContext.getInjector();
SequenceMapper sequenceMapper = injector.getInstance(SequenceMapper.class);
AttachmentMapper attachmentMapper = injector.getInstance(AttachmentMapper.class);
Long communicationId = sequenceMapper.queryForSequence(sequenceName);
communicationDto.setUpdateId(communicationId);
Set<AttachmentDto> set = communicationDto.getAttachment();
if(set != null && set.size() > 0) {
for(AttachmentDto attachmentDto : set) {
attachmentDto.setCommunicationId(communicationId);
attachmentMapper.insert(attachmentDto);
}
}
preparedStatement.setLong(i, communicationId);
}
取得sequence
主键回写
Set<Attach> 插入communicationid， insert
将主键给到mybatis VALUE
2.4.2 改进：
"#{RequestCommunicationDto,typeHandler = com.xxx.config.typehandler.CommunicationInsertTypeHandler},\n" +
"#{RequestCommunicationDto.updateTime,typeHandler = com.xxx.config.typehandler.TimezoneNewYorkTypeHandler},\n" +
"#{RequestCommunicationDto.User,jdbcType=NUMERIC,typeHandler = com.xxx.config.typehandler.UserDtoOneOneTypeHandler},\n" +
"#{RequestCommunicationDto.comments,jdbcType=VARCHAR},\n" +
"#{RequestCommunicationDto.requestId,jdbcType=NUMERIC},\n" +
"#{RequestCommunicationDto.reconFlag,jdbcType=VARCHAR}\n" +
")";
@SelectKey(statement= "SELECT req.nextval FROM DUAL", before = true, keyProperty = "RequestCommunicationDto.UpdateId", resultType = Long.class)
@Insert(INSERT)
int insert(@Param("RequestCommunicationDto") RequestCommunicationDto RequestCommunicationDto);
public class CommunicationInsertTypeHandler extends BaseTypeHandler<RequestCommunicationDto> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, RequestCommunicationDto communicationDto, JdbcType jdbcType) throws SQLException {
Injector injector = CRFGuiceContext.getInjector();
AttachmentMapper attachmentMapper = injector.getInstance(AttachmentMapper.class);
Set<AttachmentDto> set = communicationDto.getAttachment();
if(set != null && set.size() > 0) {
for(AttachmentDto attachmentDto : set) {
attachmentDto.setCommunicationId(communicationDto.getUpdateId());
attachmentMapper.insert(attachmentDto);
}
}
preparedStatement.setLong(i, communicationDto.getUpdateId());
}
获取sequence与主键回写托管给MyBatis
3 改进
3.1 对2.1 2.2的改进
2.1.1查询
String QUERY_FOR_OBJECT = "SELECT UPDATE_USER,\n" + 之前就是因为要查的字段没在select里面导致使用one失败
@Result(column = "UPDATE_USER", property = "User",
one = @One(select="com.xxx.config.module.ods.riskods..UserMapper.queryForObject")),
2.1.1 插入
"<if test=\"RequestCommunicationDto.User != null \">#{RequestCommunicationDto.User.userId, jdbcType=NUMERIC},</if>\n" +
"<if test=\"RequestCommunicationDto.User == null \">null,</if>\n" +
此处还是要jdbcType=，防止userId为null，User不为null
直接给null居然可以
2.2 查询
@Result(column = "_UPDATE_ID", property = "attachment", javaType = Set.class,
many = @Many(select="com.cxxx.config.module.ods.riskods..AttachmentMapper.queryForSetByCommunicationId")),
4 小结：
1）插入，many_to_one 不处理 one_to_one 插入，one_to_many插入，many_to_many不处理
2）更新，many_to_one 不处理 one_to_one 插入or更新，one_to_many不处理，many_to_many不处理
3）删除，不处理
4）查询，4个注解都处理
