jdbc(day04).txt

jsp连接数据库的事例代码,对多个数据库适用,以及jdbc的基础知识

                                    JDBC第四天    2007年6月7日
                                    
一、ID的High/Low算法
　　高位数字分别与低位数字相匹配，得到的数字是唯一的
　　减少与数据库的交互

二、ORM
１、类映射成表
　　类名与表名对应
２、属性定义映射成列，类型之间必须是兼容的
３、类关系映射成表关系

一对一双向关系
内存中都保存对方的一个引用
数据库中，表b的id是主键，也是外键，引用a表的id主键　－－　share pk
          表b中有一个字段aid是外键，引用a表的主键，并且有唯一约束　　－－　pk+fk
共享主键：
create table car_pk (
    id number(10,0) not null,
    name varchar2(15),
    serial varchar2(30),
    manufacturer varchar2(50),
    producedate date,
    primary key (id)
);

create table engine_pk (
    id number(10,0) not null,
    model varchar2(20),
    manufacturer varchar2(50),
    producedate date,
    primary key (id)
);

alter table engine_pk 
    add constraint fk_engine_car_pk 
    foreign key (id) 
    references car_pk(id);
    
外键＋唯一约束
create table car_fk (
    id number(10,0) not null,
    name varchar2(15) not null,
    serial varchar2(30) not null,
    manufacturer varchar2(50) not null,
    producedate date,
    primary key (id)
);

create table engine_fk (
    id number(10,0) not null,
    model varchar2(20) not null,
    manufacturer varchar2(50) not null,
    producedate date,
    carid number(10,0) unique,
    primary key (id)
);

alter table engine_fk 
    add constraint fk_engine_car_fk 
    foreign key (carid) 
    references car_fk(id);              
          
实体对象：在内存中有id属性的
值对象：没有id的，依赖其他对象存在


一对多关系          
一的一方保存多一方的一个集合，最好使用set，保证无重复元素
多的一方保存一一方的一个对象的引用
public class Order implements Serializable{
    private int id;
    private String owner;
    private String phone;
    private String address;
    private Set<Item> items = new HashSet<Item>();
}
public class Item implements Serializable{
    private int id;
    private String product;
    private int amount;
    private Order order;    
}    

create table ec_item (
    id number(10,0) not null,
    product varchar2(15) not null,
    amount number(10,0) not null,
    orderid number(10,0) not null,
    primary key (id)
);

create table ec_order (
    id number(10,0) not null,
    owner varchar2(15) not null,
    phone varchar2(15) not null,
    address varchar2(50),
    primary key (id)
);

alter table ec_item 
    add constraint fk_item_order 
    foreign key (orderid) 
    references ec_order(id);
    
    
多对多
双方都保存对方的多个引用
例子：学生选课
public class TarenaCourse implements Serializable{
    private int id;
    private String name;
    private int period;
    private Set<TarenaStudent> students = new HashSet<TarenaStudent>();    
}
public class TarenaStudent implements Serializable{
    private int id;
    private String name;
    private Date birthday;
    private Set<TarenaCourse> courses = new HashSet<TarenaCourse>();    
}

create table student (
    id number(10,0) not null,
    name varchar2(15) not null,
    birthday date,
    primary key (id)
);

create table student_course (
    sid number(10,0) not null,
    cid number(10,0) not null,
    primary key (sid, cid)
);

create table course (
    id number(10,0) not null,
    name varchar2(15) not null,
    perion number(10,0),
    primary key (id)
);

alter table student_course 
    add constraint fk_student
    foreign key (sid) 
    references student(id);

alter table student_course 
    add constraint fk_course 
    foreign key (cid) 
    references course(id);

通过学生姓名找课程    
select c.name from cource c,student s,student_course sc
where c.id=sc.cid and  s.id=sc.sid 
and s.name = 's1'  


三、继承关系     
public abstract class Computer implements Serializable{
    private int id;
    private int price;
    private String manufacturer;
}

public class Desktop extends Computer{
    private boolean isLCD;    
}

public class Notepad extends Computer{
    private float weight;
    private float thickness;    
}  

１、建３张表　table per class
子类中保存父类的主键作为外键
create table computer_tpc (
    id number(10,0) not null,
    price number(10,0) not null,
    manufacturer varchar2(30) not null,
    primary key (id)
);

create table desktop_tpc (
    computerid number(10,0) not null,
    islcd char(1),
    primary key (computerid)
);

create table notepad_tpc (
    computerid number(10,0) not null,
    weight float,
    thickness float,
    primary key (computerid)
);

alter table desktop_tpc 
    add constraint fk_desk_computer_tpc 
    foreign key (computerid) 
    references computer_tpc(id);

alter table notepad_tpc 
    add constraint fk_note_computer_tpc 
    foreign key (computerid) 
    references computer_tpc(id);

查找所有电脑的配制（只要是电脑就能被查出来）    
select c.id,c.price,d.islcd,n.weight,n.thickness
from computer c, desktop d,notepad n
where  c.id = d.computerid(+)
and  c.id = n.computer(+) 
    
２、建２张表      
create table desktop (
    id number(10,0) not null,
    price number(10,0) not null,
    manufacturer varchar2(30) not null,
    islcd char(1),
    primary key (id)
);

create table notepad (
    id number(10,0) not null,
    price number(10,0) not null,
    manufacturer varchar2(30) not null,
    weight float,
    thickness float,
    primary key (id)
);

３、建１张表
create table computer_tph (
    id number(10,0) not null,
    category char(1) not null,
    price number(10,0) not null,
    manufacturer varchar2(30) not null,
    islcd char(1),
    weight float,
    thickness float,
    primary key (id)
);