Updates records from the Access/SQL table using Swing Components

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

This example updates records into 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 'update' 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 : UpdatesRecords.java WebSite : http://javaproglang.blogspot.in/ Facebook : https://www.facebook.com/AdvanceJavaProgramming Twitter ID : https://twitter.com/AdvanceJava Created By : Bintu Chaudhary =========================================================== */ //*************************************************************************** /* UpdatesRecords.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 UpdatesRecords 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_update,btn_cancel,btn_exit; String strupd_reg,strupd_rollno,strupd_class,strupd_name,strupd_fname,strupd_mname,strupd_dob,strupd_cate,strupd_sex,strupd_address,strupd_city,strupd_state,strupd_phoneno,strupd_mobno; public UpdatesRecords() { setBackground(c5); setLayout(null); border=new BevelBorder(BevelBorder.LOWERED); JLabel lbl_update=new JLabel("Updating Records"); lbl_update.setBounds(350,8,350,30); lbl_update.setFont(f6); add(lbl_update); //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.setBorder(border); 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.setBounds(230,95,100,30); txtrollno.setBorder(border); 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.setBorder(border); txtclass.setBackground(c5); txtclass.setFont(f4); txtclass.addFocusListener(this); 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.setBorder(border); txtname.setBackground(c5); txtname.setFont(f4); txtname.addFocusListener(this); 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.setBorder(border); txtfname.setBackground(c5); txtfname.setFont(f4); txtfname.addFocusListener(this); 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.setBorder(border); txtmname.setBackground(c5); txtmname.setFont(f4); txtmname.addFocusListener(this); 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.setBorder(border); txtcate.setBackground(c5); txtcate.setFont(f4); txtcate.addFocusListener(this); 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.setBorder(border); txtdob.setBackground(c5); txtdob.setFont(f4); txtdob.addFocusListener(this); 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.setBorder(border); txtsex.setBackground(c5); txtsex.setFont(f4); txtsex.addFocusListener(this); 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.setBorder(border); taddress.setBackground(c5); taddress.setFont(f4); taddress.addFocusListener(this); 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.setBorder(border); txtcity.setBackground(c5); txtcity.setFont(f4); txtcity.addFocusListener(this); 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.setBorder(border); txtstate.setBackground(c5); txtstate.setFont(f4); txtstate.addFocusListener(this); 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.setBorder(border); txtphone.setBackground(c5); txtphone.setFont(f4); txtphone.addFocusListener(this); 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.setBorder(border); txtmob.setBackground(c5); txtmob.setFont(f4); txtmob.addFocusListener(this); add(txtmob); //JButton Update btn_update=new JButton("Update"); btn_update.setBackground(c5); btn_update.setBounds(250,520,100,30); btn_update.setMnemonic(KeyEvent.VK_U); btn_update.setToolTipText("Update the Record"); btn_update.setMnemonic('u'); btn_update.addActionListener(this); add(btn_update); //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_update) { Update_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); getValueForUpdate(); } } catch(SQLException se) { JOptionPane.showMessageDialog(null,"There is a Problem within Database !!!","Note",2); System.out.println(se.getMessage()); } } public void getValueForUpdate() { strupd_reg =txtreg.getText(); strupd_rollno =txtrollno.getText(); strupd_class =txtclass.getText(); strupd_name =txtname.getText(); strupd_fname =txtfname.getText(); strupd_mname =txtmname.getText(); strupd_dob =txtdob.getText(); strupd_cate =txtcate.getText(); strupd_sex =txtsex.getText(); strupd_address =taddress.getText(); strupd_city =txtcity.getText(); strupd_state =txtstate.getText(); strupd_phoneno =txtphone.getText(); strupd_mobno =txtmob.getText(); } public void clsAllFlds() { 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 Update_Record() { try { if(flag==2) { reply=JOptionPane.showConfirmDialog(null,"Do You Want To Modify The Record ?"); if(reply==JOptionPane.YES_OPTION) { getValueForUpdate(); PreparedStatement pre_update; pre_update=conn.prepareStatement("UPDATE SchoolClasses SET Roll_No='"+strupd_rollno+"',Name='"+strupd_name+"',Father_Name='"+strupd_fname+"',Mother_Name='"+strupd_mname+"',Category='"+strupd_cate+"',DOB='"+strupd_dob+"',Sex='"+strupd_sex+"',Address='"+strupd_address+"',City='"+strupd_city+"',State='"+strupd_state+"',Phone_No='"+strupd_phoneno+"',Mobile_No='"+strupd_mobno+"'WHERE Admission_No="+strupd_reg); pre_update.executeUpdate(); JOptionPane.showMessageDialog(null,"Successfully Update Record","Note",2); setBackgroundColor(); Search_Record(); } } if(reply==JOptionPane.NO_OPTION) { return; } } catch(SQLException e) { JOptionPane.showMessageDialog(null,"Currently There is a Problem within Update Database","Note",2); System.out.println(e.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[]) { UpdatesRecords fr=new UpdatesRecords(); fr.connection(); JFrame frm=new JFrame("Updating Records"); frm.setContentPane(fr); frm.setSize(800,600); frm.setVisible(true); frm.setResizable(false); } } /* END OF THE MAIN CLASS */ //*************************************************************************** /* UpdatesRecords.java Created by Bintu Chaudhary */ //***************************************************************************

The Output of UpdatesRecords table is as shown below:

Diagram 22.28 The Update Record Screen for UpdatesRecords.class.

Once the UI, (as shown in diagram 22.25), has been created it is necessary to add the functionality to the UI so that it works like any standard Window's based commerical application data entry form.

Now, first of all, you will 'Search' record entering Registration No in Reg.No JTextField like '601' and then click on the 'Search' Button. If record is exist in database then it will show all records in UI fields otherwise form will show Error Message that 'Registration No. 61 is Not Exist!!!' as shown below diagram 22.26.

Diagram 22.29 Message displayed when wrong Reg.No is entered.

Diagram 22.30 Screen Ready for udpate record

Diagram 22.31 Screen asking Do you want update this record?

Diagram 22.32 Display message Record Successfully Updated

Diagram 22.33 Display Screen after successfully updated record

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.