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();
		}
	}
}

<!-- 无参数的查询 -->  
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是动态的

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

详细的情况请看：

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

使用selectKey标签

Hi java_user

 

我改用selectkey也不管用啊

 

xml 部分

	<insert id="insertPersons"  parameterType="com.wayne.domain.Person">
		<selectKey keyProperty="id" order="AFTER" resultType="long">
		select LAST_INSERT_ID() as id
		</selectKey>
		INSERT INTO Person (name,address,age)
		VALUES
		<foreach collection="persons" item="item" index="index"
			separator=",">
			(#{item.name},#{item.address},#{item.age})
		</foreach>
	</insert>

 

code部分

	public void insertPersons(){
		List<Person> persons = new ArrayList<Person>(10);
		Person tmp= null; 
		for(int i=0;i<10;i++){
			tmp = new Person();
			tmp.setName("a "+i);
			tmp.setAddress("aa " + i);
			tmp.setAge(i);
			persons.add(tmp);
		}
		Map<String,List<Person>> map = new HashMap<String, List<Person>>();
		map.put("persons", 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());
		}
	}

 

现在能确保batch insert是ok的，但是key value没有返回到person.id里

特意debug后发现key value实际已经由mybatis获取到,但是并未set到person.id里  @_@

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

单独的可以用selectkey取，并且他也不是任何时候都可以获取的，批量的呢？？

可以指定pre或者post表示前生成还是后生成。

<insert id="registerGeneral" parameterType="User" useGeneratedKeys="true" keyProperty="id">