SqlServiceClientTransactionTest.java

/*
 *   This software is distributed under the terms of the FSF 
 *   Gnu Lesser General Public License (see lgpl.txt). 
 *
 *   This program is distributed WITHOUT ANY WARRANTY. See the
 *   GNU General Public License for more details.
 */
package com.scooterframework.orm.sqldataexpress.service;

import static org.junit.Assert.assertEquals;

import java.util.HashMap;
import java.util.Map;

import org.junit.Test;

import com.scooterframework.test.ScooterTestHelper;
import com.scooterframework.transaction.TransactionManager;
import com.scooterframework.transaction.TransactionManagerUtil;

/**
 * SqlServiceClientTransactionTest class
 * 
 * @author (Fei) John Chen
 *
 */
public class SqlServiceClientTransactionTest extends ScooterTestHelper {
	
	@Test
	public void test_retrieveTableDataBySQL() {
		String countSql = "SELECT count(*) FROM pets";
		
        TransactionManager tm =  TransactionManagerUtil.getTransactionManager();
        try{
        	tm.beginTransaction();
			
			Object countBeforeInsert = SqlServiceClient.retrieveObjectBySQL(countSql);
			assertEquals("Total rows countBeforeInsert", "13", countBeforeInsert.toString());
			
			Object nextID = getNextPetID();
			String sql = "INSERT INTO pets (id, name, type_id, owner_id) VALUES (?id, ?name, 1, 10)";
			Map<String, Object> inputs = new HashMap<String, Object>();
			inputs.put("id", nextID);
			inputs.put("name", "Lingling");
			int insertCount = SqlServiceClient.executeSQL(sql, inputs);
			assertEquals("number of rows inserted", 1, insertCount);
			
			String sql2 = "SELECT name FROM pets WHERE name = 'Lingling'";
			Object data = SqlServiceClient.retrieveObjectBySQL(sql2);
			assertEquals("name of the new pet", "Lingling", data.toString());
			
			Object countAfterInsert = SqlServiceClient.retrieveObjectBySQL(countSql);
			assertEquals("Total rows countAfterInsert", "14", countAfterInsert.toString());
			
			//artificially creating an exception 
			int i = 1;
			int j = 0;
			System.out.println("You should not see this line: " + i/j);
			
	    	tm.commitTransaction();
	    }
	    catch (Exception ex) {
	    	tm.rollbackTransaction();
			
			Object countAfterRollback = SqlServiceClient.retrieveObjectBySQL(countSql);
			assertEquals("Total rows countAfterRollback Lingling", "13", countAfterRollback.toString());
	    }
	    finally {
	    	tm.releaseResources();
	    }
		
		Object countTheEnd = SqlServiceClient.retrieveObjectBySQL(countSql);
		assertEquals("Total rows countTheEnd", "13", countTheEnd.toString());
	}
	
	@Test
	public void test_transactional_executeSQL_inputs() {
		String countSql = "SELECT count(*) FROM pets";
//		String tType = (String)CurrentThreadCache.get("key.TransactionStarterType");
//		Object tr = CurrentThreadCache.get("key.Transactions");
		
        TransactionManager tm =  TransactionManagerUtil.getTransactionManager();
    	
        try{
        	tm.beginTransaction();
			
			Object countBeforeInsert = SqlServiceClient.retrieveObjectBySQL(countSql);
			assertEquals("Total rows countBeforeInsert", "13", countBeforeInsert.toString());

			Object nextID = getNextPetID();
			String sql = "INSERT INTO pets (id, name, type_id, owner_id) VALUES (?id, ?name, 1, 10)";
			Map<String, Object> inputs = new HashMap<String, Object>();
			inputs.put("id", nextID);
			inputs.put("name", "Pingping");
			int insertCount = SqlServiceClient.executeSQL(sql, inputs);
			assertEquals("number of rows inserted", 1, insertCount);
			
			String sql2 = "SELECT name FROM pets WHERE name = 'Pingping'";
			Object data = SqlServiceClient.retrieveObjectBySQL(sql2);
			assertEquals("name of the new pet", "Pingping", data.toString());
			
			Object countAfterInsert = SqlServiceClient.retrieveObjectBySQL(countSql);
			assertEquals("Total rows countAfterInsert", "14", countAfterInsert.toString());
			
			//artificially creating an exception 
			int i = 1;
			int j = 0;
			System.out.println("You should not see this line: " + i/j);
			
	    	tm.commitTransaction();
	    }
	    catch (Exception ex) {
	    	tm.rollbackTransaction();
			
			Object countAfterRollback = SqlServiceClient.retrieveObjectBySQL(countSql);
			assertEquals("Total rows countAfterRollback Pingping", "13", countAfterRollback.toString());
	    }
	    finally {
	    	tm.releaseResources();
	    }
		
		Object countTheEnd = SqlServiceClient.retrieveObjectBySQL(countSql);
		assertEquals("Total rows countTheEnd", "13", countTheEnd.toString());
	}
	
	private Object getNextPetID() {
		String findNextID = "SELECT (max(id)+1) FROM pets";
		return SqlServiceClient.retrieveObjectBySQL(findNextID);
	}
}