搭建Mybatis+Oracle项目以及简单的增删改查语法-原创手记-慕课网

1.项目的相关路径
图片描述

2.Mybatis的配置文件

<?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"></transactionManager>  
        <dataSource type="pooled">  
          <property name="driver" value="oracle.jdbc.driver.OracleDriver"></property>  
          <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"></property>  
          <property name="username" value="scott"></property>  
          <property name="password" value="123"></property>    
        </dataSource>  
     </environment>  

</environments> 

<mappers> 
<mapper resource="com/mapping/UserMapping.xml"/> 
</mappers> 
</configuration>

3.Mybatis的工具类

     /**
 * 
 */
/**
 * @author lmy
 *
 */
package com.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Util {  

    private static SqlSessionFactory sqlSessionFactory = null;  

    /** 
     * 初始化Session工厂 
     * @throws IOException 
     */  
    private static void initialFactory() throws IOException {  
        String resource = "Mybatis-config.xml";  
        InputStream inputStream = Resources.getResourceAsStream(resource);  
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);  
    }  

    /** 
     * 获取Session 
     * @return 
     */  
    public static SqlSession getSession() {  
        if(sqlSessionFactory == null) {  
            try {  
                initialFactory();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  

        return  sqlSessionFactory.openSession();  
    }  

}

4.创建实体类以及对应的Mapping.xml文件

/**
 * 
 */
/**
 * @author lmy
 *
 */
package com.model;

public class Users { 
    private Integer id; 
    private String name; 
    private int age; 
    public Integer getId() 
    { return id; } 
    public void setId(Integer 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 "User [id=" + id + ", name=" + name + ", age=" + age + "]"; } }

<?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="com.mapping.UserMapping"> 
<!-- 1.查询 -->
<select id="getUser" resultType="com.model.Users"> 
select * from Users
</select> 

<!-- 2.插入 -->
<insert id="insertUser" parameterType="com.model.Users" useGeneratedKeys="true">
insert into Users (id,name,age) values (#{id},#{name},#{age})
</insert>
<!--在Oracle的版本中，有几点需要注意的：

1.SQL中没有VALUES；

2.<foreach>标签中的(selece ..... from dual)；

3.<foreach>标签中的separator的属性为"UNION ALL"，将查询合并结果集。  -->

<!-- 3.批量插入 list-->
<insert id="insertUserList" parameterType="java.util.List">
insert into Users (id,name,age)
<foreach collection="list" item="item" index="index" separator="UNION ALL">
SELECT
#{item.id} id,
#{item.name} name,
#{item.age} age
FROM dual
</foreach>
</insert>

<!-- 4.批量插入 map-->
<insert id="insertUserMap" parameterType="java.util.Map">
insert into Users (id,name,age)
<foreach collection="map" item="item" index="index" separator="UNION ALL">
SELECT
#{item.id} id,
#{item.name} name,
#{item.age} age
FROM dual
</foreach>
</insert>

<!-- 5.批量插入 List<Map<String,Object>>-->
<insert id="insertUserMapList" parameterType="java.util.List">
insert into Users (id,name,age)
<foreach collection="list" item="item" index="index" separator="UNION ALL">
SELECT
#{item.id} id,
#{item.name} name,
#{item.age} age
FROM dual
</foreach>
</insert>

<!--6.返回值类型是 List<Map<String,Object>>类型的-->
<select id="ListMapData" resultType="java.util.HashMap" >
select u.id id,u.name name from Users u
</select>

<!-- 7.查询-->
<select id="getUserById" parameterType="int" resultType="com.model.Users">
select * from Users where id=#{id}
</select>

<!-- 8.删除 -->
<delete id="deleteUserById" parameterType="int">
delete from Users where id=#{id}
</delete>

<!-- 9.批量删除 -->
<delete id="deleteUser" parameterType="java.util.List" >
delete from Users where id in (
<foreach collection="list" item="item" index="index" separator="UNION ALL">
select 
#{item.id} 
from dual
</foreach>
)
</delete>

<!-- 10.更新-->
<update id="updateUserById" parameterType="com.model.Users">
update Users set name=#{name} where id=#{id}
</update>

</mapper>

5.测试类

/**
 * 
 */

package com.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.After;
import org.junit.Before;
import org.junit.Test;

import com.model.Users;
import com.util.Util;

public class TestMybatis{

      private SqlSession session = null;  

        @Before  
        public void before() {  
            session = Util.getSession();  
        }  

        @After  
        public void after() {  
            session.commit();  
            session.close();  
        } 
    //1.查询（数字对应Mapping.xml的数字方法）
       @SuppressWarnings("unchecked")
       @Test  
        public  void testSelectUser() throws IOException { 
         List<Users> userList = session.selectList("com.mapping.UserMapping.getUser");
         for(Users each : userList) {  
            System.out.println("each->" + each);  
        }  
    }

      //2.插入
        @Test  
        public void testInsert() {  
            Users user = new Users(); 
            user.setId(200);
            user.setName("路飞");  
            user.setAge(23);  
            session.insert("com.mapping.UserMapping.insertUser" , user);  
        } 
     //7.根据id查询数据
        @Test  
        public void testSelectUserById() throws IOException { 

            Users user = (Users) session.selectOne("com.mapping.UserMapping.getUserById",1);
            System.out.println("each->" + user.toString());  

        }

       //8.根据id删除数据
        @Test  
        public void testDeleteUserById() throws IOException { 
              session.delete("com.mapping.UserMapping.deleteUserById",1);
              System.out.println("删除成功！");  
        }

      // 10.更新数据
        @Test  
        public void testUpdateUserById() throws IOException { 
            Users user = (Users) session.selectOne("com.mapping.UserMapping.getUserById",1);
            user.setName("11111111");
            session.update("com.mapping.UserMapping.updateUserById",user);
            System.out.println("更新成功！");  
        }
        //3.批量插入list
        @Test  
        public void testInsertUserList() throws IOException { 
            Users user = new Users(); 
            user.setId(100);
            user.setName("路飞");  
            user.setAge(23);  
            Users user1 = new Users(); 
            user1.setId(101);
            user1.setName("路飞");  
            user1.setAge(23);  
            List<Users> userList = new ArrayList<Users>();
            userList.add(user);
            userList.add(user1);
            session.insert("com.mapping.UserMapping.insertUserList" , userList);  
            System.out.println("插入成功！");  
        }

        //4.批量插入map
        @Test  
        public void testInsertUserMap() throws IOException { 
            Users user = new Users(); 
            user.setId(106);
            user.setName("路飞");  
            user.setAge(23);  
            Users user1 = new Users(); 
            user1.setId(104);
            user1.setName("路飞");  
            user1.setAge(23);  
            List<Users> list =new ArrayList<Users>();
            list.add(user1);
            list.add(user);
            Map<String,List<Users>> map = new HashMap<String,List<Users>>();  
            map.put("map",list);//map存放的key值要与mapper.xml文件中的<foreach>的collection名称一致
            session.insert("com.mapping.UserMapping.insertUserMap" , map);  
            System.out.println("插入成功！");  
        }

      //  5.批量插入List<Map<String,Object>>数据
        @Test  
        public void testInsertUserListMap() throws IOException { 
            List<Map<String,Object>> list =new ArrayList<Map<String,Object>>();
            Map<String,Object> map = new HashMap<String,Object>(); 
            map.put("id",23);
            map.put("name", "ui0");
            map.put("age", 21);
            list.add(map);
            session.insert("com.mapping.UserMapping.insertUserMapList" , list);  
            System.out.println("插入成功！");  
        }

        //6.返回值类型为List<Map<String,Object>>
            @Test  
            public void testSelectUserListData() throws IOException { 
            List<Map<String,Object>> list = session.selectList("com.mapping.UserMapping.ListMapData");
            for (int i=0;i<list.size();i++) {
                System.out.println(list.get(i).toString());

            }
            }

//9.批量删除
            @Test      
            public void testDeleteUserByList() throws IOException { 
//              List<Integer> userList =new ArrayList<Integer>();
//              userList.add(23);
//              userList.add(24);
                List<Users> userList = new ArrayList<Users>();
                Users user = new Users(); 
                user.setId(1);
                Users user2 = new Users(); 
                user2.setId(2);
                userList.add(user2);
                userList.add(user);
                session.delete("com.mapping.UserMapping.deleteUser",userList);
                System.out.println("删除成功！");  
            }

}

5.foreach知识小总结

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。

foreach元素的属性主要有 item，index，collection，open，separator，close。

item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list

2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

3.如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map