大数据正式35

大数据正式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);
  }
  

补充

•