Java Web Hosting Blog

[ Team LiB ] 6.3 Using SQL Directly from JSPs The standard tag library contains tags that allow SQL commands to be embedded directly in a page. The most basic of these is the query tag, which allows a page to perform a select and display the results. The tag’s use is demonstrated in Listing 6.3, which selects the list of artists from the table defined in Listing 6.1. Listing 6.3 A page that gets data from a database <%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %> <%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>

• “>

This example starts by importing the core library and a new SQL library that contains the new tags. Immediately after loading the library, the query tag is used to load some data. The query tag has many options, but the ones used here are the most common. First, the tag needs to be told how to connect to the database where the information lives, which is specified as the dataSource parameter. The exact form of this will make more sense after Chapter 9, but for now, think of it as naming three things: the location of the database, the kind of database, and the user name and password with which to connect to the database. These are all specified on one line, separated by commas. The sql parameter specifies the SQL to execute. The SQL used here is a simple select command. Finally, the var parameter names a variable in which the results of the query should be stored. This is somewhat similar to the var parameter in the c:forEach tag in that both make a value available elsewhere on the page. Not coincidentally, the next place this variable is seen is in a c:forEach tag on the next line. Note that this variable is used as the items, because this one variable contains something like an array, each element of which will be one row of data. The artist variable, defined in the c:forEach tag, will hold each row in turn. Within the body of the c:forEach tag, the artist variable acts like the param variable in Section 4.6, which has a different property for each value sent by a form. Similarly, artist will have one property for each column, which may be obtained by using the normal dot notation used with beans. The artist name, therefore, is obtained with Page 112
Note: If you are looking for top 10 and very good webhost to host and run your jsp application check Actions jsp hosting services

[ Team LiB ] Page 111

Hint: This post is supported by Gama web hosting hrvatska services

[ Team LiB ] 6.2 A Language for Databases For humans and databases to work together, they must speak a common language. Although in principle, every database manufacturer could define its own such language, doing so would cause problems for both users and database vendors. To avoid these problems, a standard called Structured Query Language (SQL, pronounced “sequel”) that all database vendors support, although frequently with some enhancements specific to their products, has been defined. Most databases provide a utility program that allows users to enter SQL commands interactively and get results back. That program for hsqldb’s can be accessed by running the following: java -cp hsqldb.jar org.hsqldb.util.DatabaseManager One such command might be instructions to create a new table by specifying the names and types. The SQL commands to create the CD and track tables from Tables 6.1 and 6.2 are shown in Listing 6.1. Listing 6.1 SQL commands to create tables CREATE TABLE artist ( artist_id int, name char(40) ); CREATE TABLE cd ( album_id int, artist_id int, name char(40) ); CREATE TABLE track ( album_id int, name char(60) ); These commands define the columns in each table by giving each column a name and a type. The semicolons here indicate the end of each SQL command. This is a common convention but is not universal. Some SQL interpreters require the word go after each command. Once the tables have been created, data can be stored in them with SQL’s insert command, as shown in Listing 6.2. Listing 6.2 SQL commands to put data into tables INSERT INTO artist VALUES(1,’Mors Syphilitica’); INSERT INTO cd VALUES(1,1,’Primrose’); INSERT INTO cd VALUES(2,1,’Feather and Fate’); INSERT INTO track VALUES(1,’Ungrateful Girl’); INSERT INTO track VALUES(1,’Remidy’); INSERT INTO track VALUES(2,’The Hues of Longing’); INSERT INTO track VALUES(2,’Naturally Cruel’); Page 110
Note: If you are looking for top 10 and very good webhost to host and run your jsp application check Actions jsp hosting services

[ Team LiB ] Page 109
Note: If you are looking for high quality webhost to host and run your jsp application check Vision jsp hosting services

[ Team LiB ] 6.1 A Quick Introduction to Databases Because any large collection of information is in a sense a database, there are many kinds of databases. The most commonly used kinds of commercial databases are called relational databases. Relational databases store information in conceptually simple structures called tables. A table in a database is something like an HTML table or, for that matter, a table in blog. For example, Table 6.1 contains some information about a CD collection. Table 6.1. A Table with CD Information Artist Album Name Black Tape for a Blue Girl The Scavenger Bride Mors Syphylitica Feather and Fate Voltaire Boo Hoo The data in Table 6.1 is organized into rows, each of which describes a single CD. Each row has columns, or fields, each containing a simple attribute of the CD. Each column also has a name, specified in the table header. A table in a database also has rows containing named columns; the only additional feature is that each column also has a specified type. Most databases handle types that will be familiar to Java developers: integers, characters, strings, dates, floats, and so on. Some fields will be allowed to have a special value, NULL, which means “no data is available.” The empty test as used in Listing 4.12 can be used to check for this special value. Next, consider the problem of adding track data to the CD table. One possibility would be simply to add fields, such as track title and track length, to Table 6.1, but doing so would mean that every track entry would need to contain the album and artist name as well, which would waste space on the page or on disc, in the case of a real database. It would be much more efficient to use two tables: one for tracks and one for CDs. The two can be linked by giving each CD a unique integer ID and referencing that ID in the track table. This would lead to Tables 6.2 and 6.3. Using integers to link up tables is a very common technique, especially when mapping one-to-many relationships, whereby a row in one table may connect to many rows of another table. Integers are small and so do not take up much space in the database, and because integers are easy to sort and manipulate, looking up information based on an ID is typically very fast. Similarly, because artists typically have many albums, another possible efficiency is to be gained by moving artists into their own tables and using an artist ID to map them to their albums. Many, many databases are available. Many business sites use products from Oracle or Microsoft, but a number of high-quality, free databases also are available. These databases are perfectly suitable for small to midsized sites or for development and are very attractive to people who cannot afford a large commercial database. MySQL and PostgreSQL are prime examples of this latter type of database. MySQL is available from http://www.mysql.org, and PostgreSQL is available from http://www.postgresql.org. Table 6.2. The CD Table with a Unique ID Artist Album Name Album ID Black Tape for a Blue Girl The Scavenger Bride 1 Page 108
Note: If you are looking for reliable and quality webspace company to host and run your servlet application check Actions servlet hosting services

[ Team LiB ] Chapter 6. Databases In one sense, all Web sites are about information, or data. The stories on a news site are data, as are the items in a catalog. A great deal of data exists behind the scenes, such as information about users or the types of data they are interested in. The problem of organizing large amounts of data is not a new one; many companies had to organize inventory or customer data long before the Web. This need to organize data gave rise to a kind of application called a database, a repository of structured information optimized to store and retrieve data quickly. Databases also allow multiple users to access or even change the same data simultaneously without corrupting it. This chapter presents a brief overview of database technology, including standard tag library built-in features that greatly simplify working with databases. This chapter also discusses low-level techniques that allow JavaServer Pages to access databases and then discusses a bean-based approach that is both sophisticated and simple to use. [ Team LiB ] Page 107
Note: If you are looking for cheap and inexpensive provider to host and run your tomcat application check Actions tomcat hosting services

[ Team LiB ] 5.10 Tags Learned in this Chapter c:param Passes a parameter to a page or URL Parameters: name: The name of the parameter value: The value of the parameter; may be a script Body: None c:url Construct a URL suitable for use in an href Parameters: value: The base page of the URL Body: c:param tags [ Team LiB ] Page 106
Note: If you are looking for cheap and inexpensive provider to host and run your tomcat application check Actions tomcat hosting services

[ Team LiB ] 5.9 Summary and Conclusions This chapter conveyed how easy it is to put together a site using beans and the standard tag library. Although Java News Today is still quite simple in both design and functionality, the principles used in this example are universal and will scale well in any site. Although the JNT site itself is fairly dynamic, the data behind it is not. There is no way to add new stories, user preferences will be lost when the session expires, and comments will be lost if the system is ever shut down. The solution to all these problems is to have the beans communicate with a database, but before seeing how this is done, it will be necessary to discuss databases in general. This is the topic of the next chapter. [ Team LiB ] Page 105
Note: If you are looking for reliable and quality webspace company to host and run your servlet application check Actions servlet hosting services

[ Team LiB ] 5.8 The Remaining Pages That pretty much wraps up the set of pages available at Java News Today, at least for now. Two other pages were not mentioned because they do not include anything new, but for the sake of completeness, they will be discussed briefly. All pages are available on the companion CD-ROM. The front page, index.jsp, shows a list of the ten most recent stories. This looks exactly like the section page except that the list comes from edition. recentArticles instead of currentSection.articles. Finally, a page is available for the user to change preferences, which was already covered in Listings 3.16 and 3.17. [ Team LiB ] Page 104

Hint: This post is supported by Gama web hosting hrvatska services

[ Team LiB ] Page 103

Hint: If you are looking for very good and affordable webspace to host and run your j2ee hosting application check Virtualwebstudio j2ee web hosting services