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
报错信息:
问题分析:
发现需求中有条件,就习惯的时候用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
总结:
- 首先执行
FROM
子句,从student表检索记录; - 如果有
WHERE
执行WHERE
子句,筛选出student表中符合条件的所有记录; - 执行
GROUP BY
子句,把student表按address分组; - 计算
COUNT(*)
求出address相同的记录总数,计算SUM(math)
求出address相同的记录中数学总成绩,计算AVG(math)
求出address相同的记录中数学平均成绩; - 执行
HAVING
子句,筛选出COUNT(*)
大于2的分组; - 提取address,``COUNT(*),SUM(math),AVG(math) 4个字段,产生新的结果集;
- 执行
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语句的流程:
- 注册驱动,获取Connection对象
Connection connection = JdbcUtil.getConnection(); - 定义sql
要注意的是,这里的sql是带有?
占位符的,也就是说这条sql当前无法执行String sql = "insert into user values(null,?,?,?)";
- 获取执行sql语句的对象PreparedStatement
注意,当这句话执行之后,sql语句其实已经发送到了数据库中,数据库会对其进行编译(但不执行),然后返回当前sql语句对应的PreparedStatement对象
PreparedStatement pstmt = connection.prepareStatement(sql); - 给PreparedStatement对象中sql中的 ?赋值
注意,位置从1开始
pstmt.setString(1, “小兰”);
pstmt.setString(2, “admin”);
pstmt.setFloat(3, 100f); - 执行sql,接受返回结果
执行这个方法时,数据库才会真正的去执行sql语句
int i = pstmt.executeUpdate();
要注意,sql语句前面已经传递给数据库,并且编译了,此时就不需要再传递了
如果传递了,它会直接执行此处传递的sql语句,而这个sql语句又是个带?
符,不能执行,所以报错,如果此处非要写sql,可以写成写一条可以运行的SQL,如下面这样:pstmt.executeUpdate("insert into user values(null,'小兰','22','admin22','100')");
此时,数据库会直接执行这个sql语句,那上面的赋值就都没用了,sql注入问题又回来了 - 释放资源
JdbcUtil.close(pstmt,connection)
最终解决办法:
修改第32行代码
int i = pstmt.executeUpdate();