?a?ep?st?

1
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
????af???? ???????
???446-???????µ??e? ??se?? ?ed?µ????
2
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
What is Derby???

• Derby is a relational database
• implemented completely in Java.
• It has a small footprint that allows it to be
  easily embedded into any Java
  application.

???446-???????µ??e? ??se?? ?ed?µ????
3
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
History Lesson

• Cloudscape Inc .founded in 1996 to develop Java
  database technology
• The first release called JBMS (1997)
• Renamed Cloudscape
• Bought by Informix software Inc.(1999)
• Bought by IBM (2001),IBMs Java products and
  middleware used.

???446-???????µ??e? ??se?? ?ed?µ????
4
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
History Lesson

• IBM contributes code to Apache Software
  Foundation
• Renamed DERBY !!!
• Sun joins the Derby project
• Sun packaged Derby in the JDK (6/12/2006) as
  JavaDB
• IBM announced marketing and support withdrawal
  for the Cloudscape product (3/2007)

???446-???????µ??e? ??se?? ?ed?µ????
5
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Derby Advantages

• Derby has a small footprint About 2 megabytes
  for the base engine and embedded JDBC driver.
• Derby is based on the Java, JDBC, and SQL
  standards.
• Derby provides an embedded JDBC driver that
  lets you embed Derby in any Java-based solution.
• Derby is easy to install, deploy, and use.
• Databases are easily transferable to other
  systems.

???446-???????µ??e? ??se?? ?ed?µ????
6
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Derby architecture Embedded
???446-???????µ??e? ??se?? ?ed?µ????
7
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Derby architecture Client-server
???446-???????µ??e? ??se?? ?ed?µ????
8
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Install µ?s?... tutorial

• C\gt set DERBY_INSTALLC\Apache\db-derby-10.4.1.3
  -bin
• C\gt set CLASSPATHDERBY_INSTALL\lib\derby.jar
  DERBY_INSTALL\lib\derbytools.jar.
• C\gt cd DERBY_INSTALL\bin
• C\Apache\db-derby-10.4.1.3-bin\bingt
  setEmbeddedCP.bat
• java org.apache.derby.tools.sysinfo

???446-???????µ??e? ??se?? ?ed?µ????
9
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
IJ
ij is an interactive SQL scripting tool that
comes with Derby
???446-???????µ??e? ??se?? ?ed?µ????
10
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Example of java example running
???446-???????µ??e? ??se?? ?ed?µ????
11
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Netbeans-JavaDB
???446-???????µ??e? ??se?? ?ed?µ????
12
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Netbeans-JavaDB
???446-???????µ??e? ??se?? ?ed?µ????
13
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Java code
public class University private static
String dbURL "jdbcderby//localhost1527/univer
sity2createtrueusergianninospassword"
private static String tableName "COURSE"
// jdbc Connection private static
Connection conn null private static
Statement stmt null public static void
main(String args)
createConnection() insertCourse("EPL323",
"Compilers", "P.Trancoso","Computer-Science")
selectCourse() shutdown()
???446-???????µ??e? ??se?? ?ed?µ????
14
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Java code
private static void insertCourse(String id,
String title, String professor,String
department) try
stmt conn.createStatement()
stmt.execute("insert into " tableName "
values ('" id "','"
title "','" professor "','" department
"')") stmt.close()
catch (SQLException sqlExcept)
sqlExcept.printStackTrace()
???446-???????µ??e? ??se?? ?ed?µ????
15
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Java code
private static void selectCourse() try
stmt conn.createStatement()
ResultSet results stmt.executeQuery("s
elect from " tableName)
ResultSetMetaData rsmd results.getMetaData()
int numberCols rsmd.getColumnCount()
for (int i1 iltnumberCols i)
//print Column Names
System.out.print(rsmd.getColumnLabel(i)"\t\t
") System.out.println("
\n------------------------------------------------
-") while(results.next())
String id results.getString(1)
String title results.getString(2)
String professor results.getString(3)
String department
results.getString(4)
System.out.println(id "\t\t" title "\t\t"
professor "\t\t" department )
results.close()
stmt.close() catch (SQLException
sqlExcept) sqlExcept.printStackTrace(
)
???446-???????µ??e? ??se?? ?ed?µ????
16
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Test Configuration
???446-???????µ??e? ??se?? ?ed?µ????
17
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Database Buffer
???446-???????µ??e? ??se?? ?ed?µ????
18
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Performance Tips

• Use Prepared Statements
• Avoid Table Scans

???446-???????µ??e? ??se?? ?ed?µ????
19
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Performance EvaluationMySQL, PostgreSQL and
Derby
???446-???????µ??e? ??se?? ?ed?µ????
20
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Database Configurations
???446-???????µ??e? ??se?? ?ed?µ????
21
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Throughput TPC-B like load
???446-???????µ??e? ??se?? ?ed?µ????
22
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Throughput Single-record Select
???446-???????µ??e? ??se?? ?ed?µ????
23
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Observations

• Derby outperforms MySQL on disk-based databases
• MySQL performs better on small main-memory
• Databases
• PostgreSQL performs best on read-only
  databases, and has lowest throughput on
  update-intensive databases

???446-???????µ??e? ??se?? ?ed?µ????
24
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Conclusion

• Ensure the integrity and recoverability of data
• Portable
• Good solution for a programmer

???446-???????µ??e? ??se?? ?ed?µ????
25
?a?ep?st?µ?? ??p??? ?µ?µa ?????f??????
Thanks for your attention THE
END!!!! Any questions???
???370-????te?t????? ?p?????st??