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);
}
}
);
}
}