Java-SQL: Form Master-Detil

Contoh Lanjutan dari form java dengan koneksi database sql server 2000. Form ini memeberikan contoh untuk form dengan bentuk Master-Detil, yaitu hasil form disimpan pada dua jenis tabel, tabel master untuk header transaksi dan tabel detil untuk menyimpan detil dari transaksi, contohnya pada struk penjualan dengan satu header struk tapi mempunyai banyak data detil barang yang dijual. Pada sistem ini mencontohkan form input kartu studi tanpa normalisasi relasi tabel yang ada untuk memudahkan pemahaman dasar formnya saja. Form dibuat dengan NetBeans 3.6 tanpa menggunakan class tambahan sehingga bisa langsung dicoba di sistem dengan terinstalasi JDK.
Konsep dasar penggunaan form:
- penekanan tombol enter pada NIM akan mencari data mahasiswa, apabila sudah ada ditampilkan dan bisa diedit, apabila belum ada bisa langsung ditambahkan
- berpindah antar komponen dengan tombol Enter
- pemilihan semua isi textfield untuk memudahkan pengeditan
- Pada JTable:
+ kolom KodeJadwal dan Kelas bisa diedit
+ kolom MataKuliah dan Dosen diambil dari tabel tJadwal dengan dasar KodeJadwal
+ KodeJadwal yang dimasukkan harus sudah terdaftar pada tJadwal
+ [INSERT] untuk menambah baris baru
+ [DELETE] untuk menghapus baris dengan kursor

Langkah praktek:
- Atur database
Database Name: dbKuliah
Table Name: tMhs, tJadwal, tKardi
Buat tabel dengan konsep relasi berikut:

- Pilih jenis koneksi ke database, lihat contoh sebelumnya
+ dengan mssqldriver, tentukan host, database, username, password
+ dengan datasource, buat dengan nama koneksi
- Buat Jframe,
+ JPanel: jPanel1 (null Layout, tempat semua JLabel, JTextField, dan jScrollPane1),jPanel2 (GridLayout, tempat semua JButton)
+ JScrollPane: jScrollPane1, tempat tabel
+ JLabel: jLabel1,jLabel2,jLabel3,jLabel4
+ JTextField: tKode,tNama,tJurusan,tAngkatan
+ JTable: tabel
+ JButton: bSimpan,bBatal,bHapus

- Ubah kodenya
1. Import
2. Constructor
3. Fungsi Tambahan
3.a. kosongkan
3.b. testDriver
3.c. updateTabel
3.d. tambahBaris
3.e. hapusBaris
3.f. simpanDetil
4. Variabel global
5. Event
5.a. tabelKeyReleased
5.b. tabelKeyPressed
5.c. bHapusActionPerformed
5.d. bSimpanKeyPressed
5.e. tAngkatanFocusGained,tJurusanFocusGained,tNamaFocusGained,tKodeFocusGained
5.f. bBatalActionPerformed
5.g. tAngkatanActionPerformed,tJurusanActionPerformed,tNamaActionPerformed
5.h. tKodeActionPerformed

//KODE LENGKAP:
//1
import javax.swing.*;
import java.sql.*;
import java.text.*;
import javax.swing.table.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.event.*;
public class frmKardi extends javax.swing.JFrame {

//2
public frmKardi() {
initComponents();
testDriver();
kosongkan();
tKode.setText("");
updateTabel();
}
private void initComponents() {
jPanel1 = new javax.swing.JPanel();
jLabel1 = new javax.swing.JLabel();
jLabel2 = new javax.swing.JLabel();
jLabel3 = new javax.swing.JLabel();
jLabel4 = new javax.swing.JLabel();
tKode = new javax.swing.JTextField();
tNama = new javax.swing.JTextField();
tJurusan = new javax.swing.JTextField();
tAngkatan = new javax.swing.JTextField();
jScrollPane1 = new javax.swing.JScrollPane();
tabel = new javax.swing.JTable();
jPanel2 = new javax.swing.JPanel();
bSimpan = new javax.swing.JButton();
bHapus = new javax.swing.JButton();
bBatal = new javax.swing.JButton();

setTitle("Form Dosen");
addWindowListener(new java.awt.event.WindowAdapter() {
public void windowClosing(java.awt.event.WindowEvent evt) {
exitForm(evt);
}
});

jPanel1.setLayout(null);

jPanel1.setBackground(new java.awt.Color(0, 204, 204));
jLabel1.setFont(new java.awt.Font("Arial", 1, 12));
jLabel1.setText("Kode");
jPanel1.add(jLabel1);
jLabel1.setBounds(50, 50, 60, 30);

jLabel2.setFont(new java.awt.Font("Arial", 1, 12));
jLabel2.setText("Nama");
jPanel1.add(jLabel2);
jLabel2.setBounds(50, 90, 60, 30);

jLabel3.setFont(new java.awt.Font("Arial", 1, 12));
jLabel3.setText("Jurusan");
jPanel1.add(jLabel3);
jLabel3.setBounds(50, 130, 60, 30);

jLabel4.setFont(new java.awt.Font("Arial", 1, 12));
jLabel4.setText("Angkatan");
jPanel1.add(jLabel4);
jLabel4.setBounds(50, 170, 60, 30);

tKode.setBackground(new java.awt.Color(204, 255, 204));
tKode.setFont(new java.awt.Font("Tahoma", 1, 12));
tKode.setText("tKode");
tKode.setBorder(new javax.swing.border.BevelBorder(javax.swing.border.BevelBorder.RAISED));
tKode.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
tKodeActionPerformed(evt);
}
});
tKode.addFocusListener(new java.awt.event.FocusAdapter() {
public void focusGained(java.awt.event.FocusEvent evt) {
tKodeFocusGained(evt);
}
});

jPanel1.add(tKode);
tKode.setBounds(120, 50, 110, 30);

tNama.setBackground(new java.awt.Color(204, 255, 204));
tNama.setFont(new java.awt.Font("Tahoma", 1, 12));
tNama.setText("tNama");
tNama.setBorder(new javax.swing.border.BevelBorder(javax.swing.border.BevelBorder.RAISED));
tNama.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
tNamaActionPerformed(evt);
}
});
tNama.addFocusListener(new java.awt.event.FocusAdapter() {
public void focusGained(java.awt.event.FocusEvent evt) {
tNamaFocusGained(evt);
}
});

jPanel1.add(tNama);
tNama.setBounds(120, 90, 240, 30);

tJurusan.setBackground(new java.awt.Color(204, 255, 204));
tJurusan.setFont(new java.awt.Font("Tahoma", 1, 12));
tJurusan.setText("tJurusan");
tJurusan.setBorder(new javax.swing.border.BevelBorder(javax.swing.border.BevelBorder.RAISED));
tJurusan.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
tJurusanActionPerformed(evt);
}
});
tJurusan.addFocusListener(new java.awt.event.FocusAdapter() {
public void focusGained(java.awt.event.FocusEvent evt) {
tJurusanFocusGained(evt);
}
});

jPanel1.add(tJurusan);
tJurusan.setBounds(120, 130, 240, 30);

tAngkatan.setBackground(new java.awt.Color(204, 255, 204));
tAngkatan.setFont(new java.awt.Font("Tahoma", 1, 12));
tAngkatan.setText("tAngkatan");
tAngkatan.setBorder(new javax.swing.border.BevelBorder(javax.swing.border.BevelBorder.RAISED));
tAngkatan.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
tAngkatanActionPerformed(evt);
}
});
tAngkatan.addFocusListener(new java.awt.event.FocusAdapter() {
public void focusGained(java.awt.event.FocusEvent evt) {
tAngkatanFocusGained(evt);
}
});

jPanel1.add(tAngkatan);
tAngkatan.setBounds(120, 170, 240, 30);

tabel.setBorder(new javax.swing.border.EtchedBorder());
tabel.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {
{null, null, null, null},
{null, null, null, null},
{null, null, null, null},
{null, null, null, null}
},
new String [] {
"Title 1", "Title 2", "Title 3", "Title 4"
}
));
tabel.setGridColor(new java.awt.Color(204, 204, 255));
tabel.addKeyListener(new java.awt.event.KeyAdapter() {
public void keyPressed(java.awt.event.KeyEvent evt) {
tabelKeyPressed(evt);
}
public void keyReleased(java.awt.event.KeyEvent evt) {
tabelKeyReleased(evt);
}
});

jScrollPane1.setViewportView(tabel);

jPanel1.add(jScrollPane1);
jScrollPane1.setBounds(4, 214, 460, 110);

getContentPane().add(jPanel1, java.awt.BorderLayout.CENTER);

jPanel2.setLayout(new java.awt.GridLayout(1, 0));

bSimpan.setBackground(new java.awt.Color(0, 153, 153));
bSimpan.setText("Simpan");
bSimpan.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
bSimpanActionPerformed(evt);
}
});
bSimpan.addKeyListener(new java.awt.event.KeyAdapter() {
public void keyPressed(java.awt.event.KeyEvent evt) {
bSimpanKeyPressed(evt);
}
});

jPanel2.add(bSimpan);

bHapus.setBackground(new java.awt.Color(0, 153, 153));
bHapus.setText("Hapus");
bHapus.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
bHapusActionPerformed(evt);
}
});

jPanel2.add(bHapus);

bBatal.setBackground(new java.awt.Color(0, 153, 153));
bBatal.setText("Batal");
bBatal.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
bBatalActionPerformed(evt);
}
});

jPanel2.add(bBatal);

getContentPane().add(jPanel2, java.awt.BorderLayout.SOUTH);

java.awt.Dimension screenSize = java.awt.Toolkit.getDefaultToolkit().getScreenSize();
setBounds((screenSize.width-473)/2, (screenSize.height-381)/2, 473, 381);
}
//5.a mengisi data dari tabel tJadwal
private void tabelKeyReleased(java.awt.event.KeyEvent evt) {
if (evt.getKeyCode()==10 && kolomPress==0) {
try {
String kode= tabel.getValueAt(barisPress, kolomPress).toString();
if (kode.trim().length()>0) {
String SQL = " Select MataKuliah,Dosen from tJadwal where KodeJadwal = '"+ kode + "'";
//System.out.println("SQL = " + SQL);
ResultSet hasil=stmt.executeQuery(SQL);
hasil.next();
tabel.setValueAt(hasil.getString(1), barisPress, 1);
tabel.setValueAt(hasil.getString(2), barisPress, 2);
}
} catch(NullPointerException e) {
} catch(Exception e) {
JOptionPane.showMessageDialog(getParent(),"Gagal, "+e);
}
}
}
//5.b menghapus dan menambah kolom dengan tombol keyboard
private void tabelKeyPressed(java.awt.event.KeyEvent evt) {
if(evt.getKeyCode()==KeyEvent.VK_INSERT){
tambahBaris();
}else if(evt.getKeyCode()==KeyEvent.VK_DELETE){
hapusBaris();
}else{
barisPress= tabel.getSelectedRow();
kolomPress= tabel.getSelectedColumn();
if (barisPress == -1) barisPress =0;
if (kolomPress == -1) kolomPress =0;
}
}
//5.c menghapus data master dengan tombol bHapus
private void bHapusActionPerformed(java.awt.event.ActionEvent evt) {
try{
sql="delete from tMhs where NIM='"+tKode.getText().trim()+"' ";
stmt.executeUpdate(sql);
System.out.println(sql);
}catch(SQLException e){JOptionPane.showMessageDialog(this,"Gagal, "+e);}
updateTabel();
kosongkan();
}
//5.d menyimpan data
private void bSimpanKeyPressed(java.awt.event.KeyEvent evt) {
bSimpanActionPerformed(null);
}
//5.e memilih semua isi textfield
private void tAngkatanFocusGained(java.awt.event.FocusEvent evt) {
tAngkatan.selectAll();
}

private void tJurusanFocusGained(java.awt.event.FocusEvent evt) {
tJurusan.selectAll();
}

private void tNamaFocusGained(java.awt.event.FocusEvent evt) {
tNama.selectAll();
}

private void tKodeFocusGained(java.awt.event.FocusEvent evt) {
tKode.selectAll();
}
//5.f mengosongkan textfield dengan tombol batal
private void bBatalActionPerformed(java.awt.event.ActionEvent evt) {
kosongkan();
}
//5.d menyimpan data
private void bSimpanActionPerformed(java.awt.event.ActionEvent evt) {
try{
sql="select count(*) from tMhs where NIM='"+tKode.getText().trim()+"'";
hasil=stmt.executeQuery(sql);
hasil.next();
if(Integer.parseInt(hasil.getString(1))<1){
sql="insert into tMhs values ('"+tKode.getText()+"','"+tNama.getText()+"','"+tJurusan.getText()+"','"+tAngkatan.getText()+"') ";
stmt.executeUpdate(sql);
System.out.println(sql);
}else{
sql="update tMhs set Nama='"+tNama.getText()+"', Jurusan='"+tJurusan.getText()+"', Angkatan='"+tAngkatan.getText()+"' where NIM='"+tKode.getText().trim()+"' ";
stmt.executeUpdate(sql);
System.out.println(sql);
}
simpanDetil();
}catch(SQLException e){JOptionPane.showMessageDialog(this,"Gagal, "+e);}

kosongkan();
updateTabel();
tKode.requestFocus();
}
//5.g berpindah antar komponen
private void tAngkatanActionPerformed(java.awt.event.ActionEvent evt) {
bSimpan.requestFocus();
}

private void tJurusanActionPerformed(java.awt.event.ActionEvent evt) {
tAngkatan.requestFocus();
}

private void tNamaActionPerformed(java.awt.event.ActionEvent evt) {
tJurusan.requestFocus();
}
//5.h mencari kode yang cocok
private void tKodeActionPerformed(java.awt.event.ActionEvent evt) {
try{
sql="select count(*) from tMhs where NIM='"+tKode.getText().trim()+"'";
hasil=stmt.executeQuery(sql);
hasil.next();
if(Integer.parseInt(hasil.getObject(1).toString())>=1){
sql="select Nama,Jurusan,Angkatan from tMhs where NIM='"+tKode.getText().trim()+"'";
hasil=stmt.executeQuery(sql);
hasil.next();
tNama.setText(hasil.getString(1));
tJurusan.setText(hasil.getString(2));
tAngkatan.setText(hasil.getString(3));
bHapus.setEnabled(true);
}else{
kosongkan();
}
updateTabel();
tNama.requestFocus();
}catch(SQLException e){JOptionPane.showMessageDialog(this,"Gagal, "+e);}
}

/** Exit the Application */
private void exitForm(java.awt.event.WindowEvent evt) {
System.exit(0);
}

/**
* @param args the command line arguments
*/
public static void main(String args[]) {
new frmKardi().show();
}
//3.a Untuk mengosongkan textfield
private void kosongkan(){
tNama.setText("");
tJurusan.setText("");
tAngkatan.setText("");
bHapus.setEnabled(false);
}
//3.b untuk membuat koneksi ke database
public void testDriver(){
try{
Class.forName(dbDrv);
con = DriverManager.getConnection(dbUrl,sqlUser,sqlPass);
stmt=con.createStatement();
}catch(Exception ce){
System.out.println(ce.toString());
}
}
//3.c untuk memperbaharui isi JTable
private void updateTabel(){
tabel.setModel(model=new DefaultTableModel());
//tabel.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
try{
sql="SELECT tKardi.KodeJadwal,tJadwal.MataKuliah,tJadwal.Dosen,tKardi.Kelas " +
"FROM tKardi LEFT JOIN tJadwal on tKardi.KodeJadwal=tJadwal.KodeJadwal ";
ResultSet hasil=stmt.executeQuery(sql);
ResultSetMetaData rsKolom=hasil.getMetaData();
for(int I=1;I<=rsKolom.getColumnCount();I++)
model.addColumn(rsKolom.getColumnName(I).trim());

Vector V=null;

tambahBaris();

for(int I=0;hasil.next();I++){
V=new Vector();
for(int J=1;J<=hasil.getMetaData().getColumnCount();J++){
V.add(hasil.getObject(J));
}
model.addRow(V);
}
}catch(Exception e){System.err.println(e.getMessage()+"2");}
}
//3.d untuk menambah baris baru
private void tambahBaris(){
Vector V=new Vector();
for(int I=1;I<=model.getColumnCount();I++)
V.add(null);
model.addRow(V);
}
//3.e untuk menghapus baris yg dipilih
public void hapusBaris(){
model.removeRow(tabel.getSelectedRow());
}
//3.f untuk menyimpan isi JTable pada tabel detil
public void simpanDetil(){
try{
sql="delete from tKardi where NIM='"+tKode.getText().trim()+"'";
stmt.executeUpdate(sql);
for(int i=0;i<tabel.getRowCount();i++){
sql="insert into tKardi values ( '"+tKode.getText()+"','"+model.getValueAt(i,0)
+"','"+model.getValueAt(i,3)+"')";
stmt.executeUpdate(sql);
System.out.println(sql);
}
}catch(SQLException e){JOptionPane.showMessageDialog(this,"Gagal, "+e);}
}
try{
return new SimpleDateFormat("MM/dd/yyyy").format(new SimpleDateFormat("dd/MM/yyyy").parse(d));
}catch(Exception e){return null;}
}

// Variables declaration - do not modify
private javax.swing.JButton bBatal;
private javax.swing.JButton bHapus;
private javax.swing.JButton bSimpan;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JPanel jPanel1;
private javax.swing.JPanel jPanel2;
private javax.swing.JScrollPane jScrollPane1;
private javax.swing.JTextField tAngkatan;
private javax.swing.JTextField tJurusan;
private javax.swing.JTextField tKode;
private javax.swing.JTextField tNama;
private javax.swing.JTable tabel;
// End of variables declaration

//4. variabel global
private String sql,sql2;
Connection con;
Statement stmt;
ResultSet hasil,hasil2;
String sqlUser="sa"; //UserName untuk administrator SQL server
String sqlPass="sa"; //Password untuk administrator SQL server
String sqlDb="dbKuliah"; //Database yang diakses
String koneksiOdbc="koneksi"; //Nama koneksi odbc
String dbDrv = "sun.jdbc.odbc.JdbcOdbcDriver";
//String dbDrv = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String dbUrl = "jdbc:odbc:"+koneksiOdbc;
//String dbUrl = "jdbc:microsoft:sqlserver://localhost;DatabaseName="+sqlDb;
DefaultTableModel model;
int barisPress=0, kolomPress=0;
}