package com.landtool.lanbase.modules.res.controller;
|
|
import java.io.IOException;
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.HashMap;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.servlet.http.HttpServletResponse;
|
|
import org.apache.shiro.SecurityUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.ui.Model;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.ResponseBody;
|
|
import com.alibaba.fastjson.JSON;
|
import com.alibaba.fastjson.JSONArray;
|
import com.alibaba.fastjson.JSONObject;
|
import com.landtool.lanbase.config.SysTemPropertyConfig;
|
import com.landtool.lanbase.modules.api.utils.Excel;
|
import com.landtool.lanbase.modules.api.utils.JDBCUtils;
|
import com.landtool.lanbase.modules.api.utils.PageBean;
|
import com.landtool.lanbase.modules.res.entity.Res_ExtDataBase;
|
import com.landtool.lanbase.modules.res.entity.Res_ExtDataSource;
|
import com.landtool.lanbase.modules.res.entity.Res_MainInfo;
|
import com.landtool.lanbase.modules.res.entity.Res_Subscriptions;
|
import com.landtool.lanbase.modules.res.entity.SelectShuJuKu;
|
import com.landtool.lanbase.modules.res.entity.DataBaseLeftDataSource.DataBaseLeftDataSource;
|
import com.landtool.lanbase.modules.res.entity.JSONModels.DateType;
|
import com.landtool.lanbase.modules.res.entity.JSONModels.FieldMapping;
|
import com.landtool.lanbase.modules.res.entity.JSONModels.SqlMapJson;
|
import com.landtool.lanbase.modules.res.entity.JSONModels.ZiDuanPeiZi;
|
import com.landtool.lanbase.modules.res.service.ResExtDataBaseService;
|
import com.landtool.lanbase.modules.res.service.ResExtDataSourceService;
|
import com.landtool.lanbase.modules.res.service.ResMainInfoService;
|
import com.landtool.lanbase.modules.res.service.ResSubscriptionsService;
|
import com.landtool.lanbase.modules.sys.controller.AbstractController;
|
import com.landtool.lanbase.modules.sys.service.SysFieldvalueService;
|
|
import cn.hutool.json.JSONUtil;
|
|
@Controller
|
@RequestMapping("/res")
|
public class ResExtDataBaseController extends AbstractController {
|
|
@Autowired
|
private ResSubscriptionsService resSubscriptionsService;
|
|
@Autowired
|
private ResExtDataBaseService resExtDataBaseService;
|
|
@Autowired
|
private ResExtDataSourceService resExtDataSourceService;
|
|
@Autowired
|
private ResMainInfoService resMainInfoService;
|
|
@Autowired
|
private SysTemPropertyConfig sysConfig;
|
|
@Autowired
|
private SysFieldvalueService FieldUtils;
|
|
@RequestMapping("/ResManage/ResRegister/ExtDataBase")
|
public String ExtDataBase(Integer resMainInfoId, Model model) {
|
Res_ExtDataBase resExtDataBase = resExtDataBaseService.selectByMainInfoId(resMainInfoId);
|
|
//返回主键list
|
List list = new ArrayList();
|
if(resExtDataBase != null) {
|
//根据数据源id，查询到账号密码
|
Res_ExtDataSource resExtDataSource=resExtDataSourceService.selectByPrimaryKey(resExtDataBase.getDatasourceid());
|
//开始拼接sql,然后判断是哪个数据库
|
String leirong="[]";
|
if(resExtDataSource != null) {
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
|
String sql = " select name as TABLENAME from sysobjects where xtype='u' ORDER BY TABLENAME";
|
leirong = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), sql);
|
|
}
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
|
String sql = "SELECT table_name as tablename FROM user_tables ORDER BY table_name";
|
leirong = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
|
}
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")) {
|
String sql = " select table_name as TABLENAME from information_schema.tables where table_type='base table' ORDER BY TABLENAME";
|
leirong = JDBCUtils.MysqlConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
|
}
|
}
|
LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
|
if(!leirong.equals("[]")) {
|
JSONArray obj = JSON.parseArray(leirong);//获取用户信息
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
JSONArray maps = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
for (int i = 0; i < maps.size(); i++) {
|
// Map maps = (Map) JSON.parse(obj.getJSONObject(0).get("data").toString());
|
map.put(maps.getJSONObject(i).get("TABLENAME").toString(), maps.getJSONObject(i).get("TABLENAME").toString());
|
}
|
}
|
}
|
model.addAttribute("map",map);
|
//查询id对应的 name
|
Res_ExtDataSource res_extDataSource=resExtDataSourceService.selectByPrimaryKey(resExtDataBase.getDatasourceid());
|
model.addAttribute("Databasename",res_extDataSource == null ? null : res_extDataSource.getDatabasealias()); //缺少null值处理 alert 2018/12/27
|
model.addAttribute("databasetype",res_extDataSource == null ? null : res_extDataSource.getDatabasetype());
|
model.addAttribute("Res_ExtDataBase",resExtDataBase);
|
model.addAttribute("tabletype", res_extDataSource == null ? null : res_extDataSource.getTabletype());
|
model.addAttribute("resMainInfoId",resMainInfoId);
|
Res_MainInfo res_mainInfo = resMainInfoService.selectByPrimaryKey(resMainInfoId);
|
model.addAttribute("resMainInfo",res_mainInfo);
|
|
if(resExtDataBase.getPrimarykey() != null) {
|
String[] keys = resExtDataBase.getPrimarykey().split(",");
|
for (int i =0;i<keys.length;i++) {
|
list.add(keys[i]);
|
}
|
}
|
}
|
else {
|
Res_ExtDataBase resExtDataBase1 = new Res_ExtDataBase();
|
model.addAttribute("Res_ExtDataBase",resExtDataBase1);
|
model.addAttribute("resMainInfo",new Res_MainInfo());
|
model.addAttribute("tabletype", 0);
|
}
|
if(SecurityUtils.getSubject().isPermitted("org_user_admin")) {
|
//判断当前用户是否是管理员，是管理员或是未提交的资源才可以修改资源相关信息
|
model.addAttribute("admin",true);
|
}
|
else {
|
model.addAttribute("admin",false);
|
}
|
|
model.addAttribute("keys",list);
|
return "ResManage/ResRegister/ExtDataBase";
|
}
|
|
@ResponseBody
|
@RequestMapping("/resExtDataBase/deleteByPrimaryKey")
|
public int deleteByPrimaryKey(Integer resourceid) {
|
return resExtDataBaseService.deleteByPrimaryKey(resourceid);
|
}
|
|
@ResponseBody
|
@RequestMapping("/resExtDataBase/insert")
|
public int insert(Res_ExtDataBase record) {
|
return resExtDataBaseService.insert(record);
|
}
|
|
@ResponseBody
|
@RequestMapping("/resExtDataBase/insertSelective")
|
public int insertSelective(Res_ExtDataBase record) {
|
return resExtDataBaseService.insertSelective(record);
|
}
|
|
@ResponseBody
|
@RequestMapping("/resExtDataBase/selectByPrimaryKey")
|
public Res_ExtDataBase selectByPrimaryKey(Integer datasourceid) {
|
return resExtDataBaseService.selectByPrimaryKey(datasourceid);
|
}
|
|
@ResponseBody
|
@RequestMapping("/resExtDataBase/updateByPrimaryKeySelective")
|
public int updateByPrimaryKeySelective(Res_ExtDataBase record) {
|
return resExtDataBaseService.updateByPrimaryKeySelective(record);
|
}
|
|
@ResponseBody
|
@RequestMapping("/resExtDataBase/updateByPrimaryKey")
|
public int updateByPrimaryKey(Res_ExtDataBase record) {
|
return resExtDataBaseService.updateByPrimaryKey(record);
|
}
|
|
/** 插入数据库表列表功能(可以只填写必填字段)*/
|
@ResponseBody
|
@RequestMapping("/resExtDataBase/insertSelectiveAndUpdate")
|
public int insertSelectiveAndUpdate(Res_ExtDataBase record) {
|
//============20190401 wangq--字段配置json多了\ ================
|
StringBuffer sbf = new StringBuffer();
|
sbf.append("{\"sql\":");
|
JSONObject obj = (JSONObject)JSONObject.parse(record.getFieldconfig());
|
sbf.append(obj.get("sql").toString());
|
sbf.append("}");
|
record.setFieldconfig(sbf.toString());
|
//===================
|
Res_ExtDataBase resExtDataBase=resExtDataBaseService.selectByMainInfoId(record.getResourceid());
|
if(resExtDataBase == null){
|
//添加
|
Res_ExtDataSource res_extDataSource = resExtDataSourceService.selectByPrimaryKey(record.getDatasourceid());
|
if(!res_extDataSource.getDatabasetype().equals("Oracle")) {
|
if(record.getSpacename() != null) {
|
record.setSpacename(null);
|
}
|
}
|
return resExtDataBaseService.insertSelective(record);
|
}else {
|
//更新
|
Res_ExtDataSource res_extDataSource = resExtDataSourceService.selectByPrimaryKey(record.getDatasourceid());
|
if(!res_extDataSource.getDatabasetype().equals("Oracle")) {
|
if(record.getSpacename() != null) {
|
record.setSpacename(null);
|
}
|
}
|
return resExtDataBaseService.updateByPrimaryKey(record);
|
}
|
}
|
//查询id 是否填写了扩展表
|
@ResponseBody
|
@RequestMapping("/ZiYuan/selectISNULL")
|
public String selectISNULL(Integer resourceid){
|
DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(resourceid);
|
if(database==null){
|
return "1";
|
}else {
|
return "0";
|
}
|
}
|
|
|
//根据id拼接sql,查询sql
|
@RequestMapping("/ZiYuan/BaseLeftSource")
|
public String selectBaseLeftSource(SelectShuJuKu selectShuJuKu,Model model){
|
model.addAttribute("resourceid",selectShuJuKu.getResourceid());
|
DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(selectShuJuKu.getResourceid());
|
//如果没有配置，查询所有的资源
|
String leirong="0";
|
JSONArray objList = null;
|
String sqlConfig="";
|
List list=new ArrayList();
|
Map<String, String> widthMap = new HashMap<String, String>();//存自带宽度
|
if(database.getFieldconfig().equals("")){
|
String sql="SELECT a.name NAME,b.name type,cast(g.[value] as varchar(500)) [remarks] FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id where d.name='"+database.getTablename()+"' order by a.name";
|
leirong=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
|
objList = JSON.parseArray(leirong);//获取用户信息
|
StringBuffer str=new StringBuffer();
|
str.append("[");
|
for (int i = 0; i < objList.size(); i++) {
|
if(i>0){
|
sqlConfig+= ",";
|
str.append(",");
|
}
|
DateType dateType=JSONUtil.toBean(objList.get(i).toString(), DateType.class);
|
sqlConfig+=dateType.getNAME();
|
sqlConfig+=" as ";
|
sqlConfig+=dateType.getNAME();
|
str.append("{\"ZiDuanName\":\""+dateType.getNAME()+"\"");
|
str.append(",");
|
str.append("\"ZiDuanType\":\""+dateType.getType()+"\"");
|
str.append(",");
|
str.append("\"ZiDuanBieMing\":\""+dateType.getNAME()+"\"");
|
str.append(",");
|
str.append("\"ZiDuanGeShi\":{}}");
|
list.add(dateType.getNAME());
|
}
|
str.append("]");
|
JSONArray jsarr = JSONArray.parseArray(String.valueOf(str));
|
model.addAttribute("map",jsarr);
|
}else {
|
//开始拼接sql,然后判断是哪个数据库
|
JSONObject sqlObj = JSON.parseObject(database.getFieldconfig());
|
JSONArray jsarr = JSONArray.parseArray(sqlObj.getString("sql"));
|
|
for (int i = 0; i < jsarr.size(); i++) {
|
ZiDuanPeiZi ziDuanPeiZi=JSONUtil.toBean(jsarr.get(i).toString(), ZiDuanPeiZi.class);
|
if(Integer.parseInt(ziDuanPeiZi.getZiDuanKuanDu())>=0 && Integer.parseInt(ziDuanPeiZi.getZiDuanKuanDu())<50){
|
ziDuanPeiZi.setZiDuanKuanDu("50");//最小宽度
|
}
|
if(Integer.parseInt(ziDuanPeiZi.getZiDuanKuanDu())>1000){
|
ziDuanPeiZi.setZiDuanKuanDu("1000");//最大宽度
|
}
|
if(ziDuanPeiZi.getZiDuanKuanDu().equals("") || ziDuanPeiZi.getZiDuanKuanDu()==null){
|
ziDuanPeiZi.setZiDuanKuanDu("0");
|
}
|
widthMap.put(ziDuanPeiZi.getZiDuanBieMing(),ziDuanPeiZi.getZiDuanKuanDu());
|
if(i>0){
|
sqlConfig+= ",";
|
}
|
String Geshi=ziDuanPeiZi.getZiDuanGeShi().toJSONString();
|
String ZiDuanMing=ziDuanPeiZi.getZiDuanName();
|
if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("1")>0 ){
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")) {
|
|
}
|
else {
|
ZiDuanMing = "CONVERT(varchar(12)," + ziDuanPeiZi.getZiDuanName() + ",111 )";
|
}
|
}
|
if((ziDuanPeiZi.getZiDuanType().equals("小数") || ziDuanPeiZi.getZiDuanType().equals("整数")) && Geshi.indexOf("xiaoshudian")>0){
|
//获取字段保留几位小数
|
String[] xiaoshu=Geshi.split(":");
|
//获取到保留几位小数
|
String num= xiaoshu[1].replace("}","").trim();
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")) {
|
ZiDuanMing = "round(" + ziDuanPeiZi.getZiDuanName() + "," + num + ")";
|
}
|
else {
|
ZiDuanMing = "CONVERT(NUMERIC(18," + num + ")," + ziDuanPeiZi.getZiDuanName() + ")";
|
}
|
}
|
|
sqlConfig+=ZiDuanMing ;
|
sqlConfig+=" as \"";
|
sqlConfig+=ziDuanPeiZi.getZiDuanBieMing();
|
sqlConfig += "\"";
|
list.add(ziDuanPeiZi.getZiDuanBieMing());
|
// map.put(ziDuanPeiZi.getZiDuanName(),ziDuanPeiZi.getZiDuanBieMing());
|
}
|
System.out.println(jsarr);
|
model.addAttribute("map",jsarr);
|
}
|
|
String sql="select "+sqlConfig+" from "+database.getTablename() ;
|
leirong = "[]";
|
String msg = "";
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
leirong=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),
|
database.getUsername(), database.getPassword(),sql);
|
}
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
leirong=JDBCUtils.OracleConnUtils(database.getServeraddress(),database.getDatabasename(), database.getUsername(), database.getPassword(),database.getPort(),sql);
|
}
|
if(!leirong.equals("[]")){
|
JSONArray obj = JSON.parseArray(leirong);
|
// JSONObject obj =
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
// Map maps = (Map) JSON.parse(obj.getJSONObject(0).get("data").toString());
|
model.addAttribute("list", list);
|
model.addAttribute("chaxun", obj.getJSONObject(0).get("data").toString());
|
|
double width = Double.parseDouble("96") / Double.parseDouble(String.valueOf(list.size()));
|
String columns = "[{ xtype: 'rownumberer', text: '序号', width: 50, align: 'center' }";
|
for (int i = 0; i < list.size(); i++) {
|
columns += ",{ text: '" + list.get(i) + "', dataIndex: '" + list.get(i) + "', width: " + widthMap.get(list.get(i)) + ", sortable: true, align: 'center', filter: { type: 'string' } }";
|
}
|
columns += " ]";
|
JSONArray arr = JSONObject.parseArray(columns);
|
model.addAttribute("columns", arr);
|
System.out.println(columns);
|
}
|
else {
|
JSONArray arr = JSONObject.parseArray("[]");
|
model.addAttribute("list","[]");
|
model.addAttribute("chaxun","[]");
|
model.addAttribute("columns" ,arr);
|
msg = obj.getJSONObject(0).get("msg").toString();
|
}
|
}else {
|
JSONArray arr = JSONObject.parseArray("[]");
|
model.addAttribute("list","[]");
|
model.addAttribute("chaxun","[]");
|
model.addAttribute("columns" ,arr);
|
}
|
model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
|
model.addAttribute("systemName", sysConfig.getAppFullName());
|
model.addAttribute("msg",msg);
|
return "/ZiYuanZhongXin/BaseLeftSource";
|
}
|
|
//数据库别名页面
|
@RequestMapping("/ResManage/ResRegister/ZiDuanBieMingBasic")
|
public String ZiDuanBieMingBasic(Model model,Res_ExtDataSource res_extDataSource,String tablename,Integer resMainInfoId,Integer bianji,String primarykey){
|
//根据数据源id，查询到账号密码
|
model.addAttribute("datasourceid",res_extDataSource.getDatasourceid());
|
model.addAttribute("tablename",tablename);
|
//fieldconfig是 字段配置文本框里面的内容
|
model.addAttribute("resMainInfoId",resMainInfoId);
|
//如果bianji ==null 那么 bianji ==0()
|
if(bianji==null){
|
bianji=0;
|
}
|
model.addAttribute("bianji",bianji);
|
model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
|
model.addAttribute("systemName", sysConfig.getAppFullName());
|
model.addAttribute("primarykey", primarykey);
|
return "/ZiYuanZhongXin/ZiDuanBieMingBasic";
|
}
|
|
/**
|
* wq:查询关联字典内容
|
* @param key
|
* @return
|
*/
|
@ResponseBody
|
@RequestMapping("/ResManage/ResRegister/selectDictionry")
|
public LinkedHashMap<String, String> selectDictionry(String key){
|
LinkedHashMap<String, String> MapTypeList = FieldUtils.getFieldListByKey(key);
|
return MapTypeList;
|
}
|
/**
|
* wq:字段映射配置跳转
|
* @param model
|
* @param datasourceid
|
* @param resourceid
|
* @param tablename
|
* @return
|
*/
|
@RequestMapping("/ResManage/ResRegister/fieldMapping")
|
public String fieldMapping(Model model,String datasourceid,String resourceid,String tablename){
|
model.addAttribute("resourceid", resourceid);
|
model.addAttribute("datasourceid", datasourceid);
|
model.addAttribute("tablename", tablename);
|
model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
|
return "/ZiYuanZhongXin/ZiDuanYingSheBasic";
|
}
|
|
|
/**
|
* wq:字段映射配置
|
* @param model
|
* @param res_Subscriptions
|
* @return
|
*/
|
@RequestMapping("/ResManage/ResRegister/fieldMapping_Edit")
|
public String fieldMapping_Edit(Model model,Res_Subscriptions res_Subscriptions){
|
List<ZiDuanPeiZi> fieldMappings = new ArrayList<ZiDuanPeiZi>(); //数据源表字段集合
|
List<FieldMapping> fieldlist = new ArrayList<FieldMapping>();
|
List<String> fieldNames = new ArrayList<String>();
|
|
//数据源已配置暴露字段
|
DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(res_Subscriptions.getResourceid());
|
model.addAttribute("primarykeys", database.getPrimarykey());
|
//数据源表类型(1为属性,2为空间)
|
model.addAttribute("tabletype", database.getTabletype());
|
model.addAttribute("resourceid", res_Subscriptions.getResourceid());
|
if(database!=null && database.getFieldconfig()!=null && !database.getFieldconfig().equals("")){
|
JSONObject json = JSONObject.parseObject(database.getFieldconfig());
|
JSONArray jsonArray = json.getJSONArray("sql");
|
for(int i=0;i<jsonArray.size();i++){
|
ZiDuanPeiZi fieldMapping =JSONUtil.toBean(String.valueOf(jsonArray.get(i)), ZiDuanPeiZi.class);
|
if(database.getTabletype()==1){ //属性表
|
fieldMappings.add(fieldMapping);
|
}else{ //空间表
|
if(!fieldMapping.getZiDuanName().contains("$") && !fieldMapping.getZiDuanName().equals("SHAPE") && !fieldMapping.getZiDuanName().equals("OBJECTID")){
|
fieldMappings.add(fieldMapping);
|
}
|
}
|
}
|
}
|
|
//订阅表已配置映射字段
|
Res_Subscriptions resSubscriptions = resSubscriptionsService.selectSubscriptions(res_Subscriptions);
|
if(resSubscriptions!=null && resSubscriptions.getTablefields()!=null && !"".equals(resSubscriptions.getTablefields())){
|
JSONObject json = JSONObject.parseObject(resSubscriptions.getTablefields());
|
JSONArray jsonArray = json.getJSONArray("fieldMappings");
|
for(int i=0;i<jsonArray.size();i++){
|
FieldMapping fieldMapping =JSONUtil.toBean(String.valueOf(jsonArray.get(i)), FieldMapping.class);
|
fieldMapping.setDataSourceList(fieldMappings);
|
fieldlist.add(fieldMapping);
|
fieldNames.add(fieldMapping.getFieldName());
|
}
|
}
|
|
//目标表数据库
|
Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_Subscriptions.getDatasourceid());
|
//目标表字段集合
|
List<FieldMapping> fields = selectTargetSourceByDataSourceId(database.getTabletype(),res_Subscriptions.getTablename(),resExtDataSource);
|
if(!fields.isEmpty() && fields.size()>0){
|
for(FieldMapping field :fields){
|
if(!fieldNames.contains(field.getFieldName()) && field.getIsNullAble().equals("0")){
|
field.setDataSourceList(fieldMappings);
|
fieldlist.add(field);
|
}
|
}
|
for(FieldMapping field :fields){
|
if(!fieldNames.contains(field.getFieldName()) && field.getIsNullAble().equals("1")){
|
field.setDataSourceList(fieldMappings);
|
fieldlist.add(field);
|
}
|
}
|
model.addAttribute("isnew", 0);
|
}else{
|
//目标表不存在
|
for(ZiDuanPeiZi field : fieldMappings){
|
FieldMapping mapping = new FieldMapping();
|
mapping.setFieldName(field.getZiDuanName());
|
mapping.setFieldType(field.getZiDuanType());
|
mapping.setDataSourceList(fieldMappings);
|
mapping.setFieldMappingType(field.getZiDuanType());
|
String[] keys = database.getPrimarykey().split(",");
|
if(Arrays.asList(keys).contains(field.getZiDuanName())){
|
mapping.setIsPrimaryKey(1);
|
}
|
fieldlist.add(mapping);
|
}
|
model.addAttribute("isnew", 1);
|
}
|
model.addAttribute("fieldlist", fieldlist);
|
return "/ZiYuanZhongXin/ZiDuanYingShe";
|
}
|
|
/**
|
* wq:查询目标表字段集合
|
* @param tablename
|
* @param datasourceid
|
* @return
|
*/
|
private List<FieldMapping> selectTargetSourceByDataSourceId(Integer tabletype,String tablename,Res_ExtDataSource resExtDataSource) {
|
|
List<FieldMapping> flist = new ArrayList<FieldMapping>();
|
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
String sql = "SELECT D.column_name AS fieldName,D.data_type AS fieldType,B.is_nullable AS isNullAble FROM SYS.tables A INNER JOIN SYS.columns B ON B.object_id = A.object_id LEFT JOIN SYS.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id LEFT JOIN(SELECT table_name,column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS)D ON D.column_name = B.name and D.TABLE_NAME = A.name WHERE A.name='"+tablename+"'";
|
String json=JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
for(int i =0;i<jsonarr.size();i++){
|
FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
|
if(fieldMapping.getIsNullAble().equals("false")){
|
fieldMapping.setIsNullAble("0");
|
}else{
|
fieldMapping.setIsNullAble("1");
|
}
|
if(tabletype==1){
|
flist.add(fieldMapping);
|
}else{
|
if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") &&
|
!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
|
flist.add(fieldMapping);
|
}
|
}
|
}
|
}
|
}
|
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
|
String sql = "select distinct(t.column_name) as "+"\"fieldName\",t.data_type as "+"\"fieldType\",t.NULLABLE as "+"\"isNullAble\" from all_tab_cols t where t.table_name='"+tablename+"'";
|
String json=JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
for(int i =0;i<jsonarr.size();i++){
|
FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
|
if(fieldMapping.getIsNullAble().equals("N")){
|
fieldMapping.setIsNullAble("0");
|
}else{
|
fieldMapping.setIsNullAble("1");
|
}
|
if(tabletype==1){
|
flist.add(fieldMapping);
|
}else{
|
if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") &&
|
!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
|
flist.add(fieldMapping);
|
}
|
}
|
}
|
}
|
}
|
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
|
String sql ="SELECT COLUMN_NAME AS fieldName,DATA_TYPE AS fieldType FROM information_schema.COLUMNS where table_name='"+tablename+"'";
|
String json=JDBCUtils.MysqlConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
for(int i =0;i<jsonarr.size();i++){
|
FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
|
if(tabletype==1){
|
flist.add(fieldMapping);
|
}else{
|
if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") &&
|
!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
|
flist.add(fieldMapping);
|
}
|
}
|
}
|
}
|
}
|
return flist;
|
}
|
|
/**
|
* @param tablename
|
* @param resMainInfoId
|
* @return
|
*/
|
@ResponseBody
|
@RequestMapping("/ResManage/ResRegister/changeFieldConfig")
|
public String changeFieldConfig(String tablename,Integer resMainInfoId){
|
String FieldConfig ="";
|
Res_ExtDataBase res_extDataBase=resExtDataBaseService.selectByMainInfoId(resMainInfoId);
|
if(res_extDataBase!=null && res_extDataBase.getTablename().equals(tablename)){
|
FieldConfig = res_extDataBase.getFieldconfig();
|
}
|
return FieldConfig;
|
}
|
|
|
/**
|
* 判断字段是长整还是短整
|
* @param resourceid
|
* @param columnName
|
* @return
|
*/
|
@ResponseBody
|
@RequestMapping("/ResManage/ResRegister/selectColumnType")
|
public String selectColumnType(Integer resourceid,String columnName){
|
//数据源
|
DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(resourceid);
|
String type = "short";
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
String sql = "select data_precision data_scale from all_tab_columns where table_name='"+database.getTablename()+"' and COLUMN_NAME='"+columnName+"'";
|
String json=JDBCUtils.OracleConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),database.getPort(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
String length = jsonarr.getJSONObject(0).get("DATA_SCALE").toString();
|
type = Integer.parseInt(length)>5?"long":"short";
|
}
|
}
|
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
String sql = "select a.length as ZiDuanKuanDu from syscolumns a ,systypes b where a.xtype=b.xtype and a.id=(select id from sysobjects where name='"+database.getTablename()+"') and a.name='"+columnName+"'";
|
String json=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
String length = jsonarr.getJSONObject(0).get("ZiDuanKuanDu").toString();
|
type = Integer.parseInt(length)>5?"long":"short";
|
}
|
}
|
|
if(database.getDatabasetype().equalsIgnoreCase("MySQL")){
|
String sql = "select character_maximum_length as ZiDuanKuanDu from information_schema.columns where table_name='"+database.getTablename()+"') and column_name='"+columnName+"'";
|
String json=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
String length = jsonarr.getJSONObject(0).get("ZiDuanKuanDu").toString();
|
if(length!=null && !length.equals("")){
|
type = Integer.parseInt(length)>5?"long":"short";
|
}
|
}
|
}
|
return type;
|
}
|
|
@RequestMapping("/ResManage/ResRegister/ZiDuanBieMingBasic_Edit")
|
public String ZiDuanBieMingBasic_Edit(Model model,Res_ExtDataSource res_extDataSource,String tablename,String primarykey
|
,Integer resMainInfoId,Integer bianji){
|
//bianji==1 表示之前有内容，我点了编辑按钮，然后不查询resMainInfoId关联的字符串内容
|
//第二次打开时，加载之前的配置信息。要做一个判断
|
model.addAttribute("primarykey",primarykey);
|
Res_ExtDataBase res_extDataBase=resExtDataBaseService.selectByMainInfoId(resMainInfoId);
|
String FileldConfig="";
|
JSONArray jsonArray = null;
|
List list=new ArrayList();
|
if(res_extDataBase!=null){
|
if(!res_extDataBase.getFieldconfig().equals("") && res_extDataBase.getTablename().equals(tablename)){
|
FileldConfig=res_extDataBase.getFieldconfig();
|
JSONObject json;
|
json = JSONObject.parseObject(FileldConfig);
|
jsonArray= JSONArray.parseArray(json.getString("sql"));
|
//获取配置字段
|
for (int i = 0; i < jsonArray.size(); i++) {
|
ZiDuanPeiZi ziDuanPeiZi=JSONUtil.toBean(jsonArray.get(i).toString(), ZiDuanPeiZi.class);
|
if(ziDuanPeiZi.getZiDuanType().equals("int") || ziDuanPeiZi.getZiDuanType().equals("tinyint")){
|
String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"整数\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
|
jsonArray.set(i,JSONObject.parseObject(js));
|
}
|
if(ziDuanPeiZi.getZiDuanType().equals("nvarchar") || ziDuanPeiZi.getZiDuanType().equals("varchar") ||ziDuanPeiZi.getZiDuanType().equals("char")){
|
String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"字符串\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
|
jsonArray.set(i,JSONObject.parseObject(js));
|
}
|
if(ziDuanPeiZi.getZiDuanType().equals("datetime")){
|
String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"时间\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
|
jsonArray.set(i,JSONObject.parseObject(js));
|
}
|
if(ziDuanPeiZi.getZiDuanType().equals("decimal")){
|
String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"小数\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
|
jsonArray.set(i,JSONObject.parseObject(js));
|
}
|
String ZiDuan[]=new String[]{ziDuanPeiZi.getZiDuanName(),ziDuanPeiZi.getZiDuanBieMing(),ziDuanPeiZi.getZiDuanGeShi().toString(),ziDuanPeiZi.getZiDuanKuanDu()};
|
list.add(ZiDuan);
|
}
|
}
|
}
|
if(bianji==1){
|
//根据数据源id，查询到账号密码
|
Res_ExtDataSource resExtDataSource=resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
|
//开始拼接sql,然后判断是哪个数据库
|
String leirong="0";
|
JSONArray obj = null;
|
JSONArray ob = null;
|
JSONArray jsonarr = new JSONArray();
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
// String sql="SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('"+tablename+"')";
|
String sql="SELECT a.name NAME,b.name type,cast(g.[value] as varchar(500)) [remarks] FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id where d.name='"+tablename+"' order by a.name";
|
leirong=JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),sql);
|
obj = JSON.parseArray(leirong);//获取用户信息
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
//选择过的资源
|
int index = -1;
|
for (int j = 0; j < list.size(); j++) {
|
for (int i = 0; i < ob.size(); i++) {
|
Integer k = 0;
|
DateType dateType = JSONUtil.toBean(ob.get(i).toString(), DateType.class);
|
if (dateType.getRemarks() == null) {
|
dateType.setRemarks("");
|
}
|
String js = "";
|
String[] listDate = null;
|
//增加一个是否显示要勾选的字段
|
if (dateType.getNAME().equals(((String[]) list.get(j))[0])) {
|
listDate = (String[]) list.get(j);
|
k = 1;
|
}
|
if(k==1){
|
index++;
|
js = IsZiDuanXuanZe(k, dateType, js, listDate);
|
jsonarr.set(index, JSONObject.parseObject(js));
|
}
|
}
|
}
|
for (int m = 0; m < ob.size(); m++) {
|
Integer k = 0;
|
DateType dateType = JSONUtil.toBean(ob.get(m).toString(), DateType.class);
|
if (dateType.getRemarks() == null) {
|
dateType.setRemarks("");
|
}
|
String js = "";
|
String[] listDate = null;
|
for (int n = 0; n < list.size(); n++) {
|
//增加一个是否显示要勾选的字段
|
if (dateType.getNAME().equals(((String[]) list.get(n))[0])) {
|
listDate = (String[]) list.get(n);
|
k = 1;
|
}
|
}
|
if(k!=1){
|
index++;
|
js = IsZiDuanXuanZe(k, dateType, js, listDate);
|
jsonarr.set(index, JSONObject.parseObject(js));
|
}
|
}
|
}
|
}
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
|
// String sql="SELECT USER_TAB_COLS.COLUMN_NAME as name , USER_TAB_COLS.DATA_TYPE as \"type\",user_col_comments.comments as \"remarks\" FROM USER_TAB_COLS inner join user_col_comments on user_col_comments.TABLE_NAME=USER_TAB_COLS.TABLE_NAME and user_col_comments.COLUMN_NAME=USER_TAB_COLS.COLUMN_NAME where USER_TAB_COLS.TABLE_NAME='"+tablename+"'";
|
String sql ="select distinct(a.column_name) as name,a.comments as \"remarks\",b.data_type as \"type\" from user_tab_columns b,user_col_comments a where a.table_name=b.table_name and a.column_name=b.column_name and a.table_name='"+tablename+"'";
|
System.out.println(sql);
|
leirong=JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
|
obj = JSON.parseArray(leirong);//获取用户信息
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
//选择过的资源
|
int index = -1;
|
for (int j = 0; j < list.size(); j++) {
|
for (int i = 0; i < ob.size(); i++) {
|
Integer k = 0;
|
DateType dateType = JSONUtil.toBean(ob.get(i).toString(), DateType.class);
|
if (dateType.getRemarks() == null) {
|
dateType.setRemarks("");
|
}
|
String js = "";
|
String[] listDate = null;
|
//增加一个是否显示要勾选的字段
|
if (dateType.getNAME().equals(((String[]) list.get(j))[0])) {
|
listDate = (String[]) list.get(j);
|
k = 1;
|
}
|
if(k==1){
|
index++;
|
js = IsZiDuanXuanZe(k, dateType, js, listDate);
|
jsonarr.set(index, JSONObject.parseObject(js));
|
}
|
}
|
}
|
for (int m = 0; m < ob.size(); m++) {
|
Integer k = 0;
|
DateType dateType = JSONUtil.toBean(ob.get(m).toString(), DateType.class);
|
if (dateType.getRemarks() == null) {
|
dateType.setRemarks("");
|
}
|
String js = "";
|
String[] listDate = null;
|
for (int n = 0; n < list.size(); n++) {
|
//增加一个是否显示要勾选的字段
|
if (dateType.getNAME().equals(((String[]) list.get(n))[0])) {
|
listDate = (String[]) list.get(n);
|
k = 1;
|
}
|
}
|
if(k!=1){
|
index++;
|
js = IsZiDuanXuanZe(k, dateType, js, listDate);
|
jsonarr.set(index, JSONObject.parseObject(js));
|
}
|
}
|
}
|
}
|
model.addAttribute("list",jsonarr);
|
System.out.println(jsonarr);
|
}
|
|
if(bianji==null){
|
bianji=0;
|
}
|
if(bianji==1){
|
FileldConfig=null;
|
jsonArray = null;
|
|
}
|
|
model.addAttribute("jsonArray",jsonArray);
|
model.addAttribute("FileldConfig",FileldConfig);
|
model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
|
model.addAttribute("systemName", sysConfig.getAppFullName());
|
return "/ZiYuanZhongXin/ZiDuanBieMingBasic_Edit";
|
}
|
|
//sqlserver 数据替换
|
private String IsZiDuanXuanZe(Integer k, DateType dateType, String js, String[] listDate) {
|
if(k == 1){
|
if(listDate[2].equals("")){
|
listDate[2]="{}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("long") || dateType.getType().equalsIgnoreCase("int") || dateType.getType().equalsIgnoreCase("number") || dateType.getType().equalsIgnoreCase("tinyint")){
|
js="{\"type\": \"整数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("varchar2") || dateType.getType().equalsIgnoreCase("nvarchar2") || dateType.getType().equalsIgnoreCase("nvarchar") || dateType.getType().equalsIgnoreCase("varchar") ||dateType.getType().equalsIgnoreCase("char")){
|
js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("date") || dateType.getType().equalsIgnoreCase("datetime") || dateType.getType().equalsIgnoreCase("TIMESTAMP")){
|
js="{\"type\": \"时间\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("decimal") || dateType.getType().equalsIgnoreCase("float") || dateType.getType().equalsIgnoreCase("double")){
|
js="{\"type\": \"小数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("nclob") || dateType.getType().equalsIgnoreCase("blob") || dateType.getType().equalsIgnoreCase("binary") || dateType.getType().equalsIgnoreCase("bit") || dateType.getType().equalsIgnoreCase("tinyblob") || dateType.getType().equalsIgnoreCase("varbinary") || dateType.getType().equalsIgnoreCase("image")){
|
js="{\"type\": \"二进制对象\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
}
|
else {
|
js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
}
|
}
|
else{
|
if(dateType.getType().equalsIgnoreCase("number") || dateType.getType().equalsIgnoreCase("long") || dateType.getType().equalsIgnoreCase("int") || dateType.getType().equalsIgnoreCase("tinyint")){
|
js="{\"type\": \"整数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("varchar") || dateType.getType().equalsIgnoreCase("varchar2") || dateType.getType().equalsIgnoreCase("nvarchar") || dateType.getType().equalsIgnoreCase("varchar") ||dateType.getType().equalsIgnoreCase("char") || dateType.getType().equalsIgnoreCase("ntext")){
|
js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("datetime") || dateType.getType().equalsIgnoreCase("date") || dateType.getType().equalsIgnoreCase("TIMESTAMP")){
|
js="{\"type\": \"时间\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
}
|
else if(dateType.getType().equalsIgnoreCase("decimal") || dateType.getType().equalsIgnoreCase("float") || dateType.getType().equalsIgnoreCase("double")){
|
js="{\"type\": \"小数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\":\"{}\"}";
|
}else if(dateType.getType().equalsIgnoreCase("nclob") || dateType.getType().equalsIgnoreCase("blob") || dateType.getType().equalsIgnoreCase("binary") || dateType.getType().equalsIgnoreCase("bit") || dateType.getType().equalsIgnoreCase("tinyblob") || dateType.getType().equalsIgnoreCase("varbinary") || dateType.getType().equalsIgnoreCase("image")){
|
js="{\"type\": \"二进制对象\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
}else {
|
js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
|
}
|
}
|
return js;
|
}
|
|
//oracle 数据替换
|
private String IsOracleZiDuanXuanZe(Integer k, DateType dateType, String js, String[] listDate) {
|
if(k == 1){
|
if(listDate[2].equals("")){
|
listDate[2]="{}";
|
}
|
else if(dateType.getType().equals("NUMBER") || dateType.getType().equals("INTEGER") ||dateType.getType().equals("FLOAT") ){
|
js="{\"type\": \"整数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
|
}
|
else if(dateType.getType().equals("NVARCHAR2") || dateType.getType().equals("VARCHAR2") ||dateType.getType().equals("CHAR") || dateType.getType().equals("NCHAR")){
|
js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
|
}
|
else if(dateType.getType().equals("DATE") || dateType.getType().equals("TIMESTAMP")){
|
js="{\"type\": \"时间\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
|
}
|
else if(dateType.getType().equals("DECIMAL")){
|
js="{\"type\": \"小数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
|
}else{
|
js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
|
}
|
|
}
|
else{
|
if(dateType.getType().equals("NUMBER") || dateType.getType().equals("INTEGER") ||dateType.getType().equals("FLOAT") ){
|
js="{\"type\": \"整数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
|
}
|
else if(dateType.getType().equals("NVARCHAR2") || dateType.getType().equals("VARCHAR2") ||dateType.getType().equals("CHAR") || dateType.getType().equals("NCHAR")){
|
js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
|
}
|
else if(dateType.getType().equals("DATE") || dateType.getType().equals("TIMESTAMP")){
|
js="{\"type\": \"时间\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
|
}
|
else if(dateType.getType().equals("DECIMAL")){
|
js="{\"type\": \"小数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\":\"{}\"}";
|
|
}else {
|
js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
|
}
|
}
|
return js;
|
}
|
|
@ResponseBody
|
@RequestMapping("/ZiYuan/BaseLeftSourceShuJu")
|
public String BaseLeftSourceShuJu(SelectShuJuKu selectShuJuKu, Model model, PageBean pageBean){
|
DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(selectShuJuKu.getResourceid());
|
String sqlConfig="";
|
String sqlid="";
|
String leirong="";
|
JSONArray objList = null;
|
LinkedHashMap<String, String> ZiDianMap=null;
|
LinkedHashMap<String,JSONArray > SqlMap=null;
|
if(database.getFieldconfig().equals("")){
|
String sql="SELECT a.name NAME,b.name type,cast(g.[value] as varchar(500)) [remarks] FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id where d.name='"+database.getTablename()+"' order by a.name";
|
leirong=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
|
objList = JSON.parseArray(leirong);//获取用户信息
|
for (int i = 0; i < objList.size(); i++) {
|
if(i>0){
|
sqlConfig+= ",";
|
}
|
DateType dateType=JSONUtil.toBean(objList.get(i).toString(), DateType.class);
|
sqlConfig+=dateType.getNAME();
|
sqlConfig+=" as \"";
|
sqlConfig+=dateType.getRemarks();
|
sqlConfig += "\"";
|
}
|
}else {
|
//开始拼接sql,然后判断是哪个数据库
|
//获取json
|
JSONObject sqlObj = JSON.parseObject(database.getFieldconfig());
|
JSONArray jsarr = sqlObj.getJSONArray("sql");
|
sqlConfig="";
|
|
//字典处理，记录配置的别名，和配置的字典名称，等查询到结果之后，处理字典
|
ZiDianMap=new LinkedHashMap<>();
|
//sql处理，记录配置sql的字段
|
SqlMap=new LinkedHashMap<>();
|
for (int i = 0; i < jsarr.size(); i++) {
|
ZiDuanPeiZi ziDuanPeiZi=JSONUtil.toBean(jsarr.get(i).toString(), ZiDuanPeiZi.class);
|
if(i>0){
|
sqlConfig+= ",";
|
}
|
//sqlserver分页排序 可以写字段名，但是oracl排序设置了别名要用别名排序
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
sqlid=((JSONObject) jsarr.get(0)).get("ZiDuanName").toString();
|
}
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
sqlid=((JSONObject) jsarr.get(0)).get("ZiDuanBieMing").toString();
|
}
|
|
String Geshi=ziDuanPeiZi.getZiDuanGeShi().toJSONString();
|
String ZiDuanMing=ziDuanPeiZi.getZiDuanName();
|
if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("1")>0 ){
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
ZiDuanMing="CONVERT(varchar(12),"+ziDuanPeiZi.getZiDuanName()+",111 )";
|
}
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
ZiDuanMing=" to_date('"+ziDuanPeiZi.getZiDuanName()+"','yyyy-mm-dd')";
|
}
|
}
|
if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("3")>0 ){
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
ZiDuanMing="DATENAME(YEAR,"+ziDuanPeiZi.getZiDuanName()+")+'年'+DATENAME(month,"+ziDuanPeiZi.getZiDuanName()+")+'月'+DATENAME(day,"+ziDuanPeiZi.getZiDuanName()+")+'日'";
|
}
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
ZiDuanMing="to_char("+ziDuanPeiZi.getZiDuanName()+",'yyyy\"年\"MM\"月\"dd\"日\"')";
|
}
|
}
|
if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("4")>0 ){
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
ZiDuanMing="DATENAME(YEAR,"+ziDuanPeiZi.getZiDuanName()+")+'年'+DATENAME(month,"+ziDuanPeiZi.getZiDuanName()+")+'月'";
|
}
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
ZiDuanMing="to_char("+ziDuanPeiZi.getZiDuanName()+",'yyyy\"年\"MM\"月\"')";
|
}
|
}
|
if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("5")>0 ){
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
ZiDuanMing="DATENAME(YEAR,"+ziDuanPeiZi.getZiDuanName()+")+'年'";
|
}
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
ZiDuanMing="to_char(LASTEDITDATE,'yyyy\"年\"')";
|
}
|
}
|
if(ziDuanPeiZi.getZiDuanType().equals("小数") && Geshi.indexOf("xiaoshudian")>0){
|
//获取字段保留几位小数
|
String[] xiaoshu=Geshi.split(":");
|
//获取到保留几位小数
|
String num= xiaoshu[1].replace("}","").trim();
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
ZiDuanMing="Convert(NUMERIC(18,"+num+"),"+ziDuanPeiZi.getZiDuanName()+")";
|
}
|
}
|
if(Geshi.indexOf("guanlianzidian")>0){
|
//获取字典数组
|
String[] ZiDian=Geshi.split(":");
|
//获取到关联的是哪个字典
|
String ZiDianBiaoShi= ZiDian[1].replace("}","").replace("\"","").replace("\"","").trim();
|
ZiDianMap.put(ziDuanPeiZi.getZiDuanBieMing(),ZiDianBiaoShi);
|
|
}
|
if(Geshi.indexOf("SQLchaxun")>0){
|
SqlMap.put(ziDuanPeiZi.getZiDuanBieMing(),JSON.parseObject(Geshi).getJSONArray("SQLchaxun"));
|
}
|
sqlConfig+=ZiDuanMing ;
|
sqlConfig+=" as \"";
|
sqlConfig+=ziDuanPeiZi.getZiDuanBieMing().replace("（","").replace("）","").trim();
|
sqlConfig += "\"";
|
// map.put(ziDuanPeiZi.getZiDuanName(),ziDuanPeiZi.getZiDuanBieMing());
|
}
|
}
|
System.out.println(sqlConfig);
|
String sql="select "+sqlConfig+" from "+database.getTablename() ;
|
//查询总数的sql
|
String count ="select count(*) as zongshu from "+database.getTablename();
|
leirong = "[]";
|
Integer totalCount=0;
|
|
if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
//判断查询有没有
|
if(selectShuJuKu.getSelcetZiDuan().length()>0){
|
if(selectShuJuKu.getSelcetZiDuanZhi().length()>0){
|
count="select count(*) as zongshu from "+database.getTablename() +" where "+selectShuJuKu.getSelcetZiDuan() +" like '%"+selectShuJuKu.getSelcetZiDuanZhi()+"%'";
|
}
|
}
|
if(selectShuJuKu.getSelcetZiDuan().length()>0){
|
if(!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")){
|
count="select count(*) as zongshu from "+database.getTablename() +" where ";
|
if(!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= CAST ( '"+selectShuJuKu.getPubdateBegin()+"' AS datetime )";
|
}
|
if(!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= CAST ( '"+selectShuJuKu.getPubdatefinish()+"' AS datetime )";
|
}
|
if(!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("") ){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= CAST ( '"+selectShuJuKu.getPubdateBegin()+"' AS datetime )";
|
count+=" and ";
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= CAST ( '"+selectShuJuKu.getPubdatefinish()+"' AS datetime )";
|
}
|
|
}
|
}
|
//判断是否是整数查询
|
if(selectShuJuKu.getSelcetZiDuan().length()>0){
|
if(selectShuJuKu.getIntNumBegin()!=null || selectShuJuKu.getIntNumfinish()!=null){
|
count="select count(*) as zongshu from "+database.getTablename()+" where ";
|
if(selectShuJuKu.getIntNumBegin()!=null && selectShuJuKu.getIntNumfinish()==null){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
|
}
|
if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()==null){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
|
}
|
if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()!=null){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
|
count+=" and ";
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
|
}
|
}
|
}
|
|
String zongshu=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),
|
database.getUsername(), database.getPassword(),count);
|
JSONArray obj = JSON.parseArray(zongshu);
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
Map maps = (Map) JSON.parse(ob.get(0).toString());
|
totalCount = (int) maps.get("zongshu");
|
pageBean.setLimit(20);
|
PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), (int) maps.get("zongshu"));
|
//拼接sql ，分页查询
|
String fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by (select 0))as rownumber," + sqlConfig + " from " + database.getTablename() + ") as o where rownumber>" + pageData.getStartIndex() + "";
|
|
//判断查询有没有s
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
|
fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by (select 0))as rownumber," + sqlConfig + " from " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " like'%" + selectShuJuKu.getSelcetZiDuanZhi() + "%') as o where rownumber>" + pageData.getStartIndex() + "";
|
}
|
}
|
|
//判断 是否是时间字段
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")) {
|
String tiaojian = "";
|
if (!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= CAST ( '" + selectShuJuKu.getPubdateBegin() + "' AS datetime )";
|
}
|
if (!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= CAST ( '" + selectShuJuKu.getPubdatefinish() + "' AS datetime )";
|
}
|
if (!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("")) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= CAST ( '" + selectShuJuKu.getPubdateBegin() + "' AS datetime )";
|
tiaojian += " and ";
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= CAST ( '" + selectShuJuKu.getPubdatefinish() + "' AS datetime )";
|
}
|
fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by (select 0))as rownumber,"
|
+ sqlConfig + " from " + database.getTablename() + " where "
|
+ tiaojian +
|
") as o where rownumber>" + pageData.getStartIndex() + "";
|
}
|
}
|
//判断 是否是整数查询
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getIntNumBegin() != null || selectShuJuKu.getIntNumfinish() != null) {
|
String tiaojian = "";
|
if (selectShuJuKu.getIntNumBegin() != null && selectShuJuKu.getIntNumfinish() == null) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
|
}
|
if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() == null) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
|
}
|
if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() != null) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
|
tiaojian += " and ";
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
|
}
|
fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by (select 0))as rownumber,"
|
+ sqlConfig + " from " + database.getTablename() + " where "
|
+ tiaojian +
|
") as o where rownumber>" + pageData.getStartIndex() + "";
|
|
}
|
}
|
|
System.out.println(fenye);
|
leirong = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
|
database.getUsername(), database.getPassword(), fenye);
|
//处理关联字典字段
|
}
|
|
}
|
|
if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
|
//判断查询有没有
|
if(selectShuJuKu.getSelcetZiDuan().length()>0){
|
if(selectShuJuKu.getSelcetZiDuanZhi().length()>0){
|
count="select count(*) as zongshu from "+database.getTablename() +" where "+selectShuJuKu.getSelcetZiDuan() +" like '%"+selectShuJuKu.getSelcetZiDuanZhi()+"%'";
|
}
|
}
|
if(selectShuJuKu.getSelcetZiDuan().length()>0){
|
if(!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")){
|
count="select count(*) as zongshu from "+database.getTablename() +" where ";
|
if(!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= to_date ( '"+selectShuJuKu.getPubdateBegin()+"','yyyy-mm-dd' )";
|
}
|
if(!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= to_date ( '"+selectShuJuKu.getPubdatefinish()+"' ,'yyyy-mm-dd' )";
|
}
|
if(!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("") ){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= to_date ( '"+selectShuJuKu.getPubdateBegin()+"' ,'yyyy-mm-dd' )";
|
count+=" and ";
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= to_date ( '"+selectShuJuKu.getPubdatefinish()+"' ,'yyyy-mm-dd')";
|
}
|
}
|
}
|
|
//判断是否是整数查询
|
if(selectShuJuKu.getSelcetZiDuan().length()>0){
|
if(selectShuJuKu.getIntNumBegin()!=null || selectShuJuKu.getIntNumfinish()!=null){
|
count="select count(*) as zongshu from "+database.getTablename()+" where ";
|
if(selectShuJuKu.getIntNumBegin()!=null && selectShuJuKu.getIntNumfinish()==null){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
|
}
|
if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()==null){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
|
}
|
if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()!=null){
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
|
count+=" and ";
|
count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
|
}
|
}
|
}
|
|
String zongshu=JDBCUtils.OracleConnUtils(database.getServeraddress(),database.getDatabasename(),
|
database.getUsername(), database.getPassword(),database.getPort(),count);
|
JSONArray obj = JSON.parseArray(zongshu);
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
Map maps = (Map) JSON.parse(ob.get(0).toString());
|
totalCount = Integer.parseInt(maps.get("ZONGSHU").toString());
|
pageBean.setLimit(20);
|
PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), totalCount);
|
//拼接sql ，分页查询
|
//判断查询有没有
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
|
sql = "select " + sqlConfig + " from " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " like '%" + selectShuJuKu.getSelcetZiDuanZhi() + "%'";
|
}
|
}
|
//判断 是否是时间字段
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")) {
|
String tiaojian = "";
|
if (!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= to_date ( '" + selectShuJuKu.getPubdateBegin() + "','yyyy-mm-dd' )";
|
}
|
if (!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= to_date ( '" + selectShuJuKu.getPubdatefinish() + "' ,'yyyy-mm-dd')";
|
}
|
if (!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("")) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= to_date ( '" + selectShuJuKu.getPubdateBegin() + "','yyyy-mm-dd' )";
|
tiaojian += " and ";
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= to_date ( '" + selectShuJuKu.getPubdatefinish() + "' ,'yyyy-mm-dd')";
|
}
|
sql = "select " + sqlConfig + " from " + database.getTablename() + " where " + tiaojian;
|
}
|
}
|
//判断 是否是整数查询
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getIntNumBegin() != null || selectShuJuKu.getIntNumfinish() != null) {
|
String tiaojian = "";
|
if (selectShuJuKu.getIntNumBegin() != null && selectShuJuKu.getIntNumfinish() == null) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
|
}
|
if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() == null) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
|
}
|
if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() != null) {
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
|
tiaojian += " and ";
|
tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
|
}
|
sql = "select " + sqlConfig + " from " + database.getTablename() + " where " + tiaojian;
|
|
}
|
}
|
|
String fenye = "select * from(select * from(select t.*,row_number() over(order by " + sqlid + ") as rownumber from(" + sql + ") t) p where p.rownumber>" + pageData.getStartIndex() + ") where rownum<= " + pageBean.getLimit();
|
System.out.println(fenye);
|
leirong = JDBCUtils.OracleConnUtils(database.getServeraddress(), database.getDatabasename(), database.getUsername(), database.getPassword(), database.getPort(), fenye);
|
}
|
}
|
|
JSONArray obj = JSON.parseArray(leirong);
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
//循环获取 字典表
|
if (ZiDianMap != null) {
|
if (ZiDianMap.size() > 0) {
|
for (String s : ZiDianMap.keySet()) {
|
System.out.println("key : " + s + " value : " + ZiDianMap.get(s));
|
LinkedHashMap<String, String> MapTypeList = FieldUtils.getFieldListByKey(ZiDianMap.get(s));
|
for (int i = 0; i < ob.size(); i++) {
|
JSONObject job = ob.getJSONObject(i);
|
Map m = job;
|
//null值处理 alert ykm 2019/01/03
|
m.put(s, MapTypeList.get(m.get(s)==null?null : m.get(s).toString()));
|
ob.set(i, JSONUtil.parseFromMap(m));
|
}
|
}
|
}
|
}
|
//循环 sqlmap 当有关联数据库表的时候才循环
|
if (SqlMap != null) {
|
if (SqlMap.size() > 0) {
|
for (String s : SqlMap.keySet()) {
|
System.out.println("key : " + s + " value : " + SqlMap.get(s));
|
SqlMapJson sqlMapJson = JSONUtil.toBean(SqlMap.get(s).get(0).toString(), SqlMapJson.class);
|
String sql1 = "select " + sqlMapJson.getSQLkey() + " as onlykey ," + sqlMapJson.getSQLtext() + " as text from " + sqlMapJson.getSQLtable();
|
String sqlMapList = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
|
database.getUsername(), database.getPassword(), sql1);
|
JSONArray sqlArray = JSON.parseArray(sqlMapList);
|
LinkedHashMap<String, String> MapTypeList = new LinkedHashMap<String, String>();
|
String dataStr = sqlArray.getJSONObject(0).get("data").toString();
|
JSONArray jsonarray = JSON.parseArray(dataStr);
|
for (int i = 0; i < jsonarray.size(); i++) {
|
String onlykey = jsonarray.getJSONObject(i).get("onlykey").toString();
|
String text = jsonarray.getJSONObject(i).get("text").toString();
|
MapTypeList.put(onlykey, text);
|
}
|
for (int i = 0; i < ob.size(); i++) {
|
JSONObject job = ob.getJSONObject(i);
|
Map m = job;
|
//null值处理 alert ykm 2019/01/03
|
m.put(s, MapTypeList.get(m.get(s) == null ? null : m.get(s).toString()));
|
ob.set(i, JSONUtil.parseFromMap(m));
|
}
|
System.out.println(JSON.parseArray(sqlMapList));
|
}
|
}
|
}
|
StringBuilder rsb = new StringBuilder();
|
rsb.append("{'totalCount':'" + totalCount);
|
rsb.append("','topics':");
|
rsb.append(ob);
|
rsb.append("}");
|
return rsb.toString();
|
}
|
else {
|
StringBuilder rsb = new StringBuilder();
|
rsb.append("{'totalCount':'" + totalCount);
|
rsb.append("','topics':");
|
rsb.append("[]");
|
rsb.append("}");
|
return rsb.toString();
|
}
|
|
}
|
|
//导出excel
|
@ResponseBody
|
@RequestMapping("/ZiYuan/BaseLeftSourceShuJuExcel")
|
public String BaseLeftSourceShuJuExcel(HttpServletResponse response, SelectShuJuKu selectShuJuKu, Model model, PageBean pageBean) {
|
DataBaseLeftDataSource database = resExtDataBaseService.selectBaseLeftSource(selectShuJuKu.getResourceid());
|
JSONObject sqlObj = JSON.parseObject(database.getFieldconfig());
|
JSONArray jsarr = sqlObj.getJSONArray("sql");
|
String sqlConfig = "";
|
String sqlid = "";
|
for (int i = 0; i < jsarr.size(); i++) {
|
if (i > 0) {
|
sqlConfig += ",";
|
}
|
sqlConfig += ((JSONObject) jsarr.get(i)).get("ZiDuanName").toString();
|
sqlid = ((JSONObject) jsarr.get(0)).get("ZiDuanName").toString();
|
sqlConfig += " as \"";
|
sqlConfig += ((JSONObject) jsarr.get(i)).get("ZiDuanBieMing").toString();
|
sqlConfig += "\"";
|
}
|
System.out.println(sqlConfig);
|
|
//开始拼接sql,然后判断是哪个数据库
|
String sql = "select " + sqlConfig + " from " + database.getTablename();
|
//查询总数的sql
|
String count = "select count(*) as zongshu from " + database.getTablename();
|
String leirong = "[]";
|
Integer totalCount = 0;
|
|
if (database.getDatabasetype().equalsIgnoreCase("SQLServer")) {
|
//判断查询有没有
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
|
count = "select count(*) as zongshu from " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " = '" + selectShuJuKu.getSelcetZiDuanZhi() + "'";
|
}
|
}
|
String zongshu = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
|
database.getUsername(), database.getPassword(), count);
|
JSONArray obj = JSON.parseArray(zongshu);
|
if (obj.getJSONObject(0).get("success").toString().equals("true")) {
|
JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
Map maps = (Map) JSON.parse(ob.get(0).toString());
|
totalCount = Integer.parseInt(maps.get("zongshu").toString());
|
pageBean.setLimit(20);
|
PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), Integer.parseInt(maps.get("zongshu").toString()));
|
//拼接sql
|
String fenye = "select " + sqlConfig + " from " + database.getTablename();
|
|
//判断查询有没有
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
|
fenye = "select " + sqlConfig + " from " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " like '%" + selectShuJuKu.getSelcetZiDuanZhi() + "'%";
|
}
|
}
|
System.out.println(fenye);
|
|
leirong = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
|
database.getUsername(), database.getPassword(), fenye);
|
}
|
|
}
|
|
if (database.getDatabasetype().equalsIgnoreCase("Oracle")) {
|
//判断查询有没有
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
|
count = "select count(*) as zongshu from " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " = '" + selectShuJuKu.getSelcetZiDuanZhi() + "'";
|
}
|
}
|
|
String zongshu = JDBCUtils.OracleConnUtils(database.getServeraddress(), database.getDatabasename(),
|
database.getUsername(), database.getPassword(), database.getPort(), count);
|
JSONArray obj = JSON.parseArray(zongshu);
|
if (obj.getJSONObject(0).get("success").toString().equals("true")) {
|
JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
Map maps = (Map) JSON.parse(ob.get(0).toString());
|
totalCount = Integer.parseInt(maps.get("ZONGSHU").toString());
|
pageBean.setLimit(20);
|
PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), Integer.parseInt(maps.get("ZONGSHU").toString()));
|
//拼接sql ，分页查询
|
//判断查询有没有
|
if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
|
if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
|
sql = "select " + sqlConfig + " from " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " = '" + selectShuJuKu.getSelcetZiDuanZhi() + "'";
|
}
|
}
|
String fenye = sql;
|
|
System.out.println(fenye);
|
leirong = JDBCUtils.OracleConnUtils(database.getServeraddress(), database.getDatabasename(), database.getUsername(), database.getPassword(), database.getPort(), fenye);
|
}
|
}
|
|
JSONArray obj = JSON.parseArray(leirong);
|
if (obj.getJSONObject(0).get("success").toString().equals("true")) {
|
JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
|
StringBuilder rsb = new StringBuilder();
|
rsb.append("{'totalCount':'" + totalCount);
|
rsb.append("','topics':");
|
rsb.append(ob);
|
rsb.append("}");
|
Map maps = (Map) JSON.parse(ob.get(0).toString());
|
String[] list = new String[maps.keySet().size()];
|
for (int i = 0; i < maps.keySet().size(); i++) {
|
list[i] = (String) maps.keySet().toArray()[i];
|
}
|
try {
|
String Filename = Excel.ShuJuKugoing(response, list, rsb.toString(),
|
sysConfig.getUploadPath() + "excel\\");
|
String path2 = "/uploadPath/excel/";
|
String desFile = path2 + Filename;
|
System.out.println(desFile);
|
return desFile;
|
} catch (IOException e) {
|
e.printStackTrace();
|
return "异常：" + e.getMessage();
|
}
|
}
|
else {
|
return "失败：" + obj.getJSONObject(0).get("msg").toString();
|
}
|
}
|
|
|
//查询数据库 有哪些表
|
@ResponseBody
|
@RequestMapping("/ZiYuan/selectSqlTable")
|
public String selectSqlTable(Res_ExtDataSource res_extDataSource) {
|
//根据数据源id，查询到账号密码
|
Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
|
//开始拼接sql,然后判断是哪个数据库
|
String leirong = "0";
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
|
String sql = " select name as TABLENAME from sysobjects where xtype='u' ORDER BY name";
|
leirong = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), sql);
|
|
}
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
|
String sql = "SELECT table_name as tablename FROM user_tables ORDER BY tablename";
|
leirong = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
|
}
|
JSONArray obj = JSON.parseArray(leirong);
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
leirong = obj.getJSONObject(0).get("data").toString();
|
}
|
return leirong;
|
}
|
|
//查询数据库表主键
|
@ResponseBody
|
@RequestMapping("/ZiYuan/selectTableKey")
|
public String selectTableKey(Res_ExtDataSource res_extDataSource,String tablename,Integer resMainInfoId){
|
//根据数据源id，查询到账号密码
|
Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
|
String leirong = "0";
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
|
String sql = "select b.column_name as ZHUJIAN from " +
|
"information_schema.table_constraints a inner join " +
|
"information_schema.constraint_column_usage b on a.constraint_name = b.constraint_name " +
|
"where a.constraint_type = 'PRIMARY KEY' and a.table_name = '"+tablename+"'";
|
leirong = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), sql);
|
}
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
|
String sql = "select col.column_name as zhujian" +
|
" from user_constraints con, user_cons_columns col " +
|
" where con.constraint_name = col.constraint_name " +
|
" and con.constraint_type='P' " +
|
" and col.table_name = '"+tablename+"'";
|
leirong = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
|
}
|
if(!leirong.equals("0")) {
|
JSONArray obj = JSON.parseArray(leirong);
|
if(obj.getJSONObject(0).get("success").toString().equals("true")) {
|
leirong = obj.getJSONObject(0).get("data").toString();
|
}
|
}
|
return leirong;
|
}
|
|
@ResponseBody
|
@RequestMapping("/ZiYuan/selectTabFileds")
|
public List<String> selectTabFileds(Res_ExtDataSource res_extDataSource,Integer tabletype,String tablename){
|
|
List<String> slist = new ArrayList<String>();
|
|
Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
|
String sql = "SELECT D.column_name AS fieldName,D.data_type AS fieldType,B.is_nullable AS isNullAble FROM SYS.tables A INNER JOIN SYS.columns B ON B.object_id = A.object_id LEFT JOIN SYS.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id LEFT JOIN(SELECT table_name,column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS)D ON D.column_name = B.name and D.TABLE_NAME = A.name WHERE A.name='"+tablename+"'";
|
String json=JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
for(int i =0;i<jsonarr.size();i++){
|
FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
|
if(tabletype==1){
|
slist.add(fieldMapping.getFieldName());
|
}else{
|
if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") &&
|
!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
|
slist.add(fieldMapping.getFieldName());
|
}
|
}
|
}
|
}
|
}
|
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
|
String sql = "select distinct(t.column_name) as "+"\"fieldName\",t.data_type as "+"\"fieldType\",t.NULLABLE as "+"\"isNullAble\" from all_tab_cols t where t.table_name='"+tablename+"'";
|
String json=JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
for(int i =0;i<jsonarr.size();i++){
|
FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
|
if(tabletype==1){
|
slist.add(fieldMapping.getFieldName());
|
}else{
|
if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") &&
|
!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
|
slist.add(fieldMapping.getFieldName());
|
}
|
}
|
}
|
}
|
}
|
|
if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
|
String sql ="SELECT COLUMN_NAME AS fieldName,DATA_TYPE AS fieldType FROM information_schema.COLUMNS where table_name='"+tablename+"'";
|
String json=JDBCUtils.MysqlConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
|
JSONArray jsonArray = JSONObject.parseArray(json);
|
JSONArray jsonarr = null;
|
if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
|
jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
|
for(int i =0;i<jsonarr.size();i++){
|
FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
|
if(tabletype==1){
|
slist.add(fieldMapping.getFieldName());
|
}else{
|
if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") &&
|
!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
|
slist.add(fieldMapping.getFieldName());
|
}
|
}
|
}
|
}
|
}
|
return slist;
|
}
|
/**
|
* wq:查询表字段及类型
|
* @param res_extDataSource
|
* @param tablename
|
* @param resMainInfoId
|
* @return 时间格式的字段
|
*/
|
@ResponseBody
|
@RequestMapping("/ZiYuan/selectDataField")
|
public List<String> selectDateField(Res_ExtDataSource res_extDataSource,String tablename,Integer resMainInfoId){
|
//根据数据源id，查询到账号密码
|
Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
|
String tableinfo ="";
|
ArrayList<String> slist = new ArrayList<String>();
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
|
String timestamp_sql ="select syscolumns.name as ZiDuanName,systypes.name as ZiDuanType from syscolumns,systypes where syscolumns.xusertype=systypes.xusertype and syscolumns.id=OBJECT_ID('"+tablename+"')";
|
tableinfo = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), timestamp_sql);}
|
if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
|
String sql = "select t.column_name as "+"\"ZiDuanName\",t.data_type as "+"\"ZiDuanType\" from all_tab_cols t where t.table_name= '"+tablename+"'";
|
tableinfo = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
|
}
|
if(!tableinfo.equals("")){
|
JSONArray json = JSON.parseArray(tableinfo);
|
if(json.getJSONObject(0).get("success").toString().equals("true")){
|
String str = json.getJSONObject(0).get("data").toString();
|
JSONArray jsonArray = JSONArray.parseArray(str);
|
for(int i=0;i<jsonArray.size();i++){
|
ZiDuanPeiZi bean = JSONUtil.toBean(jsonArray.get(i).toString(),ZiDuanPeiZi.class);
|
if(bean.getZiDuanType().equalsIgnoreCase("date") || bean.getZiDuanType().equalsIgnoreCase("datetime") || bean.getZiDuanType().equalsIgnoreCase("timestamp")){
|
slist.add(bean.getZiDuanName());
|
}
|
}
|
}
|
}
|
return slist;
|
}
|
|
//数据源管理
|
@ResponseBody
|
@RequestMapping("/ZiYuan/DataType")
|
public String DataSource(Model model, String type){
|
HashMap<String, String> DatabaseTypeList = FieldUtils.getFieldListByKey(type);// 获取数据库类型列表
|
String ResourceTypeListJson = "";
|
for (Map.Entry<String, String> entry : DatabaseTypeList.entrySet()) {
|
if (ResourceTypeListJson != ""){
|
ResourceTypeListJson += ",";
|
}
|
ResourceTypeListJson += "{key:'" + entry.getKey() + "',value:'" + entry.getValue() + "'}";
|
}
|
return ResourceTypeListJson;
|
}
|
//根据sql查询-对应的属性
|
@ResponseBody
|
@RequestMapping("/ZiYuan/SQLSource")
|
public String SQLSource(Model model, SqlMapJson sqlMapJson,Integer resourceid){
|
DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(resourceid);
|
String sql1="select "+sqlMapJson.getSQLkey()+" as onlykey ,"+sqlMapJson.getSQLtext()+" as text from "+sqlMapJson.getSQLtable();
|
String sqlMapList=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),
|
database.getUsername(), database.getPassword(),sql1);
|
JSONArray sqlArray=JSON.parseArray(sqlMapList);
|
LinkedHashMap<String, String> MapTypeList =new LinkedHashMap<String,String>();
|
for(int i = 0; i < sqlArray.size(); i++){
|
MapTypeList.put(JSON.parseArray(sqlMapList).getJSONObject(i).get("onlykey").toString(),JSON.parseArray(sqlMapList).getJSONObject(i).get("text").toString());
|
}
|
|
String ResourceTypeListJson = "";
|
for (Map.Entry<String, String> entry : MapTypeList.entrySet()) {
|
if (ResourceTypeListJson != ""){
|
ResourceTypeListJson += ",";
|
}
|
ResourceTypeListJson += "{key:'" + entry.getKey() + "',value:'" + entry.getValue() + "'}";
|
}
|
return ResourceTypeListJson;
|
}
|
|
|
|
|
|
}
|
