MVC, duet: Ubuntu, MySQL, Java, NetBeans, Tomcat, ZK, iReport (Part 1)

Database:The design of the database is made as simple as possible for ease in understanding. Do not get dizzy first before the war. For the connections will be staying in are generated from Netbeans, but patiently, one at a time. Each table must have primary keys.

CREATE TABLE  `pfm`.`item` (  `iditem` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key', `namaitem` varchar(20) NOT NULL COMMENT 'transaction item name',  `alurkas` tinyint(4) unsigned NOT NULL COMMENT 'cash flow, in: 1, out: 0',  PRIMARY KEY (`iditem`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='transaction item';

CREATE TABLE  `pfm`.`pengguna` (  `idpengguna` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',  `namalogin` varchar(10) NOT NULL COMMENT 'name to login',  `katasandi` char(32) NOT NULL COMMENT 'password, use md5 (32 character)',  PRIMARY KEY (`idpengguna`),  UNIQUE KEY `uniknamalogin` (`namalogin`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='user;

CREATE TABLE  `pfm`.`transaksi` (  `idtransaksi` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',  `idpengguna` int(10) unsigned NOT NULL COMMENT 'foreign key',  `iditem` int(10) unsigned NOT NULL COMMENT 'foreign key',  `nominal` int(10) unsigned NOT NULL COMMENT 'transaction nominal',  `tgltransaksi` date NOT NULL COMMENT 'transaction date', `keterangan` varchar(100) NOT NULL COMMENT 'transaction info',  PRIMARY KEY (`idtransaksi`),  KEY `fkpengguna` (`idpengguna`),  KEY `fkitem` (`iditem`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='transactin note';

View is very useful in programs that will be made because just create the view, then generate connections from Netbeans, give @ Id on its primary key and ready for use.
Warning:Avoid the join between the view (eg: view1 join view2) and each view is expected to have a unique value as its Id (primary key can be retrieved from among the tables used).

CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `pfm`.`v_rekap` AS select sql_cache `t`.`idtransaksi` AS `idtransaksi`,`t`.`idpengguna` AS `idpengguna`,`t`.`nominal` AS `nominal`,month(`t`.`tgltransaksi`) AS `bulan`,year(`t`.`tgltransaksi`) AS `tahun`,`i`.`namaitem` AS `namaitem`,`i`.`alurkas` AS `alurkas` from (`pfm`.`transaksi` `t` join `pfm`.`item` `i` on((`i`.`iditem` = `t`.`iditem`)));

From the example above, used his idtransaksi as Id, drawn from the transaction table, because this column is the most unique amongst the other column.
And this is an example of the data:

insert into item values (1, 'gaji', 1);
insert into item values (2, 'non gaji', 1);
insert into item values (3, 'hutang', 1);
insert into item values (4, 'cicilan piutang', 1);
insert into item values (5, 'belanja', 0);
insert into item values (6, 'bayar tagihan', 0);
insert into item values (7, 'biaya pendidikan', 0);
insert into item values (8, 'piutang', 0);
insert into item values (9, 'cicilan hutang', 0);

insert into pengguna values (1, 'user', md5('user'));