iBatis2 实现 增 删 改 查

在最近两年项目开发中 一直都是使用hibernate做数据持久层 我个人练习是使用了iBatis2 灵活的sql 半自动化的ORM 下面是我作为一名初学iBatis的源代码

sqlConfig.xml 配置iBatis访问数据库

<?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>
	<transactionManager type="JDBC">
		<dataSource type="SIMPLE">
			<property name="JDBC.Driver" value="com.mysql.jdbc.Driver" />
	<property name="JDBC.ConnectionURL"
value="jdbc:mysql://192.168.0.250:3306/common" />
			<property name="JDBC.Username" value="admin" />
			<property name="JDBC.Password" value="admin" />
		</dataSource>
	</transactionManager>
	<sqlMap resource="com/ibatisdemo/user.xml" />
</sqlMapConfig>

user.xml iBatis 配置SQL

<?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="User" type="com.ibatisdemo.ShnUsers" />

	<!-- 无参数的查询 -->
	<select id="queryUser" resultClass="User">
		select * from shn_users
	</select>

	<!-- 有参数的查询 -->
	<select id="queryUserByUserid" resultClass="User"
		parameterClass="int">
		select * from shn_users where userid=#userid#
	</select>


	<!-- 插入数据 -->
	<insert id="insertUser" parameterClass="User">
		insert into
		shn_users(username,passwd,email,createtime,isban,lastupdate
,registerip,userstate)
values(#username#,#passwd#,#email#,#createtime#,#isban#,#lastupdate#
,#registerip#,#userstate#)
	</insert>


    <!-- 删除数据 -->
	<delete id="deleteUser" parameterClass="User">
		delete from shn_users where userid=#userid#
	</delete>
	
	<!-- 更新数据 -->
	<update id="updateUser" parameterClass="User">
		update shn_users set username=#username# where userid=#userid#
	</update>
	
	
	<!-- 模糊查询 -->
	<select id="likeUser" parameterClass="String" resultClass="User">
	select * from shn_users where username like '%$userid$%'
	</select>
	
</sqlMap>

类文件

package com.ibatisdemo;

import java.util.Date;
import java.util.HashSet;
import java.util.Set;

/**
 * ShnUsers entity.
 * 
 * @author MyEclipse Persistence Tools
 */

public class ShnUsers implements java.io.Serializable {

	// Fields

	/**
	 * 
	 */
	private static final long serialVersionUID = 6346476404799232913L;
	private Integer userid;
	private String username;
	private String passwd;
	private String email;
	private Date createtime;
	private Integer isban;
	private Date lastupdate;
	private String registerip;
	private String userstate;
	private String grade;
	private Integer score;
	private Integer rescore;
	private String life;
	private String question;
	private String answer;

}

测试类调用 iBatis方法

public class test {

	public static void main(String[] args) {
		SqlMapClient sqlMapClient = null;
		try {

			Reader reader = com.ibatis.common.resources.Resources
					.getResourceAsReader("com/ibatisdemo/sqlConfig.xml");
			sqlMapClient = com.ibatis.sqlmap.client.SqlMapClientBuilder
					.buildSqlMapClient(reader);
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
//			 //无参数的查询
//			 List<ShnUsers> userlist = sqlMapClient.queryForList("queryUser");
//			 for (ShnUsers user : userlist) {
//			 System.out.print(user.getUsername()+"\n");
//			 System.out.print(user.getPasswd()+"\n");
//			 System.out.print(user.getAnswer()+"\n");
//			 }
//			// 有参数的查询
//			ShnUsers us = (ShnUsers) sqlMapClient.queryForObject(
//					"queryUserByUserid", 2);
//			System.out.print(us.getUsername() + "\n");
//			System.out.print(us.getPasswd() + "\n");
//			System.out.print(us.getAnswer() + "\n");

//			//添加数据
//			ShnUsers users=new ShnUsers();
//			users.setAnswer("");
//			users.setCreatetime(new Date());
//			users.setEmail("");
//			users.setUsername("");
//			users.setPasswd("");
//			users.setLife("");
//			users.setScore(123);
//			sqlMapClient.insert("insertUser", users);
			
			
//			//删除数据
//			ShnUsers userds=new ShnUsers();
//			userds.setUserid(729);
//			sqlMapClient.delete("deleteUser", userds);
			
//			//更新数据
//			ShnUsers userus=new ShnUsers();
//			userus.setUsername("123456");
//			userus.setUserid(727);
//			sqlMapClient.delete("updateUser", userus);
			
			
			 //模糊查询
			 List<ShnUsers> userlist = sqlMapClient.queryForList("likeUser", "a");
			 for (ShnUsers user : userlist) {
			 System.out.print(user.getUsername()+"\n");
			 System.out.print(user.getPasswd()+"\n");
			 System.out.print(user.getAnswer()+"\n");
			 }
			 
			 
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

评论

5 楼 zzy7182 2011-06-30  

调用insert方法后，会返回生成的主键的值。

4 楼 donglangjohn 2011-06-10  

<p>Hi java_user</p>
<p> </p>
<p>我改用selectkey也不管用啊</p>
<p> </p>
<p>xml 部分</p>
<pre name="code" class="xml"> &lt;insert id="insertPersons"  parameterType="com.wayne.domain.Person"&gt;
&lt;selectKey keyProperty="id" order="AFTER" resultType="long"&gt;
select LAST_INSERT_ID() as id
&lt;/selectKey&gt;
INSERT INTO Person (name,address,age)
VALUES
&lt;foreach collection="<span style="color: #ff0000;">persons</span>" item="item" index="index"
separator=","&gt;
(#{item.name},#{item.address},#{item.age})
&lt;/foreach&gt;
&lt;/insert&gt;</pre>
<p> </p>
<p>code部分</p>
<pre name="code" class="java"> public void insertPersons(){
List&lt;Person&gt; persons = new ArrayList&lt;Person&gt;(10);
Person tmp= null;
for(int i=0;i&lt;10;i++){
tmp = new Person();
tmp.setName("a "+i);
tmp.setAddress("aa " + i);
tmp.setAge(i);
persons.add(tmp);
}
Map&lt;String,List&lt;Person&gt;&gt; map = new HashMap&lt;String, List&lt;Person&gt;&gt;();
map.put("<span style="color: #ff0000;">persons</span>", persons);

SqlSession session = MyFactory.sessionFactory().openSession();
PersonMapper personMapper = session.getMapper(PersonMapper.class);

personMapper.insertPersons(map);
session.commit();
for (Person person : persons) {
System.out.println(person.getId());
}
}</pre>
<p> </p>
<p>现在能确保batch insert是ok的，但是key value没有返回到person.id里</p>
<p> 特意debug后发现key value实际已经由mybatis获取到,但是并未set到person.id里  @_@</p>

3 楼 java_user 2011-06-09  

donglangjohn 写道

请问LZ怎么从batch insert 里获取DB自动产生的ID值啊

详细的情况请看：

http://www.iteye.com/problems/66512

使用selectKey标签

2 楼 donglangjohn 2011-06-09  

请问LZ怎么从batch insert 里获取DB自动产生的ID值啊

详细的情况请看：

http://www.iteye.com/problems/66512

1 楼 kkvsyy 2011-06-02  

<!-- 无参数的查询 -->  
9.    <select id="queryUser" resultClass="User">  
10.        select * from shn_users  
11.    </select>  
12. 
13.    <!-- 有参数的查询 -->  
14.    <select id="queryUserByUserid" resultClass="User" 
15.        parameterClass="int">  
16.        select * from shn_users where userid=#userid#  
17.    </select>  
<!-- 模糊查询 -->  
42.    <select id="likeUser" parameterClass="String" resultClass="User">  
43.    select * from shn_users where username like '%$userid$%' 
44.    </select>  
你这三条语句　可以折成一条　ibatis是动态的