Using select query Issue

Question

I'm using the select query to select a particular record from the table by name. The table consists of more than 25000 records.

Table format is

|rcode|rname|vcode|vname|div_sec|ofrn|phone|dat|

Also, it may contains more than one record with the same name.. I'm using the following query

ResultSet rs=stmt.executeQuery("select * from newfarmer where rname='"+get+"'");
if(rs.next())
{
     rcode=rs.getString("rcode");
     out.print(rcode);
     out.print(" ");
}

I have to find out the rcode of the given rname. Now the problem by using the above query is that, If I search for record with name "kannan" the table contains 6 records with the name "kannan" as

10001 kannan
10089 kannan
11826 kannan
12241 kannan
12389 kannan
19926 kannan

Now, my query only fetched the first record and give result as rcode="10001"

If I use

while(rs.next())
{
     rcode=rs.getString("rcode");
     out.print(rcode);
     out.print(" ");
}

it will print only the last record as rcode="19926". Suppose I want to fetch record for "kannan" with rcode 12241, how can I modify the query to get the record? Note that I have to use only rname to fetch the details.

Is there any solution to this problem? Someone help me please

No. There's no solution to this problem. It never occurred to the developers of relational database management systems that a user may want to relate two pieces of information in some way. Oh wait... — Strawberry
– Strawberry, Commented Feb 6, 2013 at 9:30
Just join the table to itself... FROM my_table x JOIN my_table y ON y.something = x.something... — Strawberry
– Strawberry, Commented Feb 6, 2013 at 9:36

paxdiablo · Accepted Answer · 2013-02-06 09:34:24Z

2

Actually that second snippet should output all of the kannan records, unless you've transcribed it wrongly. This would output the last:

while(rs.next()) {
    rcode=rs.getString("rcode");
}
out.print(rcode);
out.print(" ");

The ideal solution would be to change the query to limit based on both columns:

select * from newfarmer where rname = 'kannan' and rcode = '12241'

But, since you're dismissing that as a possibility, you'll have to get them all and filter for the ones you want (however inefficient that is compared to letting the DBMS do it). In other words, something like:

while(rs.next()) {
    rcode=rs.getString("rcode");
    if (rcode == "12241") {       // or some other identifying condition.
        out.print(rcode);
        out.print(" ");
    }
}

My advice would be to go back and re-examine your need for only using rname to get the records. That seems a bizarre limitation and makes any solution far less optimal than it could otherwise be.

answered Feb 6, 2013 at 9:34

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

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

5 Comments

Linga Over a year ago

But I need to fetch the rcode. I don't know the rcode. So I can't use it in my query

paxdiablo Over a year ago

@ling.s, you either know the rcode in advance (in which case use it) or you don't. If you don't, you have to fetch all the kannan records to try and figure out which is the right one. Presumably you have some way of figuring out which is the correct kannan record once you have them.

Linga Over a year ago

actually I need to retrieve the records when the user select a name

Linga Over a year ago

Ok. No other ways. I'll change my logic

paxdiablo Over a year ago

@ling.s, that's fine but if all you have is the name, you'll need to get them all and let the user figure it out.

NickJ · Accepted Answer · 2013-02-06 09:47:37Z

The 2nd snipped would certainly retrieve all rows in the ResultSet, but be aware that there's no guaranteed order of the records unless you supply an order by clause in the SQL. Maybe that's why you think it's only getting the last - you're only looking at the first row returned, finding it's the id you expect last, and ignoring the rest. Just a possibility.

Is there an index on the rname column? If not, the query will be quite slow.

Also, this:

ResultSet rs=stmt.executeQuery("select * from newfarmer where rname='"+get+"'");

is a big, open invitation to SQL injection attacks. Insead use parameters in a prepared statement:

//Just prepare the statement once for multiple uses:
Statement stmt = conn.prepareStatement("select * from newfarmer where rname=?");

//use it:
stmt.setString(1, name);
ResultSet rs = stmt.execute(); 
// etc...

Collectives™ on Stack Overflow

Using select query Issue

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related