CODEWITHSUNDEEP
javasqloracle11g
user1285928
user1285928

Reputation: 1476

How to create Java method to count rows in Oracle table

I want to create Java method which can count the rows in Oracle table. So far I made this:

public int CheckDataDB(String DBtablename, String DBArgument) throws SQLException {
    System.out.println("SessionHandle CheckUserDB:"+DBArgument);
    int count;
    String SQLStatement = null;

    if (ds == null) {
        throw new SQLException();
    }

    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException();
    }

    PreparedStatement ps = null;

    try {
        conn.setAutoCommit(false);
        boolean committed = false;
        try {
            SQLStatement = "SELECT count(*) FROM ? WHERE USERSTATUS = ?";

            ps = conn.prepareStatement(SQLStatement);
            ps.setString(1, DBtablename);
            ps.setString(2, DBArgument);

            ResultSet result = ps.executeQuery();

            if (result.next()) {
                count = result.getString("Passwd");
            }

            conn.commit();
            committed = true;
        } finally {
            if (!committed) {
                conn.rollback();
            }
        }
    } finally {
        /* Release the resources */
        ps.close();
        conn.close();
    }

    return count;
}

I want to use for different tables. This is the problem that I cannot solve:

count = result.getString("row");

Can you help me to solve the problem?

Upvotes: 1

Views: 3439

Answers (3)

jay c.
jay c.

Reputation: 1561

You cannot use bind variable in place of a database object in an SQL query, can you? It can only be used for parameter binding. Try this instead,

"SELECT count(*) as row_count FROM " + DBtablename + " WHERE USERSTATUS = ?";

This could be vulnerable to SQL Injection so you might want to check that DBtablename parameter is a valid database object name (i.e. at most 30 bytes long without spaces, and contains only valid chars for database object identifiers).

count = result.getInt("row_count");

Upvotes: 1

Keppil
Keppil

Reputation: 46239

This should do it: 

count = result.getInt("count(*)");

You need to use the same name as you specified in your query to get the value. You could also make your 

count = result.getString("row");

work by changing your query to 

SQLStatement = "SELECT count(*) as row FROM ? WHERE USERSTATUS = ?";

Upvotes: 2

Zsolt János
Zsolt János

Reputation: 511

count = result.getInt(1);

This is needed, because count is int. And you can specify the index of the row returned by the query, you don't need to access it by name.

But you could also do:

count = result.getInt("count(*)");

Upvotes: 4

Related Questions