使用开源C3p0+dbutils开发（小总结）

ゝ一纸荒年。 2022-07-11 08:26 139阅读 0赞

c3p0是一个开源的连接池，实现了数据源和JNDI绑定，随时取用，平均每次取用只需要10-20毫秒，所以如果是很多客户端并发随机访问数据库的话，使用连接池的效率会高，至于DbUtils只是一个为简化JDBC操作的小类库，主要方法：

public Object query():执行查询操作，至于里面的参数等具体的百度吧  
public int update():用来执行一个更新（插入、更新或删除）操作。  
**以上是copy的。。。接下来干货**

如果使用c3p0连接池必须引入：  
c3p0包：c3p0-0.9.1.2.jar  
dbutils包：commons-dbutils-1.6.jar  
MySQL驱动包：mysql-connector-java-5.1.28-bin.jar  
日志包：log4j-1.2.11.jar等你需要的包  
具体如下  
![这里写图片描述][SouthEast]

使用c3p0必须创建文件c3p0-config.xml里面写一些配置以下是我的通用的。。默认Oracle，如果用MySQL则写连接的时候指定

<c3p0-config>
    
    <default-config>
        <property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521:orcl</property>
        <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
        <property name="user">scott</property>
        <property name="password">scott</property>
        <property name="initialPoolSize">3</property>  
         <property name="maxIdleTime">30</property> 
         <property name="maxPoolSize">100</property> 
         <property name="minPoolSize">10</property> 
    </default-config>
    
    <named-config name="mysql"> 
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/person</property>
            <property name="driverClass">com.mysql.jdbc.Driver</property>
            <property name="user">root</property>
            <property name="password">root</property>
            <property name="initialPoolSize">3</property>  
              
            <property name="maxIdleTime">30</property>  
              
            <property name="maxPoolSize">20</property>  
              
            <property name="minPoolSize">3</property>  
             
            <property name="maxIdleTimeExcessConnections">15</property>
    </named-config>
    
    </c3p0-config>

数据库连接部分： 指定数据源的语句：  
ComboPooledDataSource dp = new ComboPooledDataSource(“mysql”);

写个main函是为了测试数据库连接是否正确，避免以后调试程序出现不应该出现的错误

package jdbc;
    
    import org.apache.commons.dbutils.QueryRunner;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    public class jdbc {
        
        public static void main(String[] args) {
            System.out.print(jdbc.getQuery());
        }
        private static ComboPooledDataSource dp = new ComboPooledDataSource("mysql");
    
    
        public static QueryRunner getQuery(){
            return new QueryRunner(dp);
        }
    }
    ``
    Dao层：

package dao;  
import java.util.List;  
import jdbc.jdbc;  
import org.apache.commons.dbutils.QueryRunner;  
import org.apache.commons.dbutils.handlers.BeanHandler;  
import org.apache.commons.dbutils.handlers.BeanListHandler;  
import value.person;

public class dao \{  
public static QueryRunner qr=jdbc.getQuery();  
/\*\*  
\* 增加操作  
\* @param p  
\* @return  
\*/  
public boolean addperson(person p)\{  
String sql=”insert into person(name,age,sex) values(?,?,?)”;  
try\{  
int i = qr.update(sql,p.getName(),p.getAge(),p.getSex());  
if(i==0)\{  
return false;  
\}  
\}catch(Exception e)\{  
e.printStackTrace();  
\}  
return true;  
\}  
/\*\*  
\* 删除操作  
\* @param id  
\* @return  
\*/  
public boolean delperson(int id)\{  
String sql=”delete from person where id=?”;  
try\{  
int i = qr.update(sql,id);  
if(i==0)\{  
return false;  
\}  
\}catch(Exception e)\{  
e.printStackTrace();  
\}  
return true;  
\}  
/\*\*  
\* 更新操作  
\* @param p  
\* @return  
\*/  
public boolean alterperson(person p)\{  
String sql=”update person set name=?,age=?,sex=? where id=?”;  
try\{  
int i = qr.update(sql,p.getName(),p.getAge(),p.getSex(),p.getId());  
if(i==0)\{  
return false;  
\}  
\}catch(Exception e)\{  
e.printStackTrace();  
\}  
return true;  
\}  
/\*\*  
\* 按ID查找  
\* @param id  
\* @return  
\*/  
public person getpersonByid(int id)\{

String sql="select from person where id=?";
        person p=null;
        try{
            p = qr.query(sql,new BeanHandler<person>(person.class),id);
    
        }catch(Exception e){
            e.printStackTrace();
        }
        return p;
    }
    /**
     * 查找所有
     * @return
     */
    public List<person> selectAllperson(){
        List<person> lp=null;
        String sql="select *from person ";
        try{
            lp=qr.query(sql, new BeanListHandler<person>(person.class));
    
        }catch(Exception e){
            e.printStackTrace();
        }
        return lp;
    }
    /**
     * 模糊查询
     * @param name
     * @param sex
     * @return
     */
    public List<person> findBykey(String name,String sex) {
        List<person> lp=null;
        String sql="select *from person where name like ? and sex like ?";
        try{
    
            lp=qr.query(sql,new BeanListHandler<person>(person.class),"%"+name+"%","%"+sex+"%");
    
        }catch(Exception e){
            e.printStackTrace();
        }
        return lp;
    }

servlet层：主要是截取url分发操作，随着使用框架多了，一些基本操作感觉生疏了，记一下嘎嘎

package servlet;

import java.io.IOException;  
import java.util.List;

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

import org.json.JSONArray;

import value.person;  
import dao.dao;

public class personServlet extends HttpServlet\{

public static dao dao=new dao();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        /*String m=req.getParameter("m");
        if("add".equals(m)){
            this.addperson(req, resp);
        }*/
        String url = req.getRequestURL().toString();
        if (url.endsWith("addperson")) {
            this.addperson(req, resp);
        }else if(url.endsWith("findBykey")){
            this.findBykey(req,resp);
        }else if(url.endsWith("selectAll")){
            this.selectAll(req,resp);
        }else if(url.endsWith("del")){
            this.del(req,resp);
        }
    }
    
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        this.doGet(req, resp);
    }
    
    protected void addperson(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
            String name1=req.getParameter("name");  
            String age=req.getParameter("age");
            String sex=req.getParameter("sex");
            String name=new String (name1.getBytes("iso-8859-1"),"utf-8");
    
            person p=new person(name,Integer.valueOf(age),sex);
            dao.addperson(p);
            resp.getWriter().print("增加成功");
    }

“\`  
基本上就是这些了 忘记了回来看看，嘎嘎嘎

[SouthEast]: /images/20220711/42edba241f194775b4ce41c80fe5c610.png