Introduction to connecting to MySQL databases using Java

1. Download the MySQL database Official Website: www.mysql.com to register a MySQL user
MySQL for Windows v5.2 Alpha http://www.skycn.com/soft/1262.html updated on:
2. Download the MySQL database driver. Address:
Http://www.mysql.com/products/connector/all drivers
Java: http://dev.mysql.com/downloads/connector/j/5.0.html
Graphic interface tool: Workshop

3. decompress the package, copy the mysql-connector-java-3.1.0-alpha-bin.jar file to your tomcat/common/lib, restart tomcat, JSP connection MySQL environment is complete.
Website approach is to release the JAR file directly to the website directory/WEB-INF/lib
Installation references: (Package) http://hi.baidu.com/ybbucd/blog/item/5662d02a115f762fd42af1c0.html
(Installation Package) How step by step install MySQL database http://www.zttt.net/blog/showartic.asp? Id = 474
Installation notes:
1) before installing MySQL 5.0, you must uninstall the old version.
2 ). for MySQL garbled characters, you also need to set the language to be selected during installation. You do not need to select GBK or gb2312 by default. when the test is successful, it will not pass the start server immediately. and so on.
3). You can download the MySQL management software: EMS MySQL Manager 3 lite.
4). My local secutity settins: the Default User Root rootpwd: bin5812086 allows remote operations and does not allow anonymous operations
5). MySQL Initialization
6). Start
Method 1: the system starts by default. Because the database will exist as a service after MySQL is installed, the MySQL database is automatically started every time the system is started.
Method 2: run the mysqladmin program in the bin directory by running the command to start the MySQL database.

7) problems encountered after installation:
I. The database server passes the test connection on the local machine. when the machine is connected, the following error occurs: NULL, message from server: "host '210. 209.142.190 'is not allowed to connect to this MySQL Server "'.
Solution: In addition to setting the root password, remember to check enable acess remote connect to allow remote connection. Otherwise, only local connections are allowed.
If it is already installed, open mysqlinstanceconfig.exe in the/MySQL Server 5.0/bindirectory to reinitialize the settings.
2. Pay attention to the database connection URL. Do not forget the complete address, including the default port number 3306.
3. Modify the default Character Set of MySQL: Add default-character-set = GBK in the [mysqld] section of the configuration file. Note that GBK is in lower case. If it is changed to upper case, it may not work, I have not tested it. After the modification, restart the MySQL server. The newly added table character set will be the GBK character set. You can also use the alter table command to modify the character set of a table. For more information about the syntax, see the syntax description of querybrowser.
Chinese garbled characters in MySQL are strange.
MySQL servers in two locations combined with JSP pages and MySQL-front forced me to test multiple encoding combinations
The final solution that is satisfactory to you is:
Set MySQL to utf8 in remote Linux
Utf8 for connecting JSP pages to databases
MySQL on local Windows is set to gb2312.
When importing data from Windows MySQL to Linux MySQL
Use mysqldump -- default-character-set = utf8 database [Table]> some. SQL
No extra conversion is required when backing up data from Linux to Windows.
In addition, the local utf8 JSP operation for MySQL of gb2312 is normal.
The reason may be that MySQL on Windows is 5. * While MySQL on Linux is only 4 .*
Advanced edition enhances automatic conversion or compatibility
4. Syntax
1). Common MySQL commands:
Connection: mysql-H 192.168.1.2-u root-P <enter> nlt
2). MySQL does not support stored procedures.
3). MySQL 5.1 reference manual http://dev.mysql.com/doc/refman/5.1/zh/index.html
4). Connection syntax
Class. forname ("com. MySQL. JDBC. Driver ");
Connection con = drivermanager. getconnection ("JDBC: mysql: // host: Port/Database", "user", "password ");

5. Programming
 
1) Use drivermanager to register mm. MySQL
The Class Name of MM. MySQL is org. gjt. Mm. MySQL. Driver. It must be written during registration.
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();
 
2) jdbc url parameter description
URL format: JDBC: mysql: // [hostname] [: Port]/dbname [? Param1 = value1] [? M2 = value2]...
Default parameter name
User Database User name none
Password Database User Password unavailable
Autoreconnect determines whether to automatically connect when the database connection is lost. The value is true/False false.
Maxreconnects if autoreconnect is true, this parameter indicates the number of retries. The default value is 3.
Initialtimeout if autoreconnect is true, this parameter is the number of seconds waiting before reconnection 2
Maxrows sets the number of rows returned during the query. 0 indicates all 0
Whether useunicode uses Unicode output, true/False false
Characterencoding if useunicode, this parameter is set to the encoding type. It is recommended that you use 8859_1
 
Prompt
At the same time, useunicode and characterencoding can be used to solve Chinese problems in database output.
For example, JDBC: mysql: // localhost/test? User = root & useunicode = true; characterencoding = 8859_1
 
3). Unsigned indicates "unsigned,
Unsigned and zerofill are non-negative values. You can use this type to increase the Data Length,
For example, if the maximum int value is 65535, the maximum int unsigned zerofill value is

Example 1: A simple example
This JSP Example uses a library, which has only one table (Address Book)
 
Create Database addressbook
Use addressbook
 
Create Table addressbook
(
Id int auto_increment primary key,
Name varchar (30 ),
Address varchar (255 ),
Phone varchar (20)
);
 
Insert record
Insert into addressbook (name, address, phone) values ('jx', 'zhejiang Yuyao ', '2017-0574 ');
JSP code: absolute, resultset. type_scrool_sensitive and other directories are used in the code to test whether mm. MySQL complies with JDBC 2.0 specifications.
<% @ Page import = "Java. SQL. *" %>
<%
Out. println ("Address Book! ");
Try
{
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();
}
Catch (exception E)
{
Out. println ("unable to load driver .");
}
Try
{
Connection c = drivermanager. getconnection ("JDBC: mysql: // localhost/addressbook? User = root & Password = jjx & useunicode = true & characterencoding = 8859_1 ");
Statement S = C. createstatement (resultset. type_scroll_sensitive, resultset. concur_read_only );
Resultset rs = s.exe cutequery ("select * From addressbook ");
Out. println ("<Table border = 1> ");
Int I = 1;
For (I = 10; I <20; I ++)
{
If (Rs. Absolute (I ))
{
Out. println ("<tr> <TD> ");
Out. println (Rs. getstring (1 ));
Out. println ("</TD> ");
Out. println ("<TD> ");
Out. Print (Rs. getstring (2 ));
Out. println ("</TD> ");
Out. println ("<TD> ");
Out. Print (Rs. getstring (3 ));
Out. println ("</TD> </tr> ");
}
Else
{
Break;
}
}
Out. println ("</table> ");
Rs. Close ();
S. Close ();
C. Close ();
}
Catch (sqlexception E)
{
Out. println ("sqlexception:" + E. getmessage ());
Out. println ("sqlstate:" + E. getsqlstate ());
Out. println ("vendorerror:" + E. geterrorcode ());
}
%>
 
Example 2:

<% @ Page contenttype = "text/html; charset = gb2312" %>
<% @ Page import = "Java. SQL. *" %>
<HTML>
<Body>
<%
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();
String url = "JDBC: MySQL :/// localhost: 3306/db_name? User = yourusername & Password = yourpassword & useunicode = true & characterencoding = gb2312 ";
Connection conn = drivermanager. getconnection (URL );
Statement stmt = conn. createstatement ();
String query = "select * From table_name order by ID ";
Resultset rs1_stmt.exe cutequery (query );
While (Rs. Next ())
{
String S = Rs. getstring (1); // check whether your field is of the struct type. Otherwise, you cannot use getstring. Use getboolean according to the field class.
Out. Print (S + "<br> ");
}
%>
</Body>
</Html>