小项目练习: 订房系统
面向对象分析与设计 and 数据库设计:
从两个角度分析:
1. 业务逻辑 (房间管理,用户管理)
2. 业务数据 (房间信息,用户信息)
业务逻辑包: biz包将业务逻辑的类放到这个包
业务数据包: 实体包 entity包将实体类,数据类放到这个包
工具包: util包一些工具类,如: 连接数据库,关闭资源
临时测试包: test包测试局部功能类
业务数据包:
建立房间类和用户类
/**
* 程序目标:
* 建立房间类
* 建表:
* create table room
(
id number(4) not null,
no varchar2(4),
type varchar2(2),
description varchar2(512),
constraint pk_room primary key(id)
)
*/
package moudule1.hbs.entity;
import java.io.Serializable;
@SuppressWarnings("serial")
public class Room implements Serializable{
private int id;
private String no;
private String type;
private String description;
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
}
/**
* 程序目标:
* 建立用户抽象类
*/
package moudule1.hbs.entity;
import java.io.Serializable;
public abstract class User implements Serializable{
private int id;
private String userName;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
}
/**
* 程序目标:
* 建立管理员类
*/
package moudule1.hbs.entity;
@SuppressWarnings("serial")
public class Administrator extends User{
private int grade;
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
}
/**
* 程序目标:
* 建立客户类
*/
package moudule1.hbs.entity;
@SuppressWarnings("serial")
public class Customer extends User{
private String phone;
private String email;
private Address address;
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
/**
* 程序目标:
* 建立记录用户联系方式的类
*/
package moudule1.hbs.entity;
import java.io.Serializable;
@SuppressWarnings("serial")
public class Address implements Serializable{
private String qq;
public String getQq() {
return qq;
}
public void setQq(String qq) {
this.qq = qq;
}
}
/**
* 程序目标:
* 订房记录,维护多对多的关系的类
*/
package moudule1.hbs.entity;
import java.io.Serializable;
import java.sql.Date;
@SuppressWarnings("serial")
public class Subscription implements Serializable
{
private Room room;
private Customer customer;
private Date start;
private Date end;
public Customer getCustomer()
{
return customer;
}
public void setCustomer(Customer customer)
{
this.customer = customer;
}
public Date getEnd()
{
return end;
}
public void setEnd(Date end)
{
this.end = end;
}
public Room getRoom()
{
return room;
}
public void setRoom(Room room)
{
this.room = room;
}
public Date getStart()
{
return start;
}
public void setStart(Date start)
{
this.start = start;
}
}
为用户类建表:
用户类中有继承的关系,在处理继承关系的时候,有2种建表的策略和3种建表准则
继承关系建表准则:
1. 一个类就一个表
2. 为具体类建表
3. 继承层次建一个表
1.父子都建表,有关联的合并为一个表,合并表的原则,数值对象和它的所属实体对象合并为一个表
create table users
(
id number(9) not null,
username varchar2(20),
password varchar2(15),
constraint pk_user_tbl primary key(id)
)
create table admin
(
id number(9) not null,
grade number(2),
constraint pk_admin_tbl primary key(id)
)
create table customer
(
id number(9) not null,
phone varchar2(15),
email varchar2(30),
district varchar2(10),
street varchar2(30),
zipcode varchar2(6),
constraint pk_cust_tbl primary key(id)
)
create sequence user_seq;
//策略一:共享主键(将表的主键设置为父类表的外键)
alter table customer
add constraint fk_cust_user foreign key(id)
references users(id)
alter table admin
add constraint fk_admin_user foreign key(id)
references users(id)
//策略二:外键唯一约束(在子类再添加一个外键,引用父类的主键并设置唯一约束)
2.子类建表,有关联的合并为一个表
create table admin
(
id number(9) not null,
username varchar2(20),
password varchar2(15),
grade number(2),
constraint pk_admin primary key(id)
)
create table customer
(
id number(9) not null,
username varchar2(20),
password varchar2(15),
phone varchar2(15) not null,
email varchar2(30),
district varchar2(10),
street varchar2(30),
zipcode varchar2(6),
constraint pk_cust primary key(id)
)
3.就建一个表
create table user
(
id number(9) not null,
username varchar2(20),
password varchar2(15),
phone varchar2(15) not null,
email varchar2(30),
district varchar2(10),
street varchar2(30),
zipcode varchar2(6),
grade number(2) not null,
constraint pk_cust_tbl primary key(id)
)
工具包:
1. 连接数据库
2. 关闭数据库相关资源
3. 自动生成id
1. 连接数据库
/**
* 程序目标:
* 数据库连接对象工厂类
* 建立数据库的连接:两种方式
* 1.Driver
* 2.数据源
* 需要资源:
* ojdbc14.jar
*/
package moudule1.hbs.util;
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
public class ConnectionFactory {
//Driver
public static Connection getConnection(){
Connection con=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:name", "scott", "tiger");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
//DataSource
public static Connection getDataSourceConnection() throws SQLException{
Connection con=null;
OracleDataSource ods=null;
try {
ods=new OracleDataSource();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ods.setDriverType("thin");
ods.setServerName("127.0.0.1");
ods.setDatabaseName("name");
ods.setPortNumber(1521);
ods.setUser("scott");
ods.setPassword("tiger");
con=ods.getConnection();
return con;
}
}
2.关闭数据库相关资源
/**
* 程序目标:
* 关闭数据库相关资源
*/
package moudule1.hbs.util;
import java.sql.*;
public class JdbcUtil {
public static void close(Connection con){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(ResultSet rs){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(Statement st){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(ResultSet rs,Statement st,Connection con){
close(rs);
close(st,con);
}
public static void close(Statement st,Connection con){
close(st);
close(con);
}
}
3.自动生成id
自动生成id的解决办法:
注意: 在Oracle下没有自动增加id, 只能通过序列(Sequence)来获取
三种方式:
3.1通过Oracle中的序列(Sequence)来获取
3.2写java的自动生成ID的程序
3.3专门在数据库中建立一个表,设置一个高值,写个java程序,取这个高值,取一次,加1

/**
* 程序目标:
* 获得id的父接口
*/
package moudule1.hbs.util;
public interface IdGenerator {
public int nextId();//获得id的方法
public void setProperty(String name,String value);
}
/**
* 抽象类:简单实现了父接口中setProperty方法
* 这个类是所有实现类的父类
*/
package moudule1.hbs.util;
import java.util.Properties;
public abstract class BaseIdGenerator implements IdGenerator{
protected Properties proper=new Properties();
public void setProperty(String name, String value)
{
proper.setProperty(name, value);
}
}
/**
* 生产获得id的实现类的工厂
*/
package moudule1.hbs.util;
public class IdGeneratorFactory {
public static IdGenerator getIdGenerator(){
return new SequenceIdGenerator();
}
public static IdGenerator getTableIdGenerator(){
return new TableIdGenerator();
}
public static IdGenerator getnJavaIdGenerator(){
return new javaIdGenerator();
}
}
/**
* sequence方式
* 建表:
* create sequence room_seq //创建序列
*/
package moudule1.hbs.util;
import java.sql.*;
public class SequenceIdGenerator extends BaseIdGenerator{
public int nextId() {
// TODO Auto-generated method stub
int nextId=-1;
Connection con=null;
Statement st=null;
ResultSet rs=null;
try {
// con=ConnectionFactory.getConnection();
con=ConnectionFactory.getDataSourceConnection();
st=con.createStatement();
String sql="select "+proper.getProperty("sequence")+".nextval from dual";
rs=st.executeQuery(sql);
rs.next();
nextId=rs.getInt(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(rs,st,con);
}
return nextId;
}
}
/**
* 取高值+低值的方式
* 建表:
* 取值表:create table a( id number(7) );
* insert into a (id) values (5000);
*/
package moudule1.hbs.util;
import java.sql.*;
public class TableIdGenerator extends BaseIdGenerator{
private static Object obj=new Object();
public int nextId() {
// TODO Auto-generated method stub
int nextId=-1;
synchronized(obj){
Connection con=null;
Statement st=null;
// con=ConnectionFactory.getConnection();
try {
con=ConnectionFactory.getDataSourceConnection();
} catch (SQLException e1) {
// TODO 自动生成 catch 块
e1.printStackTrace();
}
try {
st=con.createStatement();
String sql="select " + proper.getProperty("lie") + " from " + proper.getProperty("table");
ResultSet rs=st.executeQuery(sql);
rs.next();
nextId=rs.getInt(1);
JdbcUtil.close(rs);
System.out.println(nextId);
sql = "update " + proper.getProperty("table") + " set " + proper.getProperty("lie") + " = " + proper.getProperty("lie") + " + 1 ";
st.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtil.close(st,con);
}
}
return nextId;
}
}
/**
* 通过java程序的方法
*/
package moudule1.hbs.util;
public class javaIdGenerator extends BaseIdGenerator{
private static int Id=1;
private Object obj=new Object();
public int nextId() {
// TODO Auto-generated method stub
synchronized(obj){
return Id++;
}
}
}
业务逻辑包:
/**
* 程序目标:
* 1.添加房间信息:只简单实现了获得id的实现
* 2.删除房间信息
* 3.修改房间信息
* 4.查找房间信息
*/
package moudule1.hbs.biz;
import moudule1.hbs.entity.Room;
import moudule1.hbs.util.*;
public class RoomBiz {
public void addRoom(Room room){
//sequence
// IdGenerator generator= IdGeneratorFactory.getIdGenerator();
// generator.setProperty("sequence","room_seq");
//
// room.setId(generator.nextId());
// System.out.println(room.getId());
// 使用第三种方式生成id
IdGenerator generator2 = IdGeneratorFactory.getTableIdGenerator();
generator2.setProperty("table","a");
generator2.setProperty("lie","id");
room.setId(generator2.nextId());
System.out.println(room.getId());
//java
// IdGenerator generator3=IdGeneratorFactory.getnJavaIdGenerator();
// room.setId(generator3.nextId());
}
}
/**
* 程序目标:
* 添加用户信息
* 删除用户信息
* 修改用户信息
* 查找用户信息
*/
package moudule1.hbs.biz;
import java.util.Collection;
import moudule1.hbs.entity.User;
public class UserBiz {
public void add(User user){
}
public Collection findAd(){
return null;
}
public Collection findCus(){
return null;
}
}
ORM策略:
1. 继承策略: 引例: entity包.room.java Customer.java Subscription.java
2. 聚合策略
2.1 一对一关系的处理
引例: 汽车和发动机
package moudule1.ORM.oto;
public class Car {
private int id;
private String moudule;//模型
private Engine engine; //发动机
public Engine getEngine() {
return engine;
}
public void setEngine(Engine engine) {
this.engine = engine;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getMoudule() {
return moudule;
}
public void setMoudule(String moudule) {
this.moudule = moudule;
}
public void addEngine(Engine ei){//将汽车和发动机组装起来,注意顺序
this.engine=ei;
ei.setCar(this);
}
public void removeEngine(){//拆卸
this.engine.setCar(null);
this.engine=null;
}
}
package moudule1.ORM.oto;
public class Engine {
private int id;
private String manufacture;//制造商
private Car car;
public Car getCar() {
return car;
}
public void setCar(Car car) {
this.car = car;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getManufacture() {
return manufacture;
}
public void setManufacture(String manufacture) {
this.manufacture = manufacture;
}
}
/**
* 知识点:
* one to one
* 程序目标:
* Car.java:汽车类
* Engine.java:发动机类
* CarBiz.java:业务逻辑类:汽车入库
* 一辆汽车只有一个发动机,一个发动机对应一辆汽车,一对一关系
* 建表:
* 方法一:共享主键,将Engine的主键设置为外键
* 方法二:另添加一个外键,并且设置唯一
*/
package moudule1.ORM.oto;
public class CarBiz {
public void add(Car car){//汽车入库
}
public static void main(String[] args){
CarBiz cb=new CarBiz();
Car car=new Car();
Engine ei=new Engine();
car.addEngine(ei);
cb.add(car);
}
}
2.2 一对多关系的处理
/**
* 知识点:
* 1对多的关系,一个母亲可以有多个孩子,但是一个孩子只有一个母亲
* 程序目标:
* Mather.java
* Son.java
* MatherBiz.java
* 建表:
* 1对多的关系
* 方法一:Son表添加一个外键,引用自Mather表
* 方法二:共享主键
*/
package moudule1.ORM.otm;
public class MatherBiz {
public static void main(String[] args) {
// TODO Auto-generated method stub
Son son=new Son();
Mather mather=new Mather();
mather.setSet(son);
}
}
package moudule1.ORM.otm;
import java.util.Collection;
import java.util.HashSet;
public class Mather {
private int id;
private String mName;
private Collection set=new HashSet();
public Collection getSet() {
return set;
}
public void setSet(Son son) {
set.add(son);
son.setMather(this);
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getMName() {
return mName;
}
public void setMName(String name) {
mName = name;
}
}
package moudule1.ORM.otm;
public class Son {
private int id;
private String sName;
private Mather mather;
public Mather getMather() {
return mather;
}
public void setMather(Mather mather) {
this.mather = mather;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSName() {
return sName;
}
public void setSName(String name) {
sName = name;
}
}
2.3 多对多关系的处理
引例: entity包.room.java Customer.java Subscription.java
DAO:
什么是DAO?
有什么用?解决什么问题?
Data Access Object 数据访问对象,就是把连接数据库,关闭数据库资源,基本的数据库操作语句等封装起来
这样一来,我们在写业务逻辑类的时候,就不用搀杂那么多和业务逻辑无关的代码了,使得程序更清晰,并且易于维护
引例: 客户端—输入指令(对某个实体进行增删改查)—获得DAO对象—DAO对象执行客户端的指令(借助util包)
/**
* 知识点:
* DAO架构
* 程序目标:
* 设置用户信息,并保存到数据库中
* 对用户信息可以进行增加,删除,修改,查询的操作
* 包结构说明:
* client包--客户端:用来设置用户信息,并且发出命令的(增删改查)
* dao包--实现了dao的模式,包装了连接数据库,增删改查
* entity包--业务数据类
* util包--包装了获得id,获得数据库连接,关闭资源的实现
*/
package moudule1.DAO.client;
//插入一个用户信息到数据库,从数据库中查询用户的信息
import moudule1.DAO.entity.User;
import moudule1.DAO.dao.*;
import java.sql.*;
import java.util.Collection;
import java.util.Iterator;
public class Test {
public static void main(String[] args) {
//得到DAO对象(有了它就可以进行增删改查的操作了)
UserDAO dao=(UserDAO) DaoFactory.getDao("user");
User user=new User();
//插入用户信息
user.setUserName("zhangsanfeng");
user.setPassword("123");
user.setTruename("zhangsan");
user.setDate(Date.valueOf("1980-3-10"));
dao.insert(user);
//查询
Collection users=dao.select(null);//为null,查询所有的user
Iterator it=users.iterator();
while(it.hasNext()){
user=(User)it.next();
System.out.println(user.getTruename());
}
}
}
entity包:
package moudule1.DAO.entity;
//用户类:存储用户信息建表
import java.io.Serializable;
import java.sql.*;
public class User implements Serializable{
private int id;
private String userName;
private String password;
private String truename;
private Date date;
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getTruename() {
return truename;
}
public void setTruename(String truename) {
this.truename = truename;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
}
引用util包: BaseIdGenerator.java ConnectionFactory.java IdGenerator.java IdGeneratorFactory.java JdbcUtil.java SequenceIdGenerator.java
DAO包:
package moudule1.DAO.dao;
//DAO对象的父接口:增删改查
import java.util.Collection;
import moudule1.DAO.entity.User;
public interface UserDAO {
public void insert(User user);
public void update(User user);
public Collection select(User user);
public void delete(User user);
}
package moudule1.DAO.dao;
//生产dao对象的工厂
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
public class DaoFactory {
private static Properties config=new Properties();
static{
//当运行DaoFactory这个类的时候,获得一个输入流,源为daoconfig.properties配置文件
InputStream in=DaoFactory.class.getClassLoader().getResourceAsStream("daoconfig.properties");
try {
config.load(in);//用属性类去读这个配置文件
in.close();
} catch (IOException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
throw new ExceptionInInitializerError(e.getMessage());
}
}
public static Object getDao(String type){
Object dao=null;
String leiname=config.getProperty(type);
try {
return Class.forName(leiname).newInstance();
} catch (InstantiationException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
return dao;
}
}
package moudule1.DAO.dao;
//dao删改查操作:没有增加的操作实现,因为不同数据库有不同的增加
//方法,所以,让子类去单独实现
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import linshi.com.allanlxf.daoa.util.ConnectionFactory;
import linshi.com.allanlxf.daoa.util.JdbcUtil;
import moudule1.DAO.util.*;
import moudule1.DAO.entity.User;
public abstract class UserDaoBaseImpl implements UserDAO{
public void delete(User user) {
// TODO 自动生成方法存根
Connection con=null;
PreparedStatement ps = null;
try {
con=ConnectionFactory.getConnection();
ps=con.prepareStatement("delete from dao_user where id = ?");
ps.setInt(1, user.getId());
ps.executeUpdate();
} catch (ClassNotFoundException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (Exception e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}finally
{
JdbcUtil.close(ps, con);
}
}
public Collection select(User user) {
// TODO 自动生成方法存根
Collection users = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try
{
StringBuffer sql = new StringBuffer("select * from dao_user where 1 = 1 ");
if(user != null)
{
if(user.getUserName() != null)
{
sql.append(" and userName = ?");
}
if(user.getPassword() != null)
{
sql.append(" and userName = ?");
}
if(user.getTruename() != null)
{
sql.append(" and name = ?");
}
if(user.getDate() != null)
{
sql.append(" and birthday = ?");
}
}
sql.append(" order by name desc ");
con = ConnectionFactory.getConnection();
ps = con.prepareStatement(sql.toString());
if(user != null)
{
int index = 1;
if(user.getUserName() != null)
{
ps.setString(index++, user.getUserName());
}
if(user.getPassword() != null)
{
ps.setString(index++, user.getPassword());
}
if(user.getTruename() != null)
{
ps.setString(index++, user.getTruename());
}
if(user.getDate() != null)
{
ps.setDate(index++, user.getDate());
}
}
rs = ps.executeQuery();
users = new ArrayList();
while(rs.next())
{
User record = new User();
record.setId(rs.getInt("id"));
record.setUserName(rs.getString("username"));
record.setPassword(rs.getString("password"));
record.setTruename(rs.getString("name"));
record.setDate(rs.getDate("birthday"));
users.add(record);
}
}catch(Exception e)
{
e.printStackTrace();
}finally
{
JdbcUtil.close(rs, ps, con);
}
return users;
}
public void update(User user) {
// TODO 自动生成方法存根
Connection con = null;
PreparedStatement ps = null;
StringBuffer sql = new StringBuffer("update dao_user");
sql.append(" set username = ?, password = ?, name = ?, birthday = ?");
sql.append(" where id = ?");
try
{
con = ConnectionFactory.getConnection();
ps = con.prepareStatement(sql.toString());
int index = 1;
ps.setString(index++, user.getUserName());
ps.setString(index++, user.getPassword());
ps.setString(index++, user.getTruename());
ps.setDate(index++, user.getDate());
ps.setInt(index++, user.getId());
ps.executeUpdate();
}catch(Exception e)
{
e.printStackTrace();
}finally
{
JdbcUtil.close(ps, con);
}
}
}
package moudule1.DAO.dao;
//DAO对象的实现类(oracle):增加操作
import java.sql.Connection;
import java.sql.PreparedStatement;
import moudule1.DAO.entity.User;
import moudule1.DAO.util.ConnectionFactory;
import moudule1.DAO.util.IdGenerator;
import moudule1.DAO.util.IdGeneratorFactory;
import moudule1.DAO.util.JdbcUtil;
public class UserDaoOracleImpl extends UserDaoBaseImpl
{
public void insert(User user)
{
Connection con = null;
PreparedStatement ps = null;
StringBuffer sql = new StringBuffer("insert into dao_user(id, username, password, name, birthday)");
sql.append("values(?, ?, ?, ?, ?)");
try
{
con = ConnectionFactory.getConnection();
IdGenerator generator = IdGeneratorFactory.getIdGenerator();
generator.setProperty("sequence", "user_sequence");
user.setId(generator.nextId());
ps = con.prepareStatement(sql.toString());
int index = 1;
ps.setInt(index++, user.getId());
ps.setString(index++, user.getUserName());
ps.setString(index++, user.getPassword());
ps.setString(index++, user.getTruename());
ps.setDate(index++, user.getDate());
ps.executeUpdate();
}catch(Exception e)
{
e.printStackTrace();
}finally
{
JdbcUtil.close(ps, con);
}
}
}