Connect to the MySQL database using Java

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:

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 the mysql-connector-java-3.1.8-bin. I copied jar files to some folders. I read a lot of information on the Internet and asked a lot of people about the various statements. I have integrated it and I have done all the work for the sake of insurance, haha, It is copying a K file, now list to copy the mysql-connector-java-3.1.8-bin.jar folder, 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, permission setting, and other operations, 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 "?" on the command line "?", The following is a simple help for MySQL commands:

Well, we can also know that exit 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 ("<br> ");

While (Rs. Next ()){

Out. Print (Rs. getstring (1) + "");

Out. Print ("| ");

Out. Print (Rs. getstring (2 ));

Out. Print ("<br> ");

}

Out. Print ("<br> ");

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 test_mysql.jsp, see "Configure eclpise + Tomcat and compile and deploy jsp". The result is displayed in the browser.