mybatis中的增删改查操作

在这一个部分，主要进行增删改查的示例书写。

增删改查可以基于xml的，也可以基于注解的方式。

一：对单条数据的查询

1.目录结构

　　这个使得目录更加清晰

　　

2.User.java

　　这个使用以前的user表。

　　因此，domain不需要改动。

3.配置文件

　　重要的参考部分是引用映射文件的部分

<?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>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC">
        <property name="" value=""/>
      </transactionManager>
      <dataSource type="UNPOOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
      </dataSource>
    </environment>
  </environments>

   <mappers>
      <mapper resource="com/cao/sql/config/users.xml"/>
   </mappers>

</configuration>

4.映射文件

<?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="quickFirst">
    <resultMap type="com.cao.bean.User" id="User">
        <id column="ID" jdbcType="INTEGER" property="id"/>
        <result column="NAME" jdbcType="VARCHAR" property="name"/>
        <result column="AGE" jdbcType="INTEGER" property="age"/>
    </resultMap>
      <!-- 单条数据的查询，根据id查询 -->
    <select id="selectUserOne" parameterType="int" resultMap="User">
        SELECT * From users where id = #{id};
    </select>
</mapper>

5.测试类

　　这个对于加载的配置文件的方式不同，稍微有点区别，顺便整理一下。

package com.cao.test1;

import java.io.IOException;
import java.io.InputStream;
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.Test;

import com.cao.bean.User;
/**
 * 这是一个对比程序
 * 当然也是一个根据条件，进行单条查询的程序
 * @author dell
 *
 */
public class mainTest {
    /**
     * 查询单条数据
     * 根据id进行查询
     * @throws Exception
     */
    @Test
    public void testSelectOne1() throws Exception {
        String resources="/com/cao/config/Configuration.xml";       //这种方式在前面需要加一个/
        InputStream is=mainTest.class.getResourceAsStream(resources);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.selectUserOne";
        User user=sqlSession.selectOne(statement, 1);
        System.out.println(user);
    }
    @Test
    public void testSelectOne2() throws Exception {                    ////这种方式在前面不需要添加
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.selectUserOne";
        User user=sqlSession.selectOne(statement, 2);
        System.out.println(user);
    }
}

二：插入数据

1.xml

<!-- 插入数据 -->
    <insert id="insertData" parameterType="com.cao.bean.User">
        INSERT INTO USERS(name,age) value(#{name},#{age});
    </insert>

2.测试程序

/**
     * 插入数据
     */
    @Test
    public void testInsert()throws Exception {
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.insertData";
        int insert=sqlSession.insert(statement, new User(-1,"KK",9));
        sqlSession.commit();
        System.out.println(insert);
        sqlSession.close();
    }

3.效果

　　打印出1，表示插入一条数据。

　　

　　数据库：

　　

三：删除数据

1.xml

<!-- 删除数据 -->
    <delete id="deleteData" parameterType="int">
        DELETE FROM USERS where id=#{id};
    </delete>

2.测试程序

/**
     * 删除数据
     */
    @Test
    public void testDelete()throws Exception {
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.deleteData";
        int delete=sqlSession.delete(statement, 4);
        System.out.println(delete);    
        sqlSession.commit();
        sqlSession.close();
    }

3.效果

　　打印出1，表示删除一条数据。

　　

　　数据库：

　　

四：更新数据

1.xml

<!-- 更新数据 -->
    <update id="updateData" parameterType="com.cao.bean.User">
        UPDATE USERS set name=#{name},age=#{age} where id=#{id};
    </update>

2.测试类

/**
     * 更新数据
     */
    @Test
    public void testUpdate()throws Exception{
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.updateData";
        int update=sqlSession.update(statement, new User(3,"TT",99));
        sqlSession.commit();
        System.out.println(update);
        sqlSession.close();
        
    }

3.效果

　　打印1，表示更新一条数据。

　　

　　数据库：

　　

、

五：完整程序

1.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="quickFirst">
    <resultMap type="com.cao.bean.User" id="User">
        <id column="ID" jdbcType="INTEGER" property="id"/>
        <result column="NAME" jdbcType="VARCHAR" property="name"/>
        <result column="AGE" jdbcType="INTEGER" property="age"/>
    </resultMap>
    
      <!-- 单条数据的查询，根据id查询 -->
    <select id="selectUserOne" parameterType="int" resultMap="User">
        SELECT * From users where id = #{id};
    </select>
    
    <!-- 插入数据 -->
    <insert id="insertData" parameterType="com.cao.bean.User">
        INSERT INTO USERS(name,age) value(#{name},#{age});
    </insert>
    
    <!-- 删除数据 -->
    <delete id="deleteData" parameterType="int">
        DELETE FROM USERS where id=#{id};
    </delete>
    
    <!-- 更新数据 -->
    <update id="updateData" parameterType="com.cao.bean.User">
        UPDATE USERS set name=#{name},age=#{age} where id=#{id};
    </update>
</mapper>

2.测试类

package com.cao.test1;

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.session.TransactionIsolationLevel;
import org.junit.Test;

import com.cao.bean.User;
/**
 * 这是一个对比程序
 * 当然也是一个根据条件，进行单条查询的程序
 * @author dell
 *
 */
public class mainTest {
    /**
     * 查询单条数据
     * 根据id进行查询
     * @throws Exception
     */
    @Test
    public void testSelectOne1() throws Exception {
        String resources="/com/cao/config/Configuration.xml";       //这种方式在前面需要加一个/
        InputStream is=mainTest.class.getResourceAsStream(resources);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.selectUserOne";
        User user=sqlSession.selectOne(statement, 1);
        System.out.println(user);
    }
    @Test
    public void testSelectOne2() throws Exception {                    ////这种方式在前面不需要添加
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.selectUserOne";
        User user=sqlSession.selectOne(statement, 2);
        System.out.println(user);
    }
    /**
     * 插入数据
     */
    @Test
    public void testInsert()throws Exception {
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.insertData";
        int insert=sqlSession.insert(statement, new User(-1,"KK",9));
        sqlSession.commit();
        System.out.println(insert);
        sqlSession.close();
    }
    /**
     * 删除数据
     */
    @Test
    public void testDelete()throws Exception {
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.deleteData";
        int delete=sqlSession.delete(statement, 4);
        System.out.println(delete);    
        sqlSession.commit();
        sqlSession.close();
    }
    /**
     * 更新数据
     */
    @Test
    public void testUpdate()throws Exception{
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession();
        String statement="quickFirst.updateData";
        int update=sqlSession.update(statement, new User(3,"TT",99));
        sqlSession.commit();
        System.out.println(update);
        sqlSession.close();
        
    }
    
}

六：基于注解的增删改查

1.目录

　　

2.接口

package com.cao.test1;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.cao.bean.User;

public interface UserMapper {
    @Insert("INSERT INTO USERS(name,age) value(#{name},#{age})")
    public int add(User user);
    
    @Delete("DELETE FROM USERS where id=#{id}")
    public int delete(int id);
    
    @Update("UPDATE USERS set name=#{name},age=#{age} where id=#{id}")
    public int update(User user);
    
    @Select("SELECT * From users where id = #{id}")
    public User selectById(int id);
    
    @Select("SELECT * From users")
    public List<User> select();
}

3.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>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC">
        <property name="" value=""/>
      </transactionManager>
      <dataSource type="UNPOOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
      </dataSource>
    </environment>
  </environments>

   <mappers>
      <mapper class="com.cao.test1.UserMapper"/>
   </mappers>

</configuration>

4.测试类

package com.cao.test;

import java.io.IOException;
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.Test;

import com.cao.bean.User;
import com.cao.test1.UserMapper;

public class MainTest {
    @Test
    public void test1() throws Exception {
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);                //true后是自动提交
        UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
        int add=userMapper.add(new User(-1,"TT",89));
        System.out.println(add);
        sqlSession.close();
    }
}

5.效果

　　

　　

、