h2数据库的创建与使用
程序员文章站
2022-09-14 14:22:32
话不多说直接上代码import java.sql.SQLException;import org.h2.tools.Server;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import gov.cnao.bigdata.drt.utils.Constants;public class H2DBServer { private final static Logger LOGGER = LoggerFactory.g...
话不多说直接上代码
import java.sql.SQLException;
import org.h2.tools.Server;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import gov.cnao.bigdata.drt.utils.Constants;
public class H2DBServer {
private final static Logger LOGGER = LoggerFactory.getLogger(H2DBServer.class);
public void start() {
try {
LOGGER.info("正在启动h2...");
Constants.H2DBSERVER = Server.createTcpServer(new String[] { "-tcp", "-tcpAllowOthers", "-tcpPort", "8044" }).start();
LOGGER.info("TCP Server 启动成功:" + Constants.H2DBSERVER.getStatus());
Server webServer = Server.createWebServer(new String[] { "-web", "-webAllowOthers" }).start();
LOGGER.info("web Server 启动成功:" + webServer.getStatus());
} catch (SQLException e) {
LOGGER.error("启动h2出错:" + e.toString());
}
}
public void stop() {
if (Constants.H2DBSERVER != null) {
LOGGER.info("正在关闭h2...");
Constants.H2DBSERVER.stop();
LOGGER.info("关闭成功.");
Constants.H2DBSERVER = null;
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Locale;
import gov.cnao.adp.security.DNSAddress;
import gov.cnao.bigdata.drt.utils.Constants;
import gov.cnao.bigdata.drt.utils.config.propertiesUtil;
public class JDBCConnFactory {
private static volatile JDBCConnFactory JDBCConnFactory;
private String os = System.getProperty("os.name");
private String usrHome = null;
private JDBCConnFactory() {}
public static JDBCConnFactory getInstance() {
if (JDBCConnFactory == null) {
synchronized (JDBCConnFactory.class) {
if (JDBCConnFactory == null) {
JDBCConnFactory = new JDBCConnFactory();
}
}
}
return JDBCConnFactory;
}
public Connection getConnection() throws SQLException {
return initDbConn();
}
private Connection initDbConn() {
// 判断操作系统
if (os.toLowerCase(Locale.ENGLISH).startsWith("win")) {
// Windows
usrHome = System.getProperty("user.home");
} else {
// linux
usrHome = Constants.LINUX_PATH;
}
try {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:tcp://"+DNSAddress.getHostAddress(DNSAddress.getLocalHost())+":8044/"+usrHome+"/h2db/drtdb", propertiesUtil.prop.getProperty("H2_USERNAME"), propertiesUtil.prop.getProperty("H2_PWD"));
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.eclipse.core.runtime.IProgressMonitor;
import org.eclipse.jface.operation.IRunnableWithProgress;
import org.eclipse.swt.widgets.Shell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import gov.cnao.bigdata.drt.domain.DateFiledir;
import gov.cnao.bigdata.drt.domain.Upgrade;
import gov.cnao.bigdata.drt.domain.UploadDir;
import gov.cnao.bigdata.drt.utils.Constants;
import gov.cnao.bigdata.drt.utils.DialogUtils;
/**
* JDBC处理类
*
* @author jiaoruisong
* 2020/2/5
*/
public class JDBCProcessor {
private static final Logger LOG = LoggerFactory.getLogger(JDBCProcessor.class);
private static QueryRunner runner;
private static volatile JDBCProcessor JDBCProcessor;
protected static Shell shell;
private static List<DateFiledir> DateFiledir_Info;
public static List<UploadDir> list;
private JDBCProcessor() {}
public static JDBCProcessor getInstance() {
if (JDBCProcessor == null) {
synchronized (JDBCProcessor.class) {
if (JDBCProcessor == null) {
JDBCProcessor = new JDBCProcessor();
try {
// 初始化连接池
JDBCConnFactory.getInstance().getConnection();
runner = new QueryRunner();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
return JDBCProcessor;
}
public Connection getConnection() {
try {
return JDBCConnFactory.getInstance().getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public JSONArray doQuery(String sql) throws SQLException {
JSONArray array = null;
Connection conn = getConnection();
try {
array = runner.query(conn, sql, new ResultSetHandler<JSONArray>() {
@Override
public JSONArray handle(ResultSet rs) throws SQLException {
return resultSetToJson(rs);
}
});
} catch (Exception e) {
e.printStackTrace();
} finally {
conn.close();
}
return array;
}
public Integer doUpdate(String sql, Object[] params) throws SQLException {
int update = 0;
Connection conn = getConnection();
try {
update = runner.update(conn, sql, params);
} catch (Exception e) {
e.printStackTrace();
} finally {
conn.close();
}
return update;
}
public Integer doExecute(String sql, Object[] params) throws SQLException {
int execute = 0;
Connection conn = getConnection();
try {
execute = runner.execute(conn, sql, params);
} catch (Exception e) {
e.printStackTrace();
} finally {
conn.close();
}
return execute;
}
/**
* 创建数据库表
* @throws SQLException
*/
public void createTable(String sql) throws SQLException {
Connection conn = getConnection();
try {
runner.execute(conn, sql, new Object[] {});
LOG.info("创建数据库表成功.");
} catch (SQLException e) {
LOG.error("创建数据库表失败:{}", e.getMessage(), e);
} finally {
conn.close();
}
}
/**
* 返回JSON
*
* @param rs
* @return
* @throws SQLException
*/
private JSONArray resultSetToJson(ResultSet rs) throws SQLException {
JSONArray array = new JSONArray();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnLabel(i);
String value = rs.getString(columnName) == null ? "" : rs.getString(columnName);
jsonObj.put(columnName, value);
}
array.add(jsonObj);
}
return array;
}
//查询案例
/**
* 按任务名称、id查询正常的任务
* @param taskName
* @return
*/
public List<UploadDir> findfileProgress(String taskId,String taskName){
List<UploadDir> list=new ArrayList<UploadDir>();
try {
String sql = "SELECT * FROM "+Constants.tableName+" WHERE TASKID = '"+taskId+"' AND TASKNAME ='"+taskName+"' AND STATUS IN ('STATUS1','STATUS2','STATUS3','STATUS7') AND STOPSTATUS =''";
JSONArray array = doQuery(sql);
if(array != null && array.size() > 0) {
for(Object obj : array) {
JSONObject o = (JSONObject) obj;
UploadDir dir=new UploadDir();
dir = JSONObject.parseObject(o.toString(),UploadDir.class);
list.add(dir);
}
}
} catch (SQLException e) {
LOG.error("按任务查询历史报送文件记录失败:{}", e.getMessage(), e);
}
return list;
}
//修改案例
/**
* 更新文件移动id
* @param dir
* @return
*/
public void updateExchangeid(UploadDir dir) {
try {
String sql ="UPDATE "+Constants.tableName+" SET EXCHANGEID = '"+ dir.getExchangeId() +"' WHERE ID ='"+dir.getId()+"' ";
doUpdate(sql,null);
} catch (SQLException e) {
LOG.error("更新文件移动id:{}", e.getMessage(), e);
}
}
//添加案例
/**
* 插入文件夹对应的数据文件记录
*/
public void insertDatefile(DateFiledir datedir) {
try {
String uuid=UUID.randomUUID().toString().replace("-", "").substring(0,32);
String sql ="INSERT INTO DRT_FILE_INFO (ID,FOLDERNAME,DATEFILENAME,TASKNAME,TASKID,FOLDERUPLOADID) VALUES('"+uuid+"', '"+datedir.getFolderName()+"','"+datedir.getDateFileName()+"','"+datedir.getTaskName()+"','"+datedir.getTaskId()+"','"+datedir.getFolderuploadId()+"') ";
doExecute(sql, null);
LOG.info("添加文件夹数据文件信息成功.");
} catch (SQLException e) {
LOG.error("添加文件夹数据文件信息失败:{}", e.getMessage(), e);
}
}
}
启动实例并创建表
Constants类内容
/**
* H2数据库Server
*/
public static Server H2DBSERVER = null;
/**
* 创建H2数据库文件上传进度表
*/
public static String TABLE_CREATE="CREATE TABLE IF NOT EXISTS DRT_FOLDERUPLOAD_RECORD (id VARCHAR(100) PRIMARY KEY ,userId VARCHAR(100), taskName VARCHAR(100),"
+ "folderName VARCHAR(300), exchangeId VARCHAR(100), drtProgress VARCHAR(100), createDate TIMESTAMP,taskId VARCHAR(100),AntiVirusDate TIMESTAMP,userName VARCHAR(100),status VARCHAR(100),remark VARCHAR(100),uniquemark VARCHAR(100),AntiVirusName VARCHAR(100),AntiVirusPath VARCHAR(100),StopStatus VARCHAR(100))";
//数据库上传进度表名
public static String tableName = "DRT_FOLDERUPLOAD_RECORD";
启动后可以通过浏览器对控制台进行访问
最后倒入
本文地址:https://blog.csdn.net/qq_42493452/article/details/107158030
上一篇: java连接操作mysql
下一篇: mysql 优化方案,纯干活
推荐阅读
-
在网站SEO优化中nofollow标签的使用与外部链接
-
OC可变数组的创建与简单应用
-
创建基于ASP.NET core 3.1 的RazorPagesMovie项目(一)-创建和使用默认的模板
-
flask与Flask-CORS的使用
-
flask 与 flask_migrate的使用
-
Laravel框架使用monolog_mysql实现将系统日志信息保存到mysql数据库的方法
-
使用Docker registry镜像创建私有仓库的方法
-
PHP5中使用PDO连接数据库的方法
-
MySQL数据库中CAST与CONVERT函数实现类型转换的讲解
-
Three.js使用THREE.TextGeometry创建三维文本中文乱码的问题如何解决?