@Configuration
public class DBConfig {

    @Bean(name = "mysqlDataSource")
    @Qualifier("mysqlDataSource")
    @Primary //重要
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean(name = "sqlServerDataSource")
    @Qualifier("sqlServerDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
    public DataSource sqlServerDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

}


/**
 * basePackages: 扫描repository 所在的包
 * entiryMangerFactory package : 实例bean的包
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "mysqlEntityManagerFactory",
        transactionManagerRef = "mysqlTransactionManager",
        basePackages = {"com.example.consumer.repository"})
public class MysqlDbConfig {

    @Autowired
    @Qualifier("mysqlDataSource")
    private DataSource mysqlDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Bean(name = "mysqlEntityManager")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return mysqlEntityManagerFactory(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "mysqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(mysqlDataSource)
                .properties(getVendorProperties(mysqlDataSource))
                .packages("com.example.consumer.bean")
                .persistenceUnit("mysqlPersistenceUnit")
                .build();
    }

    private Map<String, Object> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

    @Primary
    @Bean(name = "mysqlTransactionManager")
    PlatformTransactionManager mysqlTransactionManager(EntityManagerFactoryBuilder builder) {
        JpaTransactionManager pm = new JpaTransactionManager(mysqlEntityManagerFactory(builder).getObject());
        pm.setGlobalRollbackOnParticipationFailure(false);
        return pm;
    }

}


/**
 * @author shenzm
 */
@Configuration
@MapperScan(basePackages = "com.example.consumer.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class SqlServerDbConfig {

    @Autowired
    @Qualifier("sqlServerDataSource")
    private DataSource sqlServerDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(sqlServerDataSource);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sessionTemplate() throws Exception {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory());
        return sqlSessionTemplate;
    }


}

//jap 持久化的bean

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "userNew")
public class UserNew implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    private Integer age;
}

//mybatis 查询业务的bean

/**
 * @author shenzm
 * @date 2019-4-2
 * @description 作用
 */
public class SecuMain {
    private Long ID;
    private int InnerCode;
    private int CompanyCode;
    private String SecuCode;
    private String ChiName;
    private String ChiNameAbbr;
    private String SecuAbbr;
    private String ChiSpelling;
    private int SecuMarket;
    private int SecuCategory;
    private Date ListedDate;
    private int ListedSector;
    private int ListedState;

    //getter setter ....
}


//jpa repository

@Repository
public interface UserNewRepository extends JpaRepository<UserNew, Long> {

    /**
     * 根据名称获取用户
     *
     * @param name
     * @return
     */
    public UserNew findUserNewByName(String name);

}


/**
 * @author shenzm
 * @date 2019-3-28
 * @description 作用
 */

@Service
public class UserNewServiceImpl implements UserNewService {

    @Autowired
    private UserNewRepository userNewRepository;

    //           tx          抛异常    保存情况
    //  save     REQUIRED      no          no
    //  save2    REQUIRES_NEW  yes         no

    //  save     REQUIRED      yes         no
    //  save2    REQUIRES_NEW  no          no


    @Transactional(
            value = "mysqlTransactionManager",
            rollbackFor = {Exception.class, RuntimeException.class},
            propagation = Propagation.REQUIRED,
            isolation = Isolation.DEFAULT
    )
    @Override
    public void saveUser(UserNew userNew) {
        userNewRepository.save(userNew);

        try {
            Thread.sleep(200);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        UserNew user = new UserNew();
        user.setName("22222");
        user.setAge(222);
        saveUser2(user);
        throw new RuntimeException("异常发生了");

    }

    @Transactional(
            value = "mysqlTransactionManager",
            rollbackFor = {Exception.class, RuntimeException.class},
            propagation = Propagation.REQUIRES_NEW,
            isolation = Isolation.DEFAULT
    )
    @Override
    public void saveUser2(UserNew user) {
        userNewRepository.save(user);
    }
}


//mybatis mapper 接口


/**
 * @author shenzm
 * @date 2019-4-2
 * @description 作用
 */

@Mapper
@Repository
public interface SecuMainMapper {


    @Select("SELECT ID,InnerCode,CompanyCode,SecuCode,ChiName,ChiNameAbbr,SecuAbbr," +
            "ChiSpelling,SecuMarket,SecuCategory,ListedDate,ListedState " +
            "from SecuMain where SecuMarket in (83,90) and SecuCategory in (1,2,6)")
    public List<SecuMain> loadSecuMainTable();

    @Select("SELECT ID,InnerCode,CompanyCode,SecuCode,ChiName,ChiNameAbbr,SecuAbbr," +
            "ChiSpelling,SecuMarket,SecuCategory,ListedDate,ListedState " +
            "from SecuMain where SecuMarket in (#{sm}) and SecuCategory in (#{sc})")
    public List<SecuMain> loadSecuMainTableParam(@Param("sm") String sm, @Param("sc") String sc);


    /**
     * @param params 可能包含的参数secuMarket-->List<Integer> , secuCategory-->List<Integer>
     * @return
     */
    @SelectProvider(type = SecuMainQueryProvider.class, method = "queryByParams")
    public List<SecuMain> loadSecuMainTableByParams(Map<String, Object> params);

    class SecuMainQueryProvider {
        public String queryByParams(Map<String, Object> params) {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT ID,InnerCode,CompanyCode,SecuCode,ChiName,ChiNameAbbr,SecuAbbr,")
                    .append("ChiSpelling,SecuMarket,SecuCategory,ListedDate,ListedState ")
                    .append("from SecuMain where 1=1 ");

            if (params.containsKey("secuMarket")) {
                List<Integer> list = (List<Integer>) params.get("secuMarket");
                String str = list.stream().map(i -> String.valueOf(i)).collect(Collectors.joining(","));
                sql.append(" and SecuMarket in (" + str + ") ");
            }
            if (params.containsKey("secuCategory")) {
                List<Integer> list = (List<Integer>) params.get("secuCategory");
                String str = list.stream().map(i -> String.valueOf(i)).collect(Collectors.joining(","));
                sql.append(" and SecuCategory in (" + str + ") ");
            }
            return sql.toString();
        }
    }

}

@RestController
public class TestController {

    @Autowired
    UserNewService userNewService;

    @Autowired
    SecuMainMapper secuMainMapper;


    @RequestMapping("/querydb")
    public List<SecuMain> querydb() {
        List<SecuMain> secuMains = secuMainMapper.loadSecuMainTable();
        return secuMains;
    }

    @RequestMapping("/querydb2")
    public List<SecuMain> querydb2() {
        Map<String, Object> params = new HashMap<>();
        params.put("secuMarket", Arrays.asList(83, 90));
        params.put("secuCategory", Arrays.asList(1, 2, 6));
        List<SecuMain> secuMains = secuMainMapper.loadSecuMainTableByParams(params);
        return secuMains;
    }

    @RequestMapping("/querydb3")
    public List<SecuMain> querydb3(@RequestParam("sm") String sm, @RequestParam("sc") String sc) {
        List<SecuMain> secuMains = secuMainMapper.loadSecuMainTableParam(sm, sc);
        return secuMains;
    }

    @RequestMapping("/save/user")
    public String save() {
        UserNew userNew = new UserNew();
        userNew.setAge(12);
        userNew.setName("java");
        userNewService.saveUser(userNew);
        return "ok";
    }
}