一、JDBC操作数据库代码:
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
// 通过驱动管理类获取数据库链接
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?
characterEncoding=utf-8", "root", "root");
// 定义sql语句?表示占位符
String sql = "select * from user where username = ?";
// 获取预处理statement
preparedStatement = connection.prepareStatement(sql);
// 设置参数
preparedStatement.setString(1, "tom");
// 向数据库发出sql查询,查询结果集
resultSet = preparedStatement.executeQuery();
// 遍历结果集
while (resultSet.next()) {
int id = resultSet.getInt("id");
String username = resultSet.getString("username");
// 封装User
user.setId(id);
user.setUsername(username);
}
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
1,JDBC问题总结:
(1)数据库连接创建(三次握手)、释放频繁造成系统资源浪费,从而影响性能
(2)sql存在硬编码,不易维护
(3)对结果集解析存在硬编码(查询列名),导致sql变化后不易维护
2,解决思路:
数据库频繁创建连接、释放资源:使用连接池
sql硬编码:配置文件
解析封装返回结果集:反射、内省
二、自定义框架设计(mybatis底层雏形)
使用端:提供核心配置文件
sqlMapConfig.xml:存放数据源信息,引入mapper.xml
Mapper.xml:sql语句的配置文件信息
框架端:
1,读取配置文件
读取完以后以流的形式存在,创建两个javaBean来存储相关信息
(1)MappedStatement:id、存放sql语句、输入参数java类型、输出参数java类型
(2)Configration:存放数据库基本信息、存放一个Map<key,MappedStatement>。key为唯一标识:namespace+"."+id
2,解析配置文件
创建SqlSessionFactoryBuilder类,方法:SqlSessionFactory build(),该方法主要:
第一:使用dom4j解析配置文件,将解析内容封装到MappedStatement和Configration对象中
第二:创建SqlSessionFactory对象,生产sqlSession:会话对象(工厂模式)
3,创建SqlSessionFactory接口及其实现类DefaultSqlSessionFactory
方法:openSession():生产sqlSession
4,创建SqlSession接口及实现类DefaultSqlSession
定义对数据库的CURD操作:selectList()、selectOne()、update()
5,创建Executor接口及其实现类SimpleExecutor
query(Configration config,MappedStatement statement, Object...param):执行的就是JDBC代码
涉及到的设计模式:构建者模式、工厂模式、代理模式
具体代码实现这里就不展示了
三、mybatis复杂映射
3.1,一对一查询
现在有两张表,一张用户表,一张订单表
一对一查询需求:查询一个订单同时查出该订单所属用户信息。
对应sql语句:select * from orders o,user u where o.uid=u.id;
创建User和Order实体
public class Order {
private int id;
private Date ordertime;
private double total;
//代表当前订单属于哪个用户
private User user;
}
public class User {
private int id;
private String username;
private String password;
private Date birthday;
}
//创建OrderMapper接口
public interface OrderMapper {
List<Order> findAll();
}
//配置OrderMapper.xml
<mapper namespace="com.lagou.mapper.OrderMapper">
<resultMap id="orderMap" type="com.lagou.domain.Order">
<result property="id" column="id"></result>
<result property="ordertime" column="ordertime"></result>
<result property="total" column="total"></result>
<association property="user" javaType="com.lagou.domain.User">
<result property="uid" column="id"></result>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="birthday" column="birthday"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * from orders o,user u where o.uid=u.id
</select>
</mapper>
这里其实就是想演示下resultMap这个属性怎么用,因为项目中平常用的很少
3.2,一对多查询
一对多查询需求:查询所有用户,同时查出用户所对应订单
sql语句:select *,o.id oid from user u left join orders o on u.id=o.uid;
public class Order {
private int id;
private Date ordertime;
private double total;
}
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//当前用户拥有订单
private List<Order> orderList;
}
对应mapper.xml配置
<mapper namespace="com.lagou.mapper.UserMapper">
<resultMap id="userMap" type="com.lagou.domain.User">
<result property="id" column="id"></result>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="birthday" column="birthday"></result>
<collection property="orderList" ofType="com.lagou.domain.Order">
<result property="oid" column="id"></result>
<result property="ordertime" column="ordertime"></result>
<result property="total" column="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select *,o.id oid from user u left join orders o on u.id=o.uid
</select>
</mapper>
3.3,多对多查询
用户和角色关系是多对多,有三张表
多对多查询需求:查询所有用户同时查询出用户所有角色
sql语句:select * from user u left join sys_user_role ur on u.id = ur.userid left join sys_role r on r.id = ur.roleid
创建User和Role实体
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//该用户所对应的角色
private List<Role> roleList;
}
public class Role {
private int id;
private String rolename;
}
mapper.xml配置
<mapper namespace="com.lagou.mapper.UserMapper">
<resultMap id="userRoleMap" type="com.lagou.pojo.User">
<result property="id" column="userid"></result>
<result property="username" column="username"></result>
<collection property="roleList" ofType="com.lagou.pojo.Role">
<result property="id" column="roleid"></result>
<result property="roleName" column="roleName"></result>
<result property="roleDesc" column="roleDesc"></result>
</collection>
</resultMap>
<select id="findAllUserAndRole" resultMap="userRoleMap">
select * from user u left join sys_user_role ur on u.id = ur.userid left join sys_role r on r.id = ur.roleid
</select>
</mapper>