#
习惯Spring + Hibernate带来的方便, 但有时在应用中测试Hibernate的新功能并不是很方便.
实际上Hibernate的test代码就有这个功能, 而且不用考虑create table等麻烦事.
在自己的project中使用, 只需继承org.hibernate.test.TestCase, 改变hand code的路径, 设定hibernate.properties文件, 这样就有了纯Hibernate测试环境
public abstract class HibernateTestCase extends TestCase
{
protected HibernateTestCase(String x)
{
super(x);
}
protected String getBaseForMappings() {
return "com/waterye/test/";
}
/**//**
* true时, 使用auto schema export, hibernate.hbm2ddl.auto create-drop
*/
protected boolean recreateSchema() {
return false;
}
}
说明: 以idea为例
1. 将hibernate.properties放到test根目录下
2. pojo, hbm.xml, Test.java放到同一目录下(参考hibernate的测试代码)
3. 在idea中run test method
使用Oracle特有的查询语法, 可以达到事半功倍的效果
1. 树查询
create table tree (
id number(10) not null primary key,
name varchar2(100) not null,
super number(10) not null // 0 is root
);
-- 从子到父
select * from tree start with id = ? connect by id = prior super
-- 从父到子
select * from tree start with id = ? connect by prior id = suepr
-- 整棵树
select * from tree start with super = 0 connect by prior id = suepr 2. 分页查询
select * from (
select my_table.*, rownum my_rownum from (
select name, birthday from employee order by birthday
) my_table where rownum < 120
) where my_rownum >= 100; 3. 累加查询, 以scott.emp为例
select empno, ename, sal, sum(sal) over(order by empno) result from emp;
EMPNO ENAME SAL RESULT
---------- ---------- ---------- ----------
7369 SMITH 800 800
7499 ALLEN 1600 2400
7521 WARD 1250 3650
7566 JONES 2975 6625
7654 MARTIN 1250 7875
7698 BLAKE 2850 10725
7782 CLARK 2450 13175
7788 SCOTT 3000 16175
7839 KING 5000 21175
7844 TURNER 1500 22675
7876 ADAMS 1100 23775
7900 JAMES 950 24725
7902 FORD 3000 27725
7934 MILLER 1300 29025 4. 高级group by
select decode(grouping(deptno),1,'all deptno',deptno) deptno,
decode(grouping(job),1,'all job',job) job,
sum(sal) sal
from emp
group by ROLLUP(deptno,job);
DEPTNO JOB SAL
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 all job 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 all job 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 all job 9400
all deptno all job 29025 5. use hint
当多表连接很慢时,用ORDERED提示试试,也许会快很多
SELECT /**//*+ ORDERED */*
FROM a, b, c, d
WHERE
dbunit: DbUnit is a JUnit extension (also usable with Ant) targeted for database-driven projects
official site, 好久没更新了, 最新版本2.1还是2004年5月的
1. use ant task
<taskdef classpathref="project.classpath" classname="org.dbunit.ant.DbUnitTask" name="dbunit" />
导出数据
<target name="export">
<dbunit password="${database.password}" userid="${database.userid}"
url="${database.url}" driver="${database.driver}" supportbatchstatement="true">
<export format="xml" dest="data/export-data.xml">
<query name="FOO" sql="SELECT COL1, COL2 FROM FOO WHERE COL1=4"/>
<table name="BAR"/>
</export>
</dbunit>
</target>
tip: 不指定query、table， 导出所有的table
导入数据
<target name="clean_insert">
<dbunit password="${database.password}" userid="${database.userid}"
url="${database.url}" driver="${database.driver}">
<operation format="xml" src="data/init-data.xml" type="CLEAN_INSERT" />
</dbunit>
</target>
tip:
type: UPDATE, INSERT, DELETE, DELETE_ALL, REFRESH,
MSSQL_INSERT, MSSQL_REFRESH, MSSQL_CLEAN_INSERT.
比较数据
<target name="compare">
<dbunit password="${database.password}" userid="${database.userid}"
url="${database.url}" driver="${database.driver}">
<compare format="xml" src="data/init-data.xml" />
</dbunit>
</target>
2. use code
导出数据
IDatabaseConnection conn = new DatabaseConnection(jdbcConnection, schema); // oracle指定schema
IDataSet dataSet = conn.createDataSet();
XmlDataSet.write(dataSet, new FileOutputStream("export-data.xml")); // xml file
FlatXmlDataSet.write(dataSet,new FileOutputStream("export-data.xml")); // flat xml file
XlsDataSet.write(dataSet,new FileOutputStream("export-data.xls")); // xls file
FlatDtdDataSet.write(dataSet,new FileOutputStream("export-data.dtd")); // dtd file
CsvDataSetWriter.write(dataSet, new File("export-data-csv")); // csv file
使用DatabaseSequenceFilter, 解决违反外键约束的问题
IDatabaseConnection conn = new DatabaseConnection(jdbcConnection);
ITableFilter filter = new DatabaseSequenceFilter(conn);
// ITableFilter filter = new DatabaseSequenceFilter(conn, tableNames);
IDataSet dataset = new FilteredDataSet(filter, conn.createDataSet());
XmlDataSet.write(dataset, new FileOutputStream("export-data.xml"));
导入数据
DatabaseOperation.REFRESH.execute(conn, dataSet);
DatabaseOperation.INSERT.execute(conn, dataSet);
删除数据
DatabaseOperation.DELETE.execute(conn, dataSet);
比较数据
IDatabaseConnection conn = new DatabaseConnection(jdbcConnection);
Compare compare = new Compare();
compare.setFormat("xml");
compare.setSrc(new File("export-data.xml"));
compare.execute(conn);
tip: 使用assert进行比较, 作用不大
code: Assertion.assertEquals(expectedTable, actualTable);
3. QueryDataSet: use sql
QueryDataSet queryDataSet = new QueryDataSet(conn);
queryDataSet.addTable("orders", ordersQuerySQL);
使intellij idea更加强大的plugins
1. BackgroudImage
在代码编辑器显示背景图, 个人喜欢透明度为10%
2. GenerateToString
为Java Bean生成toString()
3. UpperLowerCapitalize
Alt-C (Capitalize), Alt-L (Lowercase), Alt-P (Uppercase)
4. GroovyJ
groovy plugin for idea
5. Tomcat Integration
6. CVS Integration
7. JUnit Generator
自动生成test code
8. sql query plugin
新版本功能更强大, 个人主要用于从代码中抽取出sql语句, 然后到pl/sql developer debug
9. IdeaJad
反编译工具
10. PropertiesEditor
编辑属性文件
11. ShowEncodingPlugin