Database Development

materialisticrampantInternet και Εφαρμογές Web

10 Νοε 2013 (πριν από 3 χρόνια και 5 μήνες)

74 εμφανίσεις

Database  Development
Bruce  Campbell
Notes

This  is  a  training  NOT  a  presenta6on

Please  ask  ques6ons

Prerequisites

Introduc6on  to  Spring

Introduc6on  to  Spring  MVC

A  basic  understanding  of  SQL

LDS  Tech  IDE  Installed

JDBC

The  Need  for  Data

Benefits  of  JDBC

DataSource  &  Connec6on

Connec6on  Demo

Connec6on  Pool

Connec6on  Challenges

Connec6on  Pool  Benefits

Connec6on  Pool  Demo
Outline

Java  Stack  Config

Dependencies

catalina.proper6es

server.xml

context.xml

applica6onContext.xml

Java  Stack  Config  Benefits

Lab
Outline

JDBC  (back)

Statement

ResultSet

JDBC  Demo

JDBC  Pain  Points
Outline

Spring  JDBC

Spring  JDBC  Features

Main  Classes

Spring  JDBC  Demo

Lab

Excep6on  Mapping

Other  Best  Prac6ces

Conclusion
Outline
Data

Most  applica6ons  rely  on  data...  in  fact,  in  the  
business  world  most  are  totally  useless  without  it

Databases  do  a  good  job  of  storing  and  serving  
tabular  and  some6mes  other  data

Structured  Query  Languge  (SQL)  

provides  
C
reate  
R
ead  
U
pdate  and  
D
elete  abili6es  in  a  
database  (a.k.a  
CRUD
 opera6ons)

We  need  a  good  way  to  perform  CRUD  
opera6ons  from  Java
JDBC  API

Java  DataBase  Connec6vity  (JDBC)  API

is  a  Java  API  for  working  with  databases

connect

perform  CRUD  and  other  opera6ons

is  included  in  Java  SE

java.sql  and  javax.sql  packages
Main  JDBC  API’s

DataSource
 -­‐  new  way  to  connect  to  the  DB

Connec.on
 -­‐  represents  a  connec6on  to  the  
database  and  provides  a  mechanism  to  
execute  SQL  etc.
Demo

Demo:  Connec6ng  to  a  Database  with  JDBC

In  the  jdbc-­‐examples  project  see  and  run

ConnectExample.java  

TimedExample.java
JDBC  Connection  Issues

Connec6ng  to  a  database  takes  a  significant  
amount  of  6me

Opening  a  connec6on  for  every  database  call  
is  therefore  also  6me  consuming

Maintaining  a  connec6on  for  every  end  user  

doesn’t  fit  with  the  stateless  design  we  are  
striving  for  in  web  applica6ons

can  consume  too  many  resources  
Connection  Pool

A  Connec6on  Pool  is  a  cache  of  database  
connec6ons  that  can  be  re-­‐used  when  needed

The  number  of  connec6ons  is  configurable:  
minimum,  maximum,  idle

The  pool  will  close  connec6ons  when  demand  is  
low  and  open  new  connec6ons  as  needed

It  can  validate  the  connec6on,  before  handing  it  
out,  aYer  geZng  it  back,  or  when  idle

Protect  against  resource  satura6on  
Connection  Pool

improves  applica6on  response  6me  by  sharing  the  
connec6on  overhead  across  mul6ple  DB  calls

reduces  boilerplate  code  because  the  opening  and  
closing  of  the  connec6on  is  done  by  the  pool

reduces  errors/leaks  caused  by  forgeZng  to  close  
the  Connec6on,  ResultSet,  or  Statement

might  reduce  license  costs  -­‐  concurrent  database  
connec6ons  is  one  (of  several)  licensing  models  
with  Oracle
Demo

Demo:  Trivial  Connec6on  Pool

In  the  jdbc-­‐examples  project  see  and  run

PoolExample.java  

No6ce  the  simplified  code  v.s.  previous  demo
Java  Stack  Config

Maven  Dependencies

Oracle  JDBC  Drivers  -­‐  ojdbc6

Tomcat  JDBC  for  connec6on  pooling  -­‐  tomcat-­‐jdbc  

stack  3.2+

Oracle  UCP  for  connec6on  pooling

as  an  addi6onal  op6on  and  backward  compa6bility  in  
stack  3.2+

the  only  op6on  in  stack  3.1

Transi6ve

tomcat-­‐juli  required  by  tomcat-­‐jdbc

Java  Stack  Config

Dependencies

catalina.proper6es

server.xml

context.xml

applica6onContext.xml

Java  Stack  Config  Benefits

Lab
Outline
Java  Stack  Config
server.xml
Java  Stack  Config
catalina.proper6es  -­‐  Tomcat’s  property  file
#  DataSource  Proper6es
ds.url=jdbc:oracle:thin:@192.168.56.101:1521:xe
ds.username=oracle
ds.password=oracle
ds.maxPoolSize=25
Java  Stack  Config
src/main/webapp/META-­‐INF/context.xml  
<ResourceLink  
       name="jdbc/default/DataSource"
     global="jdbc/default/DataSource"
/>
Java  Stack  Config
applica6onContext.xml
...
<stack-­‐db:data-­‐source  jndi-­‐name="jdbc/default/DataSource"/>
...
Simplified  by  a  custom  namespace
Java  Stack  Config

<stack-­‐db:data-­‐source...  results  in  the  following  
spring  configura6on...

DataSource

JdbcTemplate

NamedParameterJdbcTemplate

MBean  to  monitor  connec6on  pool  size

Switching  between  Tomcat  and  Websphere  
configura6ons

supports  embedded  DataSource  (integra6on  tests)

See  also  

Java  Stack  Tomcat  Training

hlps://tech.lds.org/wiki/Java_Stack_Training

Tomcat  JNDI  Docs

hlp://tomcat.apache.org/tomcat-­‐7.0-­‐doc/jndi-­‐
resources-­‐howto.html

data-­‐source  namespace  docs

hlp://code.lds.org/maven-­‐sites/stack/module.html?
module=db-­‐spring/xsddoc/
hlp___code.lds.org_schema_spring_db/element/data-­‐
source.html#
Java  Stack  Config

Benefits

data  source  seZngs  in  catalina.proper6es  allows  

change  without  modifying  the  war  and  without  re-­‐
deploying  the  applica6on

different  values  in  each  environment

the  custom  namespace  handles  the  bulk  of  the  
spring  configura6on  automa6cally

the  dataSource,  jdbcTemplate,  and  other  
resources  are  available  through  spring  injec6on
Lab

hlp://tech.lds.org/wiki/Database_Development_1

Summary

download  the  project  template

unzip

import  into  LDS  Tech  IDE

configure  the  connec6on  pool

run  the  app  to  verify

JDBC  (back)

Statement

ResultSet

ResultSetMetaData

JDBC  Demo

JDBC  Pain  Points
Outline
Main  JDBC  API’s

Statement
 -­‐  represents  a  SQL  statement  and  
provides  access  to  the  set  of  results.    
Three  types...

Statement
:  simple  sql  queries  without  parameters

PreparedStatement
:  precompiled  sql  queries  with  
or  without  parameters

CallableStatement
:  Execute  a  call  to  a  database  
stored  procedure
Main  JDBC  API’s

ResultSet
 -­‐  table  of  data  resul6ng  from  SQL  
execu6on  against  a  database

rows  are  retrieved  in  sequence

a  pointer  to  the  current  row  is  maintained  and  is  
ini6ally  posi6oned  before  the  first  row

the  next()  method  steps  to  the  next  row

ResultSetMetaData
 -­‐  holds  informa6on  on  
the  types  and  proper6es  of  the  columns  in  a  
ResultSet
Demo

Demo:  Query  Data  with  JDBC

In  the  jdbc-­‐examples  project  see  and  run

StatementExample.java  
JDBC  Pain  Points

tedious

lots  of  boilerplate  code

is  prone  to  error

Spring  JDBC

Spring  JDBC  Features

Main  Classes

Spring  JDBC  Demo

Lab

Excep6on  Mapping
Outline
Spring  JDBC
“The  Spring  Framework  takes  care  of  all  the  
low-­‐level  details  that  can  make  JDBC  such  a  
tedious  API  to  develop  with”
Spring  JDBC

Spring  JDBC  takes  care  of

open  the  connec6on

prepare  and  execute  the  statement

setup  the  loop  and  iterate  through  the  results

process  the  excep6on

handle  transac6ons

close  the  connec6on,  statement  and  resultSet
Spring  JDBC

See  Also

Reference  documenta6on  and  javadoc  API  
including  current  and  development  releases

hlp://www.springsource.org/spring-­‐
core#
documenta6on
Spring  JDBC  Main  Classes

JdbcTemplate
 -­‐  classic  approach,  now  
includes  Java  5  syntax  support  like  generics  
and  varargs

NamedParameterJdbcTemplate
 -­‐  provides  
named  parameters  instead  of  “?”  (ques6on  
marks)  placeholders

SimpleJdbcInsert
 and  
SimpleJdbcCall
 use  
database  metadata  to  minimize  configura6on

SimpleJdbcTemplate

Deprecated.

since Spring 3.1 in favor of
JdbcTemplate
and
NamedParameterJdbcTemplate
.
The JdbcTemplate and NamedParameterJdbcTemplate
now provide all the functionality of the
SimpleJdbcTemplate.

RowMapper
 -­‐  An  interface  used  by  
JdbcTemplate
 for  mapping  rows  of  a  
ResultSet
 
on  a  per-­‐row  basis.  Implementa6ons  of  this  
interface  perform  the  actual  work  of  mapping  
each  row  to  a  result  object

jdbcTemplate.query(String  sql)
sql
=
"select count(*) from EXAMPLE"
;
int
i =
jdbcTemplate
.queryForInt(sql);

namedParameterJdbcTemplate.query(...)
sql
=
"select * from EXAMPLE where DATA = :data"
;
MapSqlParameterSource

params
=
new
MapSqlParameterSource();
params.addValue(
"data"
, data);
List<Example> examples =

namedParameterJdbcTemplate
.query(
sql, params,
new
ExampleRowMapper());
Row  Mapper

RowMapper
private

static

class
ExampleRowMapper

implements
RowMapper<Example> {

@Override

public
Example mapRow(ResultSet rs,
int
rowNum)

throws
SQLException {
Example example =
new
Example();
example.setId(rs.getLong(
"ID"
));
example.setName(rs.getString(
"EXAMPLE_NAME"
));
example.setData(rs.getString(
"DATA"
));

return
example;
}
}

simpleJdbcInsert.execute(...)
SimpleJdbcInsert  si  =  
new
 SimpleJdbcInsert(
jdbcTemplate
);
si.withTableName(
"EXAMPLE"
);
si.setOverrideIncludeSynonymsDefault(
true
);
Map<String,  Object>  parameters  =  
new  
HashMap<>();
parameters.put(
"EXAMPLE_NAME"
,  example.getName());
parameters.put(
"DATA"
,  example.getData());
exampleModelJdbcInsert
.execute(parameters);
Spring  JDBC  Demo

Spring  JDBC

ExampleServiceImpl.java  

constructor  -­‐  injec6on

show  findExample(Long  id)  -­‐  queryforObject(...)

RowMapper
Spring  JDBC  Lab

hlp://tech.lds.org/wiki/Database_Development_1

Summary

finish  implemen6ng  getAllExamples()

finish  implemen6ng  createExample()  using  the  
SimpleJdbcInsert  that  was  created  in  the  
constructor

run  the  applica6on  and  make  sure  you  can  create  
a  new  “example”
Spring  JDBC  Exception  Mapping

Spring  translates  SQLExcep6on  to  
DataAccessExcep6on  subclasses  which  are

independent  of  database  access  strategy

more  informa6ve  -­‐  in  the  Java  stack  
implementa6on  the  Oracle  error  codes  are  passed  
through  (yes,  that  is  more  informa6ve)
Spring  JDBC  Exception  Mapping

Instead  of  geZng  SQLExcep6on  for  every  
excep6on  that  occurs,  you  get...

DataIntegrityViola6onExcep6on

DuplicateKeyExcep6on

PermissionDeniedDataAccessExcep6on

BadSqlGrammarExcep6on

TypeMismatchDataAccessExcep6on

RecoverableDataAccessExcep6on
Other  Best  Practices

Use  prepared  statements  to  avoid  SQL  injec6on

Always  specify  column  names  on  insert  
statements

Avoid  sub-­‐queries,  learn  and  use  Oracle  analy6cal  
func6ons,  union,  and  minus

rownum  is  not  usually  a  valid  where  condi6on,  
however  the  row_number  analy6cal  func6on  is

More?
Conclusion

Use  a  connec6on  pool

Use  Spring  JDBC  over  raw  JDBC

The  java  stack  makes  configura6on  easier  and  
more  flexible
Final  Ques6ons?