Java SQL Type readMapListBySQL(String fileDir, String sql, List headers, List headerTypes)

Description

read Map List By SQL

License

Apache License

Declaration

public static List<Map<String, Object>> readMapListBySQL(String fileDir, String sql, List<String> headers,
            List<String> headerTypes) throws Exception 

Method Source Code

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

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class Main {
    public static List<Map<String, Object>> readMapListBySQL(String fileDir, String sql, List<String> headers,
            List<String> headerTypes) throws Exception {
        List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();

        StringBuilder headerLine = new StringBuilder();
        StringBuilder headerLineTypes = new StringBuilder();
        int headerSize = headers.size();
        for (int i = 0; i < headerSize - 1; i++) {
            headerLine.append(headers.get(i));
            headerLine.append(',');
            headerLineTypes.append(headerTypes.get(i));
            headerLineTypes.append(',');
        }/*from   w  w  w  . j a v a2s.  c  o  m*/
        headerLine.append(headers.get(headerSize - 1));
        headerLineTypes.append(headerTypes.get(headerSize - 1));

        Properties props = new java.util.Properties();
        props.put("headerline", headerLine);
        props.put("suppressHeaders", "false");
        props.put("fileExtension", ".csv");
        props.put("charset", "UTF-8");
        props.put("commentChar", "#");
        props.put("columnTypes", headerLineTypes);
        props.put("timeFormat", "HH:mm:ss");
        props.put("dateFormat", "yyyy-MM-dd");
        props.put("trimHeaders", "true");
        props.put("trimValues", "true");

        Class.forName("org.relique.jdbc.csv.CsvDriver");
        Connection conn = DriverManager.getConnection("jdbc:relique:csv:" + fileDir, props);
        Statement stmt = conn.createStatement();
        ResultSet results = stmt.executeQuery(sql);

        Map<String, Object> map = null;
        ResultSetMetaData resultMeta = results.getMetaData();
        int resultFieldCount = resultMeta.getColumnCount();
        while (results.next()) {
            map = new HashMap<String, Object>();
            for (int i = 1; i < resultFieldCount + 1; i++) {
                map.put(resultMeta.getColumnLabel(i), results.getObject(i));
            }
            lists.add(map);
        }

        results.close();
        stmt.close();
        conn.close();
        return lists;
    }
}

Related

1. newArrayOfType(int typeCode, int size)
2. newInstance(Class clazz, Object... params)
3. parse(String txt, int level)
4. prettyPrint(Object obj)
5. readAscii(Clob clob, String defaultValue)
6. readObject(DataInput in, int sqlType)
7. resolveEscapes(String escaped, boolean noBackslashEscapes)
8. setFieldValue(Object oBean, String sFieldName, Object oValue)
9. setProperty(Object object, String name, String value)