spring mybatis

[uniquejava]

Spring 5.x Reference: https://docs.spring.io/spring/docs/current/spring-framework-reference/

Spring CORS

Spring 4.2.0GA开始内置对CORS的支持, 见: https://spring.io/blog/2015/06/08/cors-support-in-spring-framework

最简单的办法是在MvcConfig加上如下方法

@Override
public void addCorsMappings(CorsRegistry registry) {
    registry.addMapping("/**");
}

Transactional加在Interface上好,还是加在Implementation上好?

Implementation 见: https://stackoverflow.com/questions/5551541/where-to-put-transactional-in-interface-specification-or-implementation

AOP打印SQL执行时间

在pom.xml中添加依赖

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aop</artifactId>
    <version>${spring.version}</version>
</dependency>

<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.8.10</version>
</dependency>

增加一个Config类, 并将其引入AppConfig(或你的MvcConfig)

@Configuration
@EnableAspectJAutoProxy
@ComponentScan(basePackages = "com.xxx.config.aspects")
public class AspectConfig {
    //Here you can define Aspect beans or just use @ComponentScan (as above)
    //to scan the @Aspect annotation in specified package
}

配置你的Aspect类.

@Component
@Aspect
public class LoggingAspect {
    private static final Logger logger = LoggerFactory.getLogger(LoggingAspect.class);

    @Around("execution(* com.xxx.dao.*.*(..)) || execution(* com.xxx.mapper.*.*(..))")
    public Object logTime(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.nanoTime();

        Object result = joinPoint.proceed();

       if(logger.isDebugEnabled()) {
           long endTime = System.nanoTime();
           long duration = (endTime - startTime);

           logger.debug("Total: " + (duration / 1000000) + "ms");
       }

        return result;
    }

}

主要参考: https://stackoverflow.com/questions/14068525/javaconfig-replacing-aopadvisor-and-txadvice https://stackoverflow.com/questions/7819300/aspectj-aspect-for-specifying-multiple-packages

[uniquejava]

MyBatis

#{}是使用预编译SQL, 并且可以指定参数类型, ${}是直接字符串替换

see http://www.mybatis.org/mybatis-3/sqlmap-xml.html#select

整合步骤

pom.xml加两个依赖

<!-- mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.5</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.1</version>
</dependency>

在AppConfig中配置@MapperScan和sessionFactory

@Configuration
@EnableTransactionManagement
@MapperScan("com.xxx.mapper")
public class AppConfig {
    private static final Logger logger = LoggerFactory.getLogger(AppConfig.class);

    @Value("${datasource.refreshable}")
    private boolean refreshable;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sfb = new SqlSessionFactoryBean();
        sfb.setDataSource(dataSource());
        sfb.setTypeAliasesPackage("com.xxx.model.vo");

        SqlSessionFactory factory = sfb.getObject();
        factory.getConfiguration().setMapUnderscoreToCamelCase(true);
        return factory;
    }

UserDao接口不用做任何修改 (和不使用mybatis一样定义) 定义一个同名的 UserDao.xml, 其中的sql id对应Dao接口中的方法名即可.

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.mapper.XxxMapper">

    <select id="count" resultType="int">
        SELECT COUNT(1) FROM TABLE
        <include refid="where"></include>
    </select>

in的4种方式

如果参数是一个单纯的collection

where ID in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
   #{item}
</foreach>

如果参数是一个单纯的array

where ID in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
   #{item}
</foreach>

如果有多个参数, 需要使用map做为参数, 在collection中指定key的名字

where ID in
<foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
   #{item}
</foreach>

以上都会使用预编译SQL, 即ID in (?,?,?..)

最后一种是暴力字符串替换的方式, 这个时候ids的格式如下: ('aaaa','bbbb')

where ID in ${ids}
</foreach>

like

https://stackoverflow.com/questions/7491291/how-can-i-use-like-in-sql-queries-with-mybatis-safely-and-db-agnostic

[uniquejava]

多PropertySource和SimpleDriverDataSource

参考了: https://www.mkyong.com/spring/spring-propertysources-example/ 其中buildConnectionProperties拷贝自dbcp2的源代码.

@PropertySource({
    "classpath:/application.properties",
    "classpath:/db.properties" //if same key, this will 'win'
})
public class AppConfig {
    private static final Logger logger = LoggerFactory.getLogger(AppConfig.class);

    @Autowired
    Environment env;

    @Bean
    public DataSource dataSource() {
        SimpleDriverDataSource ds = new SimpleDriverDataSource();
        try {
            ds.setDriver((Driver) Class.forName(env.getProperty("driverClassName")).newInstance());
            ds.setUrl(env.getProperty("url"));
            ds.setUsername(env.getProperty("username"));
            ds.setPassword(env.getProperty("password"));
            ds.setConnectionProperties(buildConnectionProperties(env.getProperty("connectionProperties")));
            return ds;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }

        return null;

    }

    private Properties buildConnectionProperties(String connectionProperties) {
        if (connectionProperties == null) {
            throw new NullPointerException("connectionProperties is null");
        }

        String[] entries = connectionProperties.split(";");
        Properties properties = new Properties();
        for (String entry : entries) {
            if (entry.length() > 0) {
                int index = entry.indexOf('=');
                if (index > 0) {
                    String name = entry.substring(0, index);
                    String value = entry.substring(index + 1);
                    properties.setProperty(name, value);
                } else {
                    // no value is empty string which is how java.util.Properties works
                    properties.setProperty(entry, "");
                }
            }
        }
        return properties;
    }