Alparslan Develio─člu Alparslan Develio─člu - 5 months ago 10
SQL Question

SQL Join query error in JSF 2.2 with Oracle 11g XE database

Hi everybody I am coding a Library Management System with using JSF 2.2 and Oracle XE 11g database. I have seperate tables like Borrowers Books etc. and certainly some web pages like Borrowers.xhtml and Books.xhtml. In my Borrowers table thee is a foreign key named Bookid. It references to Book table's primary key named Id. They both are Number(5) type attributes. I want to show book names which people borrowed. Now I can only show bookid s. I writed a join sql but this did not work. I have the following in book names line [DataPackage.Book[ isbn=null ]] My codes:

public List<Book> getBookNames(int bookid2)throws ClassNotFoundException,SQLException, InstantiationException, IllegalAccessException{
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","Alparslan-PC","123456");

BookidPs = con.prepareStatement("SELECT DISTINCT TITLE FROM BOOK JOIN BORROWER ON BOOK.ISBN= "+bookid2);
ResultSet Rs2 = BookidPs.executeQuery();
List<Book> liste = new ArrayList<Book>();
while(Rs2.next()){
book.setTitle(Rs2.getString("TITLE"));
liste.add(book);
}
System.out.print(liste);
return liste;
}


I tried to turn list to string. (deleted list and wrote String instead of it) I tried everything but this code gives me that error I have written on the top. Please help me. Thanks.

Answer

Your ON clause doesn't actually make the join on the referenced column. Try something like this:

SELECT DISTINCT b.Title FROM BOOK b
JOIN Borrower bo ON b.id = bo.BookID
WHERE b.ISBN = ?