1. Creating Data Access Services Presented by Ashraf Memon Hands-on Ashraf Memon, Ghulam Memon

2. Overview • Writing data access service classes in Java • Generating service • Deploying services with Apache Axis • Generating client files and testing them

3. Writing data access service classes in Java • Data Access code in Java heavily relies on JDBC (Java Database Connectivity) • JDBC is a industry standard Java API from Sun Microsystems for communicating with almost all the database. • The driver is usually provided by the database company.

4. Writing data access service classes in Java

5. Writing data access service classes in Java • Sample Data Access class contains 1 function, which reads country data from PGAP database and generates XML for it • Method signature is • public String getData(String countryName) • Anatomy of class code follows on next slide • For complete source code check c:\data\csig05\ws\solutions\database\DBAccess.java

6. Code Anatomy • Load JDBC Driver • Class.forName("com.mysql.jdbc.Driver"); • Establish databse conenction • Connection connection = DriverManager.getConnection( "jdbc:mysql://geon07.sdsc.edu:3306/test", "csig", "csig"); • Create jdbc statement • Statement stmt = connection.createStatement(); • Execute SQL query • stmt.executeQuery(sql); • Get database metadata (schema) ResultSetMetaData rsmd = records.getMetaData(); int numColumns = rsmd.getColumnCount(); String columnNames[] = new String[numColumns+1]; // Get the column names; column indices start from 1 for (int i=1; i<numColumns+1; i++) { columnNames[i] = rsmd.getColumnName(i); }

7. Code Anatomy (contd) • Get result data (schema) while (records.next()) { xml += "<record>\r\n\t\t"; for (int i=1; i<numColumns+1; i++) { xml += "<" + columnNames[i] + ">" + records.getString(i) + "</" + columnNames[i] + ">\r\n\t\t"; } xml += "</record>\r\n\t"; }

8. Testing the code • Navigate to solutions directory c:\data\csig05\ws\solutions • Open command prompt by right clicking on the database directory and selecting “Command Prompt Here” • Change to database directory by typing following at the command prompt cd database • Compile DBAccess.java file by typing following at command prompt javac DBAccess.java • Add mySQL JDBC driver to classpath by typing following at the command prompt C:\tools\Tomcat4.1\webapps\axis\WEB-INF\classes\classpath.bat • Run program by typing following at command prompt java DBAccess • Output should be of the form:

9. Testing the code (contd) <table> <record> <country>GERMANY</country> <state></state> <area_code></area_code> <lat_dec>53.2</lat_dec> <lng_dec>9.6</lng_dec> <age_top>50000</age_top> <age_bot>50000</age_bot> <book>EUR</book> <plate_code>703</plate_code> <pgap_envi1>5</pgap_envi1> <pgap_envi2>5</pgap_envi2> <facies></facies> <formation_name>KONINGSMOOR</formation_name> <strat_reliability>Complete biostratigraphic control</strat_reliability> <geo_reliability>null</geo_reliability> <investigator></investigator> <Date_of_Entry>12-3-92</Date_of_Entry> <Date_of_Modified></Date_of_Modified> <comments></comments> <loc_no>12465</loc_no> <date_created>20010421160313</date_created> <date_modified>20010421000549</date_modified> </record> </table>

10. Deploying your code as a web services with Apache Axis • Copy generated class file to C:\tools\tomcat4.1\webapps\axis\WEB-INF\classes\ • Open using any text editor deployDBAccess.wsdd from C:\tools\tomcat4.1\webapps\axis\WEB-INF\classes\ • Verify the content so that it matches the current service and its corresponding class

11. Deploying services with Apache Axis • Type following on command line to navigate to C:\tools\tomcat4.1\webapps\axis\WEB-INF\classes\ cd C:\tools\tomcat4.1\webapps\axis\WEB-INF\classes\ • Execute deployment descriptor by typing deploy.bat deployDBAccess.wsdd at command prompt • This will deploy database webservice on Axis SOAP Server • Test it by going to http://localhost/axis/ and navigating to DBAccessService

12. Generating client files and testing them • Change directory to c:\data\csig05\ws\solutions\database by typing “cd c:\data\csig05\ws\solutions\database” on the command prompt • Compile DBAccessServiceClient.javaby typing following at command prompt • javac DBAccessServiceClient.java • Execute Client by typing following at command prompt • java DBAccessServiceClient • Output should be similar to previous one.

13. Next Chapter • Creating Web Service to access functionality of a tool