java sqlite util

package org.stock;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class SqliteUtil {
public static void main(String[] args) {
SqliteUtil sqliteUtil = new SqliteUtil();
Connection conn = null;
ResultSet rs = null;
try {
conn = sqliteUtil.getConnection("c:/test/world2.db");
if (!sqliteUtil.tableExists(conn, "role1")) {
// sqliteUtil.exec(conn, "drop table if exists role; ");
System.out.println("table not found....");
sqliteUtil.exec(conn, "create table role1 (id int,name varchar(200));");
} else {
System.out.println("table found....");
}

/*
int j = 0;
List<String> list = new ArrayList<String>();
String sql = "insert into role values ";
String sql1 = "";
for (int i = 0; i < 10000; i++) {
sql1 += String.format("(%d, '%s'),", i, "姓名中文测试_" + i);
if (j > 10000) {
System.out.println(j);
sql1 = sql1.substring(0, sql1.length() - 1) + ";";
sqliteUtil.exec(conn, sql + sql1);
list.clear();
j = 0;
sql1 = "";
}
j++;
} */

Map<String, Object> params = new LinkedHashMap<String, Object>();

rs = sqliteUtil.getResult(conn, "select * from role limit 1, 100;");
/*
ResultSetMetaData meta = rs.getMetaData();
for (int i = 1; i <= meta.getColumnCount(); i++) {
System.out.println(meta.getColumnName(i));
System.out.println(meta.getColumnTypeName(i));
}*/

List<Map<String,Object>> list = sqliteUtil.toList(rs);

for(Map<String,Object> map:list) {
map.keySet().forEach(k->{
System.out.println(k+ "-->" +map.get(k));
});
}

while (rs.next()) { // 将查询到的数据打印出来
System.out.println("name = " + rs.getString("name") + " "); // 列属性一
}

} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
sqliteUtil.closeResultSet(rs);
sqliteUtil.closeConnection(conn);
}

System.out.println("i am ok~");
}

public Connection getConnection(String db) throws ClassNotFoundException, SQLException {

Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:" + db);
return conn;
}

public ResultSet getResult(Connection conn, String sql) throws SQLException {
Statement state = conn.createStatement();
ResultSet rs = state.executeQuery(sql); // 查询数据
return rs;
}

public void closeResultSet(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}

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

public ResultSet getResult(Connection conn, String sql, Map<String, ?> params) throws SQLException {

for (String key : params.keySet()) {
sql = sql.replace("{:" + key + "}", params.get(key).toString());
}
Statement state = conn.createStatement();
System.out.println(sql);
ResultSet rs = state.executeQuery(sql); // 查询数据
return rs;
}

public void exec(Connection conn, String sql, Map<String, ?> params) throws SQLException {
PreparedStatement state = conn.prepareStatement(sql);
int i = 0;
for (String key : params.keySet()) {
state.setObject(i, params.get(key));
i++;
}
state.execute(sql);
}

public void exec(Connection conn, String sql) throws SQLException {
PreparedStatement state = conn.prepareStatement(sql);
state.execute();
}

public void exec(Connection conn, List<String> list) throws SQLException {
Statement state = conn.createStatement();
for (String s : list) {
state.addBatch(s);
}
state.executeBatch();
}

public boolean tableExists(Connection conn, String tableName) throws SQLException {
String sql = "SELECT count(*) from sqlite_master where type='table' and name='" + tableName + "';";
ResultSet rs = getResult(conn, sql);
while (rs.next()) {
if (rs.getInt(1) == 1) {
return true;
}
}
rs.close();
return false;
}

@SuppressWarnings("unused")
private List<Map<String,Object>> toList(ResultSet rs) throws SQLException {
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while (rs.next()) {
Map<String,Object> rowData = new HashMap<String,Object>();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
return list;
}

}