就業(yè)mybatis day02筆記1、輸入映射和輸出

IfWhereSqlForeach4、MybatisMapper接口動態(tài)5、Mybatis逆向工程（了解sql映射文件是mybatis的。昨天的工程，按照下圖進(jìn)行Mapper接口開發(fā)相關(guān)的文件，其他的刪除SqlMapConfig.xml配置文件。Mapper使用#{}占位符，或者${}sqlpojoMybatisognl表達(dá)式解析對象字段的值，#{}或者${}pojo屬性pojopojo條件（比如查詢用戶信息的時候，將用戶商品信息也作為查詢條件，這時包裝對象：PojopojoQueryVouserpublicpublicclassQueryVoprivateUserpublicUsergetUser()return}publicvoidsetUser(Useruser)this.user=}}SqlSELECT*FROMuserWHEREusernameLIKE張MapperUserMapperpublicvoidpublicvoidtestQueryUserByQueryVo()SqlSessionsqlSession= UserMapperuserMapper=QueryVoqueryVo=newuserUserusernewUser();List<User>list=for(Useru:list){}}sql：SELECTcount(*)FROMMapperUserMapperpublicvoidpublicvoidtestQueryUserCount()SqlSessionsqlSession= UserMapperuserMapper=intcount=userMapper.queryUserCount();}pojopojoresultTypepojopojosql查sqlpojoresultMap將字段名resultMappojo對resultMappojo，比如在查詢結(jié)果映pojolist實現(xiàn)一對一查詢和一對多查詢。ordersql：SELECTid,user_id,number,createtime,noteFROM2.4.1.pojo對OrderpublicpublicclassOrderprivateintprivateIntegerprivateStringprivateDateprivateStringnote;}<?xml<?xmlversion="1.0"encoding="UTF-8"<!DOCTYPEPUBLIC"-////DTDMapper <!--namespace：命名空間，用于 開發(fā)的時候使用，需要指定Mapper的類路徑--><mapper <selectid="queryOrderAll"resultType="order">SELECTid,user_id,createtime,noteFROMMapperpublicpublicinterfaceOrderMapper*List<Order>}publicpublicclassOrderMapperTestprivateSqlSessionFactorypublicvoidinit()throwsException{InputStreaminputStream=this.sqlSessionFactory=}publicvoidtestQueryAll()SqlSessionsqlSession=//OrderMapperOrderMapperorderMapperList<Order>list=for(Orderorder:list){}}}}userIdmapper.xmlsql查詢列(user_id)Order類屬性(userId)不一致，所pojo中。resultMaporderResultMapsql查詢列(user_id)Order<?xmlversion="1.0"<?xmlversion="1.0"encoding="UTF-8"<!DOCTYPEPUBLIC"-////DTDMapper <!--namespace：命名空間，用于 開發(fā)的時候使用，需要指定Mapper的類路徑--><mapper resultMappojo上，type就是指定映射到哪一pojo-->idResultMapid<resultMaptype="order",非常重要。如果是多個字段,idpropertypojocolumn <idproperty="id"column="id" <resultproperty="userId"column="user_id" <result <resultproperty="createtime"column="createtime" <resultproperty="note"column="note"<selectid="queryOrderAll"resultMap="orderResultMap">SELECTid,user_id,createtime,noteFROM<resultproperty="number"column="number"只需要修改Mapper.xml就可以了，再次如下通過mybatis提供的各種方法實現(xiàn)動態(tài)拼接sql。需求：根據(jù)和名字查詢用戶SELECTid,username,birthday, ,addressFROM`user`WHERE =1ANDusernameLIKE'%張%'If<selectid="queryUserByWhere"parameterType="user"SELECTSELECTid,username,,addressFROM = }ANDusernameMapperMapperpublicvoidpublicvoidtestQueryUserByWhere()SqlSessionsqlSession= UserMapperuserMapper=UserusernewUser(); List<User>list=for(Useru:list){}}如果注釋掉 寫的sql就了，顯然這樣是不靠譜的。sqlif<selectid="queryUserByWhere"parameterType="usSELECTid,username,birthday,,WHERE<if!=null!= AND =#{ <iftest="username!=nullandusername!= ANDusername Wheresqlwhere1=1這樣的語句，很麻煩可以使用where進(jìn)行改造<select<selectid="queryUserByWhere"parameterType="user"SELECTid,username, ,addressFROM wheresqland關(guān)鍵字<if !==}<iftest="username!=nullandusername!=ANDusernameLIKESqlSql中可將重復(fù)的sql提取出來，使用時用include即可，最終達(dá)到sql重用id,id,username,,

<selectid="queryUserByWhere"parameterType="user"<!--SELECTid,username,birthday,,addressFROM`user`--<!--使用include加載sql片段；refid是sql片段id-->SELECT<includerefid="userFields"/>FROM`user`<iftest=" !=null"> =#{}<iftest="username!=nullandusername!=ANDusernameLIKE sql<sqlid,username,birthday, ,如果要使用別的Mapper.xml配置的sql片段，可以在refidforeachsqlList，mybatisforeachid查詢用戶信息sql：SELECT*FROMuserWHEREidIN如下圖在pojo中定義list屬性ids多個用戶id，并添加getter/setter方idsids<selectid="queryUserByIds"parameterType="queryVo"SELECT*FROM 屬性<!--<!--<!--separator：指定遍歷的 間使用的分隔符--<foreachcollection="ids"item="item"open="idINpublicpublicvoidtestQueryUserByIds()SqlSessionsqlSession= UserMapperuserMapper=//userMapper執(zhí)行根據(jù)條件查詢用戶QueryVoqueryVo=newQueryVo();List<Integer>idsnewArrayList<>();List<User>list=for(Useru:list){}}記錄了 商品的o.user_ido.user_iduserId,`order``order`LEFTJOIN`user`uONo.user_id=resultTypepojopojo類中包括了訂單信息和用戶信息這樣返回對象的時候，mybatis自動把用戶信息也注入進(jìn)來了pojoOrderUserOrderOrderUserOrder<selectid="queryOrderUser"resultType="orderUser">`order`LEFTJOIN`user`uONo.user_id=MapperUserMapperpublicvoidpublicvoidtestQueryOrderUser()SqlSessionsqlSession= UserMapperuserMapper=List<OrderUser>list=for(OrderUserou:list){}}pojosql查詢結(jié)果集所有的字段。resultMapresultMappojo為訂單關(guān)聯(lián)查詢用戶是一對一關(guān)系，所以這里使用單個User對象關(guān)聯(lián)查詢Order<resultMaptype="order"<resultMaptype="order"<idproperty="id"column="id"<resultproperty="userId"column="user_id"<resultproperty="number"column="number"<resultproperty="createtime"column="createtime"<resultproperty="note"column="note"associationproperty:orderUserjavaType:<associationproperty="user" <idproperty="id"column="user_id"<resultproperty="username"column="username"<resultproperty="address"column="address"<selectid="queryOrderUserResultMap"resultMap="orderUserResultMap">`order`LEFTJOIN`user`uONo.user_id=MapperUserMapperpublicvoidpublicvoidtestQueryOrderUserResultMap()SqlSessionsqlSession= UserMapperuserMapper=List<Order>list=for(Ordero:list){}} o.idoid,`user`LEFTJOIN`order`oONu.id=pojoUserList<Order>orders屬性,<resultMaptype="user"<resultMaptype="user"<idproperty="id"column="id"<resultproperty="username"column="username"<resultproperty="birthday"column="birthday"<result " "<resultproperty="address"column="address"<collectionproperty="orders"javaType="list"Order<idproperty="id"column="oid"<resultproperty="number"column="number"<resultproperty="createtime"column="createtime"<resultproperty="note"column="note"<selectid="queryUserOrder"resultMap="userOrderResultMap"> o.idoid,`user`LEFTJOIN`order`oONu.id=MapperUserMapperpublicvoidpublicvoidtestQueryUserOrder()SqlSessionsqlSession= UserMapperuserMapper=List<User>list=for(Useru:list){} Mybatis1、SqlSessionFactoryspring2daospringsqlsession3、Mapper形式中，應(yīng)該從spring容器中直接獲得mapper的對象4springjar1、springjar2、Mybatisjar3、Spring+mybatis的整合包。4、Mysqljar包。5jar包。jarjavajarjarmybatisSpring事務(wù)管理（暫時可以不配置sqlsessionFactoryspringmapeer對象或者是dao實現(xiàn)類配置到spring容器中config<?xmlversion="1.0"<?xmlversion="1.0"encoding="UTF-8"<!DOCTYPEPUBLIC"-////DTDConfig 2.指定掃描包，會把包內(nèi)所有的類都設(shè)置別名，別名的名稱就是類名，大小寫不敏感--><package .mybatis.pojo"SqlSessionFactoryBeanmybatis-springjarspring來說，mybatisjar包。mybatis-spring-1.2.2.jar的源碼，如下圖<?xmlversion=<?xmlversion="1.0"encoding="UTF-<beansxmlns=""""""" ceholder<bean<propertyname="driverClassName"value="${jdbc.driver}"<propertyname="url"value="${jdbc.url}"<propertyname="username"value="${jdbc.username}"<property<propertyname="password"value="${jdbc.password}"<propertyname="maxActive"value="10"<propertyname="maxIdle"value="5"<beanid="sqlSessionFactory"<!--配置 配置文件--<propertyname="configLocation"value="classpath:SqlMapConfig.xml"/><propertyname="dataSource"ref="dataSource"##Globalloggingconfigurationlog4j.rootLogger=DEBUG,stdout#Consoleoutput...log4j.appender.stdout.layout.ConversionPattern=%5p[%t]-%m%nDaodao1dao2、使用Mapper形式開發(fā)方MapperMapperidpublicpublicclassUserprivateintprivateStringusernameprivate privateDatebirthday;//privateStringaddress;//}daoDAO開發(fā)接口+daoSqlsessionDaoSupport<?xml<?xmlversion="1.0"encoding="UTF-8"<!DOCTYPEPUBLIC"-////DTDMapper <mapperid<select<selectid="queryUserById"parameterType="int"resultType="user">select*fromuserwhereid=#{id}<selectid="queryUserByUsername"parameterType="string"select*fromuserwhereusernamelike<insertid="saveUser"<selectKeykeyProperty="id"keyColumn="id"order="AFTER"selectinsertintouser SqlMapConfig如下圖進(jìn)行配置UserDaopublicpublicinterfaceUserDao*@paramUserqueryUserById(int*@paramList<User>queryUserByUsername(String@paramvoidsaveUser(User}DAOSqlSessionDaoSupportpublicpublicclassUserDaoImplextendsSqlSessionDaoSupportimplementsUserDao{publicUserqueryUserById(intid)SqlSessionsqlSession=Useruser=sqlSession.selectOne("queryUserById",return}publicList<User>queryUserByUsername(Stringusername)SqlSessionsqlSession=List<User>list=sqlSession.selectList("queryUserByUsername",username);return}publicvoidsaveUser(Useruser)SqlSessionsqlSession=}}daospringJunit用例。publicpublicclassUserDaoTestprivateApplicationContextpublicvoidsetUp()throwsExceptionthis.context=}publicvoidtestQueryUserById()UserDaouserDao=Useruser=userDao.queryUserById(1);}publicvoidtestQueryUserByUsername()UserDaouserDao=List<User>List<User>listuserDao.queryUserByUsername("張for(Useruser:list){}}publicvoidtestSaveUser()UserDaouserDao=Useruser=newUser(); user.setBirthday(newDate());}}Mapper形式開發(fā)<?xmlversion="1.0"<?xmlversion="1.0"encoding="UTF-8"<!DOCTYPEPUBLIC"-////DTDMapper <mapper id<selectid="queryUserById"parameterType="int"resultType="user">select*fromuserwhereid=#{id}<selectid="queryUserByUsername"parameterType="string"select*fromuserwhereusernamelike<insertid="saveUser"<selectKeykeyProperty="id"keyColumn="id"order="AFTER"selectinsertintouser ,address)values UserMapperpublicpublicinterfaceUserMapper*@paramUserqueryUserById(int@paramList<User>queryUserByUsername(String@paramvoidsaveUser(User}MapperFactoryBeanmybatis-spring <beanMapper<propertyname="mapperInterface" .mybatis.mapper.UserMapper"/>sqlSessionFactory<propertyname="sqlSessionFactory"ref="sqlSessionFactory"publicpublicclassUserMapperTestprivateApplicationContextpublicvoidsetUp()throwsExceptionthis.context=}publicvoidtestQueryUserById()//MapperUserMapperuserMapperUseruser=userMapper.queryUserById(1);}publicvoidtestQueryUserByUsername()//MapperUserMapperuserMapperList<User>listuserMapper.queryUserByUsername("張for(Useruser:list)}}publicvoidtestSaveUser()//獲取MapperUserMapperuserMapperUseruser=newUser(); user.setBirthday(newDate());}} <beanMapper<propertyname="basePackage" .mybatis.mapper"idMybatis使用的Mapper自動生成工具mybatis-generator-core-1.3.2來生成po類Mapper映射文件6.1.1.逆向工程到工作空間6.1.2eclipsegeneratorConfig.xmlMapper注意修改以下幾點(diǎn)pojoMapper配置文件如下<?xmlversion="1.0"encoding="UTF-<!DOCTYPEPUBLIC"-////DTDMyBatisGeneratorConfiguration <contextid="testTables"truefalse:<property ments"value="true" <jdbcConnectiondriverClass="com.mysql.jdbc.Driver"userId="root"<!--<jdbcConnectiondriverClass="oracle.jdbc