Mybatis之数据操作(增删改查+批量操作)

项目资源包可以去我的资源里下载

创建表

（1）创建表

-- 学生表

create table student(

id int PRIMARY KEY auto_increment,

name varchar(20) not null,

age int not null);

-- 向学生表中增加记录

INSERT INTO student values(null,"李华",22);

INSERT INTO student values(null,"王华",21);

INSERT INTO student values(null,"张华",20);

 

创建持久化类

 

package cn.sdut.po;

 

public class Student {

int id;

String name;

int age;

 

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 int getAge() {

return age;

}

 

public void setAge(int age) {

this.age = age;

}

 

@Override

public String toString() {

return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";

}

 

}

 

表映射文件：StudentMapper.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="aaa">

   <resultMap type="cn.sdut.po.Student" id="stuMap">

   <id column="id" property="id"/>

   <result column="name" property="name"/>

   <result column="age" property="age"/>

   

   </resultMap>

<!-- 条件查询：若name条件非空，则模糊查询；若age不为0，则匹配等值；若姓名为空且age=0，则查询所有学生。 -->

<select id="getByCond" resultType="cn.sdut.po.Student">

<!-- if -->

<!-- select * from student where 1=1 <if test="id!=0"> and id>#{id} </if>

<if test="name!=null and name!=''"> and name like concat('%',#{name},'%')

</if> <if test="age!=0"> and age>=#{age} </if> -->

 

<!-- where标签

select * from student <where> <if test="id!=0"> and id>#{id} </if>

<if test="name!=null and name!=''"> and name like concat('%',#{name},'%')

</if> <if test="age!=0"> and age>=#{age} </if> </where> -->

 

<!-- trim标签 -->

select * from student

<trim prefix="where" prefixOverrides="and">

<if test="id!=0">

and id>#{id}

</if>

<if test="name!=null and name!=''">

and name like concat('%',#{name},'%')

</if>

<if test="age!=0">

and age>=#{age}

</if>

</trim>

</select>

<!-- 批量查询：给定若干id的值，查询与之相匹配的所有记录。 -->

<select id="getInRange" resultMap="stuMap">

select * from student where id in

<foreach collection="list" item="id" 

open="(" close=")" separator="," 

index="index"

>

#{id}

</foreach>

</select>

 

<select id="getAll" resultType="cn.sdut.po.Student">

select * from student

</select>

 

<select id="getStudentById" resultType="student">

select * from student

where id=#{id111111}

</select>

 

<insert id="addStudent">

insert into student values(null,#{name},#{age})

</insert>

 

<delete id="delStudent">

delete from student where id=#{id}

</delete>

 

<update id="updateStudent">

update student set name=#{name},age=#{age} where

id=#{id}

</update>

<!-- 条件修改：若name不为空，则将给定id的学生姓名更换，若age不为0，则将给定id的年龄更换；否则，不做修改。 -->

<update id="updateStudentByCond">

update student

<!-- <set> <if test="name!=null and name!=''"> name=#{name}, </if> <if

test="age!=0"> age=#{age}, </if> </set> -->

 

<trim prefix="set" suffixOverrides=",">

<if test="name!=null and name!=''">

name=#{name},

</if>

<if test="age!=0">

age=#{age},

</if>

</trim>

where id=#{id}

</update>

<!-- 批量插入：给定若干学生的name和age，批量插入学生信息。 -->

<insert id="insertBatch">

insert into student (id,name,age) values

<foreach collection="list" item="Student" separator="," index="index">

(null, #{Student.name}, #{Student.age})

</foreach>

</insert>

</mapper> 

全局配置文件：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="jdbc.properties"></properties>

  <typeAliases>

   <typeAlias type="cn.sdut.po.Student" alias="student"/>

  </typeAliases>

  

  <environments default="development">

    <environment id="development">

      <transactionManager type="JDBC"/>

      <dataSource type="POOLED">

        <property name="driver" value="${jdbc.driver}"/>

        <property name="url" value="${jdbc.url}"/>

        <property name="username" value="${jdbc.user}"/>

        <property name="password" value="${jdbc.password}"/>

      </dataSource>

    </environment>

  </environments>

  <mappers>

    <mapper resource="cn/sdut/mapper/StudentMapper.xml"/>

  </mappers>

</configuration>

Jbdc配置文件：jbdc.properties

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/sdut?useUnicode=true&characterEncoding=utf-8

jdbc.user=root

jdbc.password=root

日志配置文件：log4j.properties

 ### \u8BBE\u7F6E###

log4j.rootLogger = debug,stdout

 

### \u8F93\u51FA\u4FE1\u606F\u5230\u63A7\u5236\u62AC ###

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 = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

 

 

 

测试文件：Main.java

package cn.sdut.view;

import java.io.IOException;

import java.io.InputStream;

import java.io.Reader;

import java.util.ArrayList;

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 cn.sdut.po.Student;

public class Main {

public static void main(String[] args) throws IOException {

InputStream reader=Resources.getResourceAsStream("mybatis-config.xml");

SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);

SqlSession session = factory.openSession();

/*Student student=new Student();

student.setId(7);

student.setName("山东1111");

student.setAge(31222);

int r=session.insert("addStudent", student);*/

//int r=session.update("updateStudent",student);

/*int r=session.delete("delStudent",7);

System.out.println(r);

session.commit();

List<Student> stuList=session.selectList("aaa.getAll");

System.out.println(stuList);

Student stu=session.selectOne("getStudentById",1);

System.out.println(stu);*/

/*条件查询*/

Student stu1=new Student();

stu1.setName("华");

stu1.setAge(20);

List<Student> stuList2=session.selectList("getByCond",stu1);

System.out.println("条件查询："+stuList2);

/*条件修改*/

Student stu=new Student();

//stu.setName("山东1111");

stu.setAge(26);

stu.setId(4);

session.update("updateStudentByCond",stu);

session.commit();

/*int[] arr= {1,2,3};

List<Student> stuList=session.selectList("getInRange",arr);

System.out.println(stuList);*/

/*批量查询*/

List<Integer> list=new ArrayList<Integer>();

list.add(1);

list.add(2);

list.add(3);

List<Student> stuList=session.selectList("getInRange",list);

System.out.println("批量查询:"+stuList);

/*批量插入*/

Student insert1=new Student();

insert1.setId(7);

insert1.setAge(20);

insert1.setName("李四");

Student insert2=new Student();

insert2.setId(8);

insert2.setAge(22);

insert2.setName("張三");

List<Student> insertlist=new ArrayList<Student>();

insertlist.add(insert1);

insertlist.add(insert2);

int r1=session.insert("insertBatch", insertlist);

session.commit();

System.out.println("批量插入:"+r1);

}

}