How to use IN clause in iBatis

How to use IN clause in iBatis

1. Table Structure (Table Name: users)

CREATE TABLE users(
username varchar(15) NOT NULL,
name varchar(255) NOT NULL,
password varchar(15) NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (username))

2. Insert data in this table

3. DAO Methos to fetch username with IN clause
public void getAllUserByName() {
List users;
try {
List names = new ArrayList();
names.add("Bond");
names.add("Josef");
names.add("Robert");
names.add("abcd");

users = sqlMap.queryForList("getAllUserByName",names);
for (int i = 0; i < user =" (User)">NOTE: If you use IN clause then iBatis expects that either you will send List or array. For example you can use names as array like String names[] = {"Robert","Bush","abcd"};

4. Code in SQL config file

<resultMap id="UserMap" class="com.domain.User">
<result property="name" column="username" />
<result property="pass" column="password" />
</resultMap>

<select id="getAllUserByName" resultMap="UserMap">
SELECT username, password from users
WHERE username IN
<iterate open="(" close=")" conjunction=",">
#[]#
</iterate>
</select>

NOTE : In select tag, no need to use parameterClass, it will take either List or array as you send during call the sqlMap.queryForList("getAllUserByName",names);

:) :)

Create XSD file from XML file

There are many tools to generate XSD from XML file, but I am using here trang.

STEP 1: First download trang from here (choose trang-20030619.zip)
STEP 2: Unzip this file at any location say C:\jar\trang-20030619\trang-20030619
STEP 3: Create one xml file at any location say C:\workspaceAll\XSD\XMLTOXSD\src\StudentInfo.xml

StudentInfo.xml

<?xml version="1.0" encoding="UTF-8"?><StudentData xmlns="http://mycompany.com/hr/schemas;
<Student>
<RollNo>110</RollNo>
<FirstName>Ultimate</FirstName>
<LastName>Answer</LastName>
<ContactNo>9900990011</ContactNo>
</Student>
<Hostel>
<Name>Ganga</Name>
<Location>South Corner</Location>
<RoomNo>20</RoomNo>
</Hostel>
</StudentData>

STEP 4: Now use this command

C:\workspaceAll\XSD\XMLTOXSD\src>java -jar C:\jar\trang-20030619\trang-20030619\trang.jar StudentInfo.xml StudentRecord.xsd

STEP 5: Now you will get StudentRecord.xsd at C:\workspaceAll\XSD\XMLTOXSD\src location. And StudentRecord.xsd will like this

<?xml version="1.0" encoding="UTF-8"?><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" targetNamespace="http://mycompany.com/hr/schemas" xmlns:schemas="http://mycompany.com/hr/schemas;
<xs:element name="StudentData">
<xs:complexType>
<xs:sequence>
<xs:element ref="schemas:Student"/>
<xs:element ref="schemas:Hostel"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Student">
<xs:complexType>
<xs:sequence>
<xs:element ref="schemas:RollNo"/>
<xs:element ref="schemas:FirstName"/>
<xs:element ref="schemas:LastName"/>
<xs:element ref="schemas:ContactNo"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="RollNo" type="xs:integer"/>
<xs:element name="FirstName" type="xs:NCName"/>
<xs:element name="LastName" type="xs:NCName"/>
<xs:element name="ContactNo" type="xs:integer"/>
<xs:element name="Hostel">
<xs:complexType>
<xs:sequence>
<xs:element ref="schemas:Name"/>
<xs:element ref="schemas:Location"/>
<xs:element ref="schemas:RoomNo"/>
</xs:sequence>

</xs:complexType>
</xs:element> <xs:element name="Name" type="xs:NCName"/>
<xs:element name="Location" type="xs:string"/>
<xs:element name="RoomNo" type="xs:integer"/>
</xs:schema>

It is very easy :)

IMP: If you want to develop any XSD file, so it would be better idea that first write XML file then using trang utility create XSD file. For example, I have below xml (persons.xml)
<?xml version="1.0" ?>
<information>
<person id="1">
<name>Binod</name>
<age>24</age>
<gender>Male</gender>
</person>

<person id="2">
<name>Pramod</name>
<age>22</age>
<gender>Male</gender>
</person>

<person id="3">
<name>Swetha</name>
<age>19</age>
<gender>Female</gender>
</person>
</information>
Now use the trang command and get your XSD file. I kind suggestion, never dig your head to develop XSD file by using XSD tags. Always use trang command. :)
java -jar c:\jar\trang-20030619\trang-20030619\trang.jar persons.xml personsFormat.xsd
personsFormat.xsd

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="information">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="person"/>
</xs:sequence>
</xs:complexType>

</xs:element>
<xs:element name="person">
<xs:complexType>
<xs:sequence>
<xs:element ref="name"/>
<xs:element ref="age"/>
<xs:element ref="gender"/>
</xs:sequence>
<xs:attribute name="id" use="required" type="xs:integer"/>
</xs:complexType>
</xs:element>
<xs:element name="name" type="xs:NCName"/>
<xs:element name="age" type="xs:integer"/>
<xs:element name="gender" type="xs:NCName"/>
</xs:schema>

Validate XML file using XSD in Java

XSD stands for XML Schema Definition.
XSD can be used to express a set of rules to which an XML document must conform in order to be considered 'valid' according to that schema. It used to validate the xml file.

Here we have :
1.one xml file (holiday-request.xml),
2. one xsd file (hr.xsd) and
3. one jave file (ValidateXMLUsingXSD.java) to validate xml file using the xsd file.
4. You have to add two jar file in your project (jaxb1-impl.jar and dom4j-1.6.1.jar) that you can google and download.

holiday-request.xml
<?xml version="1.0" encoding="UTF-8"?>
<HolidayRequest xmlns="http://mycompany.com/hr/schemas;
<Holiday>
<StartDate>2006-07-03</StartDate>
<EndDate>2006-07-07</EndDate>
</Holiday>

<Employee>
<Number>42</Number>
<FirstName>Binod</FirstName>
<LastName>Suman</LastName>
</Employee>
</HolidayRequest>

hr.xsd
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
targetNamespace="http://mycompany.com/hr/schemas"
xmlns:schemas="http://mycompany.com/hr/schemas;
<xs:element name="HolidayRequest">
<xs:complexType>
<xs:sequence>
<xs:element ref="schemas:Holiday"/>
<xs:element ref="schemas:Employee"/>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Holiday">
<xs:complexType>
<xs:sequence>
<xs:element ref="schemas:StartDate"/>
<xs:element ref="schemas:EndDate"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="StartDate" type="xs:NMTOKEN"/>
<xs:element name="EndDate" type="xs:NMTOKEN"/>
<xs:element name="Employee"> <xs:complexType>
<xs:sequence>
<xs:element ref="schemas:Number"/>
<xs:element ref="schemas:FirstName"/>
<xs:element ref="schemas:LastName"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Number" type="xs:integer"/>
<xs:element name="FirstName" type="xs:NCName"/>
<xs:element name="LastName" type="xs:NCName"/>
</xs:schema>

ValidateXMLUsingXSD.java

import java.io.File;
import org.iso_relax.verifier.Schema;
import org.iso_relax.verifier.Verifier;
import org.iso_relax.verifier.VerifierFactory;
import org.iso_relax.verifier.VerifierHandler;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.io.SAXReader;
import org.dom4j.io.SAXWriter;
import org.xml.sax.ErrorHandler;
import org.xml.sax.SAXParseException;
public class ValidateXMLUsingXSD {
private String schemaURI;
private Document document;
public ValidateXMLUsingXSD(Document document, String schemaURI) {
this.schemaURI = schemaURI;
this.document = document;
}
public boolean validate() throws Exception {
VerifierFactory factory = new com.sun.msv.verifier.jarv.TheFactoryImpl();
Schema schema = factory.compileSchema(schemaURI);
Verifier verifier = schema.newVerifier();
verifier.setErrorHandler(new ErrorHandler() {
public void error(SAXParseException saxParseEx) {
System.out.println("ERROR :: ");
saxParseEx.printStackTrace();
}
public void fatalError(SAXParseException saxParseEx) {
System.out.println("FATAL ERROR :: ");
saxParseEx.printStackTrace();
}
public void warning(SAXParseException saxParseEx) {
System.out.println("WARNING :: ");
saxParseEx.printStackTrace();
}
});
VerifierHandler handler = verifier.getVerifierHandler();
SAXWriter writer = new SAXWriter(handler);
writer.write(document);
return handler.isValid();
}
public static void main(String []args) throws DocumentException{
SAXReader reader=new SAXReader();
Document document=reader.read(new File("src\\holiday-request.xml"));
ValidateXMLUsingXSD val=new ValidateXMLUsingXSD(document,"src\\hr.xsd");
try {
System.out.println("Result :: "+val.validate());
if(val.validate()){
System.out.println("XML IS COMPATIBLE WITH XSD SCHEMA");
}
} catch (Exception e) {
System.out.println("0");
e.printStackTrace();
}
}
}

Now, if you will run, then you will get "XML IS COMPATIBLE WITH XSD SCHEMA" output, if your xml is compatible with xsd otherwise you will get error output.
For test you can change any datatype in xml file and check the result.
So, simple ............. :)

java

How to put restriction in XSD file to validate XML

In XSD file you can put data restriction then XML should satisfy those restriction, otherwise XML would not get validated.
Suppose some number I want only from 100 to 500. Or some name I want only India, US, UK. In this situation We can put the restriction in XSD file.
I am giving one example below, as per the this XSD number can only accept from 100 (Inclusice) to upto 500 (exclusive) and name would accept only Binod, Pramod, Manish.
Using this below XSD you can check xml with give some thing other than restricted value.

hrrecord.xsd

<?xml version="1.0" encoding="UTF-8"?><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" targetNamespace="http://mycompany.com/hr/schemas" xmlns:schemas="http://mycompany.com/hr/schemas;
<xs:element name="HolidayRequest">
<xs:complexType>
<xs:sequence> <xs:element maxOccurs="unbounded" ref="schemas:Student"/>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Student">
<xs:complexType>
<xs:sequence>
<xs:element ref="schemas:Holiday"/>
<xs:element ref="schemas:Employee"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Holiday">
<xs:complexType>
<xs:sequence>
<xs:element ref="schemas:StartDate"/>
<xs:element ref="schemas:EndDate"/>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="StartDate" type="xs:NMTOKEN"/>
<xs:element name="EndDate" type="xs:NMTOKEN"/>
<xs:element name="Employee"> <xs:complexType>
<xs:sequence>
<xs:element ref="schemas:Number"/>
<xs:element ref="schemas:FirstName"/>
<xs:element ref="schemas:LastName"/> </xs:sequence> </xs:complexType> </xs:element>
<xs:element name="Number">
<xs:simpleType>
<xs:restriction base="xs:integer">
<xs:minInclusive value="100"></xs:minInclusive>
<xs:maxExclusive value="500"></xs:maxExclusive>
</xs:restriction>
</xs:simpleType>
</xs:element>

<xs:element name="FirstName">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="Binod"></xs:enumeration>
<xs:enumeration value="Pramod"></xs:enumeration>
<xs:enumeration value="Manish"></xs:enumeration>
</xs:restriction>
</xs:simpleType>
</xs:element>

<xs:element name="LastName" type="xs:NCName"/>
</xs:schema>

If you will give name other than specfied in XSD you will get this error
com.sun.msv.verifier.ValidityViolation: the value is not a member of the enumeration: ("Binod"/"Manish"/"Pramod")
at com.sun.msv.verifier.Verifier.onError(Verifier.java:367)

If you will give number more than or equal to 500 then you will get error
com.sun.msv.verifier.ValidityViolation: the value is out of the range (maxExclusive specifies 500).
at com.sun.msv.verifier.Verifier.onError(Verifier.java:367)

How to set wait in javascript, JavaScript Delay, JavaScript wait, JavaScript pause

Please visit to my other blog:

http://binodservlet.blogspot.com/2009/06/how-to-set-wait-in-javascript.html

Thanks,

Binod Suman