retrieving data from mysql database table in Java

Question

I'm having a problem retrieving data from the database. I have a function

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;  
import java.sql.SQLException;
import java.sql.Statement;

public class DataAccess {
private Connection conn;
private User user = new User();

public DataAccess(){
    connect();
}

public boolean connect() {
    boolean success = true;

    String driverName ="com.mysql.jdbc.Driver";
    String conURL = "jdbc:mysql://localhost:3306/final_project";
    String user = "root";
    String pass = "1009";

    try {
        Class.forName(driverName).newInstance();
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        System.err.println(e.getMessage() + "------Cannot Load Driver");
        success = false;
    }

    try {
        conn = DriverManager.getConnection(conURL, user, pass);
    } catch (SQLException e) {
        System.err.println(e.getMessage() + "--SQL States: " + e.getSQLState() + "---- ErrorCode: " + e.getErrorCode());
        success = false;
    }
    return success;
}

public void insertBooks (Books books)
{
    try {           
        PreparedStatement stmt = conn.prepareStatement("insert into Books (Title, Author, ISBN, Total) VALUES (?, ?, ?, ?);");
        stmt.setString(1, books.getTitle());
        stmt.setString(2, books.getAuthor());
        stmt.setInt(3, books.getISBN());
        stmt.setDouble(4, books.getPrice());

        stmt.executeUpdate();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

public void selectBooks () {
    try {
        Statement stm = conn.createStatement();
        ResultSet rs = stm.executeQuery("SELECT * FROM books");
        while (rs.next()) {
            Books books = new Books();

            books.setTitle(rs.getString(1));
            books.setAuthor(rs.getString(2));
            books.setISBN(rs.getInt(3));
            books.setPrice(rs.getDouble(4));

            user.add(books);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}

which selects from the table books and gets and sets every field as title, author, isbn and price, and then it's added to the bookList object in the user class. When I use the function

public ArrayList<Books> getBookList() {
    return bookList;
}

nothing is returned.

The User class:

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import java.util.ArrayList;

@ManagedBean(name = "user")
@SessionScoped
public class User {
private String firstName;
private String lastName;
private ArrayList<Books> bookList = new ArrayList<Books>();
private ArrayList<Books> shopBookList = new ArrayList<Books>();
private double total;

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}

public ArrayList<Books> getBookList() {
    return bookList;
}

public void setBookList(ArrayList<Books> bookList) {
    this.bookList = bookList;
}

public double getTotal() {
    for (Books bk : bookList) {
        total += bk.getPrice();
    }
    return total;
}

public void setTotal(double total) {
    this.total = total;
}

public ArrayList<Books> getShopBookList() {
    return shopBookList;
}

public void setShopBookList(ArrayList<Books> shopBookList) {
    this.shopBookList = shopBookList;
}

public String add(Books books) {
    bookList.add(books);
    DataAccess da = new DataAccess();
    da.insertBooks(books);
    return "Added";
}

public String searchBooks() {
    DataAccess da = new DataAccess();
    da.selectBooks();
    return "books";
}

public String shop(Books books) {
    shopBookList.add(books);
    return null;
}

public String start() {
    bookList = new ArrayList<Books>();
    firstName = "";
    lastName = "";
    total = 0;
    FacesContext.getCurrentInstance().getExternalContext().invalidateSession();
    return "index";
}

}

The Books class:

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

@ManagedBean(name = "books")
@SessionScoped

public class Books {
private String title;
private String author;
private int ISBN;
private double price;

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public String getAuthor() {
    return author;
}

public void setAuthor(String author) {
    this.author = author;
}

public int getISBN() {
    return ISBN;
}

public void setISBN(int iSBN) {
    ISBN = iSBN;
}

public double getPrice() {
    return price;
}

public void setPrice(double total) {
    this.price = total;
}

}

The page that is supposed to grab data from user.booklist:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:ui="http://java.sun.com/jsf/facelets"
xmlns:h="http://java.sun.com/jsf/html"
xmlns:f="http://java.sun.com/jsf/core">

<h:body>
<h:form>
    <h1>Books Page</h1>
    <hr />
    <label>The following books are available:</label><br />
    <h:dataTable value="#{user.bookList}" var="bk" border="1">
        <h:column>
            <f:facet name="header">ISBN</f:facet>
            <h:outputText value="#{bk.ISBN}" />
        </h:column>
        <h:column>
            <f:facet name="header">Title</f:facet>
            <h:outputText value="#{bk.title}" />
        </h:column>
        <h:column>
            <f:facet name="header">Author</f:facet>
            <h:outputText value="#{bk.author}" />
        </h:column>
        <h:column>
            <f:facet name="header">Price</f:facet>
            <h:outputText value="#{bk.price}" />
        </h:column>
        <h:column>
            <f:facet name="header">Add</f:facet>
            <h:commandLink value="Add" action="#{user.shop(bk)}" />
        </h:column>
    </h:dataTable>
    <h:commandButton value="Home" action="index" />
    <h:commandButton value="Checkout" action="checkout" />
</h:form>
</h:body>
</html>

You are adding the book objects to user ArrayList and not to bookList — Abi
– Abi, Commented Apr 11, 2013 at 8:45
Have you debugged to see that you ever create books? Not enough code here to help! — John3136
– John3136, Commented Apr 11, 2013 at 8:45
Either there's no data in the object or somethings wrong with the code. There's not enough code here to say whats wrong.. — Thihara
– Thihara, Commented Apr 11, 2013 at 8:46
Are you absolutely positive that the query returns results and you are connected to the correct db? — Thihara
– Thihara, Commented Apr 11, 2013 at 8:56

Young-kyu Q Han · Accepted Answer · 2013-04-11 22:49:52Z

1

I've found out what the problem was. I forgot to mention that I was using jsf technology and also using sessions to store objects and values. Everytime I tried the selectbooks method, it would create a new user instead of using the current session user. Therefore, it would always generate a new user and give null values to the current session user.

answered Apr 11, 2013 at 22:49

Young-kyu Q Han

951 gold badge3 silver badges14 bronze badges

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

Comments

Julien · Accepted Answer · 2013-04-11 08:58:06Z

0

As mentioned by Abi, you add to the wrong list. In a more general manner, you should use OpenJPA, which is more convient to manage persistance.

answered Apr 11, 2013 at 8:58

Julien

1,31210 silver badges23 bronze badges

5 Comments

Young-kyu Q Han Over a year ago

The user.add function lets me add object to the arraylist

Julien Over a year ago

Ok you should rename it in smth more specific : addBook(...)

Julien Over a year ago

Why do all the users have all the books ? This "Select * From Books" is deeply annoying to my mind ;)

Young-kyu Q Han Over a year ago

well I was hoping that select * from books would get all the books and in the shopping cart, it would tell them which books they added

Julien Over a year ago

In fact the names of methods are misleading. Your method selectbook should be named getAllBooks(). If what you want is to have the list of all available books, it should not be stored in the user bean, since it is the same for all users. This object should be accessible in unique (static) way. The list of books for a user, should be a list that is unique for this user (bought, cart, ...). I think you should first draw a stekch of you dataflow, it would ease the later design of your software

blackpanther · Accepted Answer · 2013-04-11 09:22:24Z

0

public User selectBooks () {
    try {

        User userObject=new User();

        //ArrayList<Books> bookList=new ArrayList<Books>();

        Statement stm = conn.createStatement();
        ResultSet rs = stm.executeQuery("SELECT * FROM books");
        while (rs.next()) {
            Books books = new Books();

            books.setTitle(rs.getString(1));
            books.setAuthor(rs.getString(2));
            books.setISBN(rs.getInt(3));
            books.setPrice(rs.getDouble(4));

            user.getBookList().add(books);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return userObject;
}

edited Apr 11, 2013 at 9:22

blackpanther

11.5k12 gold badges53 silver badges79 bronze badges

answered Apr 11, 2013 at 8:59

ashwinsakthi

1,9564 gold badges30 silver badges56 bronze badges

6 Comments

Young-kyu Q Han Over a year ago

I've changed user.add(books) to user.getBookList().add(books) but still nothing shows up

ashwinsakthi Over a year ago

User mainUser=new User(); user=selectBooks(); System.out.println(user.getBookList().(0));

ashwinsakthi Over a year ago

Try the above code and see if something is printed in the console.

Young-kyu Q Han Over a year ago

where would I put the code? I have an xhtml going to a function in the user class and goes to another xhtml file. Sorry, I'm fairly new at this.

ashwinsakthi Over a year ago

You should call it at where you are interacting with the DB...and need the data..

|

Collectives™ on Stack Overflow

retrieving data from mysql database table in Java

3 Answers 3

Comments

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related