baasData手写查询保存（简单数据）

Masion · 发表于 2017-1-20 16:51:19

主从数据一个事务中保存

//前台
Model.prototype.userDataCustomSave = function(event) {
// 主表与子表数据在一个请求内同时保存
var userData = event.source;
var orderData = this.comp("orderData");
var params = {
"userData" : userData.toJson(true),
"orderData" : orderData.toJson(true)
};
var success = function(resultData) {
// 这里应该先支持从表，后执行主表
orderData.applyUpdates();
userData.applyUpdates();
};
Baas.sendRequest({
"url" : "/demo",
"action" : "saveMasterDetail",
"params" : params,
"success" : success
});
};

复制代码

Masion · 发表于 2017-1-20 16:54:07

后台

private static void saveMasterDetail(ServletRequest request, ServletResponse response) throws ParseException, SQLException, NamingException {
// 参数序列化
JSONObject params = (JSONObject) JSONObject.parse(request.getParameter("params"));
// 获取参数
JSONObject userData = params.getJSONObject("userData");
JSONObject orderData = params.getJSONObject("orderData");
// 转换Table
Table userTable = Transform.jsonToTable(userData);
Table orderTable = Transform.jsonToTable(orderData);
Connection conn = Util.getConnection(DATASOURCE_TAKEOUT);
try {
conn.setAutoCommit(false);
try {
// 排除不能保存的列
Collection<String> userColumns = userTable.getColumnNames();
userColumns.remove("orderCount");
// 一个事务内同时保存多张表
Util.saveData(conn, userTable, TABLE_TAKEOUT_USER, userColumns);
Util.saveData(conn, orderTable, TABLE_TAKEOUT_ORDER);
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
}
} finally {
conn.close();
}
}

复制代码

sky90992 · 发表于 2017-1-20 17:45:16

多谢分享

Masion · 发表于 2017-1-20 17:49:36

/**
* SQL数据查询，按ResultSet的列定义转换并返回Table，支持分页。应从前端传入完整列定义（Baas.getDataColumns(data)），以解决oracle等数据库不区分date、time、datetime，导致的数据格式转换问题；兼容了以前只传入列名字符串（data.getColumnIDs()）的写法，但是已不再推荐。
* @param conn
* @param sql
* @param params SQL中问号对应的参数值，按顺序匹配
* @param columns 列定义
* @param offset 偏移行，null则不分页
* @param limit 行数，null则不分页
* @return
* @throws SQLException
*/
public static Table queryData(Connection conn, String sql, List<Object> params, Object columns, Integer offset, Integer limit) throws SQLException {
if (limit != null && offset != null) {
if (isMysql(conn)) {
sql += " LIMIT " + offset + "," + limit;
} else if (isOracle(conn)) {
sql = String.format("SELECT * FROM (SELECT rownum no___, A___.* FROM (%s) A___ WHERE rownum <= %d) WHERE no___ > %d", sql, offset + limit, offset);
}
}
// System.out.println(sql);
PreparedStatement pstat = conn.prepareStatement(sql);
try {
if (params != null) {
for (int i = 0, len = params.size(); i < len; i++) {
pstat.setObject(i + 1, params.get(i));
}
}
ResultSet rs = pstat.executeQuery();
if (limit != null && offset != null && !isMysql(conn) && !isOracle(conn)) {
for (int i = 0; i < offset; i++) {
rs.next();
}
}
Table table = null;
if (columns instanceof JSONObject) {
table = Transform.createTableByColumnsDefine((JSONObject) columns);
} else {
table = Transform.createTableByResultSet(rs, (String) columns);
}
Transform.loadRowsFromResultSet(table, rs, limit);
return table;
} finally {
pstat.close();
}
}

复制代码

Masion · 发表于 2017-1-20 18:13:32

有几个函数有助于深入了解baas结构
也放在这里

public static Table queryData(Connection conn, String sql, List<Object> params, Object columns, Integer offset, Integer limit) threw SQLException{
if(limit!=null $ offset!=null){
if(isMysql(conn)){
sql=sql+ "limit "+offset + "," + limit ;
}else if(isOracle(conn)){
sql = String.format("SELECT * FROM (SELECT rownum no___, A___.* FROM (%s) A___ WHERE rownum <= %d) WHERE no___ > %d", sql, offset + limit, offset);
}
}
PreparedStatement pstat=conn.prepareStatement(sql);
try{
if(params!=null){
for(int i=0;i<params.size();i++){
pstat.setObject(i+1,params.get(i));
}
}
ResultSet rs=pstat.executeQuery();
if(limit!=null&&offset!=null&&!isMysql(conn)&& !isOracle(conn)){
for(int i=0;i<offset;i++){
rs.next();
}
};
Table table=null;
if(columns instanceof JSONObject){
table=Transform.createTableByColumnsDefine((JSONObject) columns);
}else{
table=Transform.createTableByResultSet(rs,(String) columns);
}
Transform.loadRowsFromResultSet(table,rs,limit);
return table;
}finally{
pstat.close();
}
}
public static void saveData(Connection conn, Table table, String tableName, Collection<String> columns) threw SQLException{
if(columns==null){
columns=new ArrayList<String>();
columns.addAll(table.getColumnNames());
}
String IDColumn = table.getIDColumn();
PreparedStatement newStat=conn.prepareStatement(createNewSQL(table,tableName,columns));
try{
for(Row row : table.getRows(RowState.NEW)){
int i = 1;
for (String column : columns) {
newStat.setObject(i, row.getValue(column));
i++;
}
newStat.execute();
}finally{
newStat.close();
}
PreparedStatement editStat = conn.prepareStatement(createUpdateSQL(table, tableName, columns));
try {
for (Row row : table.getRows(RowState.EDIT)) {
int i = 1;
for (String column : columns) {
editStat.setObject(i, row.getValue(column));
i++;
}
editStat.setObject(columns.size() + 1, row.isChanged(idColumn) ? row.getOldValue(table.getIDColumn()) : row.getValue(idColumn));
editStat.execute();
}
} finally {
editStat.close();
}
PreparedStatement deleteStat = conn.prepareStatement(createDeleteSQL(table, tableName));
try {
for (Row row : table.getRows(RowState.DELETE)) {
deleteStat.setObject(1, row.isChanged(idColumn) ? row.getOldValue(table.getIDColumn()) : row.getValue(idColumn));
deleteStat.execute();
}
} finally {
deleteStat.close();
}
}
private static String createNewSQL(Table table, String tableName, Collection<String> columns) {
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO " + tableName);
sql.append(" (" + arrayJoin(columns.toArray(), "%s", ",") + ") ");
sql.append(" VALUES (" + arrayJoin(columns.toArray(), "?", ",") + ") ");
return sql.toString();
}
private static String createUpdateSQL(Table table, String tableName, Collection<String> columns) {
StringBuffer sql = new StringBuffer();
sql.append("UPDATE " + tableName);
sql.append(" SET " + arrayJoin(columns.toArray(), "%s=?", ",") + " ");
sql.append(" WHERE " + table.getIDColumn() + "=? ");
return sql.toString();
}
private static String createDeleteSQL(Table table, String tableName) {
StringBuffer sql = new StringBuffer();
sql.append("DELETE FROM " + tableName);
sql.append(" WHERE " + table.getIDColumn() + "=? ");
return sql.toString();
}

复制代码

Masion · 发表于 2017-1-20 18:32:28

/**
* 将一个数组连接为格式化字符串
* @param objects
* @param format 对数组元素的格式化，例如：'%s'为每个数组元素增加单引号、(%s = ?)将每个数组元素格式化为括号中等于问号
* @param separator 分隔符，例如：,、OR、AND
* @return
*/
public static String arrayJoin(Object[] objects, String format, String separator) {
StringBuffer buf = new StringBuffer();
for (Object o : objects) {
if (buf.length() > 0) {
buf.append(separator);
}
buf.append(String.format(format, o.toString()));
}
return buf.toString();
}

复制代码

doudou459 · 发表于 2017-1-24 09:47:37

get!!!!

1123165 · 发表于 2017-4-27 15:33:13

本帖最后由 1123165 于 2017-4-27 15:34 编辑

不错，看看

		自动登录	找回密码
密码			注册

[分享] baasData手写查询保存（简单数据）