How to connect to MySQL through JSP in Linux

I. Install MYSQL:

$ Sudo apt-get install mysql-admin mysql-query-browser
========================================================== ======================================
Mysql-admin-GUI tool for intuitive MySQL administration

Mysql-query-browser-Official GUI tool to query MySQL database

Put the mysql-connector-java-5.0.4-bin.jar driver under the $ CATALINA_HOME/common/lib directory

Ii. install TOMCAT:

$ Sudo apt-get install tomcat5.5 tomcat5.5-admin tomcat5.5-webapp
========================================================== ======================================
Tomcat5.5-Java Servlet 2.4 engine with JSP 2.0 support
Tomcat5.5-admin-Java Servlet engine -- admin & manager web interfaces
Tomcat5.5-webapps-Java Servlet engine -- documentation and example web applications
After tomcat is installed by default, the corresponding file is generated in/var/lib/tomcat5.5 under the/usr/share/tomcat5.5/directory.
The command to start TOMCAT is: sudo/usr/share/tomcat5.5/bin/startup. sh
The command to disable TOMCAT is sudo/usr/share/tomcat5.5/bin/shutdown. sh.

3. Set its environment variables:
Add the following in/etc/profile:

# Java environment variable settings

JAVA_HOME =/usr/lib/jvm/java-1.5.0-sun
Export JAVA_HOME

JRE_HOME =/usr/lib/jvm/java-1.5.0-sun
Export JRE_HOME

CLASSPATH =/usr/lib/jvm/java-1.5.0-sun/lib:/usr/share/tomcat5.5/common/lib: $ JAVA_HOME/jre/lib/rt. jar
Export CLASSPATH

CATALINA_BASE =/usr/share/tomcat5.5
Export CATALINA_BASE

CATALINA_HOME =/usr/share/tomcat5.5
Export CATALINA_HOME

TOMCAT_HOME =/usr/share/tomcat5.5
Export TOMCAT_HOME

PATH = $ PATH: $ HOME/bin: $ JAVA_HOME/bin:/usr/share/tomcat5.5/bin
Export PATH

4. Create testmysql. jsp under $ TOMCAT_HOME/webapps/ROOT:

The content is as follows:
<% @ Page contentType = "text/html; charset = gb2312" %>
 
<% @ Page language = "java" %>
 
<% @ Page import = "com. mysql. jdbc. Driver" %>
 
<% @ Page import = "java. SQL. *" %>
 
<%
// Driver name
 
String driverName = "com. mysql. jdbc. Driver ";
 
// Database username
 
String userName = "your name ";
 
// Password
 
String userPasswd = "your passwd ";
 
// Database Name
 
String dbName = "test ";
 
// Table name
 
String tablename = "student ";
 
// Concatenates strings
 
String url = "JDBC: mysql: // localhost/" + dbname + "? User = "+ username +" & Password = "+ userpasswd;
 
Class. forname ("com. MySQL. JDBC. Driver"). newinstance ();
 
Connection connection = drivermanager. getconnection (URL );
 
Statement statement = connection. createstatement ();
 
String SQL = "select * from" + tablename;
 
Resultset rs = statement.exe cutequery (SQL );
 
// Obtain the data result set
 
Resultsetmetadata rmeta = Rs. getmetadata ();
 
// Determine the number of columns and fields of the dataset.
 
Int numcolumns = rmeta. getcolumncount ();
 
// Output each data value
 
Out. print ("sno ");
 
Out. print ("| ");
 
Out. print ("sname ");
 
Out. print ("| ");

Out. print ("<br> ");
 
While (rs. next ()){
 
Out. print (rs. getString (1 ));
 
Out. print ("| ");
 
Out. print (rs. getString (2 ));
 
Out. print ("| ");

}
 
Out. print ("<br> ");
 
Out. print ("database access success, congratulation! ");
 
Rs. close ();
 
Statement. close ();
 
Connection. close ();
%>

Note that the following problems can be solved: the default access port of Tomcat 5.5 in UBUNTU is 8180. You can modify the port in/conf/server. xml under $ TOMCAT_HOME ,. If the access permission is displayed, modify the file/usr/share/tomcat5.5/conf/catalina. policy to grant it the access permission. For example, add: // The permissions granted to the context root directory apply to JSP pages.
Grant codeBase "file: $ CATALINA_HOME/webapps/jsp-examples /-"{
Permission java.net. SocketPermission "jdbc: mysql: // localhost: 3306", "connect ";
Permission java.net. SocketPermission "localhost: 8080", "connect ";};
Add the Administrator account in conf/tomcat-usersxml, for example
<? Xml version = '1. 0' encoding = 'utf-8'?>
<Tomcat-users>
<Role rolename = "tomcat"/>
<Role rolename = "role1"/>
<Role rolename = "manager"/>
<Role rolename = "admin"/>
<User Username = "Tomcat" Password = "Tomcat" roles = "Tomcat"/>
<User Username = "Aaron" Password = "Aaron" roles = "Admin, Manager"/>
<User Username = "role1" Password = "Tomcat" roles = "role1"/>
<User Username = "both" Password = "Tomcat" roles = "tomcat, role1"/>
</Tomcat-users>