系统在选择操作数据库的框架上面,到底是选择hibernate,还是mybatis。 首先说下两者的原理,如果你要关联几张表做查询,查出20条记录: 1.如果是mybatis select * from (select inner_table.*, rownum outer_table_rownum from (select sp_work_plan.name, spr
系统在选择操作数据库的框架上面,到底是选择hibernate,还是mybatis。
首先说下两者的原理,如果你要关联几张表做查询,查出20条记录:
1.如果是mybatis
select *
from (select inner_table.*, rownum outer_table_rownum
from (select sp_work_plan.name, sprocorgan1_.code --只是查询
from sp_work_plan workplanvo0_,
v_sp_organization sprocorgan1_,
v_sp_user sprocuserv2_,
v_sp_organization sprocorgan3_,
v_sp_user sprocuserv4_,
v_sp_user sprocuserv5_,
v_sp_organization sprocorgan6_
from workplanvo0_.apply_department_oid =
sprocorgan1_.org_id and
workplanvo0_.confirmation_uid = sprocuserv2_.user_id and
sprocuserv2_.org_id = sprocorgan3_.id and
workplanvo0_.create_uid = sprocuserv4_.user_id and
workplanvo0_.work_master_uid = sprocuserv5_.user_id and
workplanvo0_.work_team_id = sprocorgan6_.org_id) inner_table
where rownum where outer_table_rownum > 0;
2.如果是hibernate,像下面的sql要查20次。
select
workplanvo0_.id as id9_6_,
workplanvo0_.update_time as update2_9_6_,
workplanvo0_.optimistic_lock_version as optimistic3_9_6_,
workplanvo0_.code as code9_6_,
workplanvo0_.data_from as data5_9_6_,
workplanvo0_.data_state as data6_9_6_,
workplanvo0_.end_lifecycle as end7_9_6_,
workplanvo0_.name as name9_6_,
workplanvo0_.start_lifecycle as start9_9_6_,
workplanvo0_.flow_state as flow10_9_6_,
workplanvo0_.process_ins_id as process11_9_6_,
workplanvo0_.actual_end_time as actual12_9_6_,
workplanvo0_.actual_start_time as actual13_9_6_,
workplanvo0_.apply_department_oid as apply40_9_6_,
workplanvo0_.attention_level as attention14_9_6_,
workplanvo0_.complete_condition as complete15_9_6_,
workplanvo0_.confirmation_time as confirm16_9_6_,
workplanvo0_.confirmation_uid as confirm41_9_6_,
workplanvo0_.create_uid as create42_9_6_,
sprocorgan1_.id as id26_0_,
sprocorgan1_.update_time as update2_26_0_,
sprocorgan1_.optimistic_lock_version as optimistic3_26_0_,
sprocorgan1_.code as code26_0_,
sprocorgan1_.data_from as data5_26_0_,
sprocorgan1_.data_state as data6_26_0_,
sprocorgan1_.end_lifecycle as end7_26_0_,
sprocorgan1_.name as name26_0_,
sprocorgan1_.start_lifecycle as start9_26_0_,
sprocorgan1_.area_id as area10_26_0_,
sprocorgan1_.state as state26_0_,
sprocuserv2_.id as id27_1_,
sprocuserv2_.update_time as update2_27_1_,
sprocuserv2_.optimistic_lock_version as optimistic3_27_1_,
sprocuserv2_.code as code27_1_,
sprocuserv2_.data_from as data5_27_1_,
sprocuserv2_.data_state as data6_27_1_,
sprocuserv2_.end_lifecycle as end7_27_1_,
sprocuserv2_.name as name27_1_,
sprocuserv2_.start_lifecycle as start9_27_1_,
sprocuserv2_.account as account27_1_,
sprocorgan3_.id as id26_2_,
sprocorgan3_.update_time as update2_26_2_,
sprocorgan3_.optimistic_lock_version as optimistic3_26_2_,
sprocorgan3_.code as code26_2_,
sprocorgan3_.data_from as data5_26_2_,
sprocorgan3_.data_state as data6_26_2_,
sprocuserv4_.id as id27_3_,
sprocuserv4_.update_time as update2_27_3_,
sprocuserv4_.optimistic_lock_version as optimistic3_27_3_,
sprocuserv4_.code as code27_3_,
sprocuserv4_.data_from as data5_27_3_,
sprocuserv4_.data_state as data6_27_3_,
sprocuserv4_.end_lifecycle as end7_27_3_,
sprocuserv4_.name as name27_3_,
sprocuserv5_.id as id27_4_,
sprocuserv5_.update_time as update2_27_4_,
sprocuserv5_.optimistic_lock_version as optimistic3_27_4_,
sprocuserv5_.code as code27_4_,
sprocuserv5_.data_from as data5_27_4_,
sprocuserv5_.data_state as data6_27_4_,
sprocuserv5_.end_lifecycle as end7_27_4_,
sprocorgan6_.id as id26_5_,
sprocorgan6_.update_time as update2_26_5_,
sprocorgan6_.optimistic_lock_version as optimistic3_26_5_,
sprocorgan6_.code as code26_5_,
sprocorgan6_.data_from as data5_26_5_,
sprocorgan6_.data_state as data6_26_5_,
sprocorgan6_.end_lifecycle as end7_26_5_,
sprocorgan6_.name as name26_5_,
sprocorgan6_.start_lifecycle as start9_26_5_,
sprocorgan6_.state as state26_5_
from
sp_work_plan workplanvo0_
left outer join
v_sp_organization sprocorgan1_ on workplanvo0_.apply_department_oid=sprocorgan1_.org_id
left outer join
v_sp_user sprocuserv2_ on workplanvo0_.confirmation_uid=sprocuserv2_.user_id
left outer join
v_sp_organization sprocorgan3_ on sprocuserv2_.org_id=sprocorgan3_.id
left outer join
v_sp_user sprocuserv4_ on workplanvo0_.create_uid=sprocuserv4_.user_id
left outer join
v_sp_user sprocuserv5_ on workplanvo0_.work_master_uid=sprocuserv5_.user_id
left outer join
v_sp_organization sprocorgan6_ on workplanvo0_.work_team_id=sprocorgan6_.org_id
where workplanvo0_.id=? ;
1.设计阶段的影响
无法验证模型的合理性和预测性能。根据界面原型做数据库设计,优点是可以保证数据都能存到数据中,不足之处是无法保证模型的合理和性能。如果调整架构用ibatis,我们可以在设计完成后,写代码之前把复杂的查询写出来,制造一些数据进行性能预测。
2.开发阶段的影响
a.hibernate无法使用层次查询、分析函数、正则表达式等。不错,hibernate有调用sql的结果,如果通过接口调用sql,不便于调试,用ibatis非常适合调试。
b.在开发阶段也无法预测性能,就是测试sql。
c.hibernate的内部实现比较复杂,如果没有人能读懂里面的源代码,最好是只使用最简单的增、删、改,查(根据主键查)。反观mybatis,实现很简单,hold住。
3.运维阶段的影响
在运维阶段每天监控数据库,找到性能隐患,是业界的最佳实践。如果是hibernate的架构,即使我们找出了问题sql,也无法对改动sql进行调优,因为它是生成处理的,最多加一个索引。
最后:全部用hibernate对db操作,把hibernate当做一个sql生成的工具,其实就是把数据库当做一个黑盒,好像不需要对它有深入的了解,这样是被误导,看看我们现在有些组开发的报表开发工具,如果遇到大数据量,往往歇菜。