MySQL之MyBatis初体验3

• 一、注解开发
• • 1.1 注解与xml的比较
  • 1.2 使用注解完成单表CRUD
  • 1.3 使用注解完成多表映射
  • • 1.3.1 一对一
    • 1.3.2 一对多
    • 1.3.3 懒加载
• 二、MySQL经验总结
• • 2.1 SQL语句报错
  • 2.1 jdbc报错

一、注解开发

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

报错信息：

问题分析：

发现需求中有条件，就习惯的时候用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代码如下：

执行上面的代码的时候，在控制台报出如下错误：

通过上面的报错信息，我们发现一个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()；