package com.qianwen.license.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
 * h2数据库
 */
public class H2Helper {
	private static final Logger logger = LoggerFactory.getLogger(H2Helper.class);
	static final String JDBC_URL = "jdbc:h2:file:./data/licensedb";// 文件形式的
	static final String DRIVER_CLASS = "org.h2.Driver";
	static final String USER = "root";
	static final String PASSWORD = "root";

	static {
		try {
			Class.forName(DRIVER_CLASS);
		} catch (ClassNotFoundException e) {
			logger.error("注册数据库驱动失败", e);
		}
	}
	// static Connection conn = null;
	// LOCAL_FILE_PATH 本地文件（上传失败的）,TRY_COUNT 重试次数
	static final String INSERT_SQL = "insert into FILE_FAIL (id,LOCAL_FILE_PATH,FTP_HOST,FTP_PORT,FTP_USER,FTP_PASSWORD,REMOTE_PATH) values (?,?,?,?,?,?,?)";

	public static Connection connectDb() {
		Connection conn = null;
		try {
			conn = java.sql.DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
		} catch (SQLException e) {
			logger.error("连接数据库失败", e);
			e.printStackTrace();
		}

		return conn;
	}

	/**
	 * 创建数据库内的表等对象
	 */
	public static void createDb() {
		Connection conn = null;
		try {
			conn = connectDb();
			Statement statement = conn.createStatement();

			//CN 名字与姓氏 OU 组织单位名称 O组织名称  L城市或区域名称 ST省/市/自治区名称 C双字母国家/地区代码
			// 创建表,密钥钥库
			statement.execute(
					"CREATE TABLE IF NOT EXISTS KEYSTORE (id BIGINT PRIMARY KEY, NAME VARCHAR(40) NOT NULL,CN VARCHAR(40) NOT NULL,OU VARCHAR(40),O VARCHAR(40) NOT NULL,L VARCHAR(50),ST VARCHAR(40),C CHAR(2))");

			// 关闭连接
			statement.close();
			
			logger.info("h2数据表创建完成");
		} catch (SQLException e) {
			logger.error("创建数据表失败", e);
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("关闭数据连接失败", e);
			}
		}
	}
}