Java SQL Query executeSafeQuery( Connection conn, String sql)

Description

execute a query and return the result as a list of rows, each row is represented as column_name->column_value map.

License

Open Source License

Parameter

Parameter	Description
conn	a parameter
sql	a parameter

Exception

Parameter	Description
SQLException	an exception

Declaration

public static List<Map<String, String>> executeSafeQuery(
        Connection conn, String sql) throws SQLException 

Method Source Code

//package com.java2s;
//License from project: Open Source License 

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import java.sql.*;
import java.util.List;
import java.util.Map;

public class Main {
    /**/*from  w  w  w .  j  a  v  a 2  s  .co  m*/
     * execute a query and return the result as a list of rows, each row is represented
     * as column_name->column_value map. To against SQL-injection attack, the input sql
     * must be safe, which normally means it's not constructed from user input.
     *
     * @param conn
     * @param sql
     * @return
     * @throws SQLException
     */
    public static List<Map<String, String>> executeSafeQuery(
            Connection conn, String sql) throws SQLException {
        List<Map<String, String>> rows = Lists.newArrayList();

        Statement stmt = null;
        ResultSet rs = null;

        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            ResultSetMetaData md = rs.getMetaData();
            final int numCols = md.getColumnCount();

            while (rs.next()) {
                Map<String, String> row = Maps.newHashMap();
                for (int i = 1; i <= numCols; ++i) {
                    row.put(md.getColumnLabel(i), rs.getString(i));
                }
                rows.add(row);
            }

        } finally {
            free(stmt, rs);
        }

        return rows;
    }

    public static void free(Statement stmt, ResultSet rs)
            throws SQLException {
        if (stmt != null) {
            stmt.close();
        }

        if (rs != null) {
            rs.close();
        }
    }
}

Related

1. executeQuery(String query)
2. executeQuery(String sql, Connection conn, List