基于Mybatis的增删改查

基于Mybatis的增删改查

1、 依赖包： mybatis-3.4.5.jar，jdbc-connector.jar，
2、 项目结构

3、 例子文件：

// com.pojo.User.java
package com.pojo;

public class User {
    private int id;
    private String name;
    private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    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 User() {
    }

    public User(String name, String address) {
        this.name = name;
        this.address = address;
    }
}

// com.dao.UserDao.java
package com.dao;

import com.pojo.User;

public interface UserDao {
    public void addUser(User user);

    public void deleteUser(int userId);

    public void updateUser(User user);

    public User selectUserById(int userId);
    // 通过注解的方式，不需要配置mapper
    // @select("select * from user where id=#{id}")
    // public User selectUser(int id);
}

// com/dao/UserDaoMapper.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.dao.UserDao">
    <resultMap id="fuck" type="com.m.Fuck">
        // 将类Fuck中的字段一一映射出来
        <id column="fuckid" jdbcType="INTEGER" property="fuckid" />
        <result column="belong" jdbcType="VARCHAR" property="belong" />
    </resultMap>

    // 表名 sql
    <sql id="info1">table1</sql>
    <sql id="info2">table2</sql>

    // 序列 sql
    <sql id="id_seq">seq_fuck_fuckid.nextval</sql>

    // 表中的字段sql
    <sql id="table1_columns">tableid, tablename, remark</sql>

/************************* select **********************************/
    <select id="selectUserById" parameterType="int" resultType="User">
        select 
        <include  refid="table1_columns" />
         from 
        <include refid="table1" />
        where user.id=#{id, jdbcType=INTEGER}
        /*
        <if test="id != -1">
            and fuckid=#{fuckid, jdbcType=INTEGER}
        </if>
        */
    </select>
/************************* insert **********************************/
    <insert id="addUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        insert into user (name, address)
        values(#{name}, #{address})
    </insert>

    <insert id="inertTable" parameterType="com.m.Fuck">
        <selectKey keyProperty="fuckid" resultType="int" order="BEFORE">
            select <include refid="id_seq" /> from dual
        </selectKey>

        // 多表插入,插入多条记录
        insert all 
        <foreach collection="tableInfos" item="tableInfo" index="index">
            into <include refid="table1" /> values 
            (#{tableInfo.tableId, jdbcType=INTEGER},
            #{fuckid, jdbcType=INTEGER}, // 上面的序列
            #{tableInfo.tablename, jdbcType=VARCHAR})
        </foreach>
        into <include refid="table2" /> values 
        (#{tableInfo.tableId, jdbcType=INTEGER},
        #{fuckid, jdbcType=INTEGER},
        #{tableInfo.tablename, jdbcType=VARCHAR})
    </insert>
/************************* update **********************************/   
    <update id="updateUser" parameterType="User">
        update user set name=#{name}, address=#{address}
        where id=#{id}
    </update>
/************************* delete**********************************/    
    // 删除单条  返回结果为int类型
    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{id}
    </delete>
    // 多表删除 没有返回结果
    <delete id="deleteAll" parameterType="int">
        begin
        delete from user where id=#{id};
        delete from user_2 where id=#{id};
        end;
    </delete>
</mapper>

// mybatis/mysql-jdbc.properties
driver=com.mysql.jdbc.Driver
user=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8
username=root
password=123456
// mybatis/mybatis-config.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>
    <typeAliases>
        <typeAlias alias="User" type="com.pojo.User"/>
    </typeAliases>

    <properties resource="mybatis/mysql-jdbc.properties"></properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <!-- <property name="driver" value="com.mysql.jdbc.Driver"/> -->
                <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="com/dao/UserDaoMapper.xml"/>
        // 通过注解也需要配置mapper
        // <mapper class="com.dao.UserDao"/>
    </mappers>
</configuration>

// com.junit.UserDaoTest.java
package com.junit;

import java.io.Reader;

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.BeforeClass;
import org.junit.Test;

import com.dao.UserDao;
import com.pojo.User;

public class UserDaoTest {

    static SqlSession session;
    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        Reader reader = Resources.getResourceAsReader("mybatis/mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        session = factory.openSession();
        reader.close();
    }

    @Test
    public void addUser() {
        User user = new User();
        user.setName("张三");
        user.setAddress("江苏省南京市");

        UserDao dao = session.getMapper(UserDao.class); // UserDao的实现类由mybatis自己内部生成
        dao.addUser(user);
        session.commit();
        session.close();
    }
    @Test
    public void deleteUser() {
        UserDao dao = session.getMapper(UserDao.class);
        dao.deleteUser(1);
        session.commit();
        session.close();
    }
    @Test
    public void updateUser() {
        User user = new User("张三2", "江苏省南京市2");
        UserDao dao = session.getMapper(UserDao.class);
        dao.updateUser(user);
        session.commit();
        session.close();
    }
    @Test
    public User selectUserById() {
        UserDao dao = session.getMapper(UserDao.class);
        User user = dao.selectUserById(1);
        session.close();
        return user;
    }
}
// 表user
user 表结构
id int auto_increase
name char(4)
address char(10)

4、 在插入中文字符的时候，出现数据库乱码
参考博客 mybatis操作mysql乱码