Java SQL Query query(Connection connection, String sql, boolean isClose, Object... params)

Description

query

License

Apache License

Declaration

public static List<Map<String, Object>> query(Connection connection,
        String sql, boolean isClose, Object... params) 

Method Source Code

//package com.java2s;
//License from project: Apache License 

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Main {

    public static List<Map<String, Object>> query(Connection connection,
            String sql, boolean isClose, Object... params) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        PreparedStatement prepareStatement = null;
        try {//  www  . j  av a2  s. c  o  m
            sql = sql.replace("&lt;", "<");
            sql = sql.replace("&gt;", ">");
            sql = sql.replace("&amp;", "&");
            sql = sql.replace("&quot;", "\"");
            sql = sql.replace("&apos;", "'");
            prepareStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                if (param instanceof String) {
                    prepareStatement.setString(i + 1, (String) param);
                } else if (param instanceof Integer) {
                    prepareStatement.setInt(i + 1, (Integer) param);
                } else if (param instanceof Long) {
                    prepareStatement.setLong(i + 1, (Long) param);
                } else if (param instanceof Boolean) {
                    prepareStatement.setBoolean(i + 1, (Boolean) param);
                } else if (param instanceof java.sql.Date) {
                    prepareStatement.setDate(i + 1, (java.sql.Date) param);
                } else if (param instanceof Date) {
                    prepareStatement.setDate(i + 1, new java.sql.Date(
                            ((Date) param).getTime()));
                } else if (param instanceof Double) {
                    prepareStatement.setDouble(i + 1, (Double) param);
                } else if (param instanceof Byte) {
                    prepareStatement.setByte(i + 1, (Byte) param);
                } else if (param instanceof Short) {
                    prepareStatement.setShort(i + 1, (Short) param);
                }
            }
            ResultSet executeQuery = prepareStatement.executeQuery();
            ResultSetMetaData metaData = executeQuery.getMetaData();
            while (executeQuery.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnLabel = metaData.getColumnLabel(i);
                    Object value = null;
                    try {
                        value = executeQuery.getObject(columnLabel);
                    } catch (SQLException e) {
                        String message = e.getMessage();
                        if ("Value '0000-00-00' can not be represented as java.sql.Date"
                                .equals(message)) {
                            value = null;
                        }
                    }
                    map.put(columnLabel, value);
                }
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (prepareStatement != null) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (isClose) {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return null;
    }

    public static List<Map<String, Object>> query(Connection connection,
            String sql, Object... params) {
        return query(connection, sql, false, params);
    }

    public void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Related

1. executeStatement(Connection connection, String query)
2. getSqlQuery(Statement statement, Object[] args)
3. getStatement(Connection con, String query, Object... bits)
4. query(Connection conn, String sql)
5. query(Connection conn, String sql, Object[] params, Class beanClass)
6. queryForList(Connection conn, String sql, int limit)
7. queryObjectList(Connection con, String sql, Class objClass)
8. queryProjectTicketIdFromTicketId(Connection db, int ticketId)
9. queryReturnRS(String sql)