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

    • com.tsc9526.monalisa.tools.datatable.DataTable Maven / Gradle / Ivy

    There is a newer version: 2.2.0
    Show newest version
    /*******************************************************************************************
 *	Copyright (c) 2016, zzg.zhou([email protected])
 * 
 *  Monalisa is free software: you can redistribute it and/or modify
 *	it under the terms of the GNU Lesser General Public License as published by
 *	the Free Software Foundation, either version 3 of the License, or
 *	(at your option) any later version.

 *	This program is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU Lesser General Public License for more details.

 *	You should have received a copy of the GNU Lesser General Public License
 *	along with this program.  If not, see .
 *******************************************************************************************/
package com.tsc9526.monalisa.tools.datatable;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.StringWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import com.google.gson.stream.JsonWriter;
import com.tsc9526.monalisa.orm.annotation.Column;
import com.tsc9526.monalisa.orm.datasource.DbProp;
import com.tsc9526.monalisa.tools.clazz.MelpClass;
import com.tsc9526.monalisa.tools.clazz.MelpClass.ClassHelper;
import com.tsc9526.monalisa.tools.clazz.MelpClass.FGS;
import com.tsc9526.monalisa.tools.clazz.MelpLib;
import com.tsc9526.monalisa.tools.json.MelpJson;
import com.tsc9526.monalisa.tools.misc.MelpException;
import com.tsc9526.monalisa.tools.string.MelpSQL;
import com.tsc9526.monalisa.tools.string.MelpString;

 /**
  *  
  * @author zzg.zhou([email protected])
  */
public class DataTable extends ArrayList { 
	private static final long serialVersionUID = 6839964505006290332L;
	
	public static DataTable fromCsv(InputStream csvInputStream) {
		return MelpLib.createCsv().fromCsv(csvInputStream, CsvOptions.createDefaultOptions());
	}
	 
	public static DataTable fromCsv(String csvString){
		return MelpLib.createCsv().fromCsv(csvString, CsvOptions.createDefaultOptions());
	}
	
	public static DataTable fromResultSet(ResultSet rs){
		DataTable table=new DataTable();
		List headers=new ArrayList();
		
		try{
			ResultSetMetaData rsmd=rs.getMetaData();
		 
			for(int i=1;i<=rsmd.getColumnCount();i++){
				String title = rsmd.getColumnLabel(i);
				if(MelpString.isEmpty(title)){
					title  = rsmd.getColumnName(i);
				}
				headers.add(new DataColumn(title,rsmd.getColumnType(i)));
			}
			table.setHeaders(headers);
			
			while (rs.next()) {
				DataMap row=new DataMap();
				for(int i=1;i<=rsmd.getColumnCount();i++){
					row.put(headers.get(i-1).getName(), rs.getObject(i));
				}
				table.add(row);
			}
		
			return table;
		}catch(SQLException e){
			return MelpException.throwRuntimeException(e);
		}
	}
	
	protected List headers=new ArrayList();
	 
	public DataTable() {		
	}
	
	public DataTable(Collection cs) {
		super(cs);
	}
	 
	public void saveCsv(OutputStream csvOutputStream){ 
		MelpLib.createCsv().writeToCsv(this, csvOutputStream,CsvOptions.createDefaultOptions());
	}
 
	/**
	 * 获取指定列数据
	 * 
	 * @param column 列名称
	 * @return 返回指定列名的整列数据
	 */
	public List getColumn(String column){
		List rs=new ArrayList();
		
		for(DataMap m:as(DataMap.class)){
			rs.add( m.get(column) );
		}
		
		return rs;
	}
	
	
	/**
	 * 
	 * @param columns  SELECT fields,        null or ""  means: *(all fields)
	 * @param where    WHERE statement,      null or ""  means: all records 
	 * @param orderBy  ORDER BY statement,   null or ""  means: no order by
	 * @param groupBy  GROUP By statement,   null or ""  means: no group by
	 * 
	 * @return the first record. null if no result.
	 */
	public DataMap selectOne(String columns,String where,String orderBy,String groupBy){
		DataTable rs=select(columns, where, orderBy, groupBy);
		if(rs.size()>0){
			return rs.get(0);
		}else{
			return null;
		}
	}
	
	/**
	 * 
	 * @param columns  SELECT fields,        null or ""  means: *(all fields)
	 * @param where    WHERE statement,      null or ""  means: all records 
	 * @param orderBy  ORDER BY statement,   null or ""  means: no order by
	 * @param groupBy  GROUP By statement,   null or ""  means: no group by
	 * 
	 * @return data
	 */
	public DataTable select(String columns,String where,String orderBy,String groupBy){
		String sql=getSQL(columns, where, orderBy, groupBy);
		
		return MelpLib.createCsv().queryTable(this, sql);
	}
	
	protected String getSQL(String columns,String where,String orderBy,String groupBy){
		if(columns==null || columns.trim().length()==0){
			columns="*";
		}
		
		String sql="SELECT "+columns+" FROM _THIS_TABLE";
		
		if(where!=null && where.trim().length()>0){
			if(MelpSQL.isStartByKeyWord(where,"WHERE")){
				sql+=" "+where;
			}else{
				sql+=" WHERE "+where;
			}
		}
		 
		if(groupBy!=null && groupBy.trim().length()>0){
			if(MelpSQL.isStartByKeyWord(groupBy,"GROUP")){
				sql+=" "+groupBy;
			}else{
				sql+=" GROUP BY "+groupBy;
			}
		}
		
		if(orderBy!=null && orderBy.trim().length()>0){
			if(MelpSQL.isStartByKeyWord(orderBy,"ORDER")){
				sql+=" "+orderBy;
			}else{
				sql+=" ORDER BY "+orderBy;
			}
		}
		
		return sql;
	}
	 
 
	/**
	 * Inner Join
	 * 
	 * @param rightTable 连接表     
	 * @param joinFieldNames  连接的字段名称
    
	 * 	1. 表连接字段，多个字段逗号分隔。
    
	 * 	2. 如无此参数时，则按2个表中相同的字段名进行连接
    
	 * 	3. 如左右表连接字段名不同时，需提供2个逗号分开的字段列表
    
	 * @return result
	 */
	public DataTable join(DataTable rightTable,String... joinFieldNames){
		return new DataTableJoin(this,rightTable,joinFieldNames).doInnerJoin();
	}
	
	
	/**
	 * Left Join
	 * 
	 * @param rightTable  连接表    
	 * @param joinFieldNames  连接的字段名称
    
	 * 	1. 表连接字段，多个字段逗号分隔。
    
	 * 	2. 如无此参数时，则按2个表中相同的字段名进行连接
    
	 * 	3. 如左右表连接字段名不同时，需提供2个逗号分开的字段列表
    
	 * @return result
	 */
	public DataTable joinLeft(DataTable rightTable,String... joinFieldNames){
		return new DataTableJoin(this,rightTable,joinFieldNames).doLeftJoin();
	}
	
	/**
	 * Right Join
	 * 
	 * @param rightTable   连接表   
	 * @param joinFieldNames  连接的字段名称
    
	 * 	1. 表连接字段，多个字段逗号分隔。
    
	 * 	2. 如无此参数时，则按2个表中相同的字段名进行连接
    
	 * 	3. 如左右表连接字段名不同时，需提供2个逗号分开的字段列表
    
	 * @return result
	 */
	public DataTable joinRight(DataTable rightTable,String... joinFieldNames){
		return new DataTableJoin(this,rightTable,joinFieldNames).doRightJoin();
	}
	
	/**
	 * Full Join
	 * 
	 * @param rightTable   连接表   
	 * @param joinFieldNames 连接的字段名称 
    
	 * 	1. 表连接字段，多个字段逗号分隔。
    
	 * 	2. 如无此参数时，则按2个表中相同的字段名进行连接
    
	 * 	3. 如左右表连接字段名不同时，需提供2个逗号分开的字段列表
    
	 * @return result
	 */
	public DataTable joinFull(DataTable rightTable,String... joinFieldNames){
		return new DataTableJoin(this,rightTable,joinFieldNames).doFullJoin();
	}
	 
	
	public Page getPage(int limit,int offset){
		DataTable rows=new DataTable();
		
		for(int i=offset;rows.size()(rows,size(),limit,offset);
	}
	
	@SuppressWarnings("unchecked")
	public  DataTable as(Class toClass){
		if(size()>0 && get(0)!=null && toClass.isAssignableFrom(get(0).getClass())){
			return (DataTable)this;
		}
		
		DataTable r=new DataTable();
		r.headers.addAll(headers);
		
		for(int i=0;i
	 * 
	 * 如果表数据为数组, 则列名默认为： c0,c1,c2 ... 
    
	 * 如果表数据为Map,则列名为key值
    
	 * 如果表数据为对象，则列名为字段名
    
	 * 如果表数据为原始类型的数据(int,bool 等），则列名为 ： c0
	 * 
	 * @return 列名
	 * 
	 * @see #setHeaders(List)
	 */
	public synchronized List getHeaders() {
		if(headers.size()==0 && this.size()>0){
			headers.addAll(createHeader());
		}
		return headers;
	}

	
	protected List createHeader(){
		List headers=new ArrayList();
		
 		Object v=this.get(0);
		if(v!=null){
			if(v instanceof Map){
				int index=0;
				for(Object key:((Map)v).keySet()){
				 	headers.add(new DataColumn(""+key).setIndex(index++));
				}
			}else{
				int index=0;
				if(v.getClass().isPrimitive() || v.getClass().getName().startsWith("java.")){
					headers.add(new DataColumn("c0").setIndex(index++));
				}else if(v.getClass().isArray()){
					Object[] xs=(Object[])v;
					for(int k=0;k setHeaders(List headers) {
		this.headers = headers;
		return this;
	}
	
	/**
	 * 指定列名
	 * 
	 * @param names 指定列名
	 * @return 表本身
	 * 
	 * @see #getHeaders()
	 */
	public DataTable setHeaders(String... names){
		headers.clear();
		
		if(names!=null) {
			for(String name:names){
				headers.add(new DataColumn(name));
			}
		}
		
		return this;
	}
	
	public DataMap toDataMap(String ... keyNames){
		DataMap m=new DataMap();
		
		String splitKey=DbProp.CFG_DATATABLE_KEY_SPLIT;
		for(int i=0;i)v).keySet()){
						for(String kn:keyNames){
							if( (""+key).equalsIgnoreCase(kn) ){
								mkey+=splitKey+((Map)v).get(key);
							}
						}
					}
				}else{
					if(v.getClass().isPrimitive() || v.getClass().getName().startsWith("java.")){
						mkey=splitKey+v.toString();
					}else if(v.getClass().isArray()){
						Object[] xs=(Object[])v;
						for(int k=0;k0){
				mkey=mkey.substring(splitKey.length());
			}else{
				mkey=null;
			}
			
			if(!m.containsKey(mkey)){
				m.put(mkey, v);
			}else{
				throw new RuntimeException("Key existed: "+mkey+", value： "+v);
			}
		}
		
		return m;
	}
	
	public String toJson(){
		StringWriter buffer=new StringWriter();
		JsonWriter w=new JsonWriter(buffer);
		w.setSerializeNulls(true);
		
		MelpJson.writeJson(w,this,true);
		 
		return buffer.toString();
	}
	
	/**
	 * @return format string table
	 */
	public String format(){
		DataTable ts = this.as(DataMap.class);
		
		ts.headers = createHeader();
		 
		int cols = ts.headers.size();
		  		
		List rs=new ArrayList();
		if(!headers.isEmpty()){
			String[] cs=new String[cols];
			for(int i=0;i0){
				sb.append("\r\n");
			}
			
			for(int i=0;i    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy