Put SQL statement into a method

Thread view:

teser3@hotmail.com - 12 Oct 2007 00:21 GMT

I have a repeated resultset object that I use alot to execute a
statement that fetches max id from a table.

I was wondering if I can put it in a method and call the method each
time I need the max id?

The repeated part is:
Resultset rs = statement.executeQuery("select max(id) from
TableMain");
rs.next();

Here is an example of what I am doing now:

CODE
Statement statement = connection.createStatement();
if(condition here..)
{
Resultset rs = statement.executeQuery("select max(id) from
TableMain");
rs.next();
a = rs.getInt(1);
//my insert sql is here to insert into another table the value of
the max id....

//another call to get the max id:
if(another condition here...)
{
Resultset rs = statement.executeQuery("select max(id) from
TableMain");
rs.next();
c = rs.getInt(1);
//my insert sql is here to insert into another table the value of
the max id....

I need help on my attempt below because I am not sure how to do it.
My attempts keep giving me zero for max id or I dont fetch anything.
Please advise.

public ResultSet getMaxId()
{
Resultset rs = statement.executeQuery("select max(id) from
TableMain");
rs.next();
return rs;
}

Call it like this:

if(any condition here..)
{
getMaxId();
f = rs.getInt(1);
///my insert sql is here to insert into another table the value of
the max id....

Reply to this Message

Arne Vajhøj - 12 Oct 2007 00:29 GMT

> I have a repeated resultset object that I use alot to execute a
> statement that fetches max id from a table.
[quoted text clipped - 6 lines]
> TableMain");
> rs.next();

Ofcourse you can make a method:

public int getMaxId(Statement stmt) {
...
}

Arne

PS: In 99.9% of cases where people want to do a SELECT MAX(id) then
they are much better of doing something else.

Reply to this Message

Lew - 12 Oct 2007 01:12 GMT

>> I have a repeated resultset object that I use alot to execute a
>> statement that fetches max id from a table.
[quoted text clipped - 6 lines]
>> TableMain");
>> rs.next();

I know I have mentioned repeatedly in answer to this question as you have
reposted it, and others have too, that you absolutely must check the return
value of rs.next().

If you don't value our advice, why do you request it?

Signature

Lew

Reply to this Message

ram00540@gmail.com - 15 Oct 2007 12:33 GMT

> Arne Vajh?j wrote:
> > tes...@hotmail.com wrote:
[quoted text clipped - 17 lines]
> --
> Lew

hi you can create the function is as below.
public static int getMax(String column, String table) {
        PreparedStatement st = null;
        ResultSet rs = null;
        Connection con = null;

        int maxid = 0;
        try {
            con = DatabaseUtil.getConnection();
            String query = "select max(" + column + ") from " + table + ";";
            st = con.prepareStatement(query);
            rs = st.executeQuery();
            while (rs != null && rs.next()) {
                maxid = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();

                if (st != null)
                    st.close();
                if (con != null)
                    con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return maxid;
    }

}
and call every time you have required.

Reply to this Message

Put SQL statement into a method

Free Magazines