MyBatis增删改查操作Demo

客官°小女子只卖身不卖艺 2022-05-13 12:20 346阅读 0赞

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

文件目录  
![这里写图片描述][70]

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>
        
        <environments default="e1">
            <environment id="e1">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                        
                    <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>
        
        <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">
    
    <mapper namespace="ns1">
    
        
        <insert id="save">
        insert into student(name,age)values('一茶',18)    
        </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>
    
        
        
        
        
        <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 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();
    
        }
    
    }

[http_ibatis.apache.org_dtd_ibatis-3-config.dtd]: http://ibatis.apache.org/dtd/ibatis-3-config.dtd
[70]: /images/20220513/98a40afe09de495e89f1bad56053704a.png