It might also be worthwhile to learn the Java Persistence API. Here is a code sample defining named queries (with named placeholders):
@Entity
@Table(name = "passports")
@NamedQueries({
@NamedQuery(name = "PassportEntity.findAll", query = "SELECT p FROM PassportEntity p"),
@NamedQuery(name = "PassportEntity.countUniqueAllClients",
query = "SELECT count(p) FROM PassportEntity p"
+ " WHERE p.number = :number"
+ " AND p.country = :country"),
})
public class PassportEntity implements Serializable {
@Version
private int version;
@Id
@Column(unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Temporal(TemporalType.TIMESTAMP)
@Column(nullable = false)
private Date expires;
@Column(nullable = false, length = 15)
private String number;
@Column(name = "country", nullable = false, length = 2)
private String country;
// bi-directional many-to-one association to ClientEntity
@ManyToOne
@JoinColumn(name = "client_id", nullable = false)
private ClientEntity client;
// Getters & Setters (not needed for version) ...
}
Usage example 1: (JEE, e.g. EJB business rule, using injection of application server-managed resources)
:
@PersistenceContext
private EntityManager em;
:
public long countPassports(Integer clientId, String ppNumber, CountryEnum ppCountry) {
return em.createNamedQuery("PassportEntity.countUniqueAllClients", Long.class)
.setParameter("number", ppNumber)
.setParameter("country", ppCountry.name())
.getSingleResult();
}
:
:
... with the persistence.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<persistence
version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
>
<persistence-unit name="TestDB" transaction-type="JTA">
<jta-data-source>jdbc/testDataSource</jta-data-source>
<!-- for Glassfish, create JDBC connection pool as jdbc/testDataSource -->
<class>module.PassportEntity</class>
:
</persistence-unit>
</persistence>
Usage example 2: (Non-EE/standalone Java)
:
public class MyApplication {
private static EntityManagerFactory emf;
private static EntityManager em;
public static void main(String[] args) {
:
emf = Persistence.createEntityManagerFactory("TestDB"); // application scoped Entity Manager
em = emf.createEntityManager();
:
try {
long count = em.createNamedQuery("PassportEntity.countUniqueAllClients", Long.class)
.setParameter("number", ppNumber)
.setParameter("country", ppCountry.name())
.getSingleResult();
} finally {
em.close();
emf.close();
}
}
}
... with the persistence.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
>
<persistence-unit name="TestDB" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<class>module.PassportEntity</class>
:
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/mydata" />
<property name="javax.persistence.jdbc.user" value="***" />
<property name="javax.persistence.jdbc.password" value="***" />
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
</properties>
</persistence-unit>
</persistence>
As an aside: I prefer to create public static String constants for all my query and placeholder names (concatenated in when defining the query), instead of the string literals as used in the example code, so that the compiler/IDE can help me get them right. It is just too easy making a typo in a string literal, which may lead to very frustrating and hard-to-find bugs. Another place where bugs creep in easily is when you break your predefined statement into a number of concatenated strings that fit in the line length of your code, but fail to have space inside the quotes to separate the text, so always double-check.
Your IDE (I use Eclipse) should be able to generate most of the Entity class files and the persistence.xml file for you, given a database that already has the tables created. Not much sweat there (but it helps to know what it should be, to be able to check or modify afterwards or apply some tweaks).
You can still use JPA with prepared statements or even native queries, where you simply replace indexed placeholders (starting from :1, if I remember correctly). As mentioned elsewhere, string concatenation with actual values (which may come from your user interface) are an ideal door for SQL injection attacks - don't do it.
