jdbctemp.java

一个非常好的FRAMWRK!是一个外国组织做的!不!

/*
 * Copyright 2003-2005 the original author or authors.
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * 
 */
package com.jdon.model.query;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.jdon.util.DbUtil;

/**
 *  JDBC Template using this class, don't need write jdbc
 * operations.
 * 
 * @author <a href="mailto:[e-mail]">banq </a>
 *  
 */
public class JdbcTemp {
    private final static Logger logger = Logger.getLogger(JdbcTemp.class);

    private DataSource dataSource;

    private JdbcUtil jdbcUtil;

    public JdbcTemp(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcUtil = new JdbcUtil();
    }

    /**
     * get a single object from database
     * 
     * fit for this sql: select name from user where id=?
     * 
     * the "name" is single result
     * 
     * @param queryParams
     * @param sqlquery
     * @return
     * @throws Exception
     */
    public Object querySingleObject(Collection queryParams, String sqlquery)
            throws Exception {
        logger.debug("--> enter getSingleObject ");
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Object o = null;
        try {
            c = dataSource.getConnection();
            ps = c.prepareStatement(sqlquery,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            logger.debug(sqlquery);
            jdbcUtil.setQueryParams(queryParams, ps);

            rs = ps.executeQuery();
            if (rs.first()) {
                o = rs.getObject(1);
                logger.debug("-->in db found it:" + o.getClass().getName());
            }
        } catch (SQLException se) {
            throw new Exception("SQLException: " + se.getMessage());
        } catch (Exception ex) {
            logger.error(ex);
            throw new Exception(ex);
        } finally {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (c != null)
                c.close();
        }

        return o;
    }
        

    /**
     * this method is fit for this sql:
     * 
     * select id, name, password from user where id = ?
     * 
     * the " id, name, password " and its values are packed in a map of returen
     * list.
     * 
     * 
     * @param queryParams
     * @param sqlquery
     * @return
     * @throws Exception
     */
    public List queryMultiObject(Collection queryParams, String sqlquery)
            throws Exception {
        logger.debug("--> enter queryMultiObject ");
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List list = new ArrayList();
        try {
            c = dataSource.getConnection();
            ps = c.prepareStatement(sqlquery,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            logger.debug(sqlquery);
            jdbcUtil.setQueryParams(queryParams, ps);

            rs = ps.executeQuery();
            list = jdbcUtil.extract(rs);
        } catch (SQLException se) {
            throw new Exception("SQLException: " + se.getMessage());
        } catch (Exception ex) {
            logger.error(ex);
            throw new Exception(ex);
        } finally {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (c != null)
                c.close();
        }
        return list;
    }

    /**
     * same as queryMultiObject(Collection queryParams, String sqlquery)
     * but the result is a block result, the result'size is the value of count,
     * and the result's start poiny is the value of start.
     * 
     * @param queryParams
     * @param sqlquery
     * @param start
     * @param count
     * @return
     * @throws Exception
     */
    public List queryMultiObject(Collection queryParams, String sqlquery, int start, int count)
    throws Exception {
        logger.debug("--> enter queryMultiObject from:" + start + " size:" + count);
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List items = new ArrayList(count);
        boolean hasNext = false;
        try {
            c = dataSource.getConnection();
            DbUtil.testConnection(c);
            ps = c.prepareStatement(sqlquery,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            logger.debug(sqlquery);
            jdbcUtil.setQueryParams(queryParams, ps);
            rs = ps.executeQuery();
            if (DbUtil.supportsFetchSize)
                rs.setFetchSize(count);
            if (start >= 0 && rs.absolute(start + 1)) {               
                do {
                    items = jdbcUtil.extract(rs);
                } while ((hasNext = rs.next()) && (--count > 0));
            } 
        } catch (SQLException se) {
            throw new Exception("SQLException: " + se.getMessage());
        } catch (Exception ex) {
            logger.error(ex);
            throw new Exception(ex);
        } finally {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (c != null)
                c.close();
        }
        return items;
    }    
    
    /**
     * this method can used for insert/update/delete
     * @param insertParams
     * @param sqlinsert
     * @throws Exception
     */
    public void operate(Collection insertParams, String sqlinsert)
            throws Exception {
        logger.debug("--> enter getSingleObject ");
        Connection c = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Object o = null;
        try {
            c = dataSource.getConnection();
            ps = c.prepareStatement(sqlinsert);
            logger.debug(sqlinsert);
            jdbcUtil.setQueryParams(insertParams, ps);

            ps.executeUpdate();
        } catch (SQLException se) {
            throw new Exception("SQLException: " + se.getMessage());
        } catch (Exception ex) {
            logger.error(ex);
            throw new Exception(ex);
        } finally {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (c != null)
                c.close();
        }
    }

}