MySQL之MyBatis初体验3

MySQL之MyBatis初体验3

一、注解开发

1.1 注解与xml的比较

xml:作为配置文件

  • 优点:可以抽取内容(解耦),配置清晰 , 定位方便,易于寻找
  • 缺点:配置比较繁琐

注解:可以替换xml开发

  • 优点:开发速度快,不需要繁琐的配置
  • 缺点:散布在java代码的各个地方,不方便维护

SpringBoot使用的就是纯注解开发。

1.2 使用注解完成单表CRUD

手动映射: 如果数据库和实体类不一致 需要进行手动映射
@Results 手动映射的配置
@Results( value={ @Result() 数组})

接口:

public interface UserDao {
    //查询所有
    @Select("select * from tbl_user")
    List<User> findAll();
    //保存用户
    @Insert("insert into tbl_user(id,username,password,gender,email,telephone) values(#{id},#{username},#{password},#{gender},#{email},#{telephone})")
    int saveUser(User user);
    //修改用户
    @Update("update tbl_user set username=#{username},password=#{password},gender=#{gender},email=#{email},telephone=#{telephone} where id=#{id}")
    int updateUser(User user);
    //删除用户
    @Delete("delete from tbl_user where id = #{id}")
    int deleteUser(Integer id);


    /**
     * @Result 相当于一个字段
     * 主键, 普通字段 , 外键字段
     * @Result( column = "" , property = "" , id=true), 主键
     * column = ""  表示数据库的列
     * property = "" 实体类的属性值
     * id=true 此配置是主键配置
     * javaType:用于指定java中的类型
     * jdbcType:用于指定数据库的类型
     * one:用于建立多对一的关系
     * many:用于建立一对多的关系
	 * @ResultMap("myResult")  使用别的地方的映射配置
     */
    @Select("select id,username as name,password,gender,email,telephone from tbl_user")
    @Results( id="myResult",value={
            @Result( column = "id" , property = "id" , id=true),
            @Result( column = "name" , property = "username"),
            @Result( column = "password" , property = "password"),
            @Result( column = "gender" , property = "gender"),
            @Result( column = "email" , property = "email"),
            @Result( column = "telephone" , property = "telephone")
    })
    List<User> findAll2();


    @Select("select id,username as name,password,gender,email,telephone from tbl_user")
    @ResultMap("myResult")
    List<User> findAll3();

}

1.3 使用注解完成多表映射

1.3.1 一对一

接口:

public interface AccountDao {
    /**
     *
     * @Result(property = "实体类的属性" ,javaType = 实体类属性的类型" ,
     *                 one = @One(select = "")
     *                 one表示一对一
     *                      select: 定位另一条sql语句 namespace.id  接口的全限定类名.方法名称
     *             )
     */
    //查询所有的账户
    @Select("select * from tbl_account ")
    @Results(value={
            @Result(property = "id" , column = "id" , id = true),
            @Result(property = "username" , column = "username" ),
            @Result(property = "password" , column = "password" ),
            @Result(property = "gender" , column = "gender" ),
            @Result(property = "email" , column = "email" ),
            @Result(property = "telephone" , column = "telephone" ),
            @Result(property = "user" ,javaType = User.class ,
            one = @One(select = "com.baidu.dao.UserDao.findUserByUid") ,column = "uid")})
    List<Account> findAll();
}

1.3.2 一对多

接口:

public interface UserDao {
    /**
     * 多表配置
     *  @Result()
     *      property = "accounts" 封装的属性
     *      javaType = 集合的类型
     *      不需要声明泛型类型.(xml也不用 可以省略)-> 底层可以经过泛型反射进行实例化对象
     *       many = @Many 一对多配置
     *       select : "namespace.id" 接口的全限定类名.方法名称
     *       column = "id" 调用接口方法时候传入的id
     */
    //查询用户的方法
    @Select("select * from tbl_user")
    @Results(value={
            @Result(column = "id", property = "id" , id = true),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(column = "gender", property = "gender"),
            @Result(column = "email", property = "email"),
            @Result(column = "telephone", property = "telephone"),
            @Result(property = "accounts" , javaType = List.class ,
            many = @Many(select = "com.baidu.dao.AccountDao.findAccountByUid") , column = "id")})
    List<User> findAll();
}

1.3.3 懒加载

局部配置:

one = @One(select = "com.baidu.dao.UserDao.findUserByUid") ,column = "uid",fetchType=FetchType.LAZY)

many = @Many(select = "com.baidu.dao.AccountDao.findAccountByUid") , column = "id",fetchType=FetchType.LAZY)

在实际开发中,选择纯注解开发,xml开发还是混合开发都是根据实际需求来决定的。

二、MySQL经验总结

2.1 SQL语句报错

需求

查询国籍为中国并且学生总数大于2的每个地区的名字,学生的总数,数学(math)的总成绩,
数学(math)的平均成绩,并按照数学(math)的平均成绩倒叙排列

已知建表语句和数据如下

CREATE TABLE `student` (
`id` int(11) DEFAULT NULL COMMENT '主键',
`name` varchar(20) DEFAULT NULL COMMENT '名字',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`sex` varchar(5) DEFAULT NULL COMMENT '性别',
`address` varchar(100) DEFAULT NULL COMMENT '地区',
`math` int(11) DEFAULT NULL COMMENT '数学成绩',
`english` int(11) DEFAULT NULL COMMENT '英语成绩',
`nationality` varchar(10) DEFAULT NULL COMMENT '国籍'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into
`student`(`id`,`name`,`age`,`sex`,`address`,`math`,`english`,`nationality`)
values
(1,'马云',55,'男','杭州',66,78,'中国'),
(2,'化腾',45,'女','深圳',98,87,'中国'),
(3,'景涛',55,'男','香港',56,77,'中国'),
(4,'柳岩',20,'女','湖南',76,65,'中国'),
(5,'柳青',20,'男','湖南',86,NULL,'中国'),
(6,'刘德华',57,'男','香港',99,99,'中国'),
(7,'马德',22,'女','香港',99,99,'中国'),
(8,'德玛西亚',18,'男','南京',56,65,'中国'),
(9,'唐僧',25,'男','杭州',87,78,'中国'),
(10,'学友',18,'男','深圳',100,66,'中国'),
(11,'小明',22,'男','南京',58,78,'中国'),
(12,'星驰',50,'男','深圳',77,88,'中国'),
(13,'孟达',22,'女','杭州',66,66,'中国'),
(14,'孟德',23,'女','南京',88,88,'中国'),
(15,'Tom',33,'男','纽约',89,NULL,'美国'),
(16,'jerry',25,'男','休斯顿',85,NULL,'美国')

编写SQL语句

SELECT address, COUNT(*) AS total,SUM(math),AVG(math) AS avgmath
FROM student
GROUP BY address
WHERE nationality ='中国' AND total> 2
ORDER BY avgmath DESC

报错信息:
MySQL之MyBatis初体验3
问题分析

发现需求中有条件,就习惯的时候用where 来进行条件过滤。
上面的SQL语句中使用 where total>2 过滤总数,而total 是根据地区分组后计算得到的总数 。
错误的使用where 引发了上面报错

SQL语法规定:分组后得到的数据不能使用where过滤,而要使用having过滤

解决问题的办法

把where改为having,SQL修改为如下内容:

SELECT address, COUNT(*) AS total,SUM(math),AVG(math) AS avgmath
FROM student
WHERE nationality ='中国'
GROUP BY address
HAVING total> 2
ORDER BY avgmath DESC

总结

  1. 首先执行FROM子句,从student表检索记录;
  2. 如果有WHERE执行WHERE子句,筛选出student表中符合条件的所有记录;
  3. 执行GROUP BY子句,把student表按address分组;
  4. 计算COUNT(*)求出address相同的记录总数,计算SUM(math)求出address相同的记录中数学总成绩,计算AVG(math)求出address相同的记录中数学平均成绩;
  5. 执行HAVING子句,筛选出COUNT(*)大于2的分组;
  6. 提取address,``COUNT(*),SUM(math),AVG(math) 4个字段,产生新的结果集;
  7. 执行ORDER BY子句,把第6步的结果集按AVG(math)字段倒序排列

2.1 jdbc报错

Java代码如下:
MySQL之MyBatis初体验3
执行上面的代码的时候,在控制台报出如下错误:
MySQL之MyBatis初体验3
通过上面的报错信息,我们发现一个sql异常:

... MySQL server version for the right syntax to use near '?,'admin')' at line 1

还可以看到,异常发生的位置,是在java代码的32行

分析问题原因:

使用PreapredStatement对象执行sql语句的流程:

  1. 注册驱动,获取Connection对象
    Connection connection = JdbcUtil.getConnection();
  2. 定义sql
    要注意的是,这里的sql是带有 ?占位符的,也就是说这条sql当前无法执行
    String sql = "insert into user values(null,?,?,?)";
  3. 获取执行sql语句的对象PreparedStatement
    注意,当这句话执行之后,sql语句其实已经发送到了数据库中,数据库会对其进行编译(但不执行),然后返回当前sql语句对应的PreparedStatement对象
    PreparedStatement pstmt = connection.prepareStatement(sql);
  4. 给PreparedStatement对象中sql中的 ?赋值
    注意,位置从1开始
    pstmt.setString(1, “小兰”);
    pstmt.setString(2, “admin”);
    pstmt.setFloat(3, 100f);
  5. 执行sql,接受返回结果
    执行这个方法时,数据库才会真正的去执行sql语句
    int i = pstmt.executeUpdate();
    要注意,sql语句前面已经传递给数据库,并且编译了,此时就不需要再传递了
    如果传递了,它会直接执行此处传递的sql语句,而这个sql语句又是个带?符,不能执行,所以报错,如果此处非要写sql,可以写成写一条可以运行的SQL,如下面这样:
    pstmt.executeUpdate("insert into user values(null,'小兰','22','admin22','100')");
    此时,数据库会直接执行这个sql语句,那上面的赋值就都没用了,sql注入问题又回来了
  6. 释放资源
    JdbcUtil.close(pstmt,connection)

最终解决办法:

修改第32行代码

int i = pstmt.executeUpdate();
上一篇:Proxy


下一篇:python运算符优先级