jdbc结合druid连接池访问postgreSQL数据库
连接mysql的话也是一个道理，就是把对应的依赖和数据库驱动换一下
一. 在pom.xml里面加上对应的依赖
<!-- druid数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.9</version>
</dependency>
<!-- Spring Boot JDBC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- Spring Boot JDBC -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
二. 新建个配置文件postgre.properties
注：该文件放在resources目录下，否则下面的工具类里面引用的话得加上相对路径
driverClassName=org.postgresql.Driver
url: jdbc:postgresql://192.163.25.66:5432/yzstreetdp
username: postgres
password: 123456
initialSize=50
maxActive=300
maxWait=3000
三. 工具类JDBCPostGreUtils
package com.loit.common.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @ClassName JDBCPostGreUtils
* @Description jdbc工具类
* @Author lifh
* @Date 2024/4/2 14:58
* @Version 1.0
*/
public class JDBCPostGreUtils {
// 连接池
private static DataSource ds = null;
// 获取双列集合
private static Properties properties = new Properties();
static {
Properties pro = new Properties();
InputStream is = JDBCPostGreUtils.class.getClassLoader().getResourceAsStream("postgre.properties");
try {
pro.load(is);
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
// 定义返回值
Connection conn = null;
try {
conn = ds.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// 返回连接对象
return conn;
}
// 关闭连接对象
public static void close(Connection conn){
// 判断连接对象是否为空
if (conn == null){
return;
}
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
// 关闭连接对象和执行者
public static void close(Connection conn, Statement stmt){
if (stmt != null) {
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
close(conn);
}
// 关闭连接对象和执行者
public static void close(Connection conn, Statement stmt, ResultSet rs){
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
close(conn,stmt);
}
}
四. 测试类中进行测试
/**
* 获取车辆关联道路
* 地图接口
* @param lat
* @param lon
* @param layerList 作业图层 6/7/10
* @return
*/
@Test
public JSONObject getRoadName(Double lat, Double lon, List<Integer> layerList) throws SQLException {
String temp = "";
if (layerList.get(0) == 6){ //清扫
temp = "AND is_clean_road = '1'";
}else if(layerList.get(0) == 7){ //洒水
temp = "AND is_watering_road = '1'";
}else if(layerList.get(0) == 10){ //洗地
temp = "AND is_washing_road = '1'";
}
String sql = String.format("SELECT\n" +
"\tobjectid,\n" +
"\troad_name,\n" +
"\tst_distance ( ( ST_Transform ( st_geometryfromtext ( 'POINT(%s %s)', 4326 ), 3857 ) ), ST_Transform ( geometry, 3857 ) ) \n" +
"FROM\n" +
"\t\"polyline_grid\" \n" +
"WHERE\n" +
"\tobjectid IN (\n" +
"\tSELECT\n" +
"\t\tobjectid \n" +
"\tFROM\n" +
"\t\t(\n" +
"\t\tSELECT\n" +
"\t\t\tST_Contains ( ST_Buffer ( ST_Transform ( geometry, 3857 ), ? ), ST_Transform ( st_geometryfromtext ( 'POINT(%s %s)', 4326 ), 3857 ) ),* \n" +
"\t\tFROM\n" +
"\t\t\tpolyline_grid \n" +
"\t\t) polyline_result \n" +
"\tWHERE\n" +
"\t\tpolyline_result.ST_Contains = 't'\n" +
"\t\t %s \n" +
"\t) \n" +
"ORDER BY\n" +
"\tst_distance \n" +
"\tLIMIT 1;",lon,lat,lon,lat,temp);
logger.info("获取车辆关联道路sql: "+sql);
Connection conn = JDBCPostGreUtils.getConnection();
PreparedStatement preparedStatement;
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,BUFFER_LENGHT);
ResultSet rs = preparedStatement.executeQuery();
String layerId = null;
String roadName = null;
String distance = null;
while(rs.next()){
layerId = rs.getString(1);
roadName = rs.getString(2);
distance = rs.getString(3);
System.out.println("layerId--" + layerId);
System.out.println("roadName-" + roadName);
}
logger.info("获取车辆关联道路返回layerId: "+layerId);
logger.info(String.format("获取车辆关联道路返回layerId:%s,roadName:%s,距离：%s",layerId,roadName,distance));
// 关闭资源
JDBCPostGreUtils.close(conn, preparedStatement, rs);
String strJson = String.format("{\n" +
"\t\"code\": 200,\n" +
"\t\"data\": {\n" +
"\t\t\"polylineId\": %s\n" +
"\t}\n" +
"}",layerId);
return JSONUtil.parseObj(strJson);
}
/**
* 根据图层和layerid更新某条道路状态
* 地图接口
* @param polylineId 图层6/7/10
* @param geojsonId 道路的layerId
* @param state 状态1/2/3
* @return
*/
@Test
public void layerUpdate(int polylineId, int geojsonId, int state){
String temp = "";
if (polylineId == 6){ //清扫
temp = "is_clean_status";
}else if(polylineId == 7){ //洒水
temp = "is_watering_status";
}else if(polylineId == 10){ //洗地
temp = "is_washing_status";
}
String sql = String.format("UPDATE polyline_grid set %s=? where objectid= ?",temp);
logger.info("更新图层sql: "+sql);
Connection conn = null;
PreparedStatement preparedStatement = null;
try {
conn = JDBCPostGreUtils.getConnection();
preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,state);
preparedStatement.setString(2,String.valueOf(geojsonId));
int i = preparedStatement.executeUpdate();
logger.info("更新总数: "+i);
} catch (SQLException e) {
logger.info("更新图层失败"+e);
throw new RuntimeException(e);
} finally {
// 关闭资源
JDBCPostGreUtils.close(conn,preparedStatement, null);
}
}