package web.connectionManage;
|
|
import com.alibaba.druid.sql.SQLUtils;
|
import com.alibaba.druid.util.JdbcConstants;
|
import model.connectionManage.ConnectionManage;
|
import model.database.ColumnMetaData;
|
import model.database.JdbcProps;
|
import model.database.TableMetaData;
|
import org.apache.commons.lang3.StringUtils;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.ResponseBody;
|
import service.connectionManage.ConnectionManageService;
|
import service.system.helper.DataBaseMetadataHelper;
|
|
import javax.annotation.Resource;
|
import java.net.Socket;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* Created by yx on 16/8/11.
|
*/
|
@Controller
|
@RequestMapping("/connectionManage")
|
public class ConnectionManageController {
|
|
@Resource
|
private ConnectionManageService connectionManageService;
|
|
@Resource
|
private DataBaseMetadataHelper dataBaseMetadataHelper;
|
|
@RequestMapping("/add")
|
@ResponseBody
|
public Object add(ConnectionManage connectionManage){
|
return connectionManageService.add(connectionManage);
|
}
|
|
@RequestMapping("/delete")
|
@ResponseBody
|
public Object delete(ConnectionManage connectionManage){
|
return connectionManageService.delete(connectionManage);
|
}
|
|
@RequestMapping("/query")
|
@ResponseBody
|
public Object query(ConnectionManage connectionManage){
|
return connectionManageService.query(connectionManage);
|
}
|
|
@RequestMapping("/queryTree")
|
@ResponseBody
|
@SuppressWarnings("unchecked")
|
public Object queryTree(ConnectionManage connectionManage) throws Exception {
|
String lv = connectionManage.getLv();
|
List<ConnectionManage> treeNodes = new ArrayList();
|
if(StringUtils.isNotBlank(lv)){
|
String[] queryParams = connectionManage.getQueryParam().split(",");
|
connectionManage.setId(queryParams[0]);
|
connectionManage = connectionManageService.queryAsObject(connectionManage);
|
JdbcProps jdbcProps = new JdbcProps();
|
jdbcProps.setUrl(connectionManage.getDbUrl());
|
jdbcProps.setUsername(connectionManage.getUserName());
|
jdbcProps.setPassword(connectionManage.getPassword());
|
jdbcProps.setDbPort(connectionManage.getDbPort());
|
jdbcProps.setDbHost(connectionManage.getDbHost());
|
if("0".equals(lv)){
|
if(dataBaseMetadataHelper.isEffectiveDataSouce(jdbcProps)){
|
List<TableMetaData> tableMetaDatas = dataBaseMetadataHelper.getSchemaTables(jdbcProps);
|
for(TableMetaData tableMetaData : tableMetaDatas){
|
ConnectionManage treeNode = new ConnectionManage();
|
treeNode.setDbName(tableMetaData.getTableName());
|
treeNode.setId(tableMetaData.getTableName());
|
treeNode.setIsParent("true");
|
treeNode.setQueryParam(connectionManage.getQueryParam().split(",")[0]+ "," + tableMetaData.getTableName());
|
treeNode.setType("table");
|
treeNodes.add(treeNode);
|
}
|
}else{
|
throw new RuntimeException("数据库连接失败!");
|
}
|
}else if("1".equals(lv)){
|
if(dataBaseMetadataHelper.isEffectiveDataSouce(jdbcProps)){
|
List<ColumnMetaData> columnMetaDatas = dataBaseMetadataHelper.getSchemaTableColumns(jdbcProps,queryParams[1]);
|
for(ColumnMetaData columnMetaData : columnMetaDatas){
|
ConnectionManage treeNode = new ConnectionManage();
|
treeNode.setDbName(columnMetaData.getColumnName() + ":" + columnMetaData.getJdbcType());
|
treeNode.setId(columnMetaData.getColumnName());
|
treeNode.setIsParent("false");
|
treeNode.setType("field");
|
treeNodes.add(treeNode);
|
}
|
}else{
|
throw new RuntimeException("数据库连接失败!");
|
}
|
}
|
}else{
|
return connectionManageService.query(connectionManage);
|
}
|
return treeNodes;
|
}
|
|
@RequestMapping("/executeQuerySql")
|
@ResponseBody
|
public Object executeQuerySql(ConnectionManage connectionManage) throws Exception {
|
Map<String,Object> resultSet = new HashMap<>();
|
JdbcProps jdbcProps = new JdbcProps();
|
jdbcProps.setSql(connectionManage.getSql());
|
jdbcProps.setPage(connectionManage.getPage());
|
jdbcProps.setPageSize(connectionManage.getPageSize());
|
jdbcProps.setQueryMaxRows(connectionManage.getQueryMaxRows());
|
|
connectionManage = connectionManageService.queryAsObject(connectionManage);
|
jdbcProps.setUrl(connectionManage.getDbUrl());
|
jdbcProps.setUsername(connectionManage.getUserName());
|
jdbcProps.setPassword(connectionManage.getPassword());
|
|
resultSet.put("data",dataBaseMetadataHelper.executeQuerySql(jdbcProps));
|
resultSet.put("page",jdbcProps.getPage());
|
resultSet.put("total",jdbcProps.getTotalPage());
|
return resultSet;
|
}
|
|
@RequestMapping("/getQuerySqlInfo")
|
@ResponseBody
|
public Object getQuerySqlInfo(ConnectionManage connectionManage) throws Exception {
|
JdbcProps jdbcProps = new JdbcProps();
|
jdbcProps.setSql(connectionManage.getSql());
|
jdbcProps.setPage(connectionManage.getPage());
|
jdbcProps.setPageSize(connectionManage.getPageSize());
|
jdbcProps.setQueryMaxRows(connectionManage.getQueryMaxRows());
|
|
connectionManage = connectionManageService.queryAsObject(connectionManage);
|
jdbcProps.setUrl(connectionManage.getDbUrl());
|
jdbcProps.setUsername(connectionManage.getUserName());
|
jdbcProps.setPassword(connectionManage.getPassword());
|
return dataBaseMetadataHelper.getQuerySqlInfo(jdbcProps);
|
}
|
|
@RequestMapping(value = "/formatSql", produces={"text/html;charset=UTF-8;","application/json;"})
|
@ResponseBody
|
public static String formatSql(ConnectionManage connectionManage) throws Exception {
|
String dbType;
|
switch (connectionManage.getDbType()) {
|
case "MySql":
|
dbType = JdbcConstants.MYSQL;
|
break;
|
case "SqlServer":
|
dbType = JdbcConstants.SQL_SERVER;
|
break;
|
case "Oracle":
|
dbType = JdbcConstants.ORACLE;
|
break;
|
default:
|
dbType = JdbcConstants.MYSQL;
|
break;
|
}
|
return SQLUtils.format(connectionManage.getSql(),dbType);
|
}
|
}
|
