java 用Java从mysql数据库表中检索数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15944331/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
retrieving data from mysql database table in Java
提问by Young-kyu Q Han
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
它从表books 中选择并获取和设置每个字段为title、author、isbn 和price，然后将其添加到用户类中的bookList 对象中。当我使用该功能时
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:
应该从 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>
回答by Young-kyu Q Han
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.
我已经发现问题所在了。我忘了提到我使用的是 jsf 技术，并且还使用会话来存储对象和值。每次我尝试 selectbooks 方法时，它都会创建一个新用户而不是使用当前会话用户。因此，它总是会生成一个新用户并为当前会话用户提供空值。
回答by Julien
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.
正如 Abi 所提到的，您添加到错误的列表中。一般来说，您应该使用 OpenJPA，它更便于管理持久性。
回答by ashwinsakthi
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;
}
