10
Lesson5
Tools:
Target:
This
lesson will build an application that manages data tables Customer,
Computers and Reserve from the database MyCustomers with the
technique of Master / Detail.
Customer and Computers tables are
Master while Detail table is Reserve table.
Also performs research on Reserve table
according to field dateReserve through the activation of a filter.
The application performs operations Edit, Cancellation and Inserting
records in all tables .
In file
NewJFrame.java add this import:
import com.sun.rowset.FilteredRowSetImpl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
import javax.sql.rowset.FilteredRowSet;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
|
In
file NewJframe.java add at end this declarations:
private String url = "jdbc:mysql://localhost:3306/MyCustomers";
private Connection con;
public static FilteredRowSet frsUtenti;
public static FilteredRowSet frsComputers;
public static FilteredRowSet frsOrdini;
private FiltroData fil;
private NewTabella TabMia=new NewTabella();
private RowSetListener rslUtenti=new RowSetListener(){
public void rowSetChanged(RowSetEvent event) {
frsUtenti.removeRowSetListener(this);
codice.removeAllItems();
try { frsUtenti.beforeFirst();
while (frsUtenti.next()){
codice.addItem(frsUtenti.getString("code"));
}
sbUtenti.setValues(1,1,1,frsUtenti.size()+1);
sbUtenti.setBlockIncrement(5);
frsUtenti.first();
aggiornaMascheraUtenti();
} catch (SQLException ex) {
ex.printStackTrace();
}
frsUtenti.addRowSetListener(this);
}
public void rowChanged(RowSetEvent event) {
}
public void cursorMoved(RowSetEvent event) {
}
};
private RowSetListener rslComputers=new RowSetListener(){
public void rowSetChanged(RowSetEvent event) {
frsComputers.removeRowSetListener(this);
codArticolo.removeAllItems();
try { frsComputers.beforeFirst();
while (frsComputers.next()){
codArticolo.addItem(frsComputers.getString("codArticle"));
}
sbComputers.setValues(1,1,1,frsComputers.size()+1);
sbComputers.setBlockIncrement(5);
frsComputers.first();
aggiornaMascheraComputers();
} catch (SQLException ex) {
ex.printStackTrace();
}
frsComputers.addRowSetListener(this);
}
public void rowChanged(RowSetEvent event) {
}
public void cursorMoved(RowSetEvent event) {
}
};
|
Set for variable jScrollPane1
modifiers public static :
In file
NewJFrame.java replacement constructor:
public NewJFrame() {
initComponents();
}
|
with this constructor:
public class NewJFrame extends javax.swing.JFrame {
/** Creates new form NewJFrame */
public NewJFrame() {
initComponents();
setDefaultLookAndFeelDecorated(true);
try {
UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
SwingUtilities.updateComponentTreeUI(this);
this.pack();
} catch (UnsupportedLookAndFeelException ex) {
ex.printStackTrace();
} catch (InstantiationException ex) {
ex.printStackTrace();
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
} catch (IllegalAccessException ex) {
ex.printStackTrace();
}
try {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection(url, "root", "root");
con.setAutoCommit(false);
frsUtenti=new FilteredRowSetImpl();
frsUtenti.addRowSetListener(rslUtenti);
frsUtenti.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer");
frsUtenti.execute(con);
frsComputers=new FilteredRowSetImpl();
frsComputers.addRowSetListener(rslComputers);
frsComputers.setCommand("select * from Computers");
frsComputers.execute(con);
frsOrdini=new FilteredRowSetImpl();
frsOrdini.addRowSetListener(TabMia);
aggiornaTabella();
}catch (SQLException ex) { ex.printStackTrace(); }
catch (ClassNotFoundException ex) {ex.printStackTrace();}
}
|
Programming Beans:
1:
Swing: JComboBox NameVariable:
codice Code for Event: ActionPerformed
try {
int selezione;
if (codice.getItemCount()>0) {
if (codice.getSelectedIndex()!=-1) {
selezione=codice.getSelectedIndex()+1;
frsUtenti.absolute(selezione);
numRecUtenti.setText(Integer.toString(selezione)+
"/"+(sbUtenti.getMaximum()-1));
sbUtenti.setValue(selezione);
aggiornaTabella();
} else jOptionPane1.showMessageDialog(null,"Errore:codice
inesistente","Errore",jOptionPane1.ERROR_MESSAGE);
}
} catch (SQLException ex) {ex.printStackTrace(); }
|
2:
Swing: JTextField NameVariable: cognome
3:
Swing: JTextField NameVariable: nome
4:
Swing: JTextField NameVariable: data
5:
Swing: JTextField NameVariable: cap
6:
Swing: JTextField NameVariable: saldo
7:
Swing: JComboBox NameVariable:
codArticolo Code for Event: ActionPerformed
try {
int selezione;
if (codArticolo.getItemCount()>0) {
if (codArticolo.getSelectedIndex()!=-1) {
selezione=codArticolo.getSelectedIndex()+1;
frsComputers.absolute(selezione);
numRecComputers.setText(Integer.toString(selezione)+
"/"+(sbComputers.getMaximum()-1));
sbComputers.setValue(selezione);
aggiornaTabella();
} else jOptionPane1.showMessageDialog(null,
"Errore:codice inesistente","Errore",jOptionPane1.ERROR_MESSAGE);
}
} catch (SQLException ex) {ex.printStackTrace(); }
|
8:
Swing: JTextField NameVariable: descrizione
9:
Swing: JTextField NameVariable: prezzo
10:
Swing: JLabel NameVariable: numRecComputers
11:
Swing: JLabel NameVariable: numRecUtenti
12:
Swing: JButton NameVariable:
aggiungiUtenti Code for Event: ActionPerformed
try {
frsUtenti.setCommand("INSERT INTO Customer(code,firstname,name,date,zip,balance) values('ABB9',' ',' ','2007-01-01','12340',0)");
frsUtenti.execute(con);
frsUtenti.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer");
frsUtenti.execute(con);
frsUtenti.last();
//aggiornaMascheraUtenti();
sbUtenti.setValue(frsUtenti.getRow());
} catch (SQLException ex) { ex.printStackTrace(); }
|
13:
Swing: JButton NameVariable:
cancellaUtenti Code for Event: ActionPerformed
try {
int risp=jOptionPane1.showConfirmDialog(this,"Attention delete reserve!","Confirm",jOptionPane1.YES_NO_OPTION,jOptionPane1.INFORMATION_MESSAGE);
if (risp==jOptionPane1.YES_OPTION){
frsUtenti.absolute(codArticolo.getSelectedIndex()+1);
frsUtenti.deleteRow();
frsUtenti.acceptChanges(con);
frsUtenti.rowSetPopulated(new RowSetEvent(frsUtenti),1);
}
} catch (SQLException ex) { ex.printStackTrace(); }
|
14:
Swing: JButton NameVariable:
modUtenti Code for Event: ActionPerformed
try {
/* ATTENTION: CHECK DATA for ode,firstname,name,data,zip,balance */
int curRec=frsUtenti.getRow();
frsUtenti.updateString("code",(String)codice.getSelectedItem());
frsUtenti.updateString("firstname",cognome.getText());
frsUtenti.updateString("name",nome.getText());
frsUtenti.updateDate("date",Date.valueOf(data.getText()));
frsUtenti.updateString("zip",cap.getText());
frsUtenti.updateFloat("balance",Float.parseFloat(saldo.getText()));
frsUtenti.updateRow();
frsUtenti.acceptChanges(con);
frsUtenti.absolute(curRec);
//aggiornaMascheraUtenti();
sbUtenti.setValue(curRec);
} catch (SQLException ex) { ex.printStackTrace(); }
|
15:
Swing: JButton NameVariable:
aggiungiComp Code for Event: ActionPerformed
try {
frsComputers.setCommand("INSERT INTO Computers(codArticle,description,price) values(?,?,?)");
frsComputers.setString(1,"ZZZ00");
frsComputers.setString(2," ");
frsComputers.setFloat(3,0.0f);
frsComputers.execute(con);
frsComputers.setCommand("select * from Computers");
frsComputers.execute(con);
frsComputers.last();
//aggiornaMascheraComputers();
sbComputers.setValue(frsComputers.getRow());
} catch (SQLException ex) { ex.printStackTrace(); }
|
16:
Swing: JButton NameVariable:
cancellaComp Code for Event: ActionPerformed
try {
int risp=jOptionPane1.showConfirmDialog(this,"Attention delete reserve!","Confirm",jOptionPane1.YES_NO_OPTION,jOptionPane1.INFORMATION_MESSAGE);
if (risp==jOptionPane1.YES_OPTION){
frsComputers.absolute(codice.getSelectedIndex()+1);
frsComputers.deleteRow();
frsComputers.acceptChanges(con);
frsComputers.rowSetPopulated(new RowSetEvent(frsComputers),1);
}
} catch (SQLException ex) { ex.printStackTrace(); }
|
17:
Swing: JButton NameVariable:
modificaComp Code for Event: ActionPerformed
try {
/* ATTENTION: CHECK DATA for codArticle,description,price */
int curRec=frsComputers.getRow();
frsComputers.updateString("codArticle",(String)codArticolo.getSelectedItem());
frsComputers.updateString("description",descrizione.getText());
frsComputers.updateFloat("price",Float.parseFloat(prezzo.getText()));
frsComputers.updateRow();
frsComputers.acceptChanges(con);
frsComputers.absolute(curRec);
//aggiornaMascheraComputers();
sbComputers.setValue(curRec);
} catch (SQLException ex) { ex.printStackTrace(); }
|
18:
Swing: JRadioButton NameVariable:
codiceRd
19:
Swing: JRadioButton NameVariable:
articoloRd
20:
Swing: JRadioButton NameVariable:
entrambiRd
21:
Swing: JRadioButton NameVariable: dataOn
Code for Event: ActionPerformed
scegliData.setEnabled(true);
|
22:
Swing: JRadioButton NameVariable:
dataOff Code for Event: ActionPerformed
scegliData.setEnabled(false);
fil=null;
try {
frsOrdini.setFilter(null);
frsOrdini.execute(con);
} catch (SQLException ex) { ex.printStackTrace(); }
|
23:
Swing: JTable NameVariable: jTable1
24:
Swing: JScrollBar NameVariable:
sbComputers Code for Event: AdjustmentValueChanged
try {
frsComputers.absolute(evt.getValue());
numRecComputers.setText(Integer.toString(evt.getValue())+ "/"+(sbComputers.getMaximum()-1));
aggiornaMascheraComputers();
} catch (SQLException ex) { ex.printStackTrace(); }
|
25:
Swing: JScrollBar NameVariable:
sbUtenti Code for Event: AdjustmentValueChanged
try {
frsUtenti.absolute(evt.getValue());
numRecUtenti.setText(Integer.toString(evt.getValue())
+"/"+(sbUtenti.getMaximum()-1));
aggiornaMascheraUtenti();
} catch (SQLException ex) { ex.printStackTrace(); }
|
26:
Swing: JButton NameVariable:
modOrdini Code for Event: ActionPerformed
try {
/* ATTENTION: CHECK DATA for codCust,codArticle,dateReserve,riduction,transportExp */
frsOrdini.absolute(TabMia.getSelectedRow()+1);
frsOrdini.updateString("codCust",(String)TabMia.getValueAt(TabMia.getSelectedRow(),0));
frsOrdini.updateString("codArticle",(String)TabMia.getValueAt(TabMia.getSelectedRow(),1));
Date d=Date.valueOf((TabMia.getValueAt(TabMia.getSelectedRow(),2).toString()));
frsOrdini.updateDate("dateReserve",d);
frsOrdini.updateFloat("riduction",(Float)TabMia.getValueAt(TabMia.getSelectedRow(),3));
frsOrdini.updateFloat("transportExp",(Float)TabMia.getValueAt(TabMia.getSelectedRow(),4));
frsOrdini.setFilter(null);
frsOrdini.updateRow();
frsOrdini.acceptChanges(con);
if (fil!=null) {
frsOrdini.setFilter(fil);
frsOrdini.execute(con);
}
aggiornaTabella();
} catch (SQLException ex) {
ex.printStackTrace();
}
|
27:
Swing: JButton NameVariable:
cancellaOrdini Code for Event: ActionPerformed
try {
frsOrdini.absolute(TabMia.getSelectedRow()+1);
frsOrdini.setFilter(null);
frsOrdini.deleteRow();
frsOrdini.acceptChanges(con);
if (fil!=null) {
frsOrdini.setFilter(fil);
frsOrdini.execute(con);
}
frsOrdini.rowSetPopulated(new RowSetEvent(frsOrdini),1);
} catch (SQLException ex) { ex.printStackTrace(); }
|
28:
Swing: JButton NameVariable:
aggiungiOrdini Code for Event: ActionPerformed
try {
frsOrdini.setCommand("INSERT INTO Reserve(codCust,codArticle,dateReserve,riduction,transportExp) values(?,?,'2000-01-01',0,0)");
frsOrdini.setString(1,(String)codice.getSelectedItem());
frsOrdini.setString(2,(String)codArticolo.getSelectedItem());
frsOrdini.execute(con);
aggiornaTabella();
} catch (SQLException ex) {ex.printStackTrace(); }
|
29:
Swing: JButton NameVariable:
scegliData Code for Event: ActionPerformed
try {
String leggiData=jOptionPane1.showInputDialog(this,"Input date","Search",jOptionPane1.QUESTION_MESSAGE);
if (leggiData.length()!=0){
Date data=Date.valueOf(leggiData);
fil=new FiltroData(data);
frsOrdini.beforeFirst();
frsOrdini.setFilter(fil);
}else frsOrdini.setFilter(null);
aggiornaTabella();
} catch (SQLException ex) {ex.printStackTrace();}
|
Insert in the file NewFrame.java below the
main method the following code that implements the interface
aggiornaMascheraUtenti(),aggiornaMascheraComputers() e
aggiornaTabella() :
public void aggiornaMascheraUtenti(){
try {
if (codice.getItemCount()>0){
codice.setSelectedItem(frsUtenti.getString("code"));
cognome.setText(frsUtenti.getString("firstname"));
nome.setText(frsUtenti.getString("name"));
data.setText(frsUtenti.getDate("date").toString());
cap.setText(frsUtenti.getString("zip"));
saldo.setText(frsUtenti.getString("balance").toString());
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
public void aggiornaMascheraComputers(){
try {
if (codArticolo.getItemCount()>0){
codArticolo.setSelectedItem(frsComputers.getString("codArticle"));
descrizione.setText(frsComputers.getString("description"));
prezzo.setText(Float.toString(frsComputers.getFloat("price")));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
public void aggiornaTabella(){
try {
if (frsOrdini!=null){
if (codiceRd.getModel().isSelected() && codice.getItemCount()>0){
frsOrdini.setCommand("select * from Reserve where (codCust=?)");
frsOrdini.setString(1,(String)codice.getSelectedItem());
frsOrdini.execute(con);
}
if (articoloRd.getModel().isSelected() && codArticolo.getItemCount()>0) {
frsOrdini.setCommand("select * from Reserve where (codArticle=?)");
frsOrdini.setString(1,(String)codArticolo.getSelectedItem());
frsOrdini.execute(con);
}
if (entrambiRd.getModel().isSelected() && codice.getItemCount()>0 && codArticolo.getItemCount()>0) {
frsOrdini.setCommand("select * from Reserve where (codCust=? And codArticle=?)");
frsOrdini.setString(1,(String)codice.getSelectedItem());
frsOrdini.setString(2,(String)codArticolo.getSelectedItem());
frsOrdini.execute(con);
}
}
} catch (SQLException ex) { ex.printStackTrace(); }
}
|
Inspector:
Add to project a new class
java named FiltroData:
Add
to project a new class java named FiltroData as in the Lesson1.
In file FiltroData.java add this import:
import java.sql.Date;
import java.sql.SQLException;
import javax.sql.RowSet;
import javax.sql.rowset.Predicate;
|
In file FiltroData.java
replacement code:
public class FiltroData{
/** Creates a new instance of Filtro */
public FiltroData() {
}
}
|
with this code:
public class FiltroData implements Predicate{
Date dataSeek;
/** Creates a new instance of FiltroData */
public FiltroData(Date data) {
this.dataSeek=data;
}
public boolean evaluate(RowSet rs) {
boolean valutata=false;
try {
if (rs.getRow()>0) valutata=dataSeek.equals(rs.getDate("dateReserve"));
} catch (SQLException ex) {
ex.printStackTrace();
return false;
}
return valutata;
}
public boolean evaluate(Object value, int column) throws SQLException {
return true;
}
public boolean evaluate(Object value, String columnName) throws SQLException {
return true;
}
|
Add
to project a new class java named NewTabella as in Lesson1
In file NewTabella.java
add this import:
import java.sql.SQLException;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
|
In file NewTabella.java replacement code:
public class NewTabella{
/**
* Creates a new instance of NewTabella
*/
public NewTabella() {
}
}
|
with this code:
public class NewTabella extends JTable implements RowSetListener{
/** Creates a new instance of NewTabella */
public NewTabella() {
}
public void rowSetChanged(RowSetEvent event) {
Object[] record;
String [] intestazCol=new String [] {"codCust", "codArticle", "dateReserve", "riduction", "transportExp"};
DefaultTableModel dtm=new DefaultTableModel(intestazCol,0){
Class[] types = new Class [] {
java.lang.String.class, java.lang.String.class, java.lang.Object.class, java.lang.Float.class, java.lang.Float.class};
public Class getColumnClass(int columnIndex) {
return types [columnIndex];
}
};
try {
NewJFrame.frsOrdini.beforeFirst();
while (NewJFrame.frsOrdini.next()){
record=new Object[]{NewJFrame.frsOrdini.getString("codCust"), NewJFrame.frsOrdini.getString("codArticle"),
NewJFrame.frsOrdini.getDate("dateReserve"),NewJFrame.frsOrdini.getFloat("riduction"),
NewJFrame.frsOrdini.getFloat("transportExp")};
dtm.addRow(record);
}
} catch (SQLException ex) {ex.printStackTrace();}
this.setModel(dtm);
NewJFrame.jScrollPane1.setViewportView(this);
}
public void rowChanged(RowSetEvent event) {
}
public void cursorMoved(RowSetEvent event) {
}
}
|
Compile.
Thank you for attention!
|
|
