一:概述(overview)
介绍一下JSP怎么通过tomcat,连接后台的mysql数据库。
Introduce how to connect backend mysql data base.
二:拓扑图 (topological graph)
实验环境(experimental envieonment):Centos5.8(kernel 2.6.18)+tomcat5.5+mysql5.0
三:JSP连接MySQL (JSP connect Mysql)
- web服务器 搭建--->详见 http://haicang.blog.51cto.com/2590303/963670
- mysql数据库服务器的搭建-->详见http://haicang.blog.51cto.com/2590303/930541
tomcat配置连接池 (tomcat deploy pools)
- tomcat连接池配置
- vi/vim server.xml
- Oracle数据库的连接池配置
- 在<host> </host>中配置如下信息
- <Resource
- auth="Container"
- description="sqlserver Datasource"
- name="jdbc/ora"
- type="javax.sql.DataSource"
- maxActive="50"
- maxIdle="10"
- username="" ---->连接数据库的用户名
- maxWait="10000"
- driverClassName="oracle.jdbc.driver.OracleDriver"
- password=""----->连接数据库的用户密码
- url="jdbc:oracle:thin:@host:port/databases"
- removeAbandoned="true"
- removeAbandonedTimeout="60"
- logAbandoned="true"/>
- MySQL数据库的连接池配置
- <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的连接池配置
- <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(端口);数据库名字;"reconnect=true"
- removeAbandoned="true"
- removeAbandonedTimeout="60"
- logAbandoned="true" />
tomcat5.5参数解释:
在web应用程序的目录下创建WEB-INF/web.xml,并添加如下内容:
- tomcat5.5参数说明:
- 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
- 连接池中最大的连接数 设为-1 表示不限制 注意数据的连接数要大于此连接数
- 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
- 保持在连接中最大的闲置连接数(在连接池最大的空闲连接数)
- 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
- 等待一个连接成为可用连接的最大等待时间 单位毫秒ms
- 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)
- 解释:被遗弃的数据连接 回收到连接池中 默认为false
- 7 removeAbandonedTimeout="60"(a dB connection has been idle before it is considered abandoned)单位秒
- 解释:在一个连接空闲多少秒会被遗弃
- 8 logAbandoned="true"
- 记录被遗弃的数据连接 默认为false
- 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>
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 | +----+-------+----
JSP测试页面:
- <%@ 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.executeQuery(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();
- %>
/*第一次编排带源码的博客,排版不是太好,多多包涵*/