我在Java中工作，我有以下方法：
public ResultSet getRecordsWithinBoundingBox(int spillFarLeftValue, int spillFarRightValue, int spillMostDownwardValue, int spillMostUpwardValue) {
ResultSet resultSet = null;
try {
Statement statement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String sql = "SELECT * FROM OTH WHERE (jl<=" + spillMostUpwardValue + " AND (ih>=" + spillFarLeftValue + " AND ih<="
+ spillFarRightValue+ ") OR (il<=" + spillFarRightValue + " AND il>=" + spillFarLeftValue + ")) OR (jh>="
+ spillMostDownwardValue + " AND (ih>=" + spillFarLeftValue + " AND ih<=" + spillFarRightValue + ") OR (il<="
+ spillFarRightValue + " AND il>=" + spillFarLeftValue + ")) OR (il<=" + spillFarLeftValue + " AND ih>="
+ spillFarRightValue + " AND (jl<=" + spillMostUpwardValue + " AND jl>=" + spillMostDownwardValue + ") OR (jh>="
+ spillMostDownwardValue + " AND jh>=" + spillMostUpwardValue + ")) OR (jl<=" + spillMostDownwardValue + " AND jh>="
+ spillMostUpwardValue + " AND (il>=" + spillFarLeftValue + " AND il<=" + spillFarRightValue + ") OR (ih<="
+ spillFarRightValue + " AND ih>=" + spillFarLeftValue + ")) OR (il<=" + spillFarLeftValue + " AND ih>="
+ spillFarRightValue + " AND jl<=" + spillMostDownwardValue + " AND jh>=" + spillMostUpwardValue + ")";
resultSet = statement.executeQuery(sql);
statement.close( );
resultSet.close( );
} catch (SQLException ex) {
Logger.getLogger(DatabaseInteractor.class.getName()).log(Level.SEVERE, null, ex);
}
return resultSet;
}
正如您所看到的，我目前正在使用一个巨大的字符串从数据库中提取数据，有人告诉我这不是最好的解决方案。但遗憾的是，没有人告诉我应该做什么。但我觉得以我现在的方式组合SQL语句是有风险的，我想知道获得相同结果的其他方法。
一个好的替代方案是使用准备好的语句：示例
sql= "INSERT INTO imt_database.Comment(error_id,user,content) VALUES (?,?,?);";
try{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(URL,"root","toor");
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, Error_id);
ps.setString(2, User);
ps.setString(3, Content);
ps.executeUpdate();
}catch(Exception e)
学习Java Persistence API可能也是值得的。以下是定义命名查询（带有命名占位符）的代码示例：
@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) ...
}
用法示例1:（JEE，例如EJB业务规则，使用注入应用程序服务器管理的资源）
:
@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();
}
:
:
带有persistence.xml文件：
<?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>
用法示例2:（非EE/独立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();
}
}
}
带有persistence.xml文件：
<?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>
顺便说一句：我更喜欢为我的所有查询和占位符名称创建public static String常量（在定义查询时连接在一起），而不是像示例代码中使用的字符串文字，这样编译器/IDE可以帮助我正确处理它们。在字符串文字中拼写错误太容易了，这可能会导致非常令人沮丧和难以找到的错误。另一个容易出现错误的地方是，当您将预定义的语句分解为多个串联的字符串，这些字符串与代码的行长度相匹配，但引号内没有空格来分隔文本时，请始终仔细检查
您的IDE（我使用Eclipse）应该能够为您生成大部分Entity类文件和persistence.xml文件，前提是数据库已经创建了表。不需要太多的汗水（但知道它应该是什么，之后可以检查或修改，或者进行一些调整）。
您仍然可以将JPA与准备好的语句甚至本机查询一起使用，只需替换索引的占位符（如果我没记错的话，从：1开始）。如前所述，字符串与实际值（可能来自用户界面）的串联是SQL注入攻击的理想途径-不要这样做。