JDBC JDBC n JDBC is a Sun trademark

JDBC

JDBC n JDBC is a Sun trademark n n n It is often taken to stand for Java Database Connectivity Java is very standardized, but there are many versions of SQL JDBC is a means of accessing SQL databases from Java n n On the one hand, JDBC is a standardized API for use by Java programs On the other hand, JDBC is a specification for how third-party vendors should write database drivers to access specific SQL versions

Driver types n There are four types of drivers: n JDBC Type 1 Driver -- JDBC/ODBC Bridge drivers n n n ODBC (Open Data. Base Connectivity) is a standard software API designed to be independent of specific programming languages Sun provides a JDBC/ODBC implementation JDBC Type 2 Driver -- use platform-specific APIs for data access JDBC Type 3 Driver -- 100% Java, use a net protocol to access a remote listener and map calls into vendor-specific calls JDBC Type 4 Driver -- 100% Java n Most efficient of all driver types

Connector/J n n Connector/J is a JDBC Type 4 Driver for connecting Java to My. SQL Installation is very simple: n n n Download the “Production Release” ZIP file from http: //dev. mysql. com/downloads/connector/j/3. 1. ht ml Unzip it Put the JAR file where Java can find it n n Add the JAR file to your CLASSPATH, or In Eclipse: Project --> Properties --> Java Build Path --> Libraries --> Add External Jars. . .

Connecting to the server n n First, make sure the My. SQL server is running In your program, n import java. sql. Connection; // not com. mysql. jdbc. Connection import java. sql. Driver. Manager; import java. sql. SQLException; n n n Register the JDBC driver, Class. for. Name("com. mysql. jdbc. Driver"). new. Instance(); Invoke the get. Connection() method, Connection con = Driver. Manager. get. Connection("jdbc: mysql: ///my. DB", my. User. Name, my. Password); or get. Connection("jdbc: mysql: ///my. DB? user=dave&password=xxx")

A complete program n import java. sql. Connection; import java. sql. Driver. Manager; import java. sql. SQLException; public class Jdbc. Example 1 { } public static void main(String args[]) { Connection con = null; try { Class. for. Name("com. mysql. jdbc. Driver"). new. Instance(); con = Driver. Manager. get. Connection("jdbc: mysql: ///test", "root", ”rootpswd"); if (!con. is. Closed()) System. out. println("Successfully connected to My. SQL server. . . "); } catch(Exception e) { System. err. println("Exception: " + e. get. Message()); } finally { try { if (con != null) con. close(); } catch(SQLException e) {} } } Adapted from http: //www. stardeveloper. com/articles/display. html? article=2003090401

Using the Connection object n public Statement create. Statement() throws SQLException n Creates a Statement object for sending SQL statements to the database. SQL statements without parameters are normally executed using Statement objects. The Statement object may be reused for many statements public Prepared. Statement prepare. Statement(String sql) throws SQLException n n Creates a Prepared. Statement object for sending parameterized SQL statements to the database. A SQL statement with or without IN parameters can be pre-compiled and stored in a Prepared. Statement object. This object can then be used to efficiently execute this statement multiple times.

Issuing queries n The following are methods on the Statement object: n int execute. Update() -- for issuing queries that modify the database and return no result set n n n Use for DROP TABLE, CREATE TABLE, and INSERT Returns the number of rows in the resultant table Result. Set execute. Query() -- for queries that do return a result set. n Returns results as a Result. Set object

Creating a table n n n This example is from http: //www. kitebird. com/articles/jdbc. html CREATE TABLE animal ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), name CHAR(40), category CHAR(40) ) Statement s = conn. create. Statement (); s. execute. Update ("DROP TABLE IF EXISTS animal"); s. execute. Update ( "CREATE TABLE animal (" + "id INT UNSIGNED NOT NULL AUTO_INCREMENT, " + "PRIMARY KEY (id), “ + "name CHAR(40), category CHAR(40))");

Populating the table n int count; count = s. execute. Update ( "INSERT INTO animal (name, category)" + " VALUES" + "('snake', 'reptile'), " + "('frog', 'amphibian'), " + "('tuna', 'fish'), " + "('racoon', 'mammal')"); s. close (); System. out. println (count + " rows were inserted");

Result. Set n execute. Query() returns a Result. Set n Result. Set has a very large number of get. XXX methods, such as n n Results are returned from the current row You can iterate over the rows: n n public String get. String(String column. Name) public String get. String(int column. Index) public boolean next() Result. Set objects, like Statement objects, should be closed when you are done with them n public void close()

Example, continued n Statement s = conn. create. Statement (); s. execute. Query ("SELECT id, name, category FROM animal"); Result. Set rs = s. get. Result. Set (); int count = 0; // Loop (next slide) goes here rs. close (); System. out. println (count + " rows were retrieved");

Example, continued n while (rs. next ()) { int id. Val = rs. get. Int ("id"); String name. Val = rs. get. String ("name"); String cat. Val = rs. get. String ("category"); System. out. println ( "id = " + id. Val + ", name = " + name. Val + ", category = " + cat. Val); ++count; }

Prepared statements n Prepared statements are precompiled, hence much more efficient to use n Prepared. Statement s; s = conn. prepare. Statement ( "INSERT INTO animal (name, category VALUES(? , ? )"); s. set. String (1, name. Val); s. set. String (2, cat. Val); int count = s. execute. Update (); s. close (); System. out. println (count + " rows were inserted");

Error handling n try { Statement s = conn. create. Statement (); s. execute. Query ("XYZ"); // issue invalid query s. close (); } catch (SQLException e) { System. err. println ("Error message: " + e. get. Message ()); System. err. println ("Error number: " + e. get. Error. Code ()); }

The End