Java MySQL JList – INSERT SELECT UPDATE DELETE

Java MySQL JList – INSERT SELECT UPDATE DELETE Tutorial.

This is an Java MySQL JList example .Its a simple CRUD app.We shall save items to MySQL,retrieve,update and delete.We shall be using JTextfields,JButtons and JList.We used netbeans in this tutorial.

What we do :

• Connect to MySQL using JDBC.
• User types data into JTextField and clicks save button.
• We then save this data into MySQL.
• We retrieve after saving and bind our data to JList.
• As user enters data he can see the changes he’s made.
• User can select an item from JList.
• It gets set to the corresponding jtextfield.
• He can change and click update to update.
• Or he can delete and it gets deleted from MySQL database.

===

SECTION 1 : Our Database Class

Database CRUD class

Main Responsibility : Perform all CRUD operations.

• INSERTS/SAVES data to MySQL database.
• SELECTS/RETRIEVES data to MySQL.
• UPDATES/EDITS data.
• DELETES data.

package jlist.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.DefaultListModel;

public class DBClass {

    String conString = "jdbc:mysql://localhost:3306/playersdb";
    String username = "root";
    String password = "";

    //INSERT INTO DB
    public Boolean add(String name) {
        //SQL STMT
        String sql = "INSERT INTO playerstb(Name) VALUES('" + name + "')";

        try {
            //GET COONECTION
            Connection con = DriverManager.getConnection(conString, username, password);

            // PREPARED STMT
            Statement s = con.prepareStatement(sql);

            //EXECUTE
            s.execute(sql);

            return true;

        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }

    }

    //RETRIEVE DATA
    public DefaultListModel retrieve() {
        DefaultListModel dm = new DefaultListModel();

        //SQL STMT
        String sql = "SELECT Name FROM playerstb";

        try {
            Connection con = DriverManager.getConnection(conString, username, password);

            //PREPARED STMT
            Statement s = con.prepareStatement(sql);
            ResultSet rs = s.executeQuery(sql);

            //LOOP THRU GETTING ALL VALUES
            while (rs.next()) {
                //GET VALUES
                String name = rs.getString(1);

                //ADD TO DM
                dm.addElement(name);
            }

            return dm;
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return null;
    }

    //UPDATE DATA
    public Boolean update(String id, String value) {
        String sql = "UPDATE playerstb SET Name ='" + value + "' WHERE Name='" + id + "'";

        try {
            Connection con=DriverManager.getConnection(conString, username, password);

            //STATEMENT
            Statement s=con.prepareStatement(sql);

            //EXECUTE
            s.execute(sql);

            return true;

        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

   //DELETE DATA
    public Boolean delete(String id)
    {
        //SQL STMT
        String sql="DELETE FROM playerstb WHERE Name ='"+id+"'";

        try
        {
            //CONNECTION
             Connection con=DriverManager.getConnection(conString, username, password);

             //sTAETEMT
             Statement s=con.prepareStatement(sql);

             //EXECUTE
             s.execute(sql);

             return true;

        }catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

}

SECTION 2 : Our GUI Class

GUI and Main class

Main Responsibility : STARTS OUR APPLICATION

• Is our main class.
• Derives from JFrame.
• Handles all GUI interactions such as input of data.
• Instantiates Database class and invokes its database manipulation methods.

package jlist.database;

import javax.swing.DefaultListModel;
import javax.swing.JOptionPane;

public class GUI_Jlist extends javax.swing.JFrame {

    String id="";

    public GUI_Jlist() {
        initComponents();
    }

    private void retrieve()
    {
        DefaultListModel dm=new DBClass().retrieve();

        jList1.setModel(dm);
    }

    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">
    private void initComponents() {

        jLabel2 = new javax.swing.JLabel();
        jPanel1 = new javax.swing.JPanel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jList1 = new javax.swing.JList();
        jLabel1 = new javax.swing.JLabel();
        nameTxt = new javax.swing.JTextField();
        addBtn = new javax.swing.JButton();
        updateBtn = new javax.swing.JButton();
        retrieveBtn = new javax.swing.JButton();
        deleteBtn = new javax.swing.JButton();
        clearBtn = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jLabel2.setText("ProgrammingWizards Channel");

        jPanel1.setBackground(new java.awt.Color(45, 155, 193));

        jList1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jList1MouseClicked(evt);
            }
        });
        jScrollPane1.setViewportView(jList1);

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 303, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(18, Short.MAX_VALUE))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane1)
                .addContainerGap())
        );

        jLabel1.setText("Name");

        addBtn.setText("Add");
        addBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                addBtnActionPerformed(evt);
            }
        });

        updateBtn.setText("Update");
        updateBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                updateBtnActionPerformed(evt);
            }
        });

        retrieveBtn.setText("Retrieve");
        retrieveBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                retrieveBtnActionPerformed(evt);
            }
        });

        deleteBtn.setText("Delete");
        deleteBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                deleteBtnActionPerformed(evt);
            }
        });

        clearBtn.setText("Clear");
        clearBtn.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                clearBtnActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap(475, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(retrieveBtn)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(deleteBtn)
                        .addGap(35, 35, 35))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(clearBtn)
                        .addGap(78, 78, 78))
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(addBtn)
                            .addGroup(layout.createSequentialGroup()
                                .addGap(13, 13, 13)
                                .addComponent(jLabel1)))
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addGap(29, 29, 29)
                                .addComponent(updateBtn))
                            .addGroup(layout.createSequentialGroup()
                                .addGap(3, 3, 3)
                                .addComponent(nameTxt, javax.swing.GroupLayout.PREFERRED_SIZE, 118, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addContainerGap())))
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(layout.createSequentialGroup()
                    .addGap(102, 102, 102)
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addGroup(layout.createSequentialGroup()
                            .addGap(147, 147, 147)
                            .addComponent(jLabel2))
                        .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addContainerGap(224, Short.MAX_VALUE)))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(86, 86, 86)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(nameTxt, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(131, 131, 131)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(addBtn)
                    .addComponent(updateBtn))
                .addGap(46, 46, 46)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(retrieveBtn)
                    .addComponent(deleteBtn))
                .addGap(43, 43, 43)
                .addComponent(clearBtn)
                .addContainerGap(125, Short.MAX_VALUE))
            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                .addGroup(layout.createSequentialGroup()
                    .addGap(25, 25, 25)
                    .addComponent(jLabel2)
                    .addGap(8, 8, 8)
                    .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addGap(25, 25, 25)))
        );

        pack();
    }// </editor-fold>

    private void jList1MouseClicked(java.awt.event.MouseEvent evt) {
       id=jList1.getSelectedValue().toString();
       nameTxt.setText(id);
    }

    //ADD
    private void addBtnActionPerformed(java.awt.event.ActionEvent evt) {
              if(new DBClass().add(nameTxt.getText()))
              {
                  JOptionPane.showMessageDialog(null, "Successfully Inserted");
                  nameTxt.setText("");

                  retrieve();
              }else
              {
                  JOptionPane.showMessageDialog(null, "Not Inserted");
              }
    }

    //UPDATE
    private void updateBtnActionPerformed(java.awt.event.ActionEvent evt) {

              if(new DBClass().update(id,nameTxt.getText()))
              {
                  JOptionPane.showMessageDialog(null, "Successfully Updated");
                  nameTxt.setText("");

                  retrieve();
              }else
              {
                  JOptionPane.showMessageDialog(null, "Not Updated");
              }
    }

    //RETRIEVE
    private void retrieveBtnActionPerformed(java.awt.event.ActionEvent evt) {
       retrieve();
    }

    //DELETE
    private void deleteBtnActionPerformed(java.awt.event.ActionEvent evt) {

              if(new DBClass().delete(id))
              {
                  JOptionPane.showMessageDialog(null, "Successfully Deleted");
                  nameTxt.setText("");

                  retrieve();
              }else
              {
                  JOptionPane.showMessageDialog(null, "Not Deleted");
              }
    }

    //CLEAR
    private void clearBtnActionPerformed(java.awt.event.ActionEvent evt) {
        jList1.setModel(new DefaultListModel());
    }

    /
     * @param args the command line arguments
     */
    public static void main(String args[]) {

        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new GUI_Jlist().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify
    private javax.swing.JButton addBtn;
    private javax.swing.JButton clearBtn;
    private javax.swing.JButton deleteBtn;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JList jList1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTextField nameTxt;
    private javax.swing.JButton retrieveBtn;
    private javax.swing.JButton updateBtn;
    // End of variables declaration
}

LAST SECTION

• Lets share more tips in OUR FB PAGE.
• To see the XML  we were parsing and the website itself please have look at the tutorial at our youtbe channel : ProgramminWizards.
• You’ll also find the demo for this example and step by step explanations.