Duvida SQL X EJQL

Assuntos Gerais
#1

Olá,
estou usando JPA/Hibernate …

algumas duvidas pois quero converter o sql abaixo para ejql pois a finalidade que essa query rode em qualquer banco (Oracle,MYSQL,SYBASE,SQLSERVER) minhas duvidas :

  • como faria alter join pelo ejql teria algum exemplo pra analisar

  • o campo data de parametro hoje pelo da tela jsp como string e passo para jasper como String já no ejql passaria como Date pois o objeto está como Date ??? é isso ou não ??? o parametro que vou usar para esse campo Date do objeto tem que ser Date ???

se alguém puder me ajudar agradeceria…

abs


SELECT  *,
       icd.cn titular,
       ica.name ac,
       ic.notafter de,
       ic.notbefore ate,
       ic.certificate_state situacao,
       '' icp,
       ict.name tipo,
       idm.name modelo,
       id.serialnumber serie,
       iu.name
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 (ic.notafter     >= '2008-04-13' and ic.notafter <= '2099-04-05')
#2

JOVEM,

Se vc usar o SQL padrão ANSI, esse deverá rodar em qualquer SGDB.
Para tratar as datas usadas no between, vc poderia usar o "?" do


String sql = " SELECT  *,   
       icd.cn titular,   
       ica.name ac,   
       ic.notafter de,   
       ic.notbefore ate,   
       ic.certificate_state situacao,   
       '' icp,   
       ict.name tipo,   
       idm.name modelo,   
       id.serialnumber serie,   
       iu.name   
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 (ic.notafter     >= ? and ic.notafter ? ) ";

PreparedStatement stmt = null;
ResultSet rs = null;

stmt = ConnectionPool.getConnection(getRequest()).prepareStatement(sql.toString());
stmt.setObject(1, dataInicio);
stmt.setObject(2, dataFim);
rs = stmt.executeQuery();
#3

Para voce converter esse SQL para JPQL, voce vai precisar mapear todas as tabelas envolvidas em entidades JPA e fazer seus devidos relacionamentos ( @OneToOne, @OneToMany e etc ).

Depois de tudo isso feito, fica tranquilo criar o JPQL, como voce nao colocou nos post as entidades JPA envolvidas nao tem como criar o JPQL.

#4 
select ic
from _certificate ic
left join  ic.certificate_type_id ict
left join ic.dn_id icd
... (Coloque os outros Joins aqui)
where (ic.notafter     >= ? and ic.notafter <= ?)

Lembrando que ao inves de usar o nome da tabela (_certificate) deve se usar a classe Java corresponde a tabela.

[]'s

#5

se puder me ajudar …as classes estão abaixo queria ter uma idéia como montar …

abs



@Entity
@Table(name="CERTIFICATE", uniqueConstraints = {@UniqueConstraint(columnNames={"serialNumber"})})
public class DigitalCertificateImpl extends AbstractDigitalCertificate implements DigitalCertificate {
	@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 DigitalCertificateImpl parent;
	@OneToMany(fetch = FetchType.LAZY, mappedBy = "parent")
	private List<DigitalCertificateImpl> children = new ArrayList<DigitalCertificateImpl>();
	@Column(name= "certificate_state", columnDefinition="integer", nullable = true)
	private CertificateState certState;
	@Column(name= "object_state", columnDefinition="integer", nullable = true)
	private ObjectState objectState;
	@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn ( name = "CERTIFICATE_TYPE_ID")
	private CertificateType certificateType;	
	@OneToOne(cascade = (CascadeType.PERSIST))
	@JoinColumn(name = "PUBLICKEY_ID")
	private PublicKeyImpl publicKey;
	@Column
	private String serialNumber;
	@Column
	private Date notBefore;
	@Column
	private Date notAfter;
	@Column
	@Lob
	private String encodedBase64;
	@ManyToMany(targetEntity = ApplicationPolicy.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<ApplicationPolicy> appPolicies = new ArrayList<ApplicationPolicy>();
	@OneToOne
	@JoinColumn(name = "REQUEST_ID")
	private CertificateRequestImpl request;	
	@ManyToOne(cascade = (CascadeType.PERSIST))
	@JoinColumn(name = "USER_ID")
	private UserImpl 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")
	private List<KeyUsage> kus = new ArrayList<KeyUsage>();
	@OneToMany(fetch = FetchType.LAZY, mappedBy = "cert", cascade = (CascadeType.ALL))
	private List<CertificateAdditionalData> additionalData = new ArrayList<CertificateAdditionalData>();
	@OneToOne(cascade = (CascadeType.ALL))
	@JoinColumn(name = "DN_ID")
	private CertificateDn dn;	

-------------



@Entity
@Table(name="USER")
public class UserImpl extends AbstractUser implements User {
	@Id
	@GeneratedValue
	@Column(name = "USER_ID")
	/** Identificador único */
	private Long id;
	@Column(nullable = false)
	/** Nome do usuário */
	private String name;
	@Column(nullable = false)
	/** E-mail do usuário */
	private String email;
	@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn (name = "ROLE_ID")
	
    private RoleImpl role;	
	@Column(nullable = false)
	private String ldapPath;
	@OneToMany(mappedBy = "user",cascade =javax.persistence.CascadeType.ALL)
    private List<DeviceImpl> devices = new ArrayList<DeviceImpl>();
	@Column(name = "object_state", columnDefinition="integer", nullable = true)
	
	protected ObjectState state;
	@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn ( name = "GROUP_ID")	
	private GroupImpl group;
	@OneToOne(cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn(name = "ADDRESS_ID")
	private Address address;
	@Column
	@Temporal(TemporalType.DATE)
	private Calendar birthDate;
	@Column
	private String profession;
	@OneToMany(mappedBy = "user",cascade =javax.persistence.CascadeType.ALL,fetch = FetchType.LAZY)
	private List<Document> documents = new ArrayList<Document>();
	
	@Column
	@Temporal(TemporalType.DATE)
	private Calendar admissionDate;
	@Column(unique=true, nullable=false) 
	private String upn;
	@OneToMany(mappedBy = "user",cascade =javax.persistence.CascadeType.ALL)
	private List<CertificateRequestImpl> requests = new ArrayList<CertificateRequestImpl>();
	@Column
	private int logonAttempts;
	@OneToMany(mappedBy = "user",cascade =javax.persistence.CascadeType.ALL)
	private List<UserSectionImpl> sections = new ArrayList<UserSectionImpl>();
	@OneToMany(mappedBy = "user",cascade =javax.persistence.CascadeType.ALL)
	private List<CredentialImpl> credentials = new ArrayList<CredentialImpl>(); 
	
	@OneToMany(mappedBy = "user",cascade =javax.persistence.CascadeType.ALL)
	private List<DigitalCertificateImpl> certs = new ArrayList<DigitalCertificateImpl>();	
	@OneToMany(mappedBy = "user")
	private List<StateChange> stateChange = new ArrayList<StateChange>();
---


@Entity
@Table(name="CERTIFICATE_TYPE")
public class CertificateType 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 CertificatePolicy certificatePolicy;
	@OneToMany(mappedBy = "certificateType")
	private List<DigitalCertificateImpl> cert =new ArrayList<DigitalCertificateImpl>();
	@OneToMany(mappedBy = "certificateType",cascade = (CascadeType.PERSIST))
	private List<CertificateRequestImpl> requests= new ArrayList<CertificateRequestImpl>();
	@Column
	private String templateId;	
	@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn ( name = "CA_ID")
	private CertificateAuthorityImpl ca;
	@Column(name= "certificate_family", columnDefinition="integer", nullable = true)
	
	private CertificateFamily family;

---

@Entity
@Table(name="CERTIFICATE_DN")
public class CertificateDn 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;
	@OneToMany(mappedBy = "dn",cascade =javax.persistence.CascadeType.ALL)
	private List<OusAux> ousauxs = new ArrayList<OusAux>();
	@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>();	

---------------

@Entity
@Table(name="_CA")
public class CertificateAuthorityImpl extends AbstractCertificateAuthority implements CertificateAuthority {

	@Id
	@GeneratedValue
	@Column(name = "CA_ID")
	private long id;
	@Column
	private String name;
	@Column
	private String description;
	@OneToOne(cascade = (CascadeType.PERSIST))
	@JoinColumn(name = "DN_ID")
	private CertificateDn dn;	
	@ManyToMany(fetch=FetchType.LAZY, targetEntity = RegistrationAuthorityImpl.class, cascade = (CascadeType.PERSIST))
	
	@JoinTable(
		name="_CA_RA",
		joinColumns = @JoinColumn(name="CA_ID", referencedColumnName = "CA_ID") ,
		inverseJoinColumns = @JoinColumn(name = "RA_ID", referencedColumnName = "RA_ID")
    )
    @OrderBy(value="id asc")    
	private Set<RegistrationAuthorityImpl> ras = new HashSet<RegistrationAuthorityImpl>();
	@Column(name = "ca_state", columnDefinition="integer", nullable = true)
	
	private CertificateAuthorityState state;
	@OneToMany(mappedBy = "ca", cascade =javax.persistence.CascadeType.ALL)
	private List<CertificateType> certTypes;
-------------
@Entity
@Table(name="DEVICE")
public class DeviceImpl extends AbstractDevice implements Device {
	
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name = "DEVICE_ID")
	private Long id;
	@Column(nullable = false)
	private String name;
	
	@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn(name = "DEVICE_MODEL_ID")
    private DeviceModel model;
	
	@Column(nullable = false)
	private String serialNumber;
	@Column
	private String description;	
	@Column(name = "device_state", columnDefinition="integer", nullable = true)
	
	private DeviceState state;
	@Column(name= "object_state", columnDefinition="integer", nullable = true)
	
	private ObjectState objectState;	
	@OneToMany(mappedBy = "device",cascade =javax.persistence.CascadeType.ALL)	
	private Set<KeyContainerImpl> keys = new HashSet<KeyContainerImpl>();
	@ManyToOne(cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn ( name = "USER_ID")	
	private UserImpl user;
-----------

@Entity
@Table(name = "DEVICE_MODEL")
public class DeviceModel extends AbstractDeviceModel {
	
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	@Column(name = "DEVICE_MODEL_ID")
	private Long id;
	@Column(nullable = false)
	private String name;
	@Column(nullable = false)
	private String atr;
	@Column(nullable = false)
	private String cspProviderName;
	@Column(nullable = false)
	private String pkcs11ProviderFileName;
	@Column(nullable = false)
	private boolean active;
	@ManyToOne(fetch = FetchType.EAGER,cascade =javax.persistence.CascadeType.ALL)
	@JoinColumn ( name = "VENDOR_ID")	
	private DeviceVendor vendor;
	@OneToMany(mappedBy = "model",cascade =javax.persistence.CascadeType.ALL)
	private List<DeviceImpl> devices = new ArrayList<DeviceImpl>();
#6