mybatis从mysql移植到sqllite问题总结

ゝ一纸荒年。 2022-05-31 01:39 501阅读 0赞

1、表结构和数据如何转换：请使用Navicat Premium，11版本就可以，处理方式如下：

选择：数据传输

[![image][]][image 1]

[![image][image 2]][image_image 2]

转化完成，数据和结构都会导入sqllite中

2、类型不匹配

mysql中的时间类型会转换为text格式，mybatis启动后会报错，格式转换错误，解决方案使用mybatis的TypeHandler

3、关于时间类型转换

public class LocalDateTimeTypeHandler  extends BaseTypeHandler<Date> {
        @Override
        public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
            Instant instant = parameter.toInstant();
            ZoneId zoneId = ZoneId.of ( "Asia/Shanghai" );//使用北京时区
            ZonedDateTime zdt = ZonedDateTime.ofInstant ( instant , zoneId );
            LocalDate localDate = zdt.toLocalDate();
            java.sql.Date sqlDate = java.sql.Date.valueOf( localDate );
            ps.setDate(i, sqlDate);
        }
    
        @Override
        public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
            String value = rs.getString(columnName);
            return convertToDate(value);
        }
    
        @Override
        public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
            String value = rs.getString(columnIndex);
            return convertToDate(value);
        }
    
        @Override
        public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
            String value = cs.getString(columnIndex);
            return convertToDate(value);
        }
    
        private Date convertToDate(String strDate){
            SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss");
            try {
                return sdf.parse(strDate);
            }catch(Exception ex){
                ex.printStackTrace();
            }
            return  null;
        }
    }

config.xml文件增加映射（也可以用注解）

我的mapping文件格式设置：

参考文档：

[http://www.mybatis.org/mybatis-3/configuration.html\#typeHandlers][http_www.mybatis.org_mybatis-3_configuration.html_typeHandlers]

[https://stackoverflow.com/questions/25113579/java-8-localdate-mapping-with-mybatis][https_stackoverflow.com_questions_25113579_java-8-localdate-mapping-with-mybatis]

[https://stackoverflow.com/questions/530012/how-to-convert-java-util-date-to-java-sql-date][https_stackoverflow.com_questions_530012_how-to-convert-java-util-date-to-java-sql-date]

[image]: /images/20220531/d8c26b525ed94b6ead5a2e650cc396e3.png
[image 1]: https://img-blog.csdn.net/20180202215938940
[image 2]: https://img-blog.csdn.net/20180202215940109
[image_image 2]: /images/20220531/57e77488006b4e96ad7dcc984b324e4f.png
[http_www.mybatis.org_mybatis-3_configuration.html_typeHandlers]: http://www.mybatis.org/mybatis-3/configuration.html#typeHandlers
[https_stackoverflow.com_questions_25113579_java-8-localdate-mapping-with-mybatis]: https://stackoverflow.com/questions/25113579/java-8-localdate-mapping-with-mybatis
[https_stackoverflow.com_questions_530012_how-to-convert-java-util-date-to-java-sql-date]: https://stackoverflow.com/questions/530012/how-to-convert-java-util-date-to-java-sql-date