com.paladin.sys.db.QueryHelper.java Source code

Java tutorial

Introduction

Here is the source code for com.paladin.sys.db.QueryHelper.java

Source

/**
 * 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.
 */
/**
 * OSChina  dbutils JDBC????
 *  QueryHelper  dbutils ??
 * ???????
 */
package com.paladin.sys.db;

import com.paladin.common.Tools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.lang3.ArrayUtils;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * ?  
 *
 * @author Winter Lau (http://my.oschina.net/javayou)
 * @modify Erhu
 */
@SuppressWarnings("unchecked")
public class QueryHelper {
    private final static QueryRunner RUNNER = new QueryRunner();
    private final static ColumnListHandler COLUMN_LIST_HANDLER = new ColumnListHandler() {
        @Override
        protected Object handleRow(ResultSet rs) throws SQLException {
            Object obj = super.handleRow(rs);
            if (obj instanceof BigInteger)
                return ((BigInteger) obj).longValue();
            return obj;
        }
    };
    private final static ScalarHandler scalar_handler = new ScalarHandler() {
        @Override
        public Object handle(ResultSet rs) throws SQLException {
            Object obj = super.handle(rs);
            if (obj instanceof BigInteger)
                return ((BigInteger) obj).longValue();
            return obj;
        }
    };
    /**
     * 
     */
    private final static List<Class<?>> PRIMITIVE_CLASSES = new ArrayList<Class<?>>() {
        private static final long serialVersionUID = 1L;

        {
            add(Long.class);
            add(Integer.class);
            add(String.class);
            add(java.util.Date.class);
            add(java.sql.Date.class);
            add(java.sql.Timestamp.class);
        }
    };

    private final static boolean IS_PRIMITIVE(Class<?> cls) {
        return cls.isPrimitive() || PRIMITIVE_CLASSES.contains(cls);
    }

    /**
     * ? ? 
     *
     * @return
     */
    public static Connection getConnection() {
        return DBManager.getConnection();
    }

    /**
     * ? ? 
     *
     * @param sql
     * @param params
     * @return
     */
    public static <T> T read(Class<T> beanClass, String sql, Object... params) {
        try {
            return (T) RUNNER.query(getConnection(), sql,
                    IS_PRIMITIVE(beanClass) ? scalar_handler : new BeanHandler(beanClass), params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.closeConnection();
        }
        return null;
    }

    /**
     *  
     *
     * @param <T>
     * @param beanClass
     * @param sql
     * @param params
     * @return
     */
    public static <T> List<T> query(Class<T> beanClass, String sql, Object... params) {
        try {
            return (List<T>) RUNNER.query(getConnection(), sql,
                    IS_PRIMITIVE(beanClass) ? COLUMN_LIST_HANDLER : new BeanListHandler(beanClass), params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.closeConnection();
        }
        return null;
    }

    /**
     *  
     *
     * @param <T>
     * @param beanClass
     * @param _sql
     * @param _currentPage
     * @param count
     * @param params
     * @return
     */
    public static <T> List<T> query_slice(Class<T> beanClass, String _sql, int _currentPage, int count,
            Object... params) {
        if (_currentPage < 0 || count < 0)
            throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
        int from = (_currentPage - 1) * count;
        count = (count > 0) ? count : Integer.MAX_VALUE;
        return query(beanClass, _sql + " LIMIT ?, ?", ArrayUtils.addAll(params, new Object[] { from, count }));
    }

    /**
     * ???
     *
     * @param sql
     * @param params
     * @return
     */
    public static long stat(String sql, Object... params) {
        try {
            Number num = (Number) RUNNER.query(getConnection(), sql, scalar_handler, params);
            return (num != null) ? num.longValue() : -1;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            DBManager.closeConnection();
        }
    }

    /**
     *  INSERT/UPDATE/DELETE ?
     *
     * @param sql
     * @param params
     * @return
     */
    public static int update(String sql, Object... params) {
        try {
            return RUNNER.update(getConnection(), sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.closeConnection();
        }
        return -1;
    }

    /**
     * ? SQL ?
     *
     * @param sql
     * @param params
     * @return
     */
    public static int[] batch(String sql, Object[][] params) {
        try {
            return RUNNER.batch(getConnection(), sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.closeConnection();
        }
        return null;
    }

    /**
     *  Spring  queryForList??
     *
     * @param _sql
     * @param _par
     * @return
     */
    public static List<Map<String, Object>> queryList(String _sql, Object... _par) {
        MapListHandler handler = new MapListHandler() {
            @Override
            protected Map<String, Object> handleRow(ResultSet __rs) throws SQLException {
                return getMapFromRs(__rs);
            }
        };
        try {
            return RUNNER.query(getConnection(), _sql, handler, _par);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.closeConnection();
        }
        return null;
    }

    /**
     * ?
     *
     * @param rs 
     * @return Map<String, String>  Map
     * @throws SQLException
     */
    private static Map<String, Object> getMapFromRs(final ResultSet rs) {
        Map<String, Object> t_map = new HashMap<String, Object>();
        int columnCount = 0;
        try {
            columnCount = rs.getMetaData().getColumnCount();// ?
            // ????
            for (int i = 0; i < columnCount; i++) {
                t_map.put(rs.getMetaData().getColumnName(i + 1).toUpperCase(),
                        Tools.null2String(rs.getString(i + 1)));// ??map
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return t_map;
    }

    /**
     * set autoCommit
     *
     * @param _auto
     */
    public static void setAutoCommit(boolean _auto) {
        try {
            getConnection().setAutoCommit(_auto);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * commit conn
     */
    public static void commit() {
        try {
            getConnection().commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * rollback
     */
    public static void rollback() {
        try {
            getConnection().rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}