Database Lesson 3 - Setting up the Sample Database Table

Ok, so meron na tayong mga configurations sa environment. So gawa na tayo ng database mismo sa derby. Para maging simple lang, isang table lang na may tatlong column: primary key (ID), brand at model ng cellphone. Eto yung steps sa pagcreate ng database:

1. Run command prompt (cmd)
2. Type in the following:

cd %DERBY_HOME%
cd lib
java -jar derbyrun.jar server start

May lalabas dun na:

Security manager installed using the Basic server security policy.

Then, magbukas kayo ng bagong command prompt at type nyo tong mga to. Press enter sa command prompt after each line:

ij
CONNECT 'jdbc:derby://localhost:1527/sampleDB;create=true' user 'ako' password 'wala';
CREATE TABLE BRANDTABLE(ID INT PRIMARY KEY,
BRAND VARCHAR(20), MODEL VARCHAR(12));
INSERT INTO BRANDTABLE VALUES (10,'Nokia', '5110'),
(20,'Sony Ericsson', 'P1i'),(30,'HTC', 'Magic');

to check kung nacreate yung table at records, type in:

SELECT * FROM BRANDTABLE;

Bale magiging ganto ang window nyo:

Explain ko na ngayon yung derby commands natin:

ij
- this invokes the derby console

CONNECT 'jdbc:derby:sampleDB;create=true' user 'ako' password 'wala';
-this means that you are making a connection to the derby server using a jdbc driver. yung name ng database ay 'sampleDB'. Syempre pwede yan paltan ng kung ano ang gusto mo. Yung 'create=true', ibig sabihin nito, magke-create yung server ng database incase wala pang database na ganun. Which means, pag created na yung db, ok lang na wala na nung 'create=true'. So magiging ganto na yang command natin next time na magko-connect tayo:

CONNECT 'jdbc:derby:sampleDB;' user 'ako' password 'wala';

Yung user 'ako' and password 'wala' ay yung username and password para sa database na ginawa natin. Syempre pa, pwede yan paltan.

CREATE TABLE BRANDTABLE(ID INT PRIMARY KEY,
- Creates a table named 'BRANDTABLE'. Sa table na yun, merong column named 'ID' na integer type yung data type nya which means yung ilalagay sa kanya ay integer lang dapat tapos primary key din sya.

BRAND VARCHAR(20), MODEL VARCHAR(12));
- Actually kaduktong to nung previous line. Kasi like other SQL programs, yung isang line sa derby, pwede mo hatiin into multiple lines. Malalaman ng console na isang line lang sila gawa ng semicolon (;). Parang sa java, ang

System.out.println("hello");

ay same lang nito:

System.
out
.
println("hello");

Kasi nga, white space is ignore ng compiler. Basta yung semicolon indicates end of statement. Same sa mga sql programs like derby, white space is ignored din which is great kasi pag mahaba yung command, pwedeng putol-putulin kagaya ng ginawa natin. Hence, ito:

CREATE TABLE BRANDTABLE(ID INT PRIMARY KEY,
BRAND VARCHAR(20), MODEL VARCHAR(12));

at ito:

CREATE TABLE BRANDTABLE(ID INT PRIMARY KEY, BRAND VARCHAR(20), MODEL VARCHAR(12));

ay iisa lang. :) Anyway, back to the topic, yung

BRAND VARCHAR(20), MODEL VARCHAR(12));

means, gagawa ng column named 'Brand' which can contain upto 20 characters and another column named 'Model' which can contain upto 12 characters.

Since alam na natin na yung white space is ignored at pwedeng putol-putulin ang mga commands kasi nga ignored naman ang white space, pagsamahin na natin sila ngayon since pareho lang nga kung putol-putol ang commands or magkakasama sa isang line:

INSERT INTO BRANDTABLE VALUES (10,'Nokia', '5110'),(20,'Sony Ericsson', 'P1i'),(30,'HTC', 'Magic');

-Nagpa-populate sa 'Brandtable' natin. I don't think kelangan pa i-explain pa to. :)

Its good to know kung ano na yung contents ng table and to do that, eto yung command:

SELECT * FROM BRANDTABLE;

Ok, so far ok na ang config natin sa environment at meron na tayong database. Next post, integration na mismo sa java code natin. :)

In any case nawala kayo, just comment below. :)