NO IMAGE

大資料正式35

MyBatis

  • JDBC缺點

    1. 需要頻繁開閉資料庫
    2. 查詢結果需要人為進行封裝
    3. JDBC沒有快取
    4. Sql寫在java檔案
  • MyBatis優點

    1. 內建資料庫連線池
    2. 自動封裝資料
    3. 有快取
    4. Sql寫在配置檔案中

流程

入門例項

  • 顯示user

    • 目錄結構

    • 依賴jart包

    • 程式碼

      • com.peng.pojo

        • User

          package com.peng.pojo; public class User { private int id; private String name; private int age;

              public User() {
          super();
          }
          public User(int id, String name, int age) {
          super();
          this.id = id;
          this.name = name;
          this.age = age;
          }
          public int getId() {
          return id;
          }
          public void setId(int 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 "編號:"   id   ",姓名:"   name   ",年齡:"   age;
          }
          }
          
        • UserMapper

          <?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="UserMapper">
          <select id="findAll" resultType="com.peng.pojoUser">select * from user</select>
          </mapper>
          
      • com.peng.test

        • Test

          package com.peng.test;
          import java.io.InputStream;
          import java.util.List;
          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.peng.pojo.User;
          public class Test {
          @org.junit.Test
          public void findAllTest() throws Exception {
          // 載入配置檔案
          InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
          // 建立會話工廠
          SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
          // 建立會話物件
          SqlSession openSession = factory.openSession();
          // 執行SQL
          List<User> selectList = openSession.selectList("UserMapper.findAll");
          for (User user : selectList) {
          System.out.println(user);
          }
          }
          }
          
      • src下的配置檔案

        • sqlMapConfig.xml

          <?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="mysql">
          <environment id="mysql">
          <transactionManager type="JDBC" />
          <dataSource type="POOLED">
          <property name="driver" value="com.mysql.jdbc.Driver" />
          <property name="url"
          value="jdbc:mysql://localhost:3306/mybatis_db?characterEncoding=utf-8" />
          <property name="username" value="root" />
          <property name="password" value="root" />
          </dataSource>
          </environment>
          <environment id="oracle">
          <transactionManager type="JDBC" />
          <dataSource type="POOLED">
          <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
          <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:XE" />
          <property name="username" value="ht1602" />
          <property name="password" value="htdb" />
          </dataSource>
          </environment>
          </environments>
          <!-- 對映檔案 -->
          <mappers>
          <mapper resource="com/peng/pojo/UserMapper.xml" />
          </mappers>
          </configuration>
          
        • log4j.properties

          log4j.rootLogger=DEBUG, Console
          #Console
          log4j.appender.Console=org.apache.log4j.ConsoleAppender
          log4j.appender.Console.layout=org.apache.log4j.PatternLayout
          log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
          log4j.logger.java.sql.ResultSet=INFO
          log4j.logger.org.apache=INFO
          log4j.logger.java.sql.Connection=DEBUG
          log4j.logger.java.sql.Statement=DEBUG
          log4j.logger.java.sql.PreparedStatement=DEBUG
          

注:

  • openSession(true):每一次sql都提交
  • 取值符號

    • #{id}會自動加引號–有有預編譯效果(防止sql注入)
    • ${id}不加引號
    • 不加引號的地方—-order by 屬性名

單值傳遞和多值傳遞

  • 單值:可以寫任意引數代表傳的引數
  • 多值:和原引數一致

複用

<?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="UserMapper">
<sql id="selectUser">select * from user</sql>
<select id="findAll" resultType="User">
<include refid="selectUser"></include>
</select>
</mapper>

包的別名

<!-- 別名 -->
<typeAliases>
<typeAlias type="com.peng.pojo.User" alias="User" />
</typeAliases>

動態更新

  • 普通

    <update id="updateByID">
    update user set name=#{name},age=#{age}
    </update>   
    
  • set標籤–去掉多餘逗號
  • 例子

    <update id="updateByID">
    update user
    <set>
    <if test="name!=null">name=#{name},</if>
    <if test="age!=null">age=#{age},</if>
    </set>
    </update>
    

動態查詢

  • 普通

    <sql id="selectUser">select * from user</sql>
    <select id="findAll" resultType="User">
    <include refid="selectUser"></include>
    where name=#{name} and age=#{age}
    </select>
    
  • where–去掉多餘的and
  • 動態

    <sql id="selectUser">select * from user</sql>
    <select id="findAll" resultType="User">
    <include refid="selectUser"></include>
    <where>
    <if test="name!=null">and name=#{name}</if>
    <if test="age!=null">and age=#{age}</if>
    </where>
    </select>
    

注:and放前面;逗號放後面

動態插入

<!-- 動態插入 -->
<insert id="insertBy">
insert into user
<trim prefix="(" suffix=")" suffixOverrides=","><!--去掉多餘逗號 -->
<if test="name!=null">name,</if>
<if test="age!=null">age,</if>
</trim>
</insert>
</mapper>

批量刪除之陣列形式

  • UserMapper

       <!-- 批量刪除:陣列 -->
    <delete id="deleteSome">
    delete from user where id in
    <foreach collection="array" open="(" close=")" separator=","<!-- 拼接-->
    item="id">#{id}
    </foreach>
    </delete>
    
  • Test

    // 通過id陣列來批量刪除
    @org.junit.Test
    public void DeleteSomeTest() {
    int[] ids = { 2, 3, 4 };
    openSession.delete("UserMapper.deleteSome_array", ids);
    }
    

批量刪除之List形式

  • UserMapper

    <!-- 批量刪除:集合 -->
    <delete id="deleteSome_list">
    delete from user where id in
    <foreach collection="list" open="(" close=")" separator=","
    item="user">#{user.id}</foreach>
    </delete>
    
  • Test

    // 通過集合來批量刪除
    @org.junit.Test
    public void DeleteSomeTest2() {
    List<User> user_list = new ArrayList<User>();
    // 建立資料
    for (int i = 1; i < 8; i  ) {
    user_list.add(new User(i, "a"   i, i));
    }
    openSession.delete("UserMapper.deleteSome_list", user_list);
    }
    

補充