hsqldb数据操作

hsqldb数据操作

1、底层操作

// 添加数据
public Object insertHSQLMap(Map map, String tableName,
    String primaryKeyName, String db_file_name_prefix ,String user,String password)
    throws DataAccessException {
   Object pkvalue = map.get(primaryKeyName);
   if (pkvalue == null)
    throw new InvalidDataAccessApiUsageException("PrimaryKey '"
      + primaryKeyName + "' cannot find in map");
   this.setPrimaryKey(map, tableName, primaryKeyName);
   String keyStr = "( ";
   String valueStr = "( ";
   String[] args = new String[map.size()];
   Iterator it = map.keySet().iterator();
   int j = 0;
   while (it.hasNext()) {
    String key = (String) it.next();

    keyStr = keyStr + key + ",";
    args[j]=String.valueOf(map.get(key));
    valueStr =valueStr +"'"+args[j] + "',";
    j++;
   }
   keyStr = keyStr.substring(0, keyStr.length() - 1) + " ) ";
   valueStr = valueStr.substring(0, valueStr.length() - 1) + " ) ";
   String insertSql = "insert into " + tableName + " " + keyStr
     + " values " + valueStr;
   System.out.println("insertSql: "+insertSql);
   Connection conn = null;
   Statement stmt = null;
   try {
    //取得数据库连接
    conn = this.getHSQLConnection(db_file_name_prefix, user, password);
    stmt = conn.createStatement();
    stmt.execute(insertSql);
    stmt.execute("SHUTDOWN");
    stmt.close();
    conn.close();
   } catch (Exception ex1) {
    ex1.printStackTrace();

   }

   return map.get(primaryKeyName);
}
/**
* 执行sql语句
*/
public void executeHSQLSql(String sql, String db_file_name_prefix,String user,String password)
    throws DataAccessException {

   Connection conn = null;
   Statement stmt = null;
   try {
    conn = this.getHSQLConnection(db_file_name_prefix, user, password);
    stmt = conn.createStatement();
    stmt.execute(sql);
    stmt.execute("SHUTDOWN");
  
    stmt.close();
    conn.close();
   } catch (Exception ex1) {
    ex1.printStackTrace();

   }

}
/**
* 查询数据
*/
public List queryHsqlList(String sql, String db_file_name_prefix,String user,String password)
    throws DataAccessException {

   Connection conn = null;
   Statement stmt = null;
   ResultSet rs=null;
   ResultSetMetaData rsmd=null;
   List       list=new ArrayList();
   try {
    conn = this.getHSQLConnection(db_file_name_prefix, user, password);
    stmt = conn.createStatement();
    rs=stmt.executeQuery(sql);
    rsmd=rs.getMetaData();
       int columnCount = rsmd.getColumnCount(); //列的总数
  
       while(rs.next())
          {
              Map m=new HashMap();
              for(int i=1;i<=columnCount;i++)
              {
                  m.put(rsmd.getColumnName(i), rs.getObject(i));
              }
              list.add(m);
          }
    stmt.execute("SHUTDOWN");
    rs.close();
    stmt.close();
    conn.close();
  
 
   } catch (Exception ex1) {
    ex1.printStackTrace();

   }
   return list;
}

/**
* 获取数据库连接
* db_file_name_prefix为连接字符串 例如：jdbc:hsqldb:file:d:hsqldb\\
* user：hsqldb数据库用户名称
* password：hsqldb数据库用户密码
*/
public Connection getHSQLConnection(String db_file_name_prefix,String user,String password)
{
   Connection conn = null;
   try {
    Class.forName("org.hsqldb.jdbcDriver");
    conn = DriverManager.getConnection(db_file_name_prefix, user, password);
   } catch (Exception ex1) {
    ex1.printStackTrace();
   }
   return conn;
 
}

2、业务层操作

/**
* 添加数据局到hsqldb数据
* List dataList,数据列表
* String tableName：表名称
* String pkName：主键
* String db_file_name_prefix：数据库连接字符串
*/

private void insertDataToHsqldb(List dataList,String tableName,String pkName,String db_file_name_prefix){
   for(int i=0;i<dataList.size()&&dataList.size()>0;i++){
    Map dataMap=(Map)dataList.get(i);
    MapUtil.filterMap(dataMap);
    expDataDAO.insertHSQLMap(dataMap, tableName, pkName, db_file_name_prefix);
   }
}

String db_file_name_prefix = "jdbc:hsqldb:file:d:hsqldb\\sample";

private String hsqlUser="sa";
private String hsqlPassword="sa";
private String pkname="ID";

private String tablename="JCD";
List list=jcdglService.getJcdInfoList();
insertDataToHsqldb(jcdinfolist, tablename, pkname, db_file_name_prefix);