MyBatis增删改查操作Demo

• 第一步：导包：mybatis及jdbc connector.jar
• 第二步：编写数据库总配置文件SqlMapConfig.xml（引入如下.dtd约束文件）
  “http://ibatis.apache.org/dtd/ibatis-3-config.dtd“>
• 第三步：配置表和类的映射文件，实体类名.xml（在里面写sql语句）
• 第四步：创建数据库会话session，进行增删改查

文件目录

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
<!-- 可以给实体类取别名，这个放在前面，放在后面会出错 -->
    <typeAliases>
        <typeAlias type="com.test.entity.Student" alias="Student"/>
    </typeAliases>
    <!-- step1:配环境 -->
    <environments default="e1">
        <environment id="e1">
            <!-- 先配事务，事务是必须配的，简单的事务类型JDBC  -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 再配数据源，POOLED 以连接池的方式 -->
            <dataSource type="POOLED">
                <!-- 在数据源中配置连接属性,注意这里是username不是user -->    
                <property name="driver" value="com.mysql.jdbc.Driver"/> 
                <property name="url" value="jdbc:mysql://localhost:3306/test"/> 
                <property name="username" value="root"/>
                <property name="password" value="root1205"/>
            </dataSource>
        </environment>  
    </environments>
    <!-- step2:引入实体类的映射配置文件 -->
    <mappers>
        <mapper resource="com/test/entity/Student.xml"/>
    </mappers>      
</configuration>

Student.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD SQL Map 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<!-- 命名空间必须要加上，解决sql语句id重复 -->
<mapper namespace="ns1">
<!-- 插入数据 -->
    <!-- 写死的 -->
    <insert id="save">
    insert into student(name,age)values('一茶',18)    
    </insert>
    <!-- 使用占位符，不是问号了，其中id，name指的是session的insert方法所传对象参数的属性 -->
    <insert id="save2">
    insert into student(name,age)values(#{name},#{age})
    </insert>
    <insert id="save3">
    insert into student(name,age)values(#{name},#{age});
    </insert>
<!-- 修改数据 -->
    <update id="mod1">
    update student set name='王五',age='10' where id=1
    </update>
    <update id="mod2">
    update student set name=#{name},age=#{age} where id=1
    </update>
<!-- 删除数据 -->
    <delete id="del1">
    delete from student where id=1
    </delete>
    <delete id="del2">
    delete from student where name=#{name} and age=#{age}
    </delete>
<!-- 查询数据 -->
    <!-- ExecutorException:excIt's likely that neither a Result Type nor a Result Map was specified. -->
    <!-- 默认会自动映射，所以resultMap可以不配，如果要自定义映射要配置resultMap -->
    <!-- 可以给com.test.entity.Student配别名，在SqlMapConfig里配typeAliases -->
    <!-- <select id="query1" resultType="com.test.entity.Student"> -->
    <select id="query1" resultType="Student">
    select * from student where id=2    
    </select>
    <select id="query2" resultType="Student">
    select * from student where name=#{name}
    </select>
    <select id="query3" resultType="Student" parameterType="java.lang.String">
    select * from student where name=#{_parameter}
    </select>
    <select id="query4" resultType="Student" parameterType="int">
    select * from student where id=#{_parameter}
    </select>
    <!-- 默认是自动映射，还可以自定义映射，要配置resultMap -->
    <resultMap type="Student" id="myMap">
        <!-- 虽然我只映射了一个表字段，但剩下的字段会按默认情况映射 -->
        <result property="name" column="name2"/>
    </resultMap>
    <select id="query5" resultType="Student" resultMap="myMap">
    select name as name2,age,school from student where id=#{_parameter}
    </select>
    <select id="query6" resultType="Student">
    select * from student where 1=1
    </select>
    <select id="query7" resultType="Student" parameterType="java.lang.String">
    select * from student where name=#{_parameter}
    </select>
    <select id="query8" resultType="Student">
    select * from student where 1=1
    </select>
    <select id="query9" resultType="Student" parameterType="java.lang.String">
    select * from student where name=#{_parameter}
    </select>   
</mapper>

TestDemo.java

package com.test.test;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 com.test.entity.Student;

public class TestDemo {

    /**
     * @param args
     * @throws IOException 
     */
    public static void main(String[] args) throws IOException {
        // 测试能否连接数据库，并插入数据
        //第一步：和数据库连接（获取与数据库会话对象）
        SqlSession session = null;
        SqlSessionFactory sessionFactory = null;
        SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
        Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
        sessionFactory = factoryBuilder.build(reader);
        session = sessionFactory.openSession();
        //System.out.println(session);//测试是否连接成功

        //第二步：和数据库交互
        //op1:插入数据
        //session.insert("ns1.save");
        //Map map = new HashMap();
        //map.put("name", "zhangsan");
        //map.put("age", "18");//此处的18虽然是字符串，但是也按int插入到age字段里了
        //session.insert("ns1.save2",map);
        //Student stu = new Student();
        //stu.setAge(20);
        //stu.setName("lisi");
        //session.insert("ns1.save3", stu);

        //op2:修改数据
        //session.update("ns1.mod1");
        //Student stu2 = new Student();
        //stu2.setAge(20);
        //stu2.setName("lisi2");
        //session.update("ns1.mod2", stu2);

        //op3:删除数据
        //session.delete("ns1.del1");
        //Student stu3 = new Student();
        //stu3.setAge(20);
        //stu3.setName("lisi");
        //session.delete("ns1.del2",stu3);

        //op4:查询数据
        //selectOne
        Student sd1 = session.selectOne("ns1.query1");
        System.out.println(sd1.getName());
        System.out.println(sd1.getAge());
        Map map2 = new HashMap();
        map2.put("name", "张三");
        //传一个参数这样传太费劲了，name='张三'有两条数据,selectOne返回的结果只能是null或一条，所以此时报异常
        //Expected one result (or null) to be returned by selectOne(), but found: 2
        //System.out.println(session.selectOne("ns1.query2",map2));
        //直接传字符串，需要制定parameterType以及占位符用_parameter
        Student sd3 = session.selectOne("ns1.query3","马冬梅");
        System.out.println(sd3.getAge());
        Student sd4 = session.selectOne("ns1.query4", new Integer(11));
        System.out.println(sd4.getName());
        Student sd5 = session.selectOne("ns1.query5", new Integer(11));
        System.out.println(sd5.getName());
        System.out.println(sd5.getAge());
        System.out.println(sd5.getSchool());
        //selectList
        List<Student> sList = session.selectList("ns1.query6");
        for(Student s:sList){
            System.out.println(s.getName()+","+s.getAge());
        }
        List<Student> sList2 = session.selectList("ns1.query7", "张三");
        for(Student s:sList2){
            System.out.println(s.getName()+","+s.getAge());
        }
        //selectMap
        //selectMap(String s1,String s2)返回的结果是以指定列s2为key，以Bean为value的map
        Map<Integer,Student> map = session.selectMap("ns1.query8","id");
        Set<Integer> k = map.keySet();
        Iterator<Integer> it = k.iterator();
        while(it.hasNext()){
            int key = it.next();
            System.out.print(key+"-----"+map.get(key).getId());
            System.out.print(","+map.get(key).getName());
            System.out.print(","+map.get(key).getAge());
            System.out.println(","+map.get(key).getSchool());
        }
        //selectMap(String s1,Object obj,String s2)返回的结果是以指定列s2为key，以Bean为value的map
        Map<Integer,Student> map3 = session.selectMap("ns1.query9", "张三", "id");
        Set<Integer> k3 = map3.keySet();
        Iterator<Integer> it3 = k3.iterator();
        while(it3.hasNext()){
            int key = it3.next();
            System.out.print(key+"-----"+map3.get(key).getId());
            System.out.print(","+map3.get(key).getName());
            System.out.print(","+map3.get(key).getAge());
            System.out.println(","+map3.get(key).getSchool());
        }

        //必须写提交，不会默认提交的，此处不写，不会提交到数据库
        session.commit();

    }

}