|

Current Topic: 4.4.4.5.DataService framework basics

You have a privilege to create a quiz (QnA) related to this subject and obtain creativity score...

4.4.4.5.DataService framework basics

DataService framework greatly simplifies trivial code while working with databases.
In a way it is similar to the Spring framework.
DataService library allows you provide your business functions in the service related actions and provide SQL statements in small files stored in the SQL location.
The framework will connect to a database and find SQL statements while using configuration data provided in the web.xml file.

Note:
In the previous lesson and further we provide extracts of the sources in the DataService frameworks.
These source extracts are simplified and serve just to conceptually understand how the framework is implemented.
In the most cases, you will not need the sources like ItsBaseServlet, or DataBaseService, etc.
Use the library instead. In the next lesson, we will take a closer look at the library and expose more sources for your review.

Start with creating a Maven project in Eclipse: New - Maven Project
Click on the Next to get to the screen that allows to select an Archetype for the Maven project.
Select the maven-archetype-webapp and click Next again.
Fill in GroupID and ArtifactID fields with the name of the project 4.4.4.5.DataServiceWeb and click Finish.
Open the pom.xml file and provide the dependencies for the following libraries:
- com.its.util.jar - DataService library
- javax.servlet-api-3.1.0.jar - JSP and Servlet support
- mail.jar - a library for email messaging
- activation.jar - a component f the library for email messaging
- log4j.jar - a library for providing logging debugging and other information
- mysql.jar - a JDBC driver for MySql database
The pom.xml file should look this way (copy from here to the pom.xml file)

  4.0.0

  4.4.4.5.DataServiceWeb

  4.4.4.5.DataServiceWeb

  0.0.1-SNAPSHOT

  

	

		com.its.util

		com.its.util

		1.0

	

	

        

		javax.mail

		mail

		1.4

       

       

		javax.activation

		activation

		1.1.1

        

	

		log4j

		log4j

		1.2.16

	

	

		mysql

		mysql-connector-java

		5.1.6

	

    

  

After you save the pom.xml file, notice the red lines around the DataService library com.its.util.jar.
Install this library by running the following command line in the Command Line window (cmd).

 mvn install:install-file -Dfile=http://ITUniversity.us/downloads/com.its.util.jar -DgroupId=com.its.util -DartifactId=com.its.util -Dversion=1.0 -Dpackaging=jar -DgeneratePom=true

After the successful installation take a look at the Maven repository located at c:/Users/{YourUserName}/.m2/repository
Check the sub-folders - com/its/util - and find where and how Maven installed the library.
Check the sub-folders - org and com to find many familiar libraries, installed before.
At this point the red lines in the pom.xml should disappear. To force Eclipse to recompile, make a slight change in the pom.xml, for example, add a space character, and save the file again.

There is an alternative method to add a library to a project.
Create a lib folder in the project and copy the library to this folder.
In the case of the Web Dynamic project, create the lib directory under the WEB-INF and copy the library there.
Then do right-mouse click on the library and ADD to BUILD-PATH.

	javax.servlet

	javax.servlet-api

	3.1.0

Copy to this project several files from the previous project:
- web.xml to the folder src/main/webapps/WEB-INF
- Check if the src/main/java - directory was created by Eclipse.
- If it is not there, do right mouse click on the project - Build Path - Source and create the java directory under the src/main
- Copy a whole package com.its.webactions from the previous project to the src/main/java - folder.
Do not copy the second package with the sample sources of the DataService library, the connection to the library is already provided in the pom.xml
- Create the jsp folder under src/main/webapps folder and copy all jsp pages from the previous project.
- Create the css and js folders under the src/main/webapps and copy css and js files respectfully.

Now we can start modifications and additions.
Start with the web.xml file.
Change the display-name and appName parameters to 4.4.4.5.DataServiceWeb.

Add to the web.xml the following parameters:

   

    actionPackageName

    com.its.webactions.

   

Make sure that the project has Project Facets - go to the Project - Properties and click on Project Facets.
If there is only a link to create Project Facets - click on this link.
Then make sure to check the Web Dynamic Module - this will allow this project to be visible for the server.

To complete the project we need to add a database table and retrieve a login name, password and a name, if they exist.
Here is the plan.
We need to create a database in MySQL database server.
Then we will need the Accounts table, which will store the email (login), password (better be encrypted, but in this simple sample, keep it plain), and the first and last name fields.

We have to manually create the database by using MySql Workbench.
Let us call this database sampledb with the user its and password admin for simplicity.
We will associate this DB with its data source name (dsName) sampledb which will be used in the java sources.

The last parameter is the location of our SQL statements-files.
Usually in the Eclipse-Maven project, the SQL statements-files are located under src/main/webapp/config/sql folder.

You will create the config and then sql folders under the existing Eclipse project under src/main/webapp - folder.

When deployed, the location is {pathToDeploy}/{projectName}/config/sql - folder.
Hopefully all other operations related to database can be done automatically with DataService.
To create and populate the table provide the following parameters in the web.xml:

   



   

    dbParams

    sampledb,admin,its-psw,sampledb,config/sql,localhost,3306,mysql

     

   

   

    tables

    Accounts

      

   

   

    AccountsTableKeys

    email,password,firstName,lastName

     



   

    AccountsInitData

    

    john@gmail.com,Ja@track5,John,Track|

    joe@gmail.com,Joe%make34,Joe,Carter    

    

The first section of dbParams defines the following database parameters: the name of the database and the password. The third parameter defines the name of a data source, associated with the database, which will be used in a Java program as dsName variable. The last parameter defines a location for SQL statements.
So, if a deployment directory is c:/tomcat7/webapps/ and the project name is 4.4.4.5.DataServiceWeb, the location for SQL statements will be c:/tomcat7/webapps/4.4.4.5.DataServiceWeb/config/sql/.

All SQL statements will be placed here as small files, which can be easily tested with any SQL tool. Java code will call these statements by their file names.

For example, this SQL file will help to retrieve the name from the Accounts table based on an email (login) and a password:

c:/tomcat7/webapps/4.4.4.5.DataServiceWeb/config/sql/getAccountsNameByLogin.sql

select firstName, lastName from Accounts where email = ? and password = ?

The method retrieveNameFromDB() will call this SQL statement and provide run-time parameters of the email and the password entered by a user.

	/**

	 * This method is a temporary stub, till we can work with the database

         * @param appName - project name, for example, 4.4.4.5.DataServiceWeb

	 * @param login

	 * @param password

         * @param userID - login serves as a userID; used by DataService to store a history of DB changes

	 * @return name

	 */

	public String retrieveNameFromDB(String appName, String email, String password) throws Exception {

		String dsName = appName+"/sampledb"; // dsName consists of appName and origing DB name

		String[] runTimeParams = new String[] {email, password};

		String sqlName = "getAccountsNameByLogin";

                // getPrepDataWithConnection() uses JDBC connection; 

                // in the next lesson we will learn Java Persistance API (JPA) and will start using Hibernate/JPA support with DataService

		List records = DataService.getPrepDataWithConnection(sql, runTimeParams, dsName, userID);

		if(records == null || records.size() == 0) {

			return null; // not found

		}

		// there must be one record, get the first and last name

		String[] fields = records.get(0); // get the first and only record

		String firstName = (String)fields[0]; // first field is the first name

		String lastName = (String)fields[1];

		

		return firstName + " "+ lastName;

	} // end of method

The essential line in this method is:
List records = DataService.getPrepDataBySqlName(sqlName, runTimeParams, dsName, null);

The getPrepDataBySqlName() method uses the PreparedStatement, which is better performing and more secure.

The method has three parameters:

- sqlName - the name of the file with the SQL statement
Note that there is no file extension is needed in the name, DataService finds it
- runTimeParams - a string array created on-the-fly with necessary parameters, which will replace the question marks (?) in the SQL file-statement
If no run time parameters needed, there must be null as a second parameter
- dsName - Data Source name, associated with the database, usually consists of appName and original DB name, "4.4.4.5.DataServiceWeb/sampledb"
- userID - used to store DB changes with the login name (email) of a person who did the change; can be null

This method must be placed inside the LoginAction, which implements BaseAction interface.
The run() method should get all necessary parameters from the request and prepare data to call the method retrieveNameFromDB().

We are done with the project, but looking into the future, let us consider the registration action with the SQL statement to insert a new record into the Accounts table.

A similar flow is for the registration.
The registration.jsp includes the form with user data and the submit button, which sends data to the RegistrationAction.
The RegistrationAction, as any other action in DataService framework, must implement the BaseAction interface and include the run() method.
The run() method collects parameters from the request and prepare registration data to insert into DB.

Let us call this statement insertAccounts.sql.

c:/tomcat7/webapps/4.4.4.5.DataServiceWeb/config/sql/insertAccounts.sql

insert into Accounts values (?, ?, ?, ?)

Make sure that the DB has the Accounts table with the columns: email, password, firstName and lastName.

Four question marks must be replaced by run-time parameters, email, password, firstName and lastName.

String[] runTimeParams = new String[] {email, password, firstName, lastName};

DataService.setDataWithConnection(sql, runTimeParams, dsName, email); // email serves as userID

At the end of the run() method, let us point to a proper jsp page which should appear after the registration.

Assignments:
1. By using the Tomcat Server right-mouse click, add the project to the right side and remove all others.
2. Publish the project and check with Windows Explorer if it is in the webapps directory.
3. Start the Tomcat Server and check with the browser if you can enter login name and password and the project works.
4. Add to the project the register.jsp with the fields for email, password (twice), first and last name.
5. Make sure that the hidden field action in the form has value register
6. Add the RegisterAction and use the lines above to insert a new account.
7. Restart the server in the debug mode and make sure that RegisterAction works.
8. Email the project as a zip file with all necessary sources to dean@ituniversity.us

We invite you to create your own questions and answers (QnA) to increase your rank and win the Top Creativity Prize!

Topic Graph | Check Your Progress | Propose QnA | Have a question or comments for open discussion?

<br/><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<br/>  <modelVersion>4.0.0</modelVersion>
<br/>  <groupId>4.4.4.5.DataServiceWeb</groupId>
<br/>  <artifactId>4.4.4.5.DataServiceWeb</artifactId>
<br/>  <version>0.0.1-SNAPSHOT</version>
<br/>  <dependencies>
<br/>	<dependency>
<br/>		<groupId>com.its.util</groupId>
<br/>		<artifactId>com.its.util</artifactId>
<br/>		<version>1.0</version>
<br/>	</dependency>
<br/>	
<br/>        <dependency>
<br/>		<groupId>javax.mail</groupId>
<br/>		<artifactId>mail</artifactId>
<br/>		<version>1.4</version>
<br/>       </dependency>
<br/>       <dependency>
<br/>		<groupId>javax.activation</groupId>
<br/>		<artifactId>activation</artifactId>
<br/>		<version>1.1.1</version>
<br/>        </dependency>
<br/>	<dependency>
<br/>		<groupId>log4j</groupId>
<br/>		<artifactId>log4j</artifactId>
<br/>		<version>1.2.16</version>
<br/>	</dependency>
<br/>	<dependency>
<br/>		<groupId>mysql</groupId>
<br/>		<artifactId>mysql-connector-java</artifactId>
<br/>		<version>5.1.6</version>
<br/>	</dependency>
<br/>    
<br/>  </dependencies>
<br/></project>
<br/>

<br/> mvn install:install-file -Dfile=http://ITUniversity.us/downloads/com.its.util.jar -DgroupId=com.its.util -DartifactId=com.its.util -Dversion=1.0 -Dpackaging=jar -DgeneratePom=true
<br/>

<br/><dependency>
<br/>	<groupId>javax.servlet</groupId>
<br/>	<artifactId>javax.servlet-api</artifactId>
<br/>	<version>3.1.0</version>
<br/></dependency>
<br/>

<br/>   <context-param>
<br/>    <param-name>actionPackageName</param-name>
<br/>    <param-value>com.its.webactions.</param-value>
<br/>   </context-param>
<br/>

Was it clear so far? Highlight the text in question

Or onclick="window.location.href='/BASE/jsp/chat.jsp?issueID=186&ur=f'">

<br/><!-- Describe the DB: dbName dbUserName psw dsName sqlLocation hostname dbPort dbType -->   
<br/><!-- Required: dbName dbUserName psw dsName; other fields are default as provided below -->
<br/>   <context-param>
<br/>    <param-name>dbParams</param-name>
<br/>    <param-value>sampledb,admin,its-psw,sampledb,config/sql,localhost,3306,mysql</param-value>
<br/>   </context-param>  
<br/><!-- Describe the table names separated by Comma -->   
<br/>   <context-param>
<br/>    <param-name>tables</param-name>
<br/>    <param-value>Accounts</param-value>
<br/>   </context-param>   
<br/><!-- Describe the field names of for the table, which name starts the param-name -->   
<br/>   <context-param>
<br/>    <param-name>AccountsTableKeys</param-name>
<br/>    <param-value>email,password,firstName,lastName</param-value>
<br/>   </context-param>  
<br/><!-- Describe initial table records if necessary, with the records separated by "|" -->
<br/>   <context-param>
<br/>    <param-name>AccountsInitData</param-name>
<br/>    <param-value>
<br/>    john@gmail.com,Ja@track5,John,Track|
<br/>    joe@gmail.com,Joe%make34,Joe,Carter    
<br/>    </param-value>
<br/></context-param>
<br/>

<br/>select firstName, lastName from Accounts where email = ? and password = ?
<br/>

<br/>	/**
<br/>	 * This method is a temporary stub, till we can work with the database
<br/>         * @param appName - project name, for example, 4.4.4.5.DataServiceWeb
<br/>	 * @param login
<br/>	 * @param password
<br/>         * @param userID - login serves as a userID; used by DataService to store a history of DB changes
<br/>	 * @return name
<br/>	 */
<br/>	public String retrieveNameFromDB(String appName, String email, String password) throws Exception {
<br/>		String dsName = appName+"/sampledb"; // dsName consists of appName and origing DB name
<br/>		String[] runTimeParams = new String[] {email, password};
<br/>		String sqlName = "getAccountsNameByLogin";
<br/>                // getPrepDataWithConnection() uses JDBC connection; 
<br/>                // in the next lesson we will learn Java Persistance API (JPA) and will start using Hibernate/JPA support with DataService
<br/>		List<Object[]> records = DataService.getPrepDataWithConnection(sql, runTimeParams, dsName, userID);
<br/>		if(records == null || records.size() == 0) {
<br/>			return null; // not found
<br/>		}
<br/>		// there must be one record, get the first and last name
<br/>		String[] fields = records.get(0); // get the first and only record
<br/>		String firstName = (String)fields[0]; // first field is the first name
<br/>		String lastName = (String)fields[1];
<br/>		
<br/>		return firstName + " "+ lastName;
<br/>	} // end of method
<br/>

<br/>insert into Accounts values (?, ?, ?, ?)
<br/>

<br/>String[] runTimeParams = new String[] {email, password, firstName, lastName};
<br/>DataService.setDataWithConnection(sql, runTimeParams, dsName, email); // email serves as userID
<br/>