JDBC Java Database Connectivity 1 Useful JDBC Links
JDBC "Java Database Connectivity" 1
Useful JDBC Links • Getting Started Guide: http//: java. sun. com/j 2 se/1. 5. 0/docs/guide/jdbc/g etstart/Getting. Started. TOC. fm. html • java. sql Package API: http: //java. sun. com/j 2 se/1. 5. 0/docs/api/java/sql/ package-summary. html 2
Introduction to JDBC • JDBC allows for convenient database access from Java applications • Data is transferred from relations to objects and vice-versa – databases optimized for searching/indexing – objects optimized for engineering/flexibility 3
Why Access a Database from within a Program? • As we saw last week, some queries can’t be computed in SQL. • PL/pg. SQL includes more programming tools than SQL • However, sometimes using PL/pg. SQL will not be suitable: – If we require object-oriented programming – If accessing the database is a small part of a large Java application – Etc. • Why not keep all the data in Java objects? • “Separation of concerns”: DBMSes concentrate on data storage and access; programs concentrate on algorithms, networking, etc. 4
Packages to Import • In order to connect to a database from java, import the following packages: – java. sql. *; (usually enough) – javax. sql. * (for advanced features, such as scrollable result sets) 5
access • Add the following line to your. classpath file (located in your home directory): setenv CLASSPATH ${CLASSPATH}: /usr/share/java/postgresql. jar • And then open a new shell 6
Six Steps • Load the driver • Establish the Connection • Create a Statement object • Execute a query • Process the result • Close the connection 7
JDBC Architecture (1) Driver Manager Application Driver DBMS • Driver. Manager is provided by Java Software as part of the Java 2 Platform. • Drivers are provided by DBMS vendors.
JDBC Architecture (2) • The application creates a driver instance and registers it with the Driver. Manager. • The Driver. Manager tells the driver to connect to the DB • The Driver. Manager keeps track of registered driver instances and their connections to DB’s. • The Driver “talks” to a particular DB through the connection
Connecting 1. Initializing a driver and registering it with the Driver. Manager: Class. for. Name(“org. postgresql. Driver"); 2. Getting a connection: Driver. Manager. get. Connection(URL) • The URL is: “jdbc: postgresql: //dbserver/public”, ”YOUR-LOGIN”, null 10
For example Class. for. Name(“org. postgresql. Driver"); Driver. Manager. get. Connection (“jdbc: postgresql: //dbserver/public”, ”gidi”, null); 11
Interacting with the DB • Once you have established a connection, your would like to interact with the DB • Interaction are done by sending Statements and Prepared. Statements to the DB • These are used for 2 things (using different methods): 1. Querying the DB (execute. Query) 2. Changing the DB (execute. Update) 12
Statement create. Statement() – returns a new Statement object • Used to send SQL commands to the DB • Created via the connection object
Statement query methods 1. stmt. execute. Query(String query): for queries that return a single Result. Set object (typically select) 2. stmt. execute. Update(String query): for INSERT, UPDATE, DELETE, and SQL DDL directives 14
Compilation • When executing an SQL statement via JDBC, it is not checked for errors until it is run (Not checked during compilation) 15
execute. Query String query. Str = "SELECT * FROM Sailors " + "WHERE Name = 'joe smith'"; No semicolon(; ) Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query(query. Str); • The execute. Query method returns a Result. Set object representing the query result. 16
execute. Update String delete. Str = “DELETE FROM Sailors " + "WHERE sid = 15"; No semicolon(; ) Statement stmt = con. create. Statement(); int delnum = stmt. execute. Update(delete. Str); • execute. Update returns the number of rows modified 17
Prepared. Statement motivation • Suppose we would like to run the query SELECT * FROM Emp where name=‘moshe’; • But we would like to run this for all employees (separately), not only ‘moshe’… • Could we create a variable instead of ‘moshe’ which would get a different name every time? ? . . 18
Prepared. Statement prepare. Statement(String) – returns a new Prepared. Statement object 19
Prepared Statements • Prepared Statements are used for queries that are executed many times with possibly different contents. • A Prepared. Statement object includes the query and is prepared for execution (precompiled). • Question marks can be inserted as variables. -set. String(i, value) The i-th question -set. Int(i, value) mark is set to the given value. 20
Prepared. Statement. execute. Query() String query. Str = "SELECT * FROM Sailors " + "WHERE Name = ? and Rating < ? ”; Prepared. Statement pstmt = con. prepare. Statement(query. Str); Value to insert pstmt. set. String(1, “Joe”); pstmt. set. Int(2, 8); 1 st question mark Result. Set rs = pstmt. execute. Query(); 21
Prepared. Statement. execute. Update() String delete. Str = “DELETE FROM Boats " + "WHERE Name = ? and Color = ? ”; Prepared. Statement pstmt = con. prepare. Statement(delete. Str); pstmt. set. String(1, “Fluffy”); pstmt. set. String(2, "red"); int delnum = pstmt. execute. Update(); 22
• Will this work? Prepared. Statement pstmt = con. prepare. Statement(“select * from ? ”); pstmt. set. String(1, "Sailors"); No! We may put ? only instead of values 23
Why use prepared. Statement? • In most cases, you can use a regular statement and just change the string you send to execute. Query each time • You have to be careful 24
Why use prepared. Statement? • Suppose google worked without Prepared. Statements, they would implement search queries as something like: Statement s; s. execute. Query(‘select URL, Title from internet where content like ‘%”+search. String+”%’”); • What would happen if a hacker searched for: bla’ UNION select company as URL, Credit. Card. Number AS title from advertising. Clients where company like ‘ Example taken from dbi course 25
Result. Set (1) • A Result. Set is an object which contains the result of a query - a “table”. • At most one Result. Set per Statement can be open at the same time(!!). • A Result. Set maintains a cursor pointing to its current row of data. • The 'next' method moves the cursor to the next row • As of JDBC 2. 0, scrollable Result. Sets are available, which also include ‘previous’, ’first’, ‘last’, etc. .
Result. Set (2) • result. Set methods work on the current row. • The cursor is positioned before the first row upon creation.
Result. Set (3) Statement stmt= con. create. Statement(); Result. Set rs = stmt. execute. Query ( "SELECT * FROM Table 1"); while (rs. next()) { //something… }
Result. Set methods • Getting the value in some column (for the String s = current row): – get. String(int column. Num); rs. get. String(“column 1"); – get. String(String column. Name); – get. Int(int column. Num); – get. Int(String column. Name); – Etc… • To check if NULL was returned, you have to use was. Null() on the Result. Set after getting the value. 29
Example revisited Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT a, b, c FROM Table 1"); // retrieve and print the values for the current row while (rs. next()) { int i = rs. get. Int("a"); String s = rs. get. String("b"); float f = rs. get. Float("c"); System. out. println("ROW = " + i + " " + s + " " + f); } 30
Result. Set. Meta. Data An object created by the Result. Set which holds information about its columns Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); int numcols = rsmd. get. Column. Count(); for (int i = 1 ; i <= numcols; i++) { if (i > 1) System. out. print(", "); System. out. print(rsmd. get. Column. Label(i)); } 31
Printing Query Output: Result Set (2) while (rs. next()) { for (int i = 1 ; i <= numcols; i++) { if (i > 1) System. out. print(", "); System. out. print(rs. get. String(i)); } System. out. println(""); } • get. String() is allowed to access all simple JDBC types 32
Cleaning Up After Yourself • Remember to close the Connections, Statements, Prepared. Statements and Result. Sets con. close(); stmt. close(); pstmt. close(); rs. close(); 33
Dealing With Exceptions catch (SQLException e) { //human readable message about the exception System. out. println(e. get. Message()); //String describing the reason of the exception System. out. println(e. get. SQLState()); //driver-dependent code for the exception System. out. println(e. get. Error. Code()); } 34
Mapping SQL and Java Types • SQL and Java data types are not identical • There are significant variations between the SQL types supported by different database products • JDBC defines a set of generic SQL type identifiers in the class java. sql. Types • The driver is responsible for mapping between the DB SQL types and JDBC SQL types 35
Transactions in JDBC 36
Transactions • Transaction = 2 or more statements which must all succeed (or all fail) together • If one fails, the system must reverse all previous actions • Aim: don’t leave DB in inconsistent state halfway through a transaction • COMMIT = complete transaction • ROLLBACK = abort 37
Example • Suppose we want to transfer money from bank account 13 to account 72: Prepared. Statement pstmt = con. prepare. Statement(“UPDATE Bank. Account SET amount = amount + ? WHERE account. Id = ? ”); pstmt. set. Int(1, -100); pstmt. set. Int(2, 13); pstmt. execute. Update(); What happens if this pstmt. set. Int(1, 100); update fails? pstmt. set. Int(2, 72); pstmt. execute. Update(); 38
Transaction Management • The connection has a state called Auto. Commit mode • if Auto. Commit is true, then every statement is automatically committed • if Auto. Commit is false, then every statement is added to an ongoing transaction • Default: true 39
Auto. Commit con. set. Auto. Commit(boolean val) • If you set Auto. Commit to false, you must explicitly commit or rollback the transaction using Connection. commit() and Connection. rollback() 40
Example con. set. Auto. Commit(false); try { Prepared. Statement pstmt = con. prepare. Statement(“update Bank. Account set amount = amount + ? where account. Id = ? ”); pstmt. set. Int(1, -100); pstmt. set. Int(2, 13); pstmt. execute. Update(); pstmt. set. Int(1, 100); pstmt. set. Int(2, 72); pstmt. execute. Update(); con. commit(); }catch (SQLException e) { con. rollback(); } 41
- Slides: 41