jdbc结合druid连接池访问postgreSQL数据库

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);
        }

    }