ibatis2.3.4 学习笔记（CRUD)

SqlMap.properties: 数据库的配置信息

driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost/ibatis_db?characterEncoding\=UTF-8
username=root
password=

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="com/ethan/SqlMap.properties" />
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
maxRequests="128"
maxSessions="10"
maxTransactions="5"
useStatementNamespaces="false"
defaultStatementTimeout="5"
statementCachingEnabled="true"
classInfoCacheEnabled="true"
/>


<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}"/>
<property name="JDBC.DefaultAutoCommit" value="true" />
<property name="Pool.MaximumActiveConnections" value="10"/>
<property name="Pool.MaximumIdleConnections" value="5"/>
<property name="Pool.MaximumCheckoutTime" value="120000"/>
<property name="Pool.TimeToWait" value="500"/>
<property name="Pool.PingQuery" value="select 1 from ACCOUNT"/>
<property name="Pool.PingEnabled" value="false"/>
<property name="Pool.PingConnectionsOlderThan" value="1"/>
<property name="Pool.PingConnectionsNotUsedFor" value="1"/>
</dataSource>
</transactionManager>


<sqlMap resource="com/ethan/Student.xml" />
</sqlMapConfig>

数据库实体对象的映射：

Student.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>
	<typeAlias alias="Student" type="com.ethan.domain.Student"/>
	
	<select id="selectAllStudent" resultClass="Student">
		select * from student
	</select>
	
	<select id="selectStudentById"
		parameterClass="int" resultClass="Student">
		<!-- #sid#占位符 类型int -->
		select sid,sname,score,major,birth from student where sid=#sid#	
	</select>
	
	<insert id="insertStudent" 
		parameterClass="Student">
		<!-- 数据库中 sid设为主键，自增长 -->
		<selectKey resultClass="int" keyProperty="sid">  
	       select last_insert_id()
    	</selectKey>  
		insert into student(
			sname,
			major,
			score,
			birth
		)values(
			#sname#,#major#,#score#,#birth#
		)
	</insert>
	
	<delete id="deleteStudentById" parameterClass="int">
		delete 
		from student
		where sid=#sid#
	</delete>
	<!--student不区分大小写  -->
	<!-- Student.get#sname#()调用参数对象的相应方法赋值 -->
	<update id="updateStudentById" parameterClass="student">
		update student
		set sname=#sname#,
			major=#major#,
			score=#score#,
			birth=#birth#
		where sid=#sid#
	</update>
	
	<select id="selectStudentByName" parameterClass="String"
		resultClass="Student">
		select sid,sname,major,birth,score
		from student
		where sname like '%$sname$%'
	</select>
</sqlMap>

package com.ethan.domain;

import java.sql.Date;

public class Student {
	private int sid = 0;
	private String sname = null;
	private Date birth = null;
	private String major = null;
	private float score = 0;
	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	public float getScore() {
		return score;
	}
	public void setScore(float score) {
		this.score = score;
	}
	public String getMajor() {
		return major;
	}
	public void setMajor(String major) {
		this.major = major;
	}
	
	@Override
	public String toString() {
		return "sid="+sid+"\tsname="+sname+"\tmajor="+major+"\t"+"score="+score;
	}
	
	
}

package com.ethan.dao;

import java.util.List;

import com.ethan.domain.Student;

public interface IStudentDAO {
	public void addStudent(Student student);
	public void deleteStudentById(int id);
	public List<Student> queryAllStudent();
	public List<Student> queryStudentByName(String name);
	public Student queryStudentById(int id);
	public void updateStudentById(Student student);
	
}

package com.ethan.dao;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.List;

import com.ethan.domain.Student;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class IStudentDAOImpl implements IStudentDAO {
	
	private static SqlMapClient sqlMapClient = null;
	
	static {
		try {
			Reader reader = Resources.getResourceAsReader("com/ethan/SqlMapConfig.xml");
			sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
			reader.close();
		} catch (IOException e) {
			System.out.println("sqlMapClient-----------");
		}
	}
	@Override
	public void addStudent(Student student) {
		try {
			sqlMapClient.insert("insertStudent",student);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void deleteStudentById(int id) {
		try {
			sqlMapClient.delete("deleteStudentById", id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateStudentById(Student student) {
		try {
			System.out.println(sqlMapClient.update("updateStudentById",student));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<Student> queryAllStudent() {
		try {
			List<Student> studentList = sqlMapClient.queryForList("selectAllStudent");
			return studentList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public List<Student> queryStudentByName(String name) {
		List<Student> studentList = null;
		try {
			studentList = sqlMapClient.queryForList("selectStudentByName", name);
			return studentList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public Student queryStudentById(int id) {
		Student student = null;
		try {
			student = (Student) sqlMapClient.queryForObject("selectStudentById",id);
			return student;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}

package com.ethan.test;

import java.sql.Date;
import java.util.Calendar;

import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import com.ethan.dao.IStudentDAO;
import com.ethan.dao.IStudentDAOImpl;
import com.ethan.domain.Student;

public class StudentDAOTest {
	static IStudentDAO dao = null;
	
	@Before
	public void init() {
		dao = new IStudentDAOImpl();
	}
	@Test
	public void queryAll() {
		
		for(Student student:dao.queryAllStudent()) {
			System.out.println(student);
		}
	}
	
	@Test
	public void queryById() {
		
		System.out.println(dao.queryStudentById(1));
	}
	
	@Test
	public void insertStudent() {
		Student student = new Student();
		//  #sid#--->对应getSid()方法，占位符名字不是随意的，得有对应的get方法
		student.setSname("ccc");
		student.setMajor("文学");
		student.setBirth(Date.valueOf("2012-03-06"));
		student.setScore(99);
		
		dao.addStudent(student);
	}
	
	
	@Test
	public void deleteById() {
		
		dao.deleteStudentById(3);
	}
	
	@Test
	public void updateStudentById() {
		Student student = new Student();
		//  #sid#--->对应getSid()方法，占位符名字不是随意的，得有对应的get方法
		student.setSid(3);
		student.setSname("ccc");
		student.setMajor("文学2");
		student.setBirth(Date.valueOf("2012-03-06"));
		student.setScore(99);
		
		dao.updateStudentById(student);
	}
	
	/*
	 * 模糊查找
	 */
	@Test
	public void queryByName() {
		
		System.out.println(dao.queryStudentByName("c"));
	}
}