Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Hi I coded a Library Management System in JSF 2.2 with using Netbeans 8.0.2 and Oracle 11g Express Edition. I have several pages named Books, Borrowers etc. and some tables named same in database. My problem is this: in Borrowers screen book ids are displayed. But I want to reach book's titles that have same id. Here is my code.

public List<Borrower> getBorrowers()throws ClassNotFoundException,SQLException, InstantiationException, IllegalAccessException{
    Class.forName("oracle.jdbc.driver.OracleDriver");
    con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","Alparslan-PC","123456");
    ps=con.prepareStatement("SELECT * FROM BORROWER");
    BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ISBN=?");
    ResultSet rs=ps.executeQuery();
    ResultSet Rs2=BookidPs.executeQuery();
    List<Borrower> liste = new ArrayList<Borrower>();
    while(rs.next()){
        Borrower borrow = new Borrower();
        borrow.setId(rs.getBigDecimal("ID"));
        borrow.setName(rs.getString("NAME"));
        borrow.setSurname(rs.getString("SURNAME"));
        borrow.setAddress(rs.getString("ADDRESS"));
        borrow.setPhonenumber(rs.getString("PHONENUMBER"));
        borrow.setBorrowdate(rs.getString("BORROWDATE"));
        //book.setIsbn(rs.getString("BOOKID"))
        borrow.setBookid(Rs2.getString("BOOKID"));
        //borrow.setBookid(((Book) rs.getObject("BOOKID")).getIsbn());
        liste.add(borrow);
    System.out.print(liste);
    return liste;
if line 22 changed with borrow.setBookid(rs.getString("BOOKID")); The bookids are shown. I wrote another query named BookidPs. if I run this program I get Missing IN or OUT parameter at index:: 1 error. If you help I will appretiate. Thanks
You didn't supply a value for it. Try like this:
BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ISBN=?");
BookidPs.setString(1, theIsbn);
The query needs an ISBN parameter, but I don't see where you have such variable in your code.
To use this query, you need to provide the missing parameter. In fact, it seems this second query is not useful for you at all. Given the code, I don't see why you want to use this query instead of getting the value from rs.
But actually, even with that parameter filled, the query is invalid SQL.
I think you probably want just one query, a JOIN.
Your second query seems to try to select the TITLE from the BOOK table.
So maybe you want this query, without parameters:
SELECT * FROM BORROWER JOIN BOOK ON BORROWER.BOOKID = BOOK.BOOKID
                Thanks for your attention. I wrote in this way     'BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ?=?");         BookidPs.setString(1, book.getIsbn());         BookidPs.setString(2, bookid);' But in this time I got HTTP Status 500 - ORA-00933: SQL command not properly ended error. If this way is not efficient please show me how to achieve this issue. How I can print book names with using book id.
– Alp
                Dec 7, 2014 at 13:05
                ?=? is incorrect. Column names cannot be parameterized that way. You can use ? only in right-hand side expressions.
– janos
                Dec 7, 2014 at 13:08
                Because I want to show Book names that are borrowed in Borrowers screen. I tried     BookidPs = con.prepareStatement("SELECT TITLE FROM BOOK AND BORROWER WHERE ISBN=?");         BookidPs.setString(1, bookid); same error with previous one.
– Alp
                Dec 7, 2014 at 13:16
                In Book table there are book names and in Borrower table there are book id s that are wanted. I want to reach ISBN s so to book names with using Book id in Borrower table. Now I can only show bookid in borrowers screen.
– Alp
                Dec 7, 2014 at 13:17
                Thanks I saw. This helped, this is the true statement but now I should add result of query to the list item named liste. It caries the printed values to view you know. How can I add it to list
– Alp
                Dec 7, 2014 at 13:31
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.