数据库连接池实例

简单数据库连接池实例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_id,department_name) values ('"
+ departmentID + "','" + department.departmentName + "') ";
try {
DBUtil.getPstmt(sql);
DBUtil.update();
System.out.println("添加部门sql:" + sql);
} catch (Exception e) {
e.printStackTrace();
System.out.println("添加部门出错");
} finally {
DBUtil.close();
}
}

}

六、编写添加部门的servlet：DepartmentAdd.java
package com.test.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.test.bean.Department;
import com.test.model.DepartmentDao;
import com.test.model.DepartmentDaoImpl;

public class DepartmentAdd extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String departmentName = request.getParameter("departmentName");
Department department = new Department();
department.departmentName= departmentName;
DepartmentDao departmentDao = new DepartmentDaoImpl();
departmentDao.addDepartment(department);
}

}

七、在web.xml中部署servlet
<servlet>
<servlet-name>DepartmentAdd</servlet-name>
<servlet-class>com.test.servlet.DepartmentAdd</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DepartmentAdd</servlet-name>
<url-pattern>/DepartmentAdd</url-pattern>
</servlet-mapping>

八、编写添加部门的jsp页面departmentadd.jsp
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>departmentadd</title>
</head>

<body>
<form action="DepartmentAdd" method="post">
部门名称：
<input type="text" name="departmentName" id="departmentName">
<input type="submit" value="提交">
</form>
</body>
</html>

九、注意：用tomcat部署，在conf文件下的context.xml文件改成如下：
name="jdbc/test"
url="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8"

十、在IE浏览器中输入如下网址即可输入并查看效果
http://localhost:8000/test/departmentadd.jsp