Connect JDK + Tomcat + servlet to MySQL database

After several days of debugging and reading data, I finally connected the MySQL database. Although I have been depressed for the past few days, I have been unable to find the reason. Suddenly, I am very happy. After all, I feel that these efforts have not been in vain. In addition, I recorded the entire debugging process.
I. Open-Source Software
Tomcat 5.0.28
JDK j2sdk1.4.2 _ 03
JDBC mysql-connector-java-3.1.13-bin.jar
For MySQL 5.0
Ii. Configure the environment
Download the above open-source software from the corresponding official website and install it in sequence.
The installation sequence and path are as follows:
JDK: D: \ j2sdk1.4.2 _ 03
Tomcat: D: \ jakarta-tomcat-5.0.28
MySQL: C: \ Program Files \ mysql (default path)
JDBC: Decompress the downloaded JDBC driver and place it in the D: \ jdb directory.

Start Environment Change configuration: Right-click "my computer" and choose "properties"> "advanced"> "environment variable". Under the system variable, select "new". The specific configuration is as follows:
Variable name variable value
Catalina_home D: jakarta-tomcat-5.0.28
Classpath.; D: \ j2sdk1.4.2 _ 03 \ Lib \ tools. jar;
D: \ j2sdk1.4.2 _ 03 \ JRE \ Lib \ RT. jar;
D: \ Tomcat 5.0 \ common \ Lib \ servlet-api.jar;
D: \ j2sdk1.4.2 _ 03 \ Lib \ mysql-connector-java-3.1.13-bin-g.jar;
D: \ JDBC \ mysql-connector-java-3.1.13-bin.jar
Java_home D: \ j2sdk1.4.2 _ 03

Note:
1 \ when classpath is set,.; cannot be omitted. It represents the current path.
2 \ decompress the downloaded JDBC driver, you can see the mysql-connector-java-3.1.13-bin.jar file and related folders, open the debug folder, you can find the mysql-connector-java-3.1.13-bin-g.jar file.
3 \ set D: \ Tomcat 5.0 \ common \ Lib \ servlet-api.jar in classpath to make servlet available.

3. Download sqlyog.ProgramAfter installation, you can make the MySQL database visible. Self-perception is better than mysql-front.
4. Use servlet to create a bookstore database and insert data. If "Success!" is printed successfully !" Otherwise, an exception is thrown to print "failed to load database ". (This program is taken from Sun Xin's Java Web development details book)
1. d disk create folder jsplesson \ ch08, create SRC and WEB-INF subfolders under the ch08 folder, create classes subfolders under the WEB-INF folder.
2. Create the createdbservlet. Java file in the SRC folder. The content is as follows:
Package org. sunxin. Lesson. jsp. Bookstore;

Import javax. servlet .*;
Import java. Io .*;
Import javax. servlet. http .*;
Import java. SQL .*;

Public class createdbservlet extends httpservlet
{
Private string URL;
Private string user;
Private string password;

Public void Init () throws servletexception
{
String driverclass = getinitparameter ("driverclass ");
Url = getinitparameter ("url ");
User = getinitparameter ("user ");
Password = getinitparameter ("password ");
Try
{
Class. forname (driverclass );
}
Catch (classnotfoundexception CE)
{
Throw new unavailableexception ("failed to load the database driver! ");
}
}

Public void doget (httpservletrequest req, httpservletresponse resp)
Throws servletexception, ioexception
{
Connection conn = NULL;
Statement stmt = NULL;
Try
{
Conn = drivermanager. getconnection (URL, user, password );
Stmt = conn. createstatement ();
Stmt.exe cuteupdate ("create database Bookstore ");
Stmt.exe cuteupdate ("use Bookstore ");
Stmt.exe cuteupdate ("create table bookinfo (ID int not null primary key, title varchar (50) not null, author varchar (50) not null, bookconcern varchar (100) not null, publish_date date not null, price float (200) not null, amount smallint, remark varchar () engine = InnoDB ");
Stmt. addbatch ("insert into bookinfo values (1, 'java from entry to Master', 'zhang san', 'zhang sanpress ', '2017-6-1', 2004, 35, null) ");
Stmt. addbatch ("insert into bookinfo values (2, 'jsp deep programming ', 'lily', 'lilypress', '2017-10-1 ', 2004, 20, null )");
Stmt. addbatch ("insert into bookinfo values (3, 'j2ee advanced programming ', 'wang wu', 'wang wupress', '2017-3-1 ', 2005, 10, null )");
Stmt.exe cutebatch ();

Printwriter out = resp. getwriter ();
Out. println ("success! ");
Out. Close ();
}
Catch (sqlexception SE)
{
Se. printstacktrace ();
}
Finally
{
If (stmt! = NULL)
{
Try
{
Stmt. Close ();
}
Catch (sqlexception SE)
{
Se. printstacktrace ();
}
Stmt = NULL;
}
If (Conn! = NULL)
{
Try
{
Conn. Close ();
}
Catch (sqlexception SE)
{
Se. printstacktrace ();
}
Conn = NULL;
}
}
}
}
2. Compile createdbservlet. Java to generate the org \ sunxin \ Lesson \ JSP \ bookstore \ createdbservlet. Class file. Cut it to the classes folder.
3. Deploy Servlet
In the % catalina_home %/CONF/Catalina/localhost directory, configure the run directory and create the ch08.xml File
Ch08.xml
<Context Path = "/ch08" docbase = "D: \ jsplesson \ ch08" reloadable = "true"/>

4. Create a WEB-INF directory under F: \ jsplesson \ ch08 \ src directory according to the directory hierarchy of the Web application, and create a classes directory and web under the WEB-INF directory. XML file, edit web. XML file.
Web. xml
<? XML version = "1.0" encoding = "gb2312"?>

<Web-app xmlns = "http://java.sun.com/xml/ns/j2ee"
Xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance"
Xsi: schemalocation = "http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
Version = "2.4">

<Servlet>
<Servlet-Name> createdbservlet </servlet-Name>
<Servlet-class> org. sunxin. Lesson. jsp. Bookstore. createdbservlet </servlet-class>
<Init-param>
<Param-Name> driverclass </param-Name>
<Param-value> com. MySQL. JDBC. Driver </param-value>
</Init-param>
<Init-param>
<Param-Name> URL </param-Name>
<Param-value> JDBC: mysql: // localhost: 3306/MySQL </param-value>
</Init-param>
<Init-param>
<Param-Name> User </param-Name>
<Param-value> root </param-value>
</Init-param>
<Init-param>
<Param-Name> password </param-Name>
<Param-value> root </param-value>
</Init-param>
</Servlet>

<Servlet-mapping>
<Servlet-Name> createdbservlet </servlet-Name>
<URL-pattern>/createdb </url-pattern>
</Servlet-mapping>

</Web-app>

5. Configure the JDBC driver of MySQL, download the JDBC driver mysqlconnector/J from the website, find the jar package named mysql-connector-java-3.1.7-bin.jar in the decompressed directory, put it in the % catalina_home % \ common/lib directory.
6. Start tomcat, start MySQL, and enter http: // localhost: 8080/cho8/createdb

Output success! It indicates that the database and table have been innovated successfully.
5. My mistakes
1. The following prompt appears on the page:
HTTP status 404-/ch08/createdb

Type status report
Message/ch08/createdb
Description the requested resource (/ch08/createdb) is not available.

Apache Tomcat/5.0.28
The ch08.xml file has missing quotation marks.
2. After Entering the address in IE, the page is blank with no words. Check that my MySQL database already has a bookstore database and data has been inserted, so there is no success on the page! No error is reported. Manually delete an existing bookstore database, restart tomcat, and start IE to enter the address. The correct result "Success!" is displayed !"