MySQL循环插入”insert into”。

huangapple 未分类评论52阅读模式
英文:

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&lt;Bread&gt; bread = FXCollections.observableArrayList();
	Connection conn = null;
	PreparedStatement ps = null;
	Statement statement = null;
	ResultSet resultSet = null;
	try {
		conn = DriverManager.getConnection (&quot;jdbc:mysql://localhost:3306/bakery&quot;, &quot;root&quot;, &quot;a3756421&quot;);
		statement = conn.createStatement();
		resultSet = statement.executeQuery(&quot;SELECT * FROM bread&quot;);
		while (resultSet.next()) {
			Bread newBread = new Bread(
			resultSet.getString(&quot;breadName&quot;),
			resultSet.getString(&quot;breadType&quot;),
			resultSet.getString(&quot;breadFlavour&quot;),
			resultSet.getInt(&quot;breadStock&quot;),
			resultSet.getInt(&quot;breadPrice&quot;));
			newBread.setBreadId(resultSet.getInt(&quot;breadId&quot;));
			bread.add(newBread);
		}

		for(Bread b : bread){
			String sql = &quot;insert into best5(pname, profit) values (?,?)&quot;;
			ps = conn.prepareStatement(sql);
			ps.setString(1,b.getBreadName());
			ps.setInt(2, b.getBreadPrice()*b.getBreadStock());
		}

		for(Bread b : bread){
			String sql = &quot;insert into worst5(pname, profit) values (?,?)&quot;;
			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(&quot;./src/Images/foodDefault.png&quot;));

}

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.

huangapple
  • 本文由 发表于 2020年3月16日 00:42:27
  • 转载请务必保留本文链接:https://java.coder-hub.com/60695236.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定