CODEWITHSUNDEEP
mysqlsqloracle-database
Linga
Linga

Reputation: 10563

Using select query Issue

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

Upvotes: 0

Views: 74

Answers (2)

NickJ
NickJ

Reputation: 9559

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...

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881573

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.

Upvotes: 2

Related Questions