Delete records from the Access/SQL table-using Swing Components

Home	»	Java	»	Advance Java	»	Java Database Connectivity	»	Delete records-GUI

This example delete record from the Access table 'SchoolClasses' on the hard disk, using classes of java.sql and java.awt and more packages with Grapchical User Interface.

•  A JDBC-ODBC bridge driver is loaded
•  A Connection to the database is made, using getConnection method()
•  Using the Connection object, SQL's 'Delete' statement is sent to the database
•  A message is printed on the command line indicating the command was successful
•  Minimal exception handling is done

To build these concepts and skill, a 'SchoolClasses' table will be used as a working model. This table will be created in an Access database i.e. the SchoolDemo.mdb database will hold a 'SchoolClasses' table. The structure of the table will be as described below:

Database Name SchoolDemo.mdb Table Name SchoolClasses Primary Key Admission_No

The Following block of code creates the UI described using Java Swing Components.

/* =========================================================== File Name : DeleteRecords.java WebSite : http://javaproglang.blogspot.in/ Facebook : https://www.facebook.com/AdvanceJavaProgramming Twitter ID : https://twitter.com/AdvanceJava Created By : Bintu Chaudhary =========================================================== */ //*************************************************************************** /* DeleteRecords.java in M.S. Access - Created by Bintu Chaudhary */ //*************************************************************************** import java.awt.*; import java.net.*; import java.io.*; import java.sql.*; import javax.swing.*; import java.awt.event.*; import java.awt.print.*; import java.awt.geom.*; import javax.swing.event.*; import javax.swing.table.*; import javax.swing.border.*; import java.util.Calendar; import javax.swing.colorchooser.*; import javax.swing.tree.*; import javax.swing.text.html.*; import javax.swing.text.html.HTMLDocument; //__________________________________________________________________________________ /* Start the UpdatesRecords Class */ //----------------------------------------------------------------------------------- public class DeleteRecords extends JPanel implements ActionListener,FocusListener { Connection conn; Statement stmt,stmtdel; ResultSet rs; String url; int nrows,reply,response,stmtval,flag; Border border; Color c1=new Color(255,146,34); Color c2=new Color(251,254,120);//yellow Color c3=new Color(140,140,255);//Sky blue Color c4=new Color(120,120,230); Color c5=new Color(190,190,240); Color c6=new Color(147,255,174);//Light Green Font f1=new Font("Times New Roman",Font.BOLD,40); Font f2=new Font("Times New Roman",Font.BOLD,14); Font f3=new Font("Times New Roman",Font.BOLD,18); Font f4=new Font("Times New Roman",Font.BOLD,12); Font f5=new Font("Times New Roman",Font.BOLD,11); Font f6=new Font("COMIC SANS MS",Font.BOLD,22); Font fp=new Font("Times New Roman",Font.BOLD,45); Font fp1=new Font("COMIC SANS SERIF",Font.BOLD,30); JLabel lblinsrcds,clas,reg,rollno,sname,fname,mname,category,studob,sex,city,state,lblhobby,address,phoneno,mobno; JTextField txtreg,txtrollno,txtname,txtfname,txtmname,txtcate,txtdob,txtcity,txtstate,txtphone,txtmob,txtclass,txtsex; JTextArea taddress; String classval,regval,rollnoval,nameval,fnameval,mnameval,cateval,dateval,monthval,yearval,dobval,sexval,addval,cityval,stateval,phoneval,mobval; String sql_sixth,sql_seventh,sql_eigth,sql_nineth,sql_tenth,sql_eleth,sql_twlth,sql_alcls,sql_alcls6; JButton btn_search,btn_delete,btn_cancel,btn_exit; String strdel_reg,strdel_rollno,strdel_class,strdel_name,strdel_fname,strdel_mname,strdel_dob,strdel_cate,strdel_sex,strdel_address,strdel_city,strdel_state,strdel_phoneno,strdel_mobno; public DeleteRecords() { setBackground(c5); setLayout(null); JLabel lbl_delete=new JLabel("Delete Records"); lbl_delete.setBounds(350,8,350,30); lbl_delete.setFont(f6); add(lbl_delete); //JLabel Reg.No. reg=new JLabel("Registration No."); reg.setBounds(30,60,100,30); reg.setFont(f2); add(reg); //JTextField Reg.No. txtreg=new JTextField(10); txtreg.setBounds(230,60,100,30); txtreg.setBackground(c5); txtreg.setFont(f4); txtreg.addFocusListener(this); add(txtreg); //JButton Search btn_search=new JButton("Search"); btn_search.setBounds(332,60,76,30); btn_search.setBackground(c5); btn_search.setMnemonic(KeyEvent.VK_S); btn_search.setToolTipText("Search the Record"); btn_search.setMnemonic('s'); btn_search.addActionListener(this); add(btn_search); //JLabel RollNo rollno=new JLabel("RollNo"); rollno.setBounds(30,95,100,30); rollno.setFont(f2); add(rollno); //JTextField RollNo txtrollno=new JTextField(10); txtrollno.setEditable(false); txtrollno.setBounds(230,95,100,30); txtrollno.setBackground(c5); txtrollno.setFont(f4); //txtrollno.addFocusListener(this); add(txtrollno); //JLabel Class clas=new JLabel("Class"); clas.setBounds(30,130,100,30); clas.setFont(f2); add(clas); //JTextField Class txtclass=new JTextField(10); txtclass.setBounds(230,130,100,30); txtclass.setEditable(false); txtclass.setBackground(c5); txtclass.setFont(f4); add(txtclass); //JLabel Student's Name sname=new JLabel("Student's Name"); sname.setBounds(30,165,100,30); sname.setFont(f2); add(sname); //JTextField Student's Name txtname=new JTextField(10); txtname.setBounds(230,165,170,30); txtname.setEditable(false); txtname.setBackground(c5); txtname.setFont(f4); add(txtname); //JLabel Father's Name fname=new JLabel("Father's Name"); fname.setBounds(30,200,100,30); fname.setFont(f2); add(fname); //JTextField Father's Name txtfname=new JTextField(10); txtfname.setBounds(230,200,170,30); txtfname.setEditable(false); txtfname.setBackground(c5); txtfname.setFont(f4); add(txtfname); //JLabel Mother's Name mname=new JLabel("Mother's Name"); mname.setBounds(30,235,100,30); mname.setFont(f2); add(mname); //JTextField Mother's Name txtmname=new JTextField(10); txtmname.setBounds(230,235,170,30); txtmname.setEditable(false); txtmname.setBackground(c5); txtmname.setFont(f4); add(txtmname); //JLabel Category category=new JLabel("Category"); category.setBounds(30,270,170,30); category.setFont(f2); add(category); //JTextField Category txtcate=new JTextField(10); txtcate.setBounds(230,270,170,30); txtcate.setEditable(false); txtcate.setBackground(c5); txtcate.setFont(f4); add(txtcate); //JLabel Date of Birth studob=new JLabel("Date of Birth(DD/MM/YY)"); studob.setBounds(30,305,170,30); studob.setFont(f2); add(studob); //JTextFields Date of Birth txtdob=new JTextField(2); txtdob.setBounds(230,305,170,30); txtdob.setEditable(false); txtdob.setBackground(c5); txtdob.setFont(f4); add(txtdob); //JLabel Sex sex=new JLabel("Sex"); sex.setBounds(30,340,100,30); sex.setFont(f2); add(sex); //JTextField Sex txtsex=new JTextField(10); txtsex.setBounds(230,340,170,30); txtsex.setEditable(false); txtsex.setBackground(c5); txtsex.setFont(f4); add(txtsex); //JLabel Address address=new JLabel("Address"); address.setBounds(30,375,100,30); address.setFont(f2); add(address); //JTextArea Address taddress=new JTextArea(6,50); taddress.setBounds(230,375,170,40); taddress.setEditable(false); taddress.setBackground(c5); taddress.setFont(f4); add(taddress); //JLabel City city=new JLabel("City"); city.setBounds(30,425,100,30); city.setFont(f2); add(city); //JTextField City txtcity=new JTextField(10); txtcity.setBounds(230,425,170,30); txtcity.setEditable(false); txtcity.setBackground(c5); txtcity.setFont(f4); add(txtcity); //JLabel State state=new JLabel("State"); state.setBounds(430,425,100,30); state.setFont(f2); add(state); //JTextField State. txtstate=new JTextField(10); txtstate.setBounds(530,425,170,30); txtstate.setEditable(false); txtstate.setBackground(c5); txtstate.setFont(f4); add(txtstate); //JLabel Phone No. phoneno=new JLabel("Phone No"); phoneno.setBounds(30,460,100,30); phoneno.setFont(f2); add(phoneno); //JTextField Phone No. txtphone=new JTextField(10); txtphone.setBounds(230,460,170,30); txtphone.setEditable(false); txtphone.setBackground(c5); txtphone.setFont(f4); add(txtphone); //JLabel Mobile No. mobno=new JLabel("Mobile No"); mobno.setBounds(430,460,100,30); mobno.setFont(f2); add(mobno); //JTextField Mobile No. txtmob=new JTextField(10); txtmob.setBounds(530,460,170,30); txtmob.setEditable(false); txtmob.setBackground(c5); txtmob.setFont(f4); add(txtmob); //JButton Update btn_delete=new JButton("Delete"); btn_delete.setBackground(c5); btn_delete.setBounds(250,520,100,30); btn_delete.setMnemonic(KeyEvent.VK_D); btn_delete.setToolTipText("Delete the Record"); btn_delete.setMnemonic('d'); btn_delete.addActionListener(this); add(btn_delete); //JButton Cancel btn_cancel=new JButton("Cancel"); btn_cancel.setBackground(c5); btn_cancel.setBounds(350,520,100,30); btn_cancel.setMnemonic(KeyEvent.VK_C); btn_cancel.setToolTipText("Cancel"); btn_cancel.setMnemonic('c'); btn_cancel.addActionListener(this); add(btn_cancel); //JButton Exit btn_exit=new JButton("Exit"); btn_exit.setBackground(c5); btn_exit.setBounds(450,520,100,30); btn_exit.setMnemonic(KeyEvent.VK_X); btn_exit.setToolTipText("Exit"); btn_exit.setMnemonic('x'); btn_exit.addActionListener(this); add(btn_exit); } public void focusGained(FocusEvent fe) { fe.getComponent().setBackground(Color.yellow); } public void focusLost(FocusEvent fe) { fe.getComponent().setBackground(Color.white); } public void actionPerformed(ActionEvent ae) { if(ae.getSource()==btn_search) { Search_Record(); } if(ae.getSource()==btn_delete) { String strreg=txtreg.getText(); String strrollno=txtrollno.getText(); if(strreg.length()==0) { JOptionPane.showMessageDialog(null,"Please first Search Reg.No then Delete Record !!!","Note",2); } else if(strrollno.length()==0) { JOptionPane.showMessageDialog(null,"Nothing Delete Please Try Again!!!","Note",2); } else { Delete_Record(); flag=2; } } if(ae.getSource()==btn_cancel) { clsAllFlds(); } if(ae.getSource()==btn_exit) { reply=JOptionPane.showConfirmDialog(null,"Do you Quit the Application ?"); if(reply==JOptionPane.YES_OPTION) { clsAllFlds(); System.exit(0); } if(reply==JOptionPane.NO_OPTION) { return; } } } public void Search_Record() { String regval_search=txtreg.getText(); try { int intregval_search=Integer.parseInt(regval_search); String sql_search="SELECT * FROM SchoolClasses WHERE Admission_No="+intregval_search; rs=stmt.executeQuery(sql_search); if(rs.next()==false) { JOptionPane.showMessageDialog(null,"Registration No. " +intregval_search+ " is Not Exist!!!"); clsAllFlds(); } else { int int_addnoval=rs.getInt("Admission_No"); String str_addnoval=Integer.toString(int_addnoval); int int_rollval=rs.getInt("Roll_No"); String str_rollnoval=Integer.toString(int_rollval); String str_classval=rs.getString("Class"); String str_nameval=rs.getString("Name"); String str_fnameval=rs.getString("Father_Name"); String str_mnameval=rs.getString("Mother_Name"); String str_cateval=rs.getString("Category"); String str_dobval=rs.getString("DOB"); String str_sexval=rs.getString("Sex"); String str_addval=rs.getString("Address"); String str_cityval=rs.getString("City"); String str_stateval=rs.getString("State"); String str_phoneval=rs.getString("Phone_No"); String str_mobval=rs.getString("Mobile_No"); txtreg.setText(str_addnoval); txtrollno.setText(str_rollnoval); txtclass.setText(str_classval); txtname.setText(str_nameval); txtfname.setText(str_fnameval); txtmname.setText(str_mnameval); txtdob.setText(str_dobval); txtcate.setText(str_cateval); txtsex.setText(str_sexval); taddress.setText(str_addval); txtcity.setText(str_cityval); txtstate.setText(str_stateval); txtphone.setText(str_phoneval); txtmob.setText(str_mobval); getValueForDelete(); } } catch(SQLException se) { JOptionPane.showMessageDialog(null,"There is a Problem within Database !!!","Note",2); System.out.println(se.getMessage()); } } public void getValueForDelete() { strdel_reg =txtreg.getText(); strdel_rollno =txtrollno.getText(); strdel_class =txtclass.getText(); strdel_name =txtname.getText(); strdel_fname =txtfname.getText(); strdel_mname =txtmname.getText(); strdel_dob =txtdob.getText(); strdel_cate =txtcate.getText(); strdel_sex =txtsex.getText(); strdel_address =taddress.getText(); strdel_city =txtcity.getText(); strdel_state =txtstate.getText(); strdel_phoneno =txtphone.getText(); strdel_mobno =txtmob.getText(); } public void clsAllFlds() { txtreg.setText(""); txtrollno.setText(""); txtclass.setText(""); txtname.setText(""); txtfname.setText(""); txtmname.setText(""); txtdob.setText(""); txtcate.setText(""); txtsex.setText(""); taddress.setText(""); txtcity.setText(""); txtstate.setText(""); txtphone.setText(""); txtmob.setText(""); } public void setBackgroundColor() { txtreg.setBackground(c1); txtrollno.setBackground(c1); txtclass.setBackground(c1); txtname.setBackground(c1); txtfname.setBackground(c1); txtmname.setBackground(c1); txtdob.setBackground(c1); txtcate.setBackground(c1); txtsex.setBackground(c1); taddress.setBackground(c1); txtcity.setBackground(c1); txtstate.setBackground(c1); txtphone.setBackground(c1); txtmob.setBackground(c1); } //__________________________________________________________________________________________ /* In Real This Method allows to Modify the Student Information */ //-------------------------------------------------------------------------------------------- public void Delete_Record() { String sql_del; int int_delval; try { if(strdel_reg.length()==0) { JOptionPane.showMessageDialog(null,"Nothing to Delete Please Try Again !!!","Note",2); } else { int_delval=Integer.parseInt(strdel_reg); response=JOptionPane.showConfirmDialog(null,"Do You Really Want To Delete The Record ? "+int_delval); sql_del="DELETE FROM SchoolClasses WHERE Admission_No= "+int_delval; if(response==JOptionPane.YES_OPTION) { stmtval=stmt.executeUpdate(sql_del); if(stmtval==1) { JOptionPane.showMessageDialog(null," RECORD-DELETED...!!!","Note",2); clsAllFlds(); } } if(response==JOptionPane.NO_OPTION) { return; } } } catch(SQLException se) { JOptionPane.showMessageDialog(null,"Currently There is a Problem within Database","Note",2); System.out.println(se.getMessage()); } } //__________________________________________________________________________________ /* METHOD FOR PERSISTENT.ONE-TIME CONNECTION THROUGHOUT THE COMPLETE OPERATION */ //---------------------------------------------------------------------------------- public void connection() { url="jdbc:odbc:SchoolDemo"; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn=DriverManager.getConnection(url); stmt=conn.createStatement(); System.out.println("Successfully Connection"); } catch(SQLException se) { System.out.println("ERROR:"+se); } catch(ClassNotFoundException cnf) { System.out.println("ERROR:"+cnf); } } //________________________________________________________________________________________ /* MAIN CLASS */ //---------------------------------------------------------------------------------------- public static void main(String args[]) { DeleteRecords fr=new DeleteRecords(); fr.connection(); JFrame frm=new JFrame("Delete Records"); frm.setContentPane(fr); frm.setSize(800,600); frm.setVisible(true); frm.setResizable(false); } } /* END OF THE MAIN CLASS */ //*************************************************************************** /* DeleteRecords.java Created by Bintu Chaudhary */ //***************************************************************************

The Output of DeleteRecords table is as shown below:

Diagram 22.34 output of DeleteRecords.class

After running DeleteRecords.class file you will see above screen just enter Reg.No/AdmissionNo into Reg.No textbox and click on 'Search' Button this will show the next screen as shown in diagram 22.35, if reg.no is exists then it will show records otherwise it will give error message that. "Registration No. ...... is Not Exist!!!"

Diagram 22.35 entering Reg.No into Reg.No JTextBox

If Reg.No 603 is Exist then it will shown below diagram 22.28. You can use Keyboard short cut to search records like 'Alt + S'. Same for Delete Record 'Alt + D'.

Diagram 22.36 Reg.No is Exist

Diagram 22.37 Warring Message "Do You Really Want To Delete The Record ? 603"

Diagram 22.38 Record Deleted Successfully

JDBC - Working With User Interfaces - Using Swing Compoents

	Inserts records into the Access/SQL table using Swing Components
	View records from the Access table/SQL using Swing JTable
	Updates records into the Access/SQL table using Swing Components
	Deletes records from the Access/SQL table using Swing Components

JDBC - Programming:

	View records from the Access/SQL table.
	Inserts records into the Access/SQL table.
	Updates records into the Access/SQL table.
	Deletes records from the Access/SQL table.
	Drops the Access/SQL table.

• Next Creating A Model For An Air-line Reservation Using RMI
• Previous Updates records from the Access/SQL table using Swing Components