Olá,
estou em duvida como converter um SQL para EJBHL …
duvida de sintase abaixo algumas classes que uso , estou usando JPA/Hibernate …
alguém poderia me ajudar …
abs
---sql
SELECT ic.certificate_id,
icd.cn titular,
ica.name ac,
ic.notafter ate,
ic.notbefore de,
ic.certificate_state situacao,
ict.certificate_family icp,
ict.name tipo,
idm.name modelo,
id.serialnumber serie,
iu.name usuario
FROM certificate ic
left join certificate_type ict on ic.certificate_type_id = ict.certificate_type_id
left join certificate_dn icd on ic.dn_id = icd.dn_id
left join ca ica on ict.ca_id = ica.ca_id
left join publickey ipk on ic.publickey_id = ipk.publickey_id
left join key_container ikc on ipk.publickey_id = ikc.publickey_id
left join device id on ikc.device_id = id.device_id
left join device_model idm on id.device_model_id = idm.device_model_id
left join user iu on id.device_model_id = idm.device_model_id
where ($P{ac} = 0 or ica.ca_id = $P{ac} )
order by 3,2,5,1
@Entity
@Table(name="CERTIFICATE", uniqueConstraints = {@UniqueConstraint(columnNames={"serialNumber"})})
public class ImsDigitalCertificateImpl extends AbstractDigitalCertificate implements ImsDigitalCertificate {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "CERTIFICATE_ID")
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "CERTIFICATE_PARENT", referencedColumnName = "CERTIFICATE_ID", updatable = false, insertable = false)
@OrderBy(value="id asc")
private ImsDigitalCertificateImpl parent;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "parent")
private List<ImsDigitalCertificateImpl> children = new ArrayList<ImsDigitalCertificateImpl>();
@Column(name= "certificate_state", columnDefinition="integer", nullable = true)
@Type(
type = "br.com.xxxxx.core.common.GenericEnumUserType",
parameters = {
@Parameter(
name = "enumClassName",
value = "br.com.xxxxx.core.data.model.certificate.CertificateState"),
@Parameter(
name = "identifierMethod",
value = "number"),
@Parameter(
name = "valueOfMethod",
value = "valueOf")
}
)
private CertificateState certState;
@Column(name= "object_state", columnDefinition="integer", nullable = true)
@Type(
type = "br.com.xxxxx.core.common.GenericEnumUserType",
parameters = {
@Parameter(
name = "enumClassName",
value = "br.com.xxxxx.ims.common.ImsObjectState"),
@Parameter(
name = "identifierMethod",
value = "number"),
@Parameter(
name = "valueOfMethod",
value = "valueOf")
}
)
private ImsObjectState objectState;
@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
@JoinColumn ( name = "CERTIFICATE_TYPE_ID")
private ImsCertificateType certificateType;
//private transient CertificateAuthority ca;
@OneToOne(cascade = (CascadeType.PERSIST))
@JoinColumn(name = "PUBLICKEY_ID")
private ImsPublicKeyImpl publicKey;
@Column
private String serialNumber;
@Column
private Date notBefore;
@Column
private Date notAfter;
@Column
@Lob
private String encodedBase64;
@ManyToMany(targetEntity = ImsApplicationPolicy.class, cascade = (CascadeType.PERSIST))
@JoinTable(
name="CERTIFICATE_EKU",
joinColumns = @JoinColumn(name="CERTIFICATE_ID", referencedColumnName = "CERTIFICATE_ID") ,
inverseJoinColumns = @JoinColumn(name = "POLICY_ID", referencedColumnName = "POLICY_ID")
)
private List<ImsApplicationPolicy> appPolicies = new ArrayList<ImsApplicationPolicy>();
@OneToOne
@JoinColumn(name = "REQUEST_ID")
private ImsCertificateRequestImpl request;
@ManyToOne(cascade = (CascadeType.PERSIST))
@JoinColumn(name = "USER_ID")
private ImsUserImpl user;
@Transient
private X509Certificate x509Certificate;
@CollectionOfElements(fetch=FetchType.LAZY)
@JoinTable(name="CERT_KU_AUX", joinColumns=@JoinColumn(name="CERTIFICATE_ID", referencedColumnName="CERTIFICATE_ID"))
@Column(name="ku")
@Type(
type = "br.com.xxxxx.core.common.GenericEnumUserType",
parameters = {
@Parameter(
name = "enumClassName",
value = "br.com.xxxxx.core.data.model.certificate.KeyUsage"),
@Parameter(
name = "identifierMethod",
value = "number"),
@Parameter(
name = "valueOfMethod",
value = "valueOf")
}
)
private List<KeyUsage> kus = new ArrayList<KeyUsage>();
@OneToMany(fetch = FetchType.LAZY, mappedBy = "cert", cascade = (CascadeType.ALL))
private List<ImsCertificateAdditionalData> additionalData = new ArrayList<ImsCertificateAdditionalData>();
@OneToOne(cascade = (CascadeType.ALL))
@JoinColumn(name = "DN_ID")
private ImsCertificateDn dn;
------------------
@Entity
@Table(name="CERTIFICATE_TYPE")
public class ImsCertificateType extends AbstractCertificateType implements CertificateType {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "CERTIFICATE_TYPE_ID")
private Long id;
@Column(nullable = false)
private String name;
@Column
private String description;
@OneToOne(cascade = (CascadeType.PERSIST))
@JoinColumn(name = "POLICY_ID")
private ImsCertificatePolicy certificatePolicy;
@OneToMany(mappedBy = "certificateType")
private List<ImsDigitalCertificateImpl> cert =new ArrayList<ImsDigitalCertificateImpl>();
@OneToMany(mappedBy = "certificateType",cascade = (CascadeType.PERSIST))
private List<ImsCertificateRequestImpl> requests= new ArrayList<ImsCertificateRequestImpl>();
@Column
private String templateId;
@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
@JoinColumn ( name = "CA_ID")
private ImsCertificateAuthorityImpl ca;
@Column(name= "certificate_family", columnDefinition="integer", nullable = true)
@Type(
type = "br.com.xxxxx.core.common.GenericEnumUserType",
parameters = {
@Parameter(
name = "enumClassName",
value = "br.com.xxxxx.core.data.model.certificate.CertificateFamily"),
@Parameter(
name = "identifierMethod",
value = "number"),
@Parameter(
name = "valueOfMethod",
value = "valueOf")
}
)
private CertificateFamily family;
-----
@Entity
@Table(name="CERTIFICATE_DN")
public class ImsCertificateDn extends AbstractCertificateDn {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "DN_ID")
private Long id;
@Column
private String cn;
@Column
private String country;
@Column
private String email;
@Column
private String locality;
@Column
private String organization;
@Column
private String state;
//inicio novo
@OneToMany(mappedBy = "dn",cascade =javax.persistence.CascadeType.ALL)
private List<ImsOusAux> ousauxs = new ArrayList<ImsOusAux>();
//fim novo
@CollectionOfElements(fetch=FetchType.LAZY)
@JoinTable(name="OUS_AUX", joinColumns=@JoinColumn(name="DN_ID", referencedColumnName="DN_ID"))
@Column(name="ou")
private List<String> ous = new ArrayList<String>();
//inicio novo
public void addOrganizationalUnit(ImsOusAux ousaux) {
ousauxs.add(ousaux);
}
public List<ImsOusAux> getOrganizationalUnit() {
return ousauxs;
}
public void setOrganizationalUnit(List<ImsOusAux> ousauxs) {
this.ousauxs = ousauxs;
}