Lecture 7

1. CS441 CURRENT TOPICS IN PROGRAMMING LANGUAGES Lecture 7 George Koutsogiannakis/Summer 2011

2. Topics • Javadoc tool. • Java Database Connectivity (JDBC). • Using the Java Server (as an independent application server outside of NetBeans).

3. JAVADOCS • CREATE AUTOMATICALLY HTML DOCUMENTATION FOR YOUR PROGRAMS SIMILAR TO THE JAVA API. • EMBED THE PROPER COMMENTS FOR EACH CLASS, EACH METHOD, EACH PARAMETER. • IDENTIFY AUTHOR, DATE AND VERSION NUMBER. • CALL THE javadocs TOOL COMMAND LINE • >javadoc –d name_of_directory_to_place_docs Package_name The folder for the documentation is with respect to the current directory where the source code is located.

4. Java Database Connectivity (JDBC) An Application Server delivers applications to the Database. Handles the business logic of an application Database Application Server Queries submitted to the database and receive a response with data

5. Java Database Connectivity (JDBC) • The idea with Application Server is to centralize the business applications thus providing Data and Code integrity. Thus: • Query updates are common to all users • Security is common • Performance is common • Configuration handled by a single administrative unit. • Some Java Platform Application Servers are: • Java Server (Glass Fish) open source – SUN Microsystems • BEA - WebLogic • Websphere – IBM • Red Hat – JBoss • Apache Geronimo – Apache Foundation • JRun - Adobe

6. Java Database Connectivity (JDBC) • Structure Query Language – SQL • Provides keywords that allow to query a database in terms of performing: Searching for records (i.e. Select keyword). Updates of records. Creation of a database ( a collection of Tables). Creation of a Table within the database. Table shave records. Tables relate to each other. Deletion of records. Etc. DB Application Server Client

7. Structure Query Language (SQL) • Standard language for every relational database. • Developed by IBM in the early 1970s. • Includes operations for data definition and manipulation. • i.e CREATE TYPE (user defined SQL data types) CREATE TABLE (defines name of table, names of columns, data type for each column, identify primary key column)

8. Structure Query Language (SQL) • SELECT, INSERT,DELETE, UPDATE (data manipulative operations). • Other operations facilitate view of data in a table, and there are also other keywords that can be part of an SQL statement. • Complete set SQL keywords can be found on the web.

9. DATABASE • DATABASE SCHEMA: refers to the tables that make up the database and their relationships to each other. • For each table the previous slide description of columns has to be provided.

10. DATABASE • Most Databases allow SQL statements to be executed • directly (in other words the scripted SQL statements) • As part of another language. SQL statements are mixed with other language coded in which case it is called “Embedded SQL”

11. DATABASE • The term “database server” is the same as the DBMS (data base management system) • The database server supports all the SQL operations. • Clients to the database server provide the applications that are to be run by the DBMS.

12. DATABASES and JDBC • Client applications can be written as embedded SQL in other languages such as JAVA. • JAVA has an API called JAVA Database Connectivity (JDBC) for that purpose. • JDBC provides the ability to connect to a database server and to send embedded SQL statements to the database server for execution. • JDBC also provides the ability to process data returned from the database server.

13. Java Database Connectivity (JDBC) • Problem: There are many versions of SQL. Vendors of Databases provide their own version. • Solution Provide drivers that translate a version. Need to be installed on both sides -client to the database (Application Server) and server (the server for the database).

14. Java Database Connectivity (JDBC) • Java provides its set of drivers for various databases. • Microsoft has its own set of drivers for various databases called: • ODBC – Open Data Base Connectivity. • Problem with Java: • More database vendors support ODBC than they support JDBC.

15. Java Database Connectivity (JDBC) For example: Client’ query gets translated to ODBC driver on the client side. The ODBC driver on the server side translates back to the query version for the database. Drivers Drivers Server-host of database Client-Application server

16. Java Database Connectivity (JDBC) • Java Drivers: • Type 1: JDBC-ODBC bridge. Provides access to a database from a Java application (JDBC) via ODBC drivers. • Type 2: Native API: converts JDBC calls to calls understood by the particular database. Requires some binary code proprietary to the database be installed on client machines. • Type 3: Net-protocol: Translates JDBC calls into some independent network protocol which in turn gets translated into the database protocol by a server. Supports internet access of a database. Vendors need to provide the middleware. • Type 4: Database supports JDBC drivers directly. JDBC to JDBC driver communication.

17. Java Database Connectivity (JDBC) • In our examples we will use ODBC drivers. • Therefore we will use the JDBC to ODBC bridge driver. • We assume that our database has ODBC capability. • Therefore we need to register our database with the ODBC drivers in our local environment where the database resides. Procedure for registering a Database with ODBC drivers: • Control Panel -> Performance and Maintenance -> Administrative Tools. Click on Data Sources (ODBC) • In User DSN • Select Data Source (database) ie. Access • Click Add

18. Java Database Connectivity (JDBC) • Create New Data Source • Select Driver (i.e MS Access Database *.mdb ) • Click Finish • MS Access Set Up Dialog appears • Data Source Name: Add name of your database without extension. • Description: Optional entries. • Click Advanced. • Set Advanced Options appears: • Enter a login user name and a password for your database. • Click OK

19. Java Database Connectivity (JDBC) • Back to Microsoft Access Set Up Dialog window: • Click Select • Browse to your database’s directory (folder) • Highlight path and click OK. • Registration process completed. Close all windows.

20. Java Database Connectivity (JDBC) • Code highlights for establishing a query and executing it: • import java.sql package • URL String : String url = “jdbc:odbc myDB” ; //where myDB is the name of your database. • Class Definition for the proper Driver to establish the JDBC to ODBC bridge: Class forName(“sun.jdbc.odbc.JdbcOdbcDriver”); returns the class object associated with the driver . • Declare a reference of the Connection interface; Connection con: • Use static method getConnection of the DriverManager class to attempt a connection to the database: con = DriverManager.getConnection( url, username, password);

21. Java Database Connectivity (JDBC) • Establish the query as a String i.e.: String query= “Select *From Authors” ; // Select all fields from table Authors in the database. • Need a reference of the Statement interface in order to submit the query: Statement stat; stat = con. createStatement() ; • Execute the query and get the results: ResultSet results= stat.executeQuery(query); object results encapsulates the data provided by the execution of the query (if successful).

22. Java Database Connectivity (JDBC) • We can analyze the ResultSet results object to extract the data and display it, for example, to the user. A JTable component would be ideal for such a display. • The ResultSet object contains rows of data and the names of the fields (columns). We can move a cursor from row to row: • results.next(); moves the cursor and return s a boolean true if there is another record. • boolean moreRecords= results.next(); • We also need to capture the data type of record in each field: • Class ResultSetMatadata allows discovery of data types • ResultSetMetadata rsmd = result.s getMetadata(); int col = rsmd.getColumnCount(); String name = rsmd.getColumnName (col); int type = rsmd.getColumnType(col); //type is the coded sql data type String type_name = rsmd.getColumnTypeName(type);

23. Java Database Connectivity (JDBC) • Column names are represented by class java.sql.Types • Types are coded by an integer value: 1 means CHAR type 3 means DECIMAL type 8 means DOUBLE type etc. Types here are SQL types used when the fields (columns) of the database are created.

24. Java Database Connectivity (JDBC) • More on ResultSet object: • ResultSet object has characteristics: • result set type: Determines ways the cursor can be manipulated and how concurrent changes made to the database are reflected in the resultSet object. • Types are: • TYPE_FORWARD_ONLY: Cursor moves forward only. Data can be either at the time the query was executed or at the time the rows were retrieved. • TYPE_SCROLL_INCENSITIVE: Cursor is scrollable in both directions from current position. Not sensitive to changes to the data made by others concurrently. • TYPE_SCROLL_SENSITIVE: Cursor is scrollable in both directions. Sensitive to concurrent changes to data.

25. Java Database Connectivity (JDBC) • Set these attributes by using the createStatement method: Statement stat = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); where con is the Connection object. If no arguments are given to createStatement the default settings are then: TYPE_FORWARD_ONLY and CONCUR_READ_ONLY

26. Java Database Connectivity (JDBC) • RowSet Interface • Configures the database connection automatically and prepares query statements. • Used by JavaBeans and EJB s • Need a tool like NetBeans to use RowSet. • Connected RowSet –connects to database and remains connected until the application terminates (i.e possibly more than one query needs to be submitted). • Disconnected RowSet –connects , executes query and disconnects.

27. Java Database Connectivity (JDBC) • PreparedStatements interface • Provides objects that are maintained in a compiled form. • Can specify parameters where a query can be executed with different parameters each time. • The advantage is that we don’t have to recompile if we want to change the parameters in the query.

28. Java Database Connectivity (JDBC) PreparedStatement authorBooks = con. prepareStament (“Select lastName, firstName, title From Authors, Titles, AuthorISBN Where Authors.authorID=AuthorISBN.authorID AND Titles.ISBN=AuthorISBN.isbn AND lastName=? AND firstName= ?”); • Authors, Titles, AuthorISBN are table sof the database • ? Acts as a placeholder for values that will be passed programmatically to the query during execution. It is assumed that set methods for the variables exist in the program. The parameters can be set like: authorBooks.setString(1, “Kay”); authorBooks.setString(2, “George”);

29. Java Database Connectivity (JDBC) • Transaction Processing. • A set of database operations that are treated as a single operation. • Method setAutoCommit(true) of interface Connection specifies that a single operation at a time completes execution and that data in the database is changed. • Method setAutoCommit(false) specifies that a group of statements are to be executed. When all statements are executed method commit is called to finalize the data in the database. • If the transaction fails: con.rollback() clears the data and the data is not changed in the database (where con is the Connection object)

30. Java Database Connectivity (JDBC) • STORED PROCEDURES • SQL Statements are stored in the database . Applications accessing the database can invoke them. The SQL statement (s) execute and return the results. • An application can pass parameters to a stored procedure (like in a prepared statement). • To implement it you create the SQL statement (query) String createProcedure = “create procedure SHOW_SUPPLIERS as Select Suppliers.supplierName from Suppliers, Coffee where Suppliers.suplpierID=Coffee.supplierID”; Statement stat = con.createStatement(); Stat.executeUpdate(createProcedure);

31. Java Database Connectivity (JDBC) • The result of the previous code is that procedure SHOW_SUPPLIERS will be compiled and stored in the database as a database object that can be invoked. • To call a stored procedure from JDBC application: • Create a CallableStatement object: CallableStatement cs= con.prepareCall(“(call SHOW_SUPPLIERS)”); ResultSet rs = cs.executeQuery();

32. Java Database Connectivity (JDBC) • Parameters can be passed if the proper SQL syntax is used both when the procedure was created and when it is called. Use IN, OUT INOUT SQL syntax when the procedure is craeted. call procedure_name(param1, param2….) Or we can do it programmatically like a prepared statement: i.e. Suppose the procedure setDeathAge is stored in the DB. CallableStatement proc= con.prepareCall (“(call setDeathAge(?, ?))”); proc.setStrng(1, name); proc.setInt(2, int); proc.execute();

33. JDBC Example package com.devdaily.sqlprocessortests; import java.sql.*; public class BasicJDBCDemo { Connection conn; public static void main(String[] args) { new BasicJDBCDemo(); } public BasicJDBCDemo() { try {

34. JDBC Example Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://localhost/coffeebreak"; conn = DriverManager.getConnection(url, "username", "password"); doTests(); conn.close(); } //end of try

35. JDBC Example catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());} catch (IllegalAccessException ex) {System.err.println(ex.getMessage());} catch (InstantiationException ex) {System.err.println(ex.getMessage());} catch (SQLException ex) { System.err.println(ex.getMessage()); } } // end of public BasicJDBCDemo() method

36. JDBC Example private void doTests() { doSelectTest(); doInsertTest(); doSelectTest(); doUpdateTest(); doSelectTest(); doDeleteTest(); doSelectTest(); }

37. JDBC Example private void doSelectTest() { System.out.println("[OUTPUT FROM SELECT]"); String query = "SELECT COF_NAME, PRICE FROM COFFEES"; try { Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query); while (rs.next()) { String s = rs.getString("COF_NAME"); float n = rs.getFloat("PRICE");

38. JDBC Example System.out.println(s + " " + n); } } catch (SQLException ex) { System.err.println(ex.getMessage()); } } private void doInsertTest() { System.out.print("\n[Performing INSERT] ... ");

39. JDBC Example try { Statement st = conn.createStatement(); st.executeUpdate("INSERT INTO COFFEES " + "VALUES ('BREAKFAST BLEND', 200, 7.99, 0, 0)"); } catch (SQLException ex) { System.err.println(ex.getMessage()); } }

40. JDBC Example private void doUpdateTest() { System.out.print("\n[Performing UPDATE] ... "); try { Statement st = conn.createStatement(); st.executeUpdate("UPDATE COFFEES SET PRICE=4.99 WHERE COF_NAME='BREAKFAST BLEND'"); } catch (SQLException ex) { System.err.println(ex.getMessage()); } }

41. JDBC Example private void doDeleteTest() { System.out.print("\n[Performing DELETE] ... "); try { Statement st = conn.createStatement(); st.executeUpdate("DELETE FROM COFFEES WHERE COF_NAME='BREAKFAST BLEND'"); } catch (SQLException ex) { System.err.println(ex.getMessage()); } } }

42. Using the Database in a Web Application Development • The database queries will come from either one of two architectures of a Web Application Development that we are going to study: • Web Server to an RMI server or • Web Server to an Application Server. • The first Half of the course will use RMI servers to connect to the database server. • In the second half we will use an Application server: • Let us however cover some basics about the Application Server that we will be using in the second half of the course.

43. APPLICATION SERVER APPLICATION SERVER DATABASE SERVER IT IS THE CLIENT TO THE DATABASE SERVER

44. Using The Java Server. • The Java Server is an Application Server (Glass Fish server -version 3 at this time). • The Java Application Server is free and can be downloaded from Sun’ s site. • It is available as an independent download from http://developers.sun.com/appserver/ • Or, it can be downloaded as part of NetBeans. • The next few slides discuss the Java Server as a stand alone server outside of NetBeans.

45. Using The Java Server. • Java Server provides • Tool: Admin Console • A web-based GUI Enterprise Server administration utility. Used to stop the Enterprise Server and manage users, resources, and applications. • Tool:asadmin • A command-line Enterprise Serveradministration utility. Used to start and stop the Enterprise Server and manage users, resources, and applications. • Tool:appclient • A command-line tool that launches the application client container and invokes the client application packaged in the application client JAR file. • Tool:capture-schema • A command-line tool to extract schema information from a database, producing a schema file that the Enterprise Server can use for container-managed persistence.

46. Using The Java Server. • Tool: package-appclient • A command-line tool to package the application client container libraries and JAR files. • Tool:Java DB database • A copy of the Java DB database server. • Tool:xjc • A command-line tool to transform, or bind, a source XML schema to a set of JAXB content classes in the Java programming language. • Tool:schemagen • A command-line tool to create a schema file for each namespace referenced in your Java classes.

47. Using The Java Server. • Tool:wsimport • A command-line tool to generate JAX-WS portable artifacts for a given WSDL file. After generation, these artifacts can be packaged in a WAR file with the WSDL and schema documents along with the endpoint implementation and then deployed. • Tool:wsgen • A command-line tool to read a web service endpoint class and generate all the required JAX-WS portable artifacts for web service deployment and invocation.

48. Using The Java Server. • General Information about the Java Server. • See site: https://glassfish.dev.java.net/ • Or, See information which is part of NetBeans Help.

49. Using The Sun Java Server • Default port : 8080 (could conflict with your installation of Tomcat- change the default port to 80 in one or the other during installation). • Administrative server default port: 4848 (make sure it is not used by another application in your system). • A domain is a one or more Java Server instances managed by the same administrative server. If you start the server command line you can specify a domain otherwise the default domain is domain1.

50. Java Server • What is GlassFish v3? • GlassFish Enterprise Server v3 is the industry’s supports Java Platform, Enterprise Edition 6 (the Java EE 6 platform). • Installations requires JDK 6. The minimum and certified version of the JDK software depends on the operating system. • Included in GlassFish if you do a download independent of NetBeans: • Java Platform, Enterprise Edition 6 • JTS/JTA 1.1 • JPA 2.0, (EclipseLink), JDBC Connection Pooling • JDBC 4.0 • OMG CORBA APIs 3.0 • JavaMail 1.4 • Web Services APIs • Many Other APIs