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

三、注解配置

1. CRUD基本注解

1.1 接口定义

1.2 测试

1.3 插入空值异常处理

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

2.1 一对一

2.1.1 java bean

2.1.2 接口

2.1.3 测试

2.2 一对多

2.2.1 java bean

2.2.2 接口

2.2.3 测试

2.3 多对多

2.3.1 java bean

2.3.2 接口

2.3.3 测试

3. Provider注解动态SQL

3.1 @InsertProvider

3.1.1 构建动态SQL类

3.1.2 接口

3.1.3 测试类

3.1 @SelectProvider

3.1.1 构建动态SQL类

3.1.2 接口

3.1.3 测试类

3.1 @UpdateProvider

3.1.1 构建动态SQL类

3.1.2 接口

3.1.3 测试类

3.1 @DeleteProvider

3.1.1 构建动态SQL类

3.1.2 接口

3.1.3 测试类

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读