在tomcat中配置mysql資料庫連接配接池的方法及第三方對資料庫的封裝：dbutils

你可在每個web工程目錄下的META-INF\context.xml檔案中.

context.xml檔案中的内容可以這樣寫:

<Context>

<Resource  name="jdbc/mysqlds"

 auth="Container"

 type="javax.sql.DataSource" 

 maxActive="100" 

 maxIdle="100"

 maxWait="5000"  

 username="root"  

 password="root"

 driverClassName="com.mysql.jdbc.Driver" 

 url="jdbc:mysql://127.0.0.1/liuwei"

 />

</Context>

 package cn.com.jobedu.blog;

import java.io.IOException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import javax.naming.InitialContext;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

public class BlogServlet extends HttpServlet {

 private static final long serialVersionUID = 1L;

 public void doGet(HttpServletRequest request, HttpServletResponse response)

   throws ServletException, IOException {

  doPost(request, response);

 }

 public void doPost(HttpServletRequest request, HttpServletResponse response)

   throws ServletException, IOException {

  request.setCharacterEncoding("UTF-8");

  System.out.println("dopost");

  String title = request.getParameter("title");

  String content = request.getParameter("content");

  String categoryId = request.getParameter("category");

  DataSource ds = null;

  try {

   // 通過在context.xml檔案，設定的資料源對象的名字，擷取資料源對象

   InitialContext context = new InitialContext();

   ds = (DataSource) context.lookup("java:/comp/env/jdbc/mysqlds");

  } catch (Exception e) {

   System.out.println("擷取資料源時出錯");

  }

  int result = 0;

  try {

   Connection conn = ds.getConnection();

   String sql = "insert into blog (title, content,category_id,createdtime) values (?, ?, ?, now())";

   PreparedStatement pstmt = conn.prepareStatement(sql);

   pstmt.setString(1, title);

   pstmt.setString(2, content);

   pstmt.setInt(3, Integer.parseInt(categoryId));

   result = pstmt.executeUpdate();

   System.out.println(result);

  } catch (SQLException e) {

   e.printStackTrace();

  }

  String message = "";

  if (result == 1) {

   message = "添加博文成功";

  } else {

   message = "添加博文失敗";

  }

  request.setAttribute("message", message);

  request.getRequestDispatcher("/addBlogResult.jsp").forward(request,

    response);

 }

}

第三方對資料庫的封裝：dbutils

package cn.com.jobedu.blog;

import java.io.IOException;

import java.sql.Connection;

import java.sql.SQLException;

import javax.naming.InitialContext;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;

public class BlogServlet 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 {

  request.setCharacterEncoding("UTF-8");

  String title = request.getParameter("title");

  String content = request.getParameter("content");

  String categoryId = request.getParameter("category");

  System.out.println(title);

  System.out.println(content);

  System.out.println(categoryId);

  DataSource ds = null;

  try {

   // 通過在context.xml檔案，設定的資料源對象的名字，擷取資料源對象

   InitialContext context = new InitialContext();

   ds = (DataSource) context.lookup("java:/comp/env/jdbc/mysqlds");

  } catch (Exception e) {

   System.out.println("擷取資料源時出錯");

  }

  int result = 0;

  try {

   String sql = "insert into blog (title, content,category_id,createdtime) values (?, ?, ?, now())";

   QueryRunner qr = new QueryRunner(ds);

   String parmas[] = { title, content, categoryId };

   result = qr.update(sql, parmas);

   System.out.println(result);

  } catch (SQLException e) {

   e.printStackTrace();

  }

  String message = "";

  if (result == 1) {

   message = "添加博文成功";

  } else {

   message = "添加博文失敗";

  }

  request.setAttribute("message", message);

  request.getRequestDispatcher("/addBlogResult.jsp").forward(request,

    response);

 }

}