使用 JDBC 和 JavaTemplate 查询SQL语句返回 List>

使用JDBC执行sql语句返回List 类型:

public class JdbcUtil {
    private static Log log = LogFactory.getLog(JdbcUtil.class);
    public static Connection getConnection(String driverClassName, String url, String username, String password) {
        Connection connection = null;
        try {
            Class.forName(driverClassName);
            connection = DriverManager.getConnection(url, username, password);
            return connection;
        } catch (ClassNotFoundException e) {
            log.error("error message:" + e.getMessage());
            e.printStackTrace();
            return null;
        } catch (SQLException e) {
            log.error("error message:" + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    public static List<Map<String, Object>> execQuerySql(String driverClassName, String url, String username, String password, String sql) {
        Connection connection = JdbcUtil.getConnection(driverClassName, url, username, password);
        Statement statement = null;
        ResultSet rs = null;
        List<Map<String, Object>> rsList = null;
        try {
            //参考:http://www.sitesbay.com/jdbc/jdbc-scrollable-resultset.php
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = statement.executeQuery(sql);
            if (rs.getType() == ResultSet.TYPE_FORWARD_ONLY) {
                log.error("error message: ResultSet non-scrollable.");
                return null;
            }
            rs.last();
            int rsRows = rs.getRow();
            rsList = new ArrayList<>();
            for (int i = 0; i < rsRows; i++) {
                rs.absolute(i + 1);
                Map<String, Object> rsMaps = new HashMap<>();
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                for (int j = 0; j < columnCount; j++) {
                    String columnLable = resultSetMetaData.getColumnLabel(j + 1);
                    Object columnValue = rs.getObject(j + 1);
                    rsMaps.put(columnLable, columnValue);
                }
                rsList.add(rsMaps);
            }
            return rsList;
        } catch (SQLException e) {
            log.error("error message:" + e.getMessage());
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (statement != null) {
                    if (!statement.isClosed()) {
                        statement.close();
                    }
                }
                if (connection != null) {
                    if (!connection.isClosed()) {
                        connection.close();
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
} 

上面的代码问题是返回的Map中的键是英文全大写(如何解决?求解),例如:

 {"ID": 1,"USERNAME": "user"}

还可以使用JavaTemplate中封装的方法来执行sql语句返回List:

public class JdbcUtil{
    public static JdbcTemplate getJdbcTemplate(String driverClassName, String url, String username, String password) {
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource(url, username, password);
        driverManagerDataSource.setDriverClassName(driverClassName));
        JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
        return jdbcTemplate;
    }

    public static List<Map<String, Object>> execQuerySql(String driverClassName, String url, String username, String password, String sql) {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(driverClassName, url, username, password);
        List<Map<String, Object>> mpList = null;
        /*JdbcTemplate封装了三个返回List<Map<String, Object>>对象的方法,可以直接调用
        List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException;
        List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException;
        List<Map<String, Object>> queryForList(String sql) throws DataAccessException;
        */
        return mpList;
    }
}
上一篇:【CodeVS 5032】【省队集训2016 Day5 T1】Play with array


下一篇:03.Msbuild