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);