環境:
ibatis-2.3.4.726
使用ibatis2最小jar包配置
commons-collections-3.2.1.jar
commons-dbcp-1.4.jar
commons-pool-1.5.4.jar
ibatis-2.3.4.726.jar
數據庫驅動
mysql-connector-java-3.1.13-bin.jar
ibatis入門小程序
使用ibatis完成單張表的crud操作
在使用ibatis之前，我們需要明確表之間的關系，需要先創建數據庫和表及表之間的對應關系
這里先使用單張表來介紹ibatis的用法
創建book表
CREATE TABLE book
(
id int auto_increment primary key,
name varchar(200),
author varchar(200),
price int,
pub varchar(200)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
編寫對應book的實體類
package com.ibatis.model;
public class Book {
private int id;
private String name;
private String author;
private int price;
private String pub;
public Book() {
}
public Book(int id, String name, String author, int price, String pub) {
super();
this.id = id;
this.name = name;
this.author = author;
this.price = price;
this.pub = pub;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public String getPub() {
return pub;
}
public void setPub(String pub) {
this.pub = pub;
}
@Override
public String toString() {
return "id:" + this.getId() + "\tname:" + this.getName() + "\tauthor:"
+ this.getAuthor() + "\tprice:" + this.getPrice() + "\tpub:"
+ this.getPub();
}
}
在Book類的同目錄(包)下創建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 namespace="Book">
<typeAlias alias="Book" type="com.ibatis.model.Book"/>
<!-- 查詢所有 -->
<select id="selectAllBook" resultClass="Book">
select * from book order by price desc
</select>
<!-- 根據編號查詢 -->
<select id="queryBookById" parameterClass="java.lang.Integer" resultClass="Book">
select * from book where id=#id#
</select>
<!-- 根據書名稱模糊查詢 -->
<select id="queryBookLikeName" resultClass="Book" parameterClass="java.lang.String">
select * from book where name like '%$name$%'
</select>
<!-- 模糊查詢 -->
<select id="pageQueryBook" resultClass="Book">
select * from book
</select>
<!-- 添加 -->
<insert id="insertBook" parameterClass="Book">
insert into book (name,author,price,pub) values(#name#,#author#,#price#,#pub#)
</insert>
<!-- 刪除 -->
<delete id="deleteBook" parameterClass="java.lang.Integer">
delete from book where id=#id#
</delete>
<!-- 修改 -->
<update id="updateBook" parameterClass="Book">
update book set name=#name#,author=#author#,price=price where id=#id#
</update>
<!-- 動態查詢 查詢書名中含o的且作者姓名含李的 -->
<select id="dynaicQuery" parameterClass="Book" resultClass="Book">
select * from book
<dynamic prepend="WHERE">
<isNotEmpty prepend="AND" property="name">
(name like '%$name$%')
</isNotEmpty>
<isNotEmpty prepend="AND" property="author">
(author like '%$author$%')
</isNotEmpty>
<isNotEmpty prepend="AND" property="price">
(price != #price#)
</isNotEmpty>
</dynamic>
</select>
</sqlMap>
在classpath目錄下一次添加ibatis.properties、SqlMapConfig.xml
ibatis.properties
JDBC.Driver=com.mysql.jdbc.Driver
JDBC.ConnectionURL=jdbc:mysql://localhost:3306/ibatis
JDBC.Username=root
JDBC.Password=root
SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
<properties resource="ibatis.properties"/>
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
maxRequests="10"
maxSessions="5"
useStatementNamespaces="false"
maxTransactions="5"
errorTracingEnabled="true"
/>
<transactionManager type="JDBC" commitRequired="false">
<dataSource type="DBCP">
<property name="JDBC.Driver" value="${JDBC.Driver}"/>
<property name="JDBC.ConnectionURL" value="${JDBC.ConnectionURL}"/>
<property name="JDBC.Username" value="${JDBC.Username}"/>
<property name="JDBC.Password" value="${JDBC.Password}"/>
<property name="Pool.MaximumActiveConnections" value="25"/>
<property name="Pool.MaximumIdleConnections" value="5"/>
<property name="Pool.MaximumCheckoutTime" value="12000"/>
<property name="Pool.TimeToWait" value="500"/>
</dataSource>
</transactionManager>
<sqlMap resource="com/ibatis/model/Book.xml"/>
</sqlMapConfig>
編寫ibatis工具類
package com.ibatis.util;
import java.io.IOException;
import java.io.Reader;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class IbatisUtil {
private static SqlMapClient client;
static{
Reader reader = null;
try {
reader = Resources.getResourceAsReader("SqlMapConfig.xml");
} catch (IOException e) {
e.printStackTrace();
}
client=SqlMapClientBuilder.buildSqlMapClient(reader);
}
public static SqlMapClient getClient() {
return client;
}
}
編寫BookDAO
package com.ibatis.dao;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;
import com.ibatis.model.Book;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.util.IbatisUtil;
public class BookDAO {
/**
* 查詢所有
*/
@SuppressWarnings("unchecked")
@Test
public void queryAllBook()
{
SqlMapClient client=IbatisUtil.getClient();
try {
List<Book> list=client.queryForList("selectAllBook");
for (Book book : list) {
System.out.println(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 按Id查詢
*/
@SuppressWarnings("unchecked")
@Test
public void queryBookById(){
SqlMapClient client=IbatisUtil.getClient();
try {
List<Book> list=client.queryForList("queryBookById", 2);
for (Book book : list) {
System.out.println(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 根據屬名稱模糊查詢
*/
@SuppressWarnings("unchecked")
@Test
public void queryBookLikeName()
{
SqlMapClient client=IbatisUtil.getClient();
try {
List<Book> list=client.queryForList("queryBookLikeName","ext");
for (Book book : list) {
System.out.println(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 分頁查詢
*/
@SuppressWarnings("unchecked")
@Test
public void pageQueryBook()
{
SqlMapClient client=IbatisUtil.getClient();
try {
List<Book> list=client.queryForList("pageQueryBook", 5, 5);
for (Book book : list) {
System.out.println(book);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 添加
*/
@Test
public void insertBook()
{
SqlMapClient client=IbatisUtil.getClient();
try {
client.startTransaction();
Book book=new Book(0,"oracle dba 入門手冊","馮靖",108,"電子工業出版社");
client.insert("insertBook", book);
client.commitTransaction();
} catch (SQLException e) {
e.printStackTrace();
try {
client.endTransaction();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* 刪除
*/
@Test
public void deleteBook(){
SqlMapClient client=IbatisUtil.getClient();
try {
client.startTransaction();
client.delete("deleteBook", 91);
client.commitTransaction();
} catch (SQLException e) {
e.printStackTrace();
try {
client.endTransaction();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* 修改
*/
@Test
public void updateBook(){
SqlMapClient client=IbatisUtil.getClient();
try {
client.startTransaction();
Book book=(Book) client.queryForObject("queryBookById",3);
book.setName("oracle入門診斷實例手冊");
book.setAuthor("蓋國強");
book.setPrice(108);
client.update("updateBook", book);
client.commitTransaction();
} catch (SQLException e) {
e.printStackTrace();
try {
client.endTransaction();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* 根據書名以及(AND)作者信息動態查詢
* 查詢書名是包含o以及作者姓名中含強的書籍
*/
@SuppressWarnings("unchecked")
@Test
public void dynaicQuery(){
SqlMapClient client=IbatisUtil.getClient();
Book book=new Book();
book.setName("o");
book.setAuthor("李");
book.setPrice(55);
try {
List<Book> list=client.queryForList("dynaicQuery", book);
for (Book books : list) {
System.out.println(books);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
over!到此ibatis但張表的crud結束
任何疑問QQ：184675420
sxyx2008@163.com
下篇ibatis的多對一雙向關聯