mybatis实现简单的增删改查操作

（一）环境搭建

（1）导包，没有创建maven项目，需要手动导包，如下图所示

（2）debug日志打印，log4j.properties配置

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

（二）

（1）sqlMapConfig.xml配置

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

<configuration>
	<!-- 和spring整合后environments配置将废弃，spring中有JdbcTemplates等可以进行
数据库连接 -->
	<environments default="development">
		<environment id="development">
			<!-- 使用jdbc事务管理 -->
			<transactionManager type="JDBC"/>
			<!-- 数据库连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" 
				   value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8"/>
				<property name="username" value="root"/>
				<property name="password" value="123456"/>
			</dataSource>
			
		</environment>
	</environments>
<mappers>
<!-- sql包和sqlMapConfig.xml处于并列的位置，都在src下 -->
	<mapper resource="sqlmap/User.xml"/>
</mappers>
	
</configuration>

（2）POJO类书写，User.java

package cn.shu.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable{
	//实现序列化接口
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	private Integer id;
	private String username;
	private String sex;
	private Date birthday;
	private String address;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
				+ address + "]";
	}
	
	
}

(3)User.xml(Mapper.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">

<!-- 写sql语句
namespace 命名空间，区分以便于知道执行那个文件中的sql语句
user.findUserById
order.findUserById -->
<mapper namespace="test">
	<!-- 通过id查询一个用户 -->
	<select id="findUserById" parameterType="Integer" resultType="cn.shu.pojo.User">
	<!-- resultMap为手动映射，resultType为自动映射,自动映射要求数据库中的表字段和User类的字段一致
	否则必须进行手动映射.返回类型是全限定名称 
	如果返回类型写正确了，按住Ctrl可以进入User类中-->
		select * from user where id = #{v}
		<!-- #{}表示占位符,此时#{}中间可以随便写,parameterType 表示id类型 -->
	</select>
	
	<!-- 根据用户名称模糊查询用户列表 -->
	<!-- 
	#{}: select * from user where id =?   占位符   形态（？用字符串替代，类似）： ?=='呵呵'
	${}:select * from user where username like 字符串拼接    '%${}%'=>'%呵呵%'
	                                        若使用#{}  '%#{}%'=>'%'呵呵'%'  出现两层引号，语法错误
	
	 -->
	<select id="FindUserByUserName" parameterType="String" resultType="cn.shu.pojo.User">
	<!-- resultType指的是List<User>中的泛型User ,使用${}时，括号中只能填写value不能填写其他的-->
	select * from user where username like '%${value}%'
	<!-- 使用#{}写法，防止sql语句注入
	select * from user where username like "%"#{val}"%",此时{}内可以不固定写value
	 -->
	
	</select>
	
	<!-- 添加用户 -->
	<insert id="InsertUser" parameterType="cn.shu.pojo.User">
		<!-- 查询主键 ，LAST_INSERT_ID:最新保存的ID，MySQL提供的，在表中是主键自增的
		将最新插入语句形成的id赋给keyProperty，id类型为Integer
		，主键integer类型自增长，对数据库进行保存操作时是先将数据保存下来，再增长id值
		如果将id设置为varchar类型
		-->
		<selectKey keyProperty="id" resultType="Integer" order="AFTER">
			select LAST_INSERT_ID()
		</selectKey>
		insert into user(username,birthday,address,sex) 
		values (#{username},#{birthday},#{address},#{sex})
	</insert>
	
	<!-- 更新 -->
	<update id="updateUserById" parameterType="cn.shu.pojo.User">
		update user
		set username = #{username},sex=#{sex},birthday=#{birthday},address = #{address}
		where id =#{id}
	</update>
	
	<!-- 删除 -->
	
	<delete id="deleteUserById" parameterType="Integer">
		delete from user
		where id =#{haha}
	</delete>
	
</mapper>

（4）数据库表格创建，user表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', null, null, null);

（5）书写测试案例

package cn.shu.junit;

import java.io.InputStream;
import java.util.Date;
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;
import org.junit.Test;

import cn.shu.pojo.User;

public class MybatisFirstTest {
	//通过id查询一个用户
	@Test
	public void testMybatis() throws Exception{
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		//创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//执行sql语句(10取代占位符，形成完整sql语句，返回用户对象)
		User user = sqlSession.selectOne("test.findUserById", 10);
	    //返回一个对象，用selectOne
		System.out.println(user);//user已经具有了toString方法
	}
	
	//根据用户名称模糊查询用户列表
	@Test
	public void testFindUserByUserName() throws Exception{
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		//创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//执行sql语句
		List<User> users = sqlSession.selectList("test.FindUserByUserName", "五");
	    for(User u:users){
	    	System.out.println(u);
	    }
		
	}
	
	
	//添加用户
	@Test
	public void testInsertUser() throws Exception{
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		//创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//执行sql语句
		
		User u = new User();
		u.setUsername("stephen hong");
		u.setBirthday(new Date());
		u.setAddress("*");
		//u.setSex("men");
		u.setSex("男");
		int i = sqlSession.insert("test.InsertUser", u);
	    //返回的i为影响的行数
		//提交事务
		sqlSession.commit();
		//通过User配置文件中selectKey标签设置，可以获得主键自增长id的值
		System.out.println(u.getId());
	}
	
	//更新用户
	@Test
	public void testUpdateUserById() throws Exception{
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		
		//创建SqlSessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//执行sql语句
		
		User u = new User();
		u.setAddress("wuhan");
		u.setBirthday(new Date());
		u.setId(28);
		u.setSex("女");
		u.setUsername("sunshine");
		
		int i = sqlSession.update("test.updateUserById", u);
		sqlSession.commit();
	}
	
	
	//删除用户
		@Test
		public void testDelete() throws Exception{
			//加载核心配置文件
			String resource = "sqlMapConfig.xml";
			InputStream in = Resources.getResourceAsStream(resource);
			
			//创建SqlSessionFactory
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
			//创建SqlSession
			SqlSession sqlSession = sqlSessionFactory.openSession();
			//执行sql语句
			
			sqlSession.delete("test.deleteUserById", 27);
			
			
			sqlSession.commit();
		}

	
}

（三）遇到的问题

u.setSex("men");

建表时`sex` char(1) DEFAULT NULL COMMENT '性别',只有一位

改为u.setSex("男");即可

 

（四）数据库最终的显示结果

一顿操作学习下来，感觉和hibernate有几分相似，也有几分不同，后续学习会进行两者的对比思考