Database Lesson 5 - Database + Java integration using iBatis (part 2)

Haha, may nakalimutan ulit ako. Kelangan nga pala natin idownload yung iBatis na class. Anyway, madali lang naman yun, idagdag ko na lang dito:

1. Go here, and download the iBatis files by clicking on 'Download iBATIS Java 2.3.4'
2. Extract kahit san. :)

Now, mag-gagawa uli tayo ng isa pang SQL file:

1. Right click sa 'com.pinoyjavatutorial.dao.sql' na package
2. choose 'New' -> 'Other' -> 'XML' -> 'XML' -> 'Next'
3. Sa 'Filename:', type in 'sqlMapConfig.xml' and click 'Finish'
5. Meron dun sa bandang baba (just on top nung mga tabs na 'Problems', 'Javadoc', 'Declaration' etc.)na dalawang tabs: 'Design' at 'Source'. Paki click nyo yung source.
6. To yung makikita nyo na code:

<?xml version="1.0" encoding="UTF-8" ?>

7. Now, idagdag nyo to:

<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="org.apache.derby.jdbc.ClientDriver"/>
<property name="JDBC.ConnectionURL" value="jdbc:derby://localhost:1527/sampleDB"/>
<property name="JDBC.Username" value="ako"/>
<property name="JDBC.Password" value="wala"/>
</dataSource>
</transactionManager>

<sqlMap resource="com/pinoyjavatutorial/dao/sql/BrandTable.xml" />

</sqlMapConfig>

8. Sa 'Package Explorer' na tab, right click -> New -> Folder
9. Type in 'lib' sa 'Folder name: ' the field and click 'Finish'
10. Punta kayo kung san nyo inextract ang Apache derby. Sa loob ng folder na 'lib', click on 'derbyclient.jar', copy (ctrl + c) then balik sa window ng eclipse. Sa click nyo yung newly created na 'lib' na folder, press 'ctrl+v' or kaya right click -> paste. Same procedure para sa iBatis na class, punta kayo kung san inextract ang iBatis. Sa loob nung lib folder under kung san inextract ang iBatis, copy 'ibatis-2.3.4.726.jar' and paste it din sa 'lib' na folder ng eclipse.
11. Sa 'Package Explorer' uli, right click -> 'Properties' -> sa left side nung lumabas na window -> 'Java Build Path'. Then sa right side, yung sa 'Libraries' na tab, click 'Add Jars...'. Expand mo lang yung folder ng project natin until makarating ka sa 'lib' na folder. Press and hold 'shift' or 'ctrl' key, then click nyo yung 'derbyclient.jar' and 'ibatis-2.3.4.726.jar' and press 'Ok' button ng 'Jar Selection' at 'Properties for SampleDatabaseProject' na dialog boxes.
12. Now, the fun part, let's create the main class. Sa 'Package Explorer' ulit, right click on 'SampleDatabaseProject' -> New -> Class.
13. Sa 'Package: ' na field, type in 'com.pinoyjavatutorial.core' and sa 'Name: ' field, type in, 'MainClass'.
14. Click 'Finish'
15. Type in the following:

package com.pinoyjavatutorial.core;

import java.sql.SQLException;

import com.ibatis.sqlmap.client.SqlMapClient;
import com.pinoyjavatutorial.dao.BrandTableJavaObject;

public class MainClass {

public static void main(String[] args) {
SqlMapClient sqlMap = DBConn.getSqlMapInstance();
BrandTableJavaObject brandTable = null;

int id = 10;
try {
brandTable = (BrandTableJavaObject) sqlMap.queryForObject ("selectByID", id);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Phone details with ID = 10:");
System.out.println("Brand: " + brandTable.getBrand());
System.out.println("Model: " + brandTable.getModel());
}

}

16. Sa 'Package Explorer' ulit, right click on 'SampleDatabaseProject' -> New -> Class.
17. Sa 'Package: ' na field, type in 'com.pinoyjavatutorial.core', sa 'Name: ' field, type in, 'DBConn' and click 'Finish'
18. Type in the following:

package com.pinoyjavatutorial.core;

import java.io.IOException;
import java.io.Reader;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class DBConn{
private static final SqlMapClient sqlMap;

static {
try {
String resource = "com/pinoyjavatutorial/dao/sql/sqlMapConfig.xml";
Reader reader = Resources.getResourceAsReader (resource);
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException ("Error initializing DBConn class. Cause: " + e);
}
}
public static SqlMapClient getSqlMapInstance () {
return sqlMap;
}
}

Now, its time to run our program. Dapat yung 'MainClass.java' ang nasa harap na tab sa eclipse, sa menu bar, go to Run (nasa pagitan ito ng 'Project' at 'Window' - > Run As -> Java Application. Kung may lumabas na error at ang pinasimula nung lahat ng error ay ito:

java.sql.SQLNonTransientConnectionException: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused.

It means, patay yung database server natin na pina gawa ko sa inyo sa Lesson 3 steps 1-2. Anyway, para i-up yung server:

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

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

Now, ok na dapat yan. Alam nyo na for sure ang lalabas sa console ;)

Yung mga old school programmers na baka magtaka kasi ibang way to ng pag-access ng database. Yes, iba nga to. Sa industry kasi ito na ngayon ang uso, ang pag gamit ng persistence framework. Sa ibang time ko na yan explain kung ano ba yung persistence framework. Basically kaya naging iba kasi gumamit tayo ng iBatis (which is a persistence framework) instead na diretso sa codes ang integration ng Database and Java. Pero sa totoo lang, madami itong benefits. Lalo na if your working sa industry kasi madaling magbago ng query (nakalagay lang sa mga xml files), pag may changes sa database sa xml file ka lang pupunta para baguhin at di na hahanapin pa sa codes, kung team kayo kung gumawa, pwede na magconcentrate yung isang team sa mismong logic ng program at yung isang team sa configuration ng database. Kesa yung magkasama yung mga codes at query. Pag natapos na sa pag gawa, madaling mag integrate. At marami pang ibang benefits. Siguro kaya ito nauuso sa industry ngayon kasi nga mas madali sya. Mahirap lang sa start kasi my mga xml files pa pero once you get pass that or my partner ka, oks na. :)

Anyway, sa mga next lessons natin I'll teach you how to do delete and update kasi ngayon select lang ginamit natin. Then gagamit na tayo ng mga shortcut tools. If you noticed, we didn't use yung Apache Derby Eclipse plugin natin that I told you to download. Gagagamit din tayo ng iBator para maging madali at mabilis ang pag gawa ng mga xml files. As in we just need to run the tool at sya na bahala.

See you all sa next lesson natin which will be some time next Sunday. :)

Database Lesson 4 - Database + Java integration using iBatis (part 1)

Now since ok na ang development environment natin, balik coding tayo. We'll be using iBatis. Ok, so ano ba 'tong iBatis na to? iBatis is a persistence framework and helps in mapping of SQL databases to Java. Pwede din sya sa .Net and Ruby on Rails. In short, pinapadali nito ang pag setup ng communication/transactions between the database at Java.

Bale, para mas maintindihan, eto ang example:

Using yung Brandtable natin sa previous post, meron tayong table na ganito ang structure/database schema:

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

Ngayon, gagawa tayo ng Java object na katumbas ng table na yan:

com.pinoyjavatutorial.dao BrandTableJavaObject (id: int, brand: String, model: String)

Yung mapping ng ID, brand at model sa Brandtable at Java object natin ay gagawin natin sa sql. Miya papakita ko yun. Step by step muna tayo para walang mabigla.

First, gawin muna natin yung java object natin:
1. Run eclipse
2. Create tayo ng new project: File -> New -> Java Project -> . Kung wala sa option yung Java project, try nyo to: File -> New -> Other -> Java -> Java Project -> Next. If magtanong yung eclipse about 'Java Perspective', just click 'Yes'. Kung wala naman, ayos lang, next step na tayo.
3. At 'Project name:' field, type in 'SampleDatabaseProject' then click 'Finish'
4. Dun sa 'Project Explorer' na tab (nasa right side yun), right click on 'SampleDatabaseProject' then 'New' -> 'Class'
5. Sa 'Package:' field, type in 'com.pinoyjavatutorial.dao', sa 'Name' field naman, type 'BrandTableJavaObject'
6. Click 'Finish'
7. Eto ang codes ng BrandTableJavaObject:

package com.pinoyjavatutorial.dao;

public class BrandTableJavaObject {

private int id;
private String brand;
private String model;

public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public String getModel() {
return model;
}
public void setModel(String model) {
this.model = model;
}

}
8. Click save.

Ok, so ano ba tong class na to? Bale gumawa lang tayo ng Java object na merong tatlong variables (or in Java terms its fields or properties of a class) sa loob:

private int id;
private String brand;
private String model;

Tapos meron tayong mga methods na nag-ge-get (para makuha natin ang value) at set (para makapag set tayo ng laman) sa tatlong variables na ito. These methods are usually referred to as setters and getters methods. My shortcut sa eclipse para hindi na kayo mag type ng mga getters and setters na methods. Pero sa ibang tutorial ko na yun ilalagay.

Next, yung XML mappings:

1. Dun sa 'Project Explorer' na tab (nasa right side yun), right click on 'SampleDatabaseProject' then 'New' -> 'Package'
2. Sa 'Name' field, type in: 'com.pinoyjavatutorial.dao.sql' and click 'Finish'
3. Dun sa empty package na kakagawa lang natin, right click kayo dun then choose 'New' -> 'Other' -> 'XML' -> 'XML' -> 'Next'
4. Sa 'Filename:', type in 'BrandTable.xml' and click 'Finish'
5. Dun sa 'BrandTable.xml', meron nun sa bandang baba (just on top nung mga tabs na 'Problems', 'Javadoc', 'Declaration' etc.)na dalawang tabs: 'Design' at 'Source'. Paki click nyo yung source.
6. To yung makikita nyo na code:

<?xml version="1.0" encoding="UTF-8" ?>

Bale da-dagdagan natin yan para maging ganto:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="BrandTable">
<select id="selectByID"
resultClass="com.pinoyjavatutorial.dao.BrandTableJavaObject">
SELECT
ID as id,
BRAND as brand,
MODEL as model
FROM BRANDTABLE
WHERE ID = #value#
</select>
</sqlMap>

Medyo nagugulo yung spacing pag nag post ako. Probably because of the tags. Medyo tamad na kong mag lagay pa ng non-breaking space (&nbsp) kaya paki space bar nyo na lang para maayos yung hitsura. Kahit ano naman ang spacing nyan oks lang. Basta yung readable para sa inyo.

Ok, so ano ba tong mga codes na to? Line by line ko ulit ie-explain:

<?xml version="1.0" encoding="UTF-8" ?>
-XML version and encoding stuff lang to. Nothing much. Hayaan nyo lang yan.

<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
-Just declares about the doctype. Kahit di nyo na yan bigyan pansin sa ngayon.

<sqlMap namespace="BrandTable">
-It states na gagamit tayo ng namespace na "brandTable ang pangalan. Pag ginamit na natin miya, saka nyo mas maiintindihan.

<select id="selectByID" resultClass="com.pinoyjavatutorial.dao.BrandTableJavaObject">
SELECT
ID as id,
BRAND as brand,
MODEL as model
FROM BRANDTABLE
WHERE ID = #value#
</select>
-Ok, to na yung select statement natin. Bale binigyan natin sya ng name na 'selectbyID' at ang result e ilalagay nya sa kaninang class (BrandTableJavaObject) na ginawa din natin.

Magiging ganto ang hitsura ng eclipse nyo:
Just like my other screenshots, click nyo lang para mag full size yung screenshot.

Medyo mahaba na kaya I'll continue this to my next post. :)

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. :)

Database Lesson 2 - Additional Setup (System Variables)

Apologies for the delay; was quite busy lately. Anyway, tuloy na natin ang tutorial! :)

May kulang pa pala tayo sa environment, di pa naka configure yung Apache Derby na system variable at yung 'Path' na system variable din ay i-e-edit natin. Bale ganto ang gagawin:

1. Go to Control Panel -> System -> Advanced Tab -> Environment Variables.
2. Under 'System Variables', click 'New'
3. Sa 'Variable name:' type in 'DERBY_HOME' at sa 'Variable value:' yung path kung san mo inextract yung apache derby.
4. Click 'Ok'

Bale, magiging ganto ang hitsura:

Now, para ma-edit yung 'Path' na system variable:

5. Sa 'System Variable', hanapin nyo yung 'Path' then click 'Edit'
6. Sa dulo nung 'Variable value:' lagay nyo ';%DERBY_HOME%\bin\'
7. Click 'Ok' hanggang mawala lahat nung mga windows.

Parang ganto yung magiging hitsura ng 'Path' na 'System Variable':


8. Restart your PC. Sa ibang PC kasi minsan ok lang walang restart, minsan kelangan. Better irestart na lang natin.
9. Para maverify kung ok na ang installation, go to command promt (Start -> Run -> cmd) and type 'sysinfo'. Dapat lalabas yung 'Java Information', 'Derby Information' and 'Locale Information'.

Ok, alis lang muna ulit ako, pag uwi ko miya saka natin itutuloy. Let me know pag may hindi nakasunod. :)

Database Lesson 1 - Applications and Installation Steps

I'm suppose to write about the next Java basics tutorial but decided to make a quick and dirty tutorial on making database work on java. This is because quite a number of people are asking me for this.

For our setup, we'll be using the following:

1. Eclipse - our usual IDE of choice. Of course you can use Netbeans or any other IDE but I prefer eclipse for a number of reason I stated on my previous posts.
2. Apache Derby - I can hear chatters asking why apache derby and not mysql or some other high profile DB application. Well, you can also use yung ibang database apps pero for this tutorial, apache derby na lang muna tayo since madaling mag setup ng apache derby. Isa pa, kung small scale lang naman na mga applications, apache derby is more than sufficient. Maliit lang pati kumain ng memory ang apache derby (around 2MB lang foot print nya) which makes it great and perfect fit for small applications.
3. Apache Derby Plug-in - eclipse plugin para maging mas madali ang pag develop.

Installation:
1. Download and extract eclipse as usual.
2. On the apache derby download page, download db-derby-10.4.2.0-bin.zip and extract it to a folder.
3. To install the apache derby plugins, from the apache derby download page just download derby_core_plugin_10.4.2.zip and derby_ui_plugin_1.1.2.zip. Make sure eclipse isn't running and extract the folders from the two zip files to the plugins folder of eclipse.

Now our environment is ready. I'm a little sleepy right now so I'll just post the next steps tomorrow. :)