Sample code: Saving & Fetching data from a database

This is a small program which saves and fetches data from a database. I used "ucanaccess" and "JDatepicker" to make this program. Please check below for the detail.

Please make sure that you have a MSaccess inside the same folder.

download:
https://www.dropbox.com/s/bue8dhliydibchh/DatebaseTest.zip?dl=0

Code:

import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.border.EmptyBorder;

import org.jdatepicker.impl.JDatePanelImpl;
import org.jdatepicker.impl.JDatePickerImpl;
import org.jdatepicker.impl.UtilDateModel;

import javax.swing.JTextField;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextArea;
import javax.swing.JButton;
import javax.swing.JFormattedTextField.AbstractFormatter;
import javax.swing.AbstractAction;
import java.awt.event.ActionEvent;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;
import java.util.Locale;

import javax.swing.Action;

public class DatebaseTestMain extends JFrame {

 /**
  *
  */
 private static final long serialVersionUID = 1L;
 private JPanel contentPane;
 private JTextField textField_1;
 private JLabel lblCategory;
 private JTextField textField_2;
 private JLabel lblExpenseFor;
 private JTextField textField_3;
 private JLabel lblAmount;
 private JTextField textField_4;
 private JLabel lblReceiptNumber;
 private JButton btnAddInfo;
 private final Action action = new SwingAction();
 private JTextArea textArea;
 private JTextArea txtrDisprset;
 private final Action action_1 = new SwingAction_1();
 private JDatePickerImpl datePicker;
 private JLabel lblNewLabel_1;
 private String pathOfThisProgram;
 

 /**
  * Launch the application.
  */
 public static void main(String[] args) {
  EventQueue.invokeLater(new Runnable() {
   public void run() {
    try {
     DatebaseTestMain frame = new DatebaseTestMain();
     frame.setVisible(true);
    } catch (Exception e) {
     e.printStackTrace();
    }
   }
  });
 }

 /**
  * Create the frame.
  */
 public DatebaseTestMain() {
  setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  setBounds(100, 100, 450, 531);
 
  contentPane = new JPanel();
  contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
  setContentPane(contentPane);
  contentPane.setLayout(null);
 
  JLabel lblNewLabel = new JLabel("Date");
  lblNewLabel.setBounds(12, 24, 50, 13);
  contentPane.add(lblNewLabel);
 
  textField_1 = new JTextField();
  textField_1.setBounds(12, 111, 113, 19);
  contentPane.add(textField_1);
  textField_1.setColumns(10);
 
  lblCategory = new JLabel("Category");
  lblCategory.setBounds(12, 88, 75, 13);
  contentPane.add(lblCategory);
 
  textField_2 = new JTextField();
  textField_2.setBounds(12, 175, 113, 19);
  contentPane.add(textField_2);
  textField_2.setColumns(10);
 
  lblExpenseFor = new JLabel("Expense for");
  lblExpenseFor.setBounds(12, 152, 75, 13);
  contentPane.add(lblExpenseFor);
 
  textField_3 = new JTextField();
  textField_3.setBounds(232, 47, 113, 19);
  contentPane.add(textField_3);
  textField_3.setColumns(10);
 
  lblAmount = new JLabel("Amount");
  lblAmount.setBounds(232, 24, 91, 13);
  contentPane.add(lblAmount);
 
  textField_4 = new JTextField();
  textField_4.setBounds(232, 111, 113, 19);
  contentPane.add(textField_4);
  textField_4.setColumns(10);
 
  lblReceiptNumber = new JLabel("Receipt Number");
  lblReceiptNumber.setBounds(232, 88, 113, 13);
  contentPane.add(lblReceiptNumber);
 
  textArea = new JTextArea();
 
  JScrollPane scrollPane1 = new JScrollPane(textArea);
     scrollPane1.setBounds(242, 173, 178, 67);
     contentPane.add(scrollPane1);
 
  JLabel lblRemarks = new JLabel("Remarks");
  lblRemarks.setBounds(232, 152, 101, 13);
  contentPane.add(lblRemarks);
 
  btnAddInfo = new JButton("Add Info");
  btnAddInfo.setBounds(53, 219, 91, 21);
  btnAddInfo.setAction(action);
  contentPane.add(btnAddInfo);
 
  txtrDisprset = new JTextArea();
  txtrDisprset.setText("");
 
  JScrollPane scrollPane = new JScrollPane(txtrDisprset);
     scrollPane.setBounds(34, 320, 364, 114);
     contentPane.add(scrollPane);
   
     JButton btnFetchData = new JButton("Fetch data");
     btnFetchData.setAction(action_1);
     btnFetchData.setBounds(32, 449, 137, 21);
     contentPane.add(btnFetchData);

     UtilDateModel model = new UtilDateModel();
     //model.setDate(20,04,2014);
     // Need this...
     Properties p = new Properties();
     p.put("text.today", "Today");
     p.put("text.month", "Month");
     p.put("text.year", "Year");
     JDatePanelImpl datePanel = new JDatePanelImpl(model, p);
     // Don't know about the formatter, but there it is...
     datePicker = new JDatePickerImpl(datePanel, new DateLabelFormatter());
     datePicker.setDoubleClickAction(true);
     datePicker.setSize(178, 30);
     datePicker.setLocation(12, 48);
     contentPane.add(datePicker);
     datePicker.setTextEditable(true);
   
     lblNewLabel_1 = new JLabel("...");
     lblNewLabel_1.setBounds(181, 453, 251, 13);
     contentPane.add(lblNewLabel_1);
   
     JLabel lblDisplaytheformat = new JLabel("ID : Expensefor : Category : Amount : Receipt number : Remarks");
     lblDisplaytheformat.setBounds(34, 297, 364, 13);
     contentPane.add(lblDisplaytheformat);
     pathOfThisProgram = getCurrentDir();
 }
 
 public class DateLabelFormatter extends AbstractFormatter {
     private String datePattern = "yyyy-MM-dd";
     private SimpleDateFormat dateFormatter = new SimpleDateFormat(datePattern);

     @Override
     public Object stringToValue(String text) throws ParseException {
         return dateFormatter.parseObject(text);
     }

     @Override
     public String valueToString(Object value) throws ParseException {
         if (value != null) {
             Calendar cal = (Calendar) value;
             return dateFormatter.format(cal.getTime());
         }
         return "";
     }

 }
 
 private class SwingAction extends AbstractAction implements Runnable{
  /**
   *
   */
  private static final long serialVersionUID = 1L;

  public SwingAction() {
   putValue(NAME, "Add Info");
   putValue(SHORT_DESCRIPTION, "Some short description");
  }
 
  public void actionPerformed(ActionEvent e) {
   SwingAction tt = new SwingAction();
   Thread t = new Thread(tt);
   t.start();
  }
 
  public void run(){
   try{
    if(textField_3.getText().equals("") || !datePicker.getModel().isSelected()){
     JOptionPane.showMessageDialog(contentPane, "[Date] and [Amount] are essential.");
     return;
    }
    pathOfThisProgram = getCurrentDir();
    lblNewLabel_1.setText("Saving the data...");
    Connection conn
    = DriverManager.getConnection("jdbc:ucanaccess://"
      + pathOfThisProgram + "\\CompInfo.accdb");
   
    Statement statement = conn.createStatement();
   
    int day = datePicker.getModel().getDay();
    int month = datePicker.getModel().getMonth() + 1;
    //month-number starts from 0. So I must add 1 to the month number to make it precise.
    int year = datePicker.getModel().getYear();
   
    String str = year + "-" + month + "-" + day  + " 00:00:00";
   
    statement.executeUpdate("INSERT INTO ExpenseTable (Date1, Category, Expensefor, Amount, ReceiptNum, Remarks)"
      + " VALUES "
      + "('" + str + "',"
       + "'" + textField_1.getText() + "',"
          + "'" + textField_2.getText() + "',"
       + "'" + textField_3.getText() + "',"
       + "'" + textField_4.getText() + "',"
       + "'" + textArea.getText() + "');");
   
    statement.close();
    conn.close();
    lblNewLabel_1.setText("Succefully finished.");
   }catch(SQLException ex){
    System.err.print(ex);
    JOptionPane.showMessageDialog(contentPane, "Error happened. Please make sure this program and the datebase are inside the same folder.");
   }
 
  }
 }
 
 public static String getCurrentDir() {
        String path = new File(".").getAbsoluteFile().getParent();
    return path;
 }
 
 private class SwingAction_1 extends AbstractAction implements Runnable{
  /**
   *
   */
  private static final long serialVersionUID = 1L;

  public SwingAction_1() {
   putValue(NAME, "Show me the data");
   putValue(SHORT_DESCRIPTION, "Some short description");
  }
  public void actionPerformed(ActionEvent e) {
   txtrDisprset.setText("");
   SwingAction_1 tt1 = new SwingAction_1();
   Thread t1 = new Thread(tt1);
   t1.start();
  }
 
  public void run(){
   try{
   pathOfThisProgram = getCurrentDir();
   lblNewLabel_1.setText("Fetching the data...");
   Connection conn = DriverManager.getConnection("jdbc:ucanaccess://"
     + pathOfThisProgram + "\\CompInfo.accdb");
   Statement s = conn.createStatement();
   ResultSet rs = s.executeQuery("SELECT ExpenseTable.* FROM ExpenseTable ORDER BY ID;"); //Query

   ArrayList<Integer> ID = new ArrayList<Integer>();
   ArrayList<String> Date1 = new ArrayList<String>();
   ArrayList<String> Expensefor = new ArrayList<String>();
   ArrayList<String> Category = new ArrayList<String>();
   ArrayList<String> Amount = new ArrayList<String>();
   ArrayList<String> ReceiptNum = new ArrayList<String>();
   ArrayList<String> Remarks = new ArrayList<String>();
   
   SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy/MM/dd", Locale.JAPAN);

   int count = 0;
    while (rs.next()) {
     ID.add(rs.getInt(1));
     Date1.add(sdf1.format(rs.getDate(2))); //Fetch data from the DB. Column-number starts from 1.
     Expensefor.add(rs.getString(3) + " ");
     //Column number depends on the order of creating. Changing the column-location doesn't affect the column-number.  
     Category.add(rs.getString(4) + " ");
     Amount.add(getWage(rs.getString(5)) + " ");
     ReceiptNum.add(rs.getString(6));
     Remarks.add(rs.getString(7));
     
     count++;  //Count how many times it is repeated
    }
   
    for(int i=0; i < count; i++){
     txtrDisprset.append(ID.get(i) + " : ");
     txtrDisprset.append(Date1.get(i) + " : ");
     txtrDisprset.append(Expensefor.get(i) + " : ");
     txtrDisprset.append(Category.get(i) + " : ");
     txtrDisprset.append(Amount.get(i)  + "dollar" + " : ");
     txtrDisprset.append(ReceiptNum.get(i) + " : ");
     txtrDisprset.append(Remarks.get(i) + "\n");
    }
   lblNewLabel_1.setText("Successfully finished.");
   }catch( SQLException e ){
    System.err.print(e);
    lblNewLabel_1.setText("Error happened. Please make sure this program and the datebase are inside the same folder.");
   }
  }
 }
 private static String getWage(String n){
       int point = n.lastIndexOf(".");
       if (point != -1) {
           return n.substring(0, point);
       }
       return n;
 }
}