|
发表于 2012-11-28 18:05:14
|
显示全部楼层
本帖最后由 mayuhui 于 2012-11-28 19:17 编辑
你可以将你现有的存储过程数据集转化为概念
让后从概念中去查询就可以了
1、新建概念(报表展示的信息)
2、重写query和save action(本来想上传个附件,但是太大,主要代码贴下面,可以参考,报表的话只要queryaction应该就可以了)
//查询OA_EMailInfo
public static Table queryOA_EMailInfo(String concept, String select, String from, String aggregate, String dataModel, String fnModel,
String condition, List range, Boolean distinct, String idColumn, String filter, Integer limit, Integer offset, String columns,
String orderBy, String aggregateColumns, Map variables) throws NamingException, SQLException {
String querysql = null;
String countsql = null;
//新建表(类型和字段与概念相同)
Table table = TableUtils.createTable(null, "fID", "String", "fSenderID", "String", "fSender", "String", "fReceiverID", "String", "fReceiver",
"String", "fSubject", "String", "fText", "String", "fAttachment", "String", "fSendTime", "DateTime", "fStatus", "String", "fType",
"String");
//取得连接
Connection conn = ModelUtils.getConnectionInTransaction(EMail_DATAMODEL);
if (filter != null) {
countsql = "select count(*) from OA_EMailInfo where " + filter;
querysql = "select " + select + " from " + from + " where " + filter + " and fID not in (select top " + offset
+ " fID from OA_EMailInfo where " + filter + " order by fSendTime desc) order by fSendTime desc";
} else {
countsql = "select count(*) from OA_EMailInfo";
querysql = "select " + select + " from " + from;
}
log4j.info("sql==" + querysql);
//调用存储过程
CallableStatement cstmt = conn.prepareCall("{call queryOA_commonProcedure(?)}");
cstmt.setString(1, querysql);
//执行存储过程并返回结果
ResultSet rt = cstmt.executeQuery();
while (rt.next()) {
//新建行
Row row = table.appendRow();
//添加数据
row.setString("fID", rt.getString("fID"));
row.setString("fSenderID", rt.getString("fSenderID"));
row.setString("fSender", rt.getString("fSender"));
row.setString("fReceiverID", rt.getString("fReceiverID"));
row.setString("fReceiver", rt.getString("fReceiver"));
row.setString("fSubject", rt.getString("fSubject"));
row.setString("fText", rt.getString("fText"));
row.setString("fAttachment", rt.getString("fAttachment"));
row.setDateTime("fSendTime", rt.getTimestamp("fSendTime"));
row.setString("fStatus", rt.getString("fStatus"));
row.setString("fType", rt.getString("fType"));
//设置状态
row.setState(ModifyState.NONE);
}
// 设置table的主键列,必须的
table.getProperties().put(Table.PROP_NAME_ROWID, "fID");
// 设置table的总行数,bizData分页需要
//table.getProperties().put(Table.PROP_DB_COUNT, count);
return table;
}
//保存OA_EMailInfo
public static void saveOA_EMailInfo(String concept, String dataModel, String fnModel, List insertRange, List deleteRange, List updateRange,
String readOnly, String notNull, Table table) throws NamingException, SQLException {
// 遍历table
Iterator<Row> rows = table.iterator();
while (rows.hasNext()) {
Row row = rows.next();
ModifyState rowState = row.getState();
log4j.info(rowState);
String id = row.getString("OA_EMailInfo");
String fRelation1 = row.getString("fSenderID");
String fRelation2 = row.getString("fSender");
String fRelation3 = row.getString("fReceiverID");
String fRelation4 = row.getString("fReceiver");
String fRelation5 = row.getString("fSubject");
String fRelation6 = row.getString("fText");
String fRelation7 = row.getString("fAttachment");
String fRelation8 = row.getDateTime("fSendTime").toString();
String fRelation9 = row.getString("fStatus");
String fRelation10 = row.getString("fType");
if (ModifyState.NEW.equals(rowState)) {
addOA_EMailInfo(id, fRelation1, fRelation2, fRelation3, fRelation4, fRelation5, fRelation6, fRelation7, fRelation8, fRelation9,
fRelation10);
} else if (ModifyState.EDIT.equals(rowState)) {
updateOA_EMailInfo(id, fRelation1, fRelation2, fRelation3, fRelation4, fRelation5, fRelation6, fRelation7, fRelation8, fRelation9,
fRelation10);
} else if (ModifyState.DELETE.equals(rowState)) {
delOA_EMailInfo(id);
}
}
}
//新增
public static void addOA_EMailInfo(String fID, String fRelation1, String fRelation2, String fRelation3, String fRelation4, String fRelation5,
String fRelation6, String fRelation7, String fRelation8, String fRelation9, String fRelation10) throws SQLException, NamingException {
Connection conn = ModelUtils.getConnectionInTransaction(EMail_DATAMODEL);
CallableStatement cstmt = conn.prepareCall("{call addOA_EMailInfo(?,?,?,?,?,?,?,?,?,?,?)}");
cstmt.setString(1, fID);
cstmt.setString(2, fRelation1);
cstmt.setString(3, fRelation2);
cstmt.setString(4, fRelation3);
cstmt.setString(5, fRelation4);
cstmt.setString(6, fRelation5);
cstmt.setString(7, fRelation6);
cstmt.setString(8, fRelation7);
cstmt.setString(9, fRelation8);
cstmt.setString(10, fRelation9);
cstmt.setString(11, fRelation10);
cstmt.executeUpdate();
}
//更新
public static void updateOA_EMailInfo(String fID, String fRelation1, String fRelation2, String fRelation3, String fRelation4, String fRelation5,
String fRelation6, String fRelation7, String fRelation8, String fRelation9, String fRelation10) throws SQLException, NamingException {
Connection conn = ModelUtils.getConnectionInTransaction(EMail_DATAMODEL);
CallableStatement cstmt = conn.prepareCall("{call updateOA_EMailInfo(?,?,?,?,?,?,?,?,?,?,?)}");
cstmt.setString(1, fID);
cstmt.setString(2, fRelation1);
cstmt.setString(3, fRelation2);
cstmt.setString(4, fRelation3);
cstmt.setString(5, fRelation4);
cstmt.setString(6, fRelation5);
cstmt.setString(7, fRelation6);
cstmt.setString(8, fRelation7);
cstmt.setString(9, fRelation8);
cstmt.setString(10, fRelation9);
cstmt.setString(11, fRelation10);
cstmt.executeUpdate();
}
//删除
public static void delOA_EMailInfo(String fID) throws SQLException, NamingException {
Connection conn = ModelUtils.getConnectionInTransaction(EMail_DATAMODEL);
CallableStatement cstmt = conn.prepareCall("{call delOA_EMailInfo(?)}");
cstmt.setString(1, fID);
cstmt.executeUpdate();
}
3、对所建概念进行报表信息展示
|
|