JSP Database Basics
JSP can be used to display information directly from a database residing on the server, without the database naming conventions being available to the viewer. It also allows ease and speed in accessing the database information.

How To access MySQL with Java DataBase Connectivity(JDBC) on a JSP server:

1. Add a Java Application, in example, 'JDBCtest' using the Control Panel's "JSP Manager" icon. The application will create and be placed in the following path: /home/www/webapps/JDBCtest/

2. Download the MySQL JDBC driver file from http://www.mysql.com/downloads/download.php?file=Downloads/Contrib/
Note: The standard new name is MySQL Connector/J (Formerly MM.MySQL - Mark Matthews JDBC Driver for MySQL)

The file name is "mysql-connector-java-x.x.x-bin.jar"

(Place it in the "lib" directory at /home/$username/www/webapps/JDBCtest/WEB-INF/lib)

3. Create a file, in example,"database.jsp", the location will be /home/www/webapps/JDBCtest/database.jsp

4. Copy and paste the following source code for "database.jsp".

Please substitute the values below for: $dbname, $dbusername, $dbpassword and $TableName.

---------------------DATABASE.JSP SOURCE CODE------------------------

<%

//Load the MySql Driver.
Class.forName("org.gjt.mm.mysql.Driver");

//Make the Connection to the database.

//Replace IP, Name, Username, Password to match your information.
java.sql.Connection connection=java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/"dbname","$dbusername, "$dbpassword");

//Get SQL Statement.
//Replace TableName to your table's name.
java.sql.ResultSet rs=connection.createStatement().executeQuery("select * from $TableName");

//Process results.
//Make sure your table has at least 1 column. out.println("col count is "+rs.getMetaData().getColumnCount());

//Print the Results.
//Just get the first column's name of the table
String colName=rs.getMetaData().getColumnLabel(1);
out.println("col label is "+colName);

//For each row, get the first column and print it out
while(rs.next()){
Object o=rs.getObject(colName);
out.println(colName+" "+o);
}
%>

--------------------END OF SOURCE CODE-------------------------


5 Create a database, using your Control Panel's "MySQL" icon.

6. Add a table to your database.

7. This step is the only one for which you need our help. Your MySQL database has to be enabled for "remote access". All you need do is request "ODBC Remote Access" for your database. We'll send you a confirmation email when that process is complete, and you can proceed to the next step.

8. You're done! Call the URL: http://$IP/webapps/JDBCtest/database.jsp