Database

1. Database CPIT 305

2. Why database? • JDBC lets you communicate with data base using SQL, (command language for all relational database. • JDBC package is the API for communicating SQL statements to database. • Data are arranged in tables of rows and columns in which each column has name and each row contain related data called record.

3. Why database? • Tables are joined together using identifiers. • The advantage is to remove redundancy (avoid unnecessary duplication). • Query that uses SQL is written in text as: SELECT Books.Title, Books.Publisher_Id, Books.Price, Publishers.Name, Publishers.URL FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id

4. Writing SQL query • Conventionally SQL keywords are written in Capital letters (but not necessary). • The SELECT statement is quite simple , you can select rows of the table with SELECT * FROM Books • You can choose the columns you need as: SELECT ISBN, Price, Title FROM Books

5. Writing SQL query • You can restrict the choice as: SELECT ISBN, Price, Title FROM Books WHERE Price <= 29.95 • Be careful with “equals” comparison. SQL uses “=“ and “<>” instead of “==“ and “!=“ as in java programming. • Where clause can in include pattern matching by means of “LIKE” operator

6. Pattern Matching %: used to stand for zero or more characters. _: used to stand in place of one character. Ex. SELECT ISBN, Price, Title FROM Books WHERE Title NOT LIKE '%n_x%‘ This exclude title like UNIX or LINUX. Note that string are enclosed in single quotes, not double quotes Single quote inside the string is indicated by 2 single quote

7. Pattern Matching Ex. SELECT ISBN, Price, Title FROM Books WHERE Title NOT LIKE '%n’ ‘%‘ Selects all titles include single quote. • Also you can select data from different tables as: SELECT * FROM Books, Publishers without the WHERE clause is not interested since it will list all combinations (i.e. if book contains 20 rows and publisher 8 rows, then the result will be 160=20x8)

8. Pattern Matching SELECT * FROM Books, Publishers WHERE Books.Publisher_Id= Publishers.Publisher_Id This query results 20 rows only (one for each book) • When ambiguity occurs, the table name is used as prefix

9. SQL Statement to Change Data EX. UPDATE Books SET Price=Price-5.00 WHERE Title LIKE ‘%C++%’ Also, you can use the built in SQL function to calculate average, minimum …etc Visit http://sqlzoo.net

10. Database instructions • The executeUpdate method returns the number of rows affected by this instruction or -1 otherwise (if Exception occurred) • The executeUpdate can execute different SQL actions such as INSERT, UPDATE, DELETE. Also, data definition statement such as CREATE TABLE and DROP TABLE.

11. Overview of a JDBC-based Program The basic sequence of operations for a program that will use JDBC to interact with a database: Load the correct JDBC driver (using the Class.forName() ) Open a Connection to the database (using a URL) Create statements (using the Connection) Execute statements and process results Close the Connection 11

12. Loading and registering the Diver class • There are two ways: • Is to register driver with DiverManager and force loading driver class in your java program: • Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; • You can set jdbc.drivers property from: • Command line as: java -Djdbc.drivers=org.postgresql.Driver ProgramName • Within your program • System.setProperty("jdbc.drivers", "sun.jdbc.odbc.JdbcOdbcDriver");

13. Connecting to Database • You can open database connection with code like this: String url = "jdbc:postgresql:COREJAVA"; String username = "dbuser"; String password = "secret"; Connection conn = DriverManager.getConnection(url, username, password); The DriverManager iterates through the registered divers to find the driver that can use the subprotocol specified in the database URL. The getConnection returns Connection object that can be used to execute SQL statements.

14. Getting connection parameters from file • Ex. Connection con= DriverManager.getConnection("jdbc:odbc:test"); No user name or password are given. Or you can get driver, url, password and password fron a file by using this code: Properties props = new Properties(); FileInputStream in = new FileInputStream("database.properties"); props.load(in); in.close(); String drivers = props.getProperty("jdbc.drivers"); if (drivers != null) System.setProperty("jdbc.drivers", drivers); String url = props.getProperty("jdbc.url"); String username = props.getProperty("jdbc.username"); String password = props.getProperty("jdbc.password");

15. Contents in database.properties file jdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver jdbc.url=jdbc:odbc:test jdbc.username=mkhamis jdbc.password=12345

16. Debug JDBC-related problems One way to debug JDBC-related problems is by enabling JDBC tracing as follow: PrintWriter out= new PrintWriter(new FileOutputStream("test.log")); DriverManager.setLogWriter(out); Detailed listing of JDBC activity sends to the file “test.log”

17. Executing SQL statements (UPDATE) • First, you have to define Statement Object from the connection Object as follow: Statement stat= con.createStatement(); • Place the statement you want to execute in string as: String command = "UPDATE Books SET Price = Price - 5.00 WHERE Title NOT LIKE '%Introduction%‘ “ ;

18. Database instructions • Define String which to include SQL instruction: String command = "UPDATE Books" + " SET Price = Price - 5.00" + " WHERE Title NOT LIKE '%Introduction%'"; • Call executeUpdate method of the Statement class with the above string. stat.executeUpdate(command);

19. JDBC Programming – Database URLs JDBC always refers to databases by URLs. The general structure of a JDBC URL is: jdbc:<subprotocol>:<dbname>[params] Some common examples: jdbc:odbc:aMSAccessDB jdbc:odbc:oDatabase2;UID=jeffsix;PWD=simplePW jdbc:mysql://dbserver/myDB?user=jeff&password=easy jdbc:postgresql://psServer.mycorp.com:1878/myDB jdbc:sequelink://host5/someDB;uid=bob;password=pop 19

20. SQL: The DELETE Verb The SQL verb DELETE is used to remove data items (rows) from a table. Some examples of deleting rows: DELETE FROM instructors WHERE dept = 23 AND name LIKE ’James %’ DELETE FROM coursesWHERE courses.dept > 100 % is a string wildcard in SQL _ is a single c/c matching 20

21. JDBC Programming – General Form import java.sql.*; public class SimpleJDBCExample { static String drv = "org.gjt.mm.mysql.Driver"; static String url = "jdbc:mysql://localhost/instructors"; public static void main(String [] args) { try { // create a new JDBC driver object instance Class.forName(drv).newInstance(); // create a Connection object Connection conn; conn = DriverManager.getConnection(url,“jeffsix",“arrrr!"); // create a Statement object Statement stmt; stmt = conn.createStatement(); // use the Statement object to execute a SQL command/statement int rc = stmt.executeUpdate("delete from instructors where dept=23"); // all done, so close the Statement and Connection objects stmt.close(); conn.close(); } catch (Exception sqe) { System.err.println("JDBC Error: " + sqe); } } } 21

22. The first thing to do in a JDBC program is to load the driver for our specific database (in this case, mySQL). This process register the driver with the DriverManager. 2 ways for registeration: Class.forName(drv).newInstance(); (if this call fails it will throw a ClassNotFoundException) your application can set the system property with a call such as System.setProperty("jdbc.drivers", "DriverName"); Next, we need to open a connection to the database specified in the URL, using username “badr” and password “Secret!”. This one line of code does so and returns a refernce to the Connection object: String url = "jdbc:postgresql:COREJAVA"; //COREJAVA is the DB DriverManager.getConnection(url,“badr”,“Secret!”) (if this call fails it will throw a SQLException) JDBC Programming –General Form 22

23. /** create a table, add a value, show table contents, & removing the table. 33. public static void runTest() throws SQLException, IOException 34. { 35. Connection conn = getConnection(); 36. try 37. { 38. Statement stat = conn.createStatement(); 39. 40. stat.executeUpdate("CREATE TABLE Greetings (Message CHAR(20))"); 41. stat.executeUpdate("INSERT INTO Greetings VALUES ('Hello, World!')"); 43. ResultSet result = stat.executeQuery("SELECT * FROM Greetings"); 44. if (result.next()) 45. System.out.println(result.getString(1)); 46. result.close(); 47. stat.executeUpdate("DROP TABLE Greetings"); 48. } 49. finally 50. { 51. conn.close(); 52. } } JDBC Programming – General Form CREATE TABLE Greetings (Message CHAR(20)) INSERT INTO Greetings VALUES ('Hello, World!') SELECT * FROM Greetings DROP TABLE Greetings 23

24. The program can now use the database Connection to create a Statement object. Easy to do… Statement stmt = conn.createStatement(); (if access is denied then this code will throw an SQLException) The program can then use the Statement to execute an SQL command/statement. The method returns the number of table rows affected by the update comment. int rc = stmt.executeUpdate("delete " +"from instructors where dept=23"); (if failed this code will throw an SQLException) When all done, close the Statement and the Connection The close method of a Statement object automatically closes the associated result set if the statement has an open result set. Similarly, the close method of the Connection class closes all statements of the connection. stmt.close(); conn.close(); JDBC Programming – General Form 24

25. The JDBC Connection Class There are many methods a program can call on its valid Connection object. The createStatement() method will create a Statement object that can be used to assemble and run SQL commands and updates without parameters. . The preparedStatement() creates an object that is associated with a predefined SQL command (the Statement object can be used for arbitrary statements – and can be reused for other SQL commands) The prepareCall() method is used to call stored procedures in the SQL database. The getMetaData() method will return metadata associated with the database, including descriptions of all of the tables in the DB. However, a statement has at most one open result set. If you issue multiple queries whose results you analyze concurrently, then you need multiple Statement objects. 25

26. preparedStatement() ? • Rather than build a separate query statement every time the user launches such a query, we can prepare a query with a host variable and use it many times, each time filling in a different string for the variable

27. Why preparedStatement() ? • That technique benefits performance. Whenever the database executes a query, it first computes a strategy of how to efficiently execute the query. • By preparing the query and reusing it, you ensure that the planning step is done only once. • Each host variable in a prepared query is indicated with a ?. • If there is more than one variable, then you must keep track of the positions of the ? .

28. Steps of preparedStatement() • Define string with host variable String publisherQuery = "SELECT Books.Price, Books.Title" + " FROM Books, Publishers" + " WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = ? “ ; • Create preparedStatement oject with the above query string. PreparedStatement publisherQueryStat = conn.prepareStatement(publisherQuery); • Bind host variable with value: publisherQueryStat.setString(1, publisher); • Get the result of the host query: ResultSet rs = publisherQueryStat.executeQuery();

30. The JDBC Statement Class There are many methods a program can call on its valid Statement object. The executeQuery() method executes a SQL query statement and returns the results (as a ResultSet). ResultSet rs = stat.executeQuery("SELECT * FROM Books")while (rs.next()) { //there is no hasNext. look at a row of the result set } The executeUpdate() method executes a SQL update (such as UPDATE, INSERT, or DELETE and CREATE TABLE and DROP TABLE ). Returns no. of rows affected or -1 Statement stat = conn.createStatement(); String command = "UPDATE Books" + " SET Price = Price - 5.00" + " WHERE Title NOT LIKE '%Introduction%'"; stat.executeUpdate(command); stat.executeUpdate(command); The execute() method executes other, more generic, SQL statements. It's commonly used only for queries that a user supplies interactively. Return Boolean but if true it returns result sets; Call getResultSet() or getUpdateCount() to retrieve the first result 30

31. The JDBC Statement Class Check execute() Statement stat = conn.createStatement(); boolean hasResultSet = stat.execute(line); If (hasResultSet) ResultSet rs = Stat.getResultSet(); //Or Count = stat. getUpdateCount(); The setMaxRows() method allows the program to set the maximum number of data elements (rows) that can be returned from a query statement. There are MANY more methods for both the Connection and Statement classes. 31

32. JDBC Programming - Queries The most popular thing for a Java program to do via JDBC is to perform queries on a DB. You can perform a query using a Statement object and its executeQuery() method. This method will returns a ResultSet object that gives you access to the query rows. A ResultSet is just like a database table; it has zero or more rows (zero if no data elements match the query criteria) and each row has one or more columns (having no columns would make absolutely no sense). 32

33. JDBC Programming - Queries Performing a query and interacting with the ResultSet is very easy… ResultSet thequeryResults; theQueryResults = theStatement.executeQuery( "select distinct dept from instructors"); while(theQueryResults.next()) { System.out.println(“Dept:" + queryResults.getInt(1)); } theQueryResults.close(); ResultSet objects keep track of their position internally, starting before the first row. You can use next() and other navigation methods to move through them (see the API docs for more methods). This gets the first column as an integer. 33

34. JDBC Programming - ResultSets There are multiple methods of extracting data from the current row in a ResultSet. The getString() method returns the value of a particular column in the current row as a String. The getInt() method returns the value of a particular column in the current row as an int. The getBoolean() method returns the value of a particular column in the current row as an boolean. The getDouble() method returns the value of a particular column in the current row as an double. The getObject() method returns the value of a particular column in the current row as an Object. There are really two versions of both of these methods…one takes the column as an integer index, one takes the column as its name as a String. int findColumn(String columnName) return the index of the colume with that name 34

35. JDBC Statements - Types There are three types of statement objects in JDBC programming: Statement - This allows the execution of arbitrary statements on the database. PreparedStatement - This kind of statement allows the program to execute the same SQL command repeatedly, while allowing substitutions for particular words or values (to make it more useful). CallableStatement - This kind of statement allows the program to execute SQL stored procedures, with substitutions for arguments. A stored procedure is a function that is part of, and stored inside, a SQL database (hence the name). 35

36. JDBC Statements - PreparedStatements Consider the query for all books by a particular publisher, independent of the author. The SQL query is SELECT Books.Price, Books.Title FROM Books, Publishers WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = the name from variable String publisherQuery = "SELECT Books.Price, Books.Title" + " FROM Books, Publishers" + " WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = ?"; PreparedStatementpublisherQueryStat = conn.prepareStatement(publisherQuery); Before executing the prepared statement, you must bind the host variables to actual values with a set method. As with the ResultSet get methods, there are different set methods for the various types. Here, we want to set a string to a publisher name. publisherQueryStat.setString(1, publisher); The first argument is the position number of the host variable that we want to set. The position 1 denotes the first ?. The second argument is the value that we want to assign to the host variable. 36

37. JDBC Statements - PreparedStatements If you reuse a prepared query that you have already executed, all host variables stay bound unless you change them with a set method or call the clearParameters method. Once all variables have been bound to values, you can execute the query ResultSet rs = publisherQueryStat.executeQuery(); 37

38. JDBC Statements – CallableStatements The CallableStatement calls a SQL stored procedure inside the DB. The stored procedure is specified upon object creation (using the Connection’s prepareCall() method). The syntax of this call is straightforward (even using the substitution abilities for parameters to the procedure)… { ? = call procedurename(?,?) }for a procedure that takes 2 arguments; returns a value { call procedurename(?,?,?,?) }for a procedure that takes 4 arguments; does not return a value The syntax is easy to understand… CallableStatement callable;callable = conn.prepareCall("{ call replace(?,?) }");callable.setString(1, oldString);callable.setString(2, newString);int rows_modified = callable.executeUpdate(); 38

39. JDBC Statements – Batch Mode Your program can create a Batch of statements (any of the three types) and execute that batch on the database all at once… The addBatch() method adds a statement to the current batch. The executeBatch() method runs all of the statements in the batch on the database. The clearBatch() method clears the statements from the batch without running them. 39

40. JDBC Statements – An Update Example import java.sql.*; public class addInstructorExample { public static void main(String [] args) { if (args.length != 3) { System.out.println(“Syntax: java addInstructorExample ID ‘name’ dept"); System.exit(0); } try { Class.forName(”org.gjt.mm.mysql.Driver”).newInstance(); Connection theConnection; PreparedStatement prep_statement; conn = DriverManager.getConnection("jdbc:mysql://localhost/theDB",“jeffsix",“arrrr!"); prep_statement = conn.prepareStatement("insert into instructors (ID,name,dept) values (?,?,?)"); prep_statement.setString(1,args[0]); prep_statement.setString(2,args[1]); prep_statement.setString(3,args[2]); int rows_modified = prep_statement.executeUpdate(); if (rows_modified == 1) System.out.println("Added " + args[0] + " to instructor table."); prep_statement.close(); theConnection.close(); } catch (Exception exp) { System.err.println("JDBC Error: " + exp); } } } 40

41. JDBC Data Types As SQL defines its own datatypes, it is important to understand the mapping between SQL and Java datatypes… SQL Java SQL Java---------------------------------- ---------------------------------------------BIT boolean NUMERIC BigDecimalBIGINT long REAL floatBINARY byte[] SMALLINT shortCHAR String TIME TimeDATE Date TIMESTAMP TimestampDOUBLE double TINYINT byteFLOAT float VARBINARY byte[]INTEGER int VARCHAR char[] BLOB Blob REF Ref CLOB Clob STRUCT Struct Many of the non- primitive types are defined in the java.sql pacakge. 41

42. JDBC and Binary Data Types SQLs offer two ways to support storage and retrieval of binary data in database tables: Binary columns: BINARY / VARBINARY- these kinds of columns are suitable for small arrays of bytes (they are not very memory efficient). Binary large object columns: BLOB / LONGBLOB- these kinds of columns are suitable for large arrays of bytes, but are not supported in all database systems (they are much more memory efficient). BLOBs are useful in many databases: BLOBs are used to store image, audio, signal, or video data BLOBs are used to store serialized Java objects BLOBs are suitable for storing entire documents inside databases (as documents are really just binary data) 42

43. JDBC and Binary Data Types Storing binary data into the database is easy… Using the SQL INSERT verb, the binary column is set using either the setObject() method and passing it a byte array or using the setBinaryStream() method and passing it an InputStream (which the JDBC driver will then used to fill in the column’s data field). Retrieve binary data from the database is also easy: Use a normal query, and either getObject() to return a byte array, getBlob() to return a Blob object, or getBinaryStream() to get an InputStream which can be used to read from this data field. 43

44. Summary JDBC is a core Java technology that supports access to relational databases and database servers. JDBC uses the standard Structured Query Language (SQL) for all database interaction. In order to use JDBC with a particular database, you must install a driver for the database server you are using and load that driver in your programs. Almost all JDBC functionality is accomplished through the use of SQL statements, of which there are three kinds… Statement, PreparedStatement, and CallableStatement. 44

45. Connecting to Database

46. Collecting Results • The executeQuery method returns ResultSet rs = stat.executeQuery("SELECT * FROM Books") while (rs.next()) { look at a row of the result set } You must call next() before displaying any result.

47. The JDBC Connection Class There are many methods a program can call on its valid Connection object. The createStatement() method will create a Statement object that can be used to assemble and run SQL commands and updates without parameters. . The preparedStatement() creates an object that is associated with a predefined SQL command (the Statement object can be used for arbitrary statements – and can be reused for other SQL commands) The prepareCall() method is used to call stored procedures in the SQL database. The getMetaData() method will return metadata associated with the database, including descriptions of all of the tables in the DB. However, a statement has at most one open result set. If you issue multiple queries whose results you analyze concurrently, then you need multiple Statement objects. 47

48. CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id)

49. String sel="select [distinct] <column(s)> from <table> [ where <condition> ] [ order by <column(s) [asc|desc]> ]";

50. select [distinct] <column(s)> • from <table> • [ where <condition> ] • [ order by <column(s) [asc|desc]> ]