SQL query For List - Java java.sql

Java examples for java.sql:PreparedStatement

  1. HOME
  2. Java
  3. java.sql
  4. PreparedStatement

Description

SQL query For List

Demo Code


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.log4j.Logger;

public class Main{
    private static final Logger logger = Logger.getLogger(DBHelper.class);
    //from   w  w  w  . j a v  a 2  s .com
    public static List queryForList(String sql, Object[] params) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DAOFactory.getInstance().getConnection();
            stmt = conn.prepareStatement(sql);
            if (null != params && params.length > 0) {
                setStatementParameters(stmt, params);
            }
            rs = stmt.executeQuery();
            return resultSetToList(rs);
        } catch (SQLException e) {
            logger.error(e);
            throw new RuntimeException(e);
        } finally {
            DBHelper.closeConnection(conn, stmt, rs);
        }
    }
    
    public static void setStatementParameters(PreparedStatement pst,
            Object[] params) throws SQLException {
        for (int i = 0; i < params.length; ++i) {
            Object obj = params[i];
            if (null == obj) {
                pst.setNull(i + 1, Types.CHAR);
            } else if (obj instanceof java.sql.Date) {
                pst.setDate(i + 1, (java.sql.Date) obj);
            } else if (obj instanceof java.sql.Timestamp) {
                pst.setTimestamp(i + 1, (java.sql.Timestamp) obj);
            } else {
                pst.setObject(i + 1, obj);
            }
        }
    }
    
    private static List resultSetToList(ResultSet rs) throws SQLException {
        List result = new ArrayList();
        ResultSetMetaData metaData = rs.getMetaData();
        int count = metaData.getColumnCount();
        while (rs.next()) {
            Map resultMap = new HashMap();
            for (int i = 0; i < count; i++) {
                String columnName = metaData.getColumnName(i + 1);
                resultMap.put(columnName, rs.getObject(i + 1));
            }
            result.add(resultMap);
        }
        return result;
    }
    
    public static void closeConnection(Connection conn, Statement stmt,
            ResultSet rs) {
        try {
            if (null != rs) {
                rs.close();
            }
        } catch (SQLException e) {
            logger.error(e);
        }
        try {
            if (null != stmt) {
                stmt.close();
            }
        } catch (SQLException e) {
            logger.error(e);
        }
        try {
            if (null != conn) {
                if (!conn.isClosed()) {
                    conn.close();
                }
            }
        } catch (SQLException e) {
            logger.error(e);
        }
    }
}

Related Tutorials