package com.bleujin.dbfs;
import java.util.List;
import junit.framework.TestCase;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.beanutils.PropertyUtils;
import com.bleujin.dbfs.common.JSONParser;
import com.bleujin.dbfs.common.QueryObj;
import com.bleujin.framework.db.DBController;
import com.bleujin.framework.db.Page;
import com.bleujin.framework.db.manager.DBManager;
import com.bleujin.framework.db.manager.MSSQLDBManager;
import com.bleujin.framework.db.procedure.IQueryable;
import com.bleujin.framework.db.procedure.QueryType;
import com.bleujin.framework.util.Debug;
public class TestJSON extends TestCase{
private DBController dc ;
public void setUp() throws Exception {
DBManager dbm = new MSSQLDBManager("jdbc:sqlserver://localhost;databasename=test", "bleu", "redf") ;
dc = new DBController(dbm) ;
dc.initSelf() ;
}
public void tearDown() throws Exception {
dc.destroySelf() ;
}
public void testRequestInput() throws Exception {
String json = "{name=\"json\",bool:true,int:1,double:2.2,func:function(a){ return a; },array:[1,2]}";
JSONObject jsonObject = JSONObject.fromObject( json );
Object bean = JSONObject.toBean( jsonObject );
assertEquals( jsonObject.get( "name" ), PropertyUtils.getProperty( bean, "name" ) );
assertEquals( jsonObject.get( "bool" ), PropertyUtils.getProperty( bean, "bool" ) );
assertEquals( jsonObject.get( "int" ), PropertyUtils.getProperty( bean, "int" ) );
assertEquals( jsonObject.get( "double" ), PropertyUtils.getProperty( bean, "double" ) );
assertEquals( jsonObject.get( "func" ), PropertyUtils.getProperty( bean, "func" ) );
List expected = JSONArray.toList( jsonObject.getJSONArray( "array" ) );
// Assertions.assertListEquals( expected, (List) PropertyUtils.getProperty( bean, "array" ) );
}
public void testParseSelectCommand() throws Exception {
String json = "{query:{type:'usercommand', ctype:'select', command:\"select * from copy_tblc where no1 < :no1\", param:{no1:10}, page:{listnum:10, pageno:2} }}";
JSONObject jo = JSONObject.fromObject(json);
QueryObj cmd = new JSONParser(dc).parseToQueryable(jo);
// querytype
assertEquals(QueryType.USER_COMMAND, cmd.getQueryType());
// command type
assertEquals(IQueryable.QUERY_COMMAND, cmd.getCommandType());
// page
Page page = cmd.getPage() ;
assertEquals(10, page.getListNum()) ;
assertEquals(2, page.getPageNo()) ;
// param
assertEquals("10", cmd.getParam("no1").toString()) ;
assertEquals(1, cmd.getParams().size()) ;
}
public void testDefaultValue2() throws Exception {
String json = "{query:{type:'usercommand', ctype:'select', command:\"select * from copy_tblc where no1 < '10'\"}}";
JSONObject jo = JSONObject.fromObject(json);
QueryObj cmd = new JSONParser(dc).parseToQueryable(jo);
assertEquals(dc.getLimitedRows(), cmd.getPage().getListNum()) ;
assertEquals(1, cmd.getPage().getPageNo()) ;
assertEquals(0, cmd.getParams().size()) ;
}
public void testDefaultValue3() throws Exception {
String json = "{query:{command:\"select * from copy_tblc where no1 < '10'\"}}";
JSONObject jo = JSONObject.fromObject(json);
QueryObj cmd = new JSONParser(dc).parseToQueryable(jo);
assertEquals(dc.getLimitedRows(), cmd.getPage().getListNum()) ;
assertEquals(1, cmd.getPage().getPageNo()) ;
assertEquals(0, cmd.getParams().size()) ;
}
public void testDefaultValue1() throws Exception {
String json = "{query:{type:'usercommand', ctype:'select', command:\"select * from copy_tblc where no1 < 3\", param:{}, page:{} }}";
JSONObject jo = JSONObject.fromObject(json);
QueryObj cmd = new JSONParser(dc).parseToQueryable(jo);
assertEquals(dc.getLimitedRows(), cmd.getPage().getListNum()) ;
assertEquals(1, cmd.getPage().getPageNo()) ;
assertEquals(0, cmd.getParams().size()) ;
JSONObject result = cmd.execute() ;
assertEquals(2, result.getJSONObject("RESULT").getJSONArray("ROWS").size() ) ;
}
public void testSelectProcedure() throws Exception {
String json = "{query:{type:'userprocedure', ctype:'select', command=\"sample@selectEmpBy()\", page:{listnum:5, pageno:1} }}";
JSONObject jo = JSONObject.fromObject(json);
QueryObj cmd = new JSONParser(dc).parseToQueryable(jo);
// querytype
assertEquals(QueryType.USER_PROCEDURE, cmd.getQueryType());
// command type
assertEquals(IQueryable.QUERY_COMMAND, cmd.getCommandType());
// page
Page page = cmd.getPage() ;
assertEquals(5, page.getListNum()) ;
assertEquals(1, page.getPageNo()) ;
// param
assertEquals(0, cmd.getParams().size()) ;
JSONObject result = cmd.execute() ;
assertEquals(5, result.getJSONObject("RESULT").getJSONArray("ROWS").size() ) ;
}
public void testInsert() throws Exception {
dc.execUpdate("delete from update_sample") ;
String json = "{query:{ctype:'dml', command:\"insert into update_sample values(:a, :b)\", param:{a:1, b:'abc'} }}" ;
JSONObject jo = JSONObject.fromObject(json);
QueryObj query = new JSONParser(dc).parseToQueryable(jo);
assertEquals(QueryType.USER_COMMAND, query.getQueryType());
assertEquals(IQueryable.UPDATE_COMMAND, query.getCommandType());
JSONObject result = query.execute() ;
assertEquals(1, result.getJSONObject("RESULT").getInt("ROWCOUNT")) ;
}
public void testUserProceduresSelect() throws Exception {
String json = "{query:{name:'upts', type:'userprocedures', ctype:'select', " +
" command:[{query:{command:'select count(*) cnt from copy_tblc'}}, " +
" {query:{command:'select * from copy_tblc', page:{listnum:5, pageno:1}}}]" +
"}}" ;
JSONObject jo = JSONObject.fromObject(json);
QueryObj query = new JSONParser(dc).parseToQueryable(jo);
assertEquals("upts", query.getProcSQL()) ;
assertEquals("select count(*) cnt from copy_tblc", query.getQueryObj(0).getProcSQL()) ;
assertEquals("select * from copy_tblc", query.getQueryObj(1).getProcSQL()) ;
JSONObject result = query.execute() ;
Debug.debug(result) ;
}
public void testUserProceduresInsert() throws Exception {
dc.execUpdate("delete from update_sample") ;
String json = "{query:{ name:'upts', type:'userprocedures', ctype:'dml', " +
" command:[{query:{command:\"insert into update_sample values(1, '2')\"}}, " +
" {query:{command:\"update update_sample set b = '3' where a = 1\"}}]" +
"}}" ;
JSONObject jo = JSONObject.fromObject(json);
QueryObj query = new JSONParser(dc).parseToQueryable(jo);
JSONObject result = query.execute() ;
assertEquals(2, result.getJSONObject("RESULT").getInt("ROWCOUNT")) ;
}
}
/*
* {query:{ type:'usercommand | userprocedure',
* ctype:'select | dml | ddl',
* command:'select * from copy_tblc where no1 < :no1',
* param:{no:10},
* page:{listnum:10, pageno:2}
* }}
*
* {query:{type:'userprocedures', ctype:'select',
* command:[{query:{command:\'select 1'}},
* {query:{command:\'select 2'}}]
* }}
*
*
*
*/