1.首先在Tomcat的conf/context.xml中作如下配置。
<Resource name="jdbc/mysqlds"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="root"
password="jakeyouxiang"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1/blog"/>
</Context>
2.在配置中引入mysql-connector-java-5.1.11-bin.jar和commons-dbutils-1.3.jar
3.创建一个得到连接的类DBHelper.java
package com.jxau;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
public class DBHelper {
public static QueryRunner getQueryRunner(){
DataSource ds=null;
try{
Context context=new InitialContext();
ds=(DataSource)context.lookup("java:/comp/env/jdbc/mysqlds");
}catch(Exception e){
e.printStackTrace();
System.out.println("加载数据库出错!!!");
}
QueryRunner qr=new QueryRunner(ds);
return qr;
}
}
4.在应用程序中调用DBHelper.java来操作数据库,如:
package com.jxau;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.dbutils.QueryRunner;
import com.jxau.*;
public class CateGoryServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
request.setCharacterEncoding("UTF-8");
String method=request.getParameter("method");
if(method.equals("add")){
this.add(request, response);
}else if(method.equals("delete")){
this.delete(request,response);
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
public void delete(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
}
public void add(HttpServletRequest request,HttpServletResponse response)
throws ServletException,IOException{
response.setCharacterEncoding("UTF-8");
String name=request.getParameter("name");
String level=request.getParameter("level");
String sql="insert into category(name,level) values(?,?)";
String params[]={name,level};
QueryRunner qr=DBHelper.getQueryRunner();
int result=0;
String message="";
try {
result=qr.update(sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
if(result==1){
message="博客添加分类成功";
}else{
message="博客添加分类失败";
}
request.setAttribute("message", message);
request.getRequestDispatcher("/add.jsp").forward(request, response);
}
}
5.查询的时候一般用以下的方式,它更体现了面向对象的思想。
List<Blog> list=(List<Blog>)qr.query(sql, new BeanListHandler(Blog.class));
Blog blog=(Blog)list.get(0);