How do I execute SQL queries inside multiple for loops?

Question

I am developing a Java program that involves integration of an Access Database. The program's purpose is to keep up with parking and services at a marina. The item listener for the services section is a series of for loops meant to save time from having to list every single service on its own. However, these results are needed to update the database and so far there are no changes being made when the program's GUI is being used and the Access Database does not update in any specified tables I choose. I select one option from the check boxes when the GUI is active and the database executes when I do. When I select a second check box, it only results in exception errors regardless of what I pick. I need to modify the program and/or database but I don't know how to tackle the situation. My code is listed below and any help would be appreciated.

package marinaProject;

/** Services, repairs, and provisions that are provided at a marina.
 * @author Chris Cardenas
 */

 import javax.swing.*;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.awt.*;
 import java.awt.event.*;
 import java.sql.ResultSet;
 import java.util.Arrays;
 import java.util.Random;
 import java.sql.*;
 import javax.swing.border.*;
 import java.sql.SQLException;

import java.sql.Statement;

public class Services extends JFrame {

static final String DATABASE_URL = "jdbc:ucanaccess://V:/Desktop/MarinaFinal.accdb";
Connection connection = null;
Statement statement = null;
ResultSet result = null;

JTabbedPane tabbedPane = new JTabbedPane();

private JPanel mainPanel = new JPanel(),
        buttonPanel = new JPanel(),
        servicesPanel = new JPanel(),
        repairsPanel = new JPanel(),
        provisionsPanel = new JPanel();

private final int NSERVICES = 6;
private final int NREPAIRS = 4;
private final int NPROVISIONS = 5;

private JCheckBox servicesPackages[] = new JCheckBox[NSERVICES];
private String servicePackageLabels[] = {"Fuel \n", "Water \n", "Waste \n", "Paint \n", "Winter \n", "Charging \n"};
private double servicePrice[] = {4, 3, 4, 7, 10, 0};
String serviceDbStatement;

private JCheckBox repairsPackages[] = new JCheckBox[NREPAIRS];
private String repairPackageLabels[] = {"Mechanical \n", "Electrical \n", "Communication \n", "Navigation \n"};
private double repairPrice[] = {100, 212, 100, 150};
String repairsDbStatement;

private JCheckBox provisionsPackages[] = new JCheckBox[NPROVISIONS];
private String provisionPackageLabels[] = {"Ice \n", "First Aid Kit \n", "Chips \n", "Popcorn \n", "Soda \n"};
private double provisionsPrice[] = {5, 10, 3, 2, 2};
String provisionsDbStatement;

private double totalBill;
private String servicesPrinted[] = new String[6];
private String repairsPrinted[] = new String[4];
private String provisionsPrinted[] = new String[5];
private double servicesPricesPrinted[] = new double[6];
private double repairsPricesPrinted[] = new double[4];
private double provisionsPricesPrinted[] = new double[5];
private int serviceNumber = 0;
private double boatID = 12345;

private JButton doneButton = new JButton("Done");

public Services() {
    setTitle("Services");

    mainPanel.setLayout(new GridLayout(2, 1, 1, 1));

    doneButton.addActionListener(new ActionListen());
    buttonPanel.add(doneButton);

    servicesPanel.setLayout(new BoxLayout(servicesPanel, BoxLayout.Y_AXIS));
    servicesPanel.setBorder(new TitledBorder("Services"));

    for (int i = 0; i < servicesPackages.length; i++) {
        servicesPackages[i] = new JCheckBox(servicePackageLabels[i]);
        servicesPanel.add(servicesPackages[i]);
        servicesPackages[i].addItemListener(new ItemListen());
    }
    tabbedPane.addTab("Services", null, servicesPanel, "Services Information");

    repairsPanel.setLayout(new BoxLayout(repairsPanel, BoxLayout.Y_AXIS));
    repairsPanel.setBorder(new TitledBorder("Repairs"));

    for (int i = 0; i < repairsPackages.length; i++) {
        repairsPackages[i] = new JCheckBox(repairPackageLabels[i]);
        repairsPanel.add(repairsPackages[i]);
        repairsPackages[i].addItemListener(new ItemListen());
    }
    tabbedPane.addTab("Repairs", null, repairsPanel, "Repairs Information");

    provisionsPanel.setLayout(new BoxLayout(provisionsPanel, BoxLayout.Y_AXIS));
    provisionsPanel.setBorder(new TitledBorder("Provisions"));

    for (int i = 0; i < provisionsPackages.length; i++) {
        provisionsPackages[i] = new JCheckBox(provisionPackageLabels[i]);
        provisionsPanel.add(provisionsPackages[i]);
        provisionsPackages[i].addItemListener(new ItemListen());
    }
    tabbedPane.addTab("Provisions", null, provisionsPanel, "Provisions Information");

    tabbedPane.setTabPlacement(SwingConstants.LEFT);
    mainPanel.add(tabbedPane);
    mainPanel.add(buttonPanel);
    add(mainPanel);

    Random rand = new Random();

    serviceNumber = rand.nextInt(5000) + 1;
}

private class ItemListen implements ItemListener {

    public void itemStateChanged(ItemEvent event) {
        totalBill = 0;
        for (int i = 0; i < servicesPackages.length; i++) {
            if (servicesPackages[i].isSelected()) {
                totalBill += servicePrice[i];
                servicesPrinted[i] = servicePackageLabels[i];
                servicesPricesPrinted[i] = servicePrice[i];
                ResultSet serviceDbResult = null;
                String servicesQuery;
            }
        }

        for (int i = 0; i < repairsPackages.length; i++) {
            if (repairsPackages[i].isSelected()) {
                totalBill += repairPrice[i];
                repairsPrinted[i] = repairPackageLabels[i];
                repairsPricesPrinted[i] = repairPrice[i];
                ResultSet repairsDbResult = null;
                String repairssQuery;
            }
        }

        for (int i = 0; i < provisionsPackages.length; i++) {
            if (provisionsPackages[i].isSelected()) {
                totalBill += provisionsPrice[i];
                provisionsPrinted[i] = provisionPackageLabels[i];
                provisionsPricesPrinted[i] = provisionsPrice[i];
                ResultSet provisionsDbResult = null;
                String provisionsQuery;
                String DATABASE_URL = "jdbc:ucanaccess://V:/Desktop/MarinaFinal.accdb";
            }
        }

        String DATABASE_URL = "jdbc:ucanaccess://V:/Desktop/MarinaFinal.accdb";
        try {
            {
                //establish connection to database
                connection = DriverManager.getConnection(DATABASE_URL);
                System.out.println("Made a connection");

                //create Statement for querying database
                statement = connection.createStatement();
                System.out.println("Established statement");

                //SQL Query
                String sql = "INSERT INTO Services (serviceNumber, boatID, repairMech, repairElectro, serviceWater, serviceWaste, serviceWinter,"
                        + "serviceCharging, servicePaint, serviceFuel, repairCommunication, repairNavigation, provisionIce"
                        + "provisionFirstAid, provisionChips, provisionPopcorn, provisionSoda, slipNumber, billNumber)"
                        + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                // TEMPORARY VARIABLES
                int slipNumTEMP = 4;
                int billnumTEMP = 454;

                PreparedStatement ps1 = connection.prepareStatement(sql);

                ps1.setDouble(1, serviceNumber);
                ps1.setDouble(2, boatID);
                ps1.setDouble(3, servicePrice[0]);
                ps1.setDouble(4, servicePrice[1]);
                ps1.setDouble(5, servicePrice[2]);
                ps1.setDouble(6, servicePrice[3]);
                ps1.setDouble(7, servicePrice[4]);
                ps1.setDouble(8, servicePrice[5]);
                ps1.setDouble(9, repairPrice[0]);
                ps1.setDouble(10, repairPrice[1]);
                ps1.setDouble(11, repairPrice[2]);
                ps1.setDouble(12, repairPrice[3]);
                ps1.setDouble(13, provisionsPrice[0]);
                ps1.setDouble(14, provisionsPrice[1]);
                ps1.setDouble(15, provisionsPrice[2]);
                ps1.setDouble(16, provisionsPrice[3]);
                ps1.setDouble(17, provisionsPrice[4]);
                ps1.setDouble(18, slipNumTEMP);
                ps1.setDouble(19, billnumTEMP);

                // Execute Query
                int rs = ps1.executeUpdate();
                if (rs == 1) {
                    System.out.println("Query executed");
                } else {
                    System.out.println("Not found.");
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

private class ActionListen implements ActionListener {

    public void actionPerformed(ActionEvent e) {
        String label = doneButton.getText();

        if (e.getSource() == doneButton) {
            if (label.equals("Done")) {
                setVisible(false);
                billGUI nextScreen = new billGUI(totalBill, servicesPrinted, repairsPrinted,
                        provisionsPrinted, servicesPricesPrinted, repairsPricesPrinted,
                        provisionsPricesPrinted);
                nextScreen.setSize(400, 650);
                nextScreen.setVisible(true);
            }
        }
    }
}
}

EDIT: Below are images of the Services table and the relationships with the other tables in the database.

Services table in the Design View

Relationships between the tables

"Integrity constraint violations" is the answer I'm getting as far as exceptions. — CJC
– CJC, Commented Dec 2, 2016 at 22:25
As @Ivan asks, please post table schema. Are any fields autonumber (boatid?), required (not null constraint), no zero-length, no duplicate index, not a double field (as all params are cast as), etc.? You can check these properties in design view of table with MSAccess.exe. — Parfait
– Parfait, Commented Dec 3, 2016 at 0:20
I just added some screenshots showing the specific table I'm making the program for, and the relationships between all the tables in the database. Prior to this, I disabled the "Enforce Referential Integrity" option between the Service and Bill tables in order to enable the query to work. Does this help you, Ivan? — CJC
– CJC, Commented Dec 3, 2016 at 20:18

Community · Accepted Answer · 2020-06-20 09:12:55Z

Reproducing your issue as screenshot shows the GUI form, the fix came down to your SQL which on a closer read maintains a syntax error with a missing comma.

Just after provisionIce you do not have a comma and so the query attempts to insert into a non-existent column due to line break provisionIceprovisionFirstAid which may output the Integrity Constraints Violation which in MSAccess.exe would output one of either:

Number of query values and destination fields aren't the same.

INSERT INTO statement contains unknown field name 'provisionIceprovisionFirstAid'.

So simply add the needed comma:

"repairCommunication, repairNavigation, provisionIce,"
----------------------------------------------------^  
 + "provisionFirstAid, provisionChips, provisionPopcorn, provisionSoda,

Additional Notes:

As of now, every checkbox click appends same exact data across all columns. Possibly you are still in sandbox development mode.
Consider connecting to the Access database during launch of GUI program in a different called method and then just append with connection object on each checkbox click. Right now connection is made on a Listener event.
Consider adding a Window Listener or Event (a regular SO question which can be found in search or Google/Bing) to properly end the program when window is closed by the user.

Collectives™ on Stack Overflow

How do I execute SQL queries inside multiple for loops?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related