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

hsqldb数据操作 博客分类: nosql hsqldbsql

程序员文章站 2024-03-22 20:08:58
...
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);
相关标签: hsqldb sql