-
Ibatis.Net Sql语句不能包含注释
by{ guangboo }, published {2010-03-16}, Tag { IBatis.net / }ibatis.net中定义的语句不能保护注释，如
select id, -- 编号 name, --姓名 age from lc_exp因为，ibatis.net在加载的时候不能识别换行，或者vs编辑器里面的换行和ibatis识别的换行不一样，使得结果为:
select id, -- 编号 name, --姓名 age from lc_exp
这样的SQL语句显然正确。 -
Ibatis.Net 操作数类型冲突: Nvarchar 与 Image 不兼容
by{ guangboo }, published {2009-08-28}, Tag { IBatis.net / }访问大字段是一件不可避免的事情，文档，图片等等。
ibatis.net操作大字段，如果该字段不为空，插入值是是正常的。当该条记录的大字段为空值时，就会报“给byte[]赋空值会提示：操作数类型冲突: nvarchar 与 image 不兼容 如何给空值插入到image字段”的错误。那么这个时候就要调整一下map了。
之前的map的insert是这样写的：
<insert id="InsertBIDAttachmentFile" parameterClass="BIDAttachmentFile">
INSERT INTO [dbo].[BID_AttachmentFile] (
[AttachmentID]
, [AttachmentName]
, [Description]
, [AttachmentFileContent]
) VALUES (
#AttachmentID#
, #AttachmentName#
, #Description#
, #AttachmentFileContent#）
</insert>这里不能将参数以类的（parameterClass="BIDAttachmentFile"）形式传入，而是要单独写一个parameterMaps来传参数，并且将Insert的value全部改成“？”符号。修改的map为：
<parameterMaps>
<parameterMap id="insertBIDAttachmentFile" class="BIDAttachmentFile">
<parameter property="AttachmentID" column="AttachmentID" dbType="VarChar" />
<parameter property="AttachmentName" column="AttachmentName" dbType="NVarChar" />
<parameter property="Description" column="Description" dbType="NVarChar" />
<parameter property="AttachmentFileContent" column="AttachmentFileContent" dbType="Image" />
</parameterMap><insert id="InsertBIDAttachmentFile" parameterMap="insertBIDAttachmentFile">
INSERT INTO [dbo].[BID_AttachmentFile] (
[AttachmentID]
, [AttachmentName]
, [Description]
, [AttachmentFileContent]
) VALUES (
?,?,?,?
)
</insert>原文：http://hi.baidu.com/hkzj/blog/item/333bca1352f2d92bdc54015d.html
-
Ibatis.Net Dynamic Sql
by{ guangboo }, published {2009-08-27}, Tag { IBatis.net / }Dynamic SQL
A very common problem with working directly with ADO is dynamic SQL. It is normally very difficult to work with SQL statements that change not only the values of parameters, but which parameters and columns are included at all. The typical solution is usually a mess of conditional if-else statements and horrid string concatenations. The desired result is often a query by example, where a query can be built to find objects that are similar to the example object. The iBATIS DataMapper API provides a relatively elegant solution that can be applied to any mapped statement element. Here is a simple example:
Example simple dynamic select sttatement, with two possible outcomes
<select id="dynamicGetAccountList" cacheModel="account-cache" parameterClass="Account" resultMap="account-result" > select * from ACCOUNT <isGreaterThan prepend="and" property="Id" compareValue="0"> where ACC_ID = #Id# </isGreaterThan> order by ACC_LAST_NAME </select>
In the above example, there are two possible statements that could be created depending on the state of the Id property of the parameter object. If the Id parameter is greater than 0, then the statement will be created as follows:
select * from ACCOUNT where ACC_ID = ?
Or if the Id parameter is 0 or less, the statement will look as follows.
select * from ACCOUNT
The immediate usefulness of this might not become apparent until a more complex situation is encountered. For example, the following is a somewhat more complex example.
Example Complex dynamic select statement, with 16 possible outcomes
<select id="dynamicGetAccountList" parameterClass="Account" resultMap="account-result" > select * from ACCOUNT <dynamic prepend="WHERE"> <isNotNull prepend="AND" property="FirstName"> ( ACC_FIRST_NAME = #FirstName# <isNotNull prepend="OR" property="LastName"> ACC_LAST_NAME = #LastName# </isNotNull> ) </isNotNull> <isNotNull prepend="AND" property="EmailAddress"> ACC_EMAIL like #EmailAddress# </isNotNull> <isGreaterThan prepend="AND" property="Id" compareValue="0"> ACC_ID = #Id# </isGreaterThan> </dynamic> order by ACC_LAST_NAME </select>
Depending on the situation, there could be as many as 16 different SQL queries generated from the above dynamic statement. To code the if-else structures and string concatenations could get quite messy and require hundreds of lines of code.
Using dynamic statements is as simple as inserting some conditional tags around the dynamic parts of your SQL. For example:
Example Creating a dynamic statement with conditional tags
<statement id="someName" parameterClass="Account" resultMap="account-result" > select * from ACCOUNT <dynamic prepend="where"> <isGreaterThan prepend="and" property="id" compareValue="0"> ACC_ID = #id# </isGreaterThan> <isNotNull prepend="and" property="lastName"> ACC_LAST_NAME = #lastName# </isNotNull> </dynamic> order by ACC_LAST_NAME </statement>
In the above statement, the <dynamic> element demarcates a section of the SQL that is dynamic. The dynamic element is optional and provides a way to manage a prepend in cases where the prepend ("WHERE") should not be included unless the contained conditions append to the statement. The statement section can contain any number of conditional elements (see below) that will determine whether the contained SQL code will be included in the statement. All of the conditional elements work based on the state of the parameter object passed into the query. Both the dynamic element and the conditional elements have a "prepend" attribute. The prepend attribute is a part of the code that is free to be overridden by the a parent element's prepend if necessary. In the above example the "where" prepend will override the first true conditional prepend. This is necessary to ensure that the SQL statement is built properly. For example, in the case of the first true condition, there is no need for the AND, and in fact it would break the statement. The following sections describe the various kinds of elements, including Binary Conditionals, Unary Conditionals, and Iterate.
3.9.1.Binary Conditional Elements
Binary conditional elements compare a property value to a static value or another property value. If the result is true, the body content is included in the SQL query.
3.9.1.1.Binary Conditional Attributes:
prepend – the overridable SQL part that will be prepended to the statement (optional) property – the property to be compared (required) compareProperty – the other property to be compared (required or compareValue) compareValue – the value to be compared (required or compareProperty) Table Binary conditional attributes
Element Description <isEqual> Checks the equality of a property and a value, or another property. Example Usage: <isEqual prepend="AND" property="status" compareValue="Y"> MARRIED = ‘TRUE' </isEqual><isNotEqual> Checks the inequality of a property and a value, or another property. Example Usage: <isNotEqual prepend="AND" property="status" compareValue="N"> MARRIED = ‘FALSE' </isNotEqual><isGreaterThan> Checks if a property is greater than a value or another property. Example Usage: <isGreaterThan prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘FALSE' </isGreaterThan><isGreaterEqual> Checks if a property is greater than or equal to a value or another property. Example Usage: <isGreaterEqual prepend="AND" property="shoeSize" compareValue="12"> BIGFOOT = ‘TRUE' </isGreaterEqual><isLessEqual> Checks if a property is less than or equal to a value or another property. Example Usage: <isLessEqual prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘TRUE' </isLessEqual>3.9.2.Bnary Conditional Elements
Unary conditional elements check the state of a property for a specific condition.
3.9.2.1.Bnary Conditional Attributes:
prepend – the overridable SQL part that will be prepended to the statement (optional) property – the property to be checked (required) Table?.8.Bnary conditional attributes
Element Description <isPropertyAvailable> Checks if a property is available (i.e is a property of the parameter object). Example Usage: <isPropertyAvailable property="id" > ACCOUNT_ID=#id# </isPropertyAvailable>
<isNotPropertyAvailable> Checks if a property is unavailable (i.e not a property of the parameter object). Example Usage: <isNotPropertyAvailable property="age" > STATUS='New' </isNotEmpty>
<isNull> Checks if a property is null. Example Usage: <isNull prepend="AND" property="order.id" > ACCOUNT.ACCOUNT_ID = ORDER.ACCOUNT_ID(+) </isNotEmpty>
<isNotNull> Checks if a property is not null. Example Usage: <isNotNull prepend="AND" property="order.id" > ORDER.ORDER_ID = #order.id# </isNotEmpty>
<isEmpty> Checks to see if the value of a Collection, String property is null or empty ("" or size() < 1). Example Usage: <isEmpty property="firstName" > LIMIT 0, 20 </isNotEmpty>
<isNotEmpty> Checks to see if the value of a Collection, String property is not null and not empty ("" or size() < 1). Example Usage: <isNotEmpty prepend="AND" property="firstName" > FIRST_NAME LIKE '%$FirstName$%' </isNotEmpty>
3.9.3.Parameter Present Elements
These elements check for parameter object existence.
3.9.3.1.Parameter Present Attributes:
prepend – the overridable SQL part that will be prepended to the statement (optional)
Table?.9.Testing to see if a parameter is present
Element Description <isParameterPresent> Checks to see if the parameter object is present (not null). <isParameterPresent prepend="AND"> EMPLOYEE_TYPE = #empType# </isParameterPresent>
<isNotParameterPresent> Checks to see if the parameter object is not present (null). Example Usage: <isNotParameterPresent prepend="AND"> EMPLOYEE_TYPE = ‘DEFAULT' </isNotParameterPresent>
3.9.4.Iterate Element
This tag will iterate over a collection and repeat the body content for each item in a List
3.9.4.1.Iterate Attributes:
prepend – the overridable SQL part that will be prepended to the statement (optional) property – a property of type IList that is to be iterated over (required) open – the string with which to open the entire block of iterations, useful for brackets (optional) close – the string with which to close the entire block of iterations, useful for brackets (optional) conjunction – the string to be applied in between each iteration, useful for AND and OR (optional) Table?.10.Creating a list of conditional clauses
Element Description <iterate> Iterates over a property that is of type IList Example Usage: <iterate prepend="AND" property="UserNameList" open="(" close=")" conjunction="OR"> username=#UserNameList[]# </iterate>Note: It is very important to include the square brackets[] at the end of the List property name when using the Iterate element. These brackets distinguish this object as an List to keep the parser from simply outputting the List as a string.3.9.5.Simple Dynamic SQL Elements
Despite the power of the full Dynamic Mapped Statement API discussed above, sometimes you just need a simple, small piece of your SQL to be dynamic. For this, SQL statements and statements can contain simple dynamic SQL elements to help implement dynamic order by clauses, dynamic select columns or pretty much any part of the SQL statement. The concept works much like inline parameter maps, but uses a slightly different syntax. Consider the following example:
Example Ddynamic element that changes the collating order
<statement id="getProduct" resultMap="get-product-result"> select * from PRODUCT order by $preferredOrder$ </statement>In the above example the preferredOrder dynamic element will be replaced by the value of the preferredOrder property of the parameter object (just like a parameter map). The difference is that this is a fundamental change to the SQL statement itself, which is much more serious than simply setting a parameter value. A mistake made in a Dynamic SQL Element can introduce security, performance and stability risks. Take care to do a lot of redundant checks to ensure that the simple dynamic SQL elements are being used appropriately. Also, be mindful of your design, as there is potential for database specifics to encroach on your business object model. For example, you may not want a column name intended for an order by clause to end up as a property in your business object, or as a field value on your server page.
Simple dynamic elements can be included within <statements> and come in handy when there is a need to modify the SQL statement itself. For example:
Example?.60. Ddynamic element that changes the comparison operator
<statement id="getProduct" resultMap="get-product-result"> SELECT * FROM PRODUCT <dynamic prepend="WHERE"> <isNotEmpty property="Description"> PRD_DESCRIPTION $operator$ #Description# </isNotEmpty> </dynamic> </statement>In the above example the operator property of the parameter object will be used to replace the $operator$ token. So if the operator property was equal to LIKE and the description property was equal to %dog%, then the SQL statement generated would be:
SELECT * FROM PRODUCT WHERE PRD_DESCRIPTION LIKE ‘%dog%' -
Ibatis.Net支持的数据类型
by{ guangboo }, published {2009-08-27}, Tag { IBatis.net / }Table supported Types for Parameter Maps and Result Maps (.NET)
CLR Type Object/Map Property Mapping Result Class/Parameter Class** Type Alias** System.ArrayList Yes Yes list System.Boolean Yes Yes Boolean, bool System.Byte Yes Yes Byte, byte System.Char Yes Yes Char, char System.DateTime Yes Yes dateTime, date System.Decimal Yes Yes Decimal, decimal System.Double Yes Yes Double, double System.Guid Yes Yes guid System.Hashtable Yes Yes map, hashmap, hashtable System.Int16 Yes Yes Int16, short, Short System.Int32 Yes Yes Int32, int, Int, integer, Integer System.Int64 Yes Yes Int64, long, Long System.SByte Yes Yes SByte, sbyte System.Single Yes Yes Float, float, Single, single System.String Yes Yes String, string System.TimeSpan Yes Yes N/A System.UInt16 Yes Yes Short, short System.UInt32 Yes Yes Uint, uint System.UInt64 Yes Yes Ulong, ulong Nullable<bool> Yes Yes bool? Nullable<byte> Yes Yes byte? Nullable<char> Yes Yes char? Nullable<DateTime> Yes Yes DateTime? Nullable<decimal> Yes Yes decimal? Nullable<double> Yes Yes double? Nullable<Int16> Yes Yes Int16? Nullable<Int32> Yes Yes Int32? Nullable<Int64> Yes Yes Int64? Nullable<SByte> Yes Yes SByte? Nullable<Single> Yes Yes Single? Nullable<UInt16> Yes Yes UInt16? Nullable<UInt32> Yes Yes UInt32? Nullable<UInt64> Yes Yes UInt64? Table supported DbTypes for Parameter Maps and Result Maps (.NET)
CLR Type iBATIS support SqlDbType OleDbType OdbcType OracleType Byte[] Yes Binary, Image, VarBinary Binary, VarBinary Binary, Image, VarBinary Raw Boolean, bool? Yes Bit Boolean Bit Byte Byte, byte? Yes TinyInt - TinyInt Byte DateTime, DateTime? Yes DateTime, SmallDateTime Date Date, DateTime, SmallDateTime, Time DateTime char, char? Yes Not supported Char Char Byte Decimal, decimal? Yes Decimal, Money, SmallMoney Decimal, Currency, Numeric Decimal, Numeric Number Double, double? Yes Float Double Double Double Guid, Guid? Yes UniqueIdentifier Guid UniqueIdentifier Raw Int16, Int16? Yes SmallInt SmallIInt SmallInt Int16 Int32, Int32? Yes Int Integer Int Int32 Int64, Int64? Yes BigInt BigInt BigInt Number Single, Single? Yes Real Single Real Float String Yes Char, Nchar, NVarchar, Text, VarChar Char, VarChar Char, NChar, NText, NVarChar, Text, VarChar NVarChar, VarChar TimeSpan No Not supported DBTime Time DateTime UInt16, UInt16? yes Int - - UInt16 UInt32, UInt32? yes Decimal - - UInt32 UInt64, UInt64? yes Decimal - - Number