当前位置: 首页 > 知识库问答 >
问题:

如果使用一条语句选择属性,然后使用一条带有多个sql查询的准备好的语句,是否需要使用连接池

束飞捷
2023-03-14

我想用格式化字符串记录传递的sql,以检查是简单地传递问号还是实际的列名,但结果发现jdbc不支持这种线程安全日志记录。我把我准备好的陈述构造成在不同的条件下是不同的。

因此,我使用一个语句来选择我需要的所有内容,然后使用一个结果集进行检索,同时根据这些结果集创建一个准备好的对象。我的准备好的语句在两次sql更新之间可能会发生变化,而之前我使用了两个不同的准备好的语句,但我收到了死锁。因此,我需要连接池吗?我宁愿不要,因为这个独立的应用程序不应该占用太多的时间,因为我已经投入了太多的时间,试图调试和故障排除服务器问题。我的代码如下:

    public static void handleEntries(Long level,
        PreparedStatement w_ustmt, String base_url, String name5,
        String name4, String name3, String name2, String name1, String name0) throws SQLException {
    String wiki_url_entry = "";
    try {
        if (level.equals((long) 5)) {
            // wiki_url = wiki_url + name5;
            w_ustmt.setString(1, base_url + name5);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 4)) {
            // wiki_url = wiki_url + name4;
            w_ustmt.setString(1, base_url + name4);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 3)) {
            //for some reason all the results are coming from this loop
            // wiki_url = wiki_url + name3;
            w_ustmt.setString(1, base_url + name3);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 2)) {
            // wiki_url = wiki_url + name2;
            w_ustmt.setString(1, base_url + name2);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else if (level.equals((long) 1)) {
            // wiki_url = wiki_url + name1;
            w_ustmt.setString(1, base_url + name1);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        } else {
            // wiki_url = wiki_url + name0;
            w_ustmt.setString(1, base_url + name0);
            w_ustmt.addBatch();
            System.out.println(w_ustmt.toString());
        }

    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage());
        while (ex != null){
            System.out.println(ex.getNextException());
        }
        //System.out.println("SQLState: A " + ex.getSQLState());
        //System.out.println("VendorError A: " + ex.getErrorCode());
    }
}

/* this function will handle all the urls which have same entries */ 
public static void handleDups(String wiki_url,
        String base_url, String name5, String name4,
        String name3, String name2, String name1, String name0,
        PreparedStatement w_ustmt) {
    String wiki_url_entry = "";
    try {
        if (wiki_url.toString().equals(base_url + name5)) {
            wiki_url_entry = wiki_url + "(" + name0 + "." + name1  + "." + name2 + "." + name3 + "." +
            name4 + ")";
            // rewriting here, yet it is only printing ?, ?
            w_ustmt.setString(1, wiki_url_entry);
        } else if (wiki_url.toString().equals(base_url + name4)) {
            wiki_url_entry = wiki_url + "(" + name0 + "." + name1 + "." + name2 + "." + name3 +
            ")";
            w_ustmt.setString(1, wiki_url_entry);
        } else if (wiki_url.toString().equals(base_url + name3)) {
            wiki_url_entry = wiki_url + "(" + name0 + "." + name1  + "." + name2
            + ")";
            w_ustmt.setString(1, wiki_url_entry);
        } else if (wiki_url.toString().equals(base_url + name2)) {
            wiki_url_entry = wiki_url + "(" + name0 + "." + name1 + ")";
            w_ustmt.setString(1, wiki_url_entry);
        } else if (wiki_url.toString().equals(base_url + name1)) {
            wiki_url_entry = wiki_url + "(" + name0 + ")";
            w_ustmt.setString(1, wiki_url_entry);
        }
        w_ustmt.addBatch();
        System.out.println("B");
    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage());
        System.out.println("SQLState: B " + ex.getSQLState());
        System.out.println("VendorError B :" + ex.getErrorCode());
    }
}

/* main method */
@SuppressWarnings("resource")
public static void main(String[] args) throws InstantiationException,
        IllegalAccessException, SQLException {
    // TODO Auto-generated method stub
    Connection conn = null;
    // use the log4jdbc4 wrapper for the connection object
    // conn = new net.sf.log4jdbc.ConnectionSpy(conn);
    Statement stmt = null;
    ResultSet rs = null;
    try {
        // conn = makeConnection(args[0], args[1], args[2], args[3], "");
        String host = args[0];
        String port = args[1];
        String database = args[2];
        String user = args[3];
        String password = args[4];
        String sql_query = "SELECT \"NAME_0\", \"NAME_1\", \"NAME_2\", \"NAME_3\", \"NAME_4\", \"NAME_5\", \"WIKI_URL\", \"LEVEL_DEPT\" FROM"
                + " AdminBoundaries WHERE \"WIKI_URL\" IN(SELECT \"WIKI_URL\" FROM AdminBoundaries"
                + " GROUP By \"WIKI_URL\" HAVING (count (\"WIKI_URL\") > 1)) ORDER BY \"WIKI_URL\";";
        Class.forName("org.postgresql.Driver").newInstance();

        String url = "jdbc:postgresql://" + host + ":" + port + "/"
                + database;
        conn = DriverManager.getConnection(url, user, password);
        // if(conn.equals(null)){
        // System.err.println("Connection complete");
        // }
        DatabaseMetaData meta;          
        try {
            if (conn.isClosed()) {
                System.out.println("closed");
            }
            System.out.println(conn.getWarnings());
            meta = conn.getMetaData();
            boolean updateable = meta.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            /*if(updateable) System.out.println("yes");
            System.out.println("Does database support batch processes?:"
                    + meta.supportsBatchUpdates());
            ResultSet columns = meta.getColumns(null, null,
                    "adminboundaries", "%");
            while (columns.next()) {
                System.out.println(columns.getString(4));
            }*/
            // after checking the table, adminboundaries was in fact
            // evident, so the issue now is changing the sql queries to
            // lower case
            /*
             * ResultSet tables = meta.getTables(null, null, "%", null);
             * while (tables.next()){
             * System.out.println(tables.getString(3)); }
             */
        } finally {
            System.out.println(conn.getTransactionIsolation());
            System.out.println(sql_query);
            stmt = conn.createStatement(ResultSet.CLOSE_CURSORS_AT_COMMIT,
                    ResultSet.CONCUR_UPDATABLE,
                    ResultSet.TYPE_SCROLL_INSENSITIVE);
            System.out.println(stmt.getFetchSize());
            stmt.setMaxRows(900);
            System.out.println(conn.getTypeMap());
            // stmt.addBatch(sql_query);
            rs = stmt.executeQuery(sql_query);
            // batch_counter is for the row count of the SQL statements
            // executed
            // int batch_counter = pstmt.executeUpdate();
            // update dup_sql's arguments being set as it will vary
            // String wiki_sql =
            // "UPDATE AdminBoundaries SET \"WIKI_URL\" = ?";
            PreparedStatement w_ustmt = conn
                    .prepareStatement("UPDATE AdminBoundaries SET WIKI_URL = ?");
            // int counter = 0;
            // for (counter = 0; counter < 1000; counter++) {
            conn.setAutoCommit(false);
            // stmt.addBatch(sql_query);
            // stmt.setQueryTimeout(30);
            System.out.println(rs.getFetchSize());
            SQLWarning resultsetWarning = rs.getWarnings();
            System.out.println(resultsetWarning);
            // consider changing tables/columns to lower case as sources say
            // to do
            while (rs.next()) {
                String base_url = "http://127.0.0.1/mediawiki/index.php/";
                String name0 = rs.getString("NAME_0");
                String name1 = rs.getString("NAME_1");
                String name2 = rs.getString("NAME_2");
                String name3 = rs.getString("NAME_3");
                String name4 = rs.getString("NAME_4");
                String name5 = rs.getString("NAME_5");
                String wiki_url = rs.getString("WIKI_URL");
                Long level = rs.getLong("LEVEL_DEPT");
                /*for (int i = 1; i < rs.getMetaData().getColumnCount() + 1; i++) {
                    while (rs.next()) {
                        System.out.println(i + " " + rs.getMetaData().getColumnName(i));
                    }
                }*/
                // use 127.0.0.1, not ncsirad-pc b/c wiki_urls are coming
                // back 127.0.0.1

                // TO DO: rethink this if statement to include in function
                // and execute
                // both functions without conditionals above. So it will run
                // through first
                // then run through second.

                if (wiki_url.toString().equals(base_url)) {
                    // Savepoint savepoint1 =
                    // conn.setSavepoint("wiki_entry");
                    // w_ustmt.setQueryTimeout(30);
                    // System.out.println("Getting into duplicates loop");
                    handleEntries(level, w_ustmt, base_url, name5, name4,
                            name3, name2, name1, name0);
                    // add to batch
                    // w_ustmt.addBatch(wiki_sql);
                    // int counts2[] = w_ustmt.executeBatch();
                    // make sure to try the below
                    // w_ustmt.executeUpdate();
                    // conn.rollback(savepoint1);
                } else {
                    handleDups(wiki_url, base_url, name5, name4, name3,
                            name2, name1, name0, w_ustmt);
                    w_ustmt.setQueryTimeout(30);
                    // dup_pstmt.addBatch(dup_sql);
                    // int count3[] = dup_pstmt.executeBatch();
                }

            }
            // may not need executeUpdate
            int counts[] = w_ustmt.executeBatch();
            System.out.println("here is " + counts);
            conn.commit();
            conn.setAutoCommit(true);
            // counter = 0;
            // }
        }
        System.out.println("finished queries");
        rs.close();

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        // System.exit(1);
    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(Shapefile_Repair.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage());
        System.out.println("SQLState C: " + ex.getSQLState() + " " + lgr);
        System.out.println("VendorError C: " + ex.getErrorCode());
        if (ex != null) {
            System.out.println(ex.getNextException());
        }
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
        }
    }
     conn.close();
}

}

The errors/SQL returned from printing was long with the first couple print statements indicating the # of allowable rows so I shortened it: 
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/null'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/?uilly (France.Picardie.Aisne.Laon.Craonne)'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/?uilly (France.Champagne-Ardenne.Marne.Épernay.Dormans)'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/01 (Vietnam.Ðông Nam B?.H? Chí Minh city.Qu?n 3)'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/01 (Vietnam.Ðông Nam B?.H? Chí Minh city.Qu?n 10)'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/01 (Vietnam.Ðông Nam B?.H? Chí Minh city.Qu?n 6)'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/01 (Vietnam.Ðông Nam B?.H? Chí Minh city.Phú Nhu?n)'
UPDATE adminboundaries SET WIKI_URL = 'http://127.0.0.1/mediawiki/index.php/01 (Vietnam.Ðông Nam B?.H? Chí Minh city.Qu?n 4)'
Dec 18, 2012 4:31:05 PM Shapefile_Repair main 
SEVERE: Batch entry 0 UPDATE    adminboundaries SET        

WIKI_URL='http://127.0.0.1/mediawiki/index.php/null' was aborted.  
Call getNextException to see the cause.
SQLState C: 42703 java.util.logging.Logger@37504d
VendorError C: 0
org.postgresql.util.PSQLException: ERROR: column "wiki_url" of relation   

"adminboundaries" does not exist
Position: 28

共有1个答案

翟沈义
2023-03-14

异常cleary表示wiki_url不存在于表adminbounders中。

org.postgresql.util.psqlexception:错误:关系“adminbounders”的列“wiki_url”不存在

我认为它来自正在执行的update查询

conn.preparestatement(“update adminbounders SET WIKI_URL=?”);

由于postgresql区分大小写,请将查询更改为

conn.preparestatement(“update adminbordines SET WIKI_URL=?”);假设您的表名为adminbordines

 类似资料:
  • 问题内容: 我有一个oc_category_description表,其中的列是: category_id 姓名 和其他表oc_category,其中的列是: category_id 图像 parent_id 这是oc_category_description表的示例图片 oc_category表 在这里,我想显示名称,category_id,图像,parent_id,其中oc_category

  • 问题内容: 对于此查询,有必要使用吗? 任何改进或查询是否还好? 在这种情况下,查询速度很重要。 问题答案: 否,准备好的查询(正确使用时)将确保对数据进行正确的转义以进行安全查询。您有点正确地使用它们,只需要更改一件事。因为您使用的是“?” 占位符,最好通过execute方法传递参数。 请注意,如果要将其输出到页面,数据库清理并不意味着可以在HTML中安全显示,因此也可以在其上运行htmlspe

  • 下面是准备好的语句代码的片段: 它完成工作,但只有在数百次插入之后。是否有方法将列表或数组绑定到bind_param()参数,只需运行$stmtability->execute one time或其他可以提高性能的方法。 抱歉,如果这是问和回答之前。我四处看了一会儿,发现了一些类似的问题,但没有什么能明确地回答我的要求。

  • 问题内容: 如何将这些多个查询合并为一个(可以吗?) 顺便问一下,如果在完成所有查询后再执行mysql_close($ db),那会更好吗? 问题答案: 传递到作为第五个参数。 例: 当您使用mysql_fetch_ *或mysql_num_rows或mysql_affected_rows时,仅第一条语句有效。 例如,以下代码,第一个语句为INSERT,则无法执行mysql_num_rows和my

  • 问题内容: 我基本上有两个名为和的表。该表具有以下字段: 是表中字段的外键,其中包含以下字段: 我正在运行这样的查询: 除其他事项外,这将返回用户的。但是我想返回用户的用户名,而不是他们的u_id。因此,基本上,在该SELECT语句中,我还想运行: 我可以为此使用两个查询,但是我试图减少我的应用程序运行的查询,而且我知道有一种方法可以将其组合成一个查询,但是我只是不知道:< 有人知道答案吗?谢谢!