mysql java 连接池_java mysql 连接池

测试环境：

Ip地址：192.168.1.8

JDK+tomcat+mysql+mysql-connector-java

(一)安装JDK

a)./j2sdk-1.4.2-03-linux-i586.bin

b)mv j2sdk1.4.2 /usr/local/

c)ln -s /usr/local/j2sdk1.4.2 /usr/local/jdk

d)配置环境变量：/etc/profile

JAVA_HOME=/usr/local/jdk

PATH=$PATH:$JAVA_HOME/bin

CLASSPATH=$JAVA_HOME/lib/tools.jar

export JAVA_HOME CLASSPATH

e)source /etc/profile

f)验证安装：java –version

(二)安装tomcat

a)tar zxvf jakarta-tomcat-5.0.29.tar.gz

b)Tomcat-5.5以上版本必须在java1.5版本以上运行否则Tomcat无法启动

c)mv jakarta-tomcat-5.0.29 /usr/local/

d)ln -s /usr/local/jakarta-tomcat-5.0.29 /usr/local/tomcat

e)/usr/local/tomcat/bin/catalina.sh run

(三)安装mysql

a)tar zxvf mysql-5.1.37.tar.gz

b)groupadd mysql

c)useradd -g mysql mysql

d)./configure --prefix=/usr/local/mysql

e)make && make install

f)cp support-files/my-medium.cnf /etc/my.cnf

g)cd /usr/local/mysql

h)chown -R mysql .

i)chgrp -R mysql .

j)/usr/local/mysql/bin/mysql_install_db --user=mysql

k)chown -R root .

l)chown -R mysql var

m)bin/mysqld_safe --user=mysql &>/dev/null

n)修改root用户密码：./mysqladmin -u root password '123456'

o)

p)创建名为test1的数据库，并建立pet的表：

q)mysql> Create database test1use test1

r)mysql>CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

s)在本地新建一个文件名为pet.txt内容如下：

nameownerspecies sexbirthdeath

FluffyHaroldcatf1993-02-04

ClawsGwencatm1994-03-17

BuffyHarolddogf1989-05-13

FangBennydogm1990-08-27

BowserDianedogm1979-08-311995-07-29

ChirpyGwenbirdf1998-09-11

WhistlGwenbird1997-12-09

SlimBennysnakem1996-04-29

t)导入pet.txt到pet表

u)mysql> load data local infile '/pet.txt' into table pet;

(四)安装mysql-connector-java-3.2.0-alpha

a)tar zxvf mysql-connector-java-3.2.0-alpha.tar.gz

b)cp mysql-connector-java-3.2.0-alpha-bin.jar /usr/local/jdk/lib/

c)cp mysql-connector-java-3.2.0-alpha-bin.jar /usr/local/tomcat/common/lib/

(五)配置mysql数据源

a)vi /usr/local/tomcat/conf/tomcat-users.xml加入如下内容：

重启tomcat

b)用户名：admin密码：111111。选择Resources－Data sources进入配置数据源界面，选择Data Source Actions－>选择Create New Data Source,进入配置详细信息界面，内容如下：

JNDI Name:jdbc/mysql

Data Source URL:jdbc:mysql://192.168.1.8/test1

JDBC Driver Class:org.gjt.mm.mysql.Driver

User Name:root

Password:123456

Max. Active Connections:4

Max. Idle Connections:2

Max. Wait for Connection:500

Validation Query:

c)vi /usr/local/tomcat/conf/web.xml：在前加入如下内容：

DB Connection

jdbc/mysql

javax.sql.DataSource

Container

d)vi /usr/local/tomcat/conf/Catalina/localhost/ROOT.xml加入如下

e)测试连接代码：vi /usr/local/tomcat/webapps/ROOT/test.jsp

//驱动程序名

String driverName="com.mysql.jdbc.Driver";

//数据库用户名

String userName="root";

//密码

String userPasswd="123456";

//数据库名

String dbName="test1";

//表名

String tableName="pet";

//联结字符串

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);

//获得数据结果集合

ResultSetMetaData rmeta = rs.getMetaData();

//确定数据集的列数，亦字段数

int numColumns=rmeta.getColumnCount();

//输出每一个数据值

out.print("name"+"");

out.print("owner"+"");

out.print("species");

out.print("species");

out.print("birth");

out.print("death");

out.print("
");

while(rs.next()) {

out.print(rs.getString(1)+" ");

out.print(rs.getString(2)+" ");

out.print(rs.getString(3));

out.print("yuan RMB");

out.print("
");

}

out.print("
");

out.print("数据库操作成功，恭喜你");

rs.close();

statement.close();

connection.close();

%>

(六)测试：