JSP connection to MySQL database introduction _ MySQL

Source: Internet
Author: User
Tags mysql commands mysql download
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.