dbutil.java

一个完整的代码管理系统的源代码

/*
 * Copyright (c) 2008-2010 Tanming1003 Inc.
 * All rights reserved.
 * 
 * tanming1003<[e-mail]>
 * 
 * 
 * 
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions 
 * are met:
 * 
 * Redistributions of source code must retain the above copyright 
 * notice, this list of conditions and the following disclaimer. 
 * Redistributions in binary form must reproduce the above copyright 
 * notice, this list of conditions and the following disclaimer in the 
 * documentation and/or other materials provided with the distribution. 
 * Neither the name of tanming1003 nor the names of its contributors 
 * may be used to endorse or promote products derived from this 
 * software without specific prior written permission. 
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 
 * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE 
 * REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 
 * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER 
 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, 
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING 
 * IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
 * POSSIBILITY OF SUCH DAMAGE.
 */
package hunnu.edu.cn.product.common.db;

import hunnu.edu.cn.product.common.db.model.Table;
import hunnu.edu.cn.product.common.db.sql.SqlGenerator;
import hunnu.edu.cn.product.common.db.sql.SqlGeneratorImplement;
import hunnu.edu.cn.product.common.upload.IO;
import hunnu.edu.cn.product.log.LogFactory;
import hunnu.edu.cn.product.log.LogLog;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletResponse;



import oracle.sql.BLOB;


/**
 * @author tanming1003
 * @date 2008-10-10
 * @time 下午01:57:37
 * @project_name Product
 * @package_name hunnu.edu.cn.product.common.db
 * @file_name    DBUtil.java
 * @version      1.0
 */

public class DBUtil
{
	private static  final LogLog log=LogFactory.newInstance().getLog(DBUtil.class);
	
	public static Properties getResourceProperties()
    {
		Properties prop=new Properties();
	    InputStream in=DBUtil.class.getClassLoader().getResourceAsStream("hunnu/edu/cn/product/resources/DBResources.properties");
	    try
		{
			prop.load(in);
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		return prop;
    }
	
	public static Properties getBlobColumnProperties()
    {
		Properties prop=new Properties();
	    InputStream in=DBUtil.class.getClassLoader().getResourceAsStream("hunnu/edu/cn/product/resources/BlobColumn.properties");
	    try
		{
			prop.load(in);
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		return prop;
    }
	
	public static Properties getPKColumnProperties()
    {
		Properties prop=new Properties();
	    InputStream in=DBUtil.class.getClassLoader().getResourceAsStream("hunnu/edu/cn/product/resources/PKColumn.properties");
	    try
		{
			prop.load(in);
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		return prop;
    }
	
	public static String getPKColumn(String storeTable)
	{
		Properties prop=getPKColumnProperties();
		return prop.getProperty(storeTable.toUpperCase()).toLowerCase();
	}
	
	public static String getBlobColumn(String storeTable)
	{
		Properties prop=getBlobColumnProperties();
		return prop.getProperty(storeTable.toUpperCase()).toLowerCase();
	}
	
	@SuppressWarnings("deprecation")
	public static boolean storeBlob(InputStream in,String storeTable,int threshold)
	{
		
		DBProcess process=new DBWrapper();
		Connection connection=null;
		String countSql="select count(*) from ( select * from "+storeTable+" )";
		try
		{
			int totalCount;
			connection=process.getConnection();
			PreparedStatement statement=connection.prepareStatement(countSql);
            ResultSet resultSet=statement.executeQuery();
            if(resultSet.next())
            	totalCount=resultSet.getInt(1);
            else
            	totalCount=0;
            totalCount++;
            String insertSql="insert into "+storeTable+" values(?,empty_blob())";
            statement=connection.prepareStatement(insertSql);
            connection.setAutoCommit(false);
            statement.setInt(1, totalCount);
            statement.executeUpdate();
            String querySql="select "+getBlobColumn(storeTable)+" from "+storeTable+" where "+getPKColumn(storeTable)+"="+totalCount+" for update";
            statement=connection.prepareStatement(querySql);
            resultSet=statement.executeQuery();
            if(resultSet.next())
            {
            	BLOB blob=(BLOB)resultSet.getBlob(getBlobColumn(storeTable));
				OutputStream out=blob.getBinaryOutputStream();
				if(threshold>blob.getBufferSize())
					threshold=blob.getBufferSize();
				IO.copyIO(in, out);
				connection.commit();
	            
            }
            return true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
		finally
		{
			try
			{
				if(connection!=null)
				   process.closeConnection(connection);
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	
	public static void downLoadBlob(int pk_id,String fileName,HttpServletResponse response)
	{
		String storeTable="TB_STORAGE";
		DBProcess process=new DBWrapper();
		Connection connection=null;
		try
		{
			connection=process.getConnection();
			connection.setAutoCommit(false);
			Statement statement=connection.createStatement();
			String sql="select "+getBlobColumn(storeTable)+" from "+storeTable+" where "+getPKColumn(storeTable)+"="+pk_id;
			ResultSet resultSet=statement.executeQuery(sql);
			if(resultSet.next())
			{
				BLOB blob=(BLOB)resultSet.getBlob(1);
				InputStream in=blob.getBinaryStream();
				response.setContentType("application/unknown");
			    response.addHeader("Content-Disposition", "attachment; filename="+fileName);
			    OutputStream out =response.getOutputStream();
                IO.copyIO(in, out);
                connection.commit();
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(connection!=null)
				   process.closeConnection(connection);
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	
	@SuppressWarnings("unchecked")
	public static Object get(Table table, Serializable id[]) 
	{
		DBProcess processor=new DBWrapper();
	    SqlGenerator generator=new SqlGeneratorImplement();
	    String sql=generator.getSql(table);
	    Object[] param=id;
	    Class tableClass=table.getClass();
	    System.out.println(sql);
	    try
		{
	    	Table result=(Table)tableClass.newInstance();
	    	List list=(ArrayList)processor.queryRecord(sql,param);
			if(list!=null)
			{	
				Map column=(HashMap)list.get(0);
			    Set set=column.entrySet();
			    Iterator it=set.iterator();
			    while(it.hasNext())
			    {
			    	Map.Entry entry=(Map.Entry)it.next();
			    	String keyValue=(String)entry.getKey();
			    	Object value=entry.getValue();
			    	Field field=tableClass.getDeclaredField(keyValue.toLowerCase());
			    	field.setAccessible(true);
			        field.set(result, value);
			    }
			}   
			return result;
		}
		catch (Exception e)
		{
			log.debug("The Exception is:", e);
		}
	    
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static boolean remove(Table table)
	{
		JdbcContextFactory factory=JdbcContextFactory.newInstance();
		Context context=factory.newJdbcContext();
		SqlGenerator generator=new SqlGeneratorImplement();
		String sql=generator.removeSql(table);
		List keys=(ArrayList)getKeys(table);
		Object[] param=keys.toArray();
		try
		{
			return context.deleteRecord(sql,param);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return false;
	}
	
	public static boolean remove(Table[] records)
	{
		for(int i=0;i<records.length;i++)
		{
			if(!remove(records[i]))
				return false;
		}
		return true;
	}
	
	public static boolean removeAll(Table table)
	{
		String tableName=table.getSimpleName();
		String sql="delete * from "+tableName;
		DBProcess process=new DBWrapper();
		try
		{
			process.deleteRecord(sql);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return true;
	}
	
	@SuppressWarnings("unchecked")
	public static boolean add(Table table)
	{
		JdbcContextFactory factory=JdbcContextFactory.newInstance();
		Context context=factory.newJdbcContext();
		SqlGenerator generator=new SqlGeneratorImplement();
		String sql=generator.saveSql(table);
		System.out.println(sql);
		List properties=(ArrayList)getProperties(table);
		Object[] param=properties.toArray();
		try
		{
			return context.insertRecord(sql,param);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return false;
	}
    
	@SuppressWarnings("unchecked")
	public static List<?> getUpdateProperties(Table table)
	{
		List properties=new ArrayList();
		List names=getPropertyNames(table);
		for(int i=0;i<names.size();i++)
		{
			String fieldName=(String)names.get(i);
			if(isKey(table,fieldName))
				continue;
			try
			{
				Field field=table.getClass().getDeclaredField(fieldName);
				field.setAccessible(true);
				properties.add(field.get(table));
			}
			catch (SecurityException e)
			{
				e.printStackTrace();
			}
			catch (NoSuchFieldException e)
			{
				e.printStackTrace();
			}
			catch (IllegalArgumentException e)
			{
				e.printStackTrace();
			}
			catch (IllegalAccessException e)
			{
				e.printStackTrace();
			}
		}
		List keys=getKeys(table);
		properties.addAll(keys);
		return properties;
	}
	
	public static Method getMethod(Class<?> tableClass,String fieldName,String prefix)
	{
		Method[] methods=tableClass.getMethods();
		for(int i=0;i<methods.length;i++)
		{
			if(methods[i].getName().equalsIgnoreCase(prefix+fieldName))
				return methods[i];
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static List getPropertyNames(Table table)
	{
		List names=new ArrayList();
		Class c=table.getClass();
		Field[] fields=c.getDeclaredFields();
		for(int i=0;i<fields.length;i++)
		{
			names.add(fields[i].getName());
		}
		return names;
	}
    
	@SuppressWarnings("unchecked")
	public static boolean isKey(Table table,String fieldName)
	{
		List<Map<String,Object>> list=(List<Map<String,Object>>)table.getKeys();
		for(int j=0;j<list.size();j++)
		{
		    Map<String,Object> map=(Map<String,Object>)list.get(j);
		    Set<String> keys=map.keySet();
		    Iterator<String> it=keys.iterator();
		    if(it.hasNext())
		    {
		    	String keyName=it.next();
		    	if(keyName.equals(fieldName))
		    		return true;
		    }