美文网首页
mybatis 注解使用 Day14 2018-12-02

mybatis 注解使用 Day14 2018-12-02

作者: Ernest_Chou | 来源:发表于2018-12-02 03:15 被阅读0次

三、注解配置

  • select。映射查询SQL语句
  • selectProvider。select语句的动态SQL映射。
  • insert。映射插入的SQL语句
  • insertProvider。insert语句的动态SQL映射。
  • update。映射更新的SQL语句。
  • updateProvider。
  • delete。映射删除的SQL语句。
  • deleteProvider。
  • result。在列和属性直接的单独结果映射。属性包括：id、column、property、javaType、jdbcTpye、type Handler、one、many。id是一个布尔值，表示十分被用于主键映射。one属性是单独的联系，与<association>相似。many属性是对集合而言的，与<collection>相似。
  • results。多结果映射（result）列表。
  • options。提供配置选项的附加值。@Options(userGenerateKeys=true,keProperty="id")
  • one。复杂类型的单独属性映射。必须制定select属性。表示已映射SQL语句的完全限定名。
  • many。复杂类型的集合属性映射。必须制定select属性。表示已映射SQL语句的完全限定名。
  • param。用于映射器方法来给每个参数取一个名字。否则，多参数将会以它们的顺序位置和SQL语句中的表达式进行映射。
  • SelectKey。用于生产主键。
    • 属性statement="select max(empNo)+1 as myNo from emp":表示定义的子查询语句
      • before=true：表示在之前执行，booler类型的,所以为true
      • keyColumn="myNo":表示查询所返回的类名
      • resultType=int.class：表示返回值得类型
      • keyProperty="empNo"：表示将该查询的属性设置到某个列中，此处设置到empNo中

1. CRUD基本注解

1.1 接口定义

  • WebUserDao.java
public interface WebUserDao {
     /*
     * statement="select max(empNo)+1 as myNo from emp":表示定义的子查询语句
     * before=true：表示在之前执行，booler类型的,所以为true
     * keyColumn="myNo":表示查询所返回的类名
     * resultType=int.class：表示返回值得类型
     * keyProperty="empNo" ：表示将该查询的属性设置到某个列中，此处设置到empNo中
     */
    @Insert("insert into web_user (id, username,loginname,password, phone, address) values (#{id},#{username},#{loginname},#{password},#{phone},#{address})")
    @SelectKey(statement="select SQ_PHONE_DEPARTMENT_ID.nextval as id from dual",before=true,keyColumn="id",resultType=int.class,keyProperty="id" )
    int saveUser(WebUser user);
  
    @Select("select * from web_user where id = #{id}")
    @Results({
        @Result(column="id",property="id"),
        @Result(column="username",property="username"),
        @Result(column="loginname",property="loginname"),
        @Result(column="password",property="password"),
        @Result(column="phone",property="phone"),
        @Result(column="address",property="address"),
    })
    WebUser selectUserById(int id);
    @Update("update web_user set phone=#{phone}, password=#{password},address=#{address} where id=#{id}")
    int modifyUser(WebUser user);
    
    @Delete("delete from web_user where id=${id}")
    int deleteUser(@Param("id") int id);

}

1.2 测试

  • CrudAnnotationTest.java
public class CrudAnnotationTest {

        public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        WebUserDao userDao = session.getMapper(WebUserDao.class);
        CrudAnnotationTest test = new CrudAnnotationTest();
        //test.testInsert(userDao);
        //test.testUpdate(userDao);
        //test.testDelete(userDao);
        test.testSelect(userDao);
        
        session.commit();
        session.close();
    }
    
    public void testInsert(WebUserDao userDao) {
        WebUser user = new WebUser();
        user.setUsername("杨涵");
        user.setLoginname("Yanhan");
        user.setPassword("123456");
        user.setAddress("石各庄");
        userDao.saveUser(user);
        System.out.println(user.getId());
    }
    
    public void testSelect(WebUserDao userDao) {
        WebUser user = userDao.selectUserById(8);
        System.out.println(user.toString());
    }
    public void testUpdate(WebUserDao userDao) {
        WebUser user = new WebUser();
        user.setPhone("16677778888");
        user.setPassword("666666");
        user.setAddress("石家庄");
        user.setId(7);
        int num = userDao.modifyUser(user);
        System.out.println(num);
    }
    
    public void testDelete(WebUserDao userDao) {
        int num = userDao.deleteUser(7);
        System.out.println(num);
    }


}

1.3 插入空值异常处理

解决插入空值产生的异常 Error setting null for parameter #5 with JdbcType OTHER .
说明：postgreSQL，MySQL，SQLSERVER都支持JdbcType.NULL类型，Oracle是不支持，适配的时候也因为这个问题导致mybatis报错。

解决方法：

  • MyBatis-config.xml 中设置当JDBC类型为空值时，要指定的值得，默认为OTHER，我们指定为NULL就好了（注意是大写的NULL）。
<!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER -->
<setting name="jdbcTypeForNull" value="NULL"/>
  • 在每个数据变量那里设置相应的jdbcType
<select id="selectUserById" parameterType="int" resultMap="BaseResultMap">
    select * from WEB_USER
    where ID = #{id,jdbcType=DECIMAL}
  </select>

2. 一对一、一对多、多对多映射

2.1 一对一

2.1.1 java bean
  • 同（二） 6.2.1
2.1.2 接口
  • PersonDao.java
public interface PersonDao {
    @Select("select * from person where id = #{id}")
    @Results({
        @Result(column="ID" ,property="id"),
        @Result(column="NAME",property="name"),
        @Result(column="SEX" ,property="sex"),
        @Result(column="AGE",property="age"),
        @Result(column="card_id",property="card",//表示person的card属性对于表的card_id列
        one=@One(
                select="com.zhougl.web.annotation.dao.CardDao.selectCardById",
                fetchType=FetchType.EAGER))
    })
    Person selectPersonById(Integer id);

}
  • CardDao.java
public interface CardDao {
    @Select("select * from Card where id = #{id}")
    Card selectCardById(Integer id);
}
2.1.3 测试
public void testOneToOne(SqlSession session) {
         PersonDao dao = session.getMapper(PersonDao.class);
         Person person =dao.selectPersonById(1);
         System.out.println(person.toString());
         System.out.println(person.getCard().toString());
    }

2.2 一对多

2.2.1 java bean
  • 同（二） 6.1.1
2.2.2 接口
  • WebClassDao.java
public interface WebClassDao {
    @Select("select * from WEB_CLASS where ID = #{id}")
    @Results({
        @Result(column="ID",property="id" ),
        @Result(column="CODE",property="code"),
        @Result(column="NAME",property="name"),
        @Result(column="id",property="students",
        many=@Many(
                select="com.zhougl.web.annotation.dao.StudentDao.selectStudentByClassId",
                fetchType=FetchType.LAZY))
    })
    WebClass selectWebClassById(int id);
}
  • StudentDao.java
public interface StudentDao {
   
    //Student selectStudentById(int id);
    @Select("select * from STUDENT  where CLASS_ID = #{classId}")
    @Results({
        @Result(column="Id",property="id"),
        @Result(column="name",property="name"),
        @Result(column="SEX",property="sex"),
        @Result(column="AGE",property="age")
    })
    List<Student> selectStudentByClassId(int classId);

}
2.2.3 测试
public void testOneToMany(SqlSession session) {
        WebClassDao dao = session.getMapper(WebClassDao.class);
         WebClass webClass = dao.selectWebClassById(1);
         System.out.println(webClass.toString());
         webClass.getStudents().forEach(student ->System.out.println(student.toString()));
    }

2.3 多对多

2.3.1 java bean
  • 同（二）7.4.1
2.3.2 接口
  • WebUserDao.java
public interface WebUserDao {
WebUser selectUserById(int id);
    @Update("update web_user set phone=#{phone}, password=#{password},address=#{address} where id=#{id}")
    int modifyUser(WebUser user);
}
  • WebArticleDao.java
public interface WebArticleDao {
    @Select("select  *  from WEB_ARTICLE where id in( select article_id from WEB_ITEM where order_id =#{orderId} )")
    List<WebArticle> selectArticleByOrderId(int orderId);
}
  • WebOrderDao.java
public interface WebOrderDao {
    @Select("select * from WEB_ORDER where id = #{id}")
    @Results({
        @Result(column="id",property="id"),
        @Result(column="CODE",property="code"),
        @Result(column="TOTAL",property="total"),
        @Result(column="user_id",property="user",
        one=@One(select="com.zhougl.web.annotation.dao.WebUserDao.selectUserById",fetchType=FetchType.EAGER)),
        @Result(column="id",property="articles" ,
        many=@Many(select="com.zhougl.web.annotation.dao.WebArticleDao.selectArticleByOrderId",fetchType=FetchType.LAZY)),
    })
    WebOrder selectOrderById(int id); 
}
2.3.3 测试
public void testManyToMany(SqlSession session) {
        WebOrderDao dao = session.getMapper(WebOrderDao.class);
         WebOrder order = dao.selectOrderById(1);
         System.out.println(order.toString());
         System.out.println(order.getUser().toString());
         order.getArticles().forEach(article ->System.out.println(article.toString()));
        
    }

//结果
WebOrder [id=1, code=201811300001, total=3666.65, userId=null]
WebUser [id=1, username=梁二燕, loginname=yangzi, password=123456, phone=15555556666, address=武汉]
WebArticle [id=1, name=花鸟画, price=1999.99, remark=梁老师著作]
WebArticle [id=2, name=肖像画, price=1666.66, remark=梁老师著作]

3. Provider注解动态SQL

3.1 @InsertProvider

3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
    
    public String insertEmployee(WebEmployee employee){
        return new SQL() {
            {
                INSERT_INTO("web_employee");
                if(employee.getId()!=null) {
                    VALUES("id", "#{id}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("loginname", "#{loginname}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("password", "#{password}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("name", "#{name}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("sex", "#{sex}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("age", "#{age}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("phone", "#{phone}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("sal", "#{sal}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("state", "#{state}");
                }
            }
        }.toString();
    }
}
3.1.2 接口
public interface WebEmployeeDao {
    @InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
    @SelectKey(statement="select max(id)+1 as id from web_employee",before=true,keyColumn="id",resultType=int.class,keyProperty="id" )
    int insertEmployee(WebEmployee employee);
}
3.1.3 测试类
public class DynamicSQLTest {
    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        WebEmployeeDao dao = session.getMapper(WebEmployeeDao.class);
        DynamicSQLTest test = new DynamicSQLTest();
        test.testInsertEmployee(dao);
        //test.testUpdateEmployee(dao);
        //test.testDeleteEmployee(dao);
        //test.testSelectWhithMap(dao);
        session.commit();
        session.close();
    }
    
    public void testInsertEmployee(WebEmployeeDao dao) {
        WebEmployee employee = new WebEmployee();
        employee.setName("菠萝赛东");
        employee.setLoginname("boluosaidong");
        employee.setPassword("123456");
        employee.setState("active");
        int num = dao.insertEmployee(employee);
        System.out.println(num);        
    }
  • 结果
==>  Preparing: select max(id)+1 as id from web_employee 
==> Parameters: 
<==      Total: 1
==>  Preparing: INSERT INTO web_employee (id, loginname, password, name, sex, age, phone, sal, state) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) 
==> Parameters: 6(Integer), boluosaidong(String), 123456(String), 菠萝赛东(String), null, null, null, null, active(String)
<==    Updates: 1
WebEmployee [id=6, loginname=boluosaidong, password=123456, name=菠萝赛东, sex=null, age=null, phone=null, sal=null, state=active]

3.1 @SelectProvider

3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
    
    public String selectWhithMap(Map<String, Object> map) {
        return new SQL(){
            {
                SELECT("*");
                FROM("web_employee");
                //多个参数时
                //SELECT * FROM web_employee WHERE ( id = ? AND loginname = ? and password = ? AND state = ? ) 
                if(map.get("id")!=null) {
                    WHERE(" id = #{id} ");
                }
                if(map.get("loginname")!=null && map.get("password")!=null) {
                    WHERE(" loginname = #{loginname} and password = #{password} ");
                }
                if(map.get("name")!=null) {
                    WHERE(" name = #{name} ");
                }
                if(map.get("phone")!=null) {
                    WHERE(" phone = #{phone} ");
                }
                if(map.get("state")!=null) {
                    WHERE(" state = #{state} ");
                }
            }
        }.toString();
    }
}
3.1.2 接口
public interface WebEmployeeDao {
    @SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhithMap")
    List<WebEmployee> selectEmployeeWithMap(HashMap<String, Object> map);
}
3.1.3 测试类
public class DynamicSQLTest {
    
    public void testSelectWhithMap(WebEmployeeDao dao) {
        HashMap<String, Object> map = new HashMap<String, Object>();
        //map.put("id", 1);
        //map.put("loginname", "rose");
        //map.put("password", "123456");
        map.put("state", "active");
        //map.put("state", "inactive");
        List<WebEmployee> employees = dao.selectEmployeeWithMap(map);
        employees.forEach(WebEmployee -> System.out.println(WebEmployee.toString()));
    }
}
  • 结果
==>  Preparing: SELECT * FROM web_employee WHERE ( state = ? ) 
==> Parameters: active(String)
<==      Total: 5
WebEmployee [id=1, loginname=jack, password=123456, name=苏文, sex=男, age=26, phone=15566665555, sal=9800, state=active]
WebEmployee [id=2, loginname=rose, password=123456, name=苏问问, sex=女, age=24, phone=16655556666, sal=6800, state=active]
WebEmployee [id=3, loginname=tom, password=123456, name=陈大山, sex=男, age=29, phone=17766665555, sal=8800, state=active]
WebEmployee [id=4, loginname=alice, password=123456, name=苏苏豆豆, sex=女, age=25, phone=19955556666, sal=5800, state=active]
WebEmployee [id=6, loginname=boluosaidong, password=123456, name=菠萝赛东, sex=null, age=null, phone=null, sal=null, state=active]

3.1 @UpdateProvider

3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
    
    public String updateEmployee(WebEmployee employee) {
        return new SQL() {
            {
                UPDATE("web_employee");
                if(employee.getLoginname()!=null) {
                    SET("loginname = #{loginname}");
                }
                if(employee.getLoginname()!=null) {
                    SET("password = #{password}");
                }
                if(employee.getLoginname()!=null) {
                    SET("name = #{name}");
                }
                if(employee.getLoginname()!=null) {
                    SET("sex = #{sex}");
                }
                if(employee.getLoginname()!=null) {
                    SET("age = #{age}");
                }
                if(employee.getLoginname()!=null) {
                    SET("phone = #{phone}");
                }
                if(employee.getLoginname()!=null) {
                    SET("sal = #{sal}");
                }
                if(employee.getLoginname()!=null) {
                    SET("state = #{state}");
                }
                WHERE(" id = #{id} ");
            }
        }.toString();
    }
}
3.1.2 接口
public interface WebEmployeeDao {
    @UpdateProvider(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
    int updateEmployee(WebEmployee employee);
}
3.1.3 测试类
public class DynamicSQLTest {
    public void testUpdateEmployee(WebEmployeeDao dao) {
        WebEmployee employee = new WebEmployee();
        employee.setId(6);
        employee.setName("菠萝赛西");
        employee.setLoginname("boluosaiXi");
        employee.setPassword("789012");
        employee.setState("active");
        int num = dao.updateEmployee(employee);
        System.out.println(num);        
    }       
}
  • 结果
==>  Preparing: UPDATE web_employee SET loginname = ?, password = ?, name = ?, sex = ?, age = ?, phone = ?, sal = ?, state = ? WHERE ( id = ? ) 
==> Parameters: boluosaiXi(String), 789012(String), 菠萝赛西(String), null, null, null, null, active(String), 6(Integer)
<==    Updates: 1

WebEmployee [id=6, loginname=boluosaiXi, password=789012, name=菠萝赛西, sex=null, age=null, phone=null, sal=null, state=active]

3.1 @DeleteProvider

3.1.1 构建动态SQL类
public class EmployeeDynaSqlProvider {
    
    public String deleteEmployee(WebEmployee employee) {
        return new SQL() {
            {
                DELETE_FROM("web_employee");
                if(employee.getId()!=null) {
                    WHERE(" id = #{id} ");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("loginname", "#{loginname}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("password", "#{password}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("name", "#{name}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("sex", "#{sex}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("age", "#{age}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("phone", "#{phone}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("sal", "#{sal}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("state", "#{state}");
                }
            }
        }.toString();
    }
}
3.1.2 接口
public interface WebEmployeeDao {
    @DeleteProvider(type=EmployeeDynaSqlProvider.class,method="deleteEmployee")
    int deleteEmployee(WebEmployee employee);
}
3.1.3 测试类
public class DynamicSQLTest {
    public void testDeleteEmployee(WebEmployeeDao dao) {
        WebEmployee employee = new WebEmployee();
        employee.setId(6);
        int num = dao.deleteEmployee(employee);
        System.out.println(num);        
    }       
}
  • 结果
==>  Preparing: DELETE FROM web_employee WHERE ( id = ? ) 
==> Parameters: 6(Integer)
<==    Updates: 1

相关文章

网友评论

      本文标题：mybatis 注解使用 Day14 2018-12-02

      本文链接：https://www.haomeiwen.com/subject/yubdcqtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|mybatis 注解使用 Day14 2018-12-02|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！