DROP DATABASE IF EXISTS `pharmacy_db`;
CREATE DATABASE `pharmacy_db`
CHARACTER SET 'cp1251'
COLLATE 'cp1251_general_ci';
USE `pharmacy_db`;
#
# Structure for the `medicine` table :
#
DROP TABLE IF EXISTS `medicine`;
CREATE TABLE `medicine` (
`mid` int(11) NOT NULL,
`name` varchar(150) DEFAULT NULL,
`description` varchar(500) DEFAULT NULL,
PRIMARY KEY (`mid`),
UNIQUE KEY `mid` (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
#
# Data for the `medicine` table (LIMIT 0,500)
#
INSERT INTO `medicine` (`mid`, `name`, `description`) VALUES
(1,'Лекарство 1','Лекарственный препарат 1'),
(2,'Лекарство 2','Лекарственный препарат 2'),
(3,'Лекарство 3','Лекарственный препарат 3'),
(4,'Лекарство 4','Лекарственный препарат 4');
COMMIT;
#
# Structure for the `supplier` table :
#
DROP TABLE IF EXISTS `supplier`;
CREATE TABLE `supplier` (
`sid` int(11) NOT NULL,
`name` varchar(250) DEFAULT NULL,
`address` varchar(250) DEFAULT NULL,
`phone` varchar(250) DEFAULT NULL,
`contact` varchar(250) DEFAULT NULL,
PRIMARY KEY (`sid`),
UNIQUE KEY `sid` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
#
# Data for the `supplier` table (LIMIT 0,500)
#
INSERT INTO `supplier` (`sid`, `name`, `address`, `phone`, `contact`) VALUES
(1,'Поставщик 1','Адрес 1','Телефон 1','Контакт 1'),
(2,'Поставщик 2','Адрес 2','Телефон 2','Контакт 2'),
(3,'Поставщик 3','Адрес 3','Телефон 3','Контакт 3');
COMMIT;
#
# Structure for the `supply` table :
#
DROP TABLE IF EXISTS `supply`;
CREATE TABLE `supply` (
`spid` int(11) NOT NULL,
`name` varchar(150) DEFAULT NULL,
`valueof` varchar(500) DEFAULT NULL,
`dateof` varchar(500) DEFAULT NULL,
`cost` int(11) NOT NULL,
`mid` int(11) NOT NULL,
`sid` int(11) NOT NULL,
PRIMARY KEY (`spid`),
UNIQUE KEY `spid` (`spid`),
KEY `mid` (`mid`),
KEY `sid` (`sid`),
CONSTRAINT `supply_medicine_fk` FOREIGN KEY (`mid`) REFERENCES `medicine` (`mid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `supply_supplier_fk` FOREIGN KEY (`sid`) REFERENCES `supplier` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
#
# Data for the `supply` table (LIMIT 0,500)
#
INSERT INTO `supply` (`spid`, `name`, `valueof`, `dateof`, `cost`, `mid`, `sid`) VALUES
(1,'Закупка лекарства 1','3 коробки по 40 пачек','10.06.2012',3500000,1,1),
(2,'Закупка лекарства 2','2 коробки по 20 пачек','11.06.2012',600000,2,1);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
package medicine.ejb.cmp;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Collection;
@Entity
@Table(catalog = "pharmacy_db", name = "medicine")
@NamedQueries(
{
@NamedQuery(name = "Medicine.findAll", query = "SELECT c FROM Medicine c order by c.name "),
@NamedQuery(name = "Medicine.getLastID", query = "select max ( c.id ) from Medicine c")
}
)
public class Medicine implements Serializable, IID {
private int id = -1;
@Id
@Column(name = "mid")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int id() {
return getId();
}
private String name;
@Basic
@Column(name = "name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
private String description;
@Basic
@Column(name = "description")
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
private Collection
@OneToMany(mappedBy = "medicine")
public Collection
return supplies;
}
public void setSupplies(Collection
this.supplies = supplies;
}
@Override
public String toString() {
return getName();
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Medicine)) return false;
Medicine medicine = (Medicine) o;
if (id != medicine.id) return false;
return true;
}
}
package medicine.ejb.cmp;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Collection;
@Entity
@Table(catalog = "pharmacy_db", name = "supplier")
@NamedQueries(
{
@NamedQuery(name = "Supplier.findAll", query = "SELECT c FROM Supplier c order by c.name "),
@NamedQuery(name = "Supplier.getLastID", query = "select max ( c.id ) from Supplier c")
}
)
public class Supplier implements Serializable, IID {
private int id = -1;
@Id
@Column(name = "sid")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int id() {
return getId();
}
private String name;
@Basic
@Column(name = "name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
private String address;
@Basic
@Column(name = "address")
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
private String phone;
@Basic
@Column(name = "phone")
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
private String contact;
@Basic
@Column(name = "contact")
public String getContact() {
return contact;
}
public void setContact(String contact) {
this.contact = contact;
}
private Collection
@OneToMany(mappedBy = "supplier")
public Collection
return supplies;
}
public void setSupplies(Collection
this.supplies = supplies;
}
@Override
public String toString() {
return getName();
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Supplier)) return false;
Supplier supplier = (Supplier) o;
if (id != supplier.id) return false;
return true;
}
}
package medicine.ejb.cmp;
import javax.persistence.*;
import java.io.Serializable;
@Entity
@Table(catalog = "pharmacy_db", name = "supply")
@NamedQueries(
{
@NamedQuery(name = "Supply.findAllByMedicine",
query = "SELECT c FROM Supply c where c.medicine.id = :medicine_id order by c.dateof "
),
@NamedQuery(name = "Supply.findAllBySupplier",
query = "SELECT c FROM Supply c where c.supplier.id = :supplier_id order by c.dateof "
),
@NamedQuery(name = "Supply.findAllBySupplierAndMedicine",
query = "SELECT c FROM Supply c where c.supplier.id = :supplier_id and c.medicine.id = :medicine_id order by c.dateof "
),
@NamedQuery(name = "Supply.findAll", query = "SELECT c FROM Supply c order by c.dateof "),
@NamedQuery(name = "Supply.getLastID", query = "select max ( c.id ) from Supply c")
}
)
public class Supply implements Serializable, IID {
private int id = -1;
@Id
@Column(name = "spid")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int id() {
return getId();
}
private String name;
@Basic
@Column(name = "name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
private String dateof;
@Basic
@Column(name = "dateof")
public String getDateof() {
return dateof;
}
public void setDateof(String dateof) {
this.dateof = dateof;
}
private String valueof;
@Basic
@Column(name = "valueof")
public String getValueof() {
return valueof;
}
public void setValueof(String valueof) {
this.valueof = valueof;
}
private int cost;
@Basic
@Column(name = "cost")
public int getCost() {
return cost;
}
public void setCost(int cost) {
this.cost = cost;
}
private Medicine medicine;
@ManyToOne
@JoinColumn(name = "mid", referencedColumnName = "mid", nullable = false)
public Medicine getMedicine() {
return medicine;
}
public void setMedicine(Medicine medicine) {
this.medicine = medicine;
}
private Supplier supplier;
@ManyToOne
@JoinColumn(name = "sid", referencedColumnName = "sid", nullable = false)
public Supplier getSupplier() {
return supplier;
}
public void setSupplier(Supplier supplier) {
this.supplier = supplier;
}
@Override
public String toString() {
return getDateof()+". "+getName();
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Supply)) return false;
Supply supply = (Supply) o;
if (id != supply.id) return false;
return true;
}
}
package medicine.ejb.session;
import medicine.ejb.cmp.Medicine;
import medicine.ejb.cmp.Supplier;
import medicine.ejb.cmp.Supply;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;
@Stateless(name = "MedicineSessionBeanEJB")
public class MedicineSessionBean implements IMedicineSessionBean, IMedicineSessionBeanLocal {
@PersistenceContext
private EntityManager em;
private void supplierMerge(Supplier supplier, Supplier ejb) {
ejb.setAddress( supplier.getAddress() );
ejb.setContact( supplier.getContact() );
ejb.setName( supplier.getName() );
ejb.setPhone( supplier.getPhone() );
}
public void supplierAdd(Supplier supplier) {
try {
Supplier ejb = new Supplier();
ejb.setId(getLastID("Supplier.getLastID"));
supplierMerge(supplier, ejb);
em.persist(ejb);
} catch (Exception e) {
e.printStackTrace();
}
}
public void supplierUpdate(Supplier supplier) {
try {
Supplier ejb = getSupplier(supplier.getId());
supplierMerge(supplier, ejb);
em.persist(ejb);
} catch (Exception e) {
e.printStackTrace();
}
}
public void supplierDelete(Supplier supplier) {
try {
em.remove(getSupplier(supplier.getId()));
} catch (Exception e) {
e.printStackTrace();
}
}
public List
try {
Query query = em.createNamedQuery("Supplier.findAll");
return query.getResultList();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public Supplier supplierLoad(Supplier supplier) {
try {
Supplier ejb = getSupplier(supplier.getId());
return ejb;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private Supplier getSupplier(Integer recordID) {
return em.find(Supplier.class, recordID);
}
// ------------------------------------------------------------
private void supplyMerge(Supply supply, Supply ejb) {
ejb.setCost( supply.getCost() );
ejb.setDateof( supply.getDateof() );
ejb.setName( supply.getName() );
ejb.setValueof( supply.getValueof() );
ejb.setMedicine( getMedicine(supply.getMedicine().getId()) );
ejb.setSupplier( getSupplier(supply.getSupplier().getId()) );
}
public void supplyAdd(Supply supply) {
try {
Supply ejb = new Supply();
ejb.setId(getLastID("Supply.getLastID"));
supplyMerge(supply, ejb);
em.persist(ejb);
} catch (Exception e) {
e.printStackTrace();
}
}
public void supplyUpdate(Supply supply) {
try {
Supply ejb = getSupply(supply.getId());
supplyMerge(supply, ejb);
em.persist(ejb);
} catch (Exception e) {
e.printStackTrace();
}
}
public void supplyDelete(Supply supply) {
try {
em.remove(getSupply(supply.getId()));
} catch (Exception e) {
e.printStackTrace();
}
}
public List
try {
Query query = null;
if( (medicine!=null && medicine.getId() != -1) && (supplier==null || supplier.getId() == -1)) {
query = em.createNamedQuery("Supply.findAllByMedicine");
query.setParameter("medicine_id", medicine.getId());
} else
if( (medicine==null || medicine.getId() == -1) && (supplier!=null && supplier.getId() != -1)) {
query = em.createNamedQuery("Supply.findAllBySupplier");
query.setParameter("supplier_id", supplier.getId());
} else
if( (medicine!=null && medicine.getId() != -1) && (supplier!=null && supplier.getId() != -1)) {
query = em.createNamedQuery("Supply.findAllBySupplierAndMedicine");
query.setParameter("supplier_id", supplier.getId());
query.setParameter("medicine_id", medicine.getId());
} else {
query = em.createNamedQuery("Supply.findAll");
}
return query.getResultList();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public Supply supplyLoad(Supply supply) {
try {
Supply ejb = getSupply(supply.getId());
return ejb;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private Supply getSupply(Integer recordID) {
return em.find(Supply.class, recordID);
}
// ------------------------------------------------------------
public void medicineMerge(Medicine medicine, Medicine ejb) {
ejb.setDescription( medicine.getDescription() );
ejb.setName( medicine.getName() );
}
public void medicineAdd(Medicine medicine) {
try {
Medicine ejb = new Medicine();
ejb.setId(getLastID("Medicine.getLastID"));
medicineMerge(medicine, ejb);
em.persist(ejb);
} catch (Exception e) {
e.printStackTrace();
}
}
public void MedicineUpdate(Medicine medicine) {
try {
Medicine ejb = getMedicine(medicine.getId());
medicineMerge(medicine, ejb);
em.persist(ejb);
} catch (Exception e) {
e.printStackTrace();
}
}
public void MedicineDelete(Medicine medicine) {
try {
em.remove(getMedicine(medicine.getId()));
} catch (Exception e) {
e.printStackTrace();
}
}
public List
try {
Query query = em.createNamedQuery("Medicine.findAll");
return query.getResultList();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public Medicine medicineLoad(Medicine medicine) {
try {
Medicine ejb = getMedicine(medicine.getId());
return ejb;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private Medicine getMedicine(Integer recordID) {
return em.find(Medicine.class, recordID);
}
// ------------------------------------------------------------
Integer getLastID(String namedQuery) {
Query query = em.createNamedQuery(namedQuery);
Object result = query.getSingleResult();
return getLastID((Integer)result );
}
Integer getLastID(Integer lastID) {
if(lastID==null) return 1;
else return lastID + 1;
}
}
| Дата | Выполнено, % |
|---|---|
| 2020-05-31 00:11:45 | 10 |
| 2020-05-30 21:11:42 | 100 |
| 2020-05-30 21:12:19 | 100 |