数据库连接池实例
简单数据库连接池实例java+mysql

一、编写数据库连接池DBUtil.java
package com.test.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBUtil
{

public static Context context = null;
public static DataSource ds = null;
public static Connection conn = null;
public static PreparedStatement pstmt = null;

public static PreparedStatement getPstmt(String sql)
{
try{
context = new InitialContext();
ds = (DataSource) context.lookup("java:comp/env/jdbc/test");
conn = ds.getConnection();
pstmt = conn.prepareStatement(sql);
System.out.println("打开数据库连接池");
}
catch(NamingException e) {
e.printStackTrace();
}
catch(SQLException e) {
e.printStackTrace();
}

return pstmt;
}

public static ResultSet query(){
ResultSet rs = null;
try{
rs=pstmt.executeQuery();
}
catch(SQLException e) {
e.printStackTrace();

}
return rs;
}

public static int update()
{
int num = 0;
try{
num = pstmt.executeUpdate();
}
catch(SQLException e) {
e.printStackTrace();
}
return num;
}

public static void close()
{
try{
if(pstmt != null)
{
pstmt.close();
}
if(conn != null)
{
conn.close();
}
if(context != null)
{
context.close();
}
System.out.println("关闭数据库连接池");
}
catch (Exception e)
{
e.printStackTrace();
}
}

}

二、创建mysql数据库test，添加部门表department，其中2个字段分别为：
部门ID：department_id  int  32  非空  主键
部门名称：department_name  varchar  128  非空

三、编写JavaBean（以添加部门为例Department.java）
package com.test.bean;

public class Department {

public int departmentID;
public String departmentName;


public int getDepartmentID() {
return departmentID;
}
public void setDepartmentID(int departmentID) {
this.departmentID = departmentID;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}

}

四、编写Dao文件DepartmentDao.java
package com.test.model;

import com.test.bean.Department;

public interface DepartmentDao {
void addDepartment(Department department);
}

五、编写Dao文件的实现DepartmentDaoImpl.java
package com.test.model;

import java.sql.ResultSet;

import com.test.bean.Department;
import com.test.util.DBUtil;

public class DepartmentDaoImpl implements DepartmentDao {

private int maxDepartmentID() {
String sql = "select max(department_id) from department";
DBUtil.getPstmt(sql);
ResultSet rs = DBUtil.query();
try {
if (rs != null && rs.next()) {
int max = rs.getInt(1);
if (max >= 0)
return max + 1;
}
System.out.println("获得最新的DepartmentID的sql:" + sql);
} catch (Exception e) {
e.printStackTrace();
System.out.println("获得最新的DepartmentID出错");
} finally {
DBUtil.close();
}
return 0;

}

@Override
public void addDepartment(Department department) {
int departmentID = maxDepartmentID();
String sql = "insert into department(department_i