使用mybatis访问mysql数据库之二

在使用mybatis访问mysql数据库的第一版中，我们需要在main代码中指定对应的SQL的ID和参数，这样就不太灵活了。下面，我们做一个新版的访问mysql数据库的程序。该版本使用了接口技术，将接口与对应的SQL的ID进行关联，在main代码中只需要使用接口中的方法就可以直接操作数据库，mybatis真是太好用了！

基础表的配置略。与《使用mybatis访问mysql数据库之一》中的表一致。

User.java的代码如下。各个字段需要与表中的字段名称一致。

package com.baby.javaStudy.mybatis.v2;

public class User {
	private int id;
	private int classification;
	private String name;
	private String address;
	private String remark;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getClassification() {
		return classification;
	}
	public void setClassification(int classification) {
		this.classification = classification;
	}
	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;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", classification=" + classification + ", name=" + name + ", address=" + address
				+ ", remark=" + remark + "]";
	}
}

对数据库的user表建立配置文件。这个文件中主要定义了SQL语句。对应的文件名称为User.xml，内容如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.baby.javaStudy.mybatis.v2.IUserOperation">
	<select id="selectByID" parameterType="int" resultType="User">
		select *
		from user where id = #{id}
	</select>

	<!-- 为了返回list 类型而定义的returnMap -->
	<resultMap type="User" id="UserList">
		<id column="id" property="id" />
		<result column="classification" property="classification" />
		<result column="name" property="name" />
		<result column="address" property="address" />
		<result column="remark" property="remark" />
	</resultMap>
	
	<!-- 返回list 的select 语句，注意 resultMap 的值是指向前面定义好的 -->
	<select id="selectByName" parameterType="string" resultMap="UserList">
		select * from user where name like #{name}
	</select>
	
	<!--执行增加操作的SQL语句。id和parameterType 分别与IUserOperation接口中的addUser方法的名字和 参数类型一致。以#{name}的形式引用Student参数 
		的name属性，MyBatis将使用反射读取Student参数 的此属性。#{name}中name大小写敏感。引用其他 的gender等属性与此一致。seGeneratedKeys设置 
		为"true"表明要MyBatis获取由数据库自动生成的主 键；keyProperty="id"指定把获取到的主键值注入 到Student的id属性 -->
	<insert id="add" parameterType="User" useGeneratedKeys="true"
		keyProperty="id">
		insert into user(classification,name,address,remark)
		values(#{classification},#{name},#{address},#{remark})
	</insert>

	<update id="updateRemark" parameterType="User">
		update user set remark=#{remark} where id=#{id}
	</update>

	<delete id="delete" parameterType="int">
		delete from user where id=#{id}
	</delete>
</mapper>

建立一个对user表操作的接口IUserOperation.java，接口中的方法的名称，需要与User.xml中对应的SQL的ID一样。内容如下：

package com.baby.javaStudy.mybatis.v2;

import java.util.List;

public interface IUserOperation {

	public User selectByID(int id);
	public List<User> selectByName(String name);
	public void add(User book);
	public void updateRemark(User book);
	public void delete(int id);
}

数据库访问测试程序：

package com.baby.javaStudy.mybatis.v2;


import java.io.Reader;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class UserTestV2 {

	public static void main(String[] args) {
		SqlSession session = null;

		try {
			Reader reader = Resources.getResourceAsReader("com/baby/javaStudy/mybatis/v2/Configuration.xml");
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
			session = sqlSessionFactory.openSession();
			
			IUserOperation userOperation = session.getMapper(IUserOperation.class);
			
			System.out.println("========selectByID() test========");
			User user = userOperation.selectByID(2);
			System.out.println(user);
			
			System.out.println("========selectByName() test========");
			List<User> users = userOperation.selectByName("%wang%");
			for(User user2 : users){
				System.out.println(user2);
			}
			
			System.out.println("========add() test========");
			User user3 = new User();
			user3.setClassification(1);
			user3.setName("baby");
			user3.setAddress("beijing");
			user3.setRemark("added from userTestv2.java");
			System.out.println("new user info is: "+user3);
			userOperation.add(user3);
			session.commit();
			
			System.out.println("========updateRemark() test========");
			User user4 = userOperation.selectByID(2);
			System.out.println("old user info is "+user4);
			user4.setRemark("modified from userTestV2.java");
			userOperation.updateRemark(user4);
			session.commit();
			user4 = userOperation.selectByID(2);
			System.out.println("new user info is "+user4);
			
			System.out.println("========delete() test========");
			User user5 = userOperation.selectByID(2);
			System.out.println("user need delete info is "+user5);
			userOperation.delete(user5.getId());
			session.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
	}

}

运行上面的程序，然后在数据库中查询对应的表，即可用看到程序对表中数据的增加或者修改。