/***************************************************
 * Copyright 2010-5-24, -sun,All rights reserved.
 * Create date : 2010-5-24
 * 
 * Author : sun
 * JDBCHelper JDBCHelper = new JDBCHelper()测试方法
 * 
 * JDBCHelper：是为了解决业务操作存在多条，需要在一个事务中处理的情况
 **************************************************/
package test.junitTest.sysmodel;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import junit.framework.TestCase;

import org.apache.log4j.Logger;

import test.junitTest.JunitInit;

import com.persistence.service.JDBCHelper;
import com.persistence.service.assitant.DataObject;
import com.persistence.service.assitant.Field;
import com.persistence.service.exception.PersistenceException;
import com.persistence.service.exception.TransformTypeException;

/**
 * The Class TestJDBCHelper.
 */
public class TestJDBCHelper extends TestCase{

	private final static Logger	log		= Logger.getLogger(TestJDBCHelper.class);
	@SuppressWarnings("unused")
	private JunitInit			unit	= null;

	protected void setUp() throws Exception {
		unit = new JunitInit();
		super.setUp();
	}

	protected void tearDown() throws Exception {
		super.tearDown();
	}

	public void testGetConnection() {
		Connection conn = null;
		try {
			JDBCHelper JDBCHelper = new JDBCHelper(); // 默认调用 classid=99
														// 指向的数据库链接
			// JDBCHelper JDBCHelper = new JDBCHelper(880);//调用 classid=880
			// 指向的数据库链接
			// 创建数据库链接
			JDBCHelper.begin();
			conn = JDBCHelper.getConnection();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void testAddObjectData() {

		JDBCHelper JDBCHelper = new JDBCHelper();
		try {
			// 创建数据库链接
			JDBCHelper.begin();

			DataObject dataObject = new DataObject();
			dataObject.setClassid(801);

			Field attribute = new Field();
			attribute.setFieldname("User_ID");
			attribute.setFieldvalue("User_ID");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Option_Time");
			attribute.setFieldvalue("2010-05-10 11:01:12");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Description");
			attribute.setFieldvalue("Description");
			dataObject.addAttribute(attribute);

			String fdk = JDBCHelper.addObjectData(dataObject);

			log.info("插入数据库中的记录 对应 fd_objectid ::" + fdk);
			super.assertTrue(fdk.length() >= 30);

			// 忘了执行这个数据不会提交到数据库
			JDBCHelper.commit();

		} catch (TransformTypeException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		} catch (PersistenceException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unused")
	public void testUpdateObjectData() {
		JDBCHelper JDBCHelper = new JDBCHelper();
		try {
			// 创建数据库链接
			JDBCHelper.begin();

			DataObject dataObject = new DataObject();
			dataObject.setClassid(801);
			Field attribute = new Field();
			attribute.setFieldname("User_ID");
			attribute.setFieldvalue("User_ID");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Option_Time");
			attribute.setFieldvalue("2010-05-10 11:01:12");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Description");
			attribute.setFieldvalue("Description");
			dataObject.addAttribute(attribute);

			String objectID = JDBCHelper.addObjectData(dataObject);
			log.info("插入数据库中的记录 对应 fd_objectid ::" + objectID);
			super.assertTrue(objectID.length() >= 30);

			DataObject dataobject = JDBCHelper.searchObjectData(objectID, 801);
			// 获得对象中指定属性的值
			String User_ID = (String) dataobject.getValue("USER_ID");// 传入参数不区分大小写，单必须在DataModel.xml中配置
			JDBCHelper.updateObjectData(dataobject);

			// 忘了执行这个数据不会提交到数据库
			JDBCHelper.commit();

		} catch (TransformTypeException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		} catch (PersistenceException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		}

	}

	public void testDeleteDataStringIntBoolean() {
		JDBCHelper JDBCHelper = new JDBCHelper();
		try {
			// 创建数据库链接
			JDBCHelper.begin();

			DataObject dataObject = new DataObject();
			dataObject.setClassid(801);
			Field attribute = new Field();
			attribute.setFieldname("User_ID");
			attribute.setFieldvalue("User_ID");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Option_Time");
			attribute.setFieldvalue("2010-05-10 11:01:12");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Description");
			attribute.setFieldvalue("Description");
			dataObject.addAttribute(attribute);

			String objectID = JDBCHelper.addObjectData(dataObject);
			log.info("插入数据库中的记录 对应 fd_objectid ::" + objectID);
			super.assertTrue(objectID.length() >= 30);

			// 假删除：调用这个方法时，数据库中必须有列字段is_Del
			// super.assertTrue(JDBCHelper.deleteData(objectID, 801, false));

			// 真删除
			super.assertTrue(JDBCHelper.deleteData(objectID, 801, true));

			// 忘了执行这个数据不会提交到数据库
			JDBCHelper.commit();

		} catch (TransformTypeException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		} catch (PersistenceException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		}

	}

	public void testDeleteDataStringInt() {
		JDBCHelper JDBCHelper = new JDBCHelper();
		try {
			// 创建数据库链接
			JDBCHelper.begin();

			DataObject dataObject = new DataObject();
			dataObject.setClassid(801);
			Field attribute = new Field();
			attribute.setFieldname("User_ID");
			attribute.setFieldvalue("User_ID");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Option_Time");
			attribute.setFieldvalue("2010-05-10 11:01:12");
			dataObject.addAttribute(attribute);

			attribute = new Field();
			attribute.setFieldname("Description");
			attribute.setFieldvalue("Description");
			dataObject.addAttribute(attribute);

			String objectID = JDBCHelper.addObjectData(dataObject);
			log.info("插入数据库中的记录 对应 fd_objectid ::" + objectID);
			super.assertTrue(objectID.length() >= 30);

			// 假删除：调用这个方法时，数据库中必须有列字段is_Del
			super.assertTrue(JDBCHelper.deleteData(objectID, 801));

			// 忘了执行这个数据不会提交到数据库
			JDBCHelper.commit();

		} catch (TransformTypeException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		} catch (PersistenceException e) {
			JDBCHelper.rollback();
			e.printStackTrace();
		}
	}

	/**
	 * 返回执行sql语句结果
	 * 
	 * @param sql
	 *            sql语句
	 * @return 执行结果 if >0 success -1 fail public int executeUpdateSQL(String
	 *         sql) throws PersistenceException
	 */
	public void testExecuteUpdateSQLString() {
		JDBCHelper JDBCHelper = new JDBCHelper();
		try {
			// 事务操作开始
			JDBCHelper.begin();

			int insert = JDBCHelper
					.executeUpdateSQL("INSERT INTO sys_log (FD_OBJECTID, User_ID, Display_Name,"
							+ " Dept_Id, Option_Time, Action, Description, IP, URL) "
							+ "VALUES ('-1','a','','','2010-05-10 11:01:12','5','登录时,用户名不存在',"
							+ "'127.0.0.1','http://localhost:8080/nbgl/ws/sys/LoginService/Login')");
			super.assertEquals(1, insert);

			int idel = JDBCHelper.executeUpdateSQL("delete from  sys_log where FD_OBJECTID = '-1'");
			super.assertEquals(1, idel);

			// 事务操作提交
			JDBCHelper.commit();
		} catch (PersistenceException e) {
			log.error("数据操作失败");
			e.printStackTrace();
			JDBCHelper.rollback();// 捕获异常事务回滚
		}
	}

	/**
	 * 返回执行多条sql语句结果
	 * 
	 * @param sql
	 *            sql集合
	 * @return 执行结果
	 * @throws PersistenceException
	 *             public int[] executeUpdateSQL(ArrayList<String> sql) throws
	 *             PersistenceException
	 */
	public void testExecuteUpdateSQLArrayListOfString() {
		JDBCHelper JDBCHelper = new JDBCHelper();
		try {
			// 事务操作开始
			JDBCHelper.begin();

			ArrayList<String> sql = new ArrayList<String>();
			sql.add("INSERT INTO sys_log (FD_OBJECTID, User_ID, Display_Name, Dept_Id, Option_Time, Action, Description, IP, URL) VALUES ('-1','a','','','2010-05-10 11:01:12','5','登录时,用户名不存在','127.0.0.1','http://localhost:8080/nbgl/ws/sys/LoginService/Login')");
			sql.add("INSERT INTO sys_log (FD_OBJECTID, User_ID, Display_Name, Dept_Id, Option_Time, Action, Description, IP, URL) VALUES ('-2','a','','','2010-05-10 11:01:12','5','登录时,用户名不存在','127.0.0.1','http://localhost:8080/nbgl/ws/sys/LoginService/Login')");
			sql.add("INSERT INTO sys_log (FD_OBJECTID, User_ID, Display_Name, Dept_Id, Option_Time, Action, Description, IP, URL) VALUES ('-3','a','','','2010-05-10 11:01:12','5','登录时,用户名不存在','127.0.0.1','http://localhost:8080/nbgl/ws/sys/LoginService/Login')");
			int[] insert = JDBCHelper.executeUpdateSQL(sql);

			super.assertTrue(insert.length == 3);
			super.assertEquals(1, insert[0]);
			super.assertEquals(1, insert[1]);
			super.assertEquals(1, insert[2]);

			sql.clear();
			sql.add("delete from  sys_log where FD_OBJECTID in ('-1','-3')");
			sql.add("delete from  sys_log where FD_OBJECTID = '-2'");
			int[] idel = JDBCHelper.executeUpdateSQL(sql);
			super.assertTrue(idel.length == 2);
			super.assertEquals(2, idel[0]);
			super.assertEquals(1, idel[1]);

			// 事务操作提交
			JDBCHelper.commit();

		} catch (PersistenceException e) {
			log.error("数据操作失败");
			e.printStackTrace();
			JDBCHelper.rollback();// 捕获异常事务回滚

		}

	}

	/*
	 * public void testStoreBlob() { fail("Not yet implemented"); }
	 * 
	 * public void testStoreClob() { fail("Not yet implemented"); }
	 */

	// ---提供的查询方法------------------------------------------------------
	/**
	 * 查询返回数据对象<DataObject>,不包括lob数据
	 * 
	 * @param objectID
	 *            数据库表记录唯一主键 fd_objectid
	 * @param classid
	 *            数据库表记录唯一标示，对应DataModel.xml配置
	 * 
	 *            public DataObject searchObjectData(String objectID, int
	 *            classid)
	 */
	@SuppressWarnings("unused")
	public void testSearchObjectData() throws PersistenceException {

		JDBCHelper JDBCHelper = new JDBCHelper(); // 默认调用 classid=99 指向的数据库链接
		// JDBCHelper JDBCHelper = new JDBCHelper(880);//调用 classid=880 指向的数据库链接
		// 创建数据库链接
		JDBCHelper.begin();

		// 查找DataModel.xml 中一个classid，到数据库中查找一个FD_OBJECTID
		DataObject DataObject = JDBCHelper.searchObjectData("880121076688331200000039110112", 880);

		// 打印获得的对象
		DataObject.PrintDataObject();

		// 获得对象中指定属性的值
		String User_ID = (String) DataObject.getValue("USER_ID");// 传入参数不区分大小写，单必须在DataModel.xml中配置

		super.assertEquals("880121076688331200000039110112", DataObject.getObjectID());
		// 关闭数据库链接
		JDBCHelper.release();
	}

	/**
	 * 根据条件查询数据，只查询该表数据
	 * 
	 * @param SQLcondition
	 *            where 查询条件
	 * @return 返回数据集合,每个对象为DataObject
	 * 
	 *         public ArrayList<DataObject> searchAllData(int classid, String
	 *         SQLcondition)
	 */
	@SuppressWarnings("unused")
	public void testSearchAllData() throws TransformTypeException, PersistenceException {
		JDBCHelper JDBCHelper = new JDBCHelper();
		JDBCHelper.begin();
		ArrayList<DataObject> objlis = JDBCHelper.searchAllData(880, " where type='2'");

		// 注意循环中不要调用objlis.size() 这样可以提高性能
		int objlislen = objlis.size();
		for (int i = 0; i < objlislen; i++) {
			DataObject Obj = objlis.get(i);

			// 获得对象中指定属性的值
			String User_ID = (String) Obj.getValue("USER_ID");// 传入参数不区分大小写，单必须在DataModel.xml中配置
		}

		super.assertTrue(objlis.size() > 0);
		JDBCHelper.release();
	}

	/**
	 * 返回数值运算函数结果 public int getFunctionNumber(int classid, String sql)
	 */
	public void testGetFunctionNumber() throws TransformTypeException, PersistenceException {
		JDBCHelper JDBCHelper = new JDBCHelper();
		JDBCHelper.begin();
		super.assertTrue(JDBCHelper.getFunctionNumber(880, "select count(*) from SysUser_Info") > 0);
		JDBCHelper.release();
	}

	/**
	 * 结果集中每个对象是字符串数组,内容是sql语句的查询字段 public ArrayList<String[]>
	 * getSearchResult(int classid, String sql) throws PersistenceException
	 */
	public void testGetSearchResult() {
		JDBCHelper JDBCHelper = new JDBCHelper();
		try {
			JDBCHelper.begin();
			ArrayList<String[]> lst = JDBCHelper.getSearchResult(880,
					"select fd_objectid,Name from SysUser_Info");
			int lstlen = lst.size();
			// 返回对象取值方法
			for (int i = 0; i < lstlen; i++) {
				String[] temp = lst.get(i);
				log.info("fd_objectid=" + temp[0] + "  Name=" + temp[1]);
			}
			super.assertTrue(lst.size() > 0);
		} catch (PersistenceException e) {
			e.printStackTrace();
		} finally {
			JDBCHelper.release();
		}
	}

	/*
	 * <mdpClass classid="801" description="日志信息表" name="Sys_Log" oldName=""
	 * type="Sys" catche="false" exist="true" dataSource="1" validate="true">
	 * <mdpAttribute description="操作者登录名称" name="User_ID" oldName="" unit=""
	 * dataType="string" precision="24" scale="" referenceType="0"
	 * defaultValue="" logicPrimaryKey="false" notNull="false" validate="true"
	 * index="false" indexType=""/> <mdpAttribute description="操作者名称"
	 * name="Display_Name" oldName="" unit="" dataType="string" precision="24"
	 * scale="" referenceType="0" defaultValue="" logicPrimaryKey="false"
	 * notNull="false" validate="true" index="false" indexType=""/>
	 * <mdpAttribute description="操作者所属部门" name="Dept_Id" oldName="" unit=""
	 * dataType="string" precision="15" scale="0" referenceType="2"
	 * defaultValue="" logicPrimaryKey="false" notNull="false" validate="true"
	 * index="false" indexType=""> <reference displayName=""
	 * referenceTable="BM$_UserArea" storeName=""/> </mdpAttribute>
	 * <mdpAttribute description="操作时间" name="Option_Time" oldName="" unit=""
	 * dataType="string" precision="20" scale="" referenceType="0"
	 * defaultValue="" logicPrimaryKey="false" notNull="false" validate="true"
	 * index="false" indexType=""/> <mdpAttribute description="动作类型"
	 * name="Action" oldName="" unit="" dataType="string" precision="15"
	 * scale="" referenceType="2" defaultValue="" logicPrimaryKey="false"
	 * notNull="false" validate="true" index="false" indexType=""> <reference
	 * displayName="" referenceTable="BM$_LogAction" storeName=""/>
	 * </mdpAttribute> <mdpAttribute description="动作描述" name="Description"
	 * oldName="" unit="" dataType="string" precision="200" scale=""
	 * referenceType="0" defaultValue="" logicPrimaryKey="false" notNull="false"
	 * validate="true" index="false" indexType=""/> <mdpAttribute
	 * description="操作者的IP" name="IP" oldName="" unit="" dataType="string"
	 * precision="24" scale="" referenceType="0" defaultValue=""
	 * logicPrimaryKey="false" notNull="false" validate="true" index="false"
	 * indexType=""/> <mdpAttribute description="网络地址" name="URL" oldName=""
	 * unit="" dataType="string" precision="400" scale="" referenceType="0"
	 * defaultValue="" logicPrimaryKey="false" notNull="false" validate="true"
	 * index="false" indexType=""/> </mdpClass>
	 */
}