xworker.dataObject.db.DbDataObject Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of xworker_dataobject Show documentation
Show all versions of xworker_dataobject Show documentation
DataObject is a data mapping model.
/*******************************************************************************
* Copyright 2007-2013 See AUTHORS file.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************************/
package xworker.dataObject.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmeta.Action;
import org.xmeta.ActionContext;
import org.xmeta.ActionException;
import org.xmeta.Thing;
import org.xmeta.World;
import org.xmeta.XMetaException;
import org.xmeta.util.UtilMap;
import xworker.dataObject.DataObject;
import xworker.dataObject.DataObjectException;
import xworker.dataObject.PageInfo;
import xworker.dataObject.utils.DbUtil;
import xworker.db.jdbc.DataSouceActionContextActions;
import xworker.db.jdbc.DataSourceActions;
public class DbDataObject {
private static Logger log = LoggerFactory.getLogger(DbDataObject.class);
/**
* 设置参数。
*
* @param actionContext
* @throws SQLException
*/
@SuppressWarnings("unchecked")
public static void setStatementParams(ActionContext actionContext) throws SQLException{
Integer index = (Integer) actionContext.get("index");
List> cds = (List>) actionContext.get("cds");
List attributes = (List) actionContext.get("attributes");
PreparedStatement pst = (PreparedStatement) actionContext.get("pst");
for(int i=0; i c = cds.get(i);
Thing thing = null;
for(Thing attr : attributes){
String name = attr.getString("name");
if(name.equals(c.get("attributeName")) || name.equals(c.get("name"))){
thing = attr;
break;
}
}
if(thing == null){
thing = (Thing) c.get("condition");
if(thing == null){
throw new XMetaException("Can not find match DBDataObject's field, condition=" + c);
}
}
DbUtil.setParameterValue(pst, index + i, thing.getString("type"), c.get("value"));
}
}
/**
* 删除数据。
*
* @param actionContext
* @return
* @throws SQLException
*/
public static boolean delete(ActionContext actionContext) throws SQLException{
Thing self = (Thing) actionContext.get("self");
DataObject theData = (DataObject) actionContext.get("theData");
//键值
Object[][] keyDatas = theData.getKeyAndDatas();
if(keyDatas == null || keyDatas.length == 0){
throw new SQLException("no keys data cannot delete, dataObjectPath=" + theData.getMetadata().getDescriptor().getMetadata().getPath());
}
//生成insert sql语句
String sql = "delete from " + self.getString("tableName");
sql = sql + " where ";
for(int i=0; i datas = (Map) actionContext.get("conditionData");
if(datas == null){
datas = Collections.emptyMap();
}
//log.info("" + datas);
Object conditionConfig = (Object) actionContext.get("conditionConfig");
if(conditionConfig == null){
conditionConfig = Collections.emptyMap();
}
List> cds = new ArrayList>();
String clause = DbUtil.getConditionSql(conditionConfig, actionContext, datas, cds);
//log.info("clause=" + clause);
if(clause != null && clause != ""){
sql = sql + " where " + clause;
}
if(self.getBoolean("showSql")){
log.info(sql);
}
//设置参数值
Connection con = (Connection) actionContext.get("con");
PreparedStatement pst = con.prepareStatement(sql);
try{
int index = 1;
for(int i=0; i datas = (Map) actionContext.get("conditionData");
if(datas == null){
datas = Collections.emptyMap();
}
//log.info("" + datas);
Object conditionConfig = (Object) actionContext.get("conditionConfig");
if(conditionConfig == null){
conditionConfig = Collections.emptyMap();
}
String sql = "delete from ";
String tableName = self.getString("tableName");
sql = sql + tableName;
List> cds = new ArrayList>();
String clause = DbUtil.getConditionSql(conditionConfig, actionContext, datas, cds);
//log.info("clause=" + clause);
if(clause != null && clause != ""){
sql = sql + " where " + clause;
}
if(self.getBoolean("showSql")){
log.info(sql);
}
//设置参数值和查询
Connection con = (Connection) actionContext.get("con");
PreparedStatement pst = con.prepareStatement(sql);
try{
int index = 1;
for(int i=0; i c = cds.get(i);
Thing thing = theData.getMetadata().getDefinition((String) c.get("name"));
DbUtil.setParameterValue(pst, index + i, thing.getString("type"), c.get("value"));
}
//执行sql
return pst.executeUpdate();
}finally{
if(pst != null){
pst.close();
}
}
}
/**
* 获取数据库字段名。
*
* @param thing
* @return
*/
public static String getFieldName(Thing thing){
String fieldName = thing.getString("sql");
if(fieldName == null || "".equals(fieldName)){
fieldName = thing.getString("fieldName");
}
if(fieldName == null || "".equals(fieldName)){
return thing.getString("name");
}
return fieldName;
}
@SuppressWarnings("unchecked")
public static Object query(ActionContext actionContext) throws Exception{
Thing self = (Thing) actionContext.get("self");
//---------------查询参数----------------
Map datas = (Map) actionContext.get("conditionData"); //查询条件数据
if(datas == null){
datas = Collections.emptyMap();
}
Object conditionConfig = actionContext.get("conditionConfig"); //查询配置
if(conditionConfig == null){
conditionConfig = new Thing();
}
//分页信息
Map pageInfo = (Map) actionContext.get("pageInfo");
//----------------生成SQL语句--------------
//排序的列，一会要判断排序的列是否存在
String sortField = null;
String sortDir = null;
if(pageInfo != null){
sortField = (String) pageInfo.get("sort");
String dir = (String) pageInfo.get("dir");
if(dir != null && !"".equals(dir)){
sortDir = dir;
}
}
if(sortField == null || "".equals(sortField)){
//默认排序
sortField = self.getString("storeSortField");
sortDir = self.getString("storeSortDir");
}
List attributes = (List) self.get("attribute@");
for(int i=0; i 0){
sql = sql + ",";
}
atrCount ++;
String fieldName = getFieldName(attributes.get(i));
sql = sql + fieldName;
String name = attributes.get(i).getMetadata().getName();
if(!haveSortField && name.equals(sortField)){
haveSortField = true; //排序的列存在
sortField = fieldName;
}
}
if(!haveSortField){
sortField = null; //置为空，没有排序
}
//表名或子查询
String tableName = self.getString("tableName");
String querySql= self.getString("querySql");
if(querySql != null && !"".equals(querySql)){
tableName = querySql;
}
sql = sql + " from " + tableName;
//查询条件
List> cds = new ArrayList>();
String clause = DbUtil.getConditionSql(conditionConfig, actionContext, datas, cds);
if(clause != null && clause != ""){
sql = sql + " where " + clause;
}
//排序，排序的字段是否选择的列中呢？
if(sortField != null){
sql = sql + " order by " + sortField;
if(sortDir != null){
sql = sql + " " + sortDir;
}
}
if(self.getBoolean("showSql")){
log.info(sql);
}
//----------------生成SQL完毕--------------------
//----------------执行查询-----------------------
if(pageInfo != null && (Integer) pageInfo.get("limit") != 0){
//有分页查询
String dbType = (String) actionContext.get("dbType"); //dbType是有DataSource上下文设置的
if(dbType != null && !"".equals(dbType)){
dbType = dbType.toLowerCase();
if("oracle".equals(dbType) || dbType.startsWith("oracle")){
return self.doAction("queryOraclePage", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes}));
}else if("derby".equals(dbType)){
return self.doAction("queryDerbyPage", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes}));
}else if("mysql".equals(dbType)){
return self.doAction("queryMysqlPage", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes}));
}else if("sqlserver2005".equals(dbType)){
return self.doAction("querySqlServer2005Page", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes}));
}else if("sqlite".equals(dbType)){
return self.doAction("queryMysqlPage", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes}));
}
}
throw new Exception("dbType=" + dbType + ", not supported page query now");
}else{
//没有分页查询
//设置参数值和查询
Connection con = (Connection) actionContext.get("con");
PreparedStatement pst = con.prepareStatement(sql);
ResultSet rs = null;
try{
//设置查询参数
self.doAction("setStatementParams", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "pst",pst, "attributes",attributes, "index",1}));
//执行sql
rs = pst.executeQuery();
List ds = new ArrayList();
while(rs.next()){
//构造对象
DataObject data = new DataObject(self);
data.setInited(false);
//设置属性值
for(int i=0; i pageInfo = (Map) actionContext.get("pageInfo");
//----------------生成SQL语句--------------
//排序的列，一会要判断排序的列是否存在
String sortField = null;
String sortDir = null;
if(pageInfo != null){
sortField = (String) pageInfo.get("sort");
String dir = (String) pageInfo.get("dir");
if(dir != null && !"".equals(dir)){
sortDir = dir;
}
}
if(sortField == null || "".equals(sortField)){
//默认排序
sortField = self.getString("storeSortField");
sortDir = self.getString("storeSortDir");
}
List attributes = (List) self.get("attribute@");
for(int i=0; i 0){
sql = sql + ",";
}
atrCount ++;
String fieldName = getFieldName(attributes.get(i));
sql = sql + fieldName;
String name = attributes.get(i).getMetadata().getName();
if(!haveSortField && name.equals(sortField)){
haveSortField = true; //排序的列存在
sortField = fieldName;
}
}
if(!haveSortField){
sortField = null; //置为空，没有排序
}
//表名或子查询
String tableName = self.getString("tableName");
String querySql= self.getString("querySql");
if(querySql != null && !"".equals(querySql)){
tableName = querySql;
}
sql = sql + " from " + tableName;
//查询条件
List> cds = new ArrayList>();
String clause = DbUtil.getConditionSql(conditionConfig, actionContext, datas, cds);
if(clause != null && clause != ""){
sql = sql + " where " + clause;
}
//排序，排序的字段是否选择的列中呢？
if(sortField != null){
sql = sql + " order by " + sortField;
if(sortDir != null){
sql = sql + " " + sortDir;
}
}
if(self.getBoolean("showSql")){
log.info(sql);
}
//----------------生成SQL完毕--------------------
//----------------执行查询-----------------------
if(pageInfo != null && (Integer) pageInfo.get("limit") != 0){
//有分页查询
String dbType = (String) actionContext.get("dbType"); //dbType是有DataSource上下文设置的
if(dbType != null && !"".equals(dbType)){
dbType = dbType.toLowerCase();
if("oracle".equals(dbType) || dbType.startsWith("oracle")){
self.doAction("iteratorOraclePage", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes, "action", action}));
return;
}else if("derby".equals(dbType)){
self.doAction("iteratorDerbyPage", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes, "action", action}));
return;
}else if("mysql".equals(dbType)){
self.doAction("iteratorMysqlPage", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes, "action", action}));
return;
}else if("sqlserver2005".equals(dbType)){
self.doAction("iteratorSqlServer2005Page", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "sql",sql, "attributes",attributes, "action", action}));
return;
}
}
throw new Exception("dbType=" + dbType + ", not supported page query now");
}else{
//没有分页查询
//设置参数值和查询
Connection con = (Connection) actionContext.get("con");
PreparedStatement pst = con.prepareStatement(sql);
ResultSet rs = null;
try{
//设置查询参数
self.doAction("setStatementParams", actionContext, UtilMap.toMap(new Object[]{"cds",cds, "pst",pst, "attributes",attributes, "index",1}));
//执行sql
rs = pst.executeQuery();
int index = 1;
while(rs.next()){
//构造对象
DataObject data = new DataObject(self);
data.setInited(false);
//设置属性值
for(int i=0; i loadFromResultSet(ActionContext actionContext) throws SQLException{
Thing self = (Thing) actionContext.get("self");
List datas = new ArrayList();
List attributes = self.getChilds("attribute");
ResultSet rs = (ResultSet) actionContext.get("resultSet");
while(rs.next()){
//构造对象
DataObject theData = new DataObject(self);
theData.setInited(false);
//设置属性值
for(int i=0; i> cds = (List>) actionContext.get("cds");
List attributes = (List) actionContext.get("attributes");
//查询总数
String sql = (String) actionContext.get("sql");
String countSql = "select count(*) from (" + sql + ") ";
pst = con.prepareStatement(countSql);
//设置查询参数
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes", attributes, "index", 1));
rs = pst.executeQuery();
rs.next();
pageInfo.setTotalCount(rs.getInt(1));
rs.close();
pst.close();
//分页查询
sql = "select * from (select t.*, rownum rowno from (" + sql + ") t ) where rowno between ? and ?";
if(self.getBoolean("showSql")){
log.info(sql);
}
pst = con.prepareStatement(sql);
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes", attributes, "index", 1));
if(pageInfo != null && pageInfo.getLimit() != 0){
int index = cds.size() + 1;
int start = pageInfo.getStart() + 1;
pst.setInt(index, start);
pst.setInt(index + 1, start + pageInfo.getLimit() - 1);
}
//执行sql
rs = pst.executeQuery();
iteratorResult(self, rs, action, actionContext);
}finally{
if(rs != null){
rs.close();
}
if(pst != null){
pst.close();
}
}
}
public static void iteratorResult(Thing self, ResultSet rs, Action action, ActionContext actionContext) throws Exception{
List attributes = self.getChilds("attribute"); //取查询数据对象实际的列
int index = 1;
while(rs.next()){
//构造对象
DataObject data = new DataObject(self);
data.setInited(false);
//设置属性值
for(int i=0; i> cds = (List>) actionContext.get("cds");
List attributes = (List) actionContext.get("attributes");
//查询总数
String countSql = "select count(*) from (" + sql + ") as t";
pst = con.prepareStatement(countSql);
//设置查询参数
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes" ,attributes, "index", 1));
rs = pst.executeQuery();
rs.next();
pageInfo.setTotalCount(rs.getInt(1));
rs.close();
pst.close();
//分页查询
sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, t.* FROM (" + sql + " ) t) AS tmp WHERE rownum >= ? AND rownum <= ?";
if(self.getBoolean("showSql")){
log.info(sql);
}
pst = con.prepareStatement(sql);
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes", attributes, "index", 1));
if(pageInfo != null && pageInfo.getLimit() != 0){
int index = cds.size() + 1;
int start = pageInfo.getStart() + 1;
pst.setInt(index, start);
pst.setInt(index + 1, start + pageInfo.getLimit() - 1);
}
//执行sql
rs = pst.executeQuery();
iteratorResult(self, rs, action, actionContext);
}finally{
if(rs != null){
rs.close();
}
if(pst != null){
pst.close();
}
}
}
@SuppressWarnings("unchecked")
public static void iteratorMysqlPage(ActionContext actionContext) throws Exception{
Thing self = (Thing) actionContext.get("self");
PageInfo pageInfo = (PageInfo) actionContext.get("pageInfo");
Action action = (Action) actionContext.get("action");
String sql = (String) actionContext.get("sql");
PreparedStatement pst = null;
ResultSet rs = null;
try{
Connection con = (Connection) actionContext.get("con");
List> cds = (List>) actionContext.get("cds");
List attributes = (List) actionContext.get("attributes");
//查询总数
String countSql = "select count(*) from (" + sql + ") as t";
pst = con.prepareStatement(countSql);
//设置查询参数
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes", attributes, "index", 1));
rs = pst.executeQuery();
rs.next();
pageInfo.setTotalCount(rs.getInt(1));
rs.close();
pst.close();
//分页查询
sql = sql + " limit ? ,?";
if(self.getBoolean("showSql")){
log.info(sql);
}
pst = con.prepareStatement(sql);
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes", attributes, "index", 1));
if(pageInfo != null && pageInfo.getLimit() != 0){
int index = cds.size() + 1;
int start = pageInfo.getStart() + 1;
pst.setInt(index, start-1);
pst.setInt(index + 1, pageInfo.getLimit());
//println("start=" + (start -1) + ",limit=" + (start + pageInfo.limit - 2));
}
//执行sql
rs = pst.executeQuery();
iteratorResult(self, rs, action, actionContext);
}finally{
if(rs != null){
rs.close();
}
if(pst != null){
pst.close();
}
}
}
@SuppressWarnings("unchecked")
public static void iteratorSqlServer2005Page(ActionContext actionContext) throws Exception{
Thing self = (Thing) actionContext.get("self");
PageInfo pageInfo = (PageInfo) actionContext.get("pageInfo");
Action action = (Action) actionContext.get("action");
String sql = (String) actionContext.get("sql");
PreparedStatement pst = null;
ResultSet rs = null;
try{
Connection con = (Connection) actionContext.get("con");
List> cds = (List>) actionContext.get("cds");
List attributes = (List) actionContext.get("attributes");
//查询总数
String countSql = "select count(*) from (" + sql + ") ";
pst = con.prepareStatement(countSql);
//设置查询参数
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes", attributes, "index", 1));
rs = pst.executeQuery();
rs.next();
pageInfo.setTotalCount(rs.getInt(1));
rs.close();
pst.close();
//分页查询
sql = "select * from (select t.*, ROW_NUMBER() over(order by (select 0)) as rowno from (" + sql + ") t ) where rowno between ? and ?";
if(self.getBoolean("showSql")){
log.info(sql);
}
pst = con.prepareStatement(sql);
self.doAction("setStatementParams", actionContext, UtilMap.toMap("cds", cds, "pst", pst, "attributes", attributes, "index", 1));
if(pageInfo != null && pageInfo.getLimit() != 0){
int index = cds.size() + 1;
int start = pageInfo.getStart() + 1;
pst.setInt(index, start);
pst.setInt(index + 1, start + pageInfo.getLimit() - 1);
}
//执行sql
rs = pst.executeQuery();
iteratorResult(self, rs, action, actionContext);
}finally{
if(rs != null){
rs.close();
}
if(pst != null){
pst.close();
}
}
}
} © 2015 - 2025 Weber Informatics LLC | Privacy Policy