sqlexecuter.java

采用Eclispe开发平台

package com.whatratimes.dbms;

/**
 * Created by IntelliJ IDEA.
 * User: Tao
 * Date: May 8, 2003
 * Time: 10:13:23 PM
 */

import java.util.*;
import java.util.Date;
import java.sql.*;
import java.io.*;

public abstract class SQLExecuter
{
    private Class returnClass;
    private int rowsSkip = 0;
    private int rowsReturn = Integer.MAX_VALUE;

    public static final int MAX_CLOB_LENGTH = 1024 * 256;
    public static final int MAX_BLOB_LENGTH = 1024 * 1024 * 10;

    protected Connection dbConn;

    protected StringBuffer tablesClause;
    protected Vector tables = new Vector();


    protected Hashtable values = new Hashtable();

    protected Hashtable whereConditions = new Hashtable();
    protected StringBuffer whereClause = new StringBuffer();
    protected String whereClauseOperator = " AND ";

    protected SQLExecuter()
    {
        dbConn = DBHelper.getDBConnection();
    }


    protected void setRowLimit(int skip, int rows)
    {
        rowsSkip = skip;
        rowsReturn = rows;
    }

    public void setReturnClassName(String return_class_name)
    {
        try
        {
            returnClass = Class.forName(DBHelper.TABLE_PACKAGE_PREFIX + return_class_name);
            BeanHelper.keepFields(returnClass.newInstance());
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public void appendTableName(String table_name)
    {
        appendTableName(table_name, "");
    }

    public void appendTableName(String table_name, String table_alias)
    {
        tables.add(table_name);

        if (tablesClause == null)
        {
            tablesClause = new StringBuffer();
        } else
        {
            tablesClause.append(", ");
        }

        tablesClause.append(table_name);
        tablesClause.append(" ");
        tablesClause.append(table_alias);
        tablesClause.append(" ");

        BeanHelper.keepFields(table_name);
    }

    protected String getTableString()
    {
        return " FROM " + tablesClause;
    }


    protected void appendValue(String column_name, Object value)
    {
        values.put(column_name, value);

    }

    protected void appendValuesByBean(Object this_table)
    {
        Hashtable hash = BeanHelper.getValues(this_table);

        Enumeration enu = hash.keys();

        while (enu.hasMoreElements())
        {
            Object key = enu.nextElement();

            Object value = hash.get(key);

            appendValue(key.toString(), value);
        }
    }

    protected void appendValuesByHashtable(Hashtable properties)
    {
        Enumeration enu = properties.keys();

        while (enu.hasMoreElements())
        {
            Object key = enu.nextElement();

            Enumeration enu_tables = tables.elements();

            while (enu_tables.hasMoreElements())
            {
                if (BeanHelper.containsField(enu_tables.nextElement().toString(), key.toString()))
                {
                    Object value = properties.get(key);

                    appendValue(key.toString(), value);

                    break;
                }
            }

        }
    }

    protected void appendWhereCondition(String column_name, String operator, Object value)
    {
        whereConditions.put(column_name + " " + operator, value);

    }

    protected void appendWhereClause(String where_clause)
    {
        if (whereClause.length() > 0)
        {
            whereClause.append(whereClauseOperator);
        }

        whereClause.append(where_clause);
    }

    protected void setWhereConditionOR()
    {
        whereClauseOperator = " OR ";

    }

    protected String getWhereClause()
    {
        StringBuffer myWhereClause = new StringBuffer(whereClause.toString());

        if (!whereConditions.isEmpty())
        {
            String[] wheres = new String[whereConditions.size()];

            Enumeration enu = whereConditions.keys();

            int i = 0;

            while (enu.hasMoreElements())
            {
                String this_column = (String) enu.nextElement();
                wheres[i++] = this_column + " ? ";
            }

            if (myWhereClause.length() > 0)
            {
                myWhereClause.append(whereClauseOperator);
            }

            myWhereClause.append(wheres[0]);

            for (int j = 1; j < wheres.length; j++)
            {
                myWhereClause.append(whereClauseOperator + wheres[j]);
            }
        }

        if (myWhereClause.length() > 0)
        {
            myWhereClause.insert(0, " where ");
        }

        return myWhereClause.toString();


    }

    private void putParameters(PreparedStatement pst, Enumeration keys, Hashtable hash, int parameter_index) throws SQLException
    {

        while (keys.hasMoreElements())
        {
            String this_column = (String) keys.nextElement();

            Object obj = hash.get(this_column);

            if (obj instanceof Date)
            {
                obj = new Timestamp(((Date) obj).getTime());
                pst.setObject(++parameter_index, obj);
            } else if (obj instanceof Calendar)
            {
                obj = new Timestamp(((Calendar) obj).getTime().getTime());
                pst.setObject(++parameter_index, obj);
            } else if ( obj instanceof String )
            {
                pst.setObject(++parameter_index, obj);

            } else if (obj.getClass().isArray())
            {
                String ct = obj.getClass().getComponentType().getName();

                if (ct.equals("char"))
                {
                    int len = ((char[]) obj).length;
                    if (len > MAX_CLOB_LENGTH)
                    {
                        len = MAX_CLOB_LENGTH;
                    }
                    pst.setCharacterStream(++parameter_index, new CharArrayReader((char[]) obj), len);


                }

                if (ct.equals("byte"))
                {
                    int len = ((byte[]) obj).length;
                    if (len > MAX_BLOB_LENGTH)
                    {
                        len = MAX_BLOB_LENGTH;
                    }
                    pst.setBinaryStream(++parameter_index, new ByteArrayInputStream((byte[]) obj), len);

                }
            } else if (obj instanceof InputStream)
            {
                try
                {
                    pst.setBinaryStream(++parameter_index, (InputStream) obj, ((InputStream) obj).available());
                } catch (Exception e)
                {
                    e.printStackTrace();
                }

            } else
            {
                pst.setObject(++parameter_index, obj);
            }
        }

    }

    private PreparedStatement prepare() throws SQLException
    {

        PreparedStatement pst = null;

        System.out.println(getSQL());

        pst = dbConn.prepareStatement(getSQL());
        Enumeration keys;

        keys = values.keys();
        putParameters(pst, keys, values, 0);

        keys = whereConditions.keys();
        putParameters(pst, keys, whereConditions, values.size());

        return pst;
    }

    protected int executeUpdate()
    {
        PreparedStatement pst = null;

        try
        {
            synchronized (dbConn)
            {

                pst = prepare();
                return pst.executeUpdate();
            }
        } catch (Exception e)
        {
            System.err.println(getSQL());
            e.printStackTrace();
        } finally
        {
            if (pst != null)
            {
                try
                {
                    pst.close();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }

        return 0;
    }

    protected Object[] executeQuery()
    {
        PreparedStatement pst = null;
        ResultSet rs = null;

        LinkedList result = new LinkedList();
        String[] columnNames;

        try
        {
            synchronized (dbConn)
            {
                pst = prepare();

                rs = pst.executeQuery();

                ResultSetMetaData rsmd = rs.getMetaData();

                columnNames = new String[rsmd.getColumnCount()];

                for (int i = 0; i < columnNames.length; i++)
                {
                    columnNames[i] = rsmd.getColumnName(i + 1);
                }


                while (rs.next())
                {

                    if (rowsSkip-- > 0)
                    {
                        continue;
                    }

                    if (rowsReturn-- > 0)
                    {

                        Hashtable prop = new Hashtable();

                        for (int i = 0; i < columnNames.length; i++)
                        {
                            Object obj = rs.getObject(i + 1);

                            obj = convertSQLTypeToJava(obj);

                            if (obj != null)
                            {
                                prop.put(columnNames[i], obj);
                            }
                        }

                        result.addLast(prop);
                    }
                }
            }
        } catch (Exception e)
        {
            System.err.println(getSQL());
            e.printStackTrace();

            return new Object[0];

        } finally
        {
            if (rs != null)
            {
                try
                {
                    rs.close();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }

            if (pst != null)
            {
                try
                {
                    pst.close();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }

        if (result.size() == 0)
        {
            return new Object[0];
        }

        if (returnClass == null)
        {
            return (Hashtable[]) result.toArray(new Hashtable[result.size()]);
        }

        Object[] rt = new Object[result.size()];

        for (int i = 0; i < rt.length; i++)
        {
            rt[i] = buildBeanFromProperties((Hashtable) result.removeFirst());

        }

        return rt;
    }

    private Object convertSQLTypeToJava(Object obj)
    {
        if (obj instanceof java.sql.Timestamp)
        {
            return new Date(((java.sql.Timestamp) obj).getTime());
        }

        if (obj instanceof java.sql.Date)
        {
            return new Date(((java.sql.Date) obj).getTime());
        }

        if (obj instanceof java.sql.Time)
        {
            return new Date(((java.sql.Time) obj).getTime());
        }

        if (obj instanceof java.lang.String)
        {
            String str = obj.toString().trim();


            return str;
        }

        return obj;
    }

    private Object buildBeanFromProperties(Hashtable prop)
    {
        if (returnClass == null)
        {
            return prop;
        }

        Object obj;

        try
        {
            obj = returnClass.newInstance();

            Enumeration enu = prop.keys();

            while (enu.hasMoreElements())
            {
                String columnName = enu.nextElement().toString();
                Object value = prop.get(columnName);

                if (value == null)
                {
                    continue;
                }

                BeanHelper.setValue(obj, columnName, value);

            }

            return obj;

        } catch (Exception e)
        {
            e.printStackTrace();
        }

        return prop;
    }


    public abstract String getSQL();
}