使用ibatis完成持久化工作

环境:
        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() + " name:" + this.getName() + " author:"
                
+ this.getAuthor() + " price:" + this.getPrice() + " pub:"
                
+ 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"55);
            
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();
        }
    }
    
    
}
原文地址:https://www.cnblogs.com/xuzhenmin/p/3170886.html