Creating a JSP JDBC data source

JSP uses a database interface similar to ODBC to access databases: JDBC. A JDBC driver for the particular database that you wish to use must be part of your site. You can usually obtain the driver from the database vendor. The driver is placed in the WEB-INF/lib folder of your site. After adding the driver to the site and uploading, the server may need to be restarted (the Tomcat server does need to be restarted). This allows the driver to be registered with the Web server.

The MySQL driver for JDBC can be obtained from www.sourceforge.net/projects/mmmysql.

Once the driver has been installed in the site, you can set up a data source for a database.

To set up a JDBC data source in your JSP site:

Note: You need to have your site set up as a dynamic site. (see Making a Web site dynamic), and the JDBC driver must be installed in the site.

1. With the site window active, select Site Settings > Dynamic Content pane.
2. In the Data Sources area, click New.
3. Select JDBC Database, and click Add Data Source.
4. Now fill in the fields in the JDBC Data Source editor.
• Data Source Name: Choose a name for this data source. The database name that it refers to is usually a good choice. This must be a legal filename on the Web server and the machine where GoLive is running.
• Driver: Enter the driver name. Consult the documentation for the database JDBC driver. This is a Java class name. If you are using the mm.mysql driver, the driver name is org.gjt.mm.mysql.Driver.
• DB URL: Enter the jdbc URL reference to the database. Consult the documentation for the database JDBC driver. This is generally a string of the form jdbc:database_kind:// db_server_name:port/database_name. For example, for a MySQL database running on the same machine as the Web server, to reference a database called lunch, the DB URL would be jdbc:mysql://localhost:3306/lunch. In this example, 3306 is the default port used by MySQL for client connections. Other database systems would have a slightly different format.
• User and Password: If a specific identity is required to access the database, enter the username and password here.

Note: The password is stored in clear text in the data source file. Be sure that HTTP Read access is not allowed to the config/datasources folder to restrict visibility of this file. (See Relocating config/datasources folder.)

5. A simple connection pooling mechanism is available for JDBC database connections from GoLive sites. Pool.nstart and Pool.nmax control the initial and maximum number of connections in the pool. Changing these values can impact performance, but the defaults are usually satisfactory for development purposes or for a low to medium traffic site.
6. Press the Test button to test the data source. Test will make a call to the Web server and try to connect to the database. If successful, it will return and display an XML representation of the list of tables that were discovered in the database. If an error occurs, some error information will be displayed if possible. Unfortunately, depending on the database system and driver, the content of the error messages may be very obscure.