# Step 2: Define the Database Control

In this step you will create the database control VisitDB, which runs queries against a database table containing information about visitors and number of prior visits. A database control queries a database table to store, find, and update information.

The tasks in this step are:

- To Create a Package
- To Create a Database Control
- To Define Methods
- Build the Database Control

To Create a Package

All Java controls (and all Java classes) must be part of a Java package.

1. Right-click the MyControlProject folder in the Application tab, and select New-->Folder.
2. Enter the folder name hello.
3. Click OK.

To Create a Database Control

1. Right-click the hello folder in the Application tab, and select New-->Java Control.
2. In the New Java Control dialog, select Database and enter the file name VisitDB.
3. Click Next.
4. In STEP 2, make sure that cgSampleDataSource is selected. If this is not the case, click the Browse button to select it. If this data source is not given as an option, you selected the wrong domain in the previous step when you created the application.
5. Click Create.

The database control opens in Design View. The Application tab should now look like the picture on the right.

The top-level folder GettingStarted_Control is the containing folder for the entire application. All of the source files for your application exist in this folder.

The folder MyControlProject is the control project folder. An application can contain any number of project folders and there are many different kinds of project folders, including Web projects, Web Services projects, Schema projects, and so forth.

The Modules folder is for the storage of stand-alone applications (packaged as WAR and JAR files) that can deployed parallel with your web service, if you wish.

The Libraries folder is for the storage of resources that are to be used across multiple projects, provided the resources are packaged as JAR files. For example, if you had a control or an EJB that you wanted to re-use across all your projects, you would store it in the Libraries folder.

The Security Roles folder lets you define security roles and test users for your web application. You can test the security design of your web application by logging in as a test user.

To Define Methods

Next you will define the methods of the database control that, when invoked, execute a SQL query. A database control method consists of two parts, namely the method signature and the SQL query that is executed by the method. The first method you are going to create is called createTable, and it executes the SQL query that will create the database table VISITORS_GS_CONTROL used by the database control.

1. Right-click VisitDB in design view, and select Add Method. A method newMethod1 appears.
2. Rename this method createTable. If you step off the method prematurely, right-click the method and select Rename.
3. Right-click the method and select Edit SQL. The Edit SQL and Interface dialog appears.
4. In the SQL field, enter the following SQL query expression:

       CREATE TABLE VISITORS_GS_CONTROL (
       	VISITOR_NAME VARCHAR(40),
       	VISITS INT
       	)

   The dialog should now look like this
5. Click OK.

In the preceding steps you learned how to add a method to a database control. Next you are going to add three additional methods in the same manner. These methods are:

- getVisits. This method takes the visitor's name as an argument and returns the number of previous visits by this visitor
- insertVisitor. This method takes the visitor's name and adds the name to the database
- updateVisits. This method takes the visitor's name and a number of visits as arguments and updates the database to store the number of visits for this visitor

1. Right-click VisitDB in design view, and select Add Method. A method newMethod1 appears. Rename this method getVisits.
2. Right-click the getVisits method and select Edit SQL. In the SQL field of the Edit SQL and Interface dialog, enter the following SQL query expression:

       SELECT VISITS FROM VISITORS_GS_CONTROL WHERE VISITOR_NAME = {name}

   In the Java field of the Edit SQL and Interface dialog, change the signature of the method to:

       int getVisits(String name)

   Click OK.
3. Right-click VisitDB in design view, and select Add Method. A method newMethod1 appears. Rename this method insertVisitor.
4. Right-click the insertVisitor method and select Edit SQL. In the SQL field of the Edit SQL and Interface dialog, enter the following SQL query expression:

       INSERT INTO VISITORS_GS_CONTROL (VISITOR_NAME, VISITS) VALUES ({name}, 1)

   In the Java field of the Edit SQL and Interface dialog, change the signature of the method to:

       int insertVisitor(String name)

   Click OK.
5. Right-click VisitDB in design view, and select Add Method. A method newMethod1 appears. Rename this method updateVisits.
6. Right-click the updateVisits method and select Edit SQL. In the SQL field of the Edit SQL and Interface dialog, enter the following SQL query expression:

       UPDATE VISITORS_GS_CONTROL SET VISITS = {visits} WHERE VISITOR_NAME = {name}

   In the Java field of the Edit SQL and Interface dialog, change the signature of the method to:

       int updateVisits(String name, int visits)

   Click OK.
7. Save your results.

As you might have noticed above, arguments in the method are passed to the SQL query, and the results returned by the query are in turn returned by the method. For example, the method getVisits takes a String name as an argument. This name is passed to the SQL query, as shown below in bold:

    SELECT VISITS FROM VISITORS_GS_CONTROL WHERE VISITOR_NAME = {name}

This query returns a number, which is returned as an int value by the method.

Note. The insertVisitor and updateVisits methods return the values 0 and 1, indicating whether the insertion/updating of a record failed or was successful. In this tutorial you will not explicitly test the returned values to determine success or failure.

The VisitDB control should now look like this in Design View:

Build the Database Control

Now let's build the database control to make sure it is defined correctly.

1. Locate the Build window in the IDE. If the Build window is not there, select View-->Windows-->Build.
2. In the Application pane, right-click MyControlProject, and select Build MyControlProject.
3. Monitor the Build window and verify that the build completes correctly
4. Open the Libraries folder in the Application pane, and notice that the file MyControlProject.jar has been created.

If you encounter build errors, verify that the various SQL queries and Java methods have been created correctly.

Related Topics

Database Control

Click one of the following arrows to navigate through the tutorial: