h2数据库的创建与使用

程序员文章站 2022-03-30 10:56:16

话不多说直接上代码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;
    }

}

h2数据库的创建与使用

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);
        }
    }

 }

启动实例并创建表
h2数据库的创建与使用
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";

启动后可以通过浏览器对控制台进行访问
h2数据库的创建与使用

最后倒入

本文地址：https://blog.csdn.net/qq_42493452/article/details/107158030

上一篇：牛客SQL实战刷题记录（31-61题）

下一篇：全球首款三主摄旗舰！中兴Axon 30 Ultra真机曝光

h2数据库的创建与使用

Oracle数据库的备份与恢复

Android Studio 通过一个登录功能介绍SQLite数据库的使用

node.js中debug模块的简单介绍与使用

Flutter使用JsBridge方式处理Webview与H5通信的方法

详解Nginx服务器中map模块的配置与使用

C# ref引用参数与out输出参数的使用与区别

PySpark与GraphFrames的安装与使用环境搭建过程

JSP使用JDBC连接MYSQL数据库的方法

jsp中变量及方法的声明与使用

CSS的inherit与auto使用分析