大数据正式35

末蓝、 2022-06-02 00:18 113阅读 0赞

# 大数据正式35 #

### MyBatis ###

*  JDBC缺点
    
    1.  需要频繁开闭数据库
    2.  查询结果需要人为进行封装
    3.  JDBC没有缓存
    4.  Sql写在java文件
 *  MyBatis优点
    
    1.  内置数据库连接池
    2.  自动封装数据
    3.  有缓存
    4.  Sql写在配置文件中

### 流程 ###

*  ![J3rjG0p.png][]

### 入门实例 ###

*  显示user
    
     *  目录结构
        
         *  ![gzkdTpw.png][]
     *  依赖jart包
        
         *  ![NoqWl9m.png][]
     *  代码
        
         *  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);
        }

### 补充 ###

*  ![TpDDvp4.jpg][]

[J3rjG0p.png]: https://i.imgur.com/J3rjG0p.png
[gzkdTpw.png]: https://i.imgur.com/gzkdTpw.png
[NoqWl9m.png]: https://i.imgur.com/NoqWl9m.png
[TpDDvp4.jpg]: https://i.imgur.com/TpDDvp4.jpg