JSP connection to MySQL database introduction _ MySQL

1. download Mysql download version: unzip driver download version: 3.1.8dev.mysql.comdownloadsconnectorj3.1.htmlMysql interface plug-in: mysql-front download version Image: HongKong (I will install it later) www. m 1. 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 (the Chinese version will be installed when I come back)
Http://www.mysqlfront.de/download.html
　　
　　 II. software installation
　　
1. install mysql
See related articles, 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. 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 disk somewhere (I put the place: D: \ Program Files \ Java \ mysqlforjdbc), and then according to your place, configure classpath. my configuration is as follows:
　　
[Blocked advertisement] 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 the installation of Mysql, there are some places to note (recommended): 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 (assuming mysql is just installed)
　　
1. enter the dos status (remember to run the command line 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 Database permissions (user and password)
Command: grant all privileges on shujuku. * to test @ localhost 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:
　　
<% @ 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 = "cl41 ";
　　
// Password
　　
String userPasswd = "123456 ";
　　
// Database name
　　
String dbName = "db ";
　　
// Table name
　　
String tableName = "dbtest ";
　　
// 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 ("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.