@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 "); }