mybatis前因后果(一)mybaitis前因

Dear 丶 2022-11-02 13:15 103阅读 0赞

## 引言 ##

### 原始jdbc操作 ###

#### 直接进行裸操作 ####

public class JdbcTest { 
        public static void main(String[] args) throws ClassNotFoundException { 
            Connection connection = null;
            Statement statement = null;
            try { 
                Class.forName("com.mysql.jdbc.Driver");
                /*connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test2", "root","root");*/
                 connection = DriverManager.getConnection("jdbc:mysql:///test2",
                        "root", "root");
                statement = connection.createStatement();
                String  sql = "update account set balance = 1000; -- where id =1;";
    
                int i = statement.executeUpdate(sql);
                if(i<1){ 
                    System.out.println("执行失败!!!");
                }else{ 
                    System.out.println(i);
                    System.out.println("执行成功!!!");
                }
    
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }finally { 
                if(statement !=null){ 
                    try { 
                        statement.close();
                    } catch (SQLException throwables) { 
                        throwables.printStackTrace();
                    }
                }
                if(connection !=null){ 
                    try { 
                        connection.close();
                    } catch (SQLException throwables) { 
                        throwables.printStackTrace();
                    }
                }
            }
        }
    }

直接操作会照成代码重复编写，并且采用Statment会造成sql注入问题。

#### 抽取共性，采用preparestatement防止sql注入 ####

以前面章节学习的转账案例为例。  
首先写工具类

public class JDBCUtils { 
        private  static  String url;
        private  static  String username;
        private  static  String password;
        private  static  String driver;
        //静态代码块，加载资源文件
        static { 
            try { 
                Properties properties = new Properties();
                //加载文件
                ClassLoader classLoader = JDBCUtils.class.getClassLoader();
                URL resource = classLoader.getResource("jdbc.properties");
                String path = resource.getPath();
                System.out.println(path);
                properties.load(new FileReader(path));
                url = properties.getProperty("url");
                username = properties.getProperty("username");
                password = properties.getProperty("password");
                driver = properties.getProperty("driver");
                Class.forName(driver);
            } catch (IOException e) { 
                e.printStackTrace();
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            }
        }
    
        public static  Connection  getConnection(){ 
            Connection connection = null;
            try { 
                 connection = DriverManager.getConnection(url, username, password);
            } catch (SQLException throwables) { 
                throwables.printStackTrace();
            }finally { 
                return connection;
            }
        }
    
        public static void close(Statement statement, Connection connection){ 
            if(statement != null){ 
                try { 
                    statement.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
            }
            if(connection != null){ 
                try { 
                    connection.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
            }
        }
    
        public static void close(ResultSet resultSet, Statement statement, Connection connection){ 
            if(resultSet != null){ 
                try { 
                    resultSet.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
            }
            if(statement != null){ 
                try { 
                    statement.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
            }
            if(connection != null){ 
                try { 
                    connection.close();
                } catch (SQLException throwables) { 
                    throwables.printStackTrace();
                }
            }
        }
    }

测试类

public class JDBC05Test { 
        public static void main(String[] args) { 
            System.out.println("----------交易开始前----------");
            List<Emp> all = findAll();
            for (Emp emp :
                    all) { 
                System.out.println(emp);
            }
            transaction("zhangsan","lisi",500);
            System.out.println("------交易完成后-------");
            all=findAll();
            for (Emp emp :
                    all) { 
                System.out.println(emp);
            }
        }
    
        public static boolean transaction(String user1, String user2, double val ){ 
            Connection connection =null;
            PreparedStatement preparedStatement1 = null, preparedStatement2=null;
            ResultSet resultSet = null;
            try { 
                connection = JDBCUtils.getConnection();
                connection.setAutoCommit(false);
                String sql1 = " update account set balance = balance - ? where username = ?";
                String sql2 = " update account set balance = balance + ? where username = ?";
                preparedStatement1 = connection.prepareStatement(sql1);
                preparedStatement2 = connection.prepareStatement(sql2);
                preparedStatement1.setDouble(1,val);
                preparedStatement1.setString(2,user1);
                preparedStatement2.setDouble(1,val);
                preparedStatement2.setString(2,user2);
    
                int i = preparedStatement1.executeUpdate();
                int i2= 4/0;
                int i1 = preparedStatement2.executeUpdate();
                connection.commit();
    
            } catch (Exception e) { 
                try { 
                    if(connection !=null){ 
                        connection.rollback();
                    }
                } catch (SQLException sqlException) { 
                    sqlException.printStackTrace();
                }
                e.printStackTrace();
                return false;
            }finally { 
                JDBCUtils.close(resultSet,preparedStatement1,connection);
                JDBCUtils.close(resultSet,preparedStatement2,connection);
            }
            return  true;
        }
    
        public static List<Emp> findAll(){ 
            Connection connection =null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            LinkedList<Emp> emps =null;
            try { 
                connection = JDBCUtils.getConnection();
                String sql = "select * from account;";
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                emps = new LinkedList<>();
                while (resultSet.next()){ 
                    Emp emp = new Emp();
                    emp.setId(resultSet.getInt(1));
                    emp.setUsername(resultSet.getString("username"));
                    emp.setBalance(resultSet.getDouble("balance"));
                    emp.setPassword("password");
                    emps.add(emp);
                }
            } catch (SQLException e) { 
                e.printStackTrace();
            }finally { 
                JDBCUtils.close(resultSet,preparedStatement,connection);
                return emps;
            }
        }
    }

上述代码会在sql执行过程中重复的去建立和销毁数据库连接，这会导致大量的时间资源浪费

### 数据库连接池 ###

为解决资源浪费问题，就诞生了采用数据库池的解决方案

public class JDBCUtils { 
        private static DataSource dataSource;
        static { 
            try { 
                InputStream res = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
                Properties properties = new Properties();
                properties.load(res);
                 dataSource = DruidDataSourceFactory.createDataSource(properties);
            } catch (IOException e) { 
                e.printStackTrace();
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    
        public static Connection getConnection() throws SQLException{ 
            return  dataSource.getConnection();
        }
    
    
        public static void close(Statement statement, ResultSet resultSet,Connection connection){ 
            if (statement!=null){ 
                try { 
                    statement.close();
                } catch (SQLException sqlException) { 
                    sqlException.printStackTrace();
                }
            }
    
            if(resultSet!=null){ 
                try { 
                    resultSet.close();
                } catch (SQLException sqlException) { 
                    sqlException.printStackTrace();
                }
            }
            if(connection !=null){ 
                try { 
                    connection.close();
                } catch (SQLException sqlException) { 
                    sqlException.printStackTrace();
                }
            }
        }
        public static void close(Statement statement, Connection connection){ 
            if(connection !=null){ 
                try { 
                    connection.close();
                } catch (SQLException sqlException) { 
                    sqlException.printStackTrace();
                }
            }
    
            if(connection !=null){ 
                try { 
                    connection.close();
                } catch (SQLException sqlException) { 
                    sqlException.printStackTrace();
                }
            }
        }
        public static DataSource  getDataSource(){ 
            return dataSource;
        }
    }

测试类

public class Druid01Demo { 
        public static void main(String[] args)  { 
            Connection connection = null;
            PreparedStatement preparedStatement =null;
            ResultSet resultSet = null;
            try { 
                connection = JDBCUtils.getConnection();
                System.out.println(connection);
                String sql = "select * from account where username = ? ";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,"lisi");
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){ 
                    System.out.println(resultSet.getInt("id")+"------>"
                            +resultSet.getString("username")+"--------->"+
                            resultSet.getDouble("balance")+"---------->"+
                            resultSet.getString("password"));
                }
                connection = JDBCUtils.getConnection();
            } catch (SQLException sqlException) { 
                sqlException.printStackTrace();
            }finally { 
                JDBCUtils.close(preparedStatement,resultSet,connection);
            }
        }
    }

properties配置文件

driverClassName=com.mysql.jdbc.Driver
    url=jdbc:mysql:///test2
    username=root
    password=root
    # 初始化连接数量
    initialSize=5
    # 最大连接数
    maxActive=10
    # 最大等待时间
    maxWait=3000

**经过上述配置似乎一切变得十分完美，可惜还是有一些缺点，吃出的缺点包含两个方面.**

1.  上述方式无法避免sql硬编码
2.  查询的数据需要手动封装，产生较多的冗余代码。

为解决上述问题，古圣先贤们提出了一种比较完美的解决方案。

1.  将sql语句抽取到xml配置文件中
2.  使用反射、内省等底层技术，自动将实体与表进行属性与字段的自动映射。  
    mybatis框架由此而生。

## 什么是mybatis ##

mybatis 是一个优秀的基于java的持久层框架，**它内部封装了jdbc，使开发者只需要关注sql语句本身，而不需要花费精力去处理加载驱动、创建连接、创建statement等繁杂的过程**。  
mybatis通过xml或注解的方式将要执行的各种 statement配置起来，并通过java对象和statement中sql的动态参数进行映射生成最终执行的sql语句。  
最后mybatis框架执行sql并将结果映射为java对象并返回。**采用ORM思想解决了实体和数据库映射的问题，对jdbc 进行了封装，屏蔽了jdbc api 底层访问细节，使我们不用与jdbc api 打交道，就可以完成对数据库的持久化操作**。

## mybatis框架执行过程 ##

下图详细叙述了mybatis的执行过程:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]  
下面是原始sql执行的实例代码:  
properties配置文件

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/test
    jdbc.username=root
    jdbc.password=root

UserMapper.xml

<?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">
        
        <delete id="delete" parameterType="java.lang.Integer">
            delete from user where id=#{abc}
        </delete>
        
        <update id="update" parameterType="com.feitian.pojo.User">
            update user set username=#{username},password=#{password} where id=#{id}
        </update>
        
        <insert id="save" parameterType="com.feitian.pojo.User">
            insert into user values(#{id},#{username},#{password})
        </insert>
        
        <select id="findAll" resultType="user">
            select * from user
        </select>
        
        <select id="findById" resultType="user" parameterType="java.lang.Integer">
            select * from user where id=#{id}
        </select>
    </mapper>

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>
        
        <properties resource="jdbc.properties"></properties>
        
        <typeAliases>
            <typeAlias type="com.feitian.pojo.User" alias="user"></typeAlias>
        </typeAliases>
        
        <environments default="developement">
            <environment id="developement">
                <transactionManager type="JDBC"></transactionManager>
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
            <mapper resource="com/feitian/mapper/UserMapper.xml"></mapper>
        </mappers>
    </configuration>

对应的增删改查测试类

public class Mybatis01Demo { 
    
        @Test
        public void test01() throws IOException { 
            //加载核心配置文件
            InputStream rs = Resources.getResourceAsStream("sqlMapConfig.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(rs);
            //获得会话对象
            SqlSession sqlSession = sqlSessionFactory.openSession();
            List<User> userList = sqlSession.selectList("userMapper.findAll");
            System.out.println(userList);
            sqlSession.close();
        }
    
    
        @Test
        //插入操作
        public void test02() throws IOException { 
            //模拟user对象
            User user = new User();
            user.setId(1);
            user.setUsername("java");
            user.setPassword("1234");
    
            //获得核心配置文件
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
            //获得session工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            //获得session回话对象
            SqlSession sqlSession = sqlSessionFactory.openSession(true);
            //执行操作 参数：namespace+id
            sqlSession.insert("userMapper.save",user);
            //mybatis执行更新操作 提交事务
            sqlSession.commit();
            //释放资源
            sqlSession.close();
        }
    
    
        @Test
        //修改操作
        public void test3() throws IOException { 
    
            //模拟user对象
            User user = new User();
            user.setId(7);
            user.setUsername("lucy");
            user.setPassword("123");
    
            //获得核心配置文件
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
            //获得session工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            //获得session回话对象
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //执行操作 参数：namespace+id
            sqlSession.update("userMapper.update",user);
            //mybatis执行更新操作 提交事务
            sqlSession.commit();
            //释放资源
            sqlSession.close();
        }
    
    
        @Test
        //删除操作
        public void test4() throws IOException { 
    
            //获得核心配置文件
            InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
            //获得session工厂对象
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
            //获得session回话对象
            SqlSession sqlSession = sqlSessionFactory.openSession();
            //执行操作 参数：namespace+id
            sqlSession.delete("userMapper.delete",8);
    
            //mybatis执行更新操作 提交事务
            sqlSession.commit();
    
            //释放资源
            sqlSession.close();
        }
    }

从mybatis设计思想上看，mybatis整个框架分工十分明确，且将sql进行集中统一管理，这种思路的优点使得修改sql的成本大幅度降低，增加了项目开发的灵活性，一定程度上降低了开发难度。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZXNoYW5mZWl0aWFu_size_16_color_FFFFFF_t_70]: /images/20221024/2f53b121d014430690bc3ba9eb21510d.png