Java programs, applets, servlets, and Java server pages (.jsp) can access MySQL databases using MySQL Connector/J 3.0.6 (formerly MM.MySQL) --MySQL AB's JDBC driver for MySQL. The latest stable driver can be obtained from MySQL.com. In order to allow jsp and servlets to run on your server, you will need to install a servlet engine. This page has instructions on how to configuring Apache 1.3.27 to use mod_perl-1.27, PHP-4.3.1, mod_ssl-2.8.11, OpenSSL-0.9.7a, and the Tomcat 4.1.24 servlet engine.
A MySQL database is needed for connectivity testing and retrieving data. The commands to create the database, create the database user, and insert the data are not included, but the following shows how to log into MySQL, displays the specifics for the table, and displays the test data, which will be retrieved by a Java program and a Java servlet below.
The command to log into MySQL uses several command line options: -u to specify the username (java_user), -h for the hostname (green), -p to have a password prompt, and specifies which database to use (java_test).
# /usr/local/bin/mysql -u java_user -h green -p java_test
Enter password:
[MySQL Greeting/Header Cut]
mysql> DESCRIBE java_table;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| item1 | int(10) unsigned | YES | | NULL | |
| item2 | char(20) | YES | | NULL | |
| item3 | char(10) | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM java_table;
+-------+-------------+------------+
| item1 | item2 | item3 |
+-------+-------------+------------+
| 1233 | Hello World | Item3 text |
| 4545 | DB Works | Item3 text |
+-------+-------------+------------+
2 rows in set (0.00 sec)
mysql>
To install the driver, download the driver, untar and decompress it, and install the driver to $JAVA_HOME/jre/lib/ext.
# gzip -cd mysql-connector-java-3.0.7-stable.tar.gz | tar xvf - # cd mysql-connector-java-3.0.7-stable/ # echo $JAVA_HOME /usr/java/j2sdk1.4.1 # cp mysql-connector-java-3.0.7-stable-bin.jar $JAVA_HOME/jre/lib/ext
The following is an example Java program to test JDBC connectivity with a MySQL database. Save the following Java code in a file called JDBCtest.java. Replace the variables, such as $HOST and $DB_NAME, with valid values.
import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;
class JDBCtest {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
ResultSetMetaData rmd = null;
String query = null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
System.out.println("Driver Registration Successful.");
} catch (InstantiationException ie){
System.out.println("Class Instantiation Exception: " + ie);
} catch (ClassNotFoundException cnf){
System.out.println("Class Not Found Exception: " + cnf);
} catch (IllegalAccessException iae){
System.out.println("Illegal Access Exception: " + iae);
}
try {
conn = DriverManager.getConnection("jdbc:mysql://$HOST/$DB_NAME
?user=$USERNAME&password=$PASSWORD");
System.out.println("Connection to MySQL Database Successful");
} catch (SQLException sqe1){
System.out.println("Caught SQL Exception: " + sqe1);
}
try {
st = conn.createStatement();
query = "SELECT * FROM java_table" ;
rs = st.executeQuery(query);
rmd = rs.getMetaData();
System.out.println("Meta (# of columns): "+rmd.getColumnCount());
while(rs.next()){
int my_int = rs.getInt("item1");
System.out.println("Item1 = " + my_int );
String str1 = rs.getString("item2");
System.out.println("Item2 = " + str1 );
String str2 = rs.getString("item3");
System.out.println("Item3 = " + str2 );
}
} catch (SQLException sqe2){
System.out.println("Caught SQL Exception: " + sqe2);
}
}//end main
}//end class
Next, compile the program.
# javac JDBCtest.java
Finally, execute the program.
# java JDBCtest Driver Registration Successful. Connection to MySQL Database Successful Meta (# of columns): 3 Item1 = 1233 Item2 = Hello World Item3 = Item3 text Item1 = 4545 Item2 = DB Works Item3 = Item3 text #
As you can see, the result of this program is to select all the data from the table and display it.
The following is an example of a simple Java servlet to test JDBC connectivity with a MySQL database. Save the following Java code in a file called JDBC_servlet.java. Replace the variables, such as $HOST and $DB_NAME, with valid values. This program will only work if you have a servlet engine configured and running -Sun ONE Studio (formerly Forte) has a Tomcat servlet engine included with it and can be used to test this servlet.
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.Date;
public class JDBC_servlet extends HttpServlet {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
ResultSetMetaData rmd = null;
String query = null;
/** Initializes the servlet. */
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
private void createConnection(PrintWriter out){
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
out.println("Driver Registration Successful<br>");
} catch (InstantiationException ie){
out.println("class instantiation exception: " + ie);
} catch (ClassNotFoundException nf){
out.println("class not found exception: " + nf);
} catch (IllegalAccessException iae){
out.println("illegal access exception: " + iae);
}
try {
conn = DriverManager.getConnection("jdbc:mysql://$HOST/"+
"$DB_NAME?user=$USERNAME&password=$PASSWORD");
out.println("Connection Successful<br>");
} catch (SQLException sq1){
out.println("Caught SQL exception "+sq1);
}
}// End createConnection()
private void runQuery(PrintWriter out) throws SQLException {
st = conn.createStatement();
query = "SELECT * FROM java_table" ;
rs = st.executeQuery(query);
rmd = rs.getMetaData();
out.println("<br>MetaData (# of Columns): "+rmd.getColumnCount()+"<br>");
while(rs.next()){
int int1 = rs.getInt("item1");
out.println("Item1 = " +int1 + "<br>");
String str1 = rs.getString("item2");
out.println("Item2 = " +str1 + "<br>");
String str2 = rs.getString("item3");
out.println("Item3 = " +str2 + "<br><br>");
}
}//end runQuery()
/** Destroys the servlet. */
public void destroy() { }
/** Processes requests for both HTTP GET and POST methods.*/
protected void processRequest(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
resp.setContentType("text/html");
PrintWriter out = resp.getWriter();
out.println("<html>");
out.println("<head>");
out.println("<title>Servlet Conection Example</title>");
out.println("<style type=\"text/css\">");
out.println("h1{text-align:center; background:#dddddd;color:#000000}");
out.println("body{font-family:sans-serif, arial; font-weight:normal}");
out.println("</style>");
out.println("</head>");
out.println("<body>");
out.println("<h1>Test Database Connectivity</h1>");
createConnection( out );
try {
runQuery(out);
}catch (SQLException sqe){
out.println("Caught SQLException from runQuery(): " + sqe);
}
out.println("<br><br><hr>" + new Date().toString() );
out.println("</body>");
out.println("</html>");
out.close();
} //end processRequest()
/** Handles the HTTP GET method. */
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
processRequest(req, resp);
} //end doGet()
/** Handles the HTTP POST method. */
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, java.io.IOException {
processRequest(req, resp);
} //end doPost()
/** Returns a short description of the servlet. */
public String getServletInfo() {
return "Short description";
} //end getServletInfo()
}//end class JDBC_servlet
After the source is compiled and has no errors, copy the .java and .class files to the servlet directory for your servlet engine. If you are uncertain as to this location, consult the documentation for your servlet engine as there is probably a defalut directory for servlets. For the Tomcat server configured on this page, the directory would be ${TOMCAT_HOME}/webapps/examples/WEB-INF/classes with the $TOMCAT_HOME variable being equal to where you installed the Tomcat binary.
If the above configuration is used, the resulting html page produced from this servlet could be accessed at http://localhost/examples/servlet/JDBC_servlet, and will look like the output on this page (this page is the saved output and is not running through a servlet engine on this server).