英文:
MySQL looping "insert into"
问题
saveProfit方法
public void saveProfit() throws SQLException {
ObservableList<Bread> bread = FXCollections.observableArrayList();
Connection conn = null;
PreparedStatement ps = null;
Statement statement = null;
ResultSet resultSet = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bakery", "root", "a3756421");
statement = conn.createStatement();
resultSet = statement.executeQuery("SELECT * FROM bread");
while (resultSet.next()) {
Bread newBread = new Bread(
resultSet.getString("breadName"),
resultSet.getString("breadType"),
resultSet.getString("breadFlavour"),
resultSet.getInt("breadStock"),
resultSet.getInt("breadPrice"));
newBread.setBreadId(resultSet.getInt("breadId"));
bread.add(newBread);
}
for (Bread b : bread) {
String sql = "insert into best5(pname, profit) values (?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, b.getBreadName());
ps.setInt(2, b.getBreadPrice() * b.getBreadStock());
ps.executeUpdate(); // Add this line to execute the update
}
for (Bread b : bread) {
String sql = "insert into worst5(pname, profit) values (?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, b.getBreadName());
ps.setInt(2, b.getBreadPrice() * b.getBreadStock());
ps.executeUpdate(); // Add this line to execute the update
}
} finally {
// Close resources (ps, conn, resultSet) in appropriate finally blocks
}
}
Bread类
public class Bread {
private String breadName, breadFlavour, breadType;
private int breadStock, breadPrice, breadId;
private File imageFile;
private Image photo;
public Bread(String breadName, String breadType, String breadFlavour, int breadPrice, int breadStock) {
setBreadName(breadName);
setBreadType(breadType);
setBreadFlavour(breadFlavour);
setBreadPrice(breadPrice);
setBreadStock(breadStock);
setImageFile(new File("./src/Images/foodDefault.png"));
}
}
bread表
create table bread (
breadId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
breadName VARCHAR(30),
breadType VARCHAR(30),
breadFlavour VARCHAR(30),
breadStock INT,
breadPrice DOUBLE,
imageFile VARCHAR(100));
best5表
create table best5 (
id INT not null primary key auto_increment,
pname VARCHAR(30),
profit INT);
worst5表与best5表基本相同，只是表名不同。您尝试循环执行insert into best5(pname, profit) values(?,?)来从bread表中存储数据。但是，似乎只有bread表的第一行存储到best5表中，其余行被忽略。您做错了什么？我对JavaFX和MySQL都不熟悉。请纠正我的错误。
英文:
saveProfit method
public void saveProfit() throws SQLException {
ObservableList<Bread> bread = FXCollections.observableArrayList();
Connection conn = null;
PreparedStatement ps = null;
Statement statement = null;
ResultSet resultSet = null;
try {
conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/bakery", "root", "a3756421");
statement = conn.createStatement();
resultSet = statement.executeQuery("SELECT * FROM bread");
while (resultSet.next()) {
Bread newBread = new Bread(
resultSet.getString("breadName"),
resultSet.getString("breadType"),
resultSet.getString("breadFlavour"),
resultSet.getInt("breadStock"),
resultSet.getInt("breadPrice"));
newBread.setBreadId(resultSet.getInt("breadId"));
bread.add(newBread);
}
for(Bread b : bread){
String sql = "insert into best5(pname, profit) values (?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1,b.getBreadName());
ps.setInt(2, b.getBreadPrice()*b.getBreadStock());
}
for(Bread b : bread){
String sql = "insert into worst5(pname, profit) values (?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1,b.getBreadName());
ps.setInt(2, b.getBreadPrice()*b.getBreadStock());
}
}
}
Bread object
public class Bread {
private String breadName, breadFlavour, breadType;
private int breadStock, breadPrice, breadId;
private File imageFile;
private Image photo;
public Bread(String breadName, String breadType, String breadFlavour,int breadPrice, int breadStock) {
setBreadName(breadName);
setBreadType(breadType);
setBreadFlavour(breadFlavour);
setBreadPrice(breadPrice);
setBreadStock(breadStock);
setImageFile(new File("./src/Images/foodDefault.png"));
}
bread table
create table bread (
breadId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
breadName VARCHAR(30),
breadType VARCHAR(30),
breadFlavour VARCHAR(30),
breadStock INT,
breadPrice DOUBLE,
imageFile VARCHAR(100));
best5 table
create table best5 (
id INT not null primary key auto_increment,
pname VARCHAR(30),
profit INT);
worst5 table is basically the same with different name.
So, I am trying to loop the insert into best5(pname, profit) values(?,?) to store data from bread table. But, it seems like only the first row of bread table is stored into best5 table and the rest is ignored. What am I doing wrong? I am new to javafx and MySQL. Please correct my mistakes.
专注分享java语言的经验与见解，让所有开发者获益！
评论