mybatis 简单CRUD

上图： 对象-关系图

随便画的，字段和表的对应关系！

User_ACCOUNT sql 脚本   我用的是mysql ，用别的稍微改一下就好了！

DROP DATABASE IF EXISTS mybatis;

CREATE DATABASE mybatis;

USE mybatis;

#
# Table structure for table 'user'
#

CREATE TABLE USER_ACCOUNT (
	USER_ID            INT(3) NOT NULL AUTO_INCREMENT,
	USER_NAME          VARCHAR(10) NOT NULL,
	USER_PASSWORD      VARCHAR(30) NOT NULL,
	USER_GROUP_NAME    VARCHAR(10),
	PRIMARY KEY        (USER_ID)
);


#
# Data for table 'user'
#

INSERT INTO USER_ACCOUNT (USER_NAME, USER_PASSWORD, USER_GROUP_NAME)
	VALUES ('LMEADORS', 'PICKLE', 'EMPLOYEE');
INSERT INTO USER_ACCOUNT (USER_NAME, USER_PASSWORD, USER_GROUP_NAME)
	VALUES ('JDOE', 'TEST', 'MANAGER');
INSERT INTO USER_ACCOUNT (USER_NAME, USER_PASSWORD, USER_GROUP_NAME)
	VALUES ('JACK', 'SER', 'EMPLOYEE');
INSERT INTO USER_ACCOUNT (USER_NAME, USER_PASSWORD, USER_GROUP_NAME)
	VALUES ('KETTY', 'TSS', 'BOSS');
INSERT INTO USER_ACCOUNT (USER_NAME, USER_PASSWORD, USER_GROUP_NAME)
	VALUES ('ROSE', 'PICK', 'MANAGER');
INSERT INTO USER_ACCOUNT (USER_NAME, USER_PASSWORD, USER_GROUP_NAME)
	VALUES ('JOHN', 'RUQI', 'EMPLOYEE');
	COMMIT;
	
#
# 显示表结构
#
	DESC USER_ACCOUNT;
	
#
# 显示插入的字段 前5条
#
	SELECT * FROM USER_ACCOUNT LIMIT 0 , 5;
#
# completed！
#

现在需要下载 mybatis的包 ，这里用的3.1.0

mybatis包下载地址

项目结构如下：

首先 根据对象-关系图 创建对象 User .java 

package mybatis.domain;

import java.io.Serializable;

public class User implements Serializable{
	
	private static final long serialVersionUID = 202125533615707097L;
	
	private int id;
	private String name;
	private String password;
        private String groupName;
    
       //省了 setter 和 getter 方法

}

UserMapper.java  这是一个接口 ，定义了对User对象的一些CRUD操作！ 接口的方法可以根据java5的注释方式，被解析成UserMapper的实现实例，或者利用配置文件

public interface UserMapper {
	
	void saveUser(User user);
	User getUserById(int id);
	List<User> getAllUser();
	void deleteUser(int id);
	void updateUser(User user);
}

UserMapper.xml

这个配置文件和UserMapper 中的方法是一一对应的 

<?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="mybatis.domain.UserMapper" >
		

	<!-- 这里使用了resultMap 这个是mybatis对关联映射的支持,如果字段名字和表中的名字不一样，需要转换一下-->
	<resultMap type="mybatis.domain.User" id="userResult" >
		<id column="USER_ID" property="id"/>
		<result column="USER_NAME" property="name" />
		<result column="USER_PASSWORD" property="password" />
		<result column="USER_GROUP_NAME" property="groupName" />
	</resultMap>
	

	<!--这里对应接口中的saveUser方法, parameterType 是输入的参数类型，#{}取给定参数的值，statementType prepared 代表使用preparedStatement

		执行语句，防止sql被恶意注入！

	 -->	
        <insert id="saveUser" parameterType="User" statementType="PREPARED">
                INSERT INTO USER_ACCOUNT (USER_NAME, USER_PASSWORD, USER_GROUP_NAME) 
                				  VALUES (#{name},#{password},#{groupName})
        </insert>

        <select id="getUserById" parameterType="java.lang.Integer" resultMap="userResult" >
                SELECT 
                	USER_ID, USER_NAME, USER_PASSWORD, USER_GROUP_NAME 
                FROM 
                	USER_ACCOUNT
                WHERE
                	USER_ID = #{id} 
        </select>

        <select id="getAllUser" resultMap="userResult">
               SELECT 
               		USER_ID, USER_NAME, USER_PASSWORD, USER_GROUP_NAME
               	FROM
               		USER_ACCOUNT
        </select>

        <delete id="deleteUser" parameterType="java.lang.Integer">
                DELETE FROM
                	USER_ACCOUNT
                WHERE
                	USER_ID = #{id}
        </delete>

        <update id="updateUser" parameterType="User">
                UPDATE
                	USER_ACCOUNT
                SET
                	USER_NAME = #{name}, USER_PASSWORD = #{password}, USER_GROUP_NAME = #{groupName}
                WHERE
                	USER_ID = #{id}
        </update>

</mapper>

Configuration.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>
        <properties resource="config.properties" />
        <typeAliases>
                <typeAlias type="mybatis.domain.User" alias="User" />
        </typeAliases>
        <!-- 未指定的时候 default 为初始化的那个环境配置 -->
        <environments default="deveploement">
                <environment id="deveploement">
                        <transactionManager type="JDBC" />
                        <dataSource type="POOLED">
                                <property name="driver" value="${driver}" />
                                <property name="url" value="${url}" />
                                <property name="username" value="${username}" />
                                <property name="password" value="${password}" />
                        </dataSource>
                </environment>
        </environments>

        <mappers>
                <mapper resource="mybatis/domain/UserMapper.xml" />
        </mappers>
</configuration>

UserMapperTest.java

import java.io.IOException;
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;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class UserMapperTest {
    
    public static Logger logger = LoggerFactory.getLogger(UserMapperTest.class);
    
    SqlSessionFactory factory = null;
    
    @org.junit.Before
    public void Before() throws IOException{
            final String resource = "Configuration.xml"; //配置文件的位置
            Reader reader = Resources.getResourceAsReader(resource); //读取配置文件，SqlSessionFactoryBuilder会根据这个文件来配置 SqlSessionFactory 
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            factory = builder.build(reader);
    }
    

    @Test
    public void testCreate() throws IOException{
                    
            SqlSession session = factory.openSession();
            
            User user = new User();
            user.setName("xiaoyue");
            user.setPassword("password");
            user.setGroupName("BOSS");
            logger.debug("insert User : name = " + user.getName());
            UserMapper userMapper = session.getMapper(UserMapper.class);
            try {
                    userMapper.saveUser(user);
                    session.commit();
                    logger.debug("insert User is successed !");
            }catch(Exception e){
                    session.rollback();
                    logger.error("insert failed, rollback transaction !" , e);
            }finally{
                    session.close();
                    logger.debug("sqlsession was closed!");
            }
    }
    
    @Test
    public void testSelectUser(){
            SqlSession session = factory.openSession();
            UserMapper mapper = session.getMapper(UserMapper.class);
            
            try{
                User user = mapper.getUserById(1);
               	System.out.println(user);
            }catch(Exception e){
                e.printStackTrace();
            }finally{
                session.close();
            }
    }
    
    @Test
    public void testSelectUserAll(){
            logger.error("testSelectUserAll is Running!");
            SqlSession session = factory.openSession();
            UserMapper mapper = session.getMapper(UserMapper.class);
            
            try{
                    List<User> list = mapper.getAllUser();
                    System.out.println(list.size());
                    for(User user : list) {
                    	System.out.println(user);
                    }
                    logger.error("selectUserAll........");
                    
            }catch(Exception e){
                    logger.error("selected is failed!");
                    e.printStackTrace();
            }finally{
                    session.close();
                    logger.error("sqlsession is closed!");
            }
    }
    
    @Test
    public void testDelete(){
            
            SqlSession session = factory.openSession();
            UserMapper mapper = session.getMapper(UserMapper.class);
            mapper.deleteUser(7);
            session.commit();
            session.close();        
    }
    
    
    @Test
    public void testUpdate(){
            SqlSession session = factory.openSession();
            UserMapper mapper = session.getMapper(UserMapper.class);
            User user = new User();
            user.setId(2);
            user.setName("xiao");
            user.setGroupName("132131");
            user.setPassword("31231");
            mapper.updateUser(user);
            session.commit();
            session.close();
    }
    
}

log4j.properties

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=warn, stdout

log4j.logger.com.ibatis=DEBUG 
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG 
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG 
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG 
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql.Connection=DEBUG 
log4j.logger.java.sql.Statement=DEBUG 
log4j.logger.java.sql.PreparedStatement=DEBUG 
log4j.logger.java.sql.ResultSet=DEBUG

config.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=root