Shoaib Ali It's bloggin time

I used something Ruby | Rails taught me in m java jdbc assignment. It's not related to rails in any way then SQL in general. It's the concept of using Prepared statements.Really useful in this particular scenario.

public String showLoanedBooks() {
public String showLoanedBooks() {
PreparedStatement sa = null;
PreparedStatement sb = null;

String result = "";
boolean r = false;
String selectbooksql = "SELECT DISTINCT ISBN, Title, Edition_No, NumofCop, NumLeft FROM Book NATURAL JOIN Cust_Book;";
String selectauthorsql = "SELECT Surname FROM Author NATURAL JOIN Book_Author WHERE ISBN = ? ORDER BY ISBN;";
String selectcustomersql = "Select CustomerID, L_Name, F_Name, City from Customer natural join Cust_Book WHERE ISBN = ? ;";
try{
result = "Show Loaned Books: \n\n";

Statement s = con.createStatement();
sa = con.prepareStatement(selectauthorsql);
sb = con.prepareStatement(selectcustomersql);

ResultSet rs = s.executeQuery(selectbooksql);

while (rs.next()){
r = true;
//Going through each book Book
result = result +(rs.getString(1)
+ ": " +rs.getString(2)
+ "\n\tEdition number: "
+ rs.getString(3)
+ " Number of copies: "
+ rs.getString(4)
+ " Copies left: "
+ rs.getString(5)
+ "\n\tAuthors: ");

//Going through all the authors of each book
String isbn = rs.getString(1);

sa.setInt(1, Integer.parseInt(isbn));
sb.setInt(1, Integer.parseInt(isbn));

ResultSet rsa = sa.executeQuery();

while (rsa.next()){
result = result
+ (rsa.getString("surname").trim()) + ", ";
}
result = result + "\n\tBorrowers:\n";

//Selecting people that have borrowed the current book
ResultSet rsb = sb.executeQuery();

while (rsb.next()){
result = result + ("\t\t" + rsb.getInt(1) + ": "
+ rsb.getString("L_Name").trim() + ", "
+ rsb.getString("F_Name").trim() + " - "
+ rsb.getString("City") + "\n");
}
}
}
catch (SQLException sqlex){
System.out.println(sqlex.getMessage());
}
if (r == true)
return result;
else
return "Show Loaned Books: \n(no loaned books)";
}

I tried using this code instead but postgres was being very picky I think. So, I had to parse it as a integer.

//Going through all the authors of each book
String isbn = rs.getString(1);

sa.setString(1,isbn);
sb.setString(1, isbn);

The above should work! but I think it had more to do with the sql query maybe it was looking for ' 's or quotes around the "?" or something. Anyway parsing it as a in worked just as good.

At one point I got so frustrated that i decided to invent my own little code to replace the ? with appropriate variable. But then again java had more ticks up its sleeves.


String.replaceAll("\?", isbn);


But then the above would replace all the ?'s with isbn. Exactly what I wanted but what if you had more then ?'s it would replace all of em with isbn which might not be right :P

Anway.. you get the picture