有关hsql处理文本数据库的笔记
fdsaalert();
package com.nial.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtils {/** 数据库连接URL */ private String url;/** 管理员名称 */ private String user = "sa";/** 管理员密码 */ private String password = "";/** 临时表名 */ private String m_name = "cc"; /** 建表语句 */ private String m_columnSpec = "ID INTEGER PRIMARY KEY, caller INTEGER,called INTEGER, connected varchar(10) ,beginTime DATE,endTime DATE,inPhyChannel INTEGER,outPhyChannelINTEGER,callType INTEGER,outType INTEGER,timeLengthINTEGER";/** 字段分隔符 */ private String m_separatorSpec = "\\t"; /** 文本数据库链接 */ private Connection connection;/** 数据文件的位置和名字 */ private String filePathAndName; DBUtils(String filePathAndName) { this.setFilePathAndName(filePathAndName); } public String getFilePathAndName() {return filePathAndName;}public void setFilePathAndName(String filePathAndName) {this.filePathAndName = filePathAndName;}/** * 数据库启动 * @throws SQLException */protected void setUp() throws SQLException {url = "jdbc:hsqldb:file:test;sql.enforce_strict_size=true";try {Class.forName("org.hsqldb.jdbcDriver");} catch (Exception e) {e.printStackTrace();System.out.println(this + ".setUp() error: " + e.getMessage());}connection = getConnection();createTable(connection);}/** * 创建一个链接 * @return * @throws SQLException */private Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}/** * 创建一个临时表 * @param connection * @param filePathAndName * @throws SQLException */private void createTable(Connection connection) throws SQLException {String createTable = "DROP TABLE \"" + m_name + "\" IF EXISTS;";createTable += "CREATE TEXT TABLE \"" + m_name + "\" ( " + m_columnSpec + " );";connection.createStatement().execute(createTable);String setTableSource = "SET TABLE \"" + m_name + "\" SOURCE\"" + getDataSourceSpec() + "\"";connection.createStatement().execute(setTableSource);}/** * 临时表语句 * @param filePathAndName * @return */private String getDataSourceSpec() { //忽略第一行数据 return filePathAndName + ";encoding=UTF-8;ignore_first=true;all_quoted=true;cache_scale=12;fs=" + m_separatorSpec;}public int getNumTimeLengthByDate(String caller, String startDate, String endDate) throws SQLException {String selectStmt = "SELECT sum(timeLength) as sumtimelength FROM \"" + m_name + "\" WHERE caller = '"+caller+"' and beginTime between '"+startDate+"' and '"+endDate+"'";Statement statement = connection.createStatement();ResultSet results = statement.executeQuery(selectStmt);int time = 0;while (results.next()) {time = results.getInt("sumtimelength");}return time;}public static void main(String[] args) throws SQLException {DBUtils utils = new DBUtils("D:\\cstpweb-workspace\\hqlTest\\cc.csv");utils.setUp();int i = utils.getNumTimeLengthByDate("80703", "2007-12-01 00:00:00", "2007-12-31 00:00:00");System.out.println(i);}}
测试数据
页:
[1]