Select from jlist and update jtable using checkbox

Question

ive made a java program inside that i have 2 jlists(Main Category and Sub Category) and a jtable that retrieves values from mysql database , in the jtable there an extra column for checkbox , when the user selects a couple of rows and selects values from the jlists than it automatically updates 2 columns(the main category and sub category) in the jtable , the problem that im facing is that the first time u select a couple of rows and you update it works fine but then when u select a couple different rows and update than it updates the rows that u had selected before and and the rows that u just selected so how can i fix this problem? please help

My code:

//Import Statements
import java.awt.EventQueue;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.ImageIcon;
import javax.swing.JFrame;
import javax.swing.JList;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.JScrollPane;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.JLabel;
import javax.swing.JButton;

import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.Font;
import java.awt.Color;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.sql.PreparedStatement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.security.auth.Refreshable;

import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JTextField;

import net.proteanit.sql.DbUtils;

public class stock extends JFrame implements MouseListener {

    Object selectedCellValue[] = new Object[50];//Variable for selecting cell in JTable

    static Connection connection = null;//Connects to sql
    static Statement stmt = null;//Makes sql query or statement 
    static ResultSet rs = null;//gets results from the query

    JLabel lblDate;
    JLabel lblUpdateStock;
    JLabel lblcategory;
    JLabel lblSubcategory;

    JTextField txtPartNo;
    int selectedRow[] = new int[5000];//variable which stores selected row in a table 
    static int index = 0;


    static JTable table;

    JList main_list;//Main category
    JList sub_list;//Sub category

    JScrollPane scrollPane_1;
    private static String[] categories = {"Accessories", "Boxed Accessories", "Cordless", "Demolition", "Drilling", "Dust Extraction Accessories", "Garden / Agriculture Tools", "Grinding", "Grinding / Cutting", "Nibblers", "Petrol Engine Tools", "Planing", "Rotary / Demolition", "Rotary Drilling", "Routing", "Routing / Planing", "Sanding / Polishing", "Sawing", "Screwdriving", "Shears"};
    private static String[] sub_categories = {"Abrasive Belt", "Abrasive Disc / Pad", "Abrasive Paper", "Abrasive Wheels", "Accessories", "Accessories for Core Cutters", "Adapter / Drill Chuck / Dust bag", "Adapters / Battery Holder / Bulb", "Band Saw Blades and Accessories", "Battery / Charger Reference Chart", "Battery Chargers", "Belt Sander Accessories", "Broach Cutter for Magnetic Drilling Machine", "Brush and Dust bag", "Brush Cutter Accessories", "Chain Mortiser Accessories", "Chisels", "Combination Sets", "Core Bits", "Core Cutters", "Cut-out Tool Accessories", "Cutting metal", "Diamond Polishing Pad", "Drill Bits", "Drill Bits / Chisels", "Drill Bits / Holesaws", "Drill Chuck", "Drill Chucks / Angle Attachment", "Drill Stand", "Driver Drills / Bit Sets", "Dust Bag", "Dust Cover / Bag", "Extension Bar / Universal Joint / Adapters / Assembly", "Filters", "Flanges", "Glass and Tiles", "Grip", "Grip / Trolley", "Grip/ Loop Handle / Wrenches", "Guide Rule / Guide Rail", "Hammer Service Kits / Oil supply", "Hedge Trimmer Blades", "Hole saw", "Hook and Oil", "Hoses & Pipes", "Impact Sockets", "Jig Saw Accessories", "Jig Saw Blades", "Lawn Mower Blades", "Lithium-ion Batteries", "Maccess Accessories Set", "Masonry", "Masonry, Concrete", "Metal", "Mixing Blades", "Ni-Cd Batteries", "Nibbling Accessories", "Nozzles", "Oil / Grip", "Other Accessories", "Other Blades", "Other Blades & Rings", "Pad", "Petrol Blower Accessories", "Philips Bits", "Philips Bits Holder / Socket Bits", "Planner Accessories", "Plate Joiner Accessories", "Reciprocating Saw Blades", "Router Bits", "Router Bits / Impact Sockets", "Scroll Saw Blades", "Shearing Accessories", "Slide Compound / Miter Saw Accessories", "Slotted Bits", "Specialized Blades", "System Charts", "Table and Stand", "Table Saw Accessories", "Thin Cutting Wheels", "Trimmer, Router, Sash Router Accessories", "Wood", "Wood Cutting"};

    /**
     * Launch the application.
     */
    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                stock frame = new stock();
                frame.setVisible(true);
            }
        });
    }

    /**
     * Create the frame.
     */
    public stock() {
        getContentPane().setBackground(new Color(176, 224, 230));
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setBounds(100, 100, 664, 698);
        setResizable(false);
        setLocationRelativeTo(null);
        getContentPane().setLayout(null);

        // Customer List
        ImageIcon titleIcon = new ImageIcon("C:\\Users\\Samir\\Desktop\\banner.jpg");
        lblUpdateStock = new JLabel(titleIcon);
        lblUpdateStock.setFont(new Font("Castellar", Font.BOLD, 28));
        lblUpdateStock.setBounds(0, 0, 658, 111);
        getContentPane().add(lblUpdateStock);

        JLabel lblPartNo = new JLabel("Part No :");
        lblPartNo.setFont(new Font("Bookman Old Style", Font.PLAIN, 16));
        lblPartNo.setBounds(20, 132, 70, 14);
        getContentPane().add(lblPartNo);

        txtPartNo = new JTextField();
        txtPartNo.setBounds(100, 127, 172, 24);
        getContentPane().add(txtPartNo);
        txtPartNo.setColumns(10);

        lblcategory = new JLabel("Main Category");
        lblcategory.setFont(new Font("Calisto MT", Font.PLAIN, 16));
        lblcategory.setBounds(103, 173, 127, 24);
        getContentPane().add(lblcategory);

        lblSubcategory = new JLabel("Sub Category");
        lblSubcategory.setFont(new Font("Bookman Old Style", Font.PLAIN, 16));
        lblSubcategory.setBounds(432, 173, 107, 24);
        getContentPane().add(lblSubcategory);

        // ScrollPane
        JScrollPane scrollPane2 = new JScrollPane();
        scrollPane2.setBounds(20, 480, 617, 154);
        getContentPane().add(scrollPane2);

        // Table
        table = new JTable();
        scrollPane2.setViewportView(table);

        // Button Update
        JButton btnUpdate = new JButton("update");
        btnUpdate.setBounds(274, 439, 107, 30);
        btnUpdate.setFont(new Font("Copperplate Gothic Bold", Font.PLAIN, 18));
        btnUpdate.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                try {
                    UpdateData();
                    PopulateData();
                } catch (SQLException ex) {
                    Logger.getLogger(stock.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        });
        getContentPane().add(btnUpdate);

        JScrollPane scrollPane = new JScrollPane();
        scrollPane.setBounds(20, 202, 287, 226);
        getContentPane().add(scrollPane);
        main_list = new JList(categories);

        scrollPane.setViewportView(main_list);
        main_list.setFont(new Font("Simplified Arabic Fixed", Font.PLAIN, 15));
        main_list.setVisibleRowCount(3);
        main_list.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);

        scrollPane_1 = new JScrollPane();
        scrollPane_1.setBounds(336, 202, 301, 226);
        getContentPane().add(scrollPane_1);
        sub_list = new JList(sub_categories);
        scrollPane_1.setViewportView(sub_list);
        sub_list.setFont(new Font("Simplified Arabic Fixed", Font.PLAIN, 15));
        sub_list.setVisibleRowCount(3);
        sub_list.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);


        PopulateData();

        table.addMouseListener(new MouseAdapter() {
            public void mouseClicked(MouseEvent e) {
                if (e.getClickCount() == 1) {
                    JTable target = (JTable) e.getSource();

                    selectedRow[index] = target.getSelectedRow();
                    int selectedColumn = 0;

                    selectedCellValue[index] = target.getValueAt(selectedRow[index], selectedColumn);
                    index++;
                }
            }
        });

    }

    private static void PopulateData() {

        // Clear table
        table.setModel(new DefaultTableModel());

        // Model for Table
        DefaultTableModel model = new DefaultTableModel() {
            public Class<?> getColumnClass(int column) {
                switch (column) {
                    case 0:
                        return String.class;
                    case 1:
                        return String.class;
                    case 2:
                        return String.class;
                    case 3:
                        return String.class;

                    case 4:
                        return Boolean.class;

                    default:
                        return String.class;
                }
            }
        };
        table.setModel(model);

        // Add Column
        // model.addColumn("Select");
        model.addColumn("product_code");
        model.addColumn("description");
        model.addColumn("main_category");
        model.addColumn("sub_category");
        model.addColumn("Select");

        Connection con = null;
        Statement s = null;

        try {
            //Change this
           // Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           // con = DriverManager.getConnection("jdbc:odbc:Project");

             try{       
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException e) {

            e.printStackTrace();
        } catch (IllegalAccessException e) {

            e.printStackTrace();
        } catch (ClassNotFoundException e) {

            e.printStackTrace();
        }

            con = DriverManager.getConnection("jdbc:mysql://192.168.1.254/asser_old?user=&password=");
            s = con.createStatement();

            String sql = "SELECT product_code, description, main_category,sub_category FROM stock";

            ResultSet rec = s.executeQuery(sql);
            int row = 0;
            while ((rec != null) && (rec.next())) {
                model.addRow(new Object[0]);
                model.setValueAt(false, row, 4); // Checkbox
                model.setValueAt(rec.getString("product_code"), row, 0);
                model.setValueAt(rec.getString("description"), row, 1);
                model.setValueAt(rec.getString("main_category"), row, 2);
                model.setValueAt(rec.getString("sub_category"), row, 3);

                row++;
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            JOptionPane.showMessageDialog(null, e.getMessage());
            e.printStackTrace();
        }

    }

    private void UpdateData() throws SQLException {

        Connection connect = null;
        Statement s = null;
        ResultSet rs = null;

        try{        
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException e) {

            e.printStackTrace();
        } catch (IllegalAccessException e) {

            e.printStackTrace();
        } catch (ClassNotFoundException e) {

            e.printStackTrace();
        }

        connect = DriverManager.getConnection("jdbc:mysql://192.168.1.254/asser_old?user=&password=");

        System.out.println("Connected");
        s = connect.createStatement();
        String part = "";
        String partno = txtPartNo.getText().toString();
        String Descrip = "";
        String cat = "";
        String subcat = "";
        String category = main_list.getSelectedValue().toString();
        String sub_category = sub_list.getSelectedValue().toString();
        String query = "Select description,main_category,sub_category from stock where product_code ='" + partno + "'";
        stmt = connect.createStatement();
        rs = stmt.executeQuery(query);
        while (rs.next()) {
            Descrip = rs.getString("description");
            cat = rs.getString("main_category");
            subcat = rs.getString("sub_category");
        }
        String query2 = "UPDATE stock SET main_category='" + category + "',sub_category='" + sub_category + "' where product_code ='" + partno + "'";
        stmt.executeUpdate(query2);
        System.out.println("Updated");
        txtPartNo.setText("");
        txtPartNo.requestFocus();
        System.out.println(selectedCellValue[index]);
        for (int count = 0; count < index; count++) {
            String sql = "select * from stock where product_code = '" + selectedCellValue[count] + "' ";

            rs = s.executeQuery(sql);

            String sql2 = "UPDATE stock SET main_category='" + category + "',sub_category='" + sub_category + "' where product_code = '" + selectedCellValue[count] + "'";

            s.executeUpdate(sql2);


            }

    }

    @Override
    public void mousePressed(MouseEvent e) {
    }

    @Override
    public void mouseReleased(MouseEvent e) {
    }

    @Override
    public void mouseEntered(MouseEvent e) {
    }

    @Override
    public void mouseExited(MouseEvent e) {
    }

    @Override
    public void mouseClicked(MouseEvent arg0) {
    }
}

Answer 1

A basic idea would be to reset the data in the table/model back to false, for example..

for (int row = 0; row < table.getRowCount(); row++) {
    if (table.getValueAt(row, 4) == Boolean.TRUE) {
        table.setValueAt(Boolean.FALSE, row, 4);
    }
}

Take a closer look at How to Use Tables for more details

Updated

Get rid of selectedCellValue, it's simply confusing matters.

Simply use something like the above to build a List of selected items

List<Integer> selectedRows = new ArrayList<>(25);
for (int row = 0; row < table.getRowCount(); row++) {
    if (table.getValueAt(row, 4) == Boolean.TRUE) {
        selectedRows.add(row);
    }
}

Then something like...

for (int row : selectedRows) {
    int code = table.getValueAt(row, 0);
    String sql = "select * from stock where product_code = '" + code + "' ";

    // Blah, blah, blah...

To get the values you need for the selected rows. Of course, you could combine the two ideas and use a single loop instead...

You are leaving your database resources dangerously unmanaged, this could reduce the perform of your application and cause the database server to maintain open connections beyond the useful lifespan of those resources.

Take a look at The try-with-resources Statement some ideas about how to better manage your resources. Remember, if you open it, you must close it.

You also, desperately, need to learn about Using Prepared Statements