欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

}

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数据库的创建与使用
h2数据库的创建与使用
h2数据库的创建与使用
最后倒入h2数据库的创建与使用

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