How to connect to MySQL database in JSP

First, the JSP program written on Eclipse to connect to the MySQL database always reports an error. I have all been down and marshal is ready to give up MySQL,... as in the TV series, the transfer always appears at this time. First, I saw saulzy's Installation Introduction to MySQL4.1.11, and then I found a good MySQL interface plug-in, finally, the JDBC driver of MySQL was configured and connected with the help of netizens. five days in a row, it was really hard to get through, but in the process, I also felt that I had learned a lot, huh, and I had to keep moving forward. now, let's make a summary over the past few days, hoping to help the same person who is learning Java technology.

I. JSP connection to MySQL software download

MySQL

Download version: 4.1.11

Http://dev.MySQL.com/downloads/MySQL/4.1.html

JDBC driver

Download version: 3.1.8

Http://dev.MySQL.com/downloads/connector/j/3.1.html

MySQL interface plug-in: MySQL-front

Download the version image: HongKong, which is the Chinese version)

Http://www.MySQLfront.de/download.html

Ii. JSP connection to MySQL software installation

1. Install MySQL

See related articles and recommended articles:

Http://blog.csdn.net/saulzy/archive/2005/04/23/359648.aspx

2. JDBC driver: MySQL-connector-Java-3.1.8

This is just a compressed package, does not need to install, as long as it is unzipped, what I use is folder MySQL-connector-Java-3.1.8 file: MySQL-connector-Java-3.1.8-bin.jar.

3. MySQL interface plug-in: MySQL-front

This is an installation program. Follow the prompts to install it.

Iii. JSP connection to MySQL environment Configuration

First, I want to explain that my current tomcat installation path is: D: \ Program Files \ Java \ Tomcat; JDK installation path is: D: \ Program Files \ Java \ j2sdk.

The JDBC driver needs to be configured here. before configuring, first put the MySQL-connector-Java-3.1.8-bin.jar local hard drive I put somewhere: D: \ Program Files \ Java \ MySQLforjdbc), and then according to your place, configure classpath, my configuration is as follows:

 
 

  
  
D:\Program files\Java\j2sdk\lib\tools.jar;  
  
  
D:\Program Files\Java\j2sdk\lib\MySQL-connector-Java-3.1.8-bin-g.jar;  
  
  
D:\Program Files\Java\MySQLforjdbc\MySQL-connector-Java-3.1.8-bin.jar 
 
 

The purpose of this configuration is to allow your Java application to find the driver connecting to MySQL.

After configuring the environment variables, there is a very important step is to configure the driver for JSP Connection database, this is actually very simple, is to copy the MySQL-connector-Java-3.1.8-bin.jar to some folders on the line, I read a lot of information on the Internet and asked a lot of people about the various statements. I have integrated them and I have done everything for the sake of insurance, anyway, it is to copy a K file, now list the folder to copy the MySQL-connector-Java-3.1.8-bin.jar, as follows:

D: \ Program Files \ Java \ Tomcat \ common \ lib

D: \ Program Files \ Java \ Tomcat \ shared \ lib

Iv. database usage

After MySQL is installed, some recommended articles should be noted ):

Http://blog.csdn.net/saulzy/archive/2005/04/23/359811.aspx

As mentioned in the article, the most important thing after MySQL is installed is to check whether the database has been started as a system service. Therefore, before performing database operations, you should check whether the database is started, at the start of the operating system, choose run> enter services. msc. Make sure that the MySQL service you set has been started during installation. In this way, no connection error will be reported during database operations.

I mentioned a more convenient MySQL interface plug-in, but this interface was found only after I started using MySQL. At the beginning, I started to operate on it using command lines in dos. although the interface can also be used for database creation and permission setting, I think it is also an important skill to know how to use the command line. So let's start with the command line, how to use MySQL. I will talk about MySQL-front later.

Now I want to create a database shujuku in MySQL and a table biao in the database. The specific command is as follows: I have just installed MySQL)

1. Enter the dos Status. Remember the command line to run in the bin directory under the MySQL installation directory)

2. Connect to MySQL

Input: MySQL-h localhost-u root-p

Enter the password set during installation, and enter the MySQL Command editing interface.

3. Use Basic MySQL commands (after each command is entered in the MySQL command line, there must be a semicolon (; otherwise, an error will be reported)

Display Database: show databases;

Database used: use Database Name;

4. Create a database

Command: create database shujuku;

5. Set the database permission user and password)

Command: grant all privileges on shujuku. *

 
 

  
  
[url=mailto:test@localhost]test@localhost[/url]  
  
  
identified by “123456”; 
 
 

After you execute this command, you only need to operate the database shujuku when you log on to shujuku with the username: test and password: 123456. This avoids the use of root, this is of great help to database security.

6. Create a table

Command: create table biao (id int (8) primary key, name varchar (10 ));

The remaining sqsl commands are basically the same as standard sqsl commands.

It is worth mentioning that you enter "? ", There will be a simple help of the MySQL Command, as shown below:

Well, we can also know to exit, that is, "exit!

V. Use of MySQL-front

I found several MySQL interface tools and thought it was the most concise and convenient MySQL-front, but it was a pity that I had to pay for it, but fortunately I had a trial period, the most important thing is that MySQL-front has a Simplified Chinese version. If the English is not good, I will be much more comfortable to use. the following is a brief introduction.

First of all, the installation is needless to say, there is a wizard, and it is very simple. after installation, a dialog box is displayed during the first running. Here you can add shujuku as set above. The process is as follows:

After you fill in the user name and password you set in MySQL above in the check box for registration, there will be a database for shujuku in the select database box. Select and press OK. after entering MySQL-fron, you will see the following interface. You can perform this operation.

It should be noted that you can also add the root user, which requires you to select Settings on the MySQL-fron interface-> dialog-> Create, and then press the above button, with root, you can add more users. The method is the same. Setting different users makes it easy to manage different databases, do not allow others to use your root user to ensure the security of your database.

Vi. JSP connection to MySQL

Now we are trying to connect to MySQL Using jsp.

I created a test_MySQL.jsp page in eclipse. The Code is as follows:

 
 

  
  
// Driver name
  
  
StringDriverName="Com. MySQL. jdbc. Driver";
  
  
// Database username
  
  
StringUserName="Cl41";
  
  
// Password www.knowsky.com
  
  
StringUserPasswd="123456";
  
  
// Database Name
  
  
StringDbName="Db";
  
  
// Table name
  
  
StringTableName="Dbtest";
  
  
// Concatenates strings
  
  
StringUrl="Jdbc: MySQL: // localhost /"+ DbName + "?User="+ UserName +"&Password= "+ UserPasswd;
  
  
Class. forName ("com. MySQL. jdbc. Driver"). newInstance ();
  
  
ConnectionConnection=DriverManager. GetConnection (url );
  
  
StatementStatement=Connection. CreateStatement ();
  
  
StringSQL="SELECT * FROM"+ TableName;
  
  
ResultSetRs=Statement. ExecuteQuery (SQL );
  
  
// Obtain the data result set
  
  
ResultSetMetaDataRmeta=Rs. GetMetaData ();
  
  
// Determine the number of columns and fields of the dataset.
  
  
IntNumColumns=Rmeta. GetColumnCount ();
  
  
// Output each data value
  
  
Out. print ("id ");
  
  
Out. print ("| ");
  
  
Out. print ("num ");
  
  
Out. print ("
  
  
");
  
  
While (rs. next ()){
  
  
Out. print (rs. getString (1) + "");
  
  
Out. print ("| ");
  
  
Out. print (rs. getString (2 ));
  
  
Out. print ("
  
  
");
  
  
}
  
  
Out. print ("
  
  
");
  
  
Out. print ("database operation successful, congratulations ");
  
  
Rs. close ();
  
  
Statement. close ();
  
  
Connection. close ();
  
  
%> 
 
 

Then, deploy test _ MySQL. jsp to tomcat. For details about how to deploy it, see "Configure Eclpise + tomcat and compile and deploy JSP". The result is displayed in the browser.

1. Introduction to the Forward and sendRedirect methods of JSP
2. Use JSP pages to generate PDF reports
3. Step for customizing JSP labels
4. Detailed test of JSP containers
5. Describes the following features of jsp http Server