首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

IBatis SQL 投射

2012-10-05 
IBatis SQL 映射IBatis SQL 映射--------------------1. 特殊字符select idgetPersonsByAge parameter

IBatis SQL 映射
IBatis SQL 映射
--------------------
1. 特殊字符
<select id="getPersonsByAge" parameterClass=”int” resultresultresultparameter>
              SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
      </selectKey>
       insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)
       values  (#id#,#description#)
</insert>

<!— Microsoft SQL Server IDENTITY Column Example -->
<insert id="insertProduct-MS-SQL" parameter>
              SELECT @@IDENTITY AS ID
      </selectKey>
</insert>

4. 存储过程
<parameterMap id="swapParameters" >
    <parameter property="email1" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
    <parameter property="email2" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
</parameterMap>
<procedure id="swapEmailAddresses" parameterMap="swapParameters" >
    {call swap_email_address (?, ?)}
</procedure>

5. 模糊查询
SELECT * FROM PRODUCT WHERE PRD_DESCRIPTION LIKE '%$dog$%'

6. 配置Log4j日志
log4j.properties

# Global logging configurationlog4j.rootLogger=ERROR, stdout# SqlMap logging configuration...#log4j.logger.com.ibatis=DEBUG#log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG#log4j.logger.com.ibatis.sqlmap.engine.cache.CacheModel=DEBUG#log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientImpl=DEBUG#log4j.logger.com.ibatis.sqlmap.engine.builder.xml.SqlMapParser=DEBUG#log4j.logger.com.ibatis.common.util.StopWatch=DEBUG#log4j.logger.java.sql.Connection=DEBUG#log4j.logger.java.sql.Statement=DEBUG#log4j.logger.java.sql.PreparedStatement=DEBUG#log4j.logger.java.sql.ResultSet=DEBUG# Console output...log4j.appender.stdout=org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.layout=org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n


7. 动态SQL映射

<select id="dynamicGetAccountList"
cacheModel="account-cache"
resultMap="account-result" >
select * from ACCOUNT
<isGreaterThan prepend="and" property="id" compareValue="0">
where ACC_ID = #id#
</isGreaterThan>
order by ACC_LAST_NAME
</select>

上面的例子中,根据参数 bean“id”属性的不同情况,可创建两个可能的语句。如果参
数“id”大于 0,将创建下面的语句:
select * from ACCOUNT where ACC_ID = ?
或者,如果“id”参数小于等于 0,将创建下面的语句:
select * from ACCOUNT

8. 示例

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"><sqlMapConfig><settings cacheModelsEnabled="true"lazyLoadingEnabled="true" enhancementEnabled="true"errorTracingEnabled="true"maxRequests="32" maxSessions="10"maxTransactions="5"useStatementNamespaces="true"/><transactionManager type="JDBC">   <!-- 定义了ibatis的事务管理器有3中(JDBC,JTA,EXTERNAL) --><dataSource type="SIMPLE">   <!-- type属性指定了数据源的链接类型,也有3种类型(SIMPLE,DBCP,JNDI) --><property name="JDBC.Driver" value="org.hsqldb.jdbcDriver" /><property name="JDBC.ConnectionURL" value="jdbc:hsqldb:hsql://localhost/botoa" /><property name="JDBC.Username" value="sa" /><property name="JDBC.Password" value="" /><property name="Pool.MaximumActiveConnections" value="10" />   <!-- 连接池维持的最大容量 --><property name="Pool.MaximumIdleConnections" value="5" />   <!-- 连接池允许挂起的最大连接 --><property name="Pool.MaximumCheckoutTime" value="120000" />   <!-- 连接被某个任务所允许占用的最大时间 --><property name="TimeToWait" value="500" />   <!-- 线程允许等待的最大时间 --></dataSource></transactionManager><sqlMap resource="cn/bisoft/java/webapp/pojo/userMap.xml" /></sqlMapConfig> 



userMap.xml


<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"><sqlMap><typeAlias alias="User" type="cn.bisoft.java.webapp.pojo.User" /><select id="getUsers" parameterparameterparameterparameterparametername="code">package cn.bisoft.java.webapp.dao.impl;import java.sql.SQLException;import java.util.List;import cn.bisoft.java.webapp.dao.UserDAO;import cn.bisoft.java.webapp.pojo.User;import cn.bisoft.java.webapp.util.IbatisUtil;public class UserDAOImpl implements UserDAO {public void add(User user) {try {IbatisUtil.getClient().update("insertUser", user);} catch (SQLException e) {e.printStackTrace();}}public void delete(Integer id) {try {IbatisUtil.getClient().delete("deleteUser", id);} catch (SQLException e) {e.printStackTrace();}}public void update(User user) {try {IbatisUtil.getClient().update("updateUser", user);} catch (SQLException e) {e.printStackTrace();}}@SuppressWarnings("unchecked")public List<User> queryAll() {List<User> userList = null;try {userList = IbatisUtil.getClient().queryForList("getUsers");} catch (SQLException e) {e.printStackTrace();}for(User user : userList){System.out.println(user);}return userList;}@SuppressWarnings("unchecked")public List<User> queryByName(String name) {List<User> userList = null;try {userList = IbatisUtil.getClient().queryForList("queryUserByCondition", name);} catch (SQLException e) {e.printStackTrace();}for(User user : userList){System.out.println(user);}return userList;}public User query(Integer id) {User user = null;try {user = (User) IbatisUtil.getClient().queryForObject("getUsers", id);} catch (SQLException e) {e.printStackTrace();}System.out.println(user);return user;}}



User.java

package cn.bisoft.java.webapp.pojo;public class User {private Integer id;private String name;private Integer sex;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getSex() {return sex;}public void setSex(Integer sex) {this.sex = sex;}@Overridepublic String toString() {return "id=" + id + " name=" + name + " sex=" + sex;}}











热点排行