How to create Java method to count rows in Oracle table

Question

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?

if you do not know column name, you can use java.sql.ResultSetMetaData#getColumnName — JIV
– JIV, Commented Jul 23, 2012 at 15:28

Zsolt János · Accepted Answer · 2012-07-23 13:59:54Z

4

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(*)");

answered Jul 23, 2012 at 13:59

Zsolt János

5218 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Keppil · Accepted Answer · 2012-07-23 14:15:12Z

2

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 = ?";

edited Jul 23, 2012 at 14:15

answered Jul 23, 2012 at 13:56

Keppil

46.3k9 gold badges101 silver badges121 bronze badges

1 Comment

alex Over a year ago

Check link for some additional info.

jay c. · Accepted Answer · 2012-07-23 14:27:42Z

1

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

answered Jul 23, 2012 at 14:27

jay c.

1,56110 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to create Java method to count rows in Oracle table

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related