0

can u help me how to connect to MS Access database using java codes??

import java.applet.Applet;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.lang.StringBuffer;
import java.io.IOException;
import java.io.*;
import java.sql.*;  

public class sample1 extends JFrame 

{
   private DataPanel myDataPanel;
   private Connection dbconn;
   private static int numPeople=0;
   private static String info;
   private static JTextArea txtInfo=new JTextArea( 250, 40 ); 


    String ID_Num, Name, Age, Status, Gender, Address, Contact_Num, Email, Plate_Num, Type, Model;

       public sample1()

       {
     super("Car Registtration Form");

        FlowLayout myGridLayout= new FlowLayout(FlowLayout.LEFT); 

        Container p = getContentPane();
        myDataPanel=new DataPanel();
        p.add(myDataPanel);
        myDataPanel.setLayout(myGridLayout);


            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String dataSourceName = "tbl_sample1.mdb";
                String dbURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
                dbURL += dataSourceName + ";DriverID=22;READONLY=true)";
                dbconn = DriverManager.getConnection(dbURL,"","");
                Statement s = dbconn.createStatement();
        }

       catch ( ClassNotFoundException cnfex ) {
            cnfex.printStackTrace();
            info=info+"Connection unsuccessful\n" + cnfex.toString();
        }
       catch ( SQLException sqlex ) {
            sqlex.printStackTrace();
            info=info+"Connection unsuccessful\n" +sqlex.toString();
       }
       catch ( Exception excp ) {
            excp.printStackTrace();
            info=info+excp.toString();

       }
       txtInfo.append(info);  

       }







   class DataPanel extends JPanel implements ActionListener  {

           JLabel lblID_Num = new JLabel(" ID Number");
           JTextField txtID_Num = new JTextField(25);
           JLabel lblName   = new JLabel(" Name");
           JTextField  txtName= new JTextField(25);
           JLabel lblAge    = new JLabel(" Age");
           JTextField  txtAge= new JTextField(25);
           JLabel lblStatus = new JLabel(" Status");
           JTextField  txtStatus= new JTextField(25);
           JLabel lblGender = new JLabel(" Gender");
           JTextField  txtGender= new JTextField(25);
           JLabel lblAddress = new JLabel(" Address");
           JTextField txtAddress;
           JLabel lblContact_Num= new JLabel(" Contact Number");
           JTextField txtContact_Num=new JTextField(25);
           JLabel lblEmail  = new JLabel(" Email");
           JTextField txtEmail;
           JLabel lblPlate_Num  = new JLabel(" Plate Number");
           JTextField  txtPlate_Num= new JTextField(25);
           JLabel lblType  = new JLabel(" Type");
           JTextField  txtType= new JTextField(25);
           JLabel lblModel  = new JLabel(" Model");
           JTextField  txtModel= new JTextField(25);



                JButton btnAdd=new JButton("Add Record",new ImageIcon("save.png"));
                JButton btnFind=new JButton("Find Record",new ImageIcon("find.png"));
                JButton btnDelete=new JButton("Delete Record",new ImageIcon("delete2.png"));
                JButton btnUpdate=new JButton("Update Record",new ImageIcon("update.png"));    
                JButton btnClear=new JButton("Clear",new ImageIcon("open.png"));
                JButton btnExit=new JButton("Exit",new ImageIcon("exit2.png"));
                JButton btnShow=new JButton("Show",new ImageIcon("CLEAR.png"));

              public DataPanel() {

                 lblID_Num.setForeground(Color.red);
                 lblName.setForeground(Color.red);
                 lblAge.setForeground(Color.red);
                 lblStatus.setForeground(Color.red);
                 lblGender.setForeground(Color.red);
                 lblAddress.setForeground(Color.red);
                 lblContact_Num.setForeground(Color.red);
                 lblEmail.setForeground(Color.red);
                 lblPlate_Num.setForeground(Color.red);
                 lblType.setForeground(Color.red);
                 lblModel.setForeground(Color.red);

                     JPanel myPanel = new JPanel();
                     JPanel myPanel2 = new JPanel();
                     JPanel myPanel3 =new JPanel();                    

                     myPanel.setLayout(new GridLayout (50,2 , 20,2));
                     myPanel.setBounds(50,40,20,200);
                     myPanel.setBackground(new Color(14, 10,0));  
                     myPanel2.setLayout(new GridLayout (7,13));
                     myPanel3.setLayout(new GridLayout(100,100));
                     myPanel2.setSize(500,100);

                      setBackground(new Color(14,58,119));                                 
                      add(myPanel);
                      add(myPanel2);
                      myPanel.add(lblID_Num);
                      myPanel.add(txtID_Num);
                      myPanel.add(lblName);
                      myPanel.add(txtName);
                      myPanel.add(lblAge);
                      myPanel.add(txtAge);
                      myPanel.add(lblGender);
                      myPanel.add(txtGender);
                      myPanel.add(lblStatus);
                      myPanel.add(txtStatus);
                      myPanel.add(lblAddress);
                      txtAddress = new JTextField(25);
                      myPanel.add(txtAddress);
                      myPanel.add(lblContact_Num);
                      myPanel.add(txtContact_Num);
                      myPanel.add(lblEmail);
                      txtEmail = new JTextField(25);
                      myPanel.add(txtEmail);
                      myPanel.add(lblPlate_Num);
                      myPanel.add(txtPlate_Num);
                      myPanel.add(lblType);
                      myPanel.add(txtType);
                      myPanel.add(lblModel);
                      myPanel.add(txtModel);

                     myPanel2.add(btnDelete);
                     myPanel2.add(btnUpdate);
                     myPanel2.add(btnAdd);
                     myPanel2.add(btnFind);
                     myPanel2.add(btnClear);
                     myPanel2.add(btnExit);
                     myPanel2.add(btnShow);

                     setLocation(25000,1000);
                     setSize(50,20);
                     setResizable(true);
                     show();

                     btnAdd.addActionListener(this);
                     btnFind.addActionListener(this);
                     btnUpdate.addActionListener(this);
                     btnClear.addActionListener(this);
                     btnExit.addActionListener(this);
                     btnDelete.addActionListener(this);
                     btnShow.addActionListener(this);
              }

              public void actionPerformed(ActionEvent event) {
                     String ID_Num ="";     
                     String Name ="";
                     String Age ="";
                     String Status ="";
                     String Gender ="";
                     String Address = "";
                     String Contact_Num = "";
                     String Email = "";
                     String Plate_Num = "";
                     String Type = "";
                     String Model = "";

                     Object source=event.getSource();
                     ID_Num = txtID_Num.getText().trim();
                     txtID_Num.setText(ID_Num);
                     Name = txtName.getText().trim();  
                     txtName.setText(Name);          

                     Age = txtAge.getText().trim();
                     txtAge.setText(Age);
                     Status= txtStatus.getText().trim();
                     txtStatus.setText(Status);
                     Gender = txtGender.getText().trim();
                     txtGender.setText(Gender);
                     Address = txtAddress.getText().trim();
                     txtAddress.setText(Address);
                     Contact_Num = txtContact_Num.getText().trim();
                     txtContact_Num.setText(Contact_Num);
                     Email = txtEmail.getText().trim();
                     txtEmail.setText(Email);   
                     Plate_Num= txtPlate_Num.getText().trim();
                     txtPlate_Num.setText(Contact_Num); 
                     Type = txtType.getText().trim();
                     txtType.setText(Type); 
                     Model = txtModel.getText().trim();
                     txtModel.setText(Model);


                     if (source.equals(btnAdd))  {

                  try {
                        Statement statement = dbconn.createStatement();
                        if (    !ID_Num.equals("") &&
                                !Name.equals( "" ) &&
                                !Age.equals( "" ) &&
                                !Status.equals( "" ) &&
                                !Gender.equals("") &&
                                !Address.equals("")  &&
                                !Contact_Num.equals("") &&
                                !Email.equals( "" ) &&
                                !Plate_Num.equals( "" ) &&
                                !Type.equals( "" ) &&
                                !Model.equals("")) 
                          {

                               String temp = "INSERT INTO tbl_sample1 (" +

                                  "ID_Num, Name, Age, Status, Gender, Address, Contact Number, Email, Plate Number, Type, Model)" +
                                  " VALUES (" + ID_Num + ",'" + Name + "','" +
                                   Age  + "'," + Status + "','" + Gender + "','" + Address + "','" + Contact_Num + "','"+ Email + "','" + Plate_Num + "','" + Type + "','" + Model+ ")" ;

                            int result = statement.executeUpdate( temp );
                            if ( result == 1 )

                            { 
                                 JOptionPane.showMessageDialog(null,"\nRecord Inserted To DataBase\n");
                                 String query=""; 

                                 try  {

                                   query = "SELECT * FROM tbl_sample1 WHERE ID_Num=" +
                                   ID_Num ;
                                   ResultSet rs = statement.executeQuery( query );
                                   rs.next();
                                 }

                                catch ( SQLException sqlex )  {

                                   txtInfo.append( sqlex.toString() );
                                }
                             }

                           else {

                               txtInfo.append( "\nInsertion failed\n" );
                               txtID_Num.setText( "" );
                               txtName.setText( "" );
                               txtAge.setText("");
                               txtStatus.setText("");
                               txtGender.setText( "" );
                               txtAddress.setText("");
                               txtContact_Num.setText("");
                               txtEmail.setText("");
                               txtPlate_Num.setText("");
                               txtType.setText("");
                               txtModel.setText("");
                            }
                          }

                          else

                            JOptionPane.showMessageDialog(null, "Enter ID_Num,\nName,  Age , Gender " +
                            "Address & Email then press Add" );
                         statement.close();

                      }

                      catch ( SQLException sqlex )  {
                         txtInfo.append( sqlex.toString() );
                         JOptionPane.showMessageDialog(null,"ID number Already Exist\n Re-Enter");
                      }
                  }

              if (source.equals(btnFind)) {
                     try {
                       if ( !ID_Num.equals("") ) {

                              Statement statement =dbconn.createStatement();
                              String query = "SELECT * FROM tbl_sample1 " +
                                 "WHERE  ID_Num= " + ID_Num ;
                                 ResultSet rs = statement.executeQuery( query );
                                    display( rs );
                                 statement.close();
                        }

                        else


                          JOptionPane.showMessageDialog(null,"Enter ID_Num and "+
                                          " then press Find" );
                     }

                     catch ( SQLException sqlex )  {
                             txtInfo.append( sqlex.toString() + sqlex.getMessage() );
                     }
               }

            if (source.equals(btnUpdate))  {
              try   {
                  Statement statement = dbconn.createStatement();
                  if ( ! txtID_Num.getText().equals(""))  {

                           String temp = "UPDATE tbl_sample1 SET " +
                             "ID_Num=" + Integer.parseInt(txtID_Num.getText())+
                             ", Name='" + txtName.getText() +
                             "',Age='" + txtAge.getText() +
                             "', Status='" + txtStatus.getText() +
                             "', Gender='" + txtGender.getText() +
                             "',Address ='" + txtAddress.getText() +
                             "', Contact_Num='" + txtContact_Num.getText() +
                             "',Email ='" + txtEmail.getText()+
                             "', Plate_Num='" + txtPlate_Num.getText() +
                             "', Type='" + txtType.getText() +
                             "', Model='" + txtModel.getText() +
                             "' WHERE ID_Num=" + txtID_Num.getText();
                             int result = statement.executeUpdate( temp );
                             if ( result == 1 )
                             JOptionPane.showMessageDialog(null, "\nUpdate successful\n" );

                          else {
                               txtInfo.append( "\nUpdate failed\n" );
                               txtID_Num.setText( "" );
                               txtName.setText( "" );
                               txtAge.setText("");
                               txtStatus.setText("");
                               txtGender.setText( "" );
                               txtAddress.setText("");
                                txtContact_Num.setText("");
                               txtEmail.setText("");
                                txtPlate_Num.setText("");
                                 txtType.setText("");
                                  txtModel.setText("");

                          }

                        statement.close();

                       }

                       else


                          JOptionPane.showMessageDialog(null, "\nNo information to be Updated " );


                    }

                    catch ( SQLException sqlex ) {
                         txtInfo.append( sqlex.toString() );
                    }
                }

                    if (source.equals(btnDelete)) {
                        int DResult = JOptionPane.showConfirmDialog(null,"You want to delete Record?");
                        if (DResult == JOptionPane.NO_OPTION) {
                                txtInfo.append( "\nDeletion Cancled by User!\n" );
                        }        

                        if (DResult == JOptionPane.YES_OPTION)  {

                        try {
                         Statement statement = dbconn.createStatement();
                         if ( ! txtID_Num.getText().equals(""))
                         {


                        JOptionPane.showMessageDialog(null,txtID_Num.getText());
                                     String temp = "DELETE from tbl_sample1 " +
                                     " WHERE ID_Num=" + txtID_Num.getText();
                                   int result = statement.executeUpdate( temp );

                                   if ( result == 1 ) {

                                         JOptionPane.showMessageDialog(null, "Deletion successful" );
                                         txtID_Num.setText( "" );
                                        txtName.setText( "" );
                                        txtAge.setText("");
                                    txtStatus.setText("");
                                    txtGender.setText( "" );
                                    txtAddress.setText("");
                                    txtContact_Num.setText("");
                                    txtEmail.setText("");
                                    txtPlate_Num.setText("");
                                    txtType.setText("");
                                    txtModel.setText("");
                                    }

                                    else  {
                                       txtInfo.append( "\nDeletion failed\n" );
                                       txtID_Num.setText( "" );
                                       txtName.setText( "" );
                                       txtAge.setText("");
                                        txtStatus.setText("");
                                       txtGender.setText( "" );
                                       txtAddress.setText("");
                                        txtContact_Num.setText("");
                                       txtEmail.setText("");
                                        txtPlate_Num.setText("");
                                         txtType.setText("");
                                          txtModel.setText("");

                                    }
                                    statement.close();
                      }

                       else


                        JOptionPane.showMessageDialog(null, "\nNo Information To Be Deleted ");

               } 
          catch ( SQLException sqlex )

             {
                       txtInfo.append( sqlex.toString() );
             }
          }
    }
          if (source.equals(btnClear))
          {
                               txtID_Num.setText( "" );
                               txtName.setText( "" );
                               txtAge.setText("");
                                txtStatus.setText("");
                               txtGender.setText( "" );
                               txtAddress.setText("");
                                txtContact_Num.setText("");
                               txtEmail.setText("");
                                txtPlate_Num.setText("");
                                 txtType.setText("");
                                  txtModel.setText("");
          }

           if (source.equals(btnExit))
           {
                           System.exit(0);
           }
          }


          public void display( ResultSet rs )  {
             try  {
                rs.next();
                int recordNumber = rs.getInt( 1 );
                if ( recordNumber != 0 ) {
                   txtID_Num.setText( String.valueOf(recordNumber) );
                   Name = rs.getString(2).trim();
                   Age= rs.getString(3).trim();
                   Status = rs.getString(4).trim();
                   Gender = rs.getString(5).trim();
                   Address = rs.getString(7).trim();
                   Contact_Num= rs.getString(8).trim();
                   Email = rs.getString(9).trim();
                  Plate_Num= rs.getString(10).trim();
                   Type= rs.getString(11).trim();
                   Model = rs.getString(12).trim();
                   txtName.setText(Name);
                    txtAge.setText(Age);
                    txtStatus.setText(Status);
                   txtGender.setText(Gender);
                   txtAddress.setText(Address);
                   txtContact_Num.setText(Contact_Num);
                   txtEmail.setText(Email);
                   txtPlate_Num.setText(Plate_Num);
                   txtType.setText(Type);
                   txtModel.setText(Model);
                }

                else  {

                        JOptionPane.showMessageDialog(null, "\nNo Record found\n" );
                }
            }

            catch ( SQLException sqlex ) {

                     JOptionPane.showMessageDialog(null, "\n  ID Number Not Found In Database \n" );
            }
         }
   }


public static void main(String args[])
       {
        sample1 app= new sample1();
        app.setSize(500,250);
        app.show();

         app.addWindowListener
              (
                     new WindowAdapter()
                     {
                           public void windowClosing(WindowEvent e)
                           {
                                  System.exit(0);
                           }
                     }
              );

      } 
 }

Edited by Dani: Formatting fixed

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by masijade
0

Yeah write Class(es) that access the DB, then write Class(es) that display data in a Gui, then write Class(es) that act as listeners (and such) as a go between between the two and performs its actions in a separate thread.

This is called (and is only a simple explanation of) the MVC (data Model, View, Control) layered model. Google that.

This mixing of the DB and GUI code is only begging for problems with both the DB access and the gui.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.