JDBC Dr Jim Briggs WEBP JDBC JDBC Java

JDBC Dr Jim Briggs WEBP JDBC

JDBC • Java Database Connectivity • An API for connecting Java programs (applications, applets and servlets) to databases • Largely database independent – Requires SQL WEBP JDBC 2

JDBC architecture WEBP JDBC 3

Using JDBC • Obtaining drivers • Making a connection • Executing a command – Statements – Result sets WEBP JDBC 4

Obtaining drivers • Usually provided (free) by database vendors • Common ones – oracle. jdbc. driver. Oracle. Driver – com. mysql. jdbc. Driver – sun. jdbc. odbc. Jdbc. Odbc. Driver • Need to download and install – In a webapp, put the Jar file in WEB-INF/lib WEBP JDBC 5

Connecting to a database • Via a Driver. Manager (old) – Class. for. Name(Driver. Class. Name). new. Instance(); – Connection con = Driver. Manager. get. Connection(URL, Username, Password); • Via a Data. Source (new) – Create Data. Source object in configuration, e. g. • in Tomcat’s context. xml file • in an application server's environment WEBP JDBC 6

Connection URLs • JDBC has a special form of URL • Define location of database and access parameters in a driver-specific way • Examples: – jdbc: oracle: thin: @localhost: 1521: JIM – jdbc: mysql: //localhost: 3306/JIM WEBP JDBC 7

Doing a query Statement stmt = conn. create. Statement(); Result. Set result = stmt. execute. Query( "SELECT Entry, Customer, DOW, Cups, Type " + "FROM JJJJData " + "ORDER BY Cups DESC“ ); while(result. next()) { out. println(result. get. String(1)); out. println(result. get. Int(“Cups”); } WEBP JDBC 8

Further features • Prepared statements • Bind variables • Transactions WEBP JDBC 9

JDBC and web applications • Special considerations – Connection latency – Connection pooling – Multiple connections – One connection per thread (request) – One transaction per request (normally) WEBP JDBC 10