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
随时随地看视频
