All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • com.bstek.ureport.console.designer.DatasourceServletAction Maven / Gradle / Ivy

    The newest version!
    /*******************************************************************************
 * Copyright 2017 Bstek
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License.  You may obtain a copy
 * of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.  See the
 * License for the specific language governing permissions and limitations under
 * the License.
 ******************************************************************************/
package com.bstek.ureport.console.designer;

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.codehaus.jackson.JsonParseException;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.ParsedSql;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.jdbc.support.JdbcUtils;

import com.bstek.ureport.Utils;
import com.bstek.ureport.build.Context;
import com.bstek.ureport.console.RenderPageServletAction;
import com.bstek.ureport.console.exception.ReportDesignException;
import com.bstek.ureport.definition.dataset.Field;
import com.bstek.ureport.definition.datasource.BuildinDatasource;
import com.bstek.ureport.definition.datasource.DataType;
import com.bstek.ureport.expression.ExpressionUtils;
import com.bstek.ureport.expression.model.Expression;
import com.bstek.ureport.expression.model.data.ExpressionData;
import com.bstek.ureport.expression.model.data.ObjectExpressionData;
import com.bstek.ureport.utils.ProcedureUtils;

/**
 * @author Jacky.gao
 * @since 2017年2月6日
 */
public class DatasourceServletAction extends RenderPageServletAction {

	@Override
	public void execute(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String method=retriveMethod(req);
		if(method!=null){
			invokeMethod(method, req, resp);
		}
	}
	
	public void loadBuildinDatasources(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		List datasources=new ArrayList();
		for(BuildinDatasource datasource:Utils.getBuildinDatasources()){
			datasources.add(datasource.name());
		}
		writeObjectToJson(resp, datasources);
	}
	
	public void loadMethods(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String beanId=req.getParameter("beanId");
		Object obj=applicationContext.getBean(beanId);
		Class clazz=obj.getClass();
		Method[] methods=clazz.getMethods();
		List result=new ArrayList();
		for(Method method:methods){
			Class[] types=method.getParameterTypes();
			if(types.length!=3){
				continue;
			}
			Class typeClass1=types[0];
			Class typeClass2=types[1];
			Class typeClass3=types[2];
			if(!String.class.isAssignableFrom(typeClass1)){
				continue;
			}
			if(!String.class.isAssignableFrom(typeClass2)){
				continue;
			}
			if(!Map.class.isAssignableFrom(typeClass3)){
				continue;
			}
			result.add(method.getName());
		}
		writeObjectToJson(resp, result);
	}
	
	public void buildClass(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String clazz=req.getParameter("clazz");
		List result=new ArrayList();
		try{
			Class targetClass=Class.forName(clazz);
			PropertyDescriptor[] propertyDescriptors=PropertyUtils.getPropertyDescriptors(targetClass);
			for(PropertyDescriptor pd:propertyDescriptors){
				String name=pd.getName();
				if("class".equals(name)){
					continue;
				}
				result.add(new Field(name));
			}
			writeObjectToJson(resp, result);
		}catch(Exception ex){
			throw new ReportDesignException(ex);
		}
	}
	
	public void buildDatabaseTables(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		Connection conn=null;
		ResultSet rs = null;
		try{
			conn=buildConnection(req);
			DatabaseMetaData metaData = conn.getMetaData();
			String url = metaData.getURL();
			String schema = null;
			if (url.toLowerCase().contains("oracle")) {
				schema = metaData.getUserName();
			}
			List> tables = new ArrayList>();
			rs = metaData.getTables(null, schema, "%", new String[] { "TABLE","VIEW" });
			while (rs.next()) {
				Map table = new HashMap();
				table.put("name",rs.getString("TABLE_NAME"));
				table.put("type",rs.getString("TABLE_TYPE"));
				tables.add(table);
			}
			writeObjectToJson(resp, tables);
		}catch(Exception ex){
			throw new ServletException(ex);
		}finally{
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeConnection(conn);
		}
	}
	
	public void buildFields(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String sql=req.getParameter("sql");
		String parameters=req.getParameter("parameters");
		Connection conn=null;
		final List fields=new ArrayList();
		try{
			conn=buildConnection(req);
			Map map = buildParameters(parameters);
			sql=parseSql(sql, map);
			if(ProcedureUtils.isProcedure(sql)){
				List fieldsList = ProcedureUtils.procedureColumnsQuery(sql, map, conn);
				fields.addAll(fieldsList);
			}else{
				DataSource dataSource=new SingleConnectionDataSource(conn,false);
				NamedParameterJdbcTemplate jdbc=new NamedParameterJdbcTemplate(dataSource);
				PreparedStatementCreator statementCreator=getPreparedStatementCreator(sql,new MapSqlParameterSource(map));
				jdbc.getJdbcOperations().execute(statementCreator, new PreparedStatementCallback() {
					@Override
					public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
						ResultSet rs = null;
						try {
							rs = ps.executeQuery();
							ResultSetMetaData metadata=rs.getMetaData();
							int columnCount=metadata.getColumnCount();
							for(int i=0;i declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
		PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
		return pscf.newPreparedStatementCreator(params);
	}

	public void previewData(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String sql=req.getParameter("sql");
		String parameters=req.getParameter("parameters");
		Map map = buildParameters(parameters);
		sql=parseSql(sql, map);
		Connection conn=null;
		try{
			conn=buildConnection(req);
			List> list=null;
			if(ProcedureUtils.isProcedure(sql)){
				list=ProcedureUtils.procedureQuery(sql, map, conn);
			}else{
				DataSource dataSource=new SingleConnectionDataSource(conn,false);
				NamedParameterJdbcTemplate jdbc=new NamedParameterJdbcTemplate(dataSource);
				list=jdbc.queryForList(sql, map);				
			}
			int size=list.size();
			int currentTotal=size;
			if(currentTotal>500){
				currentTotal=500;
			}
			List> ls=new ArrayList>();
			for(int i=0;i fields=new ArrayList();
			if(size>0){
				Map item=list.get(0);
				for(String name:item.keySet()){
					fields.add(name);
				}
			}
			result.setFields(fields);
			result.setCurrentTotal(currentTotal);
			result.setData(ls);
			result.setTotal(size);
			writeObjectToJson(resp, result);
		}catch(Exception ex){
			throw new ServletException(ex);
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	private String parseSql(String sql,Map parameters){
		sql=sql.trim();
		Context context=new Context(applicationContext, parameters);
		if(sql.startsWith(ExpressionUtils.EXPR_PREFIX) && sql.endsWith(ExpressionUtils.EXPR_SUFFIX)){
			sql=sql.substring(2, sql.length()-1);
			Expression expr=ExpressionUtils.parseExpression(sql);
			sql=executeSqlExpr(expr,context);
			return sql;
		}else{
			String sqlForUse=sql;
			Pattern pattern=Pattern.compile("\\$\\{.*?\\}");
			Matcher matcher=pattern.matcher(sqlForUse);
			while(matcher.find()){
				String substr=matcher.group();
				String sqlExpr=substr.substring(2,substr.length()-1);
				Expression expr=ExpressionUtils.parseExpression(sqlExpr);
				String result=executeSqlExpr(expr, context);
				sqlForUse=sqlForUse.replace(substr, result);
			}
			Utils.logToConsole("DESIGN SQL:"+sqlForUse);
			return sqlForUse;
		}
	}
	
	private String executeSqlExpr(Expression sqlExpr,Context context){
		String sqlForUse=null;
		ExpressionData exprData=sqlExpr.execute(null, null, context);
		if(exprData instanceof ObjectExpressionData){
			ObjectExpressionData data=(ObjectExpressionData)exprData;
			Object obj=data.getData();
			if(obj!=null){
				String s=obj.toString();
				s=s.replaceAll("\\\\", "");
				sqlForUse=s;
			}
		}
		return sqlForUse;
	}
	
	public void testConnection(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		String username=req.getParameter("username");
		String password=req.getParameter("password");
		String driver=req.getParameter("driver");
		String url=req.getParameter("url");
		Connection conn=null;
		Map map=new HashMap();
		try{
			Class.forName(driver);
			conn=DriverManager.getConnection(url, username, password);
			map.put("result", true);
		}catch(Exception ex){
			map.put("error", ex.toString());
			map.put("result", false);
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		writeObjectToJson(resp, map);
	}
	
	@SuppressWarnings("unchecked")
	private Map buildParameters(String parameters) throws IOException, JsonParseException, JsonMappingException {
		Map map=new HashMap();
		if(StringUtils.isBlank(parameters)){
			return map;
		}
		ObjectMapper mapper=new ObjectMapper();
		List> list=mapper.readValue(parameters, ArrayList.class);
		for(Map param:list){
			String name=param.get("name").toString();
			DataType type=DataType.valueOf(param.get("type").toString());
			String defaultValue=(String)param.get("defaultValue");
			if(defaultValue==null || defaultValue.equals("")){
				switch(type){
				case Boolean:
					map.put(name, false);
				case Date:
					map.put(name, new Date());
				case Float:
					map.put(name, new Float(0));
				case Integer:
					map.put(name, 0);
				case String:
					if(defaultValue!=null && defaultValue.equals("")){
						map.put(name, "");						
					}else{
						map.put(name, "null");						
					}
					break;
				case List:
					map.put(name, new ArrayList());
				}				
			}else{
				map.put(name, type.parse(defaultValue));			
			}
		}
		return map;
	}
	
	private Connection buildConnection(HttpServletRequest req) throws Exception{
		String type=req.getParameter("type");
		if(type.equals("jdbc")){			
			String username=req.getParameter("username");
			String password=req.getParameter("password");
			String driver=req.getParameter("driver");
			String url=req.getParameter("url");
			
			Class.forName(driver);
			Connection conn=DriverManager.getConnection(url, username, password);
			return conn;
		}else{
			String name=req.getParameter("name");
			Connection conn=Utils.getBuildinConnection(name);
			if(conn==null){
				throw new ReportDesignException("Buildin datasource ["+name+"] not exist.");
			}
			return conn;
		}
	}
	
	@Override
	public String url() {
		return "/datasource";
	}
}
    
    
    
    

    




    
    
    © 2015 - 2024 Weber Informatics LLC | Privacy Policy