@Test
public void dblist() throws SQLException {
String sql = "insert into xxxx(f1,f2,f3) values(#[f1],#[f2],#[f2])";
List beans = null;
SQLExecutor.insert(sql, beans);
sql = "insert into xxxx(f1,f2,f3) values(#[f1],#[f2],#[f2])";
SQLExecutor.update("bspf", sql, beans);
sql = "insert into xxxx(f1,f2,f3) values(#[f1],#[f2],#[f2])";
SQLExecutor.delete("bspf", sql, beans);
}
@Test
public void dbobject() throws SQLException {
String sql = "insert into xxxx(f1,f2,f3) values(#[f1],#[f2],#[f2])";
//
Object bean = null;
SQLExecutor.insert("bspf", sql, bean);
//
sql = "insert into xxxx(f1,f2,f3) values(#[f1],#[f2],#[f2])";
//
SQLExecutor.update("bspf", sql, bean);
sql = "insert into xxxx(f1,f2,f3) values(#[f1],#[f2],#[f2])";
SQLExecutor.delete("bspf", sql, bean);
}
@Test
public void testinsert() {
MetadataSet m = new MetadataSet();
Date date = new Date();
Timestamp timestamp = new Timestamp(date.getTime());
m.setCreate_time(timestamp);
m.setCreator("zhangsan");
m.setMetadata_set_id("1");
m.setMetadata_set_code("aaaaaaaa");
m.setMetadata_set_name("bbbbbbbb");
m.setModifier("lisi");
m.setModify_time(timestamp);
m.setData_type_code("java");
m.setRemark("remark");
String sql = "insert into xxxx(f1,f2,f3) values(#[create_time],#[creator],#[data_type_code])";
//
Object bean = null;
try {
SQLExecutor.insertBean(sql, m);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Test
public void testNewBeanInsert() throws Exception {
LobBean bean = new LobBean();
bean.id = "2";
bean.blobname = "abcdblob";
bean.clobname = "abcdclob";
SQLExecutor.insertBean(
"insert into test(id,blobname,clobname) values(#[id],#[blobname],#[clobname])", bean);
}
@Test
public void testNewSQLParamInsert() throws Exception {
SQLParams params = new SQLParams();
params.addSQLParam("id", "1", SQLParams.STRING);
// ID,HOST_ID,PLUGIN_ID,CATEGORY_ID,NAME,DESCRIPTION,DATASOURCE_NAME,DRIVER,JDBC_URL,USERNAME,PASSWORD,VALIDATION_QUERY
params.addSQLParam("blobname", "abcdblob", SQLParams.BLOB);
params.addSQLParam("clobname", "abcdclob", SQLParams.CLOB);
SQLExecutor.insertBean(
"insert into test(id,blobname,clobname) values(#[id],#[blobname],#[clobname])", params);
}
@Test
public void testNewOrMappingQuery() throws Exception {
// SQLParams params = new SQLParams();
// params.addSQLParam("id", "1", SQLParams.STRING);
// //
// ID,HOST_ID,PLUGIN_ID,CATEGORY_ID,NAME,DESCRIPTION,DATASOURCE_NAME,DRIVER,JDBC_URL,USERNAME,PASSWORD,VALIDATION_QUERY
// params.addSQLParam("blobname", "abcdblob",
// SQLParams.BLOB);
// params.addSQLParam("clobname", "abcdclob",
// SQLParams.CLOB);
LobBean bean = SQLExecutor.queryObject(LobBean.class, "select * from test");
System.out.println();
}
public void downloadFileFromBlob(
String fileid, final HttpServletRequest request, final HttpServletResponse response)
throws Exception {
try {
SQLExecutor.queryByNullRowHandler(
new NullRowHandler() {
public void handleRow(Record record) throws Exception {
StringUtil.sendFile(
request, response, record.getString("filename"), record.getBlob("filecontent"));
}
},
"select * from filetable where fileid=?",
fileid);
} catch (Exception e) {
throw e;
}
}
/**
* CREATE TABLE CLOBFILE ( FILEID VARCHAR(100), FILENAME VARCHAR(100), FILESIZE BIGINT,
* FILECONTENT CLOB(2147483647) )
*/
public @Test void uploadClobFile() throws Exception {
File file = new File("D:\\bbossgroups-3.5.1\\bboss-taglib\\readme.txt");
String sql = "";
try {
sql =
"INSERT INTO CLOBFILE (FILENAME,FILECONTENT,fileid,FILESIZE) VALUES(#[filename],#[FILECONTENT],#[FILEID],#[FILESIZE])";
SQLParams sqlparams = new SQLParams();
sqlparams.addSQLParam("filename", file.getName(), SQLParams.STRING);
sqlparams.addSQLParamWithCharset("FILECONTENT", file, SQLParams.CLOBFILE, "GBK");
sqlparams.addSQLParam("FILEID", UUID.randomUUID().toString(), SQLParams.STRING);
sqlparams.addSQLParam("FILESIZE", file.length(), SQLParams.LONG);
SQLExecutor.insertBean(sql, sqlparams);
} catch (Exception ex) {
throw new Exception("上传附件关联临控指令布控信息附件失败：" + ex);
}
}
public File getDownloadClobFile(String fileid) throws Exception {
try {
return SQLExecutor.queryTField(
File.class,
new FieldRowHandler<File>() {
@Override
public File handleField(Record record) throws Exception {
// 定义文件对象
File f = new File("d:/", record.getString("filename"));
// 如果文件已经存在则直接返回f
if (f.exists()) return f;
// 将blob中的文件内容存储到文件中
record.getFile("filecontent", f);
return f;
}
},
"select * from CLOBFILE where fileid=?",
fileid);
} catch (Exception e) {
throw e;
}
}
/**
* 上传附件
*
* @param inputStream
* @param filename
* @return
* @throws Exception
*/
public boolean uploadFile(InputStream inputStream, long size, String filename) throws Exception {
boolean result = true;
String sql = "";
try {
sql =
"INSERT INTO filetable (FILENAME,FILECONTENT,fileid,FILESIZE) VALUES(#[filename],#[FILECONTENT],#[FILEID],#[FILESIZE])";
SQLParams sqlparams = new SQLParams();
sqlparams.addSQLParam("filename", filename, SQLParams.STRING);
sqlparams.addSQLParam("FILECONTENT", inputStream, size, SQLParams.BLOBFILE);
sqlparams.addSQLParam("FILEID", UUID.randomUUID().toString(), SQLParams.STRING);
sqlparams.addSQLParam("FILESIZE", size, SQLParams.LONG);
SQLExecutor.insertBean(sql, sqlparams);
} catch (Exception ex) {
ex.printStackTrace();
result = false;
throw new Exception("上传附件关联临控指令布控信息附件失败：" + ex);
} finally {
if (inputStream != null) {
inputStream.close();
}
}
return result;
}
public List<HashMap> queryclobfiles() throws Exception {
return SQLExecutor.queryList(HashMap.class, "select FILENAME,fileid,FILESIZE from CLOBFILE");
}
public void deletefiles() throws Exception {
SQLExecutor.delete("delete from filetable ");
SQLExecutor.delete("delete from CLOBFILE ");
}