This page contains an archived post to the Java Answers Forum made prior to February 25, 2002. If you wish to participate in discussions, please visit the new Artima Forums.

Message:

JDBC driver + mysql

Posted by Eddie Moojen on May 08, 2001 at 6:21 AM

Im assuming your mysql is setup to take connections with correct permissions (something like this):

CREATE DATABASE mydb;

CREATE TABLE mydb.mytbl(
myText TEXT NOT NULL);

#permissions for testing only!
INSERT INTO mysql.db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Grant_priv,References_priv,Index_priv,Alter_priv) VALUES ('%','mydb,'root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

The mysql jar file has to be in the classpath of the jsp environment. (You might want to try using resin (www.caucho.com) as its very easy to set up (basically just put the jar in its lib directory then click and play the following code) as your jsp/ servlet engine.

Then the following jsp code will work (put it in the doc directory if your using caucho):

&alt;html>
&alt;head>
&alt;title>Test jsp&alt;/title>&alt;/head>
&alt;%@ page import="java.sql.*" %>
&alt;%@ page import="java.util.*" %>
&alt;%!

String sql;
Connection conn;
ResultSet rset;
Statement stmt;
String url = "jdbc:mysql://my.domain.com:3306/my_db";
String id = "root";
String pass = "mypassword";

public void manageConnection()
{
try {
// The newInstance() call is a work around for some
// broken Java implementations
Class.forName("org.gjt.mm.mysql.Driver").newInstance();

conn = DriverManager.getConnection(url, id, pass);
stmt = conn.createStatement();
}
catch (Exception E) {
System.err.println("Unable to load driver.\n");
System.out.println("Unable to load driver.\n");
E.printStackTrace();
}
}
%>

&alt;p>This excucutes making a connection&alt;/p>
&alt;% manageConnection();
sql="SELECT * FROM mydb.mytbl";
rset = stmt.executeQuery(sql);
%>

&alt;p>This is the result:&alt;/p>

&alt;%= rset.getString(1) %>

&alt;p>Closing connection&alt;/p>

&alt;%
stmt.close();
conn.close();
%>

_____________________________________________________
Hope it helps, if you ever need interesting problems then contact me :)