Java JDBC Sqlite Connection query(String sql)

Description

query

License

Open Source License

Declaration

public static List<Map<String, Object>> query(String sql) 

Method Source Code

//package com.java2s;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.google.common.base.Strings;

public class Main {
    private static String default_db_name = "data";

    public static List<Map<String, Object>> query(String sql) {
        List<Map<String, Object>> list = null;
        Connection conn = getConnection(null);
        Statement st = null;/*from   w w w  . j ava 2 s  .co m*/
        ResultSet rs = null;
        if (conn != null) {
            try {
                st = conn.createStatement();
                rs = st.executeQuery(sql);
                if (rs != null) {
                    list = resultSet2List(rs);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                close(conn, st, rs);
            }
        }
        return list;
    }

    public static Connection getConnection(String dbFilePath) {
        Connection conn = null;
        try {
            if (dbFilePath == null || "".equals(dbFilePath.trim())) {
                dbFilePath = default_db_name;
            }
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    private static List<Map<String, Object>> resultSet2List(ResultSet rs) {
        List<Map<String, Object>> list = null;
        if (rs != null) {
            list = new ArrayList<Map<String, Object>>();
            try {
                while (rs.next()) {
                    Map<String, Object> map = resultSet2Map(rs);
                    if (map != null && map.size() > 0) {
                        list.add(map);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    private static void close(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Map<String, Object> resultSet2Map(ResultSet rs) {
        Map<String, Object> map = null;
        if (rs != null) {
            map = new HashMap<String, Object>();
            try {
                ResultSetMetaData rsMetaData = rs.getMetaData();
                int columnCount = rsMetaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String cName = rsMetaData.getColumnLabel(i);
                    if (!Strings.isNullOrEmpty(cName)) {
                        map.put(cName, rs.getString(cName));
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }
}

Related

1. getNewConnection(File dbFile)
2. getSenatorProp(String topicId)
3. getSQLiteConnection()
4. getUniqLabels()
5. modify(String sql)
6. removeLabel()
7. runSetupScript(final String setupScriptPath)