| | Apache Derby is a transactional, embeddable, pure-Java relational database, derived from the original Cloudscape codebase. Derby is lightweight enough to be embedded directly into Java applications and/or accessed remotely by multiple users connected to Derby's network server. | The all-Java open-source database Derby is no exception, so you'll have to make sure it will not be a bottleneck to your application. Although you can find comprehensive material on this topic among Derby's manuals, I would like to focus on certain issues in more detail and give some examples from my own experience. I will focus on application performances related to selecting data from large tables. | This article is the first in a new series, "Developing with Apache Derby -- Hitting the Trifecta," which is devoted to exploring the software technology developed by the Apache Derby project. The software released by the Apache Derby project is an open source database system based on technology donated to the Apache Software Foundation by IBM. The Apache Derby database software is written in the Java? programming language, so it's highly portable but still provides remarkable performance in a small package. | The previous article in this series, Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 3: Running scripts and inserting data, ended by running a script that inserted ten rows and then displayed these rows for validation. That article didn't discuss how the rows were selected for display because it focused on inserting data into an Apache Derby database. The topic of this article is the selection and extraction of data from an Apache Derby database. But before you can perform a database query, you must create a database that contains several tables populated with relevant data. | In the previous article in this series, you learned how to move through the rows of a Java ResultSet by using the next() method. This technique mirrors what happens when you execute an SQL query within the ij tool that is included with the Apache Derby database. In both cases, this functionality is implemented within the Derby database (or any other SQL-compliant database) by a cursor. A cursor is a database structure that allows you to iterate through the results of an SQL query. | Now that you're familiar with the basics of JDBC, you can begin to learn how to connect to an embedded Apache Derby database using the Java programming language. First, however, you must have a working installation of the Apache Derby database software, as discussed in the first article in this series. If you haven't already performed this crucial step, revisit the first article, and download and install the Derby software. After you have the Derby database software installed, you can use the sample code available with this article to connect to a Derby database, as shown in Listing 1. | In the previous article in this series, "Database development with Apache Derby, Part 2: Schemas" (developerWorks, April 2006), you executed SQL commands directly at the prompt of the ij tool. By using the ij tool, you can easily experiment with different SQL or Derby commands. Often, however, you'll need to execute multiple, complex commands. To simplify debugging a set of complex SQL commands, it's generally easier to write them in a text file and then execute the commands in the text file all at once. This operation is known as running a SQL script, and you can do so easily by using Apache Derby. By placing SQL commands in a script file, you gain the additional benefit of being able to execute the commands as many times as necessary. | | The previous article in this series, "Java database development with Apache Derby, Part 2" (developerWorks, January 2007), showed you how to use the Java Statement object to execute an SQL SELECT query on an Apache Derby database. A query, by design, returns the set of rows that satisfy the query. As a result, you use the Statement object's executeQuery method to perform the query; this method returns the set of rows as a Java ResultSet object. | This two-part tutorial series demonstrates how to create a simple wiki system by embedding Apache Derby in a Web application server. Part 1 gave you an overview of the basic system and regular expressions. This tutorial shows you how to use Java? functions, stored procedures, and triggers within the Derby database and outlines the advantages of using these features, including reducing the coupling between database and middle-tier code, minimizing the number of transactions opened, and increasing performance by reducing network traffic. | Get the basic skills you need to use Apache Derby in Java? 2 Platform, Enterprise Edition (J2EE) environments where Derby is embedded as a database, such as in Apache Geronimo. This tutorial demonstrates how to embed Derby within Geronimo and how to use Derby and Geronimo to deploy a Web-based e-commerce application. It also helps you become familiar with Derby's system tools, a set of utilities that make application development with Derby easy and straightforward. | You can quickly and easily persist simple data to Apache Derby by taking advantage of Java? dynamic proxies. In this tutorial, write a persistence API that you can integrate into any database-powered Java application. You use the API to store an application's configuration information and to notify the application when configuration items change. | As you can see, the first step is to create a new work directory and then decompress the sample code file into this new directory. The compressed sample code file includes five files: four Java source code files for the demo application, and a manifest.txt file, whose contents, as shown in Listing 5, consist of two lines that contain a name-value pair. You use this manifest file to build an executable JAR file for the demo application. The first name-value pair specifies the name of the class that is the primary application driver, which is DemoApp in this case. The second name-value pair specifies where to find the Apache Derby database Java classes, which in embedded mode are contained entirely in the derby.jar file. | Passing the ACID test is nontrivial, and many simple databases fall short. For critical e-business or Web-based applications, passing the ACID test is a must. This is one of the reasons so many companies and individuals utilize enterprise-level database systems, such as IBM DB2® Universal Database, Oracle 10g, or Microsoft® SQL Server. These databases are fully compliant with the ACID test. Compared to these database systems, the Apache Derby database may seem like the forgotten stepchild. However, Apache Derby is fully compliant with the ACID test, and you can use it with confidence to develop Web-based database applications. In addition, if you begin using Apache Derby, and your application (or business) grows, you can migrate your database application to an enterprise-class database system like DB2 with minimal difficulty. | As shown in Listing 1, you should first create a new working directory and then extract the source code file that is included with this article (see the Downloads section at the end of this article). The next step is to create an Apache Derby database that is properly initialized, which is easily done by running an SQL script from the Apache Derby ij tool. Finally, compile the three included Java source code files. Although the results aren't explicitly shown (because some of them generate a lengthy output), you can execute each of these Java applications, for example, by entering java FirstQuery at the command prompt. The rest of this article presents the source code for each of these three examples. | The easiest way to interact with a Derby database is to use the ij tool, which was described in the first article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Introduction to Apache Derby" (developerWorks, February 2006). If you follow along with the actions shown in Listing 2, you first create a new database named test. You may get a warning message when you issue the connect statement if the test database already exists. This warning can safely be ignored. Next, you implicitly create a new schema named bigdog and explicitly create two new tables -- products and vendors -- that are stored inside the bigdog schema. The creation of the schema is implicit because you don't issue a CREATE SCHEMA statement. | In a previous article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Java database development with Apache Derby, Part 3: Modifying data" (developerWorks, February 2007), you learned about the JDBC PreparedStatement object. By using a PreparedStatement, you can dynamically modify your SQL statement at run time. With this technique, you can insert large quantities of information by reusing the same SQL statement, or you can selectively query, delete, or update rows in a database by changing the value of a parameter. | From a database perspective, this type of data is called unstructured. You might be surprised to learn that a relational database such as Derby can store and retrieve such data. Unstructured data exists in big chunks, and its constituent elements are essentially of little use in isolation. Another type of unstructured data is a list of the applications installed on a given platform. The management of such unstructured data is the topic of this article. | All of this is an introduction to this article: how to embed Derby in a web browser. Why is this important? Well, integrating a database inside a browser is a big step in the direction of an integrated computing environment. |
|