1

Creating Data Access Services

Presented byAshraf Memon

Hands-onGhulam Memon, Longjiang Ding

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 promptjava 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.java by 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

• Creating Web Service to access functionality of a tool

Next Chapter