Native Query pada DB4O

Native query merupakan pengembangan dari Object Query sebelumnya. Di mana object query sebelumnya memiliki banyak kekurangan diantaranya pengecekan kesalahan dan keamanan dari query.

Dimisalkan ada relationship antar object, yaitu:

Diasumsikan bahwa seorang Person memiliki suatu Account di suatu bank. Maka relationship yang akan dibuat akan membentuk dua class dengan attribute seperti berikut:

Class Account



public class Account {

private double amount;

private double credit;

}

Class Person

1. public class Person {
2. private Account account;
3. private String name;
4. private int age;
5. }

Misalkan Object Personnya sebagai berikut:

Dari data table tersebut, Tuliskan Query di bawah ini dengan perintah SQL (untuk DBMS) dan Native Query (Object Database Db4o):

1. Tampilkan semua kolom dari Tabel tersebut
2. Tampilkan jumlah amount dan credit dari accountnya Linda.
3. Tampilkan nama serta jumlah credit yang kurang dari 100.
4. Tampilkan nama serta jumlah amount atau credit yang kurang dari 100.
5. Tampilkan nama yang umurnya antara 40-50 dan creditnya diatas 1000
6. Tampilkan nama serta umurnya yang Amount sama dengan nasabah Heinz.

Karena kita menggunakan Object Database Db4o maka tabel tersebut dibuat sebagai suatu Class.

Berikut ini kode program dari Class Person:

1. package bank;
2. /**
3. *
4. * @author Team_dakoop
5. */
6. public class Person {
7. private Account account;
8. private String Name;
9. private int Age;
10. public Person(String name, int age){
11. this.Name = name;
12. this.Age = age;
13. this.account=null;
14. }
15. public String getName(){
16. return Name;
17. }
18. public int getAge(){
19. return Age;
20. }
21. public void setAccount(Account account){
22. this.account = account;
23. }
24. public Account getAccount(){
25. return account;
26. }
27. public String toString(){
28. return Name+"/"+Age+"/"+account;
29. }
30. }

Berikut ini kode program dari Class Account:

1. package bank;
2. /**
3. *
4. * @author Team_dakoop
5. */
6. public class Account {
7. private double Amount;
8. private double Credit;
9. public Account(double amount, double credit){
10. this.Amount=amount;
11. this.Credit = credit;
12. }
13. public double getAmount(){
14. return Amount;
15. }
16. public double getCredit(){
17. return Credit;
18. }
19. public String toString(){
20. return Amount+"/"+Credit;
21. }
22. }

Solusi Query ke-1

Tampilkan semua kolom dari Tabel tersebut

Perintah SQL dalam DBMS:

SELECT *
FROM Person;

Object Query dalam Db4o:

1. ObjectSet persons = db.query(new Predicate()
2. {
3. public boolean match(Person person) {
4. return true;
5. }
6. });

Hasil Query ke-1

1. Query 1 : SELECT * FROM Person;
2. Hasil : 6
3. Rossi/28/1500.0/500.0
4. Tony/35/2500.0/300.0
5. James/42/1250.0/50.0
6. Heinz/20/1500.0/500.0
7. Eric/22/150.0/0.0
8. Linda/45/5000.0/1500.0

Solusi Query ke-2

Tampilkan jumlah amount dan credit dari accountnya Linda.

Perintah SQL dalam DBMS:

SELECT Amount, Credit
FROM Person
WHERE Name = ‘Linda’;

Object Query dalam Db4o:

1. ObjectSet persons = db.query(new Predicate()
2. {
3. public boolean match(Person person) {
4. return person.getName().equals("Linda");
5. }
6. });

Hasil Query ke-2

Query 2 : SELECT Amount,Credit FROM Person WHERE Name = 'Linda';
Hasil : 1 record
5000.0/1500.0

Solusi Query ke-3

Tampilkan nama serta jumlah credit yang kurang dari 100.

Perintah SQL dalam DBMS:

SELECT Name, Credit
FROM Person
WHERE Credit <>

Object Query dalam Db4o:

1. ObjectSet persons = db.query(new Predicate()
2. {
3. public boolean match(Person person) {
4. return person.getAccount().getCredit() <>
5. }
6. });

Hasil Query ke-3

Query 3 : SELECT Name, Credit FROM Person WHERE Credit <>

Solusi Query ke-4

Tampilkan nama serta jumlah amount atau credit yang kurang dari 100.

Perintah SQL dalam DBMS:

SELECT Name, Amount, Credit
FROM Person
WHERE Amount <>

Object Query dalam Db4o:

1. ObjectSet persons = db.query(new Predicate()
2. {
3. public boolean match(Person person) {
4. return person.getAccount().getAmount() <>
5. person.getAccount().getCredit() <>
6. }
7. });

Hasil Query ke-4

Query 4 : SELECT Name, Amount, Credit FROM Person WHERE Amount <>

Solusi Query ke-5

Tampilkan nama yang umurnya antara 40-50 dan creditnya diatas 1000

Perintah SQL dalam DBMS:

SELECT Name
FROM Person
WHERE (Age between 40 AND 50) AND (Credit > 1000);

Object Query dalam Db4o:

1. ObjectSet persons = db.query(new Predicate()
2. {
3. public boolean match(Person person) {
4. return person.getAge() >= 40
5. && person.getAge() <= 50
6. && person.getAccount().getCredit() > 1000.0;
7. }
8. });

Hasil Query ke-5

Query 5 : SELECT Name FROM Person WHERE (Age Between 40 AND 50) AND (Credit > 1000);
Hasil : 1 record
Linda

Solusi Query ke-6

Tampilkan nama serta umurnya yang Amount sama dengan nasabah Heinz.

Perintah SQL dalam DBMS:

SELECT Name, Age
FROM Person
WHERE Not( Name = ‘Heinz’) AND Amount IN (
SELECT Amount
FROM Person
WHERE Name = ‘Heinz’);

Object Query dalam Db4o:

1. ObjectSet Subpersons = db.query(new Predicate()
2. {
3. public boolean match(Person person) {
4. return person.getName().equals("Rossi");
5. }
6. });
7. while (Subpersons.hasNext())
8. {
9. final Person persons = (Person)Subpersons.next();
10. ObjectSet Objperson = db.query(new Predicate()
11. {
12. public boolean match(Person p) {
13. return p.getAccount().getAmount() ==
14. persons.getAccount().getAmount()
15. && p.getName() != "Heinz" ;
16. }
17. });
18. }

Hasil Query ke-6

Query 6 :
SELECT Name, Age
FROM Person
WHERE Not( Name = ‘Heinz’) AND Amount IN (
SELECT Amount
FROM Person
WHERE Name = ‘Heinz’);
Hasil : 1 record
Rossi

Full Source Code

1. package bank;
2. 
   
3. /**
4. *
5. * @author Team_dakoop
6. */
7. import com.db4o.*;
8. import com.db4o.query.*;
9. import java.io.File;
10. 
    
11. public class Main {
12. 
    
13. /**
14. * @param args the command line arguments
15. */
16. public static void main(String[] args) {
17. // TODO code application logic here
18. InsertData();
19. Query1();
20. Query2();
21. Query3();
22. Query4();
23. Query5();
24. Query6();
25. }
26. public static void InsertData(){
27. new File("Person.yap").delete();
28. ObjectContainer db = Db4o.openFile("Person.yap");
29. Account a1 = new Account(1500, 500);
30. Person p1 = new Person("Heinz", 20);
31. p1.setAccount(a1);
32. Account a2 = new Account(5000, 1500);
33. Person p2 = new Person("Linda", 45);
34. p2.setAccount(a2);
35. Person p3 = new Person("Eric", 22);
36. Account a3 = new Account(150, 0);
37. p3.setAccount(a3);
38. Person p4 = new Person("James", 42);
39. Account a4 = new Account(1250, 50);
40. p4.setAccount(a4);
41. Person p5 = new Person("Tony", 35);
42. Account a5 = new Account(2500, 300);
43. p5.setAccount(a5);
44. Person p6 = new Person("Rossi", 28);
45. Account a6 = new Account(1500, 500);
46. p6.setAccount(a6);
47. db.set(p1);db.set(p2);db.set(p3);db.set(p4);db.set(p5);db.set(p6);
48. db.close();
49. System.out.println("Data Person sudah tersimpan");
50. }
51. public static void Query1(){
52. ObjectContainer db = Db4o.openFile("Person.yap");
53. ObjectSet persons = db.query(new Predicate()
54. {
55. public boolean match(Person person) {
56. return true;
57. }
58. });
59. System.out.println("Query 1 : SELECT * FROM Person;");
60. System.out.println("Hasil : " + persons.size());
61. while (persons.hasNext()){
62. System.out.println(persons.next());
63. }
64. db.close();
65. }
66. 
    
67. public static void Query2(){
68. ObjectContainer db = Db4o.openFile("Person.yap");
69. ObjectSet persons = db.query(new Predicate()
70. {
71. public boolean match(Person person) {
72. return person.getName().equals("Linda");
73. }
74. });
75. System.out.println("Query 2 : SELECT Amount,Credit FROM Person WHERE Name = 'Linda';");
76. System.out.println("Hasil : " + persons.size()+" record");
77. while (persons.hasNext()){
78. Person person = (Person)persons.next();
79. System.out.println(person.getAccount());
80. }
81. db.close();
82. }
83. 
    
84. 
    
85. public static void Query3(){
86. ObjectContainer db = Db4o.openFile("Person.yap");
87. ObjectSet persons = db.query(new Predicate()
88. {
89. public boolean match(Person person) {
90. return person.getAccount().getCredit() <>
91. }
92. });
93. System.out.println("Query 3 : SELECT Name, Credit FROM Person WHERE Credit <>
94. System.out.println("Hasil : " + persons.size()+" record");
95. while (persons.hasNext()){
96. Person person = (Person)persons.next();
97. System.out.println(person.getName()+"/"+ person.getAccount().getCredit());
98. }
99. db.close();
100. }
101. public static void Query4(){
102. ObjectContainer db = Db4o.openFile("Person.yap");
103. ObjectSet persons = db.query(new Predicate()
104. {
105. public boolean match(Person person) {
106. return person.getAccount().getAmount() <>
107. }
108. });
109. System.out.println("Query 4 : SELECT Name, Amount, Credit FROM Person WHERE Amount <>
110. System.out.println("Hasil : " + persons.size()+" record");
111. while (persons.hasNext()){
112. Person person = (Person)persons.next();
113. System.out.println(person.getName()+"/"+ person.getAccount());
114. }
115. db.close();
116. }
117. 
     
118. public static void Query5(){
119. ObjectContainer db = Db4o.openFile("Person.yap");
120. ObjectSet persons = db.query(new Predicate()
121. {
122. public boolean match(Person person) {
123. return person.getAge() >= 40
124. && person.getAge() <= 50
125. && person.getAccount().getCredit() > 1000.0;
126. }
127. });
128. System.out.println("Query 5 : SELECT Name FROM Person WHERE (Age Between 40 AND 50) AND (Credit > 1000);");
129. System.out.println("Hasil : " + persons.size()+" record");
130. while (persons.hasNext()){
131. Person person = (Person)persons.next();
132. System.out.println(person.getName());
133. }
134. db.close();
135. }
136. public static void Query6(){
137. ObjectContainer db = Db4o.openFile("Person.yap");
138. ObjectSet Subpersons = db.query(new Predicate()
139. {
140. public boolean match(Person person) {
141. return person.getName().equals("Rossi");
142. }
143. });
144. while (Subpersons.hasNext())
145. {
146. final Person persons = (Person)Subpersons.next();
147. ObjectSet Objperson = db.query(new Predicate()
148. {
149. public boolean match(Person p) {
150. return p.getAccount().getAmount() == persons.getAccount().getAmount()
151. && p.getName() != "Heinz" ;
152. }
153. });
154. System.out.println("Query 6 :");
155. System.out.println("SELECT Name, Age");
156. System.out.println("FROM Person");
157. System.out.println("WHERE Not( Name = ‘Heinz’) AND Amount IN (");
158. System.out.println(" SELECT Amount");
159. System.out.println(" FROM Person");
160. System.out.println(" WHERE Name = ‘Heinz’);");
161. System.out.println("Hasil : " + Objperson.size()+" record");
162. while (Objperson.hasNext()){
163. Person person = (Person)Objperson.next();
164. System.out.println(person.getName());
165. }
166. }
167. db.close();
168. }
169. }

Referensi

Db4o-5.2 Tutorial, db4objects Inc., USA

Irwanto, Djon., 2007, Membangun Object Oriented Software dengan Java dan Object Database, PT Elex Media Komputindo, Jakarta