package com.adm.dao;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.yfsoft.comm.util.BizResult;
import org.yfsoft.comm.util.StringUtil;
public class CommonDao {
private CommonMapper mapper = null;
public void setMapper(Object mapper) {
this.mapper = (CommonMapper)mapper;
}
public Object getLastId(){
Map<String,Object> ids = query("SELECT LAST_INSERT_ID() as id").getData();
return ids.get("id");
}
public List<Map<String,Object>> getDataSource(Object ds_id) throws Throwable{
Map<String,Object> mc = new HashMap<String,Object>();
mc.put("mc_ds", ds_id);
return getDataSource(mc,null);
}
public List<Map<String,Object>> getDataSource(Map<String,Object> mc) throws Throwable{
return getDataSource(mc,null);
}
/**
* 获取控件对应的数据源
* @param entry 数据实例
* @param mc 模型控件详情
* @return 数据源列表信息
* @throws Throwable
*/
public List<Map<String,Object>> getDataSource(Map<String,Object> mc,Map<String,Object> entry) throws Throwable{
Object ds_id = mc.get("mc_ds");
BizResult ds_rst = query("sys_ds",null,"ds_id = "+ds_id);
if(!"0".equals(ds_rst.getCode())){
throw new Exception("数据源获取错误!");
}
Map<String,Object> ds = ds_rst.getData();
if(null!=ds && ds.size()>0){
String ds_type_val = ds.get("ds_type").toString();
List<Map<String,Object>> mc_ds = null;
if("map".equals(ds_type_val)){
mc_ds = new ArrayList<Map<String,Object>>();
Map<String,Object> data ;
//1:是,0:否
String ds_map = ds.get("ds_collection").toString();
String[] ds_map_split = ds_map.split(",");
for(String s : ds_map_split){
data = new HashMap<String,Object>();
data.put("k", StringUtil.between(s, ":", StringUtil.BetweenPos.END));
data.put("v", StringUtil.between(s, ":", StringUtil.BetweenPos.START));
mc_ds.add(data);
}
}else if("sql".equals(ds_type_val)){
String sql = ds.get("ds_sql").toString();
if(null != entry)
sql = StringUtil.formatSql(sql, entry.get(mc.get("mc_attr_name")));
mc_ds = list(sql).getData();
}
return mc_ds;
}
throw new Exception("未找到任何数据源!");
}
public Object getDataValue(int ds_id,Object k) throws Throwable{
BizResult ds_rst = query("sys_ds",null,"ds_id = "+ds_id);
if(!"0".equals(ds_rst.getCode())){
throw new Exception("数据源获取错误!");
}
Map<String,Object> ds = ds_rst.getData();
if(null!=ds && ds.size()>0){
String ds_type_val = ds.get("ds_type").toString();
if("map".equals(ds_type_val)){
String ds_map = ds.get("ds_collection").toString();
String[] ds_map_split = ds_map.split(",");
for(String s : ds_map_split){
if(k.equals(StringUtil.between(s, ":", StringUtil.BetweenPos.END)))
return StringUtil.between(s, ":", StringUtil.BetweenPos.START);
}
throw new Exception("未找到任何数据!");
}else if("sql".equals(ds_type_val)){
String sql = "select v from ({0}) as t where k = {1}";
sql = StringUtil.formatSql(sql, ds.get("ds_sql").toString(),k);
// System.out.println(sql);
{
Map<String,Object> v_map = query(sql).getData();
if(null == v_map)
throw new Exception("未找到任何数据!");
return v_map.get("v");
}
}
}
throw new Exception("未找到任何数据!");
}
/*
* 查询table的字段信息
*/
public BizResult desc(String table){
BizResult rst = new BizResult();
rst.setCode("0").setData(mapper.list("show full fields from "+table));
return rst;
}
/*
* 列出表的信息
*/
public BizResult table(){
BizResult rst = new BizResult();
rst.setCode("0").setData(mapper.list("SHOW TABLE STATUS"));
return rst;
}
/**
* 返回为单行数据
* @return
*/
public BizResult query(String table,String[] fields,String where){
BizResult rst = new BizResult();
if(StringUtil.isNullOrEmpty(table)){
return rst.setCode("-1").setMsg("Table Is Empty!");
}
String sql = "select {0} from {1} where {2}";
String cols = "*";
if(fields!=null && fields.length>0){
cols = StringUtil.trim(Arrays.toString(fields), 5);
}
if(StringUtil.isNullOrEmpty(where)){
where = "1 = 1";
}
sql = StringUtil.formatSql(sql, cols,table,where);
// System.out.println("QUERY:"+sql);
rst.setCode("0").setData(mapper.query(sql));
return rst;
}
public BizResult query(String sql){
BizResult rst = new BizResult();
return rst.setCode("0").setData(this.mapper.query(sql));
}
public BizResult list(String sql){
BizResult rst = new BizResult();
return rst.setCode("0").setData(this.mapper.list(sql));
}
/**
* 批量获取数据
* @param table 表
* @param fields 字段
* @param max 分页
* @param page
* @return
*/
public BizResult list(String table,String[] fields,String where,String order,int max,int page){
BizResult rst = new BizResult();
if(StringUtil.isNullOrEmpty(table)){
return rst.setCode("-1").setMsg("Table Is Empty!");
}
String sql = "select {0} from {1} where {2} {3} limit {4},{5}";
String cols = "*";
if(fields!=null && fields.length>0){
cols = StringUtil.trim(Arrays.toString(fields), 5);
}
if(StringUtil.isNullOrEmpty(where)){
where = "1 = 1";
}
if(!StringUtil.isNullOrEmpty(order)){
order = " order by "+ order;
}else{
order = "";
}
int l = page * max;
int r = l + max;
sql = StringUtil.formatSql(sql, cols,table,where,order,l,r);
// System.out.println("LIST:"+sql);
rst.setCode("0").setData(mapper.list(sql));
return rst;
}
public BizResult injoin(String[] tables,String[] fields,String on,String where,String order,int max,int page){
BizResult rst = new BizResult();
if(tables==null || tables.length<1){
return rst.setCode("-1").setMsg("Tables Can't Be Empty!");
}
String sql = "select {0} from {1} where {2} and {3} {4} limit {5},{6}";
String cols = "*",table = "";
table = StringUtil.trim(Arrays.toString(tables), 5);
if(fields!=null && fields.length>0){
cols = StringUtil.trim(Arrays.toString(fields), 5);
}
if(StringUtil.isNullOrEmpty(on)){
return rst.setCode("-1").setMsg("ON Condition Can't Be Empty!");
}
if(StringUtil.isNullOrEmpty(where)){
where = "1 = 1";
}
if(!StringUtil.isNullOrEmpty(order)){
order = " order by "+ order;
}else{
order = "";
}
int l = page * max;
int r = l + max;
sql = StringUtil.formatSql(sql, cols,table,on,where,order,l,r);
// System.out.println(sql);
rst.setCode("0").setData(mapper.list(sql));
return rst;
}
/**
* 删除
* @param table 表
* @param where 条件
* @return
*/
public BizResult delete(String table,String where){
BizResult rst = new BizResult().setCode("-1");
String sql = "delete from {0} where {1}";
sql = StringUtil.format(sql,table, where);
int rows = this.mapper.execute(sql);
//System.out.println("DELETE:"+sql);
return rst.setCode("0").setData(rows);
}
public BizResult update(Map<String,String> e,String where ,String table){
BizResult rst = new BizResult().setCode("-1");
String sql = "desc "+table;
String f,t,k;
List<Map<String,Object>> list = this.mapper.list(sql);
sql = "update {0} set {1} where {2}";
StringBuffer cols = new StringBuffer();
for(Map<String,Object> o : list){
k = o.get("Key").toString();//主键不能修改
if("PRI".equals(k)) continue;
f = o.get("Field").toString();//字段名称
if(!e.containsKey(f)) continue;//未设置字段
cols.append(f).append("=");
t = o.get("Type").toString();//字段类型
if(t.indexOf("int")<0){
//字段未非整形
cols.append("'").append(e.get(f)).append("'").append(",");
}else{
cols.append(e.get(f)).append(",");
}
}
cols = cols.deleteCharAt(cols.length()-1);
if(StringUtil.isNullOrEmpty(where))
where = "1 = 1";
sql = StringUtil.formatSql(sql,table, cols.toString(),where);
// System.out.println(sql);
int rows = this.mapper.execute(sql);
return rst.setCode("0").setData(rows);
}
/**
* 插入数据
* @param e
* @param table
* @return
*/
public BizResult insert(Map<String,String> e,String table){
BizResult rst = new BizResult().setCode("-1");
String sql = "desc "+table;
String f,t,a;
List<Map<String,Object>> list = this.mapper.list(sql);
sql = "insert into {0}({1}) values({2})";
StringBuffer cols = new StringBuffer();
StringBuffer vals = new StringBuffer();
for(Map<String,Object> o : list){
a = o.get("Extra").toString();//自增字段,过滤
if("auto_increment".equals(a)) continue;
f = o.get("Field").toString();//字段名称
if(!e.containsKey(f)) continue;//未设置字段
cols.append(f).append(",");
t = o.get("Type").toString();//字段类型
if(t.indexOf("int")<0){
//字段未非整形
vals.append("'").append(e.get(f)).append("'").append(",");
}else{
vals.append(e.get(f)).append(",");
}
}
cols = cols.deleteCharAt(cols.length()-1);
vals = vals.deleteCharAt(vals.length()-1);
sql = StringUtil.formatSql(sql,table, cols.toString(),vals.toString());
// System.out.println(sql);
int rows = this.mapper.execute(sql);
if(rows==1){
rst.setCode("0");
}
return rst;
}
public Map<String,String> dataset(String id,String key){
return null;
}
}