| |
Most transactions are best viewed and edited in JTables. Think of a JTable as a Spreadsheet Page. We use HEADLINE Scripting with lowercase prefixes for most objects. We also use acronyms for field names (AccountNumber=ACTNBR). Please pardon the formatting from the HTML editor. Indentations were lost, and it selected some funky fonts and colors.
Down Load of the Access Database File
Copyrighted by CompuCranks.com (2006)
//**************************************************************************
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.border.*;
import javax.swing.table.*;
import javax.swing.event.*;
import java.io.*;
import java.util.*;
import java.sql.*;
//**************************************************************************
class guiDATA_TABLE_EDITOR extends JFrame{
//**************************************************************************
private JPanel panel;
private String[] colDATA = {"DATE","ACCOUNT NUMBER","DESCRIPTION","AMOUNT"};
private DefaultTableModel dtmDATA;
private JTable tblDATA;
private JScrollPane sbrDATA;
private JButton btnOK;
private JButton btnCANCEL;
private Insets insets;
private String wrkCONSTR="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" +
"C:\\AccessTable.mdb";
//--------------------------------------------------------------------------
public guiDATA_TABLE_EDITOR(){ //Define GUI Controls & Properties
//--------------------------------------------------------------------------
setTitle("JAVA Data Table Editor");
setBounds(250,150,655,450);
setResizable(false);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
panel = new JPanel();
panel.setLayout(null);
insets = panel.getInsets();
this.add(panel);
dtmDATA = new DefaultTableModel(20,6);
tblDATA = new JTable(dtmDATA);
TableColumn c = null;
c = tblDATA.getColumnModel().getColumn(0);
c.setPreferredWidth(100);
c.setHeaderValue("Date");
c = tblDATA.getColumnModel().getColumn(1);
c.setPreferredWidth(100);
c.setHeaderValue("Account");
c = tblDATA.getColumnModel().getColumn(2);
c.setPreferredWidth(300);
c.setHeaderValue("Account Name");
//c.isEditable(false);
c = tblDATA.getColumnModel().getColumn(3);
c.setPreferredWidth(400);
c.setHeaderValue("Description");
c = tblDATA.getColumnModel().getColumn(4);
c.setPreferredWidth(150);
c.setHeaderValue("Amount");
c = tblDATA.getColumnModel().getColumn(5);
c.setPreferredWidth(30);
c.setHeaderValue("Cnt");
tblDATA.setBackground(new Color(180,180,255));
tblDATA.setBorder(BorderFactory.createLineBorder(Color.BLACK));
sbrDATA = new JScrollPane(tblDATA,ScrollPaneConstants.VERTICAL_SCROLLBAR_ALWAYS,ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER);
sbrDATA.setBounds(insets.left + 10,insets.top + 10,630,360);
panel.add(sbrDATA);
buildTable();
btnOK = new JButton("OK");
btnOK.setBounds(insets.left + 150,insets.top + 375,100,35);
btnOK.setBorder(BorderFactory.createRaisedBevelBorder());
btnOK.addActionListener(new ButtonHandler());
panel.add(btnOK);
btnCANCEL = new JButton("Cancel");
btnCANCEL.setBounds(insets.left + 400,insets.top + 375,100,35);
btnCANCEL.setBorder(BorderFactory.createRaisedBevelBorder());
btnCANCEL.addActionListener(new ButtonHandler());
panel.add(btnCANCEL);
}
//-----------------------------------------------------------------------------
private void buildTable(){ //Populate table rows with recordset fields.
//-----------------------------------------------------------------------------
String wrkSQL="SQL "; int R=0;
try{
//Set up database Conection.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection(wrkCONSTR);
wrkSQL="Select * from CSC_TRANSACTION, CSC_MASTER where xxmACTNBR=xxtACTNBR Order By xxtTRNDTE DESC, xxtACTNBR, xxtTRNCNT";
//Set up database SQL stament & recordset string.
Statement s = con.createStatement();
s.execute(wrkSQL);
ResultSet rs = s.getResultSet();
//Populate Table
if (rs != null) {
while (rs.next()){
if (R>=tblDATA.getRowCount()) {
dtmDATA.addRow(new Object[] {"","","",""});
}
dtmDATA.setValueAt(rs.getString("xxtTRNDTE"),R,0);
dtmDATA.setValueAt(rs.getString("xxtACTNBR"),R,1);
dtmDATA.setValueAt(rs.getString("xxmACTNME"),R,2);
dtmDATA.setValueAt(rs.getString("xxtDESCPT"),R,3);
dtmDATA.setValueAt(rs.getString("xxtTRNAMT"),R,4);
dtmDATA.setValueAt(rs.getString("xxtTRNCNT"),R,5);
R=R+1;
}
}
s.close(); con.close();
//Populate empty rows for adding records.
if (R >= (tblDATA.getRowCount()-10)) {
for (int x=0; x<10; x++){
dtmDATA.addRow(new Object[] {"","","",""});
}
}
}
catch (Exception err){
System.out.println("BUILD ERROR: " + err);
}
}
//-----------------------------------------------------------------------------
private class ButtonHandler implements ActionListener{ //Button Event.
//-----------------------------------------------------------------------------
public void actionPerformed(ActionEvent e) {
JButton b = (JButton)e.getSource();
if(b==btnOK) {
audTableData();
//dispose();
} else if (b==btnCANCEL) {
dispose();
}
}
}
//-----------------------------------------------------------------------------
private void audTableData(){ //Table Row to Database add, update, or delete.
//-----------------------------------------------------------------------------
double wrkAMT=0; int wrkCNT=0;
for (int R=0; R<tblDATA.getRowCount(); R++) {
try{wrkAMT=Double.parseDouble(dtmDATA.getValueAt(R,4).toString());}
catch(Exception err){wrkAMT=0;}
try{wrkCNT=Integer.parseInt(dtmDATA.getValueAt(R,5).toString());}
catch(Exception err){wrkCNT=0;}
if (wrkAMT!=0 && wrkCNT==0) {
addData(R); System.out.println("ADDED" + R);
} else if (wrkAMT!=0 && wrkCNT!=0) {
updateData(R); System.out.println("UPDATED" + R);
} else if (wrkAMT==0 && wrkCNT!=0) {
deleteData(R); System.out.println("DELETED" + R);
} else {
System.out.println("MISSED" + R);
}
}
}
//-----------------------------------------------------------------------------
private void addData(int R){ //Database Insert or Add.
//-----------------------------------------------------------------------------
String wrkSQL="SQL "; int wrkTRNCNT = getTransCount(R);
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(wrkCONSTR);
wrkSQL = "Insert Into CSC_TRANSACTION(" +
"xxtACTNBR," +
"xxtTRNDTE," +
"xxtTRNCNT," +
"xxtDESCPT," +
"xxtTRNAMT" +
") VALUES(" +
dtmDATA.getValueAt(R,1).toString() + "," +
dtmDATA.getValueAt(R,0).toString() + "," +
wrkTRNCNT + ",'" +
dtmDATA.getValueAt(R,3).toString() + "'," +
dtmDATA.getValueAt(R,4).toString() + ")";
Statement s = con.createStatement();
s.execute(wrkSQL);
s.close(); con.close();
}
catch (Exception err){
System.out.println("ADD ERROR: " + wrkSQL + err);
}
}
//-----------------------------------------------------------------------------
private void updateData(int R){ //Database Update.
//-----------------------------------------------------------------------------
String wrkSQL="SQL ";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(wrkCONSTR);
wrkSQL = "Update CSC_TRANSACTION Set " +
"xxtDESCPT='" + dtmDATA.getValueAt(R,3).toString() + "'," +
"xxtTRNAMT=" + dtmDATA.getValueAt(R,4).toString() + " " +
"Where xxtACTNBR=" + dtmDATA.getValueAt(R,1).toString() + " and xxtTRNDTE=" +
dtmDATA.getValueAt(R,0).toString() + " and xxtTRNCNT=" + dtmDATA.getValueAt(R,5).toString();
//System.out.println(wrkSQL);
Statement s = con.createStatement();
s.execute(wrkSQL);
s.close(); con.close();
}
catch (Exception err){
System.out.println("UPDATE ERROR: " + wrkSQL + err);
}
}
//-----------------------------------------------------------------------------
private void deleteData(int R){ //Database Delete.
//-----------------------------------------------------------------------------
String wrkSQL="SQL ";
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(wrkCONSTR);
wrkSQL = "Delete From CSC_TRANSACTION "+
"Where xxtACTNBR=" + dtmDATA.getValueAt(R,1).toString() + " and xxtTRNDTE=" +
dtmDATA.getValueAt(R,0).toString() + " and xxtTRNCNT=" + dtmDATA.getValueAt(R,5).toString();
Statement s = con.createStatement();
s.execute(wrkSQL) ;
s.close(); con.close();
}
catch (Exception err){
System.out.println("DELETE ERROR: " + wrkSQL + err);
}
}
//-----------------------------------------------------------------------------
private int getTransCount(int R) { //Retrieve new count number for add records.
//-----------------------------------------------------------------------------
String wrkSQL="SQL "; int wrkTRNCNT = 1;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(wrkCONSTR);
wrkSQL="Select max(xxtTRNCNT) as maxTRNCNT From CSC_TRANSACTION "+
"Where xxtACTNBR=" + dtmDATA.getValueAt(R,1).toString() + " and xxtTRNDTE=" + dtmDATA.getValueAt(R,0).toString();
Statement s = con.createStatement();
s.execute(wrkSQL);
ResultSet rs = s.getResultSet();
if (rs != null) {
rs.next();
wrkTRNCNT = 1 + rs.getInt("maxTRNCNT");
}
s.close(); con.close();
}
catch (Exception err){
System.out.println("COUNT ERROR: " + wrkSQL + err);
}
return wrkTRNCNT;
}
//--------------------------------------------------------------------------
public static void main(String[] args) { //Startup program
//--------------------------------------------------------------------------
guiDATA_TABLE_EDITOR gui = new guiDATA_TABLE_EDITOR();
gui.setVisible(true);
}
}
|
