8/14/2019 TD MXC MySQLjavaDB Srinivas
1/62
1
Java and Databases: ADeveloper Perspective
Raghavan Rags N. [email protected]
Sun Microsystems
1
8/14/2019 TD MXC MySQLjavaDB Srinivas
2/62
2
> Rags>CTO, Technology Evangelism
>Working on Databases for well over 2 decadesstarting with dbase
Speakers
8/14/2019 TD MXC MySQLjavaDB Srinivas
3/62
3
Goal of the Talk
Learn about MySQL and Sun Java DBfrom a Developer Perspective
8/14/2019 TD MXC MySQLjavaDB Srinivas
4/62
4
MySQL
Sun Java DB
Which Database?
Resources
Agenda
8/14/2019 TD MXC MySQLjavaDB Srinivas
5/62
5
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
6/62
6
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 sodid others!
Few features but implement them fast &
stable> Repeatable bugs went to the test suite
> We valued speed more than features
8/14/2019 TD MXC MySQLjavaDB Srinivas
7/627
The 15-min rule
Easy to install and use
> The 15 Minutes rule: Install and try in less than15 min
> Binaries for many platforms (portable source) Documentation (in 'special' English at first)
8/14/2019 TD MXC MySQLjavaDB Srinivas
8/628
Innovation: Pluggable Storage
Engines Row Level Locking versus Page level locking In MySQL you can choose what fits your application
without changing database, rewriting yourapplication etc
That is because the storage part in MySQL ispluggable
8/14/2019 TD MXC MySQLjavaDB Srinivas
9/629
MySQL Internal Architecture
8/14/2019 TD MXC MySQLjavaDB Srinivas
10/6210
Small? Innovations
Innovation in OpenSource tends to be lots of smallbut very useful ideas> SELECT stuff FROM table LIMIT 10
> The idea for this came from the PHP languagefounder> Extremely common in web applications> Now spread to other databases
A web application connects to thedatabase much more than a traditional one> MySQL has always had much faster connect
time than traditional databases
8/14/2019 TD MXC MySQLjavaDB Srinivas
11/622008 MySQL and Java DB for Java Developers 11
Fastest & most PopularOpen 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 inSolaris.
Sun acquired MySQL
8/14/2019 TD MXC MySQLjavaDB Srinivas
12/622008 MySQL and Java DB for Java Developers 12
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
13/622008 MySQL and Java DB for Java Developers 13
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 aMySQL database. 5.0.6 Recently released Requires Connector/J 5.x
8/14/2019 TD MXC MySQLjavaDB Srinivas
14/622008 MySQL and Java DB for Java Developers 14
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
15/6215
Bold maintained by MySQL AB, All others by ourcommunity!
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 directMySQL Cluster
connectors NDB-Java NDB-Python NDB-Ruby NDB-Perl
NDB-Lua NDB-C# (Mono) NDB-PHP(direct)
And even Cobol!
8/14/2019 TD MXC MySQLjavaDB Srinivas
16/6216
MySQL
Sun Java DB
Which Database?
Resources
Agenda
8/14/2019 TD MXC MySQLjavaDB Srinivas
17/6217
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
18/6218
A Brief History of JavaDB
Cloudscape Informix
IBM
Purchase Purchase
Opensourced
Contribute,
branding,support
1996
1999 2001
2004
2006
8/14/2019 TD MXC MySQLjavaDB Srinivas
19/62
19
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.
8/14/2019 TD MXC MySQLjavaDB Srinivas
20/62
20
Main Features 2
Fully ACID compliant> Full transaction isolation support
Two phase commit (XA)
Database encryption and crash recovery
Administrative tools for online backup, examiningdatabase, etc
8/14/2019 TD MXC MySQLjavaDB Srinivas
21/62
21
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
22/62
22
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/
http://developers.sun.com/javadb/downloads/index.jsphttp://db.apache.org/derby/http://db.apache.org/derby/http://developers.sun.com/javadb/downloads/index.jsp8/14/2019 TD MXC MySQLjavaDB Srinivas
23/62
23
DeploymentArchitecture
8/14/2019 TD MXC MySQLjavaDB Srinivas
24/62
24
Standalone Database Server
Application
JDBC
Application
JDBC
Network Server
JDBC
SQL
Access
Buffer
Storage
Application
JDBC
JVM
Log and data
8/14/2019 TD MXC MySQLjavaDB Srinivas
25/62
25
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
26/62
26
Embeded Database
Application
Log andData
Buffer
Storage
Access
SQL
JDBC
J
avaVirtualMac
hine
(
JVM)
Becomes part of application> Include derby.jarin yourCLASSPATH
Single JavaDB engineinstance per Java VM
Can be embedded in webpage as local storage
Multiuser and thread-safe
Database can be read-only> In a JAR file on USB drive
8/14/2019 TD MXC MySQLjavaDB Srinivas
27/62
27
Middle Tier as Cache
Enbedded in the middle tier
Act as a front end cache forbackend enterprise DB
Advantage> No network overhead> Data locality
Currently no build indatadata synchronization> Sequoia> Daffodil
JDBC
Web Server
Servlet
JavaDB
Database
8/14/2019 TD MXC MySQLjavaDB Srinivas
28/62
28
Accessing JavaDBfrom Applications
8/14/2019 TD MXC MySQLjavaDB Srinivas
29/62
29
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
Resultrs=stmt.executeQuery(select...);
rs.absolute(5);
rs.updateString("NAME","AINSWORTH");rs.updateRow();
Better support for CLOB, BLOB, ARRAY andSTRUCT
> conn.createBlob() to create a BLOB object
8/14/2019 TD MXC MySQLjavaDB Srinivas
30/62
30
JDBC 4.0 Features 2
XML datatype supportDocumentBuilderparser=DocumentBuilderFactory...
DocumentxmlDoc=parser.parse(newFile(file.xml);
SQLXMLsqlXml=conn.createSQLXML();
OutputStreamos=sqlXml.setBinaryStream();
Transformertrans=TransformerFactory....
trans.transform(xmlDoc,newStreamResult(os));
//insertdataintodatabase
sqlXml.free();
RowId support for records that do not have uniqueidentifiers> Specific to data source
> Use getRowIdLifetime() from DatabaseMetaData to
8/14/2019 TD MXC MySQLjavaDB Srinivas
31/62
31
JDBC 4.0 Features 3
Chained exception handlingtry{
//SQLstatement
...
}catch(SQLExceptionex){
for(Throwablet:ex){for(StackTraceElementste:t.getStackTrace())
if(ste.getClassName().startsWith(org.fred)
System.err.println(ste);
}
Access to vendor specific implementationsClasskls=Class.forName(oracle.jdbc.OracleStatement);
if(stmt.isWrapperFor(klass)){
OracleStatementos=(OracleStatement)stmt.unwrap(kls);
8/14/2019 TD MXC MySQLjavaDB Srinivas
32/62
32
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
33/62
33
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
34/62
34
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
35/62
35
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
36/62
36
Common URL Connection Attributes
create=boolean
> Creates the database
shutdown=boolean
> Shuts down the database user=;password=
createFrom=;restoreFrom=
> 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/
8/14/2019 TD MXC MySQLjavaDB Srinivas
37/62
37
Accessing Java Methods
Can create JavaDB stored procedures andfunctions in Java
Use CREATEPROCEDURE and CREATE
FUNCTION, with the following> LANGUAGEJAVA
> PARAMETERSTYLEJAVA
> EXTERNALNAMEClassname.methodName
Invoked with CALL
8/14/2019 TD MXC MySQLjavaDB Srinivas
38/62
38
A JavasendMailMethod
publicstaticintsendMail(StringtoAddress
,Stringsubject,Stringcontent){
recipient=newInternetAddress(toAddress);
...
msg=newMimeMessage(session);
msg.setFrom(from);msg.setSubject(subject);
msg.setText(content);
msg.addRecipient(Message.RecipientType.TO
,recipient);
Transport.send(msg);
8/14/2019 TD MXC MySQLjavaDB Srinivas
39/62
39
Using a Java Method in Trigger
CREATEFUNCTIONSEND_MAIL(TO_ADDRESSVARCHAR(320),SUBJECTVARCHAR(320),BODYVARCHAR(32000))RETURNSINTLANGUAGEJAVAPARAMETERSTYLEJAVANOSQLEXTERNALNAME'SendMail.sendMail');
--Send a Welcome e-mail when new customers are added.CREATETRIGGERWELCOME_CUSTOMER
AFTERINSERTONCUSTOMERREFERENCINGnew_tableASnewtabFOREACHSTATEMENTMODEDB2SQLSELECTSEND_MAIL(c.email,'WelcometoAcmeWidgets'
,M.EMAIL_TEXT)FROMnewtabC,MAILINGSMWHEREC.TYPE=M.CUST_TYPEANDM.OFFER_TYPE='welcome'
8/14/2019 TD MXC MySQLjavaDB Srinivas
40/62
40
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
41/62
41
persistence.xmlwith TopLink
oracle.toplink.essentials.ejb.cmp3.EntityManagerFactoryProvider
entity.Customer
entity.Order
8/14/2019 TD MXC MySQLjavaDB Srinivas
42/62
42
Example of Using JPA// Create EntityManagerFactory for a persistence unit
// called manager1EntityManagerFactory emf =
Persistence.createEntityManagerFactory("pu1");EntityManager em = emf.createEntityManager();
// Get a transaction instance and start the transactionEntityTransaction 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();
8/14/2019 TD MXC MySQLjavaDB Srinivas
43/62
43
Local Storage via JavaScript
Use JavaDB as a cache browser application (LAJAX)
Eg. Data in an unsubmitted HTML form are stored inJavaDB
Netscape's LiveScript technology allows bidicommunication 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
8/14/2019 TD MXC MySQLjavaDB Srinivas
44/62
44
Example of Using Local Storage
Applet
JavaDB
JavaScriptinvokes
methods
Appletretrives fieldsfor caching
8/14/2019 TD MXC MySQLjavaDB Srinivas
45/62
45
JavaScript to Applet
Name:
...Save
...
Staticmethod
8/14/2019 TD MXC MySQLjavaDB Srinivas
46/62
46
Applet to JavaScript
importnetscape.javascript.*;publicclassLocalStorageextendsApplet{
staticJSObjectmyForm=null;
publicvoidinit(){
//CreateaninstanceofJavaDB
...JSObjectwin=JSObject.getWindow(this);
JSObjectdoc=(JSObject)win.getMember(document);
myForm=(JSObject)doc.getMember(my_form);
}
publicstaticvoidcacheData(){
Stringuser=myForm.getMember(user);
...
//SavedatainJavaDB
Injre/lib/plugin.jar
8/14/2019 TD MXC MySQLjavaDB Srinivas
47/62
47
Performance
8/14/2019 TD MXC MySQLjavaDB Srinivas
48/62
48
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 processingbefore returing data
> Especially if you have a condition that is too complex tobe expressed in WHERE clause
8/14/2019 TD MXC MySQLjavaDB Srinivas
49/62
49
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
50/62
50
JavaDB Specific
Put the logDevice for database in a separate device> jdbc:derby:/device/toursDB;create=true;logDevice=/another/device/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
8/14/2019 TD MXC MySQLjavaDB Srinivas
51/62
51
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 inResultSet
8/14/2019 TD MXC MySQLjavaDB Srinivas
52/62
52
Administration
8/14/2019 TD MXC MySQLjavaDB Srinivas
53/62
53
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. CALLSYSCS_UTIL.SYSCS_FREEZE_DATABASE()
> NetBeans database tools
8/14/2019 TD MXC MySQLjavaDB Srinivas
54/62
54
Online Backups
Locks and perform a copy of the currently opendatabase to the provided location> CALLSYSCS_UTIL.SYSCS_BACKUP_DATABASE('/opt/ba
ckups/jan2008')
Can be called programatically via JDBCConnectionconn=DriverManager.getConnection(...);
CallableStatementcs=conn.prepareCall(
"CALLSYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
cs.setString(1,backupdirectory);
cs.execute();
cs.close();
conn.close();
8/14/2019 TD MXC MySQLjavaDB Srinivas
55/62
55
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
56/62
56
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
8/14/2019 TD MXC MySQLjavaDB Srinivas
57/62
57
MySQL
Sun Java DB
Which Database?
Resources
Agenda
8/14/2019 TD MXC MySQLjavaDB Srinivas
58/62
58
JavaDB and MySQL
JavaDB Embedded Java
applications
Departmental levelapplications
Database size in GB
Multi-tier deployment
MySQL Client-server
applications
Clustering available
Database size in TB
8/14/2019 TD MXC MySQLjavaDB Srinivas
59/62
59
MySQL
Sun Java DB
Which Database?
Resources
Agenda
8/14/2019 TD MXC MySQLjavaDB Srinivas
60/62
2008 MySQL and Java DB for Java Developers 60
Participate!
http://dev.mysql.com Place to start for developers
http://bugs.mysql.com Report bugs, submit patches
http://forge.mysql.com Projects around MySQL
http://forums.mysql.com Discuss developer issues
8/14/2019 TD MXC MySQLjavaDB Srinivas
61/62
2008 MySQL and Java DB for Java Developers 61
Participate!
http://db.apache.org/derby Download, read docs
JIRAhttp://issues.apache.org/jira/browse/DERBY Report bugs, submit patches
Discuss experience, get help, give feedback [email protected]
Discuss developer issues
8/14/2019 TD MXC MySQLjavaDB Srinivas
62/62
Thank You!
Raghavan Rags N. [email protected]
Sun Microsystems
Top Related