Tuesday, August 23, 2011

Java Database Connectivity : Updating & Deleting Data

Simple steps on how to update or delete data of a table through a GUI applications in NetBeans IDE.

1 First, follow the steps for searching the data through a GUI. This is to make sure that you are able to bring the data in the text-field for further updation or deletion.
Run View for Searching Data

2 Make sure you have made the text-fields as editable. To make them editable, select all the text-fields and right click. Now, select properties and checkmark(if, unchecked) the editable option.

3 Add a new button and rename it as "Update". Now on the Update button's actionperformed event
try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con= (Connection)  DriverManager.getConnection("jdbc:mysql://localhost:3306/database1","root","");
Statement stmt=con.createStatement();
        String num  = (String) jList1.getSelectedValue();
        String no=jTextField1.getText();
        String title=jTextField2.getText();
        String auth=jTextField3.getText();
        String type=jTextField4.getText();
        String pub=jTextField5.getText();
        String qty=jTextField6.getText();
        String amt=jTextField7.getText(); 

String sql1 = "Update library set NO='" + (no)+ "', TITLE='" + (title)+ "', AUTHOR='" + (auth)+ "', TYPE='" + (type)+ "', PUB='" + (pub)+ "', QTY='" + (qty)+ "', AMOUNT='" + (amt)+ "' where NO = '" + (num) + "'";

        stmt.executeUpdate(sql1);


}
         catch(Exception e) {
            JOptionPane.showMessageDialog(this, e.getMessage());

        }

4 To make your program more better you can make the text-fields empty when the user has completed task of updating his table. Also, you can show a Message Box stating that the update was successful.
To do this add the following codes after/below the line - stmt.executeUpdate(sql1);
jTextField1.setText("");
jTextField2.setText("");
jTextField3.setText("");
jTextField4.setText("");
jTextField5.setText("");
jTextField6.setText("");
jTextField7.setText("");
JOptionPane.showMessageDialog(null, "Table LIBRARY Updated!");
Run View for Updating Database

You can see that when a user clicks the update button the text-fields becomes empty and a message pops-ups stating that the table was updated successfully!

5 Now, for deleting a row from a database. Insert one more button and rename it to "Delete".

6 On Delete button's actionPerformed event
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con= (Connection)  DriverManager.getConnection("jdbc:mysql://localhost:3306/database1","root","");
Statement stmt=con.createStatement();
String num  = (String) jList1.getSelectedValue();


String sql2 = "Delete from library where NO = '"+(num)+"'";
stmt.executeUpdate(sql2);
JOptionPane.showMessageDialog(null, "Row deleted from the database!");
}
catch(Exception e) {
JOptionPane.showMessageDialog(this, e.getMessage());
}
7 Here also I have used a JOptionPane to show a message to the user that the row was successfully deleted. Like the above message, you can also use various ways to make your program effective, example when a user clicks on delete button all the text fields gets cleared and also you can refresh the list to show that the deleted row does not exist any more in the database.
Run View for Delete

Remember to change the codes in blue according to your database and your GUI.

Please comment, if you are not able to follow.

//watch a video tutorial for java database connectivity on the videos page

31 comments:

  1. when i click the search button several times, the data again show themselves at the end of the list , how can i do to refresh it?

    ReplyDelete
  2. @Anonymous

    list.clear();

    Add the above code BELOW the line DefaultListModel list=(DefaultListModel) jList1.getModel(); on the Search button.

    ReplyDelete
  3. I have a hashmap(String, String)
    and I wanted to print its content in a jList or textbox for example. how do I do that?

    ReplyDelete
  4. Can you post Inserting data code.

    ReplyDelete
  5. thanks you're the best!!!

    ReplyDelete
  6. Hi,

    Thank you for your good work,

    I am having a problem here,
    String num = (String) jList1.getSelectedValue();

    since i am using Jtable to list my data, i changed the jlist1 to jtable and i am getting error on "getSelectedValue" please help.

    ReplyDelete
  7. for table it is jTable1.getValueAt(row, column);
    where row and column are integer index of your row and column.
    Also, index for first row and column is 0.

    ReplyDelete
    Replies
    1. Sir! I'm fun of programming... and I have a hard time analyzing the code on how to clear a jTable by pressing a Clear button in my jFrame. we need your help! :) TnX...!

      Delete
  8. i have problem with this line..

    Statement stmt=con.createStatement(); //line no 173

    it gies error as
    java.lang.NullPointerException
    at banking.CreateAcnt.jBcreateActionPerformed(CreateAcnt.java:173)
    at banking.CreateAcnt.access$000(CreateAcnt.java:12)

    i am implementing this code for creatining account for bank database.
    Please help me.
    Thanks!

    ReplyDelete
    Replies
    1. Your con object is null, check your url, username and password in the getConnection method.

      Delete
  9. guys note that the line:
    Class.forName("com.mysql.jdbc.Driver");
    is required to load the JDBC MySQL specific drivers to memory. The JAR file containing the above driver class needs to be present in the classpath.
    Java Database Connectivity

    ReplyDelete
  10. How Get Database records and show it in the JTextfield?
    thank you ;D

    ReplyDelete
  11. i need to do following operation tat s to store ,modify,delete,display some data in java like form representation just like data as studentid ,name,phone no:

    ReplyDelete
  12. how can i delete data in 3 tables in one click button,the data in that 3 tables has same id no.? can you help me?

    ReplyDelete
  13. thank you so much
    i have to mention that your blogs have been particularly helpful!
    great work sir, kudos!

    there's other problem i need help with...
    i need to fetch data from ms excel and place it into an oracle database.
    i am able to make connections to both these databases one at a time.

    is it possible to make multiple connections in the same code?
    how?
    please help!

    ReplyDelete
  14. how can i select data first and update?
    I made like this: if(e.getSource()==b4)
    {
    int i=0;
    try
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con=DriverManager.getConnection("jdbc:odbc:db1");
    ps1=con.prepareStatement("Select * from Details where Roll No=?");
    ps1.setString(1,t3.getText());

    ResultSet rs=ps1.executeQuery();
    while(rs.next())
    {
    i++;
    s1=rs.getString(1);
    s2=rs.getString(2);
    s3=rs.getString(4);
    s4=rs.getString(5);
    s5=rs.getString(6);
    }

    if(i==0)
    {
    JOptionPane.showMessageDialog(null,"Roll no doesn't exist");
    }
    else
    {
    t2.setText(s2);
    t3.setText(s1);
    t4.setText(s3);
    t5.setText(s4);
    t6.setText(s5);
    }
    }
    catch(Exception e1)
    {
    System.out.println(e1);
    }
    }

    ReplyDelete
  15. About the delete button i have an issue here . it tells me that it deleted the row but it does nothing . i select the row from a combobox

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {

    try {
    Class.forName("org.sqlite.JDBC");
    Connection con= (Connection) DriverManager.getConnection("jdbc:sqlite:C:\\users\\Kajou\\momentsdb.sqlite");
    Statement stmt=con.createStatement();
    String num = (String) comboBox1.getSelectedItem();

    String sql2 = "Delete from Table1 where rowid = '"+(num)+"'";
    stmt.executeUpdate(sql2);
    JOptionPane.showMessageDialog(null, "Row deleted from the database!");
    }
    catch(Exception e) {
    JOptionPane.showMessageDialog(this, e.getMessage());
    }


    }

    ReplyDelete
  16. Thank so much Sir this tutorial is great

    ReplyDelete
  17. Hi wanna you one question. if for text field = gettext. for combo box?

    ReplyDelete
    Replies
    1. String selectedText = jComboBox1.getSelectedItem().toString();

      Delete
  18. can any one give me a cmplt code using gui insert dlte update
    thanks :)

    ReplyDelete
  19. sir my coding is not showing any error but it is not getting updated.please help my exams are coming near by.

    ReplyDelete
  20. my coding is not showing any error but it is not getting updated.please help my exams are coming soon.

    ReplyDelete
  21. have created design in java button want to update information in the database using the update button n see the updates there and then without going back to the database

    ReplyDelete