Spring JDBC


Spring JDBC

juni 30, 2005
1
Doel

 Database acties
 Flexibel
 Gecontroleerd
 Productief

juni 30, 2005
2
Database acties

 CRUD
 stored procedures
 Lob
 ...

juni 30, 2005
3
Flexibel

 jdbc
 ORM framework
 connection pooling
 datasource
 testen
 outside container
 mock objecten
 Database onafhankelijk (?!)

juni 30, 2005
4
Gecontroleerd

 Exception handling
 Connection leaking

juni 30, 2005
5
Productief

 gericht op functie
 geen plumbing

juni 30, 2005
6
Architectuur

Domain
Object
Domain
Object
Value
Object
JNDI
Service
DAO

juni 30, 2005
7
SIDE STEP - Architectuur issue

 Hoe implementeer je de
emp – dept en emp – mgr
relatie in de domein
objecten?
 Hoe ga je met die relatie om
in DAO’s?
 ORM taak
Department
Employee
 lazy loading vs ...

juni 30, 2005
8
Example

import java.sql.*;
import javax.sql.*;
public class EmpDao {
public List getAllEmployees() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List emps = new ArrayList();
try {
con = getConnection();
pstmt = con.prepareStatement
("select * from emp");
rs = pstmt.executeQuery();
while (rs.next()) {
Employee e = new Employee();
e.setId
(rs.getLong(1));
e.setName (rs.getString(2));
// ...
emps.add(e);
}
} catch (SQLException e) {
// handle exception
} finally {
try {
rs.close();
pstmt.close();
con.close();
} catch (SQLException e1) {
// no action needed
}
}
return emps;
}
}
private Connection getConnection() throws SQLException
{
try {
Context ic = new InitialContext();
DataSource ds =
(DataSource) ic.lookup
("java:comp/env/jdbc/myDatabase");
return ds.getConnection();
}
catch (NamingException e) {
// handle exception
return null;
}
}

juni 30, 2005
private Connection getConnection() throws SQLException
{
try {
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
return DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl“
,"scott", "tiger");
} catch (SQLException sqle) {
// handle exception
return null;
}
}
9
How can Spring help?

Make life easier:
 DAO Support
 JDBC, Hibernate, Toplink, iBatis, JDO, ...
 Dependency Injection
 Jdbc helper classes
 Exception Handling
 MockObjects

juni 30, 2005
10
Spring Architectuur

Domain
Object
Domain
Object
Domain
Object
DAO
Interface
XXDAO
Support
JdbcDaoSupport
HibernateDaoSupport
TopLinkDaoSupport
...
Service
DAO
ApplicationContext-jdbc

juni 30, 2005
11
Example A

public interface empDao {
public List getAllEmployees ();
}
public class EmployeeJdbcDao extends JdbcDaoSupport implements EmpDao {
public List getAllEmployees() {
JdbcTemplate jt = getJdbcTemplate();
return jt.queryForList (“select * from emp”);
}
}
<bean
id="dataSourceDBDirect"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:BAARSJES1" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</bean>
<bean id="employeeDAO" class="nl.amis.demo.dao.jdbc.EmployeeJdbcDao" >
<property name="dataSource">
<ref local="dataSourceDBDirect" />
</property>
</bean>

juni 30, 2005
12
Exception Handling

 RuntimeException ipv checked
SQLException
 DataAccessException
 SQLException Translation




DataIntegrityViolationException
DataRetrievalFailureException
CannotGetJdbcConnectionException
...

juni 30, 2005
13
jdbc helper classes

 JdbcTemplate
 query, queryForList, queryForInt, queryFor..
 ArrayList (per row) of HashMaps (column name
as key)
 RowMapper
 PreparedStatementCreator/Callback
 MappingSQLQuery
 ...

juni 30, 2005
14
Testen en MockObjects

public class TestEmployeeDao
extends AbstractDependencyInjectionSpringContextTests
{
private EmployeeDao employeeDAO;
public void setEmployeeDAO(EmployeeDao employeeDAO)
{
this.employeeDAO = employeeDAO;
}
protected String[] getConfigLocations() {
return new String[]
{"nl/amis/demo/dao/jdbc/applicationContext-jdbc.xml"};
}
public void testFindEmployeeById ()
{
Employee emp = employeeDAO.getEmployeeById(7839);
assertEquals("KING", emp.getName());
assertEquals("PRESIDENT", emp.getJob());
// ...
}
<bean id="employee7839"
class="nl.amis.demo.domain.Employee">
<property name="name" value="KING" />
<property name="employeeNumber" value="7839" />
<property name="job" value="PRESIDENT" />
</bean>
}
<bean id="employeeMockDAO"
class="nl.amis.demo.dao.EmployeeMockDao">
<property name="emp">
<ref local="employee7839" />
</property>
</bean>

juni 30, 2005
15