有关hsql处理文本数据库的笔记

fdsa
alert();

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,	outPhyChannel	INTEGER,callType INTEGER,outType INTEGER,timeLength  INTEGER";
	/** 字段分隔符 */
    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);
	}
}

测试数据