especialjie 2013-03-19
iBatis 简介:
iBatis 是apache 的一个开源项目,一个O/R Mapping 解决方案,iBatis 最大的特点就是小巧,上手很快。如果不需要太多复杂的功能,iBatis 是能够满足你的要求又足够灵活的最简单的解决方案,现在的iBatis 已经改名为Mybatis 了。
iBatis 让你能够更好的在JAVA应用中设计和实现实体层。这个框架有两个主要的组成部分,一个是SQL Maps,另一个是Data Access Objects。
SQL Maps:
Sql Maps是这个框架中最激动人心的部分,它是整个iBATIS Database Layer的核心价值所在。通过使用Sql Maps你可以显著的节约数据库操作的代码量。SQL Maps使用一个简单的XML文件来实现从javaBean到SQL statements的映射。跟其他的框架或者对象映射工具相比,SQL Maps最大的优势是简单。它需要学习的东西很少,在连接表或复杂查询时也不需要复杂的scheme(怎么翻complex scheme?),使用SQL Maps, 你可以自由的使用SQL语句。
Data Access Objects (DAO)
当我们开发灵活的JAVA应用时,有一个好主意就是通过一个通用API层把实体操作的细节封装起来。Data Access Objects允许你通过一个简单接口的来操作数据,从而隐藏了实现的细节。使用DAO,你可以动态配置你的应用程序来访问不同的实体存储机制。如果你有 一个复杂的应用需要用到几个不同的数据库,DAO可以让你建立一个一致的API,让系统的其他部分来调用。
工作流程:
SQL Maps提供一个简单的框架,通过XML描述来映射JAVABeans,MAP implementations甚至原始类型的包装(String,Integer等)到JDBC PreparedStatement。想法很简单,基本的步骤如下:
1) 提供一个对象作为参数(either a JavaBean, Map or primitive wrapper),The parameter object will be used setting input values in an update statement, or query values in a where clause (etc.).(感觉不译为好,你说呢?)
2) 执行这个映射的statement,这一步是魔术发生的地方。SQL Maps框架将建立一个PreparedStatement实例(instance),使用前面提供的对象的参数,执行statement,然后从ResultSet中建立一个返回对象。
3) 如果是Update,则返回有多少行修改了,如果是查询,则返回一个对象或者对象的集合。跟参数一样,返回对象也可以是一个JAVABEAN,MAP或者一个primitive type wrapper。
官网为:http://www.mybatis.org/
Demo :
下面是我的工程图样:
1.建立MyConst.java,其主要用来编写静态变量
package com.tinytinysun.consts; public final class MyConst { public static final int int_value0 = 0; public static final int int_value1 = 1; public static final String str_space = ""; public static final String str_space_1 = " "; }
2.建立Student.java,这个是模型文件,数据库存在和它对应的表
说明:由于待会需要输出数据库表的信息,所以在这里重写了toString()这个方法
package com.tinytinysun.model; import com.tinytinysun.consts.MyConst; public class Student { private int id = MyConst.int_value0; private String name = MyConst.str_space; private String address = MyConst.str_space; public Student() { } 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 getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { StringBuffer buffer = new StringBuffer(); buffer.append("id:"); buffer.append(id); buffer.append(MyConst.str_space_1); buffer.append(MyConst.str_space_1); buffer.append("name:"); buffer.append(name); buffer.append(MyConst.str_space_1); buffer.append(MyConst.str_space_1); buffer.append("address:"); buffer.append(address); return buffer.toString(); } }
3.建立StudentDao.java这个接口,用于声明对数据库操作的方法
package com.tinytinysun.dao; import java.util.List; import com.tinytinysun.model.Student; public interface StudentDao { //通过ID查询数据库 public Student selectStudentById(int id); //查询数据库Student这个表的所有数据 public List<Student> selectAllStudent(); //增加一条记录 public int addStudent(Student student); //通过ID删除某条记录 public int delectStudentById(int id); //更新一条记录 public int updateStudent(Student student); }
4.建立StudentDaoImpl.java,该类为上3接口的实现类
package com.tinytinysun.dao.impl; import java.sql.SQLException; import java.util.List; import com.ibatis.sqlmap.client.SqlMapClient; import com.tinytinysun.consts.MyConst; import com.tinytinysun.dao.StudentDao; import com.tinytinysun.dbConfig.MySqlConfig; import com.tinytinysun.model.Student; public class StudentDaoImpl implements StudentDao{ SqlMapClient client = MySqlConfig.getSqlMapClient(); @Override public int addStudent(Student student) { System.out.println("插入数据"); Object oRows = null; Integer iRows = null; int rows = MyConst.int_value0; try { oRows = client.insert("insertStudent", student); } catch (SQLException e) { e.printStackTrace(); } if(oRows == null){ return MyConst.int_value0; }else{ iRows = (Integer)oRows; rows = iRows.intValue(); System.out.println("成功插入 " + rows + " 条数据"); return rows; } } @Override public int delectStudentById(int id) { System.out.println("删除数据"); int rows = MyConst.int_value0; try { rows = client.delete("delectStudentById", id); } catch (SQLException e) { e.printStackTrace(); } System.out.println("成功删除 " + rows + " 条数据"); return rows; } @Override public List<Student> selectAllStudent() { System.out.println("查询所有数据"); List<Student> list = null; try { list = client.queryForList("getStudent"); } catch (SQLException e) { e.printStackTrace(); } return list; } @Override public Student selectStudentById(int id) { System.out.println("通过ID查询数据"); Student student = null; try { student = (Student)client.queryForObject("selectStudentById", id); } catch (SQLException e) { e.printStackTrace(); } return student; } @Override public int updateStudent(Student student) { System.out.println("更新数据"); int rows = MyConst.int_value0; try { rows = client.update("updateStudent", student); } catch (SQLException e) { e.printStackTrace(); } System.out.println("成功更新数据 " + rows + " 条数据"); return rows; } }
5.建立SqlMapConfig.properties文件,主要用于存储数据库的配置信息
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test username=tinytinysun password=123456
6.建立SqlMapConfig.xml文件,它是总配置文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <properties resource="com/tinytinysun/dbConfig/SqlMapConfig.properties" /> <transactionManager type="JDBC"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="${driver}" /> <property name="JDBC.ConnectionURL" value="${url}" /> <property name="JDBC.Username" value="${username}" /> <property name="JDBC.Password" value="${password}" /> </dataSource> </transactionManager> <sqlMap resource="com/tinytinysun/model/Student.xml"/> </sqlMapConfig>
7.建立MySqlConfig.java
package com.tinytinysun.dbConfig; 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 MySqlConfig { private static SqlMapClient client ; static { String resource = "com/tinytinysun/dbConfig/SqlMapConfig.xml"; try { Reader reader = Resources.getResourceAsReader(resource); client = SqlMapClientBuilder.buildSqlMapClient(reader); reader.close(); } catch (IOException e) { e.printStackTrace(); } } public static SqlMapClient getSqlMapClient(){ return client; } }
8.建立Student.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="Student"> <typeAlias alias="student" type="com.tinytinysun.model.Student" /> <select id="getStudent" resultClass="student"> select * from Student </select> <select id="selectStudentById" parameterClass="int" resultClass="student"> select * from Student where id =#id# </select> <insert id="insertStudent" parameterClass="student"> insert into Student(id,name,address) value(#id#,#name#,#address#) </insert> <delete id="delectStudentById" parameterClass="int"> delete from Student where id = #id# </delete> <update id="updateStudent" parameterClass="student"> update Student set name=#name# ,address=#address# where id = #id# </update> </sqlMap>
9.建立MyTest.java测试文件
package com.tinytinysun.test; import java.util.List; import com.tinytinysun.consts.MyConst; import com.tinytinysun.dao.StudentDao; import com.tinytinysun.dao.impl.StudentDaoImpl; import com.tinytinysun.model.Student; public class MyTest { public static void main(String[] args) { Student student = null; StudentDao studentDao = new StudentDaoImpl(); int flg = MyConst.int_value0; // 查询所有的数据 List<Student> studentList = studentDao.selectAllStudent(); if (studentList != null) { for (Student tempStudent : studentList) { System.out.println(tempStudent.toString()); if(tempStudent.getId() > flg){ flg = tempStudent.getId(); } } } System.out.println(); // 通过ID查找数据 student = studentDao.selectStudentById(1); System.out.println(student); System.out.println(); // 插入一条数据 student = new Student(); student.setId(flg + MyConst.int_value1); student.setName("name" + (flg + MyConst.int_value1)); student.setAddress("address" + (flg + MyConst.int_value1)); studentDao.addStudent(student); System.out.println(); if(flg == MyConst.int_value0){ student.setId(flg + MyConst.int_value1 + MyConst.int_value1); studentDao.addStudent(student); System.out.println(); } // 删除一条数据 if(flg != MyConst.int_value0){ studentDao.delectStudentById(flg); System.out.println(); } // 更新数据 student.setName("uName" + (flg + MyConst.int_value1)); student.setName("uAddress" + (flg + MyConst.int_value1)); studentDao.updateStudent(student); } }
10.由于我使用了log4j这个插件,所以加入log4j.properties这个配置文件
log4j.rootLogger=DEBUG, stdout, fileout log4j.logger.com.fiscal = DEBUG log4j.logger.com.system = DEBUG log4j.logger.com.ibatis = DEBUG log4j.logger.com.ibatis.common.jdbc.SimpleDataSource = DEBUG log4j.logger.com.ibatis.common.jdbc.ScriptRunner = DEBUG log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate = DEBUG log4j.logger.java.sql.Connection = DEBUG log4j.logger.java.sql.ResultSet = DEBUG log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.fileout=org.apache.log4j.RollingFileAppender log4j.appender.fileout.File=C\:\\ibatis.log log4j.appender.fileout.MaxFileSize=10000KB log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=[%-5p] %d{yyyy-MM-dd HH:mm:ss} :%m%n log4j.appender.fileout.layout=org.apache.log4j.PatternLayout log4j.appender.fileout.layout.ConversionPattern=[%-5p]_%d{yyyy-MM-dd HH\:mm\:ss} \:%m%n
最后开始测试了:
查询所有数据 [DEBUG] 2013-03-19 23:30:02 :Created connection 17547166. [DEBUG] 2013-03-19 23:30:02 :{conn-100000} Connection [DEBUG] 2013-03-19 23:30:02 :{conn-100000} Preparing Statement: select * from Student [DEBUG] 2013-03-19 23:30:02 :{pstm-100001} Executing Statement: select * from Student [DEBUG] 2013-03-19 23:30:02 :{pstm-100001} Parameters: [] [DEBUG] 2013-03-19 23:30:02 :{pstm-100001} Types: [] [DEBUG] 2013-03-19 23:30:02 :{rset-100002} ResultSet [DEBUG] 2013-03-19 23:30:02 :Returned connection 17547166 to pool. 通过ID查询数据 [DEBUG] 2013-03-19 23:30:02 :Checked out connection 17547166 from pool. [DEBUG] 2013-03-19 23:30:02 :{conn-100003} Connection [DEBUG] 2013-03-19 23:30:02 :{conn-100003} Preparing Statement: select * from Student where id =? [DEBUG] 2013-03-19 23:30:02 :{pstm-100004} Executing Statement: select * from Student where id =? [DEBUG] 2013-03-19 23:30:02 :{pstm-100004} Parameters: [1] [DEBUG] 2013-03-19 23:30:02 :{pstm-100004} Types: [java.lang.Integer] [DEBUG] 2013-03-19 23:30:02 :{rset-100005} ResultSet [DEBUG] 2013-03-19 23:30:02 :Returned connection 17547166 to pool. null 插入数据 [DEBUG] 2013-03-19 23:30:02 :Checked out connection 17547166 from pool. [DEBUG] 2013-03-19 23:30:02 :{conn-100006} Connection [DEBUG] 2013-03-19 23:30:02 :{conn-100006} Preparing Statement: insert into Student(id,name,address) value(?,?,?) [DEBUG] 2013-03-19 23:30:02 :{pstm-100007} Executing Statement: insert into Student(id,name,address) value(?,?,?) [DEBUG] 2013-03-19 23:30:02 :{pstm-100007} Parameters: [1, name1, address1] [DEBUG] 2013-03-19 23:30:02 :{pstm-100007} Types: [java.lang.Integer, java.lang.String, java.lang.String] [DEBUG] 2013-03-19 23:30:02 :Returned connection 17547166 to pool. 插入数据 [DEBUG] 2013-03-19 23:30:02 :Checked out connection 17547166 from pool. [DEBUG] 2013-03-19 23:30:02 :{conn-100008} Connection [DEBUG] 2013-03-19 23:30:02 :{conn-100008} Preparing Statement: insert into Student(id,name,address) value(?,?,?) [DEBUG] 2013-03-19 23:30:02 :{pstm-100009} Executing Statement: insert into Student(id,name,address) value(?,?,?) [DEBUG] 2013-03-19 23:30:02 :{pstm-100009} Parameters: [2, name1, address1] [DEBUG] 2013-03-19 23:30:02 :{pstm-100009} Types: [java.lang.Integer, java.lang.String, java.lang.String] [DEBUG] 2013-03-19 23:30:02 :Returned connection 17547166 to pool. 更新数据 [DEBUG] 2013-03-19 23:30:02 :Checked out connection 17547166 from pool. [DEBUG] 2013-03-19 23:30:02 :{conn-100010} Connection [DEBUG] 2013-03-19 23:30:02 :{conn-100010} Preparing Statement: update Student set name=? ,address=? where id = ? [DEBUG] 2013-03-19 23:30:02 :{pstm-100011} Executing Statement: update Student set name=? ,address=? where id = ? [DEBUG] 2013-03-19 23:30:02 :{pstm-100011} Parameters: [uAddress1, address1, 2] [DEBUG] 2013-03-19 23:30:02 :{pstm-100011} Types: [java.lang.String, java.lang.String, java.lang.Integer] [DEBUG] 2013-03-19 23:30:02 :Returned connection 17547166 to pool. 成功更新数据 1 条数据
如:对于sql语句order by #user_id#,如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id,则解析成的sql为order by "id"。