Question

如何在java连接mysql中的数据查询结果中解决结果覆盖问题！

public void Select2(){

this.getConnection();
try{
sql=con.createStatement();
res=sql.executeQuery("select sells.pno,pname,price from sells,product where product.pno=sells.pno");
while(res.next()){
po=res.getString("pno");
pn=res.getString("pname");
pr=res.getString("price");}}

catch(Exception e1){;
e1.printStackTrace();
}finally{
}
}

这是我查询的代码，希望高手能帮我解决查询结果覆盖问题，每次执行的结果总是最后一个，很是头疼！万分感谢！
可以在我的代码上改么？

Answer 1

ResultSet 只有当前数据库连接存在时才有效，迭代循环是每次只能获取当前游标下数据：
所以你需要建立其他容器将获取的数据存储下来，我刚刚写的这个文件，是相当普通的一个DAO文件结构模式，可以好好看下。会让以后的工作变得更加得心应手。呵呵。

唉！我大餐都给你做好了，你还叫我往嘴里喂啊！
List<String> poList = new LinkedList<String>();
List<String> pnList = new LinkedList<String>();
List<String> prList = new LinkedList<String>();
while(res.next()){
po=res.getString("pno");poList.add(po);
pn=res.getString("pname");pnList.add(pn);
pr=res.getString("price");prList.add(pr);
}

/**************************************************************************************************/

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**课题：封装数据库的增删改查的工具类的实现。
*
* 假设相关数据库的表结构如下：
* 表名：user
* 列名及属性：id(int 自增)，name(varchar(20))，tele(char(12))，birthday(date)
* @author shy2850
*/
public class UserDAO {

Connection conn;

public UserDAO(Connection conn) {
this.conn = conn;
}

public int save(User user) throws SQLException {
String sql = "insert into user values(0,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getTele());
pstmt.setDate(3, user.getBirthday());
int n = pstmt.executeUpdate();
pstmt.close();
return n;
}

public int delete(User user) throws SQLException{
String sql = "delete from user where id = "+user.getId();
Statement stmt = conn.createStatement();
int n = stmt.executeUpdate(sql);
stmt.close();
return n;
}

public int update(User user) throws SQLException{
String sql = "update user set name=?, tele=?, birthday=? where id = "+user.getId();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(2, user.getName());
pstmt.setString(3, user.getTele());
pstmt.setDate(4, user.getBirthday());
int n = pstmt.executeUpdate(sql);
pstmt.close();
return n;
}

public User getUser(Integer id) throws SQLException{
String sql = "select * from user where id = " + id;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
User user = getUserFromResultSet(rs);
rs.close();
stmt.close();
return user;
}

public List<User> getAll() throws SQLException{
List<User> userList = new LinkedList<User>();
String sql = "select * from user ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
User u = getUserFromResultSet(rs);
userList.add(u);
}
return userList;
}

static User getUserFromResultSet(ResultSet rs) throws SQLException{
Integer id = rs.getInt("id");
String name= rs.getString("name");
String tele= rs.getString("tele");
Date birthday = rs.getDate("birthday");
return new User(id, name, tele, birthday);
}
}
/**
* 构建数据库表的java类映射
*/
class User{
private Integer id;
private String name;
private String tele;
private Date birthday;

public User() {
}
public User(Integer id, String name, String tele, Date birthday) {
super();
this.id = id;
this.name = name;
this.tele = tele;
this.birthday = birthday;
}

public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getTele() {
return tele;
}

public void setTele(String tele) {
this.tele = tele;
}

public Date getBirthday() {
return birthday;
}

public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}

Answer 2

如果你只是想要第一条记录的话就简单多了，
把
while(res.next()){
po=res.getString("pno");
pn=res.getString("pname");
pr=res.getString("price");}}

改成
if(res.next()){
po=res.getString("pno");
pn=res.getString("pname");
pr=res.getString("price");}}
也就是只查第一次。
当然如果你想抽取第三行的就可以这么些。
int i = 1;
while(res.next()){
if(i>3){
break;
}
po=res.getString("pno");
pn=res.getString("pname");
pr=res.getString("price");
i++;

}}

当然，要是取第4行的，就把
if(i>3)改为if(i>4)就可以了