OOP programming iBatis study Note 3 2 tables or multi-Table Association queries

Common programming requires multi-table joint query. This example shows how to associate two tables for query. Multi-table can be simulated in this example.

Note. java

 

Public class Note {

Private int sid = 0;

Private String sname = null;

Private String major = null;

Private Date birth = null;

Private int book_oid;
Public int getBook_oid (){
Return book_oid;
}

Public void setBook_oid (int bookOid ){
Book_oid = bookOid;
}

Public int getSid (){
Return sid;
}

Public void setSid (int sid ){
This. sid = sid;
}

Public String getSname (){
Return sname;
}

Public void setSname (String sname ){
This. sname = sname;
}

Public String getMajor (){
Return major;
}

Public void setMajor (String major ){
This. major = major;
}

Public Date getBirth (){
Return birth;
}

Public void setBirth (Date birth ){
This. birth = birth;
}
}

 

 

 

Book. java

 

Package com. it;
Import java. util. List;

Public class Book {
Private int oid;
Private String name;
Private List notes;

Public int getOid (){
Return oid;
}

Public void setOid (int oid ){
This. oid = oid;
}

Public String getName (){
Return name;
}

Public void setName (String name ){
This. name = name;
}

Public List getNotes (){
Return notes;
}

Public void setNotes (List notes ){
This. notes = notes;
}
}

 

 

 

Book. xml

 

<? Xml version = "1.0" encoding = "UTF-8"?>
<! DOCTYPE sqlMap PUBLIC "-// ibatis.apache.org//DTD SQL Map 2.0 // EN"

Http://ibatis.apache.org/dtd/sql-map-2.dtd>

<SqlMap>

<TypeAlias alias = "Book" type = "com. it. Book"/>
<ResultMap id = "BookResult" class = "Book">
<Result property = "oid" column = "oid"/>
<Result property = "name" column = "name"/>
<Result property = "notes" column = "oid" select = "geNotesByBookId"/>
</ResultMap>
<Select id = "selectAllBooks" resultMap = "BookResult">
<! [CDATA [
Select oid, B. name from book B
]>

</Select>
<Select id = "geNotesByBookId" parameterClass = "int" resultClass = "Note">

<! [CDATA [
Select u. book_oid, u. sname from note u where u. book_oid = # sid #
]>
</Select>
</SqlMap>

 

Note. xml

 

<? Xml version = "1.0" encoding = "UTF-8"?>

<! DOCTYPE sqlMap PUBLIC "-// ibatis.apache.org//DTD SQL Map 2.0 // EN"

Http://ibatis.apache.org/dtd/sql-map-2.dtd>

<SqlMap>
<TypeAlias alias = "Note" type = "com. it. Note"/>
<Select id = "selectAllNote" resultClass = "Note">
Select *

From Note

</Select>

</SqlMap>

 

Test

 

Package com. it;
/***

* 2009-10-8

* 2 table join

**/
Import java. io. IOException;
Import java. io. Reader;
Import java. SQL. Date;
Import java. SQL. SQLException;
Import java. util. Iterator;
Import java. util. List;

Import com. ibatis. sqlmap. client. SqlMapClient;

Public class INoteDAOImpl implements INoteDao {
Private static SqlMapClient sqlMapClinet = null;

Static {

Reader reader;
Try {

String resource = "com/it/SqlMapConfig. xml ";

Reader = com. ibatis. common. resources. Resources. getResourceAsReader (resource); sqlMapClinet = com. ibatis. sqlmap. client. SqlMapClientBuilder. buildSqlMapClient (reader );

Reader. close ();

} Catch (IOException e ){

E. printStackTrace ();
}
}

Public static void main (String [] args ){
Try {

List <Book> books = sqlMapClinet. queryForList ("selectAllBooks ");
System. out. println (books. size ());
Book book = (Book) books. get (0 );
// System. out. println (book. getName ());
List notes = (List) book. getNotes ();

For (Iterator iterator = notes. iterator (); iterator. hasNext ();){

Note note = (Note) iterator. next ();
System. out. println (note. getBook_oid ());
System. out. println (note. getSname ());
}
} Catch (SQLException e ){

E. printStackTrace ();
}
}
}