Java - jdbc & mybatis

jdbc

首先配置maven包

<dependency><!-- jdbc -->
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency><!--mybatis-->
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>

注意pom.xml中配置结点

<resources>
    <resource>
        <directory>src/main/java</directory>
        <includes>
            <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
    </resource>
    <resource>
        <directory>src/main/resources</directory>
        <includes>
            <include>static/*.*</include>
            <include>templates/*.*</include>
            <include>**/*.xml</include>
            <include>**/*.yml</include>
            <include>**/*.properties</include>
        </includes>
        <filtering>false</filtering>
    </resource>
</resources>

针对不同的数据库，引入对应的包：SqlServer-sqljdbc4，DB2-db2jcc4，以SqlServer为例
在com.cmb.**.mapper.sqlserver目录下新建接口文件

@Repository
public interface SqlserverMapper {
    List<String> function_name();
}

同时在resources/mapperconfig/sqlserver目录下新建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.cmb.afvapp.api.mapper.sqlserver.SqlserverMapper">
    <select id="function_name" resultType="String">
        select ... from ...
    </select>
</mapper>

数据源类和工厂类配置如下

@MapperScan(basePackages = "com.cmb.**.mapper.sqlserver", sqlSessionFactoryRef = "sqlserverSqlSessionFactoryBean")
@SpringBootConfiguration
public class SqlserverDataSourceConfiguration {
    private static String jdbcUrl, jdbcUser, jdbcPassword, maximumPoolSize;
    static { ... }

    @Bean(name = "sqlserverDataSource")
    public DataSource createDataSource() {
        return DataSourceConfig.getHikariDataSourceInstance(jdbcUrl, jdbcUser, jdbcPassword, maximumPoolSize);
    }
}

@Configuration
public class SqlserverSessionFactoryConfiguration {
    @Autowired
    @Qualifier(value = "sqlserverDataSource")
    private DataSource sqlserverDataSource;
    private String mapperXmlConfigPath = "/mapperconfig/sqlserver/**.xml", mapperPackagePath = "com.cmb.**.mapper.sqlserver";

    @Bean(name = "sqlserverSqlSessionFactoryBean")
    public SqlSessionFactoryBean createSqlSessionFactory() throws Exception {
        return DataSourceConfig.getSqlSessionFactoryBeanInstance(mapperXmlConfigPath, mapperPackagePath, sqlserverDataSource);
    }
}

如需事务操作，上述配置类中分别新增

@Bean(name = "sqlserverTransactionManager")
@Primary
public DataSourceTransactionManager createTransactionManager(@Qualifier("sqlserverDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}

@Bean(name = "sqlserverSqlSessionTemplate")
@Primary
public SqlSessionTemplate createSqlSessionTemplate(@Qualifier("sqlserverSqlSessionFactoryBean") SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
}

相关疑问可移步：http://www.imooc.com/article/299316?block_id=tuijian_wz
其中，DataSourceConfig工具类

public static HikariDataSource getHikariDataSourceInstance(String jdbcUrl, String jdbcUser, String jdbcPassword, String maximumPoolSize) {
    HikariDataSource source = new HikariDataSource();
    source.setJdbcUrl(jdbcUrl);
    source.setUsername(jdbcUser);
    source.setPassword(jdbcPassword);
    source.setMaximumPoolSize(Integer.valueOf(maximumPoolSize));
    return source;
}
public static SqlSessionFactoryBean getSqlSessionFactoryBeanInstance(String mapperXmlConfigPath, String mapperPackagePath, DataSource ds) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    String xmlConfigPath = PathMatchingResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + mapperXmlConfigPath;
    bean.setMapperLocations(resolver.getResources(xmlConfigPath));//设置mapper对应的XML文件的路径 
    bean.setDataSource(ds);//设置数据源
    bean.setTypeAliasesPackage(mapperPackagePath);//设置mapper接口所在的包
    bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
    return bean;
}

附上配置文件

- name: afvapp_hikari_sql_jdbcUrl
  value: "jdbc:sqlserver://55.14.18.118:1433;DatabaseName=Business"
- name: afvapp_hikari_sql_username
  value: "sa"
- name: afvapp_hikari_sql_password
  value: "cmbzephyr"
- name: afvapp_hikari_sql_maximumPoolSize
  value: "25"

若是mysql库，记得追加?Unicode=true&characterEncoding=UTF-8，否则部分数据库会中文乱码

mybatis

★₯₰☆ SpringBoot中设置返回类型map，值为空时字段不显示
查了几种方法，均无效...

application.properties中配置 mybatis.configuration.call-setters-on-nulls=true

最后通过配置SqlSessionFactoryBean实现：高铁直达

org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
config.setCallSettersOnNulls(true);
bean.setConfiguration(config);

对于字段送默认值的问题，同时设置config.setJdbcTypeForNull(JdbcType.NULL);

★₯₰☆ #{}和${}备忘
对sql语句预编译前，会动态解析为一个BoundSql对象，#{}被解析为一个参数占位符?，${}直接被纯碎的string替换
考虑到性能问题和防sql注入：

• 能使用#{}的地方就用#{}
• 表名作为变量时，必须用${}
• 传入数值时，select top等也建议${}

#{}：创建预处理语句属性从而设置安全的值，常用作查询条件的值、指定一个确切的数据类型
${}：不会修改或转义字符串，直接在SQL语句中插入一个不改变的字符串，常用于拼凑sql的实体部分
详情参见：动态sql中的#与$

★₯₰☆ 动态sql
先来一个mybatis的案例热热身：mybatis遇坑填坑
数据库字段binary8的问题

//sqlserver
sql语句：where UniqueID=${uniqueID}，代码入参送值："0x22CE15DD10F7F70F"
预编译后：where UniqueID=0x22CE15DD10F7F70F
//db2
sql语句：where UniqueID=x${uniqueID}，代码入参送值："'2213C85A0004376B'"
预编译后：where VerifyID=x'2213C85A0004376B'

定义resultMap通常需类型转换，须引入mybatis-typehandlers-jsr310，并配置属性typeHandler，注意要以全局限定绝对路径。通常无需指定，mybatis会自动探测。建议同时引入jackson-datatype-jsr310。
实体类字段Java.sql.Date、java.sql.Timestamp、java.util.Date映射数据库的date、timestamp、datetime字段
对mybatis返回的时间戳作格式化处理，可在ObjectMapper反序列化时统一处理，与具体数据库解耦

@Component
public class JacksonConfig {
  @Bean
  @Primary
  public ObjectMapper objectMapper() {
    ObjectMapper mapper = new ObjectMapper();
    mapper.registerModule(new JSR310Module());
    mapper.setDateFormat(new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"));
    return mapper;
  }
}

问题：sqlserver列类型datetime，实体类字段为java.time.LocalDateTime，mybatis映射报错

• 1、java.util.Date可以，或直接用String
• 2、typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"无效
• 3、mybatis-3.5.0直接可以，无需typeHandler

第2种：mybatis-spring-boot-starter-2.1默认引入的mybatis-3.5.2抛异常且捕获不到，请求返回结果报错：3.5.x>=1均不行：mybatis 3.5.X的特性与bug

"status": 500,
"error": "Internal Server Error",
"message": "Method com/microsoft/sqlserver/jdbc/SQLServerResultSet.getObject(Ljava/lang/String;Ljava/lang/Class;)Ljava/lang/Object; is abstract",

此时需自定义TypeHandler，并typeHandler="com.cmb.afvapp.common.handler.MyLocalDateTimeTypeHandler"

public class MyLocalDateTimeTypeHandler extends LocalDateTimeTypeHandler {
    @Override
    public LocalDateTime getResult(ResultSet rs, String columnName) throws SQLException {
        Object object = rs.getObject(columnName);
        System.out.println(columnName + ": " + object);
        if(object instanceof java.sql.Timestamp){//强转，将sql的时间转为LocalDateTime
            return LocalDateTime.ofInstant(((Timestamp)object).toInstant(), ZoneOffset.ofHours(0));//按需转化
        }
        return super.getResult(rs, columnName);
    }
}

关于typehandler的映射问题，参见：mybatis-typeHandlers
若遇到type-aliases-package、type-handlers-package等不起作用，导致类加载失败问题，解决：

sqlSessionFactoryBean.setVfs(SpringBootVFS.class);

★₯₰☆ 全局日期格式化问题
LocalDateTime默认ISO时间：'2011-11-03T10:15:30'，提供几种格式化方法：好文参见
可以采用注解方式

@JsonSerialize(using = LocalDateTimeSerializer.class)
@JsonDeserialize(using = LocalDateTimeDeserializer.class) 
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")

若序列化后有小时差，需json序列化时指定时区为GMT+8。
或在配置类里定义两个Bean，可完成全局日期格式化处理，同时兼顾Date和LocalDateTime并存

@Configuration
public class LocalDateTimeSerializerConfig {
    @org.springframework.beans.factory.annotation.Value("${spring.jackson.date-format:yyyy-MM-dd HH:mm:ss}")
    private String pattern_format;
    @Bean
    public LocalDateTimeSerializer localDateTimeDeserializer() {
        return new LocalDateTimeSerializer(DateTimeFormatter.ofPattern(pattern_format));
    }
    @Bean
    public Jackson2ObjectMapperBuilderCustomizer jackson2ObjectMapperBuilderCustomizer() {
        return builder -> builder.serializerByType(LocalDateTime.class, localDateTimeDeserializer());
    }
}

或者采用

@Bean(name = "mapperObject") 
public ObjectMapper getObjectMapper() { 
	ObjectMapper om = new ObjectMapper(); 
	JavaTimeModule javaTimeModule = new JavaTimeModule(); 
	javaTimeModule.addSerializer(LocalDateTime.class, new LocalDateTimeSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))); 
	javaTimeModule.addSerializer(LocalDate.class, new LocalDateSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd"))); 
	javaTimeModule.addSerializer(LocalTime.class, new LocalTimeSerializer(DateTimeFormatter.ofPattern("HH:mm:ss"))); 
	om.registerModule(javaTimeModule); 
	return om; 
}

springboot以注解引入mybatis

注解：去XML化
★₯₰☆ 快速生成映射结果集方法

public static String getResultsStr(Class origin) {
    StringBuilder stringBuilder = new StringBuilder();
    stringBuilder.append("@Results({\n");
    for (Field field : origin.getDeclaredFields()) {
        String property = field.getName();
        //映射关系：对象属性(驼峰)->数据库字段(下划线)
        String column = new PropertyNamingStrategy.SnakeCaseStrategy().translate(field.getName()).toUpperCase();
        stringBuilder.append(String.format("@Result(property = \"%s\", column = \"%s\"),\n", property, column));
    }
    stringBuilder.append("})");
    return stringBuilder.toString();
}

基础参见：SpringBoot快速整合Mybatis：2种方式整合MyBatis与SpringBoot