汽车的属性有: id, 品牌, 型号, 颜色, 价格;
车牌的属性有: 车牌号, 类型 (军用, 民用等), 材质, 颜色等
A, 老刘新买了一辆汽车, 并且上了新车牌; (两个表中创建)
B, 老刘的小车被小偷偷走了, 又买了一辆新车, 但是车牌不变; (创建一辆新车, 赋值)
C, 查看老刘的小车信息 (包括车牌号); (根据名字查)
D, 根据车牌号查找对应小车的信息
- -------------------------------------------------car.sql
- /*==============================================================*/
- /* DBMS name: ORACLE Version 11g */
- /* Created on: 2018/12/3 10:03:55 */
- /*==============================================================*/
- alter table carId
- drop constraint FK_CARID_RELATIONS_CAR;
- alter table users
- drop constraint FK_USERS_RELATIONS_CARID;
- drop table Car cascade constraints;
- drop index Relationship_2_FK;
- drop table carId cascade constraints;
- drop index Relationship_1_FK;
- drop table users cascade constraints;
- /*==============================================================*/
- /* Table: Car */
- /*==============================================================*/
- create table Car
- (
- c_id NUMBER(6) not null,
- c_brand VARCHAR2(12),
- c_model VARCHAR2(12),
- c_color VARCHAR2(8),
- c_price NUMBER(8),
- constraint PK_CAR primary key (c_id)
- );
- /*==============================================================*/
- /* Table: carId */
- /*==============================================================*/
- create table carId
- (
- i_id NUMBER(6) not null,
- c_id NUMBER(6),
- i_card VARCHAR2(10),
- i_type VARCHAR2(8),
- i_texture VARCHAR2(8),
- i_color VARCHAR2(6),
- constraint PK_CARID primary key (i_id)
- );
- /*==============================================================*/
- /* Index: Relationship_2_FK */
- /*==============================================================*/
- create index Relationship_2_FK on carId (
- c_id ASC
- );
- /*==============================================================*/
- /* Table: users */
- /*==============================================================*/
- create table users
- (
- u_id NUMBER(6) not null,
- i_id NUMBER(6),
- u_name VARCHAR2(8),
- constraint PK_USERS primary key (u_id)
- );
- /*==============================================================*/
- /* Index: Relationship_1_FK */
- /*==============================================================*/
- create index Relationship_1_FK on users (
- i_id ASC
- );
- alter table carId
- add constraint FK_CARID_RELATIONS_CAR foreign key (c_id)
- references Car (c_id);
- alter table users
- add constraint FK_USERS_RELATIONS_CARID foreign key (i_id)
- references carId (i_id);
- ----------------------------------------------------------------Car.java
- package com.jcl.pojo;
- import java.io.Serializable;
- import javax.persistence.*;
- import java.math.BigDecimal;
- /**
- * The persistent class for the CAR database table.
- *
- */
- @Entity
- @NamedQuery(name="Car.findAll", query="SELECT c FROM Car c")
- public class Car implements Serializable {
- private static final long serialVersionUID = 1L;
- @Id
- @Column(name="C_ID")
- private long cId;
- @Column(name="C_BRAND")
- private String cBrand;
- @Column(name="C_COLOR")
- private String cColor;
- @Column(name="C_MODEL")
- private String cModel;
- @Column(name="C_PRICE")
- private BigDecimal cPrice;
- //bi-directional one-to-one association to Carid
- @OneToOne(mappedBy="car")
- private Carid carid;
- public Car() {
- }
- public long getCId() {
- return this.cId;
- }
- public void setCId(long cId) {
- this.cId = cId;
- }
- public String getCBrand() {
- return this.cBrand;
- }
- public void setCBrand(String cBrand) {
- this.cBrand = cBrand;
- }
- public String getCColor() {
- return this.cColor;
- }
- public void setCColor(String cColor) {
- this.cColor = cColor;
- }
- public String getCModel() {
- return this.cModel;
- }
- public void setCModel(String cModel) {
- this.cModel = cModel;
- }
- public BigDecimal getCPrice() {
- return this.cPrice;
- }
- public void setCPrice(BigDecimal cPrice) {
- this.cPrice = cPrice;
- }
- public Carid getCarid() {
- return this.carid;
- }
- public void setCarid(Carid carid) {
- this.carid = carid;
- }
- public Car(long cId, String cBrand, String cColor, String cModel, BigDecimal cPrice, Carid carid) {
- super();
- this.cId = cId;
- this.cBrand = cBrand;
- this.cColor = cColor;
- this.cModel = cModel;
- this.cPrice = cPrice;
- this.carid = carid;
- }
- public Car(long cId, String cBrand, String cColor, String cModel, BigDecimal cPrice) {
- super();
- this.cId = cId;
- this.cBrand = cBrand;
- this.cColor = cColor;
- this.cModel = cModel;
- this.cPrice = cPrice;
- }
- @Override
- public String toString() {
- return "Car [cId=" + cId + ", cBrand=" + cBrand + ", cColor=" + cColor + ", cModel=" + cModel + ", cPrice="
- + cPrice + "]";
- }
- }
- ----------------------------------------------------------------Carid.java
- package com.jcl.pojo;
- import java.io.Serializable;
- import javax.persistence.*;
- /**
- * The persistent class for the CARID database table.
- *
- */
- @Entity
- @NamedQuery(name="Carid.findAll", query="SELECT c FROM Carid c")
- public class Carid implements Serializable {
- private static final long serialVersionUID = 1L;
- @Id
- @Column(name="I_ID")
- private long iId;
- @Column(name="I_CARD")
- private String iCard;
- @Column(name="I_COLOR")
- private String iColor;
- @Column(name="I_TEXTURE")
- private String iTexture;
- @Column(name="I_TYPE")
- private String iType;
- //bi-directional one-to-one association to Car
- @OneToOne
- @JoinColumn(name="C_ID")
- private Car car;
- //bi-directional one-to-one association to User
- @OneToOne(mappedBy="carid")
- private User user;
- public Carid() {
- }
- public long getIId() {
- return this.iId;
- }
- public void setIId(long iId) {
- this.iId = iId;
- }
- public String getICard() {
- return this.iCard;
- }
- public void setICard(String iCard) {
- this.iCard = iCard;
- }
- public String getIColor() {
- return this.iColor;
- }
- public void setIColor(String iColor) {
- this.iColor = iColor;
- }
- public String getITexture() {
- return this.iTexture;
- }
- public void setITexture(String iTexture) {
- this.iTexture = iTexture;
- }
- public String getIType() {
- return this.iType;
- }
- public void setIType(String iType) {
- this.iType = iType;
- }
- public Car getCar() {
- return this.car;
- }
- public void setCar(Car car) {
- this.car = car;
- }
- public User getUser() {
- return this.user;
- }
- public void setUser(User user) {
- this.user = user;
- }
- @Override
- public String toString() {
- return "Carid [iId=" + iId + ", iCard=" + iCard + ", iColor=" + iColor + ", iTexture=" + iTexture + ", iType="
- + iType + ", car=" + car + "]";
- }
- public Carid(long iId, String iCard, String iColor, String iTexture, String iType, Car car, User user) {
- super();
- this.iId = iId;
- this.iCard = iCard;
- this.iColor = iColor;
- this.iTexture = iTexture;
- this.iType = iType;
- this.car = car;
- this.user = user;
- }
- public Carid(long iId, String iCard, String iColor, String iTexture, String iType, Car car) {
- super();
- this.iId = iId;
- this.iCard = iCard;
- this.iColor = iColor;
- this.iTexture = iTexture;
- this.iType = iType;
- this.car = car;
- }
- }
- ----------------------------------------------------------------User.java
- package com.jcl.pojo;
- import java.io.Serializable;
- import javax.persistence.*;
- /**
- * The persistent class for the USERS database table.
- *
- */
- @Entity
- @Table(name="USERS")
- @NamedQuery(name="User.findAll", query="SELECT u FROM User u")
- public class User implements Serializable {
- private static final long serialVersionUID = 1L;
- @Id
- @Column(name="U_ID")
- private long uId;
- @Column(name="U_NAME")
- private String uName;
- //bi-directional one-to-one association to Carid
- @OneToOne
- @JoinColumn(name="I_ID")
- private Carid carid;
- public User() {
- }
- public long getUId() {
- return this.uId;
- }
- public void setUId(long uId) {
- this.uId = uId;
- }
- public String getUName() {
- return this.uName;
- }
- public void setUName(String uName) {
- this.uName = uName;
- }
- public Carid getCarid() {
- return this.carid;
- }
- public void setCarid(Carid carid) {
- this.carid = carid;
- }
- public User(long uId, String uName, Carid carid) {
- super();
- this.uId = uId;
- this.uName = uName;
- this.carid = carid;
- }
- @Override
- public String toString() {
- return "User [uId=" + uId + ", uName=" + uName + ", carid=" + carid + "]";
- }
- }
- ---------------------------------------------------------------Demo.java
- package com.jcl.test;
- import java.math.BigDecimal;
- import java.util.List;
- import javax.persistence.EntityManager;
- import javax.persistence.EntityManagerFactory;
- import javax.persistence.EntityTransaction;
- import javax.persistence.Persistence;
- import javax.swing.text.Caret;
- import org.junit.After;
- import org.junit.Before;
- import org.junit.Ignore;
- import org.junit.Test;
- import com.jcl.pojo.Car;
- import com.jcl.pojo.Carid;
- import com.jcl.pojo.User;
- public class Demo1 {
- EntityManager em;
- EntityTransaction tx ;
- EntityManagerFactory factory;
- private String jpql;
- @Before
- public void init(){
- factory =
- Persistence.createEntityManagerFactory("HomeWork-JPQL");
- em = factory.createEntityManager();
- tx = em.getTransaction();
- tx.begin();
- }
- @After
- public void destroy(){
- tx.commit();
- if(em!=null){
- em.close();
- }
- if(factory!=null)
- factory.close();
- }
- /**
- * A, 老刘新买了一辆汽车, 并且上了新车牌; (两个表中创建)
- B, 老刘的小车被小偷偷走了, 又买了一辆新车, 但是车牌不变; (创建一辆新车, 赋值)
- C, 查看老刘的小车信息 (包括车牌号); (根据名字查)
- D, 根据车牌号查找对应小车的信息
- * */
- //@Test
- @Ignore
- public void answerA(){
- try {
- Car car = new Car(000001l, "保时捷", "白色", "ccc", new BigDecimal(180000));
- Carid carid = new Carid(000001l, "京 A-666", "白色", "民用", "铁", car);
- User user = new User(000001l, "老刘", carid);
- em.persist(car);
- em.persist(carid);
- em.persist(user);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- //@Test
- @Ignore
- public void answerB(){
- try {
- // 新买一辆车
- Car car = new Car(000002l, "宝马", "黑色", "ccc", new BigDecimal(260000));
- em.persist(car);
- // 获取用户车牌号 id
- jpql = "select u from User u where uName = ?0";
- User user = em.createQuery(jpql, User.class).setParameter(0, "老刘").getSingleResult();
- long iId = user.getCarid().getIId();
- // 根据车牌号 id 修改车 id
- Carid find = em.find(Carid.class, iId);
- find.setCar(car);
- em.persist(find);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- @Test
- //@Ignore
- public void answerC() {
- jpql = "select u from User u where uName = ?0";
- User user = em.createQuery(jpql, User.class).setParameter(0, "老刘").getSingleResult();
- long iId = user.getCarid().getIId();
- // 根据车牌号 id 查找车 id
- Carid carid = em.find(Carid.class, iId);
- long cid = carid.getCar().getCId();
- Car find = em.find(Car.class,cid);
- System.out.println(find);
- }
- @Test
- //@Ignore
- public void answerD() {
- jpql ="select c from Carid c where iId = ?0";
- Carid carid = em.createQuery(jpql,Carid.class).setParameter(0,000001l).getSingleResult();
- System.out.println(carid);
- }
- }
来源: http://www.bubuko.com/infodetail-2871871.html