Java database connectivity with JDBC

1
Java database connectivity with JDBC

• Frode Eika Sandnes
• Faculty of Engineering
• Oslo University College

2
About these lectures

• Assume you are all familiar with database theory,
  SQL, normalisation etc.
• Addressing practical aspects of database
  programming from Java using JDBC
• Based on material from a course on
  web-application development in Java.
• These notes and the sample code are available
  from
• http//www.iu.hio.no/frodes/dis/
• http//jdbc.postgresql.org/

3
Problems in the past

• Database programming product specific
• Require special competence
• Difficult to replace database engine
• Costly and difficult maintenance

4
Advantages of JDBC

• Allows you to program with all significant
  relational databases in existence.
• Easy to learn
• Easy to maintain
• Easy to swap to a different database product.
• Simply just change the driver
• No need to modify any code

5
Driver types

• Type 1
• Type 2
• Type 3
• Type 4

6
Type-1 driver

• Bridge drivers
• connects JDBC to other database connectivity
  mechanisms such as ODBC (Microsoft).
• Can be slow

7
Type-2 driver

• Java-to-native drivers uses JNI
• Uses functionality in a native library (C)
• Usually fast

8
Type-3 driver

• Java-to-network protocol
• 100 pure java
• Connects to database middleware over a network
• Middleware connects to the database
• Very flexible

9
Type-4 driver

• Java-to-database protocol
• 100 pure java
• Connects directly to database of the network
  using a proprietary protocol
• Usually fast

10
Programming with databases in 5 simple steps

• Register a database driver
• Connect to the database
• Execute query
• Process results
• Tidy up (close connection).

11
Registering a driver

• Simple
• import org.gjt.mm.mysal.Driver
• Driver driver new Driver()

12
Registering a driver II

• More general
• Class.forName( org.gjt.mm.mysal.Driver).ne
  wInstance()
• or
• Class.forName(driverStr).newInstance()
• Can we do better?

13
Connect to the database
Connection connection
DriverManager.getConnection(
jdbcmysql//localhost/tes
t, testuser,
I_pass)
14
The database path
jdbcsubprotokollsubnavn
jdbcmysql//localhost/test
15
Execute query

• ExecuteUpdate
• Write modifies the database
• Returns an int number of rows affected in the
  table
• ExecuteQuery
• Read no modification of the database
• Returns a ResultSet object (table)

Satement c connection.createStatement() ResultS
et r c.executeQuery(show tables)
16
Analysing the results

• The ResultSet Method
• The results are returned as a table
• The results are read row by row
• For each row individual columns are retrieved

while (resultat.next()) String fornavn
resultat.getString(firstname) String
etternavn resultat.getString(sirname)
17
Analysing the results II
18
Analysing the results II
Rs.next()
19
Analysing the results II
Rs. getString(id)
20
Analysing the results II
Rs. getString(name)
21
Analysing the results II
Rs.next()
22
Analysing the results II
Rs. getString(id)
23
Analysing the results II
Rs. getString(name)
24
Analysing the results II
Rs.next()
25
Analysing the results II
Rs. getString(id)
26
Analysing the results II
Rs. getString(name)
27
Cleaning up

• oppkobling.close()

28
When to clean up?

• When running a service
• Connect and disconnect for each query?
• Slow time consuming to connect
• Keep a connection open all the time?
• Quick as connection already established
• Connection will timeout eventually
• Hogging resources, ineffective
• Database connectivity pooling?
• More on this later

29
A complete example
import java.io. import javax.servlet. import
javax.servlet.http. import java.sql.   public
class dbtest extends HttpServlet private
String skap "create table dbtest"
" (" "
felt_no int not null" "
auto_increment"
" primary key,"
" felt_navn text" "
)"   private String leggtil "insert into
dbtest values" " ("
" null,"
" 'Dette er en test'"
" )"   private String hent
"select from dbtest"   private String rydd
"drop table dbtest"
30
A complete example
  public void service(HttpServletRequest req,
HttpServletResponse resp)
try resp.setContentType("
text/html") PrintWriter ut
resp.getWriter() // Steg 1 Registrere
driver Class.forName(
"org.gjt.mm.mysql.Driver").newInstance()
// Steg 2 Etablere forbindelse Connection
db DriverManager.getConnection(
"jdbcmysql//cube.iu.hio.no/frodes",
"frodes", "") //
Steg 3 Sende SQL-setninger Statement
kommando db.createStatement()   //
Etablere tabellen kommando.executeUpdate(ska
p)  
31
A complete example
// 3b legge elementer i tabellen
kommando.executeUpdate(leggtil)
kommando.executeUpdate(leggtil)
kommando.executeUpdate(leggtil)
kommando.executeUpdate(leggtil)   // 3c
lese tabellen fra databasen ResultSet
resultat kommando.executeQuery(hent)  
// Steg 4 Prosessere svarene
ut.println("lthtmlgtltbodygt") while
(resultat.next())
ut.println(resultat.getString(1)"ltbrgt")
ut.println("lt/bodygtlt/htmlgt")   //
Steg 5 Rydde opp kommando.executeUpdate(ryd
d) resultat.close()
kommando.close() db.close()
32
A complete example
catch (Exception e)
System.out.println("Å fysj a meg "
e.getMessage())
33
Retrieving numerical results

• Result returned in a table with one cell
• Count
• Min
• Max
• Avg
• sum

mysqlgt select count() from vare ----------
count() ---------- 10
---------- 1 row in set (0.00 sec)
34
A well-kept secret SOUNDEX

• Returns a phonetic representation of the argument
• Invented in the 1800s for sending telegrams
• Useful in information systems addressing
  telephone-based customer support

35
Soundex

• Replace each character with a numeric code,
  except the first letter
• 0 for AEIOUY
• 1 for BFPV
• 2 for CGJKQSXZ
• 3 for DT
• 4 for L
• 5 for MN
• 6 for R
• Remove all 0r
• Remove all doubles
• Keep first letter and the three leftmost digits

36
Soundex

• Example
• D312
• SOUNDEX function available in SQL
• Example query
• select from table where soundex(name)soundex(k
  ris")

37
mysqlgt select from vare --------------------
-----------------------------------------------
------- vareno navn beskrivelse
kategori beholdning pris
--------------------------------------------
------------------------------ 1 cpp
Programmering i C valgfag
39 38000 2 apput
Applikasjonsutviklin valgfag 80
79000 3 sikkerhet Datasikkerhet
med Ma valgfag 80 79000
4 grafikk Datagrafikk med Stei valgfag
19 49000 5 num
Numeriske metoder me valgfag 10
39000 6 num Kunstig
intelligens valgfag 40 39000
7 alg Algoritmer og datast
obligatorisk 149 79000 8
ark Maskinarkitektur med obligatorisk
150 79000 9 unix
Operativsystemer og obligatorisk 149
79000 10 java Programmering i
java obligatorisk 149 79000
--------------------------------------------
------------------------------ 10 rows in set
(0.00 sec)
38
mysqlgt select from vare where
soundex(navn)soundex("java") ----------------
-----------------------------------------------
------ vareno navn beskrivelse
kategori beholdning pris
--------------------------------------------
------------------------- 10 java
Programmering i java obligatorisk 149
79000 ------------------------------------
--------------------------------- 1 row in set
(0.00 sec) mysqlgt select from vare where
soundex(navn)soundex("java") ----------------
-----------------------------------------------
------ vareno navn beskrivelse
kategori beholdning pris
--------------------------------------------
------------------------- 10 java
Programmering i java obligatorisk 149
79000 ------------------------------------
--------------------------------- 1 row in set
(0.00 sec) mysqlgt select from vare where
soundex(navn)soundex("jeva") ----------------
-----------------------------------------------
------ vareno navn beskrivelse
kategori beholdning pris
--------------------------------------------
------------------------- 10 java
Programmering i java obligatorisk 149
79000 ------------------------------------
---------------------------------
39
To program, OR to exploit the database engine
an example

• A mobile service finding the closest shop in the
  neighbourhood.

40
Imagine we are at location (33,44) which shops
are within a distance of 500 metres?
select from table where
sqrt((xloc-33)(xloc-33)(yloc-44)(yloc-44)) lt
500
41
What is the problem here?
String idvar ltobtained for a user
interfacegt String sqlQuery select from
X where IDidvar
42
SQL injection

• What if the user enters the string 0 or 11
• Results in the following query
• 1 is always 1 and we leak the entire table!!!
  (Not good for confidential and sensitive
  information)

String idvar 0 or 11 String sqlQuery
select from X where IDidvar
String sqlQuery select from X where ID0
or 11
43
SQL injection II
String id ltobtained for user
interfacegt String sqlQuery insert into X
(name) values (id)
44
SQL injection II

• And the user is free to modify the database!!!
  (Generally, not a good feature)

String id ) delete from X data String
sqlQuery insert into X (name) values ()
delete from X data)
45
Prepared statements

• Parameterised queries
• Originally made for performance
• Helps us prevent SQL injection and hacking.
• ALWAYS use prepared statements

46
import java.io. import javax.servlet. import
javax.servlet.http. import java.sql. //
Dette er en fobedret versjon som benytter
prepared statements // Denne versjonen er sikker
mot SQL injeksjon // Legg merke til at semikolon
ikke er nødvendig public class dbtestFS extends
HttpServlet private String skapStr
"create table dbtest"
" (" " felt_no
int not null" "
auto_increment" "
primary key,"
" felt_navn text" "
)" private String leggtilStr "insert into
dbtest values" "
(?,?)" private String hentStr "select
from dbtest where felt_no?" private String
ryddStr "drop table dbtest"
47
public void service(HttpServletRequest req,
HttpServletResponse resp)
throws IOException resp.setContentType("
text/html") PrintWriter ut
resp.getWriter() try // Steg
1 Registrere driver Class.forName(
"org.gjt.mm.mysql.Driver").newInstance()
// Steg 2 Etablere forbindelse
Connection db DriverManager.getConnection(
"jdbcmysql//cube.iu.hio.no/frodes",
"frodes",
"trikey2fame") // Steg 3 Forberde
kommandoene PreparedStatement skape
db.prepareStatement(skapStr)
PreparedStatement leggetil db.prepareStatement(l
eggtilStr) PreparedStatement hente
db.prepareStatement(hentStr)
PreparedStatement rydde db.prepareStatement(rydd
Str) // Steg 4 Sende SQL kommandoer
// 4a Etablere tabellen skape.executeUpdate
()
48
// 4b legge elementer i tabellen
leggetil.setInt(1,1) leggetil.setString(2,"
et tekstfelt") leggetil.executeUpdate()
leggetil.setInt(1,3)
leggetil.setString(2,"et annet tekstfelt")
leggetil.executeUpdate()
leggetil.setInt(1,13) leggetil.setString(2,
"et tredje tekstfelt") leggetil.executeUpda
te() // 3c lese oppføring med
primærnøkkel 13 hente.setInt(1,13)
ResultSet resultat hente.executeQuery()
// Steg 4 Prosessere svarene
ut.println("lthtmlgtltbodygt") while
(resultat.next())
ut.println(resultat.getString(1)", "
resultat.getString(2)"ltbrgt")
ut.println("lt/bodygtlt/htmlgt") // Steg
5 Rydde opp rydde.executeUpdate()
resultat.close() db.close()
49
Division of labour

• Programmer
• Database expert
• System administrator
• Visual designer
• Language expert

50
Decreasing the coupling to the database JNDI

• Configure database details on the application
  server (sys-admin).
• Type of database
• Driver
• Location
• Username and password
• Make application 100 database independent
• Very simple porting and maintenance.

51
JNDI

• JNDI Java Naming Directory
• Configure resources on the server
• Retrieve resources via JNDI, typically databases,
  e-mail, enterprise beans etc.
• Configuration
• Application deployment descriptor web.cml is the
  glue between the server and the application

Application
App-config (web.xml)
Server config (server.xml)
52
Server configuration (server.xml)
ltContext path"" docBase"ROOT" debug"0"gt
ltResource name"jdbc/testDB"
auth"Container"
type"javax.sql.DataSource"/gt
ltResourceParams name"jdbc/testDB"gt
ltparametergt
ltnamegtuserlt/namegt
ltvaluegtfrodeslt/valuegt
lt/parametergt
ltparametergt
ltnamegtpasswordlt/namegt
ltvaluegttrikey2famelt/valuegt
lt/parametergt
ltparametergt
ltnamegtdriverClassNamelt/namegt

ltvaluegtorg.gjt.mm.mysql.Driverlt/valuegt
lt/parametergt
ltparametergt
ltnamegtdriverNamelt/namegt
ltvaluegtjdbcmysql//cube.iu.hi
o.no/frodeslt/valuegt
lt/parametergt lt/ResourceParamsgt
lt/Contextgt
53
Application configuration (web.xml)
lt?xml version"1.0" encoding"ISO-8859-1"?gt lt!DOC
TYPE web-app PUBLIC "-//Sun Microsystems,
Inc.//DTD Web Application 2.3//EN"
"http//java.sun.com/dtd/web-app_2_3.dtd"gt ltweb-a
ppgt ltresource-refgt ltres-ref-namegtjdbc/
testDBlt/res-ref-namegt ltres-typegtjavax.sql.
DataSourcelt/res-typegt ltres-authgtContainerlt
/res-authgt lt/resource-refgt lt/web-appgt
54
import java.io. import javax.servlet. import
javax.servlet.http. import java.sql. import
javax.sql. import javax.naming. public class
dbtestJNDI extends HttpServlet private
String skapStr "create table dbtest"
" ("
" felt_no int not null"
" auto_increment"
" primary key,"
" felt_navn text"
" )" private String leggtilStr
"insert into dbtest values"
" (?,?)" private String hentStr
"select from dbtest where felt_no?"
private String ryddStr "drop table dbtest"
public void service(HttpServletRequest req,
HttpServletResponse resp)
throws IOException resp.setContentType("
text/html") PrintWriter ut
resp.getWriter()
55
try // Steg 1 Finne datakilde fra
JNDI Context rotKontekst new
InitialContext() Context miljøKontekst
(Context)rotKontekst.lookup("javacomp/env")
DataSource dataKilde (DataSource)miljøKontekst
.lookup("jdbc/testDB") // Steg 2 Hente
forbindelse fra databasepoolen. Connection
db dataKilde.getConnection() // Steg 3
Forberde kommandoene PreparedStatement
skape db.prepareStatement(skapStr)
PreparedStatement leggetil db.prepareStatement(l
eggtilStr) PreparedStatement hente
db.prepareStatement(hentStr)
PreparedStatement rydde db.prepareStatement(rydd
Str) // Steg 4 Sende SQL kommandoer
// 4a Etablere tabellen skape.executeUpdate
() // 4b legge elementer i tabellen
leggetil.setInt(1,1) leggetil.setString(2,"
et tekstfelt") leggetil.executeUpdate()
leggetil.setInt(1,3)
leggetil.setString(2,"et annet tekstfelt")
leggetil.executeUpdate() ...
56
E-mail simply the same thing..
ltContext path"" docBase"ROOT" debug"0"gt
ltResource name"mail/Session"
auth"Container" type"javax.mail.Sess
ion"/gt ltResourceParams name"mail/Session"gt
ltparametergt ltnamegtmail.smtp.hostlt/namegt
ltvaluegtca30fag.iu.hio.nolt/valuegt
lt/parametergt lt/ResourceParamsgt lt/Contextgt
57
Application deployment descriptor (web.xml)
lt?xml version"1.0" encoding"ISO-8859-1"?gt lt!DOC
TYPE web-app PUBLIC "-//Sun Microsystems,
Inc.//DTD Web Application 2.3//EN"
"http//java.sun.com/dtd/web-app_2_3.dtd"gt ltweb-a
ppgt ltresource-refgt ltres-ref-namegtjmail/Sess
ionlt/res-ref-namegt ltres-typegtjavax.mail.Sessi
onlt/res-typegt ltres-authgtContainerlt/res-authgt
lt/resource-refgt lt/web-appgt
58
import java.io. import javax.servlet. import
javax.servlet.http. import javax.naming. impor
t javax.mail. import javax.mail.internet. pub
lic class Epost extends HttpServlet public
void service(HttpServletRequest req,
HttpServletResponse resp) throws
IOException resp.setContentType("text/ht
ml") PrintWriter ut resp.getWriter()
try // Steg 1 Finne datakilde fra
JNDI Context rotKontekst new
InitialContext() Context miljøKontekst
(Context)rotKontekst.lookup("javacomp/env")
Session økt (Session) miljøKontekst.lookup("ma
il/Session") Message beskjed new
MimeMessage(økt) beskjed.setFrom(new
InternetAddress("kongen_at_haugen.no"))
InternetAddress til new InternetAddress1
til0 new InternetAddress("frodes_at_iu.hio.no
") beskjed.setRecipients(Message.RecipientT
ype.TO, til) beskjed.setSubject("Bare
spam!!!") beskjed.setContent("Hei på
deg.", "text/plain") Transport.send(beskjed
) ut.println("lthtmlgtltbodygtMail
sendt...lt/bodygtlt/htmlgt")
59
Thats it!!!