How to operate MySQL database instances using JSP

How to operate MySQL database instances using JSP

I. Overview

Before starting the introduction, let's talk about why we should write this article. I personally think that as an O & M engineer, we should be familiar with the knowledge network, not just by mastering some knowledge, such as the linux system, the construction of web servers, databases, and so on should also be familiar with the networks composed of these points. Specifically, how the points affect each other and how the points interact with each other, for example, when a problem occurs at a certain point, we can analyze the system to find the root cause of the problem. In this case, how does the web Front-end JSP program or PHP, ASP, etc.) establish a line with the background database through the Intermediate program? Here we will temporarily release JSP, tomcat, mysql is called a so-called line), how to communicate, how to affect each other, there is too much content involved here, limited to personal level hate, just introduce how JSP through tomcat, connect to the mysql database in the background.

2. Topology

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1Z6255034-0.png "border =" 0 "alt =" "/>

Lab environment: Centos5.8 (kernel 2.6.18) + tomcat5.5 + mysql5.0

Iii. JSP connection to MySQL

Note: Server construction is not the focus of this Article

Here we will first introduce the front-end JSP page and Tomcat connection knowledge points. Here we will talk about my personal understanding. First, JSP programs and tomcat communication should be through the connection pool provided by tomcat, tomcat can set the maximum number of connections in the connection pool to provide JSP program connections. connections in the connection pool can be dynamically released and recycled. However, the number of connections provided in the connection pool is smaller than the number of Mysql connection pools.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1Z6256324-1.png "border =" 0 "alt =" "/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1Z6253959-2.png "border =" 0 "alt =" "/>

Tomcat configuration connection pool

 
 

  
  
Tomcat connection pool Configuration
  
  
Vi/vim server. xml
  
  
 
  
  
Oracle Database Connection Pool Configuration
  
  
Configure the following information in  
  
  
<Resource
  
  
Auth = "Container"
  
  
Description = "sqlserver Datasource"
  
  
Name = "jdbc/ora"
  
  
Type = "javax. SQL. DataSource"
  
  
MaxActive = "50"
  
  
MaxIdle = "10"
  
  
Username = "" ----> username used to connect to the database
  
  
MaxWait = "10000"
  
  
DriverClassName = "oracle. jdbc. driver. OracleDriver"
  
  
Password = "" -----> password used to connect to the database
  
  
Url = "jdbc: oracle: thin: @ host: port/databases"
  
  
RemoveAbandoned = "true"
  
  
RemoveAbandonedTimeout = "60"
  
  
LogAbandoned = "true"/>
  
  
 
  
  
MySQL database connection pool Configuration
  
  
 
  
  
<Resource
  
  
Name = "jdbc/TestDB"
  
  
Auth = "Container"
Type = "javax. SQL. DataSource"
  
  
MaxActive = "100"
  
  
MaxIdle = "30"
  
  
MaxWait = "10000"
  
  
Username = "javauser"
  
  
Password = "javadude"
  
  
DriverClassName = "com. mysql. jdbc. Driver"
  
  
Url = "jdbc: mysql: // localhost: 3306/javatest"/>
  
  
 
  
  
SQL connection pool Configuration
  
  
<Resource
  
  
Auth = "Container"
  
  
Description = "sqlserver Datasource"
  
  
Name = "jdbc/sqlserver110"
  
  
Type = "javax. SQL. DataSource"
  
  
MaxActive = "100"
  
  
MaxIdle = "10"
  
  
Username = ""
  
  
MaxWait = "10000"
  
  
DriverClassName = "com. microsoft. jdbc. sqlserver. SQLServerDriver"
  
  
Password = ""
  
  
Url = "jdbc: microsoft: sqlserver: IP (port); database name;" reconnect = true"
  
  
RemoveAbandoned = "true"
  
  
RemoveAbandonedTimeout = "60"
  
  
LogAbandoned = "true"/>
 
 

Tomcat5.5 parameter explanation:

 
 

  
  
Tomcat5.5 parameter description:
  
  
1 maxActive: Maximum number of dB connections in pool. Make sure you
  
  
Configure your mysqld max_connections large enough to handle
  
  
All of your db connections. Set to-1 for no limit
  
  
The maximum number of connections in the connection pool is-1, indicating that the number of data connections is greater than the number of connections.
  
  
2 maxIdle: Maximum number of idle dB connections to retain in pool.
  
  
Set to-1 for no limit. See also the DBCP documentation on this
  
  
And the minEvictableIdleTimeMillis configuration parameter
  
  
The maximum number of idle connections in the connection pool)
  
  
3 maxWait: Maximum time to wait for a dB connection to become available
  
  
In MS, in this example 10 seconds. An Exception is thrown if
  
  
This timeout is exceeded. Set to-1 to wait indefinitely
  
  
Maximum wait time (ms) for a connection to become available.
  
  
4 driverClassName: Class name for the old mm. mysql JDBC driver is
  
  
Org. gjt. mm. mysql. Driver-we recommend using Connector/J though.
  
  
Class name for the official MySQL Connector/J driver is com. mysql. jdbc. Driver.
  
  
5 url: The JDBC connection url for connecting to your MySQL dB
  
  
6 removeAbandoned = "true" abandoned dB connections are removed and recycled)
  
  
Explanation: The default value is false when abandoned data connections are recycled to the connection pool.
  
  
7 removeAbandonedTimeout = "60" a dB connection has been idle before it is considered abandoned) Unit: seconds
  
  
Explanation: the number of seconds when a connection is idle will be abandoned.
  
  
8 logAbandoned = "true"
  
  
The default value of record abandoned data connections is false.
 
 

Create a WEB-INF/web. xml in the directory of the web application and add the following content

 
 

  
  
web.xml configuration 
  
  
<resource-ref> 
  
  
 <description>Oracle Datasource example</description> 
  
  
 <res-ref-name>jdbc/myoracle</res-ref-name> 
  
  
 <res-type>javax.sql.DataSource</res-type> 
  
  
 <res-auth>Container</res-auth> 
  
  
</resource-ref>  
 
 

User who connects to the database through JSP

 
 

  
  
MySQL configuration
  
  
Mysql> grant all privileges on *. * TO javauser @ localhost
  
  
-> Identified by 'javadude' with grant option;
  
  
Mysql> create database javatest;
  
  
Mysql> use javatest;
  
  
Mysql> create table testdata (
  
  
-> Id int not null auto_increment primary key,
  
  
-> Foo varchar (25 ),
  
  
-> Bar int );
  
  
Mysql> insert into testdata values (null, 'Hello', 12345 );
  
  
Query OK, 1 row affected (0.00 sec)
  
  
Mysql> select * from testdata; + ---- + ------- + | ID | FOO | BAR | + ---- + ------- + | 1 | hello | 12345 | + ---- + ------- + 1 row in set (0.00 sec) note: Create a new test user, a new database and a single test table. your MySQL user must have a password assigned. the driver will fail if you try to connect with an empty password Note !!! : The above user shocould be removed once testing is complete!
  
  
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1Z62523H-3.png "border =" 0 "alt =" "/>
 
 

JSP test page

 
 

  
  
<% @ Page import = "java. SQL. *" %>
  
  
<% @ Page contentType = "text/html; charset = gb2312" %>
  
  
<% @ Page language = "java" %>
  
  
<% @ Page import = "com. mysql. jdbc. Driver" %>
  
  
<% @ Page import = "java. SQL. *" %>
  
  
<%
  
  
String driverName = "com. mysql. jdbc. Driver ";
  
  
String userName = "javauser ";
  
  
String userPasswd = "java ";
  
  
String dbName = "javatest ";
  
  
String tableName = "testdata ";
  
  
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 );
  
  
While (rs. next ())
  
  
{
  
  
String foo = rs. getString ("foo ");
  
  
String bar = rs. getString ("bar ");
  
  
Out. print (foo + "");
  
  
Out. print (bar + "");
  
  
}
  
  
Rs. close ();
  
  
Statement. close ();
  
  
Connection. close ();
  
  
%>
  
  
The above code is only an example of implementation.
 
 

Iv. Test

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1Z6253315-4.png "border =" 0 "alt =" "/>

This article is from the "haangangular" blog. For more information, contact the author!