| Now that you're ready to build your schema and populate it with data, it's worth talking about how to manage your database. In the BFG example, MySQL is the database that was chosen. Accordingly, a database instance, called BFG, was created to use with it. Although you can do schema and database maintenance on the fly during development, it's important to capture all that information into scripts that can be run repeatedly, each time re-creating a perfect beginning state for your database. WHY USE MYSQL? When choosing a database for a JSP project, a number of considerations come into play. MySQL certainly has one overwhelming characteristic that places it at the head of the class: It's free. If price were the only factor, that would be the end of the story. However, MySQL does have its problems. Although each release offers a huge leap from the previous one in functionality, MySQL still lags behind the commercial products. When choosing a database, you need to consider not only your needs today, but also your anticipated growth. This is because, unlike application platforms that can be interchanged without a lot of difficulty, it's an extremely involved proposition to swap out databases midstream. You need to consider both the size of your data and how complex the queries are that you'll be running on it. For example, when you start to introduce a lot of inner joins into your queries, you can really bog down database performance. There's no doubt that, as of today, the commercial databases such as Oracle and Sybase offer better high-end performance. So, if you're looking at an application that's going to do hundreds or thousands of transactions an hour, you might need to move up to one of these packages, although it won't be cheap. For the purposes of these examples, though, and for most small to midsize Web applications, MySQL will do just as well as the larger packages. | To begin, you need a script to create the database itself. This can be done directly from the MySQL GUI, but, again, you want it to be easily and automatically reproducible, even by untrained personnel. Listing 6.1 shows the script version for Windows (you'd need a different version for Linux, although the SQL scripts themselves would remain the same): Listing 6.1 database_create.bat REM Script to create the Books For Geeks database instance if NOT DEFINED MYSQL_HOME SET MYSQL_HOME=C:\MYSQL %MYSQL_HOME%\bin\mysqladmin drop BFG %MYSQL_HOME%\bin\mysqladmin create BFG %MYSQL_HOME%\bin\mysqladmin reload %MYSQL_HOME%\bin\mysql -e="\. bfg_init.sql" BFG This script assumes either that the environment variable MYSQL_HOME has been created or that MYSQL lives in C:\MYSQL. If you've followed the instructions in Appendix B,"Getting and Installing MySQL and JDBC," this is where it will be installed. Otherwise, you'll need to create an environment variable called MYSQL_HOME with the correct path. First, the script drops the current BFG database (if it exists), re-creates it, and reloads the database privileges tables, just in case (Appendix B goes into more detail about MySQL's access control system.) Then it goes on to execute an init script. Similarly, the bfg_init file (shown in Listing 6.2) is just a list of further initializations to run. You might notice that SQL (like UNIX shell scripts) uses the # character for comments. Listing 6.2 bfg_init.sql # Initialization scripts for the BFG website \. init_customer.sql \. init_products.sql \. init_order.sql \. init_promotions.sql \. init_misc.sql \. init_data.sql The Customer Tables It's not until you get into the first of these subscripts (init_customer, Listing 6.3) that you finally start to write some real SQL. Listing 6.3 init_customer.sql # Schema creation for customer-related tables drop table if exists CUSTOMER; create table CUSTOMER (CUSTOMER_ID int not null auto_increment, EMAIL_ADDRESS char(50) not null unique, PASSWORD char(20) not null, primary key(CUSTOMER_ID), index(EMAIL_ADDRESS)); drop table if exists ADDRESS; create table ADDRESS (ADDRESS_ID int not null auto_increment, FIRST_NAME char(30) not null, LAST_NAME char(40) not null, STREET_1 char(128) not null, STREET_2 char(128), CITY char(50) not null, STATE char(2) not null, POSTAL_CODE char(10) not null, primary key(ADDRESS_ID)); drop table if exists ADDRESS_BOOK; create table ADDRESS_BOOK (CUSTOMER_KEY int references CUSTOMER, ADDRESS_KEY int references ADDRESS); drop table if exists CREDIT_CARD; create table CREDIT_CARD (CUSTOMER_KEY int references CUSTOMER, CARD_TYPE char(5) not null, CARD_NUMBER char(25) not null, CARD_OWNERNAME char(50) not null, CARD_EXPMONTH int not null, CARD_EXPYEAR int not null, ADDRESS_KEY int references ADDRESS); As discussed, the customer table itself is pretty sparse: All it holds is a unique ID for the customer, the customer's e-mail address (which is the visible key to the customer), and the password for the account. Because MySQL doesn't have sequences like Oracle's sequences, you have to use an autoincrementing int field instead. The basic mistake that developers make in this situation is to assume that they can do an insert into this table, read back MAX(CUSTOMER_KEY) , and learn the value for the newly created customer. This would work in a single-threaded application, but not in one in which several sessions might try to create a user at the same time. Thankfully, MySQL has the capability to read back the value of an autoincrementing column when an insert is executed, so you'll be able to use this method instead. The only other alternative is to put a lock on the table just before doing the insert, immediately read back the value, and then unlock the table again (you don't want to lock it too long because others might need to use it.) If you used the ADDRESS table only to store the addresses in the address book, you could just embed CUSTOMER_KEY as a foreign key in the ADDRESS table itself. But because you also reference ADDRESS in the credit card table, you need to make a cross-reference table called ADDRESS_BOOK to hold the one-to-many relationship between CUSTOMER and ADDRESS. Because only one address is associated with a given credit card, you can embed the ADDRESS ID directly in the CREDIT_CARD table. The CARD_NUMBER field is made larger than most card numbers actually are (16 digits) because some newer European cards are 20 digits, and who knows what the future may hold. It's better to waste a few bytes per record now than have to modify the entire schema later. Another interesting question to consider at this point is whether to store the credit card expiration date as a date field or as separate integer fields. By storing it as a date, you can easily check for credit card currency with the Date.before and Date.after methods, but you'd have to construct and deconstruct the dates from the fields in the forms. Because the comparisons are simple (basically, is the expiration year greater than the current year, or is the year equal and the month greater?), you are better off storing the date as discrete fields instead, as this example does. The Product Tables Now you're ready to look at the product and category tables. In the application in Listing 6.4, you're dealing with a simple one-layer hierarchy of products, so the schema is relatively simple. Listing 6.4 init_product.sql # Schema creation for product-related tables drop table if exists PRODUCT; create table PRODUCT (ISBN char(20) not null, TITLE varchar(128) not null, PRICE float not null, PUB_DATE date not null, DESCRIPTION blob, primary key(ISBN)); drop table if exists AUTHOR; create table AUTHOR (AUTHOR_ID int not null unique, AUTHOR_NAME char(128) not null, primary key(AUTHOR_ID)); drop table if exists PRODUCT_AUTHOR_XREF; create table PRODUCT_AUTHOR_XREF (PRODUCT_ISBN char(20) references PRODUCT, AUTHOR_ID int references AUTHOR); drop table if exists CATEGORY; create table CATEGORY (CATEGORY_ID int not null, CATEGORY_NAME char(30) not null, FEATURED_PRODUCT char(20) references PRODUCT, primary key(CATEGORY_ID)); drop table if exists CATEGORY_PRODUCT_XREF; create table CATEGORY_PRODUCT_XREF (CATEGORY_ID int references CATEGORY, PRODUCT_ISBN char(20) references PRODUCT); The product table itself holds the basic information captured about a book: the price, title, and so on. Because the descriptions can be longer than 255 characters, which is the limit for MySQL char strings, use a blob instead. Blobs are used in SQL to hold large chunks of data (such as images), but they can be used for large text blocks as well. You embed attributes (such as ISBN) that are uniquely associated with this product, but not the authors. The author is not directly embedded in the product for two reasons. First, there is frequently more than one author of a book. Second, making this a separate joined table, it will be easier to add search functionalities such as "show me all books by author X." The author table is joined with a simple cross-reference. No autoincrementing field is used for the author ID because this data will come from a static file, not from dynamic creation of new records during the application's execution. In other words, these records are prepopulated and the author ID can be hardwired into the file used to fill the table. You need to know the value of the ID so that you can use it to prepopulate other tables that depend on it (such as promotions). Categories are also a simple table with a cross-reference to the products that live in the category. The only item of note here is the featured product field, which is used to indicate which product should be displayed at the top of the category page. The Order Tables The order contains pointers to credit card and shipping address records stored in the CREDIT_CARD and ADDRESS tables. In addition, it contains specific information about the order total price and subtotals for shipping and tax. By the way, the table is called ORDERS rather than ORDER because ORDER is a reserved word in many SQL dialects. You need to be able to look at an order as it was on the day that the customer placed it. The ORDER_ITEM table stores just enough information to be able to display a product on an order history page or generate a bill of lading for the shipping department to fulfill the order. Listing 6.5 init_order.sql # Schema creation for order-related tables drop table if exists ORDERS; create table ORDERS (ORDER_ID int not null auto_increment, EMAIL_ADDRESS char(50), ADDRESS_KEY int references ADDRESS, CARD_KEY int references CREDIT_CARD, ORDER_DATE date, ORDER_SUBTOTAL float, ORDER_TAX float, ORDER_SHIPPING float, ORDER_TOTAL float, primary key(ORDER_ID)); drop table if exists ORDER_ITEM; create table ORDER_ITEM (ORDER_ID int references ORDERS, PRODUCT_ISBN char(20) not null, PRODUCT_TITLE varchar(128) not null, QUANTITY int not null, UNIT_PRICE float not null, TOTAL_PRICE float not null); Again, this is not difficultit has everything that the site needs to capture for the client during an order. You record the various totals in the database rather than having the application compute them on the fly later because you don't want the application to display a different total if you change the totaling logic so that a difference occurs because of rounding. The Promotion Table The promotions table defines the two promotions defined (the BOGOF and %OFF promotions). The supporting table is very simple, as Listing 6.6 shows. Listing 6.6 init_promotions.sql # Schema creation for product-related tables drop table if exists PROMOTION; create table PROMOTION (PROMO_ID int not null unique, PROMO_NAME char(30) not null, PROD_OR_CAT char(1) not null, CATEGORY_ID int references CATEGORY, PRODUCT_ISBN char(20) references PRODUCT, PROMO_TYPE char(1) not null, DISCOUNT_QUANT int not null); Again, the promotional ID is just a unique canned ID number specified in the initialization data. The promotional name is used for display purposes, such as to show the customer what promotion he has received. The PROD_OR_CAT field is either a P for a product-specific promotion or a C for a category-specific promotion. Depending on whether it is a product or a category promotion, the CATEGORY_ID or the PRODUCT_ISBN will be filled out. The promotional type is either % or B (for percent off or BOGOF). The quantity field stores the discount (either an integer percentage value such as 20 for 20%, or the number of books that it takes to qualify for the BOGOF3 for "buy 3, get 1 free," for example). You might be tempted to make PROD_OR_CAT or PROMO_TYPE Boolean fields (for example, IS_BOGOF ). The peril with this approach is that it leaves no room for expansion. You're assuming that promotions are either BOGOFs or they're not. If you add a third type of promotion later, you'd have to change your database schema to incorporate the addition. NOTE If you write SQL queries using the % character, you might get into trouble if you use a like qualifier because % is the wildcard operator in like . There's little danger of that in this case because PROMO_TYPE is a single-character field, so you'd be unlikely to use like . Try to use values that are easily recognized whenever possible, but you also could use P for percent instead.
Populating Data The init_misc script is for miscellaneous tables. Because you don't have any that you know of yet, this script is left empty. The init_data script (see Listing 6.7) is a large one; it contains all the initialization data for the application and is basically just a file full of insert statements. You begin with a series of delete statements: Listing 6.7 init_data.sql # Data population delete from CATEGORY_PRODUCT_XREF; delete from CATEGORY; delete from PRODUCT; delete from PROMOTION; insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Java 2 Micro Edition (J2ME) Application Development', '2001-11-28','672320959','The key to Java 2 Micro Edition (J2ME) Application  Development is the clear, concise explanations of the J2ME technology in relation to the existing Java platform. This book assumes proficiency with Java and presents strategies for understanding and deploying J2ME applications. The book presents numerous real-world examples, including health care and financial sector examples from the authors\' professional experience.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Oracle and Java Development', '2001-08-17','672321173','Oracle and Java Application Development describes Oracle\'s Internet Computing Platform for developing applications; outlines Key Oracle Java technologies like Enterprise Java Beans, Business Components, Java Server Pages, and Servlets for developing and deploying applications using Oracle 8i;.describes the creation of dynamic Web content with Java.; and covers database interaction with Java using Java stored procedures, JDBC, and SQLJ.', 39.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Sams Teach Yourself Wireless Java with J2ME in 21 Days', '2001-06-27','672321424','Sams Teach Yourself Wireless Java with J2ME in 21 Days begins by establishing the basic parameters of J2ME development and its uses in building wireless applications. The tutorial chapters introduce both text and graphical application development for typical wireless devices. Finally, the book presents the major types of applications that the wireless developer will build-information management, communications, games, etc. The book also introduces the basic concepts of networking wireless devices through Java. ', 39.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Wireless Java Programming with Java 2 Micro Edition', '2001-05-24','672321351','Wireless Device Programming with Java 2 Micro Edition assumes readers are motivated to build the next generation wireless application by leveraging the J2ME technology. The book provides commercial-quality code and examples primarily based on the industry-leading Motorola phone emulator.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('"Developing Java Servlets, Second Edition"', '2001-05-21','672321076','Developing Java Servlets, Second Edition, is a comprehensive, code-intensive book for professional Java developers. It explains the Java Servlet API architecture and client/server development concepts and contains detailed, professional programming techniques for building sophisticated e-commerce and database servlet applications. New topics covered in the updated edition are: JavaMail; Servlets with XML, JSP, and EJB; Pluggable web applications; Wireless servlets with WML/WMLScripts. ', 39.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('"Sams Teach Yourself Java 2 in 21 Days, Professional Reference Edition, Second Edition"', '2001-05-21','672320614','Sams Teach Yourself Java in 21 Days continues to be one of the most popular, best-selling Java tutorials on the market. It has been acclaimed for its clear and personable writing, for its extensive use of examples, and for its logical and complete organization. The Professional Reference Edition of the book includes an extra seven chapters covering advanced topics like object serialization, remote method invocation, accessibility, security, JavaBeans, JDBC, and advanced data structures - as well as a 200-page reference section detailing the most commonly used aspects of the Java language. This edition of the book has been updated and revised to cover version 1.3 of the Java 2 Standard Edition SDK, and the book\'s CD-ROM includes a fully functional Java compiler, as well as the book\'s source code and a collection of third-party Java development tools and utilities. ', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Sams Teach Yourself JavaServer Pages in 24 Hours', '2000-12-18','672320231','Sams Teach Yourself JavaServer Pages in 24 Hours guarantees to teach 24 one-hour lessons on JSP and its ties to other J2EE technologies, including Servlets and JDBC. Additionally, this book covers the comprehensive development of JSPs using tools such as BEA\'s WebLogic Server and Apache Tomcat. Each lesson builds on the previous one, allowing the reader to understand the subject of JSPs from the ground-up.', 29.95); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Sams Teach Yourself Java 2 in 24 Hours, Second Edition', '2000-11-17','672320363','Revised and updated edition of the leading Java tutorial for beginners with no previous programming experience. The book\'s short, simple one-hour chapters are easy to understand and they carefully step the reader through the fundamentals of Java programming. This edition has been updated to cover the new Java SDK version 1.3. Readers love this book -- they say it explains Java better than any other book they\'ve seen, and that it\'s very clear, well-written, and interesting to read. They even appreciate the author\'s somewhat unique sense of humor.', 24.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('XML Development with Java 2', '2000-10-18','672316536','XML Development with Java 2 provides the information and techniques a Java developer will need to integrate XML into Java-based applications. This book presents a fast-paced introduction to XML and moves quickly into the areas where XML has the biggest impact on Java Development. The book covers crucial topics such as the XML Document Object Model (DOM), Using Java and XSL to transform and format XML data, Integrating XML into JavaBeans and EJB development, and using XML with Java Servlets. The authors also cover the impact XML has on Java database access and the way XML works with the Swing classes.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('"Sams Teach Yourself JavaScript in 24 Hours, Second Edition"', '2000-10-9','672320258','Second edition updates the current best-selling book to cover the latest version, JavaScript 1.5', 24.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Java Security Handbook', '2000-09-21','672316021','This book is a comprehensive guide to Java security issues. It assumes you are an experienced Java programmer, but have little experience with creating secure applications. This book covers formulating and enacting a network security policy to protect end-users, building e-commerce and database applications that can safely exchange secure information over networks and the Internet, cryptography, digital signatures, key management, and distributed computing: CORBA, RMI, and servlets.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('"Sams Teach Yourself Java 2 in 21 Days, Second Edition"', '2000-09-20','672319586','Sams Teach Yourself Java in 21 Days, Second Edition is known for its clear and personable writing, its extensive use of examples, and its logical step-by-step organization. This new edition maintains and improves upon all these qualities, while updating and revising the material to cover the latest developments in Java and the way the language is used today.', 29.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('"JavaScript Unleashed, Third Edition"', '2000-06-23','067231763X','JavaScript Unleashed, Third Edition serves as a reference to the JavaScript language for the high- end programmer as well as a guide for developing JavaScript applications from the ground up. The topics most important to the intermediate to advanced JavaScript programmer are covered, including Web security, integrating JavaScript with Java, and forms and data validation. Other topics include creating special effects with JavaScript, controlling layers with JavaScript, DHTML and Cascading Style Sheets, and using lookup tables in JavaScript. Some of the new topics covered are Internet Explorer 5, Active Server Pages, Netscape Plug-in autoinstalls and applets digital signature verification, and content layering.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Pure JSP: Java Server Pages', '2000-06-8','672319020','Pure JSP gives a very concise conceptual overview of the JavaServer Pages technology and its related components. Once you have a firm foundation with the JSP technology, related topics such as JavaBeans, JDBC and Servlets are covered at a very high level. The book moves on to explain a large number of JSP techniques, which were determined by studying problems faced by JSP users in the professional world. The final section covers the more technical aspects of the JSP technology. Topics include related API\'s, server configuration, and charts and diagrams related to developing JSP applications.', 34.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Building Java Enterprise Systems with J2EE', '2000-06-7','672317958','The practical angle of Building Java Enterprise Systems with J2EE provides the conceptual background and wealth of code examples needed to actually assemble systems in a useful manner with the J2EE technologies. Furthermore, this book demonstrates how the technologies complement and build on top of one another via evolution of a cohesive and real sample application. You can use this book to learn, develop, and design your custom applications immediately.', 59.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('JavaBeans Unleashed', '1999-12-22','067231424X','JavaBeans Unleashed is a practical, professional, and comprehensive guide to JavaBeans. It assumes you are an experienced Java programmer but have little experience developing network and client/server applications. This book also contains an introduction to Enterprise JavaBeans, a new Java specification from Sun. JavaBeans works with the most popular distributed object protocols, so CORBA (the distributed object leading protocol from the Object Management Group), Java IDL ( Interface Definition Language allows Java apps access to CORBA), and JNDI (Java Naming and Directory Interface which allows Java applications to access files on any network server) are also covered.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Pure Java 2', '1999-12-22','672316544','Pure Java 2 is a substantial and focused reference for professional Java programmers. This book begins with an accelerated introduction to Java 2 so that you can quickly understand the new concepts and begin developing your own applications. Professional programmers prefer to learn by examining code, so Pure Java 2 also contains hundreds of programming techniques, complete with well-commented code examples that you can immediately use in your own Java programs.', 24.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Sams Teach Yourself Java 2 Online in Web Time', '1999-09-1','672316684','Sams Teach Yourself Java 2 Online in Web Time helps the first-time programmer or the programmer new to Java to learn Java and its APIs. Written in a warm and familiar style, Teach Yourself Java in WebTime requires no previous programming experience. It incorporates the proven instructional techniques of the Teach Yourself series, including end-of-section quizzes and programming exercises that allow you to review and expand upon the concepts and skills presented in that chapter. The learning experience is further enhanced with an instructional Web site.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Java GUI Development', '1999-08-25','672315467','Java GUI Development covers the Java 2 AWT, JFC, and Swing Toolkit technologies for GUI programming. It provides professional developers and software engineers with 1) a clear understanding of the conceptual framework behind Java 2 GUI tools, 2) descriptions of Java GUI idioms, and 3) practical programming techniques proven to work with these tools. This approach enables developers to solve difficult GUI programming tasks faster, write tighter and faster code, and implement more sophisticated GUI designs. ', 34.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Java Thread Programming', '1999-08-20','672315858','Java Thread Programming shows you how to take full advantage of Java\'s thread facilities: when to use threads to increase your program\'s efficiency, how to use them effectively, and how to avoid common mistakes. There is thorough coverage of the Thread API, ThreadGroup classes, the Runnable interface, and the synchronized operator. Extensive, complete, code examples show programmers the details of creating and managing threads in real-world applications.', 34.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Pure JavaScript', '1999-08-19','672315475','Pure JavaScript is a substantial and focused reference for experienced Web developers. This book begins with an accelerated introduction to the newest features of JavaScript so that experienced Web developers can quickly understand the concepts of JavaScript and begin developing their own JavaScript solutions immediately. Pure JavaScript also contains insightful programming techniques, complete with well-commented code examples that you can immediately use in your own JavaScripts. This book contains the most complete, easily accessible JavaScript object reference with syntax, definitions, and examples of well-commented code for each entry.', 34.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Developing Java Servlets', '1999-06-21','672316005','Developing Java Servlets is a comprehensive, code-intensive book for professional Java developers. It explains the Java Servlet API architecture and client-server development concepts, and contains detailed, professional programming techniques for building sophisticated e-commerce and database servlet applications. This book explains HTTP, MIME, server-side includes, and other web-based client- server technologies that developers need to understand to build any servlet application. In addition, the book covers JDBC, RMI, CORBA, and other object technologies to the degree are needed to build sophisticated Java servlets.', 29.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Java 2 for Professional Developers', '1999-06-18','672316978','Java 2 for Professional Developers is a practical, code-intensive approach for readers who need to use Java for professional software development. This book teaches Java programming concepts and techniques within the context of professional, object- oriented, software analysis and design. Apply these concepts, idioms, and real-world applications to your own programs to become a more efficient and successful Java developer. In addition to the basic Java language, this book covers JFC, AWT, security, threads, sockets, JARs, JavaBeans, developing packages, and testing and debugging.', 34.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Java 2 Platform Unleashed', '1999-04-9','672316315','Java 2 Platform Unleashed is completely revised for Java 2. This complete reference covers all the core APIs of the Java 2 platform as well as higher-level topics that experienced Java programmers need to know. The book includes thousands of lines of code to demonstrate the sophisticated programming techniques that experienced Java programmers demand. Written for experienced Java programmers.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Sams Teach Yourself Java 2 in 21 Days', '1999-03-23','672316382','Sams Teach Yourself Java in 21 Days continues to be the most popular, best-selling Java tutorial on the market. It has been acclaimed for its clear and personable writing, for its extensive use of examples, and for its logical and complete organization. The third edition of the book maintains and improves upon all these qualities while updating the material to cover the latest developments in the Java language - such as using Java Foundation Classes, Java 2D Classes, and JavaBeans.', 29. 99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('The Official VisiBroker for Java Handbook', '1999-03-23','672314517','The Official VisiBroker for Java Handbook provides a comprehensive guide to learning how to effectively program with the VisiBroker for Java ( VBJ) development tool. This book focuses exclusively on how to use the tool and all of its features and APIs, as well as how to execute both routine and sophisticated tasks. It serves as the most comprehensive collection of VBJ code samples available today and a hands-on reference tool that starts with the VisiBroker for Java basics and concludes by showing you how to make the most difficult features easy to use. High-level issues are discussed, but only in the context of how the tool should be deployed or implemented.', 39.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Java Distributed Objects', '1998-12-22','672315378','This book is a comprehensive guide to Java distributed computing. It assumes the reader is an experienced Java programming, but has little experience with network programming and distributed objects. This book covers networking, distributed computing architectures, advanced Java facilities, security, data management, and specific distributed computing techniques including sockets, Remote Method Invocation (RMI), Java servlets, Microsoft\'s Distributed Component Model (DCOM), and the Common Object Request Broker Architecture (CORBA).This book covers all these protocols, gives advice on when to use each protocol, and demonstrates how they work (or don\'t work) together.', 49.99); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Genuine Nagahyde PDA Carrying Case', '1998-12-22','PALMCASE','A genuine Nagahyde Palm-compatible PDA case, just the thing to keep your precious PDA safe from the elements. Made from the skin of the rare endangered Naga, this case is sure to make you a hit at your next trade show.', 25.00); insert into PRODUCT (TITLE, PUB_DATE, ISBN, DESCRIPTION, PRICE) values ('Jamaican Coffee', '1998-12-22','COFFEECAN','When a late night of engineering has your eyes defocused and you just can\'t grind out that last method, grind some of these imported Jamaican Blue Mountain coffee beans instead. The caffeine will be just the thing to keep you going.', 12.00); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (1, 'Ben Forta'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (2, 'Bill Mccarty'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (3, 'Bulusu Lakshman'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (4, 'Charlton Ting'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (5, 'Donald Doherty'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (6, 'James Goodwill'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (7, 'Jamie Jaworski'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (8, 'Jason Gilliam'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (9, 'Jose Annunziato'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (10, 'Kenneth Litwak'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (11, 'Laura Lemay'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (12, 'Michael Kroll'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (13, 'Michael McCaffery'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (14, 'Michael Moncur'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (15, 'Michael Morrison'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (16, 'Mike Morgan'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (17, 'Paul Hyde'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (18, 'Paul Perrone'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (19, 'R. Allen Wyke'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (20, 'Rogers Cadenhead'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (21, 'Stefan Haustein'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (22, 'Stephanie Fesler'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (23, 'Stephen Gilbert'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (24, 'Vartan Piroumian'); insert into AUTHOR (AUTHOR_ID, AUTHOR_NAME) values (25, 'Yu Feng'); insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672320959', author_id from author where author_name='Stefan Haustein'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672320959', author_id from author where author_name='Michael Kroll'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672321173', author_id from author where author_name='Bulusu Lakshman'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672321416', author_id from author where author_name='R. Allen Wyke'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672321424', author_id from author where author_name='Michael Morrison'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672321351', author_id from author where author_name='Yu Feng'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672321076', author_id from author where author_name='James Goodwill'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672320614', author_id from author where author_name='Laura Lemay'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672320231', author_id from author where author_name='Stephanie Fesler'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672320231', author_id from author where author_name='Jose Annunziato'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672320363', author_id from author where author_name='Rogers Cadenhead'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316536', author_id from author where author_name='Michael Daconta'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672320258', author_id from author where author_name='Michael Moncur'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316021', author_id from author where author_name='Jamie Jaworski'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672319586', author_id from author where author_name='Rogers Cadenhead'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672319586', author_id from author where author_name='Laura Lemay'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '067231763X', author_id from author where author_name='R. Allen Wyke'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672319020', author_id from author where author_name='James Goodwill'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672317958', author_id from author where author_name='Paul Perrone'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '067231424X', author_id from author where author_name='Donald Doherty'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316544', author_id from author where author_name='Kenneth Litwak'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316684', author_id from author where author_name='Bill Mccarty'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316684', author_id from author where author_name='Stephen Gilbert'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672315467', author_id from author where author_name='Vartan Piroumian'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672315858', author_id from author where author_name='Paul Hyde'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672315475', author_id from author where author_name='Charlton Ting'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672315475', author_id from author where author_name='Jason Gilliam'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672315475', author_id from author where author_name='R. Allen Wyke'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316005', author_id from author where author_name='James Goodwill'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316978', author_id from author where author_name='Mike Morgan'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316315', author_id from author where author_name='Jamie Jaworski'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672316382', author_id from author where author_name='Laura Lemay'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672314517', author_id from author where author_name='Michael McCaffery'; insert into PRODUCT_AUTHOR_XREF (PRODUCT_ISBN, AUTHOR_ID) select '672315378', author_id from author where author_name='Bill Mccarty'; insert into CATEGORY (CATEGORY_ID, CATEGORY_NAME, FEATURED_PRODUCT) values (1, 'Java', '672321076'); insert into CATEGORY (CATEGORY_ID, CATEGORY_NAME, FEATURED_PRODUCT) values (2, 'J2ME', '672321351'); insert into CATEGORY (CATEGORY_ID, CATEGORY_NAME, FEATURED_PRODUCT) values (3, 'J2EE', '672317958'); insert into CATEGORY (CATEGORY_ID, CATEGORY_NAME, FEATURED_PRODUCT) values (4, 'JDBC', '672321173'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672320959'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672321173'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672321424'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672321351'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672321076'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672320614'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672320231'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672320363'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316536'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672320258'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316021'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672319586'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '067231763X'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672319020'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672317958'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '067231424X'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316544'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316684'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672315467'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672315858'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672315475'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316005'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316978'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316315'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672316382'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672314517'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (1, '672315378'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (2, '672320959'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (2, '672321424'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (2, '672321351'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (3, '672320231'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (3, '672317958'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (4, '672321173'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (4, '672320614'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (4, '672320231'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (4, '672319020'); insert into CATEGORY_PRODUCT_XREF (CATEGORY_ID, PRODUCT_ISBN) values (4, '672316005'); Even though the init_bfg script re-creates all the tables, it begins by deleting all the data in the tables so that the script can be run separately to repopulate the product and promotion tables without losing customer and order data (an important thing on a live site). The ordering of these statements is important because of cross-table dependencies. If you tried to drop products before you dropped the category cross-reference or the category table, some databases (although not MySQL) would complain that some foreign key constraints were being broken. You could also solve this by using a cascading delete, on databases such as Oracle that support it. Next, you populate the product table. Again, ordering is important. You need to populate the products before you refer to the products in any cross-references or foreign keys. Other than having to quote any single-quote characters in the description with a backslash, the product table is about as straightforward as life gets. Equally so is the author tableit simply contains the data for each author. For the product-author cross-reference, things get a little fancier because the data that was imported into MySQL was from a tab-delimited spreadsheet file. Instead of hand-translating the author names into author IDs, a lookup was used on the newly created author table. Next you come to the category list and the category cross-reference. The category table, like the author and product tables, simply describes each category. The table entries also reference a product, which are highlighted in the category display page under JSP. Currently, there are no promotions to populate; they'll be added in Chapter 12, "Handling Complex Business Rules and Third-Party Integration."  |
