第十四天三月份8日之多表设计和JDBC、静态工厂设计模式
一、SQL中多表设计
1、一对多
create table DEPARTMENT(
ID int primary key,
NAME varchar(100) not null
);
create table EMPLOYEE(
ID int primary key,
NAME varchar(100) not null,
SALARY float(8,2),
DEPART_ID int,
constraint DEPART_ID_FK foreign key (DEPART_ID) references DEPARTMENT(ID)
);
2、多对多
create table TEACHER(
ID int primary key,
NAME varchar(100) not null,
SALARY float(8,2)
);
create table STUDENT(
ID int primary key,
NAME varchar(100) not null,
GRADE varchar(100)
);
create table TEACHER_STUDENT(
T_ID int,
S_ID int,
primary key(T_ID,S_ID),
constraint T_ID_FK foreign key (T_ID) references TEACHER(ID),
constraint S_ID_FK foreign key (S_ID) references STUDENT(ID)
);
3、一对一
a、按照外键关联(用一对多模拟的)
create table PERSON1(
ID int primary key,
NAME varchar(100),
GENDER varchar(100)
);
create table ID_CARD1(
ID int primary key,
NUMBER varchar(100),
PERSON_ID int unique,
constraint PERSON_ID_FK foreign key (PERSON_ID) references PERSON1(ID)
);
b、按照主键关联
create table PERSON2(
ID int primary key,
NAME varchar(100),
GENDER varchar(100)
);
create table ID_CARD2(
ID int primary key,
NUMBER varchar(100),
constraint PERSON_ID_FK1 foreign key (ID) references PERSON2(ID)
);
二、多表查询
+----+----------+------+
| id | name | city |
+----+----------+------+
| 1 | 陈冠希 | 香港 |
| 2 | 李宗瑞 | 台北 |
| 3 | 苍井空 | 东京 |
| 4 | 钟欣桐 | 香港 |
| 5 | 芙蓉姐姐 | NULL |
+----+----------+------+
+----+--------------+---------+-------------+
| id | order_number | price | customer_id |
+----+--------------+---------+-------------+
| 1 | 0001 | 100.00 | 1 |
| 2 | 0002 | 200.00 | 1 |
| 3 | 0003 | 300.00 | 1 |
| 4 | 0004 | 100.00 | 2 |
| 5 | 0005 | 200.00 | 3 |
| 6 | 0006 | 100.00 | 4 |
| 7 | 0007 | 1000.00 | NULL |
+----+--------------+---------+-------------+
1、连接查询（面试必面）
基本语法：from LEFTTABLE 连接类型(cross join|inner join|left [outer] join|right [outer] join|UNION) RIGHTTABLE [on 连接条件] [where 过滤条件]
1.1交叉连接查询(cross join)
结果：返回两张表的笛卡尔积。（5*7=35）
select * from CUSTOMER cross join ORDERS;
1.2内连接查询
隐式内连接：不适用连接类型和on关键字。连接条件用where来连接
有订单的客户和订单信息：select c.name,c.city,o.order_number,o.price from CUSTOMER c,ORDERS o where c.id=o.customer_id;
显式内连接：(inner join)
有订单的客户和订单信息:select c.name,c.city,o.order_number,o.price from CUSTOMER c inner join ORDERS o on c.id=o.customer_id;
1.3外连接查询
左外连接：(left [outer] join).
返回满足连接条件的记录，同时返回左表中剩余的所有记录。
显示所有的客户信息，有订单的还要显示对应的订单信息。
select c.name,c.city,o.order_number,o.price from CUSTOMER c left join ORDERS o on c.id=o.customer_id;
右外连接：(right [outer] join)
返回满足连接条件的记录，同时返回右表中剩余的所有记录。
显示所有的订单信息，有客户的还要显示对应的客户信息。
select c.name,c.city,o.order_number,o.price from CUSTOMER c right join ORDERS o on c.id=o.customer_id;
2、联合查询：（UNION）
返回多条语句查询的记录的并集,去除重复的记录。
查询订单金额在200以上，且客户的id为1的记录
select * from ORDERS where price>200 UNION select * from ORDERS where customer_id=1;
3、子查询：（嵌套查询）
查询“陈冠希”的所有订单信息
select * from ORDERS where CUSTOMER_ID=(select ID from CUSTOMER where NAME='陈冠希');
4、报表查询：（使用数据库提供的函数）
统计一个班级共有多少学生？
mysql>select count(ID) from STUDENT;
select count(*) from STUDENT;
统计数学成绩大于90的学生有多少个？
mysql>select count(math) from STUDENT where math>=90;
统计总分大于250的人数有多少？
mysql>select count(*) from STUDENT where chinese+math+english>250;
统计一个班级数学总成绩？
mysql>select sum(MATH) from STUDENT;
统计一个班级语文、英语、数学各科的总成绩
mysql>select sum(chinese),sum(english),sum(math) from STUDENT;
统计一个班级语文、英语、数学的成绩总和
mysql>select sum(chinese+english+math) from STUDENT;
统计一个班级语文成绩平均分
mysql>select sum(chinese)/count(ID) from STUDENT;
求一个班级数学平均分？
mysql>select avg(MATH) from STUDENT;
求一个班级总分平均分
msyql>select avg(chinese+english+math) from STUDENT;
对订单表中商品归类后，显示每一类商品的总价
msyql>select sum(PRICE),PRODUCT from ORDERS group by PRODUCT
查询购买了几类商品，并且每类总价大于100的商品
msyql>select sum(PRICE),PRODUCT from ORDERS group by PRODUCT having sum(PRICE)>100;
随机取3条记录
select * from orders order by rand() limit 0,3;
三、数据库的备份与恢复
1、备份
c:\>mysqldump [-h localhost] -u root -p test>d:/test.sql
2、恢复
前提：数据库名必须存在
方式一：
c:\>mysql -u root -p test<d:/test.sql
方式二：
mysql>use test;
mysql>source d:/test.sql;
四、JDBC概述（JavaEE技术之一,规范：接口和抽象类组成）
Java Data Base Connectivity 用Java语言连接数据库操作
1、API：在JDK中。
java.sql.*;
javax.sql.*;
2、JDBC和数据库驱动的关系：抽象和实现的关系
3、第一个JDBC程序：查询USES表中的所有记录，打印到控制台上
a、把数据库的驱动加入到构建路径中
b、编写步骤：（非常重要）
1、注册驱动
2、获取与数据库的连接（所有的操作都是建立在连接之上）
3、得到代表sql语句的对象
4、执行语句
5、如果执行的是查询语句，遍历结果集
6、释放占用的资源
五、JDBC常用的接口
1、DriverManager：注册驱动和获取与数据库的连接
a、注册驱动：
方式一（不建议使用）：DriverManager.registerDriver(new com.mysql.jdbc.Driver());
缺点：1、依赖具体的数据库驱动；2、会导致驱动注册2遍。
方式二(推荐)：Class.forName("com.mysql.jdbc.Driver")
b、获取与数据库的连接：
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day14", "root", "sorry");
url：数据库厂商与SUN定义的连接协议
jdbc:数据库厂商:[子协议]：主机：端口
常见的数据库连接URL（建议查看数据库对应的文档）
MySQL: jdbc:mysql://localhost:3306/day14 == jdbc:mysql:///day14 (默认本机的3306端口)
Oracle： jdbc:oracle:thin:@localhost:1521:orcl
获取连接的重载方法：
DriverManager.getConnection(String url, String username, String password);
DriverManager.getConnection(String url, Properties info)
DriverManager.getConnection(String url): url?p1=value1&p2=value2
2、Connection：代表与数据库的连接。所有的与数据库的交互都是基于该对象
Statement stmt = conn.createStatement();
3、Statement:代表SQL语句对象
ResultSet executeQuery(String dqlSql)
int executeUpdate(String dmlSQL)
boolean execute(String sql):返回值不代表成功与否。有没有结果集。有true
4、ResultSet：代表查询的结果集
提供了一些获取字段值的重载方法：可以按照索引取（从1开始），亦可以按照字段名来取
boolean next():游标往下移动一个
boolean previous():游标往上移动一个
void absolute(int index):绝对定位
void beforeFirst():移动游标第一行的前面
void afterLast():移动游标到最后一行的后面
5、PreparedStatement:接口 (能用该接口就不用Statement)
a、防止SQL注入
b、预编译SQL语句，执行效率比Statment高
c、参数可以使用占位符(?)
driverClass=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/MYDB1 username=root password=sorry
package com.itheima.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtil {
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
try {
InputStream in = JdbcUtil.class.getClassLoader()
.getResourceAsStream("dbcfg.properties");
Properties pro = new Properties();
pro.load(in);
driverClass = pro.getProperty("driverClass");
url = pro.getProperty("url");
username = pro.getProperty("username");
password = pro.getProperty("password");
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
return DriverManager.getConnection(url, username, password);
}
public static void release(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.itheima.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
import com.itheima.util.JdbcUtil;
public class JdbcDemo {
@Test
public void add() {
Connection conn = null;
Statement state = null;
try {
conn = JdbcUtil.getConnection();
state = conn.createStatement();
int i = state
.executeUpdate("insert into user2 values(null,'王二','123','12312@qq.com','1990-09-09')");
if (i > 0)
System.out.println("插入成功！");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JdbcUtil.release(null, state, conn);
}
}
@Test
public void delete() {
Connection conn = null;
Statement state = null;
try {
conn = JdbcUtil.getConnection();
state = conn.createStatement();
int i = state
.executeUpdate("delete from user2 where id=2");
if (i > 0)
System.out.println("删除成功！");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JdbcUtil.release(null, state, conn);
}
}
@Test
public void update() {
Connection conn = null;
Statement state = null;
try {
conn = JdbcUtil.getConnection();
state = conn.createStatement();
int i = state
.executeUpdate("update user2 set password='123456' where id = 1");
if (i > 0)
System.out.println("更新成功！");
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JdbcUtil.release(null, state, conn);
}
}
@Test
public void query() {
Connection conn = null;
Statement state = null;
try {
conn = JdbcUtil.getConnection();
state = conn.createStatement();
ResultSet re = state .executeQuery("select * from user2");
while(re.next()){
System.out.println(re.getString("name"));
System.out.println(re.getString("password"));
System.out.println(re.getString("email"));
System.out.println(re.getDate("birthday"));
System.out.println("--------------------------------------------");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JdbcUtil.release(null, state, conn);
}
}
}
静态工厂：
package com.itheima.dao;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import com.itheima.exception.DaoException;
public class DaoFactory {
private static String userDaoImplClassName;
static{
try {
InputStream in = DaoFactory.class.getClassLoader().getResourceAsStream("dao.properties");
Properties props = new Properties();
props.load(in);
userDaoImplClassName = props.getProperty("userDao");
} catch (IOException e) {
throw new ExceptionInInitializerError(e);
}
}
public static UserDao getUserDaoInstance(){//静态工厂
try {
return (UserDao) Class.forName(userDaoImplClassName).newInstance();//通过反射拿到UserDaoMySqlImpl类的实例
} catch (Exception e) {
throw new DaoException("Instance UserDao failure,please check you dao config files");
}
}
}
package com.itheima.service.impl;
import com.itheima.dao.DaoFactory;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.exception.UserExistException;
import com.itheima.service.BusinessService;
public class BusinessServiceImpl implements BusinessService {
private UserDao dao = DaoFactory.getUserDaoInstance();//不直接依赖哪一个具体的实现，要什么实例，直接问工厂要。
//这是工厂设计模式
public void regist(User user) throws UserExistException {
//检查用户名是否存在
boolean b = checkUsernameIsExist(user.getUsername());
if(b)
throw new UserExistException("The username "+user.getUsername()+" exist,please change another");
dao.addUser(user);
}
public boolean checkUsernameIsExist(String username) {
return dao.findUserByUsername(username);
}
public User login(String username, String password) {
return dao.findUser(username, password);
}
}