jdbc手动连接数据库，执行查询语句返回对象

最新推荐文章于 2023-08-26 10:18:39 发布

庞胖

最新推荐文章于 2023-08-26 10:18:39 发布

阅读量1k

点赞数

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/qq_43393995/article/details/124350462

版权

java 专栏收录该内容

36 篇文章 0 订阅

订阅专栏

此篇博客展示了如何使用PreparedStatement在PostgreSQL中执行SQL查询，通过JSONObject处理结果，并提供两种方法：JSONArray和泛型List<T>返回。它涵盖了数据库连接池、参数化查询和数据对象映射的过程。

摘要由CSDN通过智能技术生成

大概代码贴一下，自己连接，仅执行语句返回内容
 
/**
     * 查询sql
     *
     * @param: sql
     * @return: JSONArray
     */
    public static JSONArray querySql(PostgreSQLConfig staticPostgreSQLConfig, String sql, Object... objects) {
        JSONArray resJson = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet reSet = null;
        PGConnectionPool connPool = null;
        try {
//            conn = getConnection(staticPostgreSQLConfig);
            connPool = PGConnectionPoolUtil.getPoolInstance(staticPostgreSQLConfig);
            conn = connPool.getConnection();
            pstmt = conn.prepareStatement(sql);
            if (objects != null && objects.length > 0) {
                for (int i = 0; i < objects.length; i++) {
                    pstmt.setObject(i + 1, objects[i]);
                }
            }
            logger.info("执行SQL：{}", sql);
            reSet = pstmt.executeQuery();
            // 获取列数
            ResultSetMetaData metaData = reSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 遍历ResultSet中的每条数据
            while (reSet.next()) {
                JSONObject jsonObj = new JSONObject();
                // 获取每一列数据
                for (int i = 1; i <= columnCount; i++) {
                    String value = "";
                    String columnName = metaData.getColumnLabel(i);//列名称
                    if (reSet.getString(columnName) != null && !reSet.getString(columnName).equals("")) {
                        value = new String(reSet.getBytes(columnName), "UTF-8");//列的值,有数据则转码
                    }
                    jsonObj.put(columnName, value);
                }
                resJson.add(jsonObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//            closeConnection(conn, pstmt, reSet);
            closePoolConnection(connPool, conn, pstmt, reSet);
        }
        return resJson;
    }
  
    public static <T> List<T> querySql(PostgreSQLConfig staticPostgreSQLConfig, Class<T> clazz, String sql, Object... objects) {
        List<T> resList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet reSet = null;
        PGConnectionPool connPool = null;
        try {
//            conn = getConnection(staticPostgreSQLConfig);
            connPool = PGConnectionPoolUtil.getPoolInstance(staticPostgreSQLConfig);
            conn = connPool.getConnection();
            pstmt = conn.prepareStatement(sql);
            if (objects != null && objects.length > 0) {
                for (int i = 0; i < objects.length; i++) {
                    pstmt.setObject(i + 1, objects[i]);
                }
            }
            logger.info("执行SQL：{}", sql);
            reSet = pstmt.executeQuery();
            List<Map<String, Object>> tempList = new ArrayList<>();
            Map<String, Object> tempMap = null;
            // 获取列数
            ResultSetMetaData metaData = reSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 遍历ResultSet中的每条数据
            while (reSet.next()) {
                tempMap = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);//列名称
                    tempMap.put(columnName, reSet.getObject(i));
                }
                tempList.add(tempMap);
            }
            //加载数据到对象
            if (tempList.size() > 0) {
                for (Map<String, Object> map : tempList) {
                    //解析对象
                    T bean = clazz.newInstance();
                    for (Map.Entry<String, Object> entry : map.entrySet()) {
                        String propertyName = entry.getKey();
                        Object value = entry.getValue();
                        Field field = clazz.getDeclaredField(propertyName);
                        String str = field.getName();
                        String s = str.substring(0, 1).toUpperCase() + str.substring(1);
                        Method method = clazz.getMethod("set" + s, field.getType());
                        method.invoke(bean, value);
                    }
                    resList.add(bean);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//            closeConnection(conn, pstmt, reSet);
            closePoolConnection(connPool, conn, pstmt, reSet);
        }
        return resList;
    }
/**
     * 关闭连接
     */
    public static void closePoolConnection(PGConnectionPool connPool, Connection conn, PreparedStatement pstmt, ResultSet reSet) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (reSet != null) {
                try {
                    reSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (conn != null) {
                            connPool.returnConnection(conn);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }