Transcript Database applications with Java Rory Preddy Agenda  JDBC  JPA  Questions (and giveaways)

Database applications
with Java
Rory Preddy
Agenda
 JDBC
 JPA
 Questions (and giveaways)
2
JDBC
3
Setup for Derby



Java DB comes with Java SDK. (Installed in the db directory of the JDK installation)
Java DB is Oracle’s supported distribution of the Apache Derby open source database.
To start Java DB:
In Windows:
c:\Java\jdk1.7.0_25\db\bin> startNetworkServer.bat
In Unix/Linux:
[rpreddy@mypc:/opt/java/jdk1.7.0_40/db/bin]$ ./startNetworkServer
Fri April 17 14:35:28 BST 2015 : Security manager installed using the Basic server security policy.
Fri April 17 14:35:31 BST 2015 : Apache Derby Network Server - 10.8.2.2 - (1181258) started and ready to accept connections on port
1527


By default, the database server starts on the listening port 1527.
To stop Java DB:
(Enter ctrl c)
Terminate batch job (Y/N)? y
Create table in Java DB

Connect to the Java DB using “ij”
c:\Java\jdk1.7.0_25\db\bin> ij
ij version 10.8
ij> connect 'jdbc:derby://localhost:1527/c:\\temp\\db;create=true';
ij> create table students(id integer, name varchar(20), age integer, city varchar(50));
ij> insert into students values(1, 'david', 22, 'Dublin');
1 row inserted/updated/deleted
ij> insert into students values(2, 'peter', 25, 'Galway');
1 row inserted/updated/deleted
ij> insert into students values(3, 'frank', 28, 'Limerick');
ij> alter table students alter column id NOT NULL;
Ij> alter table students add primary key (id);
1 row inserted/updated/deleted
ij> select * from students;
ID
|NAME
|AGE
|CITY
-----------------------------------------------------------1
|david
|22
|Dublin
2
|peter
|25
|Galway
3
|frank
|28
|Limerick
3 rows selected
Select data



Similar to create the database, but without the ‘create=true’ clause
All the data is saved in the hard drive in c:\temp\db
Open another cmd or terminal:
c:\Java\jdk1.7.0_25\db\bin> ij
ij version 10.8
ij> connect 'jdbc:derby://localhost:1527/c:\\temp\\db';
ij> select * from students;
ID
|NAME
|AGE
|CITY
-----------------------------------------------------------1
|david
|22
|Dublin
2
|peter
|25
|Galway
3
|frank
|28
|Limerick
3 rows selected
Using embedded DB


Possible to connect via embedded DB (no server)
New URL is:
“jdbc:derby:C:/temp/db”
JDBC API

Provides a universal data access
Access any data source, from relational database to excel spreadsheet or even
flat files.

The JDBC API is comprised of the following two packages:

java.sql
javax.sql

A JDBC driver is needed to mediate between JDBC technology and the
database.

Since Java 7 there is no need to register your driver class. Just add to classpath.
Java DB comes with a JDBC driver in the “LIB” folder

Process SQL with JDBC
1.
Establish a db connection
2.
Create an SQL statement
3.
Execute the statement
4.
Process the ResultSet Object if required
5.
Close the connection
1. Establish a db connection


Include the $JAVA_HOME/db/lib/derbyclient.jar in the project classpath.
Connect using one of the following two classes:
 DriverManager
Connection conn =
DriverManager.getConnection("jdbc:derby://localhost:1527/c:/temp/db");

DataSource
ClientDataSource ds = new ClientDataSource();
ds.setPortNumber(1527);
ds.setServerName("localhost");
ds.setDatabaseName("c:/temp/db");
Connection conn = ds.getConnection();
2. Create an SQL statement

Execute the Statement object, generating a ResultSet objects, which is a table of
data representing a database result set.

Need a Connection object to create a Statement object
Statement stmt = conn.createStatement();


Three different kinds of statements:
Statement
Simple SQL statements without parameters.

PreparedStatement
Statements that might contain input parameters.

CallableStatement
Stored procedures that may contain input/output parameters.
3. Execute the statement

To execute a query, simply call an execute method from the Statement
String sql = "select * from students";
ResultSet rs = stmt.executeQuery(sql);

There are three available execute methods can be used:
execute()
Returns true if the first object that the query returns is a ResultSet
executeQuery()
Returns the ResultSet object.
executeUpdate()
Returns the number of rows affected by the SQL statement
4. Process the ResultSet Object

Access the row data in a ResultSet object through a cursor.

Initially, the cursor is positioned before the first row
Call the next() to move the cursor forward by one row.

while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String city = rs.getString("city");
System.out.println(id + "\t" + name + "\t" + age + "\t" + city);
}

When reaching the line row of the ResultSet object, the next() will return
“false”.
5. Close the connection

Call Statement.close() or close Connection.close()
finally {
try {
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
Wrap with try-with resources in Java 7!
StudentDAODemo
1.
2.
3.
4.
5.
6.
public class StudentDaoDemo {
public static void viewStudents(Connection conn) {
String sql = "select * from students";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
7.
8.
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String city = rs.getString("city");
System.out.println(id + "\t" + name + "\t" + age + "\t" + city);
}
9.
10.
11.
12.
13.
14.
15.
}
catch (SQLException ex) {
ex.printStackTrace();
}
finally {
try {
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
}
public static void main(String[] args) throws Exception {
Connection conn = DriverManager
.getConnection("jdbc:derby://localhost:1527/c:/temp/db");
viewStudents(conn);
}
28.
29.
30.
31.
32.
33.
}
RowSet Features

RowSet extends ResultSet in the following two ways:

Trigger notifications.
 Components that have implemented the RowSetListener interface will
be noticed when any of the three events occurs:
1.
2.
3.

A cursor movement
The update, insertion or deletion of a row
A change to the entire RowSet contents
Scrollability or Updatability
A RowSet object is scrollable and updatable by default.
RowSet Types

A RowSet object is considered either connected or disconnected.

A connected RowSet object uses a JDBC driver to make a connection to a
relational database and maintains the connection.
 JdbcRowSet

A disconnected RowSet object makes a connection to a data source only when
read/write data to the data source is needed.
 CachedRowSet
 WebRowSet
 JoinRowSet
 FilteredRowSet
JdbcRowSet Basics



Maintains the connection to the data source.
To make a ResultSet object scrollable and updatable.
Create a JdbcRowSet object:
 Invoke the JdbcRowSetImpl constructor that takes a ResultSet object
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
JdbcRowSet rowSet = new JdbcRowSetImpl(rs);

Invoke the JdbcRowSetImpl constructor that takes a Connection object
Connection conn = …;
JdbcRowSet rowSet = new JdbcRowSetImpl(conn);

Invoke the JdbcRowSetImpl default constructor
JdbcRowSet rowSet = new JdbcRowSetImpl();

Using an instance of RowSetFactory, which is created from the class
RowSetProvier
RowSetFactory myRowSetFactory = RowSetProvider.newFactory();
JdbcRowSet rowSet = myRowSetFactory.createJdbcRowSet();
Using JdbcRowSet

Set the command:
rowSet.setCommand("select * from students");

After setting the command property, invoke the execute() method:
rowSet.execute();

To add listeners:
rowSet.addRowSetListener(new RowSetListener(){
@Override
public void rowSetChanged(RowSetEvent event){
System.err.println("RowSet changed...");
}
@Override
public void rowChanged(RowSetEvent event){
System.err.println("Row changed... ");
}
@Override
public void cursorMoved(RowSetEvent event){
System.err.println("Cursor moved...");
}
});
Navigating JdbcRowSet




A ResultSet can only use the next() method to move the cursor forward.
A JdbcRowSet can use all of the cursor movement methods defined in the
ResultSet interface.
The previous() method moves the cursor to the previous row
The absolute(int n) method moves the cursor directly to the n row
// get the third row
rowSet.absolute(3);
displayRow(rowSet);
// then get the previous row, i.e. the second row
rowSet.previous();
displayRow(rowSet);
Insert row JdbcRowSet

JdbcRowSet is updatable:
rowSet.moveToInsertRow();
rowSet.updateInt("id", 4);
rowSet.updateString("name", "enda");
rowSet.updateInt("age", 35);
rowSet.updateString("city", "moat");
rowSet.insertRow();
Update/Delete row JdbcRowSet

Deleting a row - move the cursor to the last row, and call the deleteRow()
method.
rowSet.last();
rowSet.deleteRow();

Updating a row :
rowSet.first();
rowSet.updateString("name", "jason");
rowSet.updateRow();
Prepared Statements


Use the same statement and supply it with different values each time it is
executed it.
Creating a PreparedStatement Object:
String sql = "update students set name = ? where id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);

Supply values for the parameters:
stmt.setString(1, name);
stmt.setInt(2, id);

int rowsAffected = stmt.executeUpdate();
JPA
24
Topics
•
•
•
•
•
•
•
•
Brief history
Entities
Entity Manager
Relationship Mapping
Inheritance Mapping
Queries
Callbacks
Listeners
• JDBC/JPA Demo
25
Brief History
• Container Managed Persistence (CMP) was introduced in EJB
1.0 as the persistence model of J2EE 1.2.
• JPA was defined as part of the EJB 3.0 specification as a
replacement for the EJB 2 CMP
• JPA itself is just a specification, it only describes persistence
concepts and provides standard interfaces.
26
Brief History (cont.)
• TopLink is the Oracle persistence provider solution.
• In 2007, TopLink 11g source code was donated to the Eclipse
Foundation and the EclipseLink project was born.
• In 2008 Sun Microsystems selected the EclipseLink project as
the reference implementation for the JPA 2.0 specification.
• The latest JPA specification version is JPA 2.1 .
27
Entities
• Objects that maps to relational database table are called Entities.
• JPA maps entities using two formats:
• Annotations: the entity class is directly annotated with
annotations.
• XML descriptors: The mapping is defined in an external XML file.
• The Entity’s name is mapped to a relational table name.
• Attribute names are mapped to a column name.
• The Entity must define a primary key (single field or a combination
of fields).
28
Entity Manager
• Entity Manager is just an interface whose implementation is
implemented by the persistence provider
• It allows simple CRUD operations as well as complex queries
using JPQL (Java Persistence Query Language).
• Queries can be divided to:
• Dynamic queries (created dynamically at runtime),
• Static/named queries (defined statically at compile time),
• Native SQL statement.
• The persistence context (persistence.xml) describes all Entities
of an Entity manager.
29
persistence.xml
30
JPA Student Demo
Simple Mappings
• Direct mappings of fields to columns
• @Basic – optional, indicates simple mapped attribute
• Can specify fetch=EAGER / fetch=LAZY
• LAZY – container defers loading until the field or property is
accessed (load on demand)
• EAGER – requires that the field or relationship be loaded when the
referencing entity is loaded (pre-load)
• Maps any of the common simple Java types
• Primitives (int, long, String), wrappers, serializable, etc.
32
Relationship Mapping
• JPA allows to define relationships between classes (Entities).
• Can be bidirectional or unidirectional.
• Bidirectional - both classes store a reference to each other
• Unidirectional - only one class has a reference to the other class.
• Relationship annotations:
•
•
•
•
@OneToOne
@OneToMany
@ManyToOne
@ManyToMany
33
Relationship Mapping (cont.)
OneToOne Example
34
Inheritance Mapping
Consider the following Inheritance hierarchy
35
Inheritance Mapping (cont.)
• The root entity class can define the inheritance strategy by
using the @Inheritance annotation.
• If it doesn’t, the default single-table-per-class hierarchy
strategy will be applied.
• Inheritance Strategies :
• A single-table-per-class.
• A joined-subclass.
• A table-per-concrete-class.
36
Inheritance Mapping (cont.)
• A single-table-per-class hierarchy strategy: (this is the default
strategy).
37
Inheritance Mapping (cont.)
• A joined-subclass strategy: each entity in the hierarchy is mapped
to its own dedicated table.
38
Inheritance Mapping (cont.)
• A table-per-concrete-class strategy: This strategy maps each
concrete entity hierarchy to its own separate table.
39
Queries
• Supported query types:
• Dynamic queries: consisting of a JPQL query string.
• Named queries (static queries): static and unchangeable.
• Native queries: to execute a native SQL statement.
• Criteria API: object-oriented query API.
40
JPQL
• Results obtained are entity or a collection of entities.
• The query string may be dynamically created at runtime.
• JPQL syntax is object oriented .
Query query = em.createQuery("SELECT c FROM
Customer c WHERE c.firstName=:fname");
query.setParameter("fname", “Rory");
List<Customer> customers = query.getResultList();
41
Named Queries
@NamedQuery
(name = "findCityCustomers",query="select c from Customer c where
c.Address.city = :city);
To use this query
Query query = em.createNamedQuery(" findCityCustomers ");
query.setParameter("city ", “Cairo");
List<Customer> customers = query.getResultList();
42
Native Queries
• Use the real database tables names.
• The result can be automatically converted back to entities.
Query query = em.createNativeQuery("SELECT * FROM t_customer",
Customer.class);
List<Customer> customers = query.getResultList();
43
Criteria API
• Supports everything JPQL can do but with an object-based
syntax.
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Customer> query = builder.createQuery(Customer.class);
Root<Customer> c = query.from(Customer.class);
query.select(c).where(builder.equal(c.get("firstName"), “Joe"));
44
Callbacks
@PrePersist
@PreUpdate
private void validate() {
if (firstName == null || "".equals(firstName))
throw new IllegalArgumentException("Invalid first name");
if (lastName == null || "".equals(lastName))
throw new IllegalArgumentException("Invalid last name");
}
45
Listeners
public class DataValidationListener {
@PrePersist
@PreUpdate
private void validate(Customer customer) {
if (customer.getFirstName() == null || "".equals(customer.getFirstName()))
throw new IllegalArgumentException("Invalid first name");
if
(customer.getLastName() == null || "".equals(customer.getLastName()))
throw new IllegalArgumentException("Invalid last name");
}
}
In the entity class:
@EntityListeners({DataValidationListener.class, ….})
@Entity
public class Customer { ……….}
46
Demo –
http://www.java2s.com/Tutorials/Java/JPA/i
ndex.htm
Questions and give away
48