GZ/nwyj/.svn/pristine/8b/8bcb7859407f0613fc4e41e7816e3701ed9fa1b3.svn-base

package com.persistence.service.logic;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.StringReader;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.persistence.service.ResultSet.TypeUtil;
import com.persistence.service.assitant.DataObject;
import com.persistence.service.assitant.Field;
import com.persistence.service.exception.PersistenceException;
import com.persistence.service.exception.TypeUnsupportedException;
import com.sysmodel.Util;

/**
 * sysmodel com.sysmodel.persistence create time 2006-5-27
 * 
 * @author nbs
 */
public class DefaultLogicHelper extends LogicHelper{

	private Log	log	= LogFactory.getLog(DefaultLogicHelper.class);

	public String getDataBaseTime(Connection con) throws PersistenceException {
		return null;
	}

	public String insertChildData(DataObject object, Connection con, String parentid)
			throws PersistenceException {
		try {
			// 判断逻辑主键
			if (isExist(con, object.getTablename(), getLogicPrimaryKeys(object.getAttributes()))) {
				throw new PersistenceException("logic primary key repeat");
			}
			// 存储非lob数据
			List<Field> reLobFields = new ArrayList<Field>();
			List<Field> reOtherFields = new ArrayList<Field>();
			String sql = "insert into " + object.getTablename()
					+ generalChildInsert(object.getAttributes(), reLobFields, reOtherFields);
			PreparedStatement ps = con.prepareStatement(sql);
			Iterator<Field> it = reOtherFields.iterator();

			ps.setString(1, object.getObjectID());
			ps.setString(2, parentid);
			int i = 3;
			while (it.hasNext()) {
				Field field = it.next();
				TypeUtil.getDataType(field.getDataType()).set(ps, field.getFieldvalue(), i);
				i++;
			}
			int count = ps.executeUpdate();
			ps.close();
			if (count <= 0)
				throw new PersistenceException("PreparedStatement execute failture");
			// 存储lob数据
			if (reLobFields.size() > 0) {
				updateLobField(object.getTablename(), object.getObjectID(), reLobFields, con);
			}
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		}
		return object.getObjectID();
	}

	public String insertData(DataObject object, Connection con) throws PersistenceException {
		try {
			// 判断逻辑主键
			if (isExist(con, object.getTablename(), getLogicPrimaryKeys(object.getAttributes()))) {
				throw new PersistenceException("logic primary key repeat");
			}
			// 存储非lob数据
			List<Field> reLobFields = new ArrayList<Field>();
			List<Field> reOtherFields = new ArrayList<Field>();
			String sql = "insert into " + object.getTablename()
					+ generalInsert(object.getAttributes(), reLobFields, reOtherFields);
			PreparedStatement ps = con.prepareStatement(sql);
			Iterator<Field> it = reOtherFields.iterator();
			ps.setString(1, object.getObjectID());
			int i = 2;
			while (it.hasNext()) {
				Field field = it.next();
				TypeUtil.getDataType(field.getDataType()).set(ps, field.getFieldvalue(), i);
				i++;
			}
			int count = ps.executeUpdate();
			ps.close();
			if (count <= 0)
				throw new PersistenceException("PreparedStatement execute failture");
			// 存储lob数据
			if (reLobFields.size() > 0) {
				updateLobField(object.getTablename(), object.getObjectID(), reLobFields, con);
			}
			// 存储子表
			if (object.getChildobject().size() != 0) {
				List<DataObject> child = object.getChildobject();
				for (int j = 0; j < child.size(); j++) {
					DataObject tempChild = child.get(j);
					insertChildData(tempChild, con, object.getObjectID());
				}
			}
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		}
		return object.getObjectID();
	}

	public void readBlobToOutputStream(DataObject object, Connection con, OutputStream os)
			throws PersistenceException {
		Field fieldLob = object.getAttributes().get(0);
		String sql = "select " + fieldLob.getFieldname() + " from " + object.getTablename()
				+ " where fd_objectid='" + object.getObjectID() + "'";
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				readBlob(rs, fieldLob.getFieldname(), os);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		}
	}

	public void readClobToWriter(DataObject object, Connection con, Writer writer)
			throws PersistenceException {
		Field fieldLob = object.getAttributes().get(0);
		String sql = "select " + fieldLob.getFieldname() + " from " + object.getTablename()
				+ " where fd_objectid='" + object.getObjectID() + "'";
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				readClob(rs, fieldLob.getFieldname(), writer);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		}
	}

	public ArrayList<DataObject> searchAllData(DataObject object, Connection con,
			List<Field> condition) throws PersistenceException {

		ArrayList<DataObject> al = new ArrayList<DataObject>();
		StringBuffer sb = new StringBuffer();
		String sql = "select " + generalListAndOrderBy(object.getAttributes(), sb)
				+ ",fd_objectid from " + object.getTablename() + generalWhere(condition, sb);
		String order = sb.toString();
		if (!order.equals(""))
			sql = sql + " order by " + sb.deleteCharAt(sb.length() - 1).toString();

		log.info(sql);
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			ps = con.prepareStatement(sql);
			for (int i = 0; i < condition.size(); i++) {
				Field field = condition.get(i);
				TypeUtil.getDataType(field.getDataType()).set(ps, field.getFieldvalue(), i + 1);
			}

			rs = ps.executeQuery();
			List<Field> alField = object.getAttributes();
			while (rs.next()) {
				DataObject temobject = new DataObject();
				temobject.setClassid(object.getClassid());
				temobject.setTablename(object.getTablename());
				temobject.setObjectID(rs.getString("fd_objectid"));
				for (int i = 0; i < alField.size(); i++) {
					Field field = alField.get(i);
					Field tempField = new Field(field.getFieldname(), null);
					tempField.setDataType(field.getDataType());
					if (field.getDataType().equals(TypeUtil.BLOB)) {
						tempField.setFieldvalue(readBolb(rs, field.getFieldname()));
					} else if (field.getDataType().equals(TypeUtil.CLOB)) {
						tempField.setFieldvalue(readColb(rs, field.getFieldname()));
					} else {
						tempField.setFieldvalue(TypeUtil.getDataType(field.getDataType()).get(rs,
								field.getFieldname()));
					}
					temobject.addAttribute(tempField);
				}
				al.add(temobject);
			}
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return al;
	}

	@Override
	public ArrayList<DataObject> searchAllData(DataObject object, Connection con,
			String SQLcondition) throws PersistenceException {
		ArrayList<DataObject> al = new ArrayList<DataObject>();
		StringBuffer sb = new StringBuffer();
		String sql = "select " + generalListAndOrderBy(object.getAttributes(), sb)
				+ ",fd_objectid from " + object.getTablename();
		if (SQLcondition.length() > 0)
			sql += " " + SQLcondition;
		log.info(sql);

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			List<Field> alField = object.getAttributes();
			while (rs.next()) {
				DataObject temobject = new DataObject();
				temobject.setClassid(object.getClassid());
				temobject.setTablename(object.getTablename());
				temobject.setObjectID(rs.getString("fd_objectid"));
				for (int i = 0; i < alField.size(); i++) {
					Field field = alField.get(i);
					Field tempField = new Field(field.getFieldname(), null);
					tempField.setDataType(field.getDataType());
					if (field.getDataType().equals(TypeUtil.BLOB)) {
						tempField.setFieldvalue(readBolb(rs, field.getFieldname()));
					} else if (field.getDataType().equals(TypeUtil.CLOB)) {
						tempField.setFieldvalue(readColb(rs, field.getFieldname()));
					} else if (field.getDataType().equals(TypeUtil.DATE)) {
						tempField.setFieldvalue(TypeUtil.getDataType(field.getDataType()).get(rs,
								field.getFieldname()));
						Timestamp valueTemp = (Timestamp) tempField.getFieldvalue();
						tempField.setFieldvalue(Util.getDateTime(field.getScale(), valueTemp));
					} else {
						tempField.setFieldvalue(TypeUtil.getDataType(field.getDataType()).get(rs,
								field.getFieldname()));
					}
					temobject.addAttribute(tempField);
				}
				al.add(temobject);
			}
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return al;
	}

	public ArrayList<DataObject> searchAllData(DataObject object, Connection con,
			String SQLcondition, String dataBase, String firstPageNum, String lastPageNum)
			throws PersistenceException {
		ArrayList<DataObject> al = new ArrayList<DataObject>();
		StringBuffer sb = new StringBuffer();
		StringBuffer sql = new StringBuffer();// "select "+
												// generalListAndOrderBy(object.getAttributes(),
												// sb)+ ",fd_objectid from " +
												// object.getTablename();
		String sql_temp = generalListAndOrderBy(object.getAttributes(), sb);
		sql.append("select ");
		if (dataBase.equals("db2")) {
			sql.append(" rownumber() over(ORDER BY fd_objectid ) AS ROWNUM_ , ");
		} else if (dataBase.equals("sqlserver")) {
			sql.append(" ROW_NUMBER() over(ORDER BY fd_objectid)AS ROWNUM_ , ");
		}
		sql.append(sql_temp).append(",fd_objectid from ").append(object.getTablename());

		if (SQLcondition.length() > 0)
			sql.append(SQLcondition);

		StringBuffer pagingSql = new StringBuffer();
		if (dataBase.equals("oracle")) {
			pagingSql.append("SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (");
			pagingSql.append(sql);
			pagingSql.append(") row_ WHERE rownum <= ").append(lastPageNum)
					.append(") WHERE rownum_ > ").append(firstPageNum);
		} else if (dataBase.equals("db2")) {
			pagingSql.append("SELECT * FROM ( ");
			pagingSql.append(sql);
			pagingSql.append(")as a where ROWNUM_ BETWEEN  ").append(firstPageNum).append(" and ")
					.append(lastPageNum);
		} else if (dataBase.equals("sqlserver")) {
			pagingSql.append("SELECT * FROM (");
			pagingSql.append(sql);
			pagingSql.append(") as t where t.ROWNUM_ > " + firstPageNum + " and t.ROWNUM_ < "
					+ lastPageNum);
		}

		log.info(pagingSql);

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(pagingSql.toString());
			rs = ps.executeQuery();
			List<Field> alField = object.getAttributes();
			while (rs.next()) {
				DataObject temobject = new DataObject();
				temobject.setClassid(object.getClassid());
				temobject.setTablename(object.getTablename());
				temobject.setObjectID(rs.getString("fd_objectid"));
				for (int i = 0; i < alField.size(); i++) {
					Field field = alField.get(i);
					Field tempField = new Field(field.getFieldname(), null);
					tempField.setDataType(field.getDataType());
					if (field.getDataType().equals(TypeUtil.BLOB)) {
						tempField.setFieldvalue(readBolb(rs, field.getFieldname()));
					} else if (field.getDataType().equals(TypeUtil.CLOB)) {
						tempField.setFieldvalue(readColb(rs, field.getFieldname()));
					} else if (field.getDataType().equals(TypeUtil.DATE)) {
						tempField.setFieldvalue(TypeUtil.getDataType(field.getDataType()).get(rs,
								field.getFieldname()));
						Timestamp valueTemp = (Timestamp) tempField.getFieldvalue();
						tempField.setFieldvalue(Util.getDateTime(field.getScale(), valueTemp));
					} else {
						tempField.setFieldvalue(TypeUtil.getDataType(field.getDataType()).get(rs,
								field.getFieldname()));
					}
					temobject.addAttribute(tempField);
				}
				al.add(temobject);
			}
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return al;
	}

	public ArrayList<DataObject> searchChildData(DataObject object, Connection con, String parentid)
			throws PersistenceException {
		ArrayList<DataObject> al = new ArrayList<DataObject>();
		StringBuffer sb = new StringBuffer();
		String sql = "select " + generalListAndOrderBy(object.getAttributes(), sb)
				+ ",fd_objectid from " + object.getTablename() + " where fd_parentid='" + parentid
				+ "'";
		String order = sb.toString();
		if (!order.equals(""))
			sql = sql + " order by " + sb.deleteCharAt(sb.length() - 1).toString();

		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			List<Field> alField = object.getAttributes();
			while (rs.next()) {
				DataObject temobject = new DataObject();
				temobject.setClassid(object.getClassid());
				temobject.setTablename(object.getTablename());
				temobject.setObjectID(rs.getString("fd_objectid"));
				for (int i = 0; i < alField.size(); i++) {
					Field field = alField.get(i);
					Field tempField = new Field(field.getFieldname(), null);
					tempField.setDataType(field.getDataType());
					if (field.getDataType().equals(TypeUtil.BLOB)) {
						tempField.setFieldvalue(readBolb(rs, field.getFieldname()));
					} else if (field.getDataType().equals(TypeUtil.CLOB)) {
						tempField.setFieldvalue(readColb(rs, field.getFieldname()));
					} else {
						tempField.setFieldvalue(TypeUtil.getDataType(field.getDataType()).get(rs,
								field.getFieldname()));
					}
					temobject.addAttribute(tempField);
				}
				al.add(temobject);
			}
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		}
		return al;
	}

	public DataObject searchData(DataObject object, Connection con) throws PersistenceException {
		String sql = "select " + generalList(object.getAttributes()) + " from "
				+ object.getTablename() + " where " + object.getObjectIDName() + "='"
				+ object.getObjectID() + "'";
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				List<Field> alField = object.getAttributes();
				for (int i = 0; i < alField.size(); i++) {
					Field field = alField.get(i);
					if (field.getDataType().equals(TypeUtil.BLOB)) {
						field.setFieldvalue(readBolb(rs, field.getFieldname()));
					} else if (field.getDataType().equals(TypeUtil.CLOB)) {
						field.setFieldvalue(readColb(rs, field.getFieldname()));
					} else {
						field.setFieldvalue(TypeUtil.getDataType(field.getDataType()).get(rs,
								field.getFieldname()));
					}
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		}
		return object;
	}

	public void storeBlob(DataObject object, Connection con, InputStream is)
			throws PersistenceException {
		Field fieldLob = object.getAttributes().get(0);
		String updateLob = "update " + object.getTablename() + " set " + fieldLob.getFieldname()
				+ "= ? where fd_objectid ='" + object.getObjectID() + "'";
		storeBlob(updateLob, con, is);
	}

	public void storeClob(DataObject object, Connection con, Reader reader)
			throws PersistenceException {
		Field fieldLob = object.getAttributes().get(0);
		String updateLob = "update " + object.getTablename() + " set " + fieldLob.getFieldname()
				+ "= ? where fd_objectid ='" + object.getObjectID() + "'";
		// 从流里读到字符串里
		StringBuffer sb = new StringBuffer();
		char[] cbuffer = new char[1024];
		int nread = 0;
		try {
			while ((nread = reader.read(cbuffer)) != -1) {
				sb.append(cbuffer, 0, nread);
			}
			cbuffer = null;
			String clobString = sb.toString();
			Reader stringReader = new StringReader(clobString);
			storeClob(updateLob, con, stringReader, clobString.length());
			stringReader.close();
		} catch (IOException e) {
			log.error("read data error ", e);
			throw new PersistenceException(e.getMessage());
		}
	}

	public boolean updateData(DataObject object, Connection con) throws PersistenceException {
		// 更新主表
		if (!updateOneData(object, con))
			return false;
		// 更新子表
		if (object.getChildobject().size() != 0) {
			List<DataObject> child = object.getChildobject();
			// 更新子表中需要更新的数据
			for (int j = 0; j < child.size(); j++) {
				DataObject tempChild = child.get(j);
				if (!updateOneData(tempChild, con))
					return false;
			}
			// 判断属否子表有删除的记录并删除

		}
		return true;
	}

	public boolean updateOneData(DataObject object, Connection con) throws PersistenceException {
		int count = -1;
		try {
			// 判断逻辑主键
			if (isExist(con, object.getTablename(), getLogicPrimaryKeys(object.getAttributes()),
					object.getObjectID())) {
				throw new PersistenceException("logic primary key repeat");
			}
			// 存储非lob数据
			List<Field> reLobFields = new ArrayList<Field>();
			List<Field> reOtherFields = new ArrayList<Field>();
			String sql = "update " + object.getTablename()
					+ generalUpdate(object.getAttributes(), reLobFields, reOtherFields)
					+ " where fd_objectid='" + object.getObjectID() + "'";
			PreparedStatement ps = con.prepareStatement(sql);
			Iterator<Field> it = reOtherFields.iterator();
			int i = 1;
			while (it.hasNext()) {
				Field field = it.next();
				TypeUtil.getDataType(field.getDataType()).set(ps, field.getFieldvalue(), i);
				i++;
			}
			count = ps.executeUpdate();
			ps.close();
			if (count == 1) {
				// 存储lob数据
				if (reLobFields.size() > 0) {
					updateLobField(object.getTablename(), object.getObjectID(), reLobFields, con);
				}
			}
		} catch (TypeUnsupportedException e) {
			throw new PersistenceException(e.getMessage());
		} catch (SQLException e) {
			throw new PersistenceException(e.getMessage());
		}
		return count <= 0 ? false : true;
	}

	/**
	 * @param reLobFields
	 * @return
	 */
	private String generalList(List<Field> reFields) {
		StringBuffer sblist = new StringBuffer();
		for (int i = 0; i < reFields.size(); i++) {
			Field field = reFields.get(i);
			if (i == 0)
				sblist.append(field.getFieldname());
			else
				sblist.append("," + field.getFieldname());
		}
		return sblist.toString();
	}

	private String generalInsert(List<Field> allFields, List<Field> reLobFields,
			List<Field> reOtherFields) {
		StringBuffer sblist = new StringBuffer(" (fd_objectid");
		StringBuffer sbvalue = new StringBuffer("values(?");
		Iterator<Field> it = allFields.iterator();
		while (it.hasNext()) {
			Field field = it.next();
			if (field.getDataType().equals(TypeUtil.BLOB)) {
				reLobFields.add(field);
			} else if (field.getDataType().equals(TypeUtil.CLOB)) {
				reLobFields.add(field);
			} else {
				sbvalue.append(",?");
				reOtherFields.add(field);
				sblist.append(",");
				sblist.append(field.getFieldname());
			}
		}
		sblist.append(") ");
		sbvalue.append(")");
		return sblist.toString() + sbvalue.toString();
	}

	/**
	 * @param attributes
	 * @param reLobFields
	 * @param reOtherFields
	 * @return insert child sql
	 */
	private String generalChildInsert(ArrayList<Field> attributes, List<Field> reLobFields,
			List<Field> reOtherFields) {
		StringBuffer sblist = new StringBuffer(" (fd_objectid,fd_parentid");
		StringBuffer sbvalue = new StringBuffer("values(?,?");
		Iterator<Field> it = attributes.iterator();
		while (it.hasNext()) {
			Field field = it.next();
			if (field.getDataType().equals(TypeUtil.BLOB)) {
				reLobFields.add(field);
			} else if (field.getDataType().equals(TypeUtil.CLOB)) {
				reLobFields.add(field);
			} else {
				sbvalue.append(",?");
				reOtherFields.add(field);
				sblist.append(",");
				sblist.append(field.getFieldname());
			}
		}
		sblist.append(") ");
		sbvalue.append(")");
		return sblist.toString() + sbvalue.toString();
	}

	private String generalListAndOrderBy(List<Field> reFields, StringBuffer sb) {
		StringBuffer sblist = new StringBuffer();
		for (int i = 0; i < reFields.size(); i++) {
			Field field = reFields.get(i);
			if (i == 0)
				sblist.append(field.getFieldname());
			else
				sblist.append("," + field.getFieldname());
			if (field.getOrderType() == Field.ASCEND)
				sb.append(field.getFieldname() + " asc,");
			else if (field.getOrderType() == Field.DESCEND)
				sb.append(field.getFieldname() + " desc,");
		}
		return sblist.toString();
	}

	/**
	 * @param condition
	 * @param sb
	 * @return sql where
	 */
	private String generalWhere(List<Field> condition, StringBuffer sb) {
		if (condition.size() == 0)
			return "";
		StringBuffer sbWhere = new StringBuffer(" where ");
		Iterator<Field> it = condition.iterator();
		boolean flag = true;
		while (it.hasNext()) {
			Field field = it.next();
			String fieldOper = field.getOperatorType();
			if (flag) {
				sbWhere.append(field.getFieldname());
				sbWhere.append(fieldOper);
				sbWhere.append(" ? ");
				flag = false;
			} else {
				sbWhere.append(" and ");
				sbWhere.append(field.getFieldname());
				sbWhere.append(fieldOper);
				sbWhere.append(" ? ");
			}
			if (field.getOrderType() == Field.ASCEND)
				sb.append(field.getFieldname() + " asc,");
			else if (field.getOrderType() == Field.DESCEND)
				sb.append(field.getFieldname() + " desc,");
		}
		return sbWhere.toString();
	}

	/**
	 * @param attributes
	 * @param reLobFields
	 * @param reOtherFields
	 * @return update sql
	 */
	private String generalUpdate(ArrayList<Field> attributes, List<Field> reLobFields,
			List<Field> reOtherFields) {
		StringBuffer sb = new StringBuffer(" set ");
		Iterator<Field> it = attributes.iterator();
		while (it.hasNext()) {
			Field field = it.next();
			if (field.getDataType().equals(TypeUtil.BLOB)) {
				reLobFields.add(field);
			} else if (field.getDataType().equals(TypeUtil.CLOB)) {
				reLobFields.add(field);
			} else {
				sb.append(field.getFieldname());
				sb.append("= ? ,");
				reOtherFields.add(field);
			}
		}
		return sb.deleteCharAt(sb.length() - 1).toString();
	}

	/**
	 * @param tablename
	 * @param objectID
	 * @param reLobFields
	 * @param con
	 */
	private void updateLobField(String tablename, String objectID, List<Field> reLobFields,
			Connection con) throws PersistenceException {
		try {
			for (int i = 0; i < reLobFields.size(); i++) {
				Field field = reLobFields.get(i);
				if (field.getDataType().equals(TypeUtil.BLOB)) {
					if (field.getFieldvalue() != null) {
						String sql = "update " + tablename + " set " + field.getFieldname()
								+ "= ? where fd_objectid ='" + objectID + "'";
						byte[] content = (byte[]) field.getFieldvalue();
						InputStream is = new ByteArrayInputStream(content);
						storeBlob(sql, con, is);
						is.close();
					}
				} else {
					if (field.getFieldvalue() != null) {
						String sql = "update " + tablename + " set " + field.getFieldname()
								+ "= ? where fd_objectid ='" + objectID + "'";
						String content = (String) field.getFieldvalue();
						Reader reader = new StringReader(content);
						storeClob(sql, con, reader, content.length());
						reader.close();
					}
				}
			}
		} catch (IOException e) {
			log.error("close error", e);
			throw new PersistenceException(e.getMessage());
		}
	}

	private void storeBlob(String sql, Connection con, InputStream is) throws PersistenceException {
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setBinaryStream(1, is, is.available());
			int count = ps.executeUpdate();
			ps.close();
			if (count != 1)
				throw new PersistenceException("update blob falture");
		} catch (SQLException e) {
			log.error("updateLobField error ", e);
			throw new PersistenceException(e.getMessage());
		} catch (IOException e) {
			log.error("updateLobField error ", e);
			throw new PersistenceException(e.getMessage());
		}
	}

	private void storeClob(String sql, Connection con, Reader reader, int charLength)
			throws PersistenceException {
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setCharacterStream(1, reader, charLength);
			int count = ps.executeUpdate();
			ps.close();
			if (count != 1)
				throw new PersistenceException("update blob falture");
		} catch (SQLException e) {
			log.error("updateLobField error ", e);
			throw new PersistenceException(e.getMessage());
		}
	}

	private void readBlob(ResultSet rs, String fieldname, OutputStream os) throws SQLException {
		try {
			InputStream blobinputStream = rs.getBinaryStream(fieldname);
			if (blobinputStream != null) {
				byte[] buffer = new byte[1024];
				int nread = 0; // Number of bytes read
				while ((nread = blobinputStream.read(buffer)) != -1)
					os.write(buffer, 0, nread);
				os.flush();

			}
			blobinputStream.close();
		} catch (SQLException e) {
			log.error("get getBinaryStream error", e);
			throw e;
		} catch (IOException e) {
			log.error("write error", e);
			throw new SQLException(e.getMessage());
		}
	}

	private byte[] readBolb(ResultSet rs, String fieldname) throws SQLException {
		byte[] reContent = null;
		ByteArrayOutputStream os = null;
		try {
			os = new ByteArrayOutputStream();
			readBlob(rs, fieldname, os);
			reContent = os.toByteArray();
			os.close();
		} catch (IOException e) {
			// log.error("close error", e);
			throw new SQLException(e.getMessage());
		} finally {
			try {
				os.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return reContent;
	}

	private void readClob(ResultSet rs, String fieldname, Writer writer) throws SQLException {
		Reader clobReader = null;
		try {
			clobReader = rs.getCharacterStream(fieldname);
			if (clobReader != null) {
				char[] cbuffer = new char[1024];
				int nread = 0; // Number of bytes read

				while ((nread = clobReader.read(cbuffer)) != -1)
					writer.write(cbuffer, 0, nread);
				writer.flush();
				clobReader.close();
			}

		} catch (SQLException e) {
			// log.error("get getBinaryStream error", e);
			throw e;
		} catch (IOException e) {
			// log.error("write error", e);
			throw new SQLException(e.getMessage());
		} finally {
			try {
				clobReader.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private String readColb(ResultSet rs, String fieldname) throws SQLException {
		String reContent = null;
		try {
			StringWriter writer = new StringWriter();
			readClob(rs, fieldname, writer);
			reContent = writer.toString();
			writer.close();
		} catch (IOException e) {
			log.error("close error", e);
			throw new SQLException(e.getMessage());
		}
		return reContent;
	}

}