使用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;}@Overridepublic 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();}}}

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