Td Mxc Mysqljavadb Srinivas

Java and Databases: A Developer Perspective Raghavan “Rags” N. Srinivas [email protected] Sun Microsystems 1

Speakers > Rags > CTO, Technology Evangelism > Working on Databases for well over 2 decades starting with dbase

2

Goal of the Talk

Learn about MySQL and Sun Java DB from a Developer Perspective

3

Agenda • • • •

MySQL Sun Java DB Which Database? Resources

4

MySQL History • • • • •

1982: First database code is written 1995: First pure MySQL code 1996: Public release & over 1000 downloads 2001: 'Real' company started, First Investment, CEO joins 2008: Sun acquires MySQL for 1 Billion USD

5

The Seeds of Success • Solved a common problem • Commercial agenda from the start • Developed for practical production use > We needed MySQL for web apps and so

did others! • Few features but implement them fast & stable > Repeatable bugs went to the test suite > We valued speed more than features 6

The 15-min rule • Easy to install and use > The 15 Minutes rule: Install and try in less than

15 min > Binaries for many platforms (portable source)

• Documentation (in 'special' English at first)

7

Innovation: Pluggable Storage Engines

• Row Level Locking versus Page level locking • In MySQL you can choose what fits your application without changing database, rewriting your application etc • That is because the storage part in MySQL is pluggable

8

MySQL Internal Architecture

9

Small? Innovations • Innovation in OpenSource tends to be lots of small but very useful ideas > SELECT stuff FROM table LIMIT 10 > The idea for this came from the PHP language founder > Extremely common in web applications > Now spread to other databases

• A web application connects to the database much more than a traditional one > MySQL has always had much faster connect

time than traditional databases 10

● ● ● ● ● ●

●

Fastest & most Popular Open Source Database Complete relational database engine Embedded, Cluster offering Dual-Licensing and The GPL Supported by NetBeans™, Eclipse Runs on more than 20 platforms, Bundled in Solaris. Sun acquired MySQL 2008 MySQL and Java DB for Java Developers

11

Characteristics ● ● ● ● ● ● ●

Multi-User, Multi-Threaded, Multi-Process Pluggable Storage Engine Architecture Full Transactional Support, MVCC No-Hassle, Self Managing Database Small Footprint – Great performance Complete Data Protection Application Development Strengths

2008 MySQL and Java DB for Java Developers

12

JDBC Drivers ●

Connector/J 5.1 ● ● ●

●

Type IV pure JDBC Driver JDBC 4.0 Support XML Processing

Connector/MXJ 5.x ●

● ●

Java Utility package for deploying and managing a MySQL database. 5.0.6 Recently released Requires Connector/J 5.x 2008 MySQL and Java DB for Java Developers

13

5.1 – What's new ? ● ● ● ● ● ● ●

Partitioning Row-based Replication PlugIn API – Creation of full-text parser plugins. XML & Xpath Support Event Scheduler mysqlslap Connector/J 5.1 ● ● ●

Ease of development Support for XML Processing Support for JDBC 4.0 NCHAR, NVARCHAR, NCLOB 2008 MySQL and Java DB for Java Developers

14

Java & MXJ ● C ● C#/.Net • ODBC ● PHP ● Python ● Ruby ● Perl ● C++ ● Delphi ● Objective C ● Visual Basic ● Smalltalk ● Pascal ● ADA ● APL ● Lasso ●

• Eiffel • Haskell • Erlang • Curl • Forth • Slang • LUA ● Pike ● Rexx ● Dylan ● Common Lisp ● Scheme ● Gauche ● Guile ● Mathlab ● TCL ● Fortran

• Also new direct MySQL Cluster connectors • NDB-Java • NDB-Python • NDB-Ruby • NDB-Perl • NDB-Lua • NDB-C# (Mono) • NDB-PHP (direct) • And even Cobol!

Bold maintained by MySQL AB, All others by our community! 15

Agenda • • • •

MySQL Sun Java DB Which Database? Resources

16

What is JavaDB? • Sun supported version of Apache Derby > All development done with the Apache Derby community

• Complete relational database engine • 100% written in Java • Small footprint > 2MB of JAR file

• Runs on JavaEE, JavaSE, CDC > Database works across all these platform

• Multi-tiered design for different use cases 17

A Brief History of JavaDB Cloudscape

Purchase 1999

Infor mix

Purchase 2001

2004 Open sourced

19 96 Contribute, branding, support

IB M

2006

18

Main Features – 1 • Multiuser relational database engine • Embeddable or as standalone server • Standards > SQL92/99/2003, JDBC 4.0 (type 4 driver), DRDA level 7

compliant

• Full SQL support > Foreign keys, stored procedure,views, triggers > Subqueries in WHERE and FROM clause > Build-in functions for string, date/time, aggregration, etc. 19

Main Features – 2 • Fully ACID compliant > Full transaction isolation support

• Two phase commit (XA) • Database encryption and crash recovery • Administrative tools for online backup, examining database, etc

20

What JavaDB is Not • A forked of Apache Derby > Same binaries as Apache Derby > All work is done in with the Apache Derby community

• An object database > Its a relational database > Can be used with JPA, Hibernate, JDO, etc

• Part of the JavaSE 6 standard > Only in Sun's JavaSE 6 SDK distribution

21

Where Can I Get JavaDB? • Download it from the JavaDB site > http://developers.sun.com/javadb/downloads/index.jsp

• JavaSE 6 JDK > For testing and for embedding

• NetBeans > And all previous incarnations of Sun's developer tools

• Glassfish Version 2 • Apache Derby > http://db.apache.org/derby/ 22

Deployment Architecture

23

Standalone Database Server Application JDBC

JVM

Application

Network Server JDBC

Application JDBC

SQL Access Storage Buffer

Log and data 24

JDBC

As A Standalone Server • Runs on its own Java VM > In client server mode

• Administrative tools control > Startup, shutdown, ping, statistics, etc

• Supports SSL and XA • No practical limit to the database size > Currently tested up to about 700GB > Plan for 1TB test

25

Embeded Database

Java
Virtual
Machine
 (JVM™)

• Becomes part of application Applicati on JDBC SQL Access Storage Buffer

Log and Data

> Include derby.jar in your

CLASSPATH

• Single JavaDB engine instance per Java VM • Can be embedded in web page as local storage • Multiuser and thread-safe • Database can be read-only > In a JAR file on USB drive 26

Middle Tier as Cache • Enbedded in the middle tier • Act as a front end cache for backend enterprise DB • Advantage

Web Server

Servlet

> No network overhead > Data locality

JDBC

• Currently no build in datadata synchronization

JavaDB

> Sequoia > Daffodil 27

Databa se

Accessing JavaDB from Applications

28

JDBC 4.0 Features – 1 • Autoloading of JDBC drivers > Uses service provider mechanism to locate drives > No more Class.forName(“database.driver”)

• ResultSet is more flexible > Scrollabe and updatable

Result
rs
=
stmt.executeQuery(“select...”); rs.absolute(5);
 rs.updateString("NAME",
"AINSWORTH");
 rs.updateRow();

• Better support for CLOB, BLOB, ARRAY and STRUCT > conn.createBlob() to create a BLOB object

29

JDBC 4.0 Features – 2 • XML datatype support DocumentBuilder
parser
=
DocumentBuilderFactory... Document
xmlDoc
=
parser.parse(new
File(“file.xml”); SQLXML
sqlXml
=
conn.createSQLXML(); OutputStream
os
=
sqlXml.setBinaryStream(); Transformer
trans
=
TransformerFactory.... trans.transform(xmlDoc,
new
StreamResult(os)); //insert
data
into
database sqlXml.free();

• RowId support for records that do not have unique

identifiers

> Specific to data source > Use getRowIdLifetime() from DatabaseMetaData to 30

JDBC 4.0 Features – 3 • Chained exception handling try
{ //SQL
statement ... }
catch
(SQLException
ex)
{ for
(Throwable
t:
ex)
{ for
(StackTraceElement
ste:
t.getStackTrace()) if
(ste.getClassName().startsWith(“org.fred”) System.err.println(ste); }

• Access to vendor specific implementations Class
kls
=
Class.forName(“oracle.jdbc.OracleStatement”); if
(stmt.isWrapperFor(klass))
{ OracleStatement
os
=
(OracleStatement)stmt.unwrap(kls); 31

JavaDB JDBC Driver • Type 4 driver – pure Java • 3 types of JDBC URL > Connecting to server database > Connecting to an in-process database > Connecting to a read only database in JAR or ZIP file

32

Network Driver • Use to connect to a server instance of JavaDB • Exactly the same as embedded JDBC URL string > Now just add server and port > jdbc:derby://server:1527/MyDatabase;create=true

33

Embedded JDBC Driver • Embedded driver > Embedded driver comes bundled with JavaDB

• Do not need to explicitly load drivers with JavaSE 6 > Class.forName("org.apache.derby.jdbc.EmbeddedDriver")

• Start, stop, create database > Start > jdbc:derby:MyDatabase;create=true

> Start and create > jdbc:derby:MyDatabase;create=true

> Stop > jdbc:derby:MyDatabase;shutdown=true

34

Read Only Database • Database in a ZIP or JAR file are read-only > Multiple database per archive

• Accessing database in a JAR or ZIP > jdbc:derby:jar:(path_to_archive)path_within_archive > jdbc:derby:jar:(/opt/dbs.jar)resources/employees

• Accessing database in JAR in CLASSPATH > jdbc:derby:/path_within_archive > jdbc:derby:classpath:/path_within_archive > jdbc:derby:/resources/employees > jdbc:derby:classpath:/resources/employees

35

Common URL Connection Attributes • create=boolean > Creates the database • shutdown=boolean > Shuts down the database • user=;password=<password> • createFrom=<path>;restoreFrom=<path> > Creates or restore from the backup provided in the path • dataEncryption=true;bootPassword= > Creates or boot and encrypted database > See Derby Developer's Guide for other details > http://db.apache.org/derby/manuals/ 36

Accessing Java Methods • Can create JavaDB stored procedures and functions in Java • Use CREATE
PROCEDURE and CREATE
 FUNCTION, with the following > LANGUAGE
JAVA > PARAMETER
STYLE
JAVA > EXTERNAL
NAME
Classname.methodName

• Invoked with CALL

37

A Java sendMail Method public
static
int
sendMail(String
toAddress ,
String
subject,
String
content)
{





recipient
=
new
InternetAddress(toAddress); ... msg
=
new
MimeMessage(session); msg.setFrom(from); msg.setSubject(subject); msg.setText(content); msg.addRecipient(Message.RecipientType.TO ,
recipient); Transport.send(msg);

38

Using a Java Method in Trigger CREATE
FUNCTION
SEND_MAIL( TO_ADDRESS
VARCHAR(320), SUBJECT
VARCHAR(320), BODY
VARCHAR(32000))
RETURNS
INT LANGUAGE
JAVA
PARAMETER
STYLE
JAVA
NO
SQL EXTERNAL
NAME
'SendMail.sendMail'); --
Send a Welcome e-mail when new customers are added. CREATE
TRIGGER
WELCOME_CUSTOMER AFTER
INSERT
ON
CUSTOMER
REFERENCING
new_table
AS
newtab FOR
EACH
STATEMENT
MODE
DB2SQL SELECT
SEND_MAIL(c.email,
'Welcome
to
AcmeWidgets' ,
M.EMAIL_TEXT) FROM
newtab
C,
MAILINGS
M WHERE
C.TYPE
=
M.CUST_TYPE
AND
M.OFFER_TYPE
=
'welcome'

39

Application Managed Entity Manager • JPA provides an object view of the underlying data > Uses annotations to define model in Java classes

• Used a container managed or application managed environment • Application managed – the application is responsible for creating the EntityManager > Typically used in JavaSE and Servlets

• Key to these is configuring persistence.xml

40

persistence.xmlwith TopLink 

 



 <provider>oracle.toplink.essentials.ejb.cmp3.EntityManagerFactoryProvider 



entity.Customer 



entity.Order 



<properties> 





<property
name="toplink.jdbc.url" value="jdbc:derby://localhost/testDB"/> 





<property
name="toplink.jdbc.user"
value="APP"/> 





<property
name="toplink.jdbc.driver"
 value="org.apache.derby.jdbc.ClientDriver"/> 





<property
name="toplink.jdbc.password"
value="APP"/> 



 



41

Example of Using JPA // Create EntityManagerFactory for a persistence unit // called manager1 EntityManagerFactory emf = Persistence.createEntityManagerFactory("pu1"); EntityManager em = emf.createEntityManager(); // Get a transaction instance and start the transaction EntityTransaction tx = em.getTransaction(); // Create a new customer,persist and commit it. tx.begin(); Customer cust = new Customer(); dist.setName("Joe Smith"); em.persist(cust); tx.commit(); em.close(); emf.close();

42

Local Storage via JavaScript • Use JavaDB as a cache browser application (LAJAX) • Eg. Data in an unsubmitted HTML form are stored in JavaDB • Netscape's LiveScript technology allows bidi communication between JavaScript and Applet > JavaScript → Applet – can invoke static methods from

applet > Applet → JavaScript – access hosting HTML page > Need to enable MAYSCRIPT option in tag

• Create an applet which holds a JavaDB in the HTML page 43

Example of Using Local Storage Appl et

JavaScript invokes methods Applet retrives fields for caching

Java DB

44

JavaScript to Applet Name:


... Save ...

Static 45method

Applet to JavaScript In jre/lib/plugin.jar

import
netscape.javascript.*; public
class
LocalStorage
extends
Applet
{ static
JSObject
myForm
=
null; public
void
init()
{ //Create
an
instance
of
JavaDB ... JSObject
win
=
JSObject.getWindow(this); JSObject
doc
=
(JSObject)win.getMember(“document”); myForm
=
(JSObject)doc.getMember(“my_form”); } public
static
void
cacheData()
{ String
user
=
myForm.getMember(“user”); ... //Save
data
in
JavaDB

46

Performance

47

JDBC Performance – 1 • Use and reuse PreparedStatement • Restrict the number of records returned • Batch your queries > New ResultSet facility for bulk updates

• Use store procedures to perform some processing before returing data > Especially if you have a condition that is too complex to be expressed in WHERE clause

48

JDBC Performance – 2 • Close all JDBC resource after you have used them • Auto commits a transaction after every statement > Turn this off for multiple updates > Perform commit yourself, much more efficient

• Provide hints to query > JPA has query hints to persistence manager > Not strictly JDBC

49

JavaDB Specific • Put the logDevice for database in a separate device > jdbc:derby:/device/toursDB;create=true;logDevice=/another/d

evice/toursLog

• Tune the page cache size, default is 4MB > derby.storage.pageCacheSize

• Use index to avoid table scans > Turn on quey plan to see how JavaDB is execting queries > Set property derby.language.logQueryPlan to true

50

Monitoring Statistics • Enable runtime and timinng information > SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); > SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);

• Query statistics > SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

• If invoked in JDBC, result will be returned in ResultSet

51

Administration

52

Offline Backups • When database engine is not running > Copy of the database directory

• Freeze database > Freeze database – SYSCS_UTIL.SYSCS_FREEZE_DATABASE() > Backup the database directory > Unfreeze database – SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE()

• Command can be executed from any SQL console > Eg. CALL
SYSCS_UTIL.SYSCS_FREEZE_DATABASE() > NetBeans database tools 53

Online Backups • Locks and perform a copy of the currently open database to the provided location > CALL


SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/opt/ba ckups/jan2008')

• Can be called programatically via JDBC Connection
conn
=
DriverManager.getConnection(...); CallableStatement
cs
=
conn.prepareCall( "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
 cs.setString(1,
backupdirectory); cs.execute();
 cs.close(); conn.close(); 54

Administrative Tools • ij – SQL scripting tool > JDBC neutral, can be used against other JDBC drivers

• dblook – schema extraction tool for Derby • sysinfo – Derby version information > Output useful for bug reporting in Jira

• Third party GUI tool > NetBeans > SquirreL

55

What is Coming in JavaDB 10.4 • Security improvements • SQL roles • SQL OLAP functionalities > E.g. LIMIT()

• • • •

Table functions (VTI) Basic replication JMX management interfaces Performance improvements 56

Agenda • • • •

MySQL Sun Java DB Which Database? Resources

57

JavaDB and MySQL

JavaDB

MySQL

• Embedded Java applications • Departmental level applications • Database size in GB • Multi-tier deployment

• Client-server applications • Clustering available • Database size in TB

58

Agenda • • • •

MySQL Sun Java DB Which Database? Resources

59

Participate! ●

http://dev.mysql.com ●

●

http://bugs.mysql.com ●

●

Report bugs, submit patches

http://forge.mysql.com ●

●

Place to start for developers

Projects around MySQL

http://forums.mysql.com ●

Discuss developer issues

2008 MySQL and Java DB for Java Developers

60

Participate! ●

http://db.apache.org/derby ●

●

JIRA http://issues.apache.org/jira/browse/DERBY ●

●

Report bugs, submit patches

[email protected] ●

●

Download, read docs

Discuss experience, get help, give feedback

[email protected] ●

Discuss developer issues 2008 MySQL and Java DB for Java Developers

61

Thank You! Raghavan “Rags” N. Srinivas [email protected] Sun Microsystems 62