1.項目相關(guān)路徑
圖片.png
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的工具類
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.創(chuàng)建實體類以及對應(yīng)的Mapping.xml文件
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的版本中，有幾點(diǎn)需要注意的：
1.SQL中沒有VALUES赚抡；
2.<foreach>標(biāo)簽中的(selece ..... from dual)蠢甲；
3.<foreach>標(biāo)簽中的separator的屬性為"UNION ALL"掷匠，將查詢合并結(jié)果集滥崩。 -->
<!-- 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.查詢（數(shù)字對應(yīng)Mapping.xml的數(shù)字方法）
@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.根據(jù)id查詢數(shù)據(jù)
@Test
public void testSelectUserById() throws IOException {
Users user = (Users) session.selectOne("com.mapping.UserMapping.getUserById",1);
System.out.println("each->" + user.toString());
}
//8.根據(jù)id刪除數(shù)據(jù)
@Test
public void testDeleteUserById() throws IOException {
session.delete("com.mapping.UserMapping.deleteUserById",1);
System.out.println("刪除成功！");
}
// 10.更新數(shù)據(jù)
@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>>數(shù)據(jù)
@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("刪除成功短条！");
}
}
foreach知識總結(jié)
foreach的主要用在構(gòu)建in條件中，它可以在SQL語句中進(jìn)行迭代一個集合才菠。
foreach元素的屬性主要有 item茸时，index，collection赋访，open可都，separator，close蚓耽。
item表示集合中每一個元素進(jìn)行迭代時的別名渠牲，index指定一個名字，用于表示在迭代過程中步悠，每次迭代到的位置签杈，open表示該語句以什么開始，separator表示在每次進(jìn)行迭代之間以什么符號作為分隔符鼎兽，close表示以什么結(jié)束答姥，在使用foreach的時候最關(guān)鍵的也是最容易出錯的就是collection屬性铣除，該屬性是必須指定的，但是在不同情況 下踢涌，該屬性的值是不一樣的，主要有一下3種情況：
1.如果傳入的是單參數(shù)且參數(shù)類型是一個List的時候序宦，collection屬性值為list
2.如果傳入的是單參數(shù)且參數(shù)類型是一個array數(shù)組的時候睁壁，collection的屬性值為array
3.如果傳入的參數(shù)是多個的時候，我們就需要把它們封裝成一個Map了互捌，當(dāng)然單參數(shù)也可以封裝成map
