自写的jdbc模板

浏览数：33 / 时间：2015年06月12日

实现增删改查：

AccountDao.java ---模板

package cn.lcp.dao;

import java.sql.SQLException;
import java.util.List;

import cn.lcp.domain.Account;
import cn.lcp.utils.BeanHandler;
import cn.lcp.utils.BeanListHandler;

public class AccountDao {

    public void add(Account account) throws SQLException{
        String sql = "insert into account(name,money) values(?,?)";
        Object params[] = {account.getName(),account.getMoney()};
        JdbcUtils.update(sql, params);
    }
    
    
    public void delete(int id) throws SQLException{
        String sql = "delete from account where id=?";
        Object params[] = {id};
        JdbcUtils.update(sql, params);
    }
    
    public void update(Account account) throws SQLException{
        
        String sql = "update account set name=?,money=? where id=?";
        Object params[] = {account.getName(),account.getMoney(),account.getId()};
        JdbcUtils.update(sql, params);
        
    }
    
    public Account find(int id) throws SQLException{
        String sql = "select * from account where id=?";
        Object params[] = {id};
        return (Account) JdbcUtils.query(sql, params, new BeanHandler(Account.class));
    }
    
    public List getAll() throws SQLException{
        String sql = "select * from account";
        Object params[] = {};
        return (List) JdbcUtils.query(sql, params,new BeanListHandler(Account.class));
    }
}

JdbcUtils.java ---模板

package cn.lcp.dao;

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

import cn.lcp.utils.ResultSetHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtils {
    
    private static ComboPooledDataSource ds = null;

    static{
        try{            
            ds = new ComboPooledDataSource("lcp");
            
        }catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }
    
    public static Connection getConnection() throws SQLException{
        
        return ds.getConnection();
    }
    
    public static void release(Connection conn,Statement st,ResultSet rs){
        
        
        if(rs!=null){
            try{
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;

        }
        if(st!=null){
            try{
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            
        }
        
        if(conn!=null){
            try{
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            
        }
    }
    

    //替换dao中的增删改方法
    public static void update(String sql,Object params[]) throws SQLException{
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        try{
            conn = getConnection();
            //预编译
            st = conn.prepareStatement(sql);
            for(int i=0;i<params.length;i++){
                st.setObject(i+1, params[i]);
            }
            st.executeUpdate();
            
        }finally{
            release(conn, st, rs);
        }
    }
    
    //替换所有dao中的查询   策略模式
    public static Object query(String sql,Object params[],ResultSetHandler rsh) throws SQLException{
        
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        
        try{
            conn = getConnection();
            st = conn.prepareStatement(sql);
            for(int i=0;i<params.length;i++){
                st.setObject(i+1, params[i]);
            }
            rs = st.executeQuery();
            //结果集处理器
            return rsh.handler(rs);
            
        }finally{
            release(conn, st, rs);
        }
    }
}

Account.java

package cn.lcp.domain;

public class Account {
    private int id;
    private String name;
    private float money;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getMoney() {
        return money;
    }
    public void setMoney(float money) {
        this.money = money;
    }
}

BeanHandler.java ---模板

package cn.lcp.utils;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class BeanHandler implements ResultSetHandler {
    private Class clazz;
    public BeanHandler(Class clazz){
        this.clazz = clazz;
    }
    public Object handler(ResultSet rs) {
        try{
            if(!rs.next()){
                return null;
            }
            Object bean = clazz.newInstance();
            
            ResultSetMetaData metadata = rs.getMetaData();
            int columnCount = metadata.getColumnCount();  //得到结果集中有几列数据
            for(int i=0;i<columnCount;i++){
                String coulmnName = metadata.getColumnName(i+1);  //得到每列的列名
                Object coulmnData = rs.getObject(i+1);
                
                Field f = clazz.getDeclaredField(coulmnName);//反射出类上列名对应的属性
                f.setAccessible(true);
                f.set(bean, coulmnData);
            }
            return bean;
        
        
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

BeanListHandler.java ---模板

package cn.lcp.utils;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

public class BeanListHandler implements ResultSetHandler {
    private Class clazz;
    public BeanListHandler(Class clazz){
        this.clazz = clazz;
    }
    
    public Object handler(ResultSet rs) {
        try{
            List list = new ArrayList();
            while(rs.next()){
                Object bean = clazz.newInstance();
                
                ResultSetMetaData  metadata = rs.getMetaData();
                int count = metadata.getColumnCount();
                for(int i=0;i<count;i++){
                    String name = metadata.getColumnName(i+1);
                    Object value = rs.getObject(name);
                    
                    Field f = bean.getClass().getDeclaredField(name);
                    f.setAccessible(true);
                    f.set(bean, value);
                }
                list.add(bean);
            }
            return list.size()>0?list:null;
        
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

ResultSetHandler.java ---模板

package cn.lcp.utils;

import java.sql.ResultSet;

public interface ResultSetHandler {
    public Object handler(ResultSet rs);
}

c3p0-config.xml ---模板

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
        <property name="user">root</property>
        <property name="password">root</property>
    
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
 -->    
    <named-config name="lcp">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
        <property name="user">root</property>
        <property name="password">lcp8090</property>
    
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
    
    <!--  
    <named-config name="oracle">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
        <property name="user">root</property>
        <property name="password">root</property>
    
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
    -->
</c3p0-config>

demo.java

package cn.lcp.demo;

import java.sql.SQLException;
import java.util.List;

import cn.lcp.dao.AccountDao;

public class Demo7 {

    /**
     * @param args
     * @throws SQLException 
     */
    public static void main(String[] args) throws SQLException {
        
        AccountDao dao = new AccountDao();
        List list = dao.getAll();
        System.out.println(list.size());
    }
}