什么是关系型数据库?
是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。简单说来就是关系型数据库用了选择、投影、连接、并、交、差、除、增删查改等数学方法来实现对数据的存储和查询。可以用SQL语句方便的在一个表及其多个表之间做非常复杂的数据查询。安全性高。
所谓的关系模型指用二维表的形式表示实体和实体间联系的数据模型
关系型数据库的优势:
- 复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
- 事务支持使得对于安全性能很高的数据访问要求得以实现。
一对一 : 一个订单只属于一个用户 ==> 订单对用户是一对一关系
一对多 : 一个用户可以拥有多个订单 ==> 用户对订单是一对多关系
1、数据库创建
#部门表
create table dept(
did int primary key,
dname varchar(20),
dinfo varchar(50)
);
insert into dept values(101,'市场部','负责公司市场的开拓');
insert into dept values(102,'产品部','负责公司产品的营销');
insert into dept values(103,'研发部','负责公司产品的研发');
insert into dept values(104,'测试部','负责公司产品的测试');
select * from dept;
#员工表
drop table if exists emp;
create table emp(
eid int primary key auto_increment,
ename varchar(20),
job varchar(20),
sal double,
phone varchar(11),
address varchar(50),
did int
);
insert into emp(ename,job,sal,phone,address,did) values('赵德保','市场专员',4600,'15785642139','信阳',101);
insert into emp(ename,job,sal,phone,address,did) values('刘瑞琳','市场经理',7600,'17956248563','商丘',101);
insert into emp(ename,job,sal,phone,address,did) values('张翠萍','产品设计师',5800,'15821563548','郑州',102);
insert into emp(ename,job,sal,phone,address,did) values('刘长河','产品经理',9800,'15821563548','洛阳',102);
insert into emp(ename,job,sal,phone,address,did) values('李耀菲','开发工程师',8800,'13658942168','许昌',103);
insert into emp(ename,job,sal,phone,address,did) values('王长林','项目经理',15800,'13954865721','南阳',103);
insert into emp(ename,job,sal,phone,address,did) values('陈清泰','测试工程师',5300,'13785463249','周口',104);
insert into emp(ename,job,sal,phone,address,did) values('杨国华','测试经理',9800,'13785463249','驻马店',104);
select * from emp
2、创建一个JAVA项目
2.1创建lib架包
2.2在src内分别创建bean包、Dao包、和测试类text包
2.21bean包内创建与数据相关联的实体类
dept部门表的实体类
private Integer did;
private String dname;
private String dinfo;
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getDinfo() {
return dinfo;
}
public void setDinfo(String dinfo) {
this.dinfo = dinfo;
}
@Override
public String toString() {
return "dept{" +
"did=" + did +
", dname='" + dname + '\'' +
", dinfo='" + dinfo + '\'' +
'}';
}
emp员工表的实体类
它是关于部门序号相关联的,所以直接创建dept包的实体类,使其与emp相关联
private Integer eid;
private String ename;
private String job;
private Double sal;
private String phone;
private String address;
private dept dept;
public Integer getEid() {
return eid;
}
public void setEid(Integer eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public com.bean.dept getDept() {
return dept;
}
public void setDept(com.bean.dept dept) {
this.dept = dept;
}
@Override
public String toString() {
return "emp{" +
"eid=" + eid +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", sal=" + sal +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", dept=" + dept +
'}';
}
2.2.3在Dao包内创建两表相关的配置文件
mybatis一对多查询:子查询映射方式,单个参数,先查询主表,然后根据主表查询结果再查询子表,主表可以sql分页
DeptDao.java
package com.dao;
import com.bean.Dept;
public interface DeptDao {
Dept selectByDid(int did);
}
DeptDao.xml
自动关联
property:子查询属性名
column:数据库的列名,可以是别名
javaType:子查询属性名返回类型
select:子查询select的id值
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.DeptDao">
<resultMap id="deptMap" type="com.bean.Dept">
<id property="did" column="did" />
<result property="dname" column="dname"/>
<result property="dinfo" column="dinfo"/>
<!--配置一对多关系:自动根据did去关联查询emp表中该did的信息-->
<collection column="did" select="com.dao.EmpDao.selectByDid" property="emp">
</collection>
</resultMap>
<select id="selectByDid" parameterType="int" resultMap="deptMap">
select * from dept where did=#{did};
</select>
</mapper>
手动关联
<resultMap id="deptMap" type="项目表的全路径">
<id property="项目表主键数据库字段" column="项目表与参与方相关联" />
把需要查询的项目字段罗列下去
<!--配置一对多关系:手动映射配置-->
<collection property="项目表对象里参与方的集合属性名称" ofType="项目表与参与方关联的包">
<id property="参与方字段" column="参与方实体类属性" />
把需要查询的项目字段罗列下去
</collection>
</resultMap>
<resultMap id="deptMap" type="com.bean.Dept">
<id property="did" column="did" />
<result property="dname" column="dname"/>
<result property="dinfo" column="dinfo"/>
<!--配置一对多关系:手动映射配置-->
<collection property="emps" ofType="com.bean.Emp">
<id property="eid" column="eid" />
<result property="ename" column="ename" />
<result property="job" column="job" />
<result property="sal" column="sal" />
<result property="phone" column="phone" />
<result property="address" column="address" />
</collection>
</resultMap>
<select id="selectByDid" parameterType="int" resultMap="deptMap">
select * from dept inner join emp on dept.did=emp.did where dept.did=#{did};
</select>
员工配置
package com.dao;
import com.bean.Emp;
import java.util.List;
public interface EmpDao {
Emp selectByEid(int eid);
List<Emp> selectByDid(int did);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.EmpDao">
<select id="selectByEid" parameterType="int" resultType="emp">
select * from emp where eid=#{eid}
</select>
<select id="selectByDid" parameterType="int" resultType="emp">
select * from emp where did=#{did}
</select>
</mapper>
text测试类
部门测试类
InputStream stream=null;
SqlSessionFactoryBuilder builder=null;
SqlSessionFactory factory=null;
SqlSession sqlSession=null;
DeptDao deptDao=null;
@Before
public void init() throws IOException {
stream= Resources.getResourceAsStream("mybatis.xml");
builder=new SqlSessionFactoryBuilder();
factory=builder.build(stream);
sqlSession=factory.openSession();
deptDao=sqlSession.getMapper(DeptDao.class);
}
@Test
public void testSelectByDid(){
Dept dept = deptDao.selectByDid(101);
System.out.println(dept);
}
@After
public void distroy() throws IOException {
sqlSession.commit();
sqlSession.close();
stream.close();
}
}
员工测试类
InputStream stream=null;
SqlSessionFactoryBuilder builder=null;
SqlSessionFactory factory=null;
SqlSession sqlSession=null;
EmpDao empDao=null;
@Before
public void init() throws IOException {
stream= Resources.getResourceAsStream("mybatis.xml");
builder=new SqlSessionFactoryBuilder();
factory=builder.build(stream);
sqlSession=factory.openSession();
empDao=sqlSession.getMapper(EmpDao.class);
}
@Test
public void testSelectByEid(){
Emp emp = empDao.selectByEid(2);
System.out.println(emp);
}
@After
public void distroy() throws IOException {
sqlSession.commit();
sqlSession.close();
stream.close();
}
在 <association> 元素中通常使用以下属性。
- property:指定映射到实体类的对象属性。
- column:指定表中对应的字段(即查询返回的列名)。
- javaType:指定映射到实体对象属性的类型。
- select:指定引入嵌套查询的子 SQL 语句,该属性用于关联映射中的嵌套查询。
一对一关联查询可采用以下两种方式:
- 单步查询,通过关联查询实现
- 分步查询,通过两次或多次查询,为一对一关系的实体 Bean 赋值
部门配置
package com.dao;
import com.bean.Dept;
public interface DeptDao {
Dept selectByDid(int did);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.DeptDao">
<select id="selectByDid" parameterType="int" resultType="com.bean.Dept">
select * from dept where did=#{did};
</select>
</mapper>
员工配置
package com.chen.dao;
import com.bean.Emp;
import java.util.List;
public interface EmpDao {
Emp selectBuEid(int eid);
List<Emp> selectAll();
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.EmpDao">
<resultMap id="empMap" type="com.bean.Emp">
<id property="eid" column="eid" />
<result property="ename" column="ename" />
<result property="job" column="job" />
<result property="sal" column="sal" />
<result property="phone" column="phone" />
<result property="address" column="address" />
<!--定义手动映射关系:数据库中字段 和 类的属性 的映射-->
<!-- <association property="dept" javaType="com.chen.bean.Dept">
<id property="did" column="did"/>
<result property="dname" column="dname"/>
<result property="dinfo" column="dinfo"/>
</association> -->
<!--一对一的映射配置association: 自动关联查询:根据外键did执行dept表的关联查询,把查询出来部门信息封装到emp的dept属性-->
<association column="did" select="com.dao.DeptDao.selectByDid" property="dept">
</association>
</resultMap>
<select id="selectAll" resultMap="empMap">
select * from emp
</select>
</mapper>