视频观看地址：

1、Mapper.xml文件

1.1、insert、update、delete、select

          select userid,user_name as userName,age,pwd,sex,birthday from tb_user where userid = #{userid}  

1.2、resultMap元素

先使用resultMap解决数据库字段名和java属性名不一致的问题

修改mapper.xml文件

        
         
         
       
          select *     from tb_user  

为了测试完美，将驼峰命名关闭

日志文件

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 25899648.DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@18b3280]DEBUG - ==>  Preparing: select * from tb_user DEBUG - ==> Parameters: DEBUG - <==      Total: 5User [userid=8, userName=阿珂, pwd=123456, age=18, sex=女, birthday=Mon Aug 13 14:06:14 CST 2018]User [userid=2, userName=张三, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]User [userid=3, userName=李四, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]User [userid=4, userName=王五, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]User [userid=5, userName=赵六, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@18b3280]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@18b3280]DEBUG - Returned connection 25899648 to pool.

总结一下：解决数据库名和属性名一致的解决方案：

1.别名

2.驼峰命名

3.resultMap

1.3、获取自增长的id元素

修改mapper.xml文件

      
    
         insert into tb_user(userid,user_name,age,pwd,sex,birthday)    values(seq_user.nextval,#{userName},#{age},#{pwd},#{sex},#{birthday})  
    

测试代码中

@Test    public void testInsertUser() {        User vo = new User("阿珂3", "123456", 18, "女", new Date());        try {            userMapper.insertUser(vo);            //提交事务            sqlSession.commit();            System.out.println(vo.getUserid());        } catch (Exception e) {            e.printStackTrace();            sqlSession.rollback();        }    }

测试日志

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 1434803926.DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@55855ed6]DEBUG - ==>  Preparing: insert into tb_user(userid,user_name,age,pwd,sex,birthday) values(seq_user.nextval,?,?,?,?,?) DEBUG - ==> Parameters: 阿珂3(String), 18(Integer), 123456(String), 女(String), 2018-08-13 14:57:50.296(Timestamp)DEBUG - <==    Updates: 1DEBUG - Committing JDBC Connection [oracle.jdbc.driver.T4CConnection@55855ed6]10DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@55855ed6]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@55855ed6]DEBUG - Returned connection 1434803926 to pool.

2、参数传递

2.1、#{}方式

当参数有多个的时候，我们可以采用自然数，或者pojo对象

方式一：采用自然数传递

需求：按照用户的姓名和年龄进行查询数据

1、添加一个方法

public List
    
      queryByNameAndAge(String name,int age) throws Exception;
    

2、编写mapper.xml文件

        select * from tb_user where user_name like #{0} and age =#{1}  

3、执行测试代码

@Test    public void testQueryByNameAndAge() throws Exception{        List
    
      list = userMapper.queryByNameAndAge("%阿%", 18);        for (User user : list) {            System.out.println(user);        }    }
    

日志文件

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 707919597.DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@2a31feed]DEBUG - ==>  Preparing: select * from tb_user where user_name like ? and age =? DEBUG - ==> Parameters: %阿%(String), 18(Integer)DEBUG - <==      Total: 3User [userid=8, userName=阿珂, pwd=123456, age=18, sex=女, birthday=Mon Aug 13 14:06:14 CST 2018]User [userid=9, userName=阿珂2, pwd=123456, age=18, sex=女, birthday=Mon Aug 13 14:56:45 CST 2018]User [userid=10, userName=阿珂3, pwd=123456, age=18, sex=女, birthday=Mon Aug 13 14:57:50 CST 2018]DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@2a31feed]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@2a31feed]DEBUG - Returned connection 707919597 to pool.

实际上还可以同param传递，但是param在传递的时候从1开始

修改mapper.xml

        select * from tb_user where user_name like #{param1} and age =#{param2}  

但是以上方式实际上可读性并不高，一般我们可以采用注解的形式：

方式二：采用注解的方式传递离散的参数

接口的声明如下

public List
    
      queryByNameAndAge(@Param("name")String name,@Param("age")int age) throws Exception;
    

mapper.xml文件就可以按如下方式编写

        select * from tb_user where user_name like #{name} and age =#{age}  

方式三：直接传递一个pojo对象

1、首先创建一个查询对象，这个对象中封装了查询需要的参数

package cn.org.kingdom.pojo;import java.io.Serializable;public class QueryUser implements Serializable {    private String name ;     private int age ;    public QueryUser() {        super();    }    public QueryUser(String name, int age) {        super();        this.name = name;        this.age = age;    }    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;    } }

2、编写接口方法

public List
    
      queryByPojo(QueryUser  vo) throws Exception;
    

3、编写mapper.xml文件

     select * from tb_user where user_name like #{name} and age =#{age}  

4、测试

@Test    public void testQueryByNameAndAge2() throws Exception{        QueryUser vo = new QueryUser("%阿%", 18);        List
    
      list = userMapper.queryByPojo(vo);        for (User user : list) {            System.out.println(user);        }    }
    

总结：一般情况下在开发中，会优先选择注解和pojo的方式来进行开发，关于自然数的方式，大家了解即可

2.2、${}方式

需求：

数据库有两个一模一样的表。历史表，当前表

查询表中的信息，有时候从历史表中去查询数据，有时候需要去新的表去查询数据。

希望使用 1 个方法来完成操作

接口方法定义

public List
    
      queryByTableName(@Param("tableName")String tableName) throws Exception;
    

mapper.xml文件定义

    select * from #{tableName}  

测试类代码

@Test    public void testQuerybyTableName() throws Exception{        List
    
      list = userMapper.queryByTableName("tb_user_hi");        for (User user : list) {            System.out.println(user);        }    }
    

运行：

org.apache.ibatis.exceptions.PersistenceException: ### Error querying database.  Cause: java.sql.SQLException: ORA-00903: 表名无效### The error may exist in cn/org/kingdom/mapper/UserMapper.xml### The error may involve defaultParameterMap### The error occurred while setting parameters### SQL: select * from ?### Cause: java.sql.SQLException: ORA-00903: 表名无效    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)    at com.sun.proxy.$Proxy4.queryByTableName(Unknown Source)    at cn.org.kingdom.test.MyBatisTest01.testQuerybyTableName(MyBatisTest01.java:120)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)    at java.lang.reflect.Method.invoke(Unknown Source)    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)    at org.junit.runners.BlockJUnit4Cla***unner.runChild(BlockJUnit4Cla***unner.java:70)    at org.junit.runners.BlockJUnit4Cla***unner.runChild(BlockJUnit4Cla***unner.java:50)    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

发现有问题，实际上#{}方式通过？形式进行传递参数的，？它不支持tableName

将#{}换成${}

    select * from ${tableName}  

再次运行

DEBUG - Opening JDBC ConnectionDEBUG - Created connection 1891119713.DEBUG - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@70b83261]DEBUG - ==>  Preparing: select * from tb_user_hi DEBUG - ==> Parameters: DEBUG - <==      Total: 2User [userid=2, userName=张三, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]User [userid=3, userName=李四, pwd=123456, age=10, sex=男, birthday=Mon Aug 13 10:07:55 CST 2018]DEBUG - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@70b83261]DEBUG - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@70b83261]DEBUG - Returned connection 1891119713 to pool.

总结：

#{} :表示sql中的参数部分，实际上底层使用的是PreparedStatement

${}:表示字符串拼接，实际上底层采用的Statement对象

能使用#{}的地方都可以使用${}代替，但是能使用${}的地方不一定能够用#{}来替代