The Programmer's Weblog: September 2008

Sunday, September 28, 2008

Tiles with Struts 1.3 or Upgrading to Struts 1.3 Tutorial Step 13

To use Tiles with Struts or to upgrade to a newer version of Tiles with Struts, follow these instructions. These instructions are for Struts version 1.3.

The configuration for tiles is a little different from prior versions. The old versions of Struts required a tld folder to hold the tld files. You do not need that folder with Struts 1.3. With Struts 1.3, the tld files are already contained within the jar file.

There is a bug in older versions of the common-chain.jar file. You should visit this website and download the latest version of commons-chain and copy it into your project lib directory.

http://commons.apache.org/downloads/download_chain.cgi

You need to update a few other files as well.

Using struts 1.3 you need to add this information to your web.xml file. You should add it in the servlet section under the servlet-class section. If you were using an older version of struts, then remove the tld tags that are referencing the old version of the struts tags. Add the below code within your servlet section of your web.xml file.

Add to web.xml

<init-param>
<param-name>chainConfig</param-name>
<param-value>org/apache/struts/tiles/chain-config.xml</param-value>
</init-param>

You need to replace your DOCTYPE with this code. This is the new version of your struts.

Replace in struts.xml


<!DOCTYPE struts-config PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 1.3//EN"
"http://struts.apache.org/dtds/struts-config_1_3.dtd">

Also, in your struts.xml file you need to change your forward that is currently going to your user.jsp file to this.
<forward name="success" path="tile.userpage" />

You are changing it to reference the tile path in your tiles configuration file.
Add this code to to your struts.xml file. This is your TilesPlugin declaration.

<plug-in className="org.apache.struts.tiles.TilesPlugin">

<set-property property="definitions-config"

value="/WEB-INF/tiles-default-defs.xml" />

<set-property property="moduleAware" value="true" />

</plug-in>

What you need to do is create a folder called layout under your jsp folder. In this folder, create three jsp pages called: classiclayout.jsp, footer.jsp, and header.jsp. Also, create a file called include-taglibs.jspf under the jsp folder as well. This file will be used to call the tag libraries. You can also include other files you will want to import into your application.

Copy the following code into the proper files:

include-taglibs.jspf


<%@ taglib uri="http://struts.apache.org/tags-tiles" prefix="tiles" %>

classiclayout.jsp



<%@ include file="../include-taglibs.jspf" %>

<html>
<body>
test classic layout
<tiles:insert attribute="header" />
<tiles:insert attribute="body" />
<tiles:insert attribute="footer" />
</body>
</html>

In the header and footer files just put some text called test footer (for the footer file) and test header (for the header file).

These pages will test your configuration. The classiclayout.jsp file is the main file. It has your open and closing html and body tags. It uses tiles:insert tags to insert the proper jsp page.

In the same folder as your struts.xml file, you should create a file called tiles-default-defs.xml.
This file should already be declared in the struts.xml file. You can see my prior posts for an example of the struts.xml file.

Paste the following code into that file.

tiles-default-defs.xml



<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE tiles-definitions PUBLIC
"-//Apache Software Foundation//DTD Tiles Configuration 1.3//EN"
"http://struts.apache.org/dtds/tiles-config_1_3.dtd">

<!-- Definitions for Tiles -->
<!--
This file contains definitions common to all struts module.
In particular, it contains the main layout and the common menus.
There is also the main page as a portal.
-->
<tiles-definitions>
<!-- Doc index page description -->
<definition name="mainLayout" path="/WEB-INF/jsp/
layout/classiclayout.jsp">
<put name="title" value="Struts Test version 1" />
<put name="header" value="/WEB-INF/jsp/layout/header.jsp" />
<put name="body" value="" />
<put name="footer" value="/WEB-INF/jsp/layout/footer.jsp" />
</definition>
<definition name="tile.userpage" extends="mainLayout">
<put name="body" value="/WEB-INF/jsp/user.jsp"/>
</definition>
</tiles-definitions>

The purpose of the Tiles is to be able to maintain your layout easier. You might want the header and the footer to be the same throughout all the pages, but the body is what will change.

The definition first references the classiclayout.jsp page. It then displays the path to the header, body, and footer. The body can be left blank because we will be inserting that below.
In your struts.xml file you reference the tile.userpage which called the tile.userpage definition. That definition is using the mainLayout definition above. The body becomes the user.jsp page.

Build your war file again and visit your Project URL.

http://localhost:8080/UserBook/

You should see something like this:
test classic layout test header This is a test test footer

If you see something like this, then that means that your forward from your action class called the proper tile.user forward which used the mainlayout and inserted user.jsp page into your classiclayout page. All of your pages are included in your tiles configuration and now you are ready to start constructing your project.

Go To Step 14

Friday, September 26, 2008

Eclipse Ant Tutorial Building War File Struts Step 12

Now that you have your Struts set up with an Action class, a Action Form, the Struts.xml, a jsp page, your libraries and the web.xml files, you are ready to create your War file and test your configuration on the Tomcat Server. If you haven't gotten this far, see my prior posts on setting up your file structure in eclipse. You now need to create a build.xml file.

Create a build.xml file under the UserBook directory. The build.xml file should be at the same tree level as your src file. If you are following my tutorial, paste the following code into your build.xml file.

build.xml



<project name="UserBook" default="war">
<!-- ============ Project Settings ====================================-->
<property environment="env" />
<property name="PROJECT_HOME" value="./" />
<property name="BUILD_DIR" value="${PROJECT_HOME}/antBuild" />
<property name="JAVA_BUILD_DIR" value="${BUILD_DIR}/classes" />
<property name="CONTENT_DIR" value="${PROJECT_HOME}/content" />
<property name="JAVA_SRC_DIR" value="${PROJECT_HOME}/src" />
<property name="CONFIG" value="${PROJECT_HOME}/config" />
<property name="LIB_DIR" value="${CONTENT_DIR}/WEB-INF/lib" />
<path id="class.path">
<fileset dir="${LIB_DIR}">
<include name="*.jar"/>
</fileset>
</path>
<!-- =========== Clean ====================================================== -->
<target name="clean">
<delete dir="${BUILD_DIR}" />
<delete dir="${CONFIG}/WEB-INF" />
</target>

<!-- =========== Init System ================================================= -->
<target name="init" depends="clean">
<tstamp>
<format property="now" pattern="MMM d yyyy hh:mm aa" />
</tstamp>
<!-- Create build dir -->
<mkdir dir="${BUILD_DIR}" />
<mkdir dir="${JAVA_BUILD_DIR}" />
<mkdir dir="${CONFIG}/WEB-INF" />
</target>
<!-- =========== Compile Java Code =========================================== -->
<target name="compile" depends="init">
<echo>Compile</echo>
<javac debug="on" classpathref="class.path" debuglevel="lines,vars,source" optimize="on" destdir="${JAVA_BUILD_DIR}">
<src path="${JAVA_SRC_DIR}" />
</javac>
</target>
<!-- =========== Jar Process ================================================== -->
<target name="jar" depends="compile">
<!-- increase the build number -->
<buildnumber />
<echo>Create Jar</echo>
<jar jarfile="${JAVA_BUILD_DIR}/UserBook.jar">
<fileset dir="${JAVA_BUILD_DIR}">
<include name="com/**" />
</fileset>
</jar>
<copy file="${JAVA_BUILD_DIR}/UserBook.jar" todir="${LIB_DIR}" />
</target>


<!-- =========== War DEV ======================================================= -->
<target name="war" depends="jar">
<echo>${BUILD_ENV}</echo>
<war warfile="${BUILD_DIR}/UserBook.war" webxml="${CONTENT_DIR}/WEB-INF/web.xml">
<manifest>
<attribute name="Built-By" value="${user.name}" />
<section name="Struts 1">
<attribute name="Implementation-Title" value="UserBook" />
<attribute name="Implementation-Build" value="${build.number}" />
<attribute name="Implementation-Build-Date" value="${now}" />
<attribute name="Implementation-Vendor" value="" />
</section>
</manifest>

<!-- include everything in content directory -->
<fileset dir="${CONTENT_DIR}" />
<lib dir="${JAVA_BUILD_DIR}">
<include name="*.jar" />
</lib>
<lib dir="${LIB_DIR}">
<include name="*.jar"/>
</lib>
<classes dir="${BUILD_DIR}/classes">
<exclude name="*.war" />
</classes>
</war>
<copy file="${BUILD_DIR}/UserBook.war" todir="${env.CATALINA_HOME}/webapps" />
</target>
</project>

At the top of the build.xml file you have the project name which in our case is UserBook.

Looking at the project settings, you will see a list of variables that will be used in your build.xml file. The environment="env" is pulling in the environmental variables that will be used. We need to know the environmental variables to know what the Tomcat path is. The Project_Home is the home of your current project. To use the Project_Home variable, you can put it like this: ${Project_Home}. So the other variables are using the Project_Home variable to declare variables in other areas of your project. We have one that goes to your library directory, your src directory, your content directory, antBuild directory and your classes directory.

The first thing you do when building your project is clean the project by deleting the Ant Build directory.

The next thing you do is to initialize the system by stamping a time date on your war file, and then making sure you have the ant build directory created.

The depends says "clean" which means that this target depends on clean. That means that clean will happen first, and then the init will take place.

The next target you have is compile which compiles your java code and puts the classes in your ant build directory.

Below that you also create a jar file. This jar file can be used later if you need to use these classes in another project. You have a copy that copies the jar file into the lib directory in your ant build directory. Within the war file section, you want to tell ant what files to include in the war file.

After you create your war file, you will copy the war file to your tomcat webapps directory.

Now you need to start your tomcat server to see if everything is working correctly.

The first time you build your file you need to select the right mouse button on your build.xml file and select "run as" ant build. After the first time you run the build, you can select the green icon at the top of eclipse to build the project.

After you build your file, you should see printing in your console that will tell you the order and what is taking place while eclipse is building your War file. First, the build file should be cleaning your system, then running init, then compile, then create your jar files, and then it will create your war file. If your build was successful, it will say Build Successful. If there was a problem, then eclipse will give you an error that the code didn't compile correctly and you will need to fix it.

Before starting Tomcat, I usually will delete the directory from the webapps folder. You don't have to delete the War file, but just the directory that says UserBook. After you build your War file, you should be able to look in this directory and see a file called UserBook.war which is the file you just created.

If you installed the Tomcat plugin for eclipse, you should see a cat at the top of the menu in eclipse. When you select the cat, tomcat will start and you should see information in your console. If you see any stack traces of Java code while Tomcat is starting, then there might be a problem with your xml mappings. If you see
INFO: Server startup in 27813 ms then your Tomcat was started successfully.

Now you need to open a browser and goto this URL to see if your project is working correctly.

http://localhost:8080/UserBook/

The localhost is the tomcat on your machine, and the 8080 is the port number for this Tomcat instance. The UserBook is the name of your project and your War file.

If you see "This is a test" then your project is working and you successfully configured Struts 1.

Your index.jsp page forwarded the user to your UserAction.do which is in your struts mapping. Struts will call the action method in your action class called forwardToUser. After the forwardToUser method is finished, it will return a mapping.findForward("success") which will read the struts.xml file and send the user to the user.jsp page.

In my next post I will show you how to set up tiles with struts. Tiles is used to seperate your application into sections to make it easier to manage.

Go To Step 13

Thursday, September 25, 2008

Struts 1 Struts.xml, Action, Forms Example with Eclipse Step 11

Struts requires a Struts config file. Under your content directory and your WEB-INF folder, you need to create a xml file called struts.xml. This should be the same name of the struts file you mentioned in your web.xml. After you create the struts file, then paste the following code in the file if you are following my tutorial.

Struts.xml

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE struts-config PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 1.1//EN"
"http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd">
<struts-config>
<!-- ========== Form Bean Definitions =================================== -->
<form-beans>
<form-bean name='userForm'
type="com.actionform.UserForm" />
</form-beans>
<action-mappings>
<action path="/UserAction" name="userForm" type="com.action.UserAction" parameter="action" scope="session">
<forward name="success" path="/WEB-INF/jsp/user.jsp" />
</action>
</action-mappings>
 
</struts-config>

At the top of the configuration file you have the DOCTYPE. Here is where you declare the version of your struts. Currently it is incorrect for the struts I am using, but I will show you how we will change this when I introduce tiles. You then have your form bean definitions. The form bean is the class objects that will be attached to your JSP paged. The form bean we are using will be called UserForm. I will show you the use of the Action Form shortly.

After the form beans you have your action mappings. These mappings are how the user will call your action classes. You will forward the user to where you want the user to go after the action is performed.

There are other options that you can put into your struts config file, and I will get into some of them later. The Action Class we are using in your struts.xml file is called UserAction.

So the next thing we need to do is create your Form and your Action classes.

Under your src (source folder) you should create two packages. You should already be familiar with Java's packages. You can create a package in eclipse by selecting the right mouse button on the src folder and select new and then package. Name one package called com.action and the other one called com.actionform. Create a class called UserAction under the com.action package and create a class called UserForm under the com.actionform package. Those should be the same name you used in your struts.xml file.

Paste the following code into your class:

UserAction.java



package com.action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;

public class UserAction extends DispatchAction{
public ActionForward forwardToUser(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
System.out.println("in forwardToUser");
return mapping.findForward("success");
}
}

UserForm.java


package com.actionform;
import org.apache.struts.action.ActionForm;

public class UserForm extends ActionForm{
private static final long serialVersionUID = 123L;
}

Currently the UserForm is just a skeleton so we will get back to that later.
The UserForm class will extend the ActionForm class and the UserAction class will extend the DispatchAction class. All I have in this class right now is a System.out.println so you will know that you have arrived to this class. Do not get into the habit of adding a bunch of System.out into your program. You should use the log4j which I will get into later.

You will also want to create a temporary jsp page to visit. Create a page called user.jsp under the jsp folder which is in your WEB-INF folder. Call the file user.jsp and copy the below code into it.

user.jsp


<body>This is a test</body>

The last file that you will create will end up becoming a login page. For now, we are going to redirect the user to an Action Class. The index.jsp page is the first page the user will come to when visiting your application. Create a file under your content directory called index.jsp and copy the below code. The code redirects the user to the Action class. After we build your WAR file, I will walk you through the system so you know how everything works.

index.jsp


<% String redirectURL = "/UserBook/UserAction.do?action=forwardToUser"; response.sendRedirect(redirectURL); %>

The above code redirects the user to the UserAction.do class when they first arrive at your application. In my coming posts, I will show you how to create a WAR file using ANT, and how to use Tiles within your Struts Application.

Go To Step 12

Wednesday, September 24, 2008

Struts 1 Web.xml on Eclipse Step 10

For a struts application to work, you need to have a web.xml file. Create a file under your content/WEB-INF directory called web.xml. To do this, select the right mouse button on the WEB-INF folder and select create new and then file. Name the file web.xml. To open up an xml file for the first time in eclipse, you should select it with your right mouse button and select open with->text editor. If you do not open it with the text editor, it will open up in a browser.

Struts Web.xml



<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">

<description>UserBook</description>

<!-- Standard Action Servlet Configuration (with debugging) -->
<servlet>
<servlet-name>action</servlet-name>
<servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
<init-param>
<param-name>application</param-name>
<param-value>ApplicationResources</param-value>
</init-param>
<init-param>
<param-name>config</param-name>
<param-value>/WEB-INF/struts.xml</param-value>
</init-param>

<load-on-startup>1</load-on-startup>
</servlet>

<!-- Standard Action Servlet Mapping -->
<servlet-mapping>
<servlet-name>action</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>


<!-- Time Out -->
<session-config>
<session-timeout>60</session-timeout>
</session-config>

</web-app>

The above code should be pasted into your web.xml file.
You should already be familiar with xml. Tomcat will read the web.xml file to load certain classes needed at the startup of the server. The action is a standard servlet that is used by struts.

A servlet is a Java class that extends the capabilities of servers that can host applications using the request-response life cycle. They are commonly used to extend the applications used by Web Servers, in this case, Tomcat.

In the web.xml file you will inform Tomcat of the struts.xml file that you are using. Load-on-startup says to load the struts config file when the server starts. You also have the action *.do url pattern. This informs Tomcat that the struts actions will be followed by a .do. That is struts standard. You also have the session timeout which we have set to an hour. This means that after an hour, the user will no longer have an active session. They will have to open up another browser to start a new session. You should always have a session timeout or your system can get overloaded by users.

Go To Step 11

Sunday, September 21, 2008

Adding Libraries to Eclipse Project-Tomcat Eclipse Set Up-Step 9

In a normal project you will need libraries to perform many functions in your system. In Java these libraries are usually jar files. See my prior posts on creating a project in Eclipse. After you have completed your project creation, you need to add the libraries to your library folder in your project.

You should now add your libraries to your Eclipse project. Open the xerces folder and copy the xerces.jar file into your lib directory which is in your WEB-INF directory in eclipse.

Copy the all the jar files from struts-1.3.8-all lib directory into the lib folder in your WEB-INF.

You will also need to copy the servlet-api in your lib directory which is located in your tomcat directory/common folder.

You should also copy the jars (xalan.jar,xml-apis.jar, xercesImpl.jar and serializer.jar ) into your lib directory from the xalan folder.

When you have all of your libraries in your lib directory, you should set your buildpath to these libraries so eclipse will know about them.

The way you need to do this is by selecting the right mouse button on your project name (in our case UserBook) and select properties. You should select Java Build Path. Select the properties tab and select Add External jars. You should locate the lib directory in your workspace and select all the jar files and press okay. All the jar files should show up as references to the lib directory in your WEB-INF directory.

You should also configure Tomcat by selecting Windows->Preferences. In that window you should select your Tomcat version which is 5.x. You will also need to browse to the Tomcat Home path which might be C:\Tomcat (should be where your Tomcat instance is located). This tells Eclipse where your Tomcat is located.

Now see my next post for setting up your first struts 1.x application.

Go To Step 10

Saturday, September 20, 2008

Struts and the MVC Architecture Step 8

To start using Struts, you have to understand its Framework. Struts uses the MVC architecture. MVC stands for Model, View and Controller. The "View" are the JSP pages that the user will see on their browser. The "Model" is the business logic that interacts with the database. The "Controller" determines the overall flow of the application.

The way struts will work is the user will interact with the JSP pages on their browser. The JSP pages uses a form which is attached to a Java Bean. A Java Bean is a standard Java class that usually consists of getters and setters to interact with the JSP pages. The user will perform an action which will call a struts Action class. The action class will perform a function, possibly perform some business logic and possibly will interact with the database. The action class will then forward the user to a specific location using an Action Forward. The Action Forwards are located in the struts.xml file. Tomcat will convert the JSP pages to html and send the html back to the browser.

Go To Step 9

Friday, September 19, 2008

Setting up File Structure in Eclipse and the WAR File Structure Step 7

Picture above is the Eclipse File Structure.

The ultimate goal is to build a WAR file that will be deployed to an application server, in our case Tomcat. The WAR file contains all the necessary files for your application. It will contain the configuration files, the Java classes, images and any other files that you will use for your application. The WAR file has a specific file structure as seen below. You will deploy your WAR file to the webapps folder in Tomcat and Tomcat will create the proper file structure when you start Tomcat.

Below is the Tomcat file structure. You will put the WAR file in the webapps folder. In that folder you have the META-INF folder which is used by Tomcat. You will have the resources folder which contains all your images, js and css files. In your WEB-INF folder you will have your JAVA classes, jsp pages, libraries and for some older versions of struts, the tld files. The WEB-INF folder will contain your struts.xml files and your web.xml file. You do not need to create this structure because Tomcat will create it for you.

The structure that you will have to build is the eclipse file structure. You should set up your file structure in eclipse similiar to the file structure in Tomcat. In eclipse you will have your Project. Under your project you will have your src folder which contains your JAVA packages. You will also have a list of any attached jar files that you are using in your project. The antbuild folder is a folder that you will keep your classes and war file in. You do not need to create this folder because it will be created in your ant script. The content folder contains your resource folder and your WEB-INF folder. The WEB-INF folder will contain your jsp, lib, and struts tld files (In our version of struts, we won't be using the tld folder). The WEB-INF folder will also contain your web.xml file and your struts config files.

You should open up your eclipse. If you haven't already picked your workspace, you should do that now. To create a new project you will select File, New and Java Project. You will want to name your project. I am going to call this project UserBook since it is about people who read books. After you name the project, you can press finish.

Eclipse should create a project for you.

You will see, under your package explorer, a project called UserBook. If you expand that project, you will see the folder src and the JRE System Library folders. You should go ahead and create the rest of the needed folders. Select the right mouse button over the UserBook folder and select new and source folder. After you have created that folder, you should create a folder called content. Follow the same procedures and under the content folder you should create a folder called resources and WEB-INF. Under the WEB-INF folder, you should create folders called jsp and lib. These should be regular folders, but your src should be a source folder.

Now your file structure is all set to begin development on your new project.

The tld folder isn't needed with newer versions of struts. The older versions of struts requires this folder to put the tld files in, but in newer versions of struts the tld files are contained within the jar files.

Go To Step 8

Tuesday, September 16, 2008

Querying mySQL database Select, Delete, Insert and Update using SQLyog Step 6

There are basically 4 functions you will want to do to your database using your mySQL Enterprise Manager. You need to know how to insert data, how to delete data, how to retrieve data, and how to update data.

First, we need to insert something. Our scenerio goes like this:

There is a user named Bob Anthony Jones.

He enjoys reading science fiction books and one book is called Dragon Lance. The author of the book is called Tracy, and the publisher is White House Publishing.

We first should enter a category called Science Fiction.

You should enter your queries into the text area of your Enterprise Manager called query. You will run your query by pressing the green button in the top left corner of the SQLyog Enterprise Manager.

To insert Science Fiction into your bookcategory database, run this query:

insert into bookcategory (category) values ("Science Fiction")

To view that your data was inserted correctly, you can run the following select statement to view that the row is inserted correctly.

select * from bookcategory

In the insert statement, you will first have the name of the columns that you are putting data, and then the values that you are inserting. You do not have to insert an id because the id is auto incremented. mySQL will assign an id to the row.

The select statement retrieves the data from bookcategory and displays it. The * is a wild card and means that it will retrieve all the columns for this table. If you want to select a specific column, you can enter it instead of the * and seperate it by commas like this:
select Id,Category from bookcategory

You should now insert a row into the book table like this:

insert into book(BookName,BookAuthor,BookPublisher,BookCategoryId) values ('Dragon Lance','Tracy','White House Publishing',1)

Again you can check that the row was inserted correctly by typing:

select * from book

The id for BookCategoryId is the id from the bookcategory table. That is the foreign key to reference the relationship to the bookcategory table. That key will tell you that the book has a category of science fiction.

Now we should insert our user like this:

insert into users(FirstName,LastName,MiddleName,Email) values ('Bob','Jones','Anthony','')

Again you can view the data by doing a select statement. To input the email address, you can enter '' for empty or if the row allows nulls, you can leave the column blank and the system will insert null. This statement will insert null into the column Email:

insert into users(FirstName,LastName,MiddleName) values ('Bob','Jones','Anthony')

We have to insert one row into the userbook table that will give the relationship between books and users.

insert into userbook (bookid,userid) values (1,1)

This query might give you an error. My user table inserted a 0 for the id in the first row. If that is the case, you need to insert it with the proper id.

insert into userbook (bookid,userid) values (1,0)

(by the way, only put single quotes around something when it is a string. If the value is an integer, do not put single quotes around it.)

Now you have data in all the tables with their proper relationships.

If you wanted to delete something, you can write the delete command like this:

delete from userbook where userid=0

You need to be careful about the delete command because if there is more then one row with a userid of 0, then all the rows will be deleted. In this case the id is unique so we don't have to worry about deleting more than one row.

The update command would be something like this:

update userbook set userid=1 where bookid=1

If you didn't put the where clause there, it would update userid on the entire table.

Now I am going to show you how to get all the data that you are going to want to view.

Run this query:

select u.firstname,u.lastname,u.middlename,u.email,b.BookName,b.BookAuthor,b.BookPublisher,bc.category from book b inner join bookcategory bc on bc.id=b.bookcategoryid inner join userbook ub on ub.bookid=b.id inner join users u on u.id=ub.userid

This query will display all of the relevant data that a user might want to see from this database. They can view the first, middle,last name and the user's email address. They can also see the book name, author, publisher and category.

If you notice I have u.firstname instead of firstname. The u is called an alias. I renamed the table in the query from user to u so it is easier to type. You use the inner join command which combines the tables by their keys. That is the purpose of putting the keys in the tables so I can easily get the tables together by their primary and foreign keys. Inner Join will retrieve the data only if the keys match. If the userbook table had a 1 for UserId but there aren't any users in the users table with an id of 1, then the query will display nothing.

Other ways to join:

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).

Now you should have a basic knowledge of setting up a database in mySQL. Now it is time for you to properly set up a project in Eclipse.

Go To Step 7

Jump to Advanced Query Information

Monday, September 15, 2008

Adding Relationships to a Database MySQL using SQLyog Step 5

I am going to explain how to relate the database properly using the SQL Enterprise Manager (SQLyog), and then I will explain what it means.

Following the tutorial, we have tables called users, userbook, book and bookcategory. We need to establish relationships between them. The users table will keep track of the users in our system, the book table keeps track of the books the user has read, and the bookcategory is the table that keeps track of what category the books are in. The userbook is the table that is the relationship between the user table and the book table. This table is to establish the many to many relationship between users and the books they read. A book can be read by many users and a user can read many books.

You want to relate users to the userbook table and you want to relate book to the userbook table. You will also want to relate book to the bookcategory table.

Select the userbook table with the right mouse button after you opened up the SQLyog. You will want to select relationships/foreign keys from the menu. You will want to press new to create a new relationship. A create relationship table will popup. The source is the table you selected which is the userbook table and the target column is the table that you are going to relate this table too. The ids are the columns that the database will usually be related to because they are unique. You first want to select the target table in the referenced table (primary key table) drop down box. In that drop down box you should select the user table. The current table will be the foreign key table.

The Source Column will be the UserId and the Target column will be Id. You should create another relationship in the userbook table. The primary key table will be the book table. The Source Column will be the BookId. You will need to change the constraint name to be FK_bookbook to be a different name then the FK_userbook since the other relationship already took that name. You can then press create to create the second relationship. You can now close the Manage Relationship GUI.

You will want to open up the Manage relationship table on the book table. Select new to create a relationship on this table. You want to select book from the referenced table. The Source Column should be BookCategoryId so the book table now has a relationship with the category table. The Target Column should be Id. Press create to create that relationship and close the manage relationships GUI.

You have now added relationships to these tables.

There are different relationships a database can have between tables.
Tables are related to each other by keys. A table usually will have a primary key which is the key to the table and possibly a foreign key which is the link to another table.

A one to one relationship between tables will mean that an employee can work in only one department. If it is a “one to one” relationship, then that will also mean that a department can have only one employee which isn’t usually the case; therefore, that would be a “one to many” relationship. In our database, we will have a “one to many” relationship between the book table and the bookcategory table. That means that a book can have only one category to classify it, but the category can have more then one book in that category. A “many to many” relationship in a database is a little more complicated to implement. In our case, we have a many to many relationship with the user and the book table. The user can read more then one book and the books can be read by more then one user. A solution to a many to many relationship is to compose the two tables into two relationships with a newly created table to intervene between the two. Our intervening table is the userbook table.

In my next post I will discuss querying the database. You will need to know how to insert data, update data, and delete data from the database.

Go To Step 6

Friday, September 12, 2008

mySQL Tutorial Creating Tables and Using an Enterprise Manager Step 4

You can do everything in mySQL by typing commands in the command prompt, but that is the hard way. I prefer to use an Enterprise Manager that will allow you to make changes to your mySQL with a GUI.

Go to this site: http://www.webyog.com/en/downloads.php and download SQLyog which is a free open source tool to help you manage your mySQL. You should download the SQLyog MySQL GUI - Community Edition 7.02 (Stable) for windows. There are some versions that you will have to pay for. I have looked for many Enterprise Managers for mySQL and this is the only one that I found that was free and open source.

You will need to install the Enterprise Manager and open it up. Once you are viewing the GUI, you should see a list of your databases. The one we created in my last post should be showing called catalog.

If you select catalog, you will see directories called Tables, Views, Stored Procs, Functions, Triggers, and Events.

We are going to create 4 tables.

The table names:

(Do not call the table User because that is a reserved word )

Users
columns: Id,FirstName,LastName,MiddleName, Email
(don't put spaces in the column names)

Book
columns: Id, BookName,BookAuthor,BookPublisher,BookCategoryId

UserBook
columns: BookId,UserId

BookCategory
columns: Id,Category

Select your catalog database and select the right mouse button on tables and select create table.

You should see a window that will allow you to input all of the field types required for
this table.

For the User table in the Field Name type Id.
The datatype is a drop down list.

The first column in this table is the Id so you should select bigint as the datatype. You should input 10 as the length. You will want to select PK, Not Null and Auto Incr.
The PK means primary key. The primary key of a table is used to reference something quickly without having to type in a full name. It is a look up value. I will explain more later. The Not Null says that if a row is inserted into this table, this column has to have a value. This column does not allow nothing to be placed in it. There is a difference between Null and an empty string. An empty string is a string that doesn't have any value, but a null value is when you don't assign anything to the column.

I am using Auto Increment to have the values in this column to be incremented when inserting a new row. This value will be incremented by one for each row that is inserted and this value will be the key for this row.

Insert the rest of these fields:
First Name,Last Name,Middle Name, Email
Select the datatypes to be char and the len to be 50.

Select create table and type the name for the table which is Users.

It will ask you if you want to add more tables so you can say yes and add the
rest of the tables. See below for what values you should input.

Book
columns:
Id-bigint, len 10, auto increment,primary key, not null
BookName-char, 50 length
BookAuthor-char, 50 length
BookPublisher-char, 50 length
BookCategoryId-big int, len 10

UserBook
columns:
BookId-big int,len 10,not null
UserId-big int,len 10,not null

BookCategory
columns:
Id-bigint, len 10, auto increment,primary key, not null
Category-char, len 50

After you have created your tables, you should see book, bookcategory, user and userbook under your tables.

Now it is time to create your database relationships. A relational database allows you to store the data in an organized way and minimizes the data that is required to be stored.

So it is important to add relationships to your database to enforce the integrity of your database. If you didn't have any relationships, then data can be inputted into the database without any organization. The relationships will force the user to input the data correctly into your database.

It is important to have your database someone normalized. Normalization is a technique to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems. There are different normal forms for a database that allows the database administor to decide how normal they want the database. There are pros and cons to them, but I won't go into them now.

In my next post, I will show you how to set up your relationships and to insert and query your database.

Go To Step 5

Thursday, September 11, 2008

mySQL Tutorial Step 3 Installation and Setup

I am choosing to do a tutorial using mySQL since it is an open source database. It is also widely used for certain projects. You first should download mySQL. I am using Windows XP, so these are the instructions I will be giving you.

See my prior posts for installing mySQL.

Now that mySQL is installed, you will need to start the service. Find your myComputer icon on your desktop or your start menu. You should select your right mouse button on it and select the manage in the list of options.

Select Services->applications and then select services. This will give you a list of all your services. If you installed mySQL as a service, then you should see a mySQL icon in the list of services. Select on it and select start service. After you have started the mySQL service, you can close the window.

Now you need to log into mySQL. Open up your Dos Prompt window. You can do this by selecting start->programs->accessories->dos prompt.

Once your DOS window is opened, you will need to traverse to the directory where your mySQL is located. You can change directories by typing: cd Name of the directory and you can move back up the directory by typing: cd ..

If you installed mySQL in your Program Files directory, you can type this from your root directory (You might need to adjust it to where your mySQL is installed) : cd \Program Files\MySQL\MySQL\MySQL Server 5.1\bin.

When you are in the correct database, you can test your installation by typing:
mysqladmin -u root -p version

Remember to enter the password that you set when you installed mysql.

Now, it is time to login to mysql. You should type the following command.

mysql -u root -p

-u specifies the root password and -p specifies to be prompted for a password.

Your dos prompt will now change from C:\> to mysql: and that means you have successfully logged in.

Now you should create a database. We are going to build a relational database that we will use later.

First create a database called: catalog. You can do this by typing Create DATABASE catalog;

Don't forget to include a semicolin after the command in mySQL.
If it says Query OK, 1 row affected then you were successful.

To exit mysql and return back to the Dos prompt, you can type exit.

In my next post I will continue to explain how you set up users and create tables in mySQL.

Go To Step 4

Linkbar

Adgitize Here

Blog Archive

Sunday, September 28, 2008

Tiles with Struts 1.3 or Upgrading to Struts 1.3 Tutorial Step 13

Friday, September 26, 2008

Eclipse Ant Tutorial Building War File Struts Step 12

Thursday, September 25, 2008

Struts 1 Struts.xml, Action, Forms Example with Eclipse Step 11

Wednesday, September 24, 2008

Struts 1 Web.xml on Eclipse Step 10

Sunday, September 21, 2008

Adding Libraries to Eclipse Project-Tomcat Eclipse Set Up-Step 9

Saturday, September 20, 2008

Struts and the MVC Architecture Step 8

Friday, September 19, 2008

Setting up File Structure in Eclipse and the WAR File Structure Step 7

Tuesday, September 16, 2008

Querying mySQL database Select, Delete, Insert and Update using SQLyog Step 6

Monday, September 15, 2008

Adding Relationships to a Database MySQL using SQLyog Step 5

Friday, September 12, 2008

mySQL Tutorial Creating Tables and Using an Enterprise Manager Step 4

Thursday, September 11, 2008

mySQL Tutorial Step 3 Installation and Setup

Followers

My Tutorials

Subscribe To My Blog

SiteMeter

My Blog List