使用场景：
简单脚本，但是有需要数据记录． （使用前升级下版本）
我的使用： 老机器，老项目，jkd6, 需要记录
SqlLiteUtils
package com.icil.edi.listener;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/***************************
*<pre>
* @Project Name : edi-kafka-service
* @Package : com.icil.edi.listener
* @File Name : SqlLiteUtils
* @Author : Sea
* @Date : 12/14/22 3:53 PM
* @Purpose :
* @History :
*</pre>
* <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
* <dependency>
* <groupId>org.xerial</groupId>
* <artifactId>sqlite-jdbc</artifactId>
* <version>3.21.0</version>
* </dependency>
***************************/
public class SqlLiteUtils {
static Connection connection = null;
public static Connection getConnection() throws Exception {
if(connection==null||connection.isValid(15)){
synchronized (SqlLiteUtils.class){
if(connection==null||connection.isValid(15)){
Class.forName("org.sqlite.JDBC");
connection = DriverManager.getConnection("jdbc:sqlite:FileCopy.db");
// connection = DriverManager.getConnection("jdbc:sqlite:/home/sealiu/FileCopy.db");
connection.setAutoCommit(true);}
}
}
return connection;
}
/**
* insert update delete
* table tb_file_name : id fileName createTs
* @param sql
* @throws Exception
*/
public static void doUpdate(String sql){
try
{
Connection connection = getConnection();
Statement statement = connection.createStatement();
// statement.executeUpdate("CREATE TABLE IF NOT EXISTS tb_file_name (createTs integer, name string)");
// statement.executeUpdate("insert into tb_file_name values(System.currentTimeMillis(), 'Sea')");
statement.executeUpdate(sql);
}catch (Exception e)
{
e.printStackTrace();
System.err.println("exec sql "+ sql + "exp and exception: " + e);
}
}
public static List<Map> doQuery(String sql){
try
{
Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
// ResultSet rs = statement.executeQuery("select * from person");
ResultSet rs = statement.executeQuery(sql);
List<Map> maps = convertList(rs);
return maps;
}catch (Exception e)
{
e.printStackTrace();
System.err.println("exec sql "+ sql + "exp and exception: " + e);
}
return null;
}
// 将查询结果resultset转换为List<Map>
private static List<Map> convertList(ResultSet rs) throws SQLException{
List<Map> list = new ArrayList<Map>();
ResultSetMetaData md = rs.getMetaData();//获取键名
int columnCount = md.getColumnCount();//获取列的数量
while (rs.next()) {
Map<String,Object> rowData = new HashMap<String,Object>();//声明Map
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
}
list.add(rowData);
}
return list;
}
// @Test
public static void main(String[] args) throws Exception
{
// String schema= "CREATE TABLE IF NOT EXISTS tb_file_name (createTs Long, name string ,primary key(name))";
String schema= "CREATE TABLE IF NOT EXISTS tb_file_name (createTs Long, name string )";
String insertSql= "insert into tb_file_name values(3, 'Sea')";
String query= "select * from tb_file_name where createTs>2";
String delete= "DELETE FROM tb_file_name WHERE createTs=2 ";
doUpdate(schema);
doUpdate(insertSql);
// doUpdate(delete);
List<Map> maps = doQuery(query);
System.err.println(maps);
}
}
H2DbUtils:
package com.icil.edi.listener;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/***************************
*<pre>
* @Project Name : edi-kafka-service
* @Package : com.sea.edi.listener
* @File Name : H2DBUtils
* @Author : Sea
* @Date : 12/15/22 3:31 PM
* @Purpose :
* @History :
* <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
* <dependency>
* <groupId>com.h2database</groupId>
* <artifactId>h2</artifactId>
* <version>1.4.187</version>
* </dependency>
*</pre>
***************************/
public class H2DBUtil {
static Connection connection = null;
public static Connection getConnection() throws Exception {
if(connection==null||connection.isValid(15)){
synchronized (SqlLiteUtils.class){
if(connection==null||connection.isValid(15)){
// 加载H2数据库驱动
Class.forName("org.h2.Driver");
final String USER = "root";
String PASSWORD = "root";
final String JDBC_URL = "jdbc:h2:/home/sea/Desktop/DMS/db/as";
// 根据连接URL，用户名，密码获取数据库连接
connection = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
connection.setAutoCommit(true);}
}
}
return connection;
}
/**
* insert update delete
* @param sql
* @throws Exception
*/
public static void doUpdate(String sql){
try
{
Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
}catch (Exception e)
{
e.printStackTrace();
System.err.println("exec sql "+ sql + "exp and exception: " + e);
}
}
public static List<Map> doQuery(String sql){
try
{
Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.setQueryTimeout(30); // set timeout to 30 sec.
// ResultSet rs = statement.executeQuery("select * from person");
ResultSet rs = statement.executeQuery(sql);
List<Map> maps = convertList(rs);
return maps;
}catch (Exception e)
{
e.printStackTrace();
System.err.println("exec sql "+ sql + "exp and exception: " + e);
}
return null;
}
// 将查询结果resultset转换为List<Map>
private static List<Map> convertList(ResultSet rs) throws SQLException{
List<Map> list = new ArrayList<Map>();
ResultSetMetaData md = rs.getMetaData();//获取键名
int columnCount = md.getColumnCount();//获取列的数量
while (rs.next()) {
Map<String,Object> rowData = new HashMap<String,Object>();//声明Map
for (int i = 1; i <= columnCount; i++) {
rowData.put((""+md.getColumnName(i)).toLowerCase(), rs.getObject(i));//获取键名及值
}
list.add(rowData);
}
return list;
}
// @Test
public static void main(String[] args) throws Exception
{
// String schema="CREATE TABLE IF NOT EXISTS tb_file_name(name VARCHAR(70) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4))";
String schema="CREATE TABLE IF NOT EXISTS tb_file_name(name VARCHAR(70),cts BIGINT,times INT)";
String insertSql= "insert into tb_file_name values('Sea',312421412,3)";
String query= "select * from tb_file_name";
String delete= "DELETE FROM tb_file_name WHERE times=2 ";
doUpdate(schema);
doUpdate(insertSql);
doUpdate(delete);
List<Map> maps = doQuery(query);
System.err.println(maps);
}
}
标签：rs,h2,utils,List,connection,static,statement,sql,sqllite From： https://www.cnblogs.com/lshan/p/16985443.html