springboot整合postgresql_SpringBoot:Mybatis整合PostgreSQL

1. 引入jar包(pgsql)

org.postgresql

postgresql

42.2.5.jre7

2. 写好配置

pgsql:

datasource:

url: jdbc:postgresql://IP:端口/库名

username: 用户名

password: 密码

driver-class-name: org.postgresql.Driver

maxActive: 50

initialSize: 10

maxWait: 60000

minIdle: 6

timeBetweenEvictionRunsMillis: 60000

minEvictableIdleTimeMillis: 300000

validationQuery: select 'x'

testWhileIdle: true

testOnBorrow: false

testOnReturn: false

poolPreparedStatements: true

maxOpenPreparedStatements: 20

3. 写配置类(以前的结合方式,还没有springboot和pgsql直接整合不用写配置类的包)

在resources下创建pgmapper文件夹,放置sql文件

@Configuration

@MapperScan(basePackages = PgsqlDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "pgSqlSessionFactory")

public class PgsqlDataSourceConfig {

static final String PACKAGE = "放mapper的位置";

static final String MAPPER_LOCATION = "classpath:pgmapper/**/*.xml";

@Value("${pgsql.datasource.url}")

private String url;

@Value("${pgsql.datasource.username}")

private String user;

@Value("${pgsql.datasource.password}")

private String password;

@Value("${pgsql.datasource.driver-class-name}")

private String driverClass;

/druid参数///

@Value("${pgsql.datasource.maxActive}")

private String maxActive;

@Value("${pgsql.datasource.initialSize}")

private String initialSize;

@Value("${pgsql.datasource.maxWait}")

private String maxWait;

@Value("${pgsql.datasource.minIdle}")

private String minIdle;

@Value("${pgsql.datasource.timeBetweenEvictionRunsMillis}")

private String timeBetweenEvictionRunsMillis;

@Value("${pgsql.datasource.minEvictableIdleTimeMillis}")

private String minEvictableIdleTimeMillis;

@Value("${pgsql.datasource.validationQuery}")

private String validationQuery;

@Value("${pgsql.datasource.testWhileIdle}")

private String testWhileIdle;

@Value("${pgsql.datasource.testOnBorrow}")

private String testOnBorrow;

@Value("${pgsql.datasource.testOnReturn}")

private String testOnReturn;

@Value("${pgsql.datasource.poolPreparedStatements}")

private String poolPreparedStatements;

@Value("${pgsql.datasource.maxOpenPreparedStatements}")

private String maxOpenPreparedStatements;

@Bean(name = "pgDataSource")

public DataSource pgDataSource() {

DruidDataSource druidDataSource = new DruidDataSource();

druidDataSource.setDriverClassName(driverClass);

druidDataSource.setUrl(url);

druidDataSource.setUsername(user);

druidDataSource.setPassword(password);

druidDataSource.setMaxActive(Integer.parseInt(maxActive));

druidDataSource.setInitialSize(Integer.parseInt(initialSize));

druidDataSource.setMaxWait(Integer.parseInt(maxWait));

druidDataSource.setMinIdle(Integer.parseInt(minIdle));

druidDataSource.setTimeBetweenEvictionRunsMillis(Integer.parseInt(timeBetweenEvictionRunsMillis));

druidDataSource.setMinEvictableIdleTimeMillis(Integer.parseInt(minEvictableIdleTimeMillis));

druidDataSource.setValidationQuery(validationQuery);

druidDataSource.setTestWhileIdle(Boolean.parseBoolean(testWhileIdle));

druidDataSource.setTestOnBorrow(Boolean.parseBoolean(testOnBorrow));

druidDataSource.setTestOnReturn(Boolean.parseBoolean(testOnReturn));

druidDataSource.setPoolPreparedStatements(Boolean.parseBoolean(poolPreparedStatements));

druidDataSource.setMaxOpenPreparedStatements(Integer.parseInt(maxOpenPreparedStatements));

return druidDataSource;

}

@Bean(name = "pgTransactionManager")

public DataSourceTransactionManager pgTransactionManager() {

return new DataSourceTransactionManager(pgDataSource());

}

@Bean(name = "pgSqlSessionFactory")

public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("pgDataSource") DataSource pgDataSource) throws Exception {

final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

sessionFactory.setDataSource(pgDataSource);

sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()

.getResources(PgsqlDataSourceConfig.MAPPER_LOCATION));

return sessionFactory.getObject();

}

}

4. 其他和mysql一样了,写mapper,写sql

5. sql示例(判断点是否在多边形区域内)

SELECT

ST_Covers (

polygon,

st_MakePoint (#{lon}, #{lat}))

FROM

bc_contact

WHERE

contact_id = #{contactId}