springBoot连接MySql配置

##1、application.yml

hyb_main_db:
  datasource:
    url: jdbc:mysql://ip地址/数据库名?useUnicode=true&characterEncoding=UTF-8
    driverClassName: com.mysql.jdbc.Driver
    username: 用户名
    password: 密码
    initialSize: 1
    minIdle: 2
    maxActive: 20
    maxWait: 1800
    validationQuery: SELECT 'x' FROM DUAL
    testOnBorrow: false
    testOnReturn: false
    testWhileIdle: true
    timeBetweenEvictionRunsMillis: 600000
    minEvictableIdleTimeMillis: 300000
    removeAbandoned: true
    removeAbandonedTimeout: 1800
    logAbandoned: true
    filters: stat

##2、pom.xml

 		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.29</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
            <scope>compile</scope>
        </dependency>
         <!--mybatis支持-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>

##3、配置类

@Configuration
@MapperScan(value = MysqlConfig.MAPPER_PACKAGE)
public class MysqlConfig {

    static final String MAPPER_PACKAGE = "cn.haoyunbang.repository";
    static final String ALIASE_PACKAGE = "cn.haoyunbang.repository.entity.*";


    @Value("${hyb_main_db.datasource.url}")
    private String url;
    @Value("${hyb_main_db.datasource.username}")
    private String user;
    @Value("${hyb_main_db.datasource.password}")
    private String password;
    @Value("${hyb_main_db.datasource.driverClassName}")
    private String driverClass;
    @Value("${hyb_main_db.datasource.initialSize}")
    private int initialSize;
    @Value("${hyb_main_db.datasource.minIdle}")
    private int minIdle;
    @Value("${hyb_main_db.datasource.maxActive}")
    private int maxActive;
    @Value("${hyb_main_db.datasource.maxWait}")
    private int maxWait;
    @Value("${hyb_main_db.datasource.validationQuery}")
    private String validationQuery;
    @Value("${hyb_main_db.datasource.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${hyb_main_db.datasource.testOnReturn}")
    private boolean testOnReturn;
    @Value("${hyb_main_db.datasource.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${hyb_main_db.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${hyb_main_db.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${hyb_main_db.datasource.removeAbandoned}")
    private boolean removeAbandoned;
    @Value("${hyb_main_db.datasource.removeAbandonedTimeout}")
    private int removeAbandonedTimeout;
    @Value("${hyb_main_db.datasource.logAbandoned}")
    private boolean logAbandoned;
    @Value("${hyb_main_db.datasource.filters}")
    private String filters;

    @Bean(name = "hybMainDbDataSource")
    @Primary
    public DataSource hybMainDbDataSource() throws  Exception{
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setInitialSize(initialSize);
        dataSource.setMinIdle(minIdle);
        dataSource.setMaxActive(maxActive);
        dataSource.setMaxWait(maxWait);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setRemoveAbandoned(removeAbandoned);
        dataSource.setRemoveAbandonedTimeout(removeAbandonedTimeout);
        dataSource.setLogAbandoned(logAbandoned);
        dataSource.setFilters(filters);
        return dataSource;
    }

    @Bean(name = "hybMainDbTransactionManager")
    @Primary
    public DataSourceTransactionManager hybMainDbTransactionManager() throws  Exception{
        return new DataSourceTransactionManager(hybMainDbDataSource());
    }

    @Bean(name = "hybMainDbSqlSessionFactory")
    @Primary
    public SqlSessionFactory hybMainDbSqlSessionFactory(@Qualifier("hybMainDbDataSource") DataSource hybMainDbDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(hybMainDbDataSource);
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        sessionFactory.setConfiguration(configuration);
        sessionFactory.setTypeAliasesPackage(MysqlConfig.ALIASE_PACKAGE);
        return sessionFactory.getObject();
    }
}

##4、Repository

public interface DocRepository {

    //获取医生信息
    @Select("SELECT * FROM doctor_account where id=#{doctor_id} limit 0,1")
    Doctor getDocInfo(String doctor_id);

    //查询医生
    @SelectProvider(type = DoctorProvider.class, method = "search")
    List<Doctor> search(Map<String, Object> args);

    //查询医生
    @SelectProvider(type = DoctorProvider.class, method = "searchwithser")
    List<Doctor> searchWithSer(Map<String, Object> args);


}

##5、sql条件的拼接类

public class DoctorProvider {


    //搜索
    public String search(Map<String, Object> args) {

        String select = "SELECT * FROM doctor_account doc left join doctor_hospital hos  on doc.doct_hospital_id=hos.id LEFT JOIN doctor_province pro ON hos.provice_id=pro.province_id WHERE doct_identify=1 AND is_local!=1";

        if (args == null) {
            return select + " limit 0," + Constant.DEFAULT_LIMIT;
        }

        String where = "";

        //地区
        if (args.containsKey("location")) {
            where += " AND pro.province_name = #{location}";
        }

        // 医院
        if (args.containsKey("hospital")) {
            where += " AND hos.hospital_name = #{hospital}";
        }

        // 专长
        if (args.containsKey("info")) {
            where += " AND doc.doct_info LIKE CONCAT('%', #{info}, '%')";
        }

        //是否搜索开通咨询的
        if (args.containsKey("service") && args.get("service").equals("advice")) {
            where += " AND doc.is_recomm=2";
        }

        // 是否开通咨询
        if (args.containsKey("is_recomm")) {
            where += " AND doc.is_recomm=2";
        }
        if (args.containsKey("city")) {
            where += " AND hos.city=#{city}";
        }

        String limit = null;
        if (args.containsKey("page") && args.containsKey("limit")) {
            int page = ((Integer) args.get("page")).intValue();
            int size = ((Integer) args.get("limit")).intValue();
            limit = ((page - 1) * size) + ", " + size;

        } else if (args.containsKey("start") && args.containsKey("rows")) {
            limit = ((Integer) args.get("start")).intValue() + ", " + ((Integer) args.get("rows")).intValue();

        }
        limit = (limit == null) ? "" : (" LIMIT " + limit);

        return select + where + " ORDER BY doc.weight DESC,doc.id" + limit;
    }


  
    //根据医生输入的用户名搜索患者
    public String searchPatient(Map<String, Object> args) {
        String select = "SELECT * FROM aa_qa_follow WHERE  is_bind IN (1,2) AND start_visit!=1 AND hospital_id=''";
        String where = "";
        if (args.containsKey("doctor_id")) {
            where += " AND doct_id = #{doctor_id}";
        }
        if (args.containsKey("input")) {
            where += " AND ( mark_name LIKE CONCAT('%', #{input}, '%')";
            where += " OR user_name LIKE CONCAT('%', #{input}, '%')";
            where += " OR follow_result LIKE CONCAT('%', #{input}, '%') )";
        }
        return select + where + " LIMIT 0, 100";
    }

   


    public String doctorCount(Map<String, Object> args) {

        String select = "SELECT * FROM doctor_account doc left join doctor_hospital hos  on doc.doct_hospital_id=hos.id LEFT JOIN doctor_province pro ON hos.provice_id=pro.province_id WHERE doct_identify=1 AND is_local!=1";

        //地区
        if (args.containsKey("location")) {
            select += " AND pro.province_name = #{location}";
        }

        // 医院
        if (args.containsKey("hospital")) {
            select += " AND hos.hospital_name = #{hospital}";
        }

        // 专长
        if (args.containsKey("info")) {
            select += " AND doc.doct_info LIKE CONCAT('%', #{info}, '%')";
        }
        if (args.containsKey("city")) {
            select += " AND hos.city = #{city}";
        }
        //排序条件
        String order;
        if (args.containsKey("sorter")) {
            order = (String) args.get("sorter");
        } else {
            order = "doc.id DESC";
        }
        order = " ORDER BY " + order;


        //分页信息
        String limit = null;
        if (args.containsKey("start") && args.containsKey("rows")) {
            limit = ((Integer) args.get("start")).intValue() + ", " + ((Integer) args.get("rows")).intValue();
        }
        limit = (limit == null) ? "" : (" LIMIT " + limit);

        return select + order + limit;

    }
}

##6、引入使用—Service

@Autowired
    private DocRepository docRepository;