database connections-githeko-presentation

8/8/2019 Database Connections-githeko-presentation

http://slidepdf.com/reader/full/database-connections-githeko-presentation 1/17

Connecting to a Database from a

Servlet

J M Githeko



ObjectivesTo be able to:y Query a database via a web interfacey View or browse datay Modify datay View database metadata [characteristics of

database]



Introduction to JDBC

JDBC = Java Database Connector JDBC is included in Java SDKsJDBC is a driver that allows access todatabasesT here are four types fo JDBCs:y T ype 1 - JDBC-ODBC Bridgey T ype 2 - Native API, partly Javay T ype 3 - Pure Java to database middlewarey T ype 4 ± Pure Java direct to database



Type 1: JDBC- ODBC Bridge

R elies on existence of ODBC driversR equires code library JdbcOdbc.dllLimited to MS WindowsODBC is not thread-safe - leads to poor performance

ODBC datasource has to be configuredLimited by underlying ODBC capabilitiesBUT ± convenient where available ± good for

learning JDDBC



Type 2 ² Native AP I

Similar to ODBCR ely on native (OS-specific) code librariesusually in the form of DLLsClient must be configured to use the nativedrivers



Type 3 - MiddlewareCommunicate via a middleware server Performance affected by ³middleware´architectureProvide greatest flexibility



Type 4 ² Pure Java Direct to

Database

Driver talks direct to database

Drivers provided by DBMS vendor Provides fastest performance



Install MySQL JDBC Connector Install MySQL JDBC Connector

Download and uncompress .zip file [useDownload and uncompress .zip file [useWinzip, 7zip or similar program]Winzip, 7zip or similar program]Locate .jar fileLocate .jar fileIn my case namedIn my case named mysqlmysql- -connector connector- -java java- -5.1.75.1.7--bin.jar bin.jar Copy .jar into the folder namedCopy .jar into the folder named< java_home< java_home >>\ \jre jre\\liblib\ \extextIn my case:In my case:

C:C: \ \Program FilesProgram Files\ \JavaJava\ \jdk1.6.0_11 jdk1.6.0_11\ \jre jre\ \liblib\ \extext



Application Application- -Specific Placement for Specific Placement for

MySQL Connector/JMySQL Connector/J

If Application Server can not findIf Application Server can not find javax.http.* javax.http.* andand javax.servlet.* javax.servlet.* then place itthen place itin also inin also in < java_home< java_home >>\ \jre jre\ \liblib\ \extext



Application Application- -Specific Placement of Specific Placement of

MySQL Connector/JMySQL Connector/JIf application cannot find MySQLIf application cannot find MySQLConnector, place MySQL Connector/J .jar Connector, place MySQL Connector/J .jar also inalso in

$ CATALINA _HOME$ CATALINA _HOME /webapp/yourapp/WEB /webapp/yourapp/WEB- -IN F/lib/IN F/lib/

In my server:In my server:C:C: \ \Program FilesProgram Files\ \A pache SoftwareA pache SoftwareFoundationFoundation\ \T omcatT omcat6.06.0 \\webappswebapps\ \ex5ex5\\WEBWEB--INFINF\\liblib



Using the MySQL Connector/JUsing the MySQL Connector/JR egister the C onnector/J JDB C Driver for MySQ LR egister the C onnector/J JDB C Driver for MySQ L

Class.forName("com.mysql.jdbc.Driver").newInstance();Class.forName("com.mysql.jdbc.Driver").newInstance();

E.g.:E.g.: Class.forName("com.mysql.jdbc.Driver");Class.forName("com.mysql.jdbc.Driver");

Make the C onnection to the Database by Using theMake the C onnection to the Database by Using theDriverManager C lassDriverManager C lassjdbc:mysql://[host][,failoverhost..][:port]/[database][?propertjdbc:mysql://[host][,failoverhost..][:port]/[database][?propert

yName1][=propertyVal1][&propertyName2][=propertyVal2]..yName1][=propertyVal1][&propertyName2][=propertyVal2]..

Example :Example :

Connection con = null;Connection con = null;con=DriverManager.getConnection("jdbc:mysql:con=DriverManager.getConnection("jdbc:mysql:

//localhost:3306/example5", "root", "");//localhost:3306/example5", "root", "");

^database ^user ̂ p/word^database ^user ̂ p/word



Cont¶dCont¶d

1.1. Get a Statement ObjectGet a Statement ObjectS tatement stmt = conn.create S tatement();S tatement stmt = conn.create S tatement();

Example :Example :

St a t emen t s t m t = null;St a t emen t s t m t = null;s t m t = con.crea t e St a t emen t ();s t m t = con.crea t e St a t emen t ();

1.1. Execute the SQ L StatementExecute the SQ L Statement

Example :Example :Resul tS e t rs = null;Resul tS e t rs = null;rs = s t m t .execu t eQuery(" SELE CT * F ROM rs = s t m t .execu t eQuery(" SELE CT * F ROM

class1");class1");



T o Update R ecordsT o Update R ecords

Use executeUpdate() Statement methodUse executeUpdate() Statement method

in t upda t eCoun t = 0 ;in t upda t eCoun t = 0 ;

upda t eCoun tupda t eCoun t == s t m t .execu t eUpda t es t m t .execu t eUpda t e ("(" IN SE RT IN SE RT INTO Class1 VAL UESINTO Class1 VAL UES

(µJohn¶,¶ S 13/2 0 20 / 05 ¶)"(µJohn¶,¶ S 13/2 0 20 / 05 ¶)" )) ;;



R etrieve the Data From theR etrieve the Data From theR

esult SetR

esult SetIterate through a resultIterate through a result- -set:set: w hile (rs.nex t ()) w hile (rs.nex t ())

{{

String

Name

=rs.ge

tString(1);

String

Name

=rs.ge

tString(1);St ring Reg_No = rs.ge tSt ring(2);St ring Reg_No = rs.ge tSt ring(2);

S ys t em.ou t .prin t (Name); S ys t em.ou t .prin t (Name);

S ys t em.ou t .prin t ln(Reg_No);S ys t em.ou t .prin t ln(Reg_No);} }

Method getString(1) gets first String item in the recordMethod getString(1) gets first String item in the recordMethod getString() gets second String item in the recordMethod getString() gets second String item in the record



R elease the R esourcesR elease the R esources

It¶s important to close the R esultSet,It¶s important to close the R esultSet,Statement, and Connection() objects.Statement, and Connection() objects.

Once closed, gabbage collection can beOnce closed, gabbage collection can bedone to remove objects from memory:done to remove objects from memory:rs.close(); rs.close();

s t m t .close(); s t m t .close();

conn.close();conn.close();



Complete ExampleComplete Example

DBConnection.javaDBConnection.java



Exercise 5/5Exercise 5/5

Write a servlet to print AICM course list from aWrite a servlet to print AICM course list from adatabase containing AICM course listing.database containing AICM course listing.Create two versions of the servlet:Create two versions of the servlet:

1.1. Print simple list with C ODE and TITL E onlyPrint simple list with C ODE and TITL E only2.2. Print complete list with C ODE, TITL E, C F andPrint complete list with C ODE, TITL E, C F and

DES C R IP TI ONDES C R IP TI ON

H ints:H ints:Collect the dataCollect the dataT abulateT abulate

Create MySQL database, import AICM dataCreate MySQL database, import AICM data

Write servlet to query databaseWrite servlet to query database

database connections-githeko-presentation

Documents