30 January 2008

Delphi-DB: Form Master Detil

Form Jadwal Mengajar Dosen


Lanjutan dari pembuatan form dosen, dengan penambahan konsep master-detil untuk mengatur jadwal mengajar dosen:
- Buat Form
+ Label: Label1, Label2, Label3, Label4
+ Button: Button1, Button2, Button3
+ TTable: Table1, Table2
+ TDataSource: DataSource1, DataSource2
+ TQuery: Query1
+ TDBNavigator: DBNavigator1
+ TDBGrid: DBGrid1
- Atur Database:
dengan menggunakan Database Desktop, buat database dan tabel nya:
+DatabaseName: dbQ
+TableName: tDosen, tJadwal
+ Pada tabel tJadwal, buat sebuah secondary index sebagai penghubung dengan tabel master ( tDosen):
Table: tJadwal
Index Name: KodeDosenIdx
Secondary Index Field: KodeDosen
- Aturan Mengenai Master-Detil adalah:
+ Tabel Master berupa dosen, tabel Detil berupa jadwal mengajar
+ Secondary Index pada tabel detil tidak bersifat unik, fungsinya untuk menghubungkan dengan primary key pada tabel master, atau berupa foreign key pada tabel detil.
+ Tabel Master berasal dari input bagian komponen Edit
+ Tabel detail digambarkan dalam bentuk grid
+ Isi grid selalu sesuai dengan kode dosen yang dipilih di bagian edit1
+ segala perubahan data disimpan dalam dua tabel, master dan detil
+ Contoh ini tidak menggunakan dasar normalisasi database untuk penyederhanaan konsep

//Kode Lengkap:
unit Unit2;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, DBTables, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids,
ComCtrls;

type
TForm2 = class(TForm)
Edit1: TEdit;
Edit2: TEdit;
Edit3: TEdit;
Button1: TButton;
Button2: TButton;
Table1: TTable;
Query1: TQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
Button3: TButton;
DateTimePicker1: TDateTimePicker;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
Table2: TTable;
DataSource2: TDataSource;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Kosongkan();
procedure Edit1KeyPress(Sender: TObject; var Key: Char);
procedure Edit2KeyPress(Sender: TObject; var Key: Char);
procedure Edit3KeyPress(Sender: TObject; var Key: Char);
procedure DateTimePicker1KeyPress(Sender: TObject; var Key: Char);
procedure Button3Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form2: TForm2;

implementation

{$R *.dfm}

procedure TForm2.FormCreate(Sender: TObject);
begin
table1.DatabaseName := 'dbQ' ;
table1.TableName := 'tDosen.db';
table1.Active:= true;
datasource1.DataSet:= table1;
Query1.DataSource:=DataSource1;
Query1.Active:=false;

table2.DatabaseName:='dbQ';
table2.TableName:='tJadwal.db';
table2.Active:=true;
table2.MasterSource:=DataSource1;

table2.IndexName:='KodeDosenIdx';
table2.MasterFields:='FKode';


datasource2.DataSet:=table2;
dbgrid1.DataSource := datasource2;
dbNavigator1.DataSource:=datasource2;

label1.Caption:='Kode';
label2.Caption:='Nama';
label3.Caption:='Alamat';
button1.Caption:='Simpan';
button2.Caption:='Hapus';
button3.Caption:='Batal';
edit1.text:='';
Form2.Caption:='Form Input Dosen';
Kosongkan();
end;

procedure TForm2.Button1Click(Sender: TObject);
var
str1:String;
begin
with Query1 do begin
Close;
SQL.Clear;
SQL.Add('SELECT count(*) as jml FROM tDosen WHERE FKode = :f1 ');
Params[0].AsString:=Edit1.Text;
Active:=true;
Open;

if Query1['jml']<>NULL then //Mencegah error apabila tabel masih kosong
str1:=Query1['jml']
else
str1:='0';

Active:=False;
end;
if strtoint(str1)>0 then
begin
table1.FindNearest([edit1.Text]);
table1.Edit;
table1.Fields[1].AsString:=edit2.Text;
table1.Fields[2].AsString:= edit3.Text;
table1.Fields[3].AsDateTime:= DateTimePicker1.DateTime;
table1.Post;
end
else
begin
table1.InsertRecord([edit1.text,edit2.text,edit3.text,DateTimePicker1.Date]);
end;
kosongkan;
edit1.SetFocus;
end;

procedure TForm2.Button2Click(Sender: TObject);
begin
table1.FindNearest([edit1.Text]);
table1.Delete;
kosongkan;
end;
procedure TForm2.Kosongkan();
begin
edit2.text:='';
edit3.text:='';
end;
procedure TForm2.Edit1KeyPress(Sender: TObject; var Key: Char);
var
str1:String;
begin
if Key=#13 then
begin

with Query1 do begin
Close;
SQL.Clear;
SQL.Add('SELECT count(*) as jml FROM tDosen WHERE FKode = :f1 ');
Params[0].AsString:=Edit1.Text;
Active:=true;
Open;

if Query1['jml']<>NULL then //Mencegah error apabila tabel masih kosong
str1:=Query1['jml']
else
str1:='0';

Active:=False;
end;
if strtoint(str1)>0 then
begin
table1.FindNearest([edit1.Text]);
edit2.Text:=table1.Fields[1].AsString;
edit3.Text:=table1.Fields[2].AsString;
DateTimePicker1.DateTime:=table1.Fields[3].AsDateTime;
end
else
kosongkan;
edit2.SetFocus;
end;
end;

procedure TForm2.Edit2KeyPress(Sender: TObject; var Key: Char);
begin
if Key=#13 then
begin
edit3.SetFocus;
end;
end;

procedure TForm2.Edit3KeyPress(Sender: TObject; var Key: Char);
begin
if Key=#13 then
begin
DateTimePicker1.SetFocus;
end;
end;

procedure TForm2.DateTimePicker1KeyPress(Sender: TObject; var Key: Char);
begin
if Key=#13 then
begin
button1.SetFocus;
end;
end;

procedure TForm2.Button3Click(Sender: TObject);
begin
kosongkan;
end;
end.
N3XT...arrow

28 January 2008

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;
}
kakashifull
N3XT...arrow

22 January 2008

Join di SQL

kakashifull
Sebuah cara memahami dasar join pada SQL.
Buat database dengan relasi berikut: Tambahkan data untuk setiap tabel, contoh datanya:

t_Siswa:
S8564 Jaja Surabaya Bandung 1999-08-16 00:00:00
S9857 Budi Surabaya Sidoarjo 1998-12-15 00:00:00

t_Gaji:
IA 500000.0000
IB 600000.0000
IIA 700000.0000
IIB 800000.0000
IIIA 900000.0000
IIIB 1000000.0000

t_Guru:
G1523 Tuti Surabaya IIA
G5623 Siti Malang IIIB

t_Kelas:
1A 1 G1523
1B 1 NULL
2A 2 NULL
2B 2 G5623
3 3 NULL
4 4 NULL
5 5 NULL
6 6 NULL

t_IsiKelas:
1 2007/2008 1A S8564
2 2007/2008 2B S9857

Setelah itu coba perintah join:

use db_Sekolah

select * from t_Siswa
select * from t_Gaji
select * from t_Guru
select * from t_Kelas
select * from t_IsiKelas

--melihat gaji semua guru
select A.Nama,B.Gaji
from t_Guru as A left join t_Gaji as B on A.Golongan=B.Golongan

--menampilkan nama guru dan jenjang kelas yang diajar
select A.No_Induk,A.Nama,B.Jenjang
from t_Guru as A left join t_Kelas as B on A.No_Induk=B.Guru

--menampilkan daftar kelas dan nama guru
select t_Kelas.Kode_Kelas,t_Kelas.Jenjang,t_Guru.Nama
from t_Kelas left join t_Guru on t_Kelas.Guru=t_Guru.No_Induk

--melihat daftar kelas siswa tahun ajaran 2007/2008
select B.Tahun_Ajaran,A.No_Induk,A.Nama,B.Kode_Kelas
from t_Siswa as A left join t_IsiKelas as B on A.No_Induk=B.Siswa where B.Tahun_Ajaran='2007/2008'

--menampilkan gaji dosen dari setiap kelas
select t_Kelas.Kode_Kelas,t_Kelas.Jenjang,t_Gaji.Gaji
from t_Kelas left join t_Guru on t_Kelas.Guru=t_Guru.No_Induk left join t_Gaji on t_Guru.Golongan=t_Gaji.Golongan

--melihat alamat siswa dan alamat guru pengajarnya
select A.Alamat as Alamat_Siswa, D.Alamat as Alamat_Pengajar
from t_Siswa as A left join t_IsiKelas as B on A.No_Induk=B.Siswa left join t_Kelas as C on B.Kode_Kelas=C.Kode_Kelas left join t_Guru as D on C.Guru=D.No_Induk

Coba jenis-jenis join dengan perintah yang sama, tentu dengan hasil yang berbeda:
- Inner join, Menampilkan rekord yang bersesuaian dari kedua tabel. Intersection kalau dalam istilah himpunan.
- Outer join, Menampilkan rekord yang bersesuaian dan rekord yang belum ditampilkan dari kedua tabel. Union dalam istilah himpunan.
- Left join, menampilkan semua record dari tabel pertama dan menampilkan isi tabel kedua yang bersesuaian dengan kondisi tabel pertama.
- Right join, menampilkan semua rekord tabel kedua dan menampilkan isi tabel pertama yang bersesuaian dengan tabel kedua.
N3XT...arrow

21 January 2008

Algoritma MD5

Karena mulai ada yg ngamuk2, protes soal mutu dan kualitas segala macem, padahal alasannya ada di sini, sini, dan situ, terpaksa mulai serius dikit deh. Enkripsi ya, mulai dengan algoritma yaaah.
MD5, enkripsi yang mengubah string menjadi 32 karakter. fungsi yang sudah ada dalam php dan mysql, sayangnya belum terintegrasi dalam sql server dan java.

Contoh enkripsi dengan MD5:
 MD5("The quick brown fox jumps over the lazy dog")
= 9e107d9d372bb6826bd81d3542a419d6
 MD5("The quick brown fox jumps over the lazy eog")
= ffd93f16876049265fbaef4da268dd0e
 MD5("")
= d41d8cd98f00b204e9800998ecf8427e




Yg proses logikanya:
(0 ≤ i ≤ 15): f := d xor (b and (c xor d))
(16 ≤ i ≤ 31): f := c xor (d and (b xor c))

Kode untuk bahasa c diambil dari en.wikipedia.org/wiki/MD5 :
Pseudocode for the MD5 algorithm follows.

//Note: All variables are unsigned 32 bits and wrap modulo 2^32 when calculating
var int[64] r, k

//r specifies the per-round shift amounts
r[ 0..15] := {7, 12, 17, 22, 7, 12, 17, 22, 7, 12, 17, 22, 7, 12, 17, 22}
r[16..31] := {5, 9, 14, 20, 5, 9, 14, 20, 5, 9, 14, 20, 5, 9, 14, 20}
r[32..47] := {4, 11, 16, 23, 4, 11, 16, 23, 4, 11, 16, 23, 4, 11, 16, 23}
r[48..63] := {6, 10, 15, 21, 6, 10, 15, 21, 6, 10, 15, 21, 6, 10, 15, 21}

//Use binary integer part of the sines of integers (Radians) as constants:
for i from 0 to 63
k[i] := floor(abs(sin(i + 1)) × (2 pow 32))

//Initialize variables:
var int h0 := 0x67452301
var int h1 := 0xEFCDAB89
var int h2 := 0x98BADCFE
var int h3 := 0x10325476

//Pre-processing:
append "1" bit to message
append "0" bits until message length in bits = 448 (mod 512)
append bit (bit, not byte) length of unpadded message as 64-bit little-endian integer to message

//Process the message in successive 512-bit chunks:
for each 512-bit chunk of message
break chunk into sixteen 32-bit little-endian words w[i], 0 = i = 15

//Initialize hash value for this chunk:
var int a := h0
var int b := h1
var int c := h2
var int d := h3

//Main loop:
for i from 0 to 63
if 0 = i = 15 then
f := (b and c) or ((not b) and d)
g := i
else if 16 = i = 31
f := (d and b) or ((not d) and c)
g := (5×i + 1) mod 16
else if 32 = i = 47
f := b xor c xor d
g := (3×i + 5) mod 16
else if 48 = i = 63
f := c xor (b or (not d))
g := (7×i) mod 16

temp := d
d := c
c := b
b := b + leftrotate((a + f + k[i] + w[g]) , r[i])
a := temp

//Add this chunk's hash to result so far:
h0 := h0 + a
h1 := h1 + b
h2 := h2 + c
h3 := h3 + d

var int digest := h0 append h1 append h2 append h3 //(expressed as little-endian)
//leftrotate function definition
leftrotate (x, c)
return (x << c) or (x >> (32-c));



{
Segitu dulu, silakan dicoba aja pasti bisa. Minta yg java/VB? nanti aja deh kalo sempet.
}
N3XT...arrow

16 January 2008

Delphi-DB: Form Input Sederhana


Contoh input data pada form delphi setelah sebelumnya cuma pembacaan data :
1. Atur database:
- Database: dbQ
- tabel: tDosen
- Field: FKode[A][*], FNama[A], FAlamat[A], FTglLahir[D]
2. Buat Form:
- Nama Form: Form2
- Label: Label1, Label2, Label3, Label4
- Edit: Edit1, Edit2, Edit3
- DateTimePicker: DateTimePicker1
- Button: Button1, Button2, Button3
- Table: Table1
- Query: Query1
- DataSource: DataSource1
- DBGrid: DBGrid1
- DBNavigator: DBNavigator1
3. Ubah Kodenya:

//Source Lengkapnya:
unit Unit2;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, DBTables, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids,
ComCtrls;

type
TForm2 = class(TForm)
Edit1: TEdit;
Edit2: TEdit;
Edit3: TEdit;
Button1: TButton;
Button2: TButton;
Table1: TTable;
Query1: TQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
Button3: TButton;
DateTimePicker1: TDateTimePicker;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
Contoh input data pada form delphi setelah sebelumnya cuma pembacaan data :
1. Atur database:
- Database: dbQ
- tabel: tDosen
- Field: FKode[A][*], FNama[A], FAlamat[A], FTglLahir[D]
2. Buat Form:
- Nama Form: Form2
- Label: Label1, Label2, Label3, Label4
- Edit: Edit1, Edit2, Edit3
- DateTimePicker: DateTimePicker1
- Button: Button1, Button2, Button3
- Table: Table1
- Query: Query1
- DataSource: DataSource1
- DBGrid: DBGrid1
- DBNavigator: DBNavigator1
3. Ubah Kodenya:

//Source Lengkapnya:
unit Unit2;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, DBTables, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids,
ComCtrls;

type
TForm2 = class(TForm)
Edit1: TEdit;
Edit2: TEdit;
Edit3: TEdit;
Button1: TButton;
Button2: TButton;
Table1: TTable;
Query1: TQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
Button3: TButton;
DateTimePicker1: TDateTimePicker;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Kosongkan();
procedure Edit1KeyPress(Sender: TObject; var Key: Char);
procedure Edit2KeyPress(Sender: TObject; var Key: Char);
procedure Edit3KeyPress(Sender: TObject; var Key: Char);
procedure DateTimePicker1KeyPress(Sender: TObject; var Key: Char);
procedure Button3Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form2: TForm2;

implementation

{$R *.dfm}

procedure TForm2.FormCreate(Sender: TObject);
begin
table1.DatabaseName := 'dbQ' ;
table1.TableName := 'tDosen.db';
table1.Active:= true;
datasource1.DataSet:= table1;
Query1.DataSource:=DataSource1;
Query1.Active:=false;
dbgrid1.DataSource := datasource1;
dbNavigator1.DataSource:=datasource1;
label1.Caption:='Kode';
label2.Caption:='Nama';
label3.Caption:='Alamat';
button1.Caption:='Simpan';
button2.Caption:='Hapus';
button3.Caption:='Batal';
edit1.text:='';
Form2.Caption:='Form Input Dosen';
Kosongkan();
end;

procedure TForm2.Button1Click(Sender: TObject);
var
str1:String;
begin
with Query1 do begin
Close;
SQL.Clear;
SQL.Add('SELECT count(*) as jml FROM tDosen WHERE FKode = :f1 ');
Params[0].AsString:=Edit1.Text;
Active:=true;
Open;

if Query1['jml']<>NULL then //Mencegah error apabila tabel masih kosong
str1:=Query1['jml']
else
str1:='0';

Active:=False;
end;
if strtoint(str1)>0 then
begin
table1.FindNearest([edit1.Text]);
table1.Edit;
table1.Fields[1].AsString:=
edit2.Text;
table1.Fields[2].AsString:=
edit3.Text;
table1.Fields[3].AsDateTime:=
DateTimePicker1.DateTime;
table1.Post;
end
else
begin
table1.InsertRecord([edit1.text,edit2.text,edit3.text,DateTimePicker1.Date]);
end;
kosongkan;
edit1.SetFocus;
end;

procedure TForm2.Button2Click(Sender: TObject);
begin
table1.FindNearest([edit1.Text]);
table1.Delete;
kosongkan;
end;
procedure TForm2.Kosongkan();
begin
edit2.text:='';
edit3.text:='';
//edit1.SetFocus;
end;
procedure TForm2.Edit1KeyPress(Sender: TObject; var Key: Char);
var
str1:String;
begin
if Key=#13 then
begin

with Query1 do begin
Close;
SQL.Clear;
SQL.Add('SELECT count(*) as jml FROM tDosen WHERE FKode = :f1 ');
Params[0].AsString:=Edit1.Text;
Active:=true;
Open;

if Query1['jml']<>NULL then //Mencegah error
str1:=Query1['jml']
else
str1:='0';

Active:=False;
end;
if strtoint(str1)>0 then
begin
table1.FindNearest([edit1.Text]);
edit2.Text:=table1.Fields[1].AsString;
edit3.Text:=table1.Fields[2].AsString;
DateTimePicker1.DateTime:=table1.Fields[3].AsDateTime;
end;
edit2.SetFocus;
end;
end;

procedure TForm2.Edit2KeyPress(Sender: TObject; var Key: Char);
begin
if Key=#13 then
begin
edit3.SetFocus;
end;
end;

procedure TForm2.Edit3KeyPress(Sender: TObject; var Key: Char);
begin
if Key=#13 then
begin
DateTimePicker1.SetFocus;
end;
end;

procedure TForm2.DateTimePicker1KeyPress(Sender: TObject; var Key: Char);
begin
if Key=#13 then
begin
button1.SetFocus;
end;
end;

procedure TForm2.Button3Click(Sender: TObject);
begin
kosongkan;
end;

end.
kakashifull N3XT...arrow

15 January 2008

Fungsi di SQL Server 2000


SQL Functions

kakashi

Table 10-1: Standard SQL2 Functions

SQL Function

Description

BIT_LENGTH (expression)

Memberikan nilai panjang dalam bit

CAST (value AS data type)

Mengubah tipe data

CHAR_LENGTH (expression)

Mengembalikan panjang karakter

CONVERT (expression USING conversion)

Mengubah tipe data

CURRENT_DATE

Mengembalikan tanggal sistem

CURRENT_TIME (precision)

-

CURRENT_TIMESTAMP (precision)

Mengembalikan waktu dan tanggal

EXTRACT (part FROM expression)

Memecah string

LOWER (expression)

Mengubah string menjadi huruf besar

OCTET_LENGTH (expression)

Mengembalikan panjang byte

POSITION (char expression IN source)

Mencari posisi karakter dalam string

SUBSTRING (string expression, start, length)

Mengambil sebagian string

TRANSLATE (string expression USING translation rule)

Mengubah string ke string lain dengan aturan tertentu

TRIM(LEADING | TRAILING | BOTH char expression FROM string expression)

Menghapus karakter tertentu

UPPER (expression)

Mengubah string menjadi huruf besar




Numeric functions

.

Table 10-2: Numeric Functions

ABS (n)

Nilai absolut n

CEILING (n)

Pembulatan n keatas

EXP (n)

Eksponen n

FLOOR (n)

Pembulatan n kebawah

Operator %

Sisa hasil pembagian

POWER.(m,n)

n pangkat m

RAND.(n)

Angka acak dari 0 – 1

ROUND (n,m,[0])

Pembulatan bilangan n sampai m dibelakang koma

SIGN(n)

-1 jika negatif, 0 jika 0, +1 jika positif

String functions

Table 10-3: String Functions

ASCII (string)

Kode ASCII dari karakter pertama String

CHAR (number) NCHAR (number)

Mengubah kode ASCII menjadi karakter

operator '+'

Menggabungkan dua string

CHARINDEX (string1,string2, n)
PATINDEX
(, )

mencari string dari string lain

LEFT (string, n)

Mengambil n huruf dari kiri string

LEN (string)

Jumlah karakter string

DATALENGTH (expression)

Jumlah byte dari data

LOWER (string)
LCASE (string)

-

REPLICATE (char expression, integer)
SPACE(integer)

Mencetak karakter sebanyak integer

LTRIM (string)

Menghapus karakter kosong didepan

REPLACE (string1, string2, string3)

Mengganti string2 dalam string1 dengan string3

SPACE (integer)

-

REPLICATE (string, n)

-

RTRIM (string)

Menghapus karakter kosong dibelakang

STR (expression)

Mengubah ekspresi menjadi string

SUBSTRING (string, n, m)

-

LTRIM (RTRIM (string))

-

UPPER (string)

-




Date and time functions

Table 10-4: Date and Time Functions

DATEADD (month, number, date)

Penambahan bulan

GETDATE**
GETUTCDATE**
CURRENT_ TIMESTAMP

Pengambilan tanggal

DAY

Mengambil hari dari tanggal

DATENAME (date part, datetime)

mengambil hari atau bulan

DATEPART (date part, datetime)

(day, month, year).

DATEDIFF

Jarak antara dua hari

GETUTCDATE

Mengambil tanggal UTC

DATEADD (day, n, m)

Menambahkan hari ke tanggal

GETDATE

Mengambil tanggal

YEAR

Mengambil tahun

Table 10-5: Getting Current Date Out of RDBMS

SELECT GETDATE()

2003-09-05 13:54:18.873

---------------

2003-09-05 13:54:18.873

Aggregate functions

Table 10-7: Aggregate Functions

AVG (number)

Rata-rata

COUNT (integer)
COUNT_BIG (bigint)

Jumlah baris dari hasil select

MAX (number)

Nilai max

MIN (number)

Nilai min

SUM (number)

Jumlah dari nilai satu kolom

Conversion functions

Table 10-8: Conversion Functions

CAST (data type AS data type)

CONVERT (into data type, value, format)

Konversi tipe data

CAST (expression as VARCHAR/CHAR (N))

-

CAST (expression as DATETIME)

-

Miscellaneous functions

Table 10-14: Miscellaneous Functions

COALESCE (expression1, expression2, expression3 ...)

Mengembalikan nilai bukan NULL

CASE (expression)
WHEN
THEN
ELSE END

Pembanding

NULLIF (expression1, expression2)

Memilih bukan NULL kecuali keduanya NULL

ISNULL (expression, value)

Mengubah NULL dengan value


Sumber:

SQL Bible
by Alex Kriegel and Boris M. Trukhnov ISBN:0764525840
John Wiley & Sons © 2003
N3XT...arrow

14 January 2008

Java-SQL: input data sederhana

kakashifull
Ini contoh input data sederhana dengan hanya menggunakan JTextfield dengan implementasi pada pemasukkan data dosen. Merupakan contoh lanjutan dari sebelumnya dengan tetap menekankan pada pemahaman dasar hubungan dengan java dengan database dengan cepat dan bersifat modular, terpisah dengan form lainnya. Penggunaan OOP tentu saja belum diterapkan.
Cobain aja......
1. Atur Database
- Tabel: dosen
- Field: kode(char), nama(varchar), alamat(varchar), tgl_lahir(smalldatetime)
2. Pilih jenis koneksi ke database MS SQL Server 2000:
a. Untuk odbc, buat datasource dengan nama koneksi(berlaku untuk semua jenis RDBMS)
b. Untuk driver, install terlebih dahulu driver koneksinya, pastikan classpath benar, harus tahu username dan password untuk koneksi.
3. Pengaturan Tampilan:
- Buat dengan class dasar JFrame Form
- JTextField: tkode, tNama, tAlamat, tTglLahir, tempatkan di jPanel1
- JButton: bSimpan, bHapus, bBatal, tempatkan didalam jPanel2
- JTable: tabel, tempatkan didalam jScrollPane1
Pengaturan Layout Manager:
JFrame tidak bisa diubah warnanya dengan mudah sehingga perlu penambahan JPanel terlebih dahulu. JFrame tetap menggunakan Layout default yaitu BorderLayout. Tambahkan jPanel1 dibagian "CENTER" untuk komponen dengan layout NULL, dan jPanel2 dibagian "SOUTH" untuk tempat tombol dengan layout GridLayout. Tempatkan JTable di dalam jScrollPane untuk memudahkan pengaturan tampilan tabel. Setelah itu pengaturan warna tampilan dan komponen.
4. Penambahan Kode:
a. tambahkan import
b. ubah Constructor dari class agar mengatur tampilan awal
c. tambahkan variabel global, pilih jenis koneksi di bagian ini
d. tambahkan Fungsi tambahan
- kosongkan, untuk mengosongkan komponen input
- testDriver, untuk mengatur koneksi db
- updatebel, untuk mengisi JTable tabel
- getTanggal dan setTanggal, untuk mengkonversi format tanggal dengan db
e. atur event dari komponen tombol: bSimpan, bHapus, bBatal
5. Masalah interaksi dengan pengguna program:
a. Mengatur aliran kursor, sehingga hanya menggunakan keyboard dan meminimalkan penggunaan mouse, sehingga lebih cepat dalam pengisian data. Gunakan requestFocus untuk komponen tujuan dengan menempatkan di bagian event actionPerformed komponen sebelumnya, atau keyPress untuk button.
b. Melakukan pencarian kode setelah penekanan Enter pada bagian kode, siap ditambah apabila belum ada, siap diedit atau dihapus apabila sudah ada.
c. Menampilkan isi baris dari tabel yang dipilih, dengan memanggil event actionPerformed dari tKode.
d. Memilih semua isi dari JTextField, apabila mendapat kursor, sehingga memudahkan untuk mengganti/menghapus isi sekaligus. Gunakan fungsi selectAll() dari komponen textfield di event focusGained

//Source Lengkapnya:
//4.a
import javax.swing.*;
import java.sql.*;
import java.text.*;
import javax.swing.table.*;
import java.util.*;
public class frmDosen extends javax.swing.JFrame {
//4.b
public frmDosen() {
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();
tAlamat = new javax.swing.JTextField();
tTglLahir = 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("Alamat");
jPanel1.add(jLabel3);
jLabel3.setBounds(50, 130, 60, 30);

jLabel4.setFont(new java.awt.Font("Arial", 1, 12));
jLabel4.setText("Tgl Lahir");
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);

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

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

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

jPanel1.add(tTglLahir);
tTglLahir.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.addMouseListener(new java.awt.event.MouseAdapter() {
public void mouseClicked(java.awt.event.MouseEvent evt) {
tabelMouseClicked(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.c
private void tabelMouseClicked(java.awt.event.MouseEvent evt) {
tKode.setText(tabel.getValueAt(tabel.getSelectedRow(), 0).toString().trim());
tKodeActionPerformed(null);
}
//4.e
private void bHapusActionPerformed(java.awt.event.ActionEvent evt) {
try{
sql="delete from dosen where kode_dsn='"+tKode.getText().trim()+"' ";
stmt.executeUpdate(sql);
System.out.println(sql);
}catch(SQLException e){JOptionPane.showMessageDialog(this,"Gagal, "+e);}
updateTabel();
kosongkan();
}
//5.a
private void bSimpanKeyPressed(java.awt.event.KeyEvent evt) {
bSimpanActionPerformed(null);
}
//5.d
private void tTglLahirFocusGained(java.awt.event.FocusEvent evt) {
tTglLahir.selectAll();
}
//5.d
private void tAlamatFocusGained(java.awt.event.FocusEvent evt) {
tAlamat.selectAll();
}
//5.d
private void tNamaFocusGained(java.awt.event.FocusEvent evt) {
tKode.selectAll();
}
//5.d
private void tKodeFocusGained(java.awt.event.FocusEvent evt) {
tKode.selectAll();
}
//4.e
private void bBatalActionPerformed(java.awt.event.ActionEvent evt) {
kosongkan();
}
//4.e
private void bSimpanActionPerformed(java.awt.event.ActionEvent evt) {
try{
sql="select count(*) from dosen where kode_dsn='"+tKode.getText().trim()+"'";
hasil=stmt.executeQuery(sql);
hasil.next();
if(Integer.parseInt(hasil.getString(1))<1){
sql="insert into dosen values ('"+tKode.getText()+"','"+tNama.getText()+"','"+tAlamat.getText()+"','"+setTanggal(tTglLahir.getText())+"') ";
stmt.executeUpdate(sql);
System.out.println(sql);
}else{
sql="update dosen set Nama='"+tNama.getText()+"', Alamat='"+tAlamat.getText()+"', Tgl_Lahir='"+tTglLahir.getText()+"' where kode_dsn='"+tKode.getText().trim()+"' ";
stmt.executeUpdate(sql);
System.out.println(sql);
}
}catch(SQLException e){JOptionPane.showMessageDialog(this,"Gagal, "+e);}
kosongkan();
updateTabel();
tKode.requestFocus();
}
//5.e
private void tTglLahirActionPerformed(java.awt.event.ActionEvent evt) {
bSimpan.requestFocus();
}
//5.e
private void tAlamatActionPerformed(java.awt.event.ActionEvent evt) {
tTglLahir.requestFocus();
}
//5.e
private void tNamaActionPerformed(java.awt.event.ActionEvent evt) {
tAlamat.requestFocus();
}
//5.b
private void tKodeActionPerformed(java.awt.event.ActionEvent evt) {
try{
sql="select count(*) from dosen where kode_dsn='"+tKode.getText().trim()+"'";
hasil=stmt.executeQuery(sql);
hasil.next();
if(Integer.parseInt(hasil.getObject(1).toString())>=1){
sql="select Nama,Alamat,tgl_lahir from dosen where kode_dsn='"+tKode.getText().trim()+"'";
hasil=stmt.executeQuery(sql);
hasil.next();
tNama.setText(hasil.getString(1));
tAlamat.setText(hasil.getString(2));
tTglLahir.setText(getTanggal(hasil.getDate(3)));
bHapus.setEnabled(true);
}else{
kosongkan();
}
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 frmDosen().show();
}
//4.d
private void kosongkan(){
tNama.setText("");
tAlamat.setText("");
tTglLahir.setText("");
bHapus.setEnabled(false);
}
//4.d
public void testDriver(){
try{
Class.forName(dbDrv);
con = DriverManager.getConnection(dbUrl,sqlUser,sqlPass);
stmt=con.createStatement();
}catch(Exception ce){
System.out.println(ce.toString());
}
}
//4.d
private void updateTabel(){
tabel.setModel(model=new DefaultTableModel());
//tabel.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
try{
sql="SELECT * FROM dosen";
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;
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");}
}
//4.d
public String getTanggal(java.util.Date d){
return new SimpleDateFormat("dd/MM/yyyy").format(d);
}
//4.d
public String setTanggal(String d){
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//GEN-BEGIN:variables
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 tAlamat;
private javax.swing.JTextField tKode;
private javax.swing.JTextField tNama;
private javax.swing.JTextField tTglLahir;
private javax.swing.JTable tabel;
// End of variables declaration//GEN-END:variables

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

N3XT...arrow