Hibernate-簡化單表RUDSQL查詢

1、（具體參考javaweb+hibernate3_v15.doc，這里項目所需知識點-單表添刪改，多表查詢用session.createsqlquery（sql）hibernate概念: orm技術(shù)，封裝jdbc， oo操作db （將db表 通過hbm.xml映射 為 pojo-entity-vo-po-bean）表1（列） 表2 .t1.hbm.xml t2.hbm.xml .類1（字段getter setter） 類2 . （圖示意）幾個類：configuration: 管理hib配置.cfg.xml(jdbc底層dburl,user,pwd,jdbcdriver,dialet.)，hib映

2、射文件*.hbm.xmlsessionfactory: 產(chǎn)生session實例，緩存生成的sql語句和hib運行時使用的映射元數(shù)據(jù)(hib3.2自動生成hibernatesessionfactory)configuration conf=new configuration().configure();/configure(resource) sessionfactory hsessionfactory=conf.buildsessionfactory();session session=hsessionfactory.getsession();session：持久化管理器，代表與db間的一次操

3、作（多個crud），用完需關(guān)閉。transaction：hib的cud操作必須transaction commit，默認autocommit=false/ jdbc為turemy db透視圖操作 （為hib配置準備，specify db connection detail）myeclipse新建projhibdemo，open-perspectivemyeclipse db explorer new db connection: mysql j/connector 填寫參數(shù) add jars(驅(qū)動類庫 )：mysql-connector-java-5.1.16-bin.jar org.gjt.

4、mm.mysql.driver 連上db后：1. sql測試：展開mydbconn/connected to mysql 右擊/new sql editor 在窗口中寫sql后執(zhí)行2. 查看er圖：右擊庫empdb,new er diagram,選擇項目hibdemo目錄為er圖存放目錄，選擇有主外鍵關(guān)系的表（如emp,department） create database empdb2;use empdb2;create table department(id int auto_increment primary key,name varchar(50) not null); create

5、table interesting(id int auto_increment primary key,name varchar(255) not null);create table emp(id int auto_increment primary key,name varchar(255) not null,sex char(1) default 'm' check(sex in('m','f'),department_id int references department(id);create table emp_interesting

6、( /m:m的中間表，hib建議保留外鍵字段即可 /id int auto_increment primary key,emp_id int references emp(id),interesting_id int references interesting(id);hib步驟（hibdemo）：添加hib支持并生成hibsessionfactory類；生成pojo類和映射文件；操作代碼dao1.添加hib支持：右擊項目hibdemo,選myeclipse/add hib capabilities, hib3.2myeclipse7.0默認,選copy lib jars to proj f

7、older(便于項目移植)看到hibernate.cfg.xml是hib配置文件，進一步設置如下：db driver (可下拉選擇來自db explorer設置）： enable dynamic table creattion會加<!-<property name="hbm2ddl.auto">update</property> ->，請不要選擇，是正向工程處理：根據(jù)class+hbm.xml生產(chǎn)表創(chuàng)建hibnatesessionfactory（用于創(chuàng)建hibsession與db的一次連接的相關(guān)封裝），給包名mon2.生成pojo類和映射

8、文件:右擊db browser中的empdb中表s可多個表，hib reverse engineering(反向工程table轉(zhuǎn)pojo) finish后：hib.cfg.xml多了表到pojo的映射文件，項目的package explorer也可看到pojo和mapping文件 可選：db connection pool設置<property name="vider_class">org.hibernate.connection.c3p0connectionprovider </property> <propert

9、y name="hibernate.c3p0.min_size">5</property> <property name="hibernate.c3p0.max_size">10</property> <property name="hibernate.c3p0.max_statements">50</property> <property name="hibernate.c3p0.timeout">3600</property&g

10、t; <property name="hibernate.c3p0.idle_test_period">120</property> <property name="hibernate.c3p0.acquire_increment">2</property> <property name="current_session_context_class">thread</property> 操作代碼測試（1單表crud、2多表查詢）-注意hql可用于批量刪改，帶條件即

11、可1單表department crud -以下添刪改代碼可參考myeclipse生成的departmentdao類中方法 添加：department dept=new department();/準備數(shù)據(jù)對象 dept.setname(“生產(chǎn)2部”);session session=hibernatesessionfactory.getsession();/conn上empdbtransaction tx=session.begintransaction(); /insert update delete事務處理select非事務session.save(dept)如何判斷save成功？用try

12、 session.save(deprt);commit; saved=true;catch() tx.rollback; saved=false; finallysession.close();/save(detp)->sql insert->department表行mit(); /hib： tx auto_commit默認false vs jdbc tx auto_commit默認truesession.close(); /別忘了關(guān)閉 (show_sql: hibernate: insert into empdb.department (name) values (?)練習：插入

13、department數(shù)據(jù) (市場部，生產(chǎn)部，銷售部，經(jīng)理室) session.save(dept) 修改 經(jīng)理室->總經(jīng)理室 session.update(dept) 插入emp(阿呆，m, 生產(chǎn)部) (波波，m, 銷售部)查詢：（1）get/load單個對象; （2）criteria.add(criterion), （3）query(hql) (4) sqlquery(sql)（1）by id： get，load方法session session=hibernatesessionfactory.getsession();/department dept=(department)sess

14、ion.get(department.class, 6);/按照id查詢/ show_sql: hibernate: select department0_.id。0_ from empdb.department department0_ where department0_.id=? department dept=(department)session.load(department.class, 6);/上面get()改load/ load默認延遲加載，只有dept.getname()時即需要去發(fā)select取值時才發(fā)sql/將hbm.xml中默認<class lazy=“true

15、” 改為 lazy=“false”> 時load（）同get(clazz,6)方法-立即加載-發(fā)sqlsession.close();system.out.println(dept.getname(); （2）criteria準則方法session session=hibernatesessionfactory.getsession();criteria criteria =session.createcriteria(department.class);criterion c1=org.hibernate.criterion.expression.like(“name”, “%部”);

16、/注意不用單引號criteria.add(c1);list<department> list=criteria.list();session.close();for(department dept :list)system.out.println(dept.getid()+”:”+dept.getname();show_sql: select this_.id as id2_0_, this_.name as name2_0_ from empdb.department this_ where this_.name like ? （3）query(hql)方法session ses

17、sion=hibernatesessionfactory.getsession();/類似 standard query language->rdb, hibernate query language ->oostring hql=“from mon.department as d where like '%部'“; query query=session.createquery(hql);list<department> list=query.list();session.close();for(department dept :list

18、)system.out.println(dept.getid()+”:”+dept.getname(); ex.2 string hql="from department where name like :a "session session=hibernatesessionfactory.getsession();list<department> departs=session.createquery(hql).setparameter("a", "%部").list();session.close();show_sql

19、: select department0_.id as id2_, department0_.name as name2_ from empdb.department department0_ where department0_.name like ?(4) sql查詢，sqlquery(sq)： 返回object session hsession =hibernatesessionfactory.getsession();stringbuffer sql=new stringbuffer();sql.append(“select e.* from empdb.emp e “);/ id n

20、ame sex new object1,”cy”,m/sql.append(“ where =?”);sql.append(“ where like ?”);/底層preparedstatementlist<object> list=hsession.createsqlquery(sql.tostring().setparameter(0, “%car%”).list();/.setparameter(0, “carl”).list();for(object data:list)for(int i=0;i<data.length;i+)system.

21、out.print(datai+” “);/1 carl 男 1 hsession.close();修改：找到（單表單值查詢get）并修改department dept=new department();dept.setid(6);/銷售部dept.setname(“銷售2部”); session session=hibernatesessionfactory.getsession(); transaction tx=session.begintransaction(); session.update(dept);/update department set name=“銷售2部” where

22、 id=6 mit(); session.close();ex.2session session=hibernatesessionfactory.getsession();/get pojo instance from db department depart=(department)session.get(department.class, 5);/url update?id=5depart.setname("經(jīng)理2室");transaction tx=session.begintransaction();/session.update(depart); /hib功能，判

23、斷-updatemit();session.close();show_sql: select department0_.id as id2_0_, department0_.name as name2_0_ from empdb.department department0_ where department0_.id=?update empdb.department set name=? where id=? 刪除： session.delete(obj); 或query 方式（hqlquery,sqlquery） （1）例，刪除id=7的部門department dept=new depa

24、rtment();dept.setid(7); session session=hibernatesessionfactory.getsession(); transaction tx=session.begintransaction();/session.delete(“mon.department”, dept);/delete方法1session.delete(dept);/只要pk值 (id)與 表行id匹配，表行被刪除mit(); session.close();（2）用hql語句query.executeupdate刪除（可where條件批量） org.hibernate.sess

25、ion hsession = mon.hibernatesessionfactory.getsession();string hql=“delete department where id=5 or id=8”;query query 面向hql、nativesql，criteria面向?qū)ο髊uery=hsession.createquery(hql);transaction tx=hsession.begintransaction();query.executeupdate(); mit();hsession.close(); 注：以上crud操作可放在一個session完成 native

26、sql: createsqlquery 建議掌握(1)stringbuffer sb=new stringbuffer();session session=hibernatesessionfactory.getsession();emp emp=(emp)session.get(emp.class, 8); sb.append(emp.getname()+” “); sb.append(emp.getsex()+” “); sb.append(emp.getdepartment().getname()+” “);set<interesting> interestings=emp.g

27、etinterestings();for(interesting inter:interestings) sb.append(inter.getname()+” “); system.out.println(sb);session.close(); (2) session session=hibernatesessionfactory.getsession();/string hql=“from emp e where like '%an%'“;string hql=“from emp e where like ?”;query query=sess

28、ion.createquery(hql);/statementlist<emp> list=query.setstring(0, “%an%”).list();/statement.executequery()for(emp emp : list)system.out.println(emp.getname()+” “+emp.getsex()+” “+(emp.getdepartment()=null?”“:emp.getdepartment().getname();/session.close(); (3.a) session session=hibernatesessionf

29、actory.getsession();string sql=“select as ename,sex, as dname from emp e left join department d on e.department_id=d.id”;sqlquery sq=session.createsqlquery(sql);list<object> list=sq.addscalar(“ename”, hibernate.string).addscalar(“sex”, hibernate.string).addscalar(“dname”, hibernat

30、e.string) 當select字段有重名時,hibernate 早期必須加addscalar( ) .list(); /3 object<->行,sex,for(int i=0;i<list.size();i+)object data=list.get(i);system.out.println(data0+” “+data1+” “+data2);session.close();(3.b) 優(yōu)化sql查詢返回：list<map> 行數(shù)據(jù)為map，取值直觀！（建議使用）session session=hibernatesessionfa

31、ctory.getsession();string sql=“select as ename,sex, as dname同名字段必須處理： 返回列必須都注冊addscalar，列名同必須as別名，否則同名字段返回為第一列相同值！見上執(zhí)行結(jié)果 from emp e left join department d on e.department_id=d.id”;sqlquery sq=session.createsqlquery(sql); /list<object> list list<map> list / <”ename”,”caoyu

32、”>,<”sex”,”m”>, =sq.addscalar(“ename”, hibernate.string) /,名稱重復,必須！.addscalar(“sex”, hibernate.string).addscalar(“dname”, hibernate.string)hibernate3.3高版本不需要加addscalar() .setresulttransformer(transformers.alias_to_entity_map)/map.list();/3 object<->行,sex,sessio

33、n.close();for(int i=0;i<list.size();i+)/*object data=list.get(i);system.out.println(data0+” “+data1+” “+data2);*/map data=list.get(i); system.out.println(data.get(“ename”)+” “+data.get(“sex”)+” +data.get(“dname”); jstl: (servlet: request.setattribute("emps",emps);) <c:foreach var=emp

34、 items="$emps"> $emp.ename </c:foreach> 項目中常用舉例：blob，clob特殊使用(myeclipse中生成class和xml需要修改）; 略datetime日期使用(hibernate3.3中無問題,但以前版本注意)主鍵生成方式class=？ assigned: 主鍵由應用代碼設置，hib無需干預，如：dept.setid(1000); session.save(dept);hilo:通過hilo算法生成主鍵，需額外的表保存主鍵歷史狀態(tài)，如：(1)create table t_hilo(hilo_id int);i

35、nsert into t_hilo values(1); (2).hbm.xml: <class name=“fruit”> <id ><generator class=“hilo”> <param name=“table”>t_hilo</param> <param name=“column”>hilo_id</param> </generator> </id> (3) fruit fruit=new fruit(“l(fā)isi”,”hello”,100); session.save(fr

36、uit); seqhilo:同hilo,主鍵歷史放sequence中（如oracle） increment：應用實例中維持1個變量，每次要主鍵時加1. 可能問題：多個應用實例同時訪問db，可能主鍵重復identity：用db提供的主鍵生成機制，如sqlserver、mysql中自增機制 sequence:用db提供的sequence機制，如oracle native：由hib根據(jù)db dialect中定義，采用identiy,hilo,sequence的其中1種來生成主鍵 uuid.hex: 由hib生成128位唯一值(32個字符)，db適應性最好且高效. id varchar(32) uui

37、d.string: 類似uuid.hex(16個字符) foreign：使用外部表字段為主鍵。 select: 早期系統(tǒng)，主鍵依賴insert觸發(fā)器生成hql語法： myeclipse中檢查：右擊項目/myeclipse/open hql editorhql子句大小寫無關(guān)，但類名、屬性名注意大小寫query query=hsession.createquery(hql); list=query.list();查詢含繼承判定，如from tuser會返回tuser子類記錄，即使子類分別對應不同表from java.lang.object 返回db所有表記錄from tuserfrom tuser

38、 as u where =erica where = <> < > >= <= between,not between,in, not in , is, likefrom tuser user where user.age>20 user.age between 20 and 30 user.age in(18,28) is null like er% user.age %2=1 /算術(shù)表達式 user.age>20 and like er% /可用 and or屬性查詢：s

39、elect ,user.age from tuser as user /list<object/map> list=q.list();for(object rs:list) rs0+”:”+ rs1 動態(tài)構(gòu)造對象：select new tuser (,user.age) from tuser as user統(tǒng)計：select new statics( count(*),min(user.age) ) from tuser as user class statics statics(int cnt, int minage) int cnt; int

40、 minage; setter setter原生sql函數(shù)：select upper() from tuser as usedistinct去重復項：select distinct from tuser as userupdate,delete: session.createquery(“update tuser set age=13 where id=1”).executeupdate(); mit(); 批量添刪處理 delete tuser where age>18 /但注意delete update極可能導致緩存同步障礙order by: f

41、rom tuser user order by desc,user.age ascgroup by:select count(user),user.age from tuser user group by user.age group by user.age having count(user.age)>10參數(shù)綁定： “from tuser user where user.age>“+age /code亂;sql每次都解析優(yōu)化,無法用到hib的preparedstm;sql注入攻擊“from tuser u where =

42、9;”+name+” ' and pwd=”+pwd+”用戶名隨意：ada，密碼x or 1=1,sql：where name=ada and pwd=x or 1=1if(rs.next() . query接口參數(shù)填充query.setxxx()：query q=hsession.createquery(“select u.id, from emp u where =? and u.sex=:sex”); q.setstring(0, “ada”);/順序占位符 q.setstring(“sex”, “m”);/引用占位符 (javabean封裝查詢參數(shù)方式，

43、略：class userqueryname,sex,getters,setters ; query.setproperties(userquery)引用查詢：實體映射hbm.xml中配置query（與class同級別， myeclipse中可design），好處無hql出現(xiàn) <query name=“querybyname”>from emp e where =:name</query> 調(diào)用：query q=hsession.getnamedquery(“querybyname”);q.setstring(“name”, “bob”); list<e

44、mp> list=q.list(); 聯(lián)合查詢：inner join,left/right outer join,full joinq=hsession.createquery(“from emp u inner join fetch fetch表明department讀出后填充到emp對象的department屬性中。忽略fetch，返回每行為一個objectu.department”);/沒有on x=y因hbm.xml有指定/sql等價select. from emp u inner join departement d on u.department_id=d.id list&l

45、t;emp> list=q.list();for(emp e:list) system.out.println(e.getname()+”:”+e.getdepartment().getname();show_sql：select emp0_.id as id1_0_, department1_.id as id2_1_, emp0_.department_id as department2_1_0_, emp0_.name as name1_0_, emp0_.sex as sex1_0_, department1_.name as name2_1_ from empdb.emp em

46、p0_ inner join empdb.department department1_ on emp0_.department_id=department1_.id比較show_sql：query q=hsession.createquery(“from emp u”); select emp0_.id as id1_, emp0_.department_id as department2_1_, emp0_.name as name1_, emp0_.sex as sex1_ from empdb.emp emp0_hibernate: select department0_.id as

47、id2_0_, department0_.name as name2_0_ from empdb.department department0_ where department0_.id=?carl:市場&銷售hibernate: select department0_.id as id2_0_, department0_.name as name2_0_ from empdb.department department0_ where department0_.id=?edwins:生產(chǎn)hibernate: select department0_.id as id2_0_, dep

48、artment0_.name as name2_0_ from empdb.department department0_ where department0_.id=?left outer join: from emp e left join fetch e.department (無部門null替代)right outter join:from emp e right join u.department (無emp null替代，返回只能object行) 注意：fetch僅對inner join 和left join有效full join: from emp e full join u.d

49、epartment笛卡爾積hql子查詢：from emp u where (select count(*) from u.department)>1 java類型, hibernate類型，sql類型( 實際開發(fā)一般以emclipse生成為準，不行再改hbm.xml )hibernate映射類型對照表java類型 hibernate映射類型 sql類型java.math.bigdecimalbig_decimalnumericbytebinaryvarbinary(blob)boolean(java.lang.boolean)booleanbitbyte(java.lang.byte)byte tinyintjava.util.calendarcalendartimestampjava.sql.clobclobclobjava.util.date 或java.sql.datedatedatedouble(java.lang.double)doubledoublefloat(java.lang.float)floatfloatint (java.lang.integer)integerintegerjava.util.locallocalvarcharlong(java.lang.lon