Java Database Connectivity JDBC Java ECommerce Martin Cooke

Java Database Connectivity (JDBC) Java E-Commerce © Martin Cooke, 2003

Plan • • Java database connectivity API (JDBC) Examples Advanced features JNDI 9/25/2021 Java E-Commerce © Martin Cooke, 2003 2

JDBC Java E-Commerce © Martin Cooke, 2003

Design goals • Build on the simplicity and portability of SQL • Simple to use for non-database programmers – queries return Java objects – access problems return as exceptions • Hide specific database details from programmer • Generalise to any tabular data source • Learn from (and avoid the mistakes of) ODBC 9/25/2021 Java E-Commerce © Martin Cooke, 2003 4

ODBC • Open database connectivity – Open = so long as you operate within Windows : -) • C API which doesn’t translate well into Java • Overly complex design from programmers perspective • Complete failure to transit outside Windows environment 9/25/2021 Java E-Commerce © Martin Cooke, 2003 5

JDBC architecture My. SQL Oracle Postgres 9/25/2021 Java E-Commerce © Martin Cooke, 2003 6

JDBC architecture • JDBC defines a set of interfaces • Vendors implement these for their database engine • Known as a JDBC driver JDBC My. SQL Oracle Postgres 9/25/2021 Java E-Commerce © Martin Cooke, 2003 7

JDBC architecture • JDBC defines a set of interfaces • Vendors implement these for their database engine • Known as a JDBC driver • Easy to switch vendors without changing a line of code (simple an entry in a configuration file choosing a different driver) • Can develop on a toy system like MS Access then migrate to industrialstrength later with ease App 1 App 2 JDBC My. SQL Oracle Postgres 9/25/2021 Java E-Commerce © Martin Cooke, 2003 8

How-to Acquire a (free) relational database engine – – postgresql My. SQL m. SQL instant. DB Acquire a (free) JDBC driver for the chosen engine – See list at industry. java. sun. com/products/jdbc/drivers 9/25/2021 Java E-Commerce © Martin Cooke, 2003 9

My. SQL Some factoids • Developed from 1995 by Michael Widenius in Sweden • Described as “one of the hottest grass roots software projects since Linux” • Ran on estimated 500, 000 servers in 1999 and has grown significantly since • Available for most platforms • Industrial-strength: fast and stable • Extensively documented • Available in DCS 9/25/2021 Java E-Commerce © Martin Cooke, 2003 10

Accessing your data in 4 steps Java E-Commerce © Martin Cooke, 2003

1: load the driver Connection con = null; try { Class. for. Name("org. gjt. mm. mysql. Driver"). new. Instance(); con = Driver. Manager. get. Connection ("jdbc: mysql: //hazel/studs", “mpc", ""); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT name FROM student"); while (rs. next()) System. out. println(rs. get. String(1)); } catch (SQLException e) { System. out. println("SQLException: " + e. get. Message()); } catch (Exception e) { System. err. println("Unable to load driver. "); e. print. Stack. Trace(); } 9/25/2021 Java E-Commerce © Martin Cooke, 2003 12

2: get a connection Connection con = null; try { Class. for. Name("org. gjt. mm. mysql. Driver"). new. Instance(); con = Driver. Manager. get. Connection ("jdbc: mysql: //hazel/studs", “mpc", ""); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT name FROM student"); while (rs. next()) System. out. println(rs. get. String(1)); } catch (SQLException e) { System. out. println("SQLException: " + e. get. Message()); } catch (Exception e) { System. err. println("Unable to load driver. "); e. print. Stack. Trace(); } 9/25/2021 Java E-Commerce © Martin Cooke, 2003 13

3: execute a query Connection con = null; try { Class. for. Name("org. gjt. mm. mysql. Driver"). new. Instance(); con = Driver. Manager. get. Connection ("jdbc: mysql: //hazel/studs", “mpc", ""); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT name FROM student"); while (rs. next()) System. out. println(rs. get. String(1)); } catch (SQLException e) { System. out. println("SQLException: " + e. get. Message()); } catch (Exception e) { System. err. println("Unable to load driver. "); e. print. Stack. Trace(); } 9/25/2021 Java E-Commerce © Martin Cooke, 2003 14

4: do something with the results Connection con = null; try { Class. for. Name("org. gjt. mm. mysql. Driver"). new. Instance(); con = Driver. Manager. get. Connection ("jdbc: mysql: //hazel/studs", “mpc", ""); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT name FROM student"); while (rs. next()) System. out. println(rs. get. String(1)); } catch (SQLException e) { System. out. println("SQLException: " + e. get. Message()); } catch (Exception e) { System. err. println("Unable to load driver. "); e. print. Stack. Trace(); } 9/25/2021 Java E-Commerce © Martin Cooke, 2003 15

Tidying up a precious resource … rs. close(); stmt. close(); } finally { if (con != null) con. close(); } 9/25/2021 Java E-Commerce © Martin Cooke, 2003 16

Key JDBC classes 9/25/2021 Driver. Manager Used, amongst other things, to obtain connection Connection Logical connection used to send SQL to the database Statement SQL statement Result. Set Data returned by a query Java E-Commerce © Martin Cooke, 2003 17

More on statements • For queries – public Result. Set execute. Query(String sql) • For updates – public int execute. Update(String sql) (returns number of rows affected) • When you don’t know which – public boolean execute(String sql) 9/25/2021 Java E-Commerce © Martin Cooke, 2003 18

More on result sets • Provides access to database queries one row at a time • Moves through sequentially, once only • Result. Set object is a reference, since actual result set could be very large 9/25/2021 Java E-Commerce © Martin Cooke, 2003 19

More on result sets • Methods for getting common data types – get. Date() – get. Int() – get. String() • Can use get. Object() if you don’t know the type • JDBC 2. 0 provides scrollable result sets (later) 9/25/2021 Java E-Commerce © Martin Cooke, 2003 20

Other features of JDBC Java E-Commerce © Martin Cooke, 2003

Other features of JDBC • • • Meta data Prepared statements Transactions Scrollable result sets Storing binary data 9/25/2021 Java E-Commerce © Martin Cooke, 2003 22

Meta data • Suppose we don’t know how many columns will be returned by a query; or what the columns are called • Use Result. Set. Meta. Data class Result. Set. Meta. Data rsmd = rs. get. Meta. Data() rsmd. get. Column. Count(i) rsmd. get. Column. Label(i) 9/25/2021 Java E-Commerce © Martin Cooke, 2003 23

Prepared statements • Like an ordinary Statement in that it represents an SQL statement • Is precompiled by database for faster execution • Can be modified after compilation Prepared. Statement pstmt = con. prepare. Statement( “SELECT name FROM student WHERE id=? ”); pstmt. clear. Parameters(); pstmt. set. Int(1, some. Value); Result. Set rs = pstmt. execute. Query(); 9/25/2021 Java E-Commerce © Martin Cooke, 2003 24

Prepared statements contd • Useful when need to run same (parameterised) statement many times • Also useful when there are lots of parameters to fill in, to avoid huge and messy SQL strings • Also useful because of the quotes problem: INSERT INTO student VALUES (null, ‘Basil d’Oliveira’) – Either remember to escape quotes with or – Use prepared statement 9/25/2021 Java E-Commerce © Martin Cooke, 2003 25

Transactions • Classic example: transferring money between accounts: – Debit account 1, credit account 2 – What if system fails between the two? • Less problematic in some applications – reading incorrect data 9/25/2021 Java E-Commerce © Martin Cooke, 2003 26

Transaction issues Dirty reads transaction 1 can see uncommitted changes from transaction 2 nonrepeatable reads 1. transaction 1 reads a row 2. transaction 2 alters the row 3. transaction rereads row, getting different values Changes in another transaction cause new rows to match your WHERE clause Phantom reads 9/25/2021 Java E-Commerce © Martin Cooke, 2003 27

Transaction isolation • Transaction isolation defines the visibility of one transaction on another • JDBC specifies 5 levels of isolation weakest: stongest: TRANSACTION_NONE (no support for transactions) TRANSACTION_SERIALIZABLE (full isolation) • Typically, tradeoff between isolation and speed – Full isolation is costly and often OTT 9/25/2021 Java E-Commerce © Martin Cooke, 2003 28

Transactions in JDBC try { con. set. Auto. Commit(false); // defaults to true s = con. create. Statement(); s. execute. Update(“INSERT …”); s. close(); s = con. create. Statement(); s. execute. Update(“INSERT …”); con. commit(); s. close(); } catch (SQLException e) { if (con != null) { try { con. rollback(); } catch (SQLException e) {} } } 9/25/2021 Java E-Commerce © Martin Cooke, 2003 29

My. SQL support for transactions • Yes, since version 3. 23. 34 • Done via Berkeley. DB • Simply add table type at table create time: CREATE TABLE student ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(4), SSID INT DEFAULT 0 ) TYPE=BDB; 9/25/2021 Java E-Commerce © Martin Cooke, 2003 30

Scrollable result sets • Added in JDBC 2. 0 • Allows in-place modification of result sets and more control of the result set ‘cursor’ • Can be inefficient for some database drivers 9/25/2021 Java E-Commerce © Martin Cooke, 2003 31

Storage/retrieval of large/binary data Large text strings (eg entire books), up to several gigs LONGVARCHAR get. Ascii. Stream() Binary objects (eg GIF images, sound files) BINARY VARBINARY LONGVARBINARY get. Binary. Stream() • Can be source of problems due to drivers • Sometimes have small maximum sizes • Underlying object is often read all at once 9/25/2021 Java E-Commerce © Martin Cooke, 2003 32

Binary and character large objects Large text strings (eg entire books), up to several gigs LONGVARCHAR get. Ascii. Stream() CLOB get. Clob() Binary objects (eg GIF images, sound files) BINARY VARBINARY LONGVARBINARY BLOB get. Binary. Stream() get. Blob() • New in JDBC 2. 0 • Return streams, so data read at your leisure • Not so simple to store 9/25/2021 Java E-Commerce © Martin Cooke, 2003 33

Connection pooling Java E-Commerce © Martin Cooke, 2003

Connection bottlenecks • Creating a connection is a very expensive operation (several seconds, typically) • The “open connection, talk to database, close connection” model is simply infeasible for typical e -commerce applications 9/25/2021 Java E-Commerce © Martin Cooke, 2003 35

Solutions Singleton: open a single connection when webapp inits and use it for all DB operations 9/25/2021 + guaranteed to be thread safe, but consequently doesn’t avoid performance bottleneck + easy and fast for small sites - doesn’t exploit database support for simultaneous connections Java E-Commerce © Martin Cooke, 2003 36

Solutions Singleton: open a single connection when webapp inits and use it for all DB operations + guaranteed to be thread safe, but consequently doesn’t avoid performance bottleneck + easy and fast for small sites - doesn’t exploit database support for simultaneous connections Connection pool: open a pool of connections on init and dish them out as needed + supported in JDBC 2. 0 + easy + fast + scales to very large sites 9/25/2021 Java E-Commerce © Martin Cooke, 2003 37

Solutions Singleton: open a single connection when webapp inits and use it for all DB operations + guaranteed to be thread safe, but consequently doesn’t avoid performance bottleneck + easy and fast for small sites - doesn’t exploit database support for simultaneous connections Connection pool: open a pool of connections on init and dish them out as needed + supported in JDBC 2. 0 + easy + fast + scales to very large sites Session connections: allocate a connection for each active user (session) + very fast + quite easy to implement - not good for sites with very high traffic 9/25/2021 Java E-Commerce © Martin Cooke, 2003 38

Connection pool how-to: 1 • Use 3 rd party connection pool class – com. javaexchange. db. Connection. Broker my. Broker = new Db. Connection. Broker (driver, db. URL, user, pwd, min. Cs, max. Cs, logfile, max. Connect. Time); conn = my. Broker. get. Connection(); my. Broker. free. Connection(conn); • Not necessarily portable since doesn’t follow API 9/25/2021 Java E-Commerce © Martin Cooke, 2003 39

Connection pool how-to: 2 • Use JDBC 2. 0 Optional Package (ie find a driver that supports it) – Feb 2002 version of mm driver • Several advantages other than pooling – Abstracts most connection details – Supports distributed transactions • Transactions which span more than one datasource eg buy book, update book db, credit card db, … • Not norm yet, but likely to be soon because of vendor-independence engendered by JDBC 9/25/2021 Java E-Commerce © Martin Cooke, 2003 40

Data. Source Context ctx = new Initial. Context(); Data. Source ds = (Data. Source) ctx. lookup(“jdbc/mydb”); 9/25/2021 • A context is a JNDI concept (see next slide) • Use context to lookup a Data. Source Java E-Commerce © Martin Cooke, 2003 41

Data. Source • A context is a JNDI concept (see next slide) • Use context to lookup a Data. Source ds = (Data. Source) ctx. lookup(“jdbc/mydb”); • Get connection (from pool) • Use it Connection con = • Close it (returns it to the ds. get. Connection(user, pwd); pool) Context ctx = new Initial. Context(); // use connection con. close() 9/25/2021 Java E-Commerce © Martin Cooke, 2003 42

JNDI • Java Naming and Directory Interface • Enterprise-level API used to provide uniform access to services: – File systems – Databases – Distributed objects (later) 9/25/2021 Java E-Commerce © Martin Cooke, 2003 43

JNDI • A naming service matches things such as printers, file servers, … to names Source: http: //www. javaworld. com/javaworld/jw-022000/jw-02 -howto. html 9/25/2021 Java E-Commerce © Martin Cooke, 2003 44

JNDI • A naming service matches things such as printers, file servers, … to names • A directory service adds attributes to the names (eg colour printer, database driver class) Source: http: //www. javaworld. com/javaworld/jw-022000/jw-02 -howto. html 9/25/2021 Java E-Commerce © Martin Cooke, 2003 45

Why JNDI? • Uniform access to existing & diverse naming services: – COS (Common Object Services) Naming: The naming service for CORBA applications; – DNS (Domain Name System): The Internet's naming service; maps people-friendly names (such as www. etcee. com) into computer-friendly IP (Internet Protocol) addresses in dotted-quad notation (207. 69. 175. 36). – LDAP (Lightweight Directory Access Protocol): Developed by the University of Michigan; as its name implies, it is a lightweight version of DAP (Directory Access Protocol), which in turn is part of X. 500, a standard for network directory services. – NIS (Network Information System) and NIS+: Network naming services developed by Sun Microsystems. Both allow users to access files and applications on any host with a single ID and password. Source: http: //www. javaworld. com/javaworld/jw-02 -2000/jw-02 -howto. html 9/25/2021 Java E-Commerce © Martin Cooke, 2003 46

JNDI benefits • Virtual: uses simple URLs and hierarchical structure • Dynamic: can configure services at runtime • Enables finding by logical names rather than by knowing the paths Data. Source ds = (Data. Source) ctx. lookup(“jdbc/mydb”); 9/25/2021 Java E-Commerce © Martin Cooke, 2003 47

JNDI how-to • Objects (such as Data. Source) have to be bound to the JNDI service before they can be accessed by clients • Usually done with a GUI tool; one-off process • Can be done programmatically: Context ctx = new Initial. Context(); ds = new Mysql. Data. Source(); ds. set. Server. Name("localhost"); ds. set. Database. Name("test"); ctx. bind(“jdbc/test", ds); ctx. close(); • Don’t forget to import java. naming. * 9/25/2021 Java E-Commerce © Martin Cooke, 2003 48