安装mybatis及增删改

安装Mybatis

导入依赖包

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.2.2</version>
</dependency>

创建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>
    <properties resource="datebase.properties"></properties>

    <typeAliases>
<!--        <typeAlias type="cn.zb.kb21.pojo.Dog" alias="Dog"/>-->
        <package name="nj.zb.kb21.pojo"/>
    </typeAliases>

    <environments default="mysqlDev">
        <environment id="mysqlDev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${mysqldriver}"/>
                <property name="url" value="${mysqlurl}"/>
                <property name="username" value="${mysqluser}"/>
                <property name="password" value="${mysqlpwd}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="nj/zb/kb21/dao/DogDao.xml"/>--路径
    </mappers>
</configuration>

编写DogDao.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="nj.zb.kb21.dao.DogDao">--包名
    <insert id = "save">--sql语句
        insert into dog(name,health,love,strain,lytm)
        values (#{name},#{health},#{love},#{strain},now())
    </insert>
</mapper>

编写测试类

public class DogTest {
    //工厂模式
    SqlSessionFactory sqlSessionFactory = null;
    //定义会话
    SqlSession sqlSession = null;
    //定义DogDao对象
    DogDao dogDao = null;

    @Before
    public void init(){
        System.out.println("Start");

        //加载mybatis-config.xml配置文件
        InputStream inputStream = DogTest.class.getClassLoader().getResourceAsStream
                ("mybatis-config.xml");

        //获取builder工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

        //通过建造工厂来建造SqlSessionFactory
        sqlSessionFactory = builder.build(inputStream);

        //创建Session会话
        sqlSession = sqlSessionFactory.openSession();

        //获取Session会话的执行者
        dogDao = sqlSession.getMapper(DogDao.class);
    }

    @Test
    public void showInfo(){
        System.out.println(sqlSessionFactory);
        System.out.println(sqlSession);
        System.out.println(dogDao);
    }

    @After
    public void down(){
        sqlSession.commit();
        sqlSession.close();
        System.out.println("Over");
    }
}

单独增删改

添加狗

xml

<insert id = "save">
        insert into dog(name,health,love,strain,lytm)
        values (#{name},#{health},#{love},#{strain},now())
    </insert>

测试类

@Test
    public void testSaveDog(){
        Dog dog = new Dog();
        dog.setName("脏脏");
        dog.setHealth(100);
        dog.setLove(100);
        dog.setStrain("法斗");
        dogDao.save(dog);
    }

执行结果

修改狗

xml

<update id="update">
        update dog
        set name = #{name},health = #{health}
        where id = #{id}
    </update>

测试类

@Test
    public void testUpdateDog(){
        Dog dog = new Dog();
        dog.setId(19);
        dog.setName("冰糖葫芦");
        dog.setHealth(999);
        dogDao.update(dog);
    }

执行结果

删除狗

xml

<delete id="delete">
        delete from dog
        where id = #{id}
    </delete>

测试类

@Test
    public void testDeleteDog(){
        Integer id = 19;
        dogDao.delete(id);
    }

执行结果

批量处理

批量删除

xml

 <delete id="batchDelete">
        delete from dog where id in
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

测试类

 @Test
    public void testBatchDelete(){
        List<Integer> dogs = new ArrayList<>();
        dogs.add(6);
        dogs.add(7);
        dogs.add(18);
        dogDao.batchDelete(dogs);
    }

执行结果

批量添加

xml

<insert id="batchAdd">
        insert into <include refid="tableName"/>
        values
        <foreach collection="list" item="dog" separator=",">
            (null,#{dog.name},#{dog.health},#{dog.love},#{dog.strain},now())
        </foreach>
    </insert>

测试类

@Test
    public void testBatchAdd(){
        List<Dog> dogs = new ArrayList<>();
        Dog dog1 = new Dog("富强", 100, 100, "自由");
        Dog dog2 = new Dog("民主", 100, 100, "平等");
        Dog dog3 = new Dog("文明", 100, 100, "公正");
        Dog dog4 = new Dog("和谐", 100, 100, "法治");
        dogs.add(dog1);
        dogs.add(dog2);
        dogs.add(dog3);
        dogs.add(dog4);
        dogDao.batchAdd(dogs);
    }

执行结果

批量更新

mysql不允许批量，需要在datebase.properties添加一段代码?allowMultiQueries=true，如下

mysqlurl=jdbc:mysql://192.168.153.141:3306/jdbcstudb?allowMultiQueries=true

xml

<update id="batchUpdate">
        <foreach collection="list" item="dog" separator=";">
        update <include refid="tableName"/>
        <set>
            <if test=" dog.name != null and dog.name != '' ">
                name = #{dog.name},
            </if>
            <if test=" dog.health != null ">
                health = #{dog.health},
            </if>
            <if test=" dog.love != null ">
                love = #{dog.love},
            </if>
            <if test=" dog.strain != null and dog.strain != '' ">
                strain = #{dog.strain},
            </if>
        </set>

        where id = #{dog.id}
        </foreach>
    </update>

测试类

@Test
    public void testBatchUpdate(){
        List<Dog> dogs = new ArrayList<>();
        Dog dog1 = new Dog(22, 10000, 10000);
        Dog dog2 = new Dog(23,"zh民主", "平等");
        Dog dog3 = new Dog(24,1000000, 1000000);
        Dog dog4 = new Dog(25,"和谐z", 100000, 1000, "法治zh");
        dogs.add(dog1);
        dogs.add(dog2);
        dogs.add(dog3);
        dogs.add(dog4);
        dogDao.batchUpdate(dogs);
    }

执行结果