Java에서 SQL 문자열을 작성하는 가장 깨끗한 방법

Java에서 SQL 문자열을 작성하는 가장 깨끗한 방법

---

데이터베이스 조작 (업데이트, 삭제, 삽입, 선택 등)을 수행하기 위해 SQL 문자열을 만들고 싶습니다-기껏해야 읽을 수없는 수백만 개의 "+"와 따옴표를 사용하는 끔찍한 문자열 연결 방법 대신-거기 더 나은 방법이어야합니다.

MessageFormat 사용을 생각했지만 합리적인 작업을 수행 할 것이라고 생각하지만 사용자 메시지에 사용되어야하지만 Java SQL 라이브러리의 SQL 유형 작업에 더 적합한 것이 있어야한다고 생각합니다.

Groovy가 좋을까요?

---

먼저 준비된 명령문에서 쿼리 매개 변수 사용을 고려하십시오.

PreparedStatement stm = c.prepareStatement("UPDATE user_table SET name=? WHERE id=?");
stm.setString(1, "the name");
stm.setInt(2, 345);
stm.executeUpdate();

수행 할 수있는 다른 작업은 모든 쿼리를 속성 파일에 보관하는 것입니다. 예를 들어 query.properties 파일에서 위의 쿼리를 배치 할 수 있습니다.

update_query=UPDATE user_table SET name=? WHERE id=?

그런 다음 간단한 유틸리티 클래스의 도움으로 :

public class Queries {

    private static final String propFileName = "queries.properties";
    private static Properties props;

    public static Properties getQueries() throws SQLException {
        InputStream is = 
            Queries.class.getResourceAsStream("/" + propFileName);
        if (is == null){
            throw new SQLException("Unable to load property file: " + propFileName);
        }
        //singleton
        if(props == null){
            props = new Properties();
            try {
                props.load(is);
            } catch (IOException e) {
                throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
            }           
        }
        return props;
    }

    public static String getQuery(String query) throws SQLException{
        return getQueries().getProperty(query);
    }

}

다음과 같이 쿼리를 사용할 수 있습니다.

PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));

이것은 다소 간단한 해결책이지만 잘 작동합니다.

---

임의의 SQL의 경우 jOOQ를 사용 하십시오 . jOOQ 현재 지원 SELECT, INSERT, UPDATE, DELETE, TRUNCATE,와 MERGE. 다음과 같이 SQL을 생성 할 수 있습니다.

String sql1 = DSL.using(SQLDialect.MYSQL)  
                 .select(A, B, C)
                 .from(MY_TABLE)
                 .where(A.equal(5))
                 .and(B.greaterThan(8))
                 .getSQL();

String sql2 = DSL.using(SQLDialect.MYSQL)  
                 .insertInto(MY_TABLE)
                 .values(A, 1)
                 .values(B, 2)
                 .getSQL();

String sql3 = DSL.using(SQLDialect.MYSQL)  
                 .update(MY_TABLE)
                 .set(A, 1)
                 .set(B, 2)
                 .where(C.greaterThan(5))
                 .getSQL();

SQL 문자열을 얻는 대신 jOOQ를 사용하여 실행할 수도 있습니다. 보다

http://www.jooq.org

(면책 조항 : 나는 jOOQ 뒤에있는 회사에서 일합니다)

---

고려해야 할 한 가지 기술은 SQLJ 입니다. 즉, Java에 SQL 문을 직접 포함하는 방법입니다. 간단한 예로 TestQueries.sqlj라는 파일에 다음이있을 수 있습니다.

public class TestQueries
{
    public String getUsername(int id)
    {
        String username;
        #sql
        {
            select username into :username
            from users
            where pkey = :id
        };
        return username;
    }
}

.sqlj 파일을 가져와 순수 Java로 변환하는 추가 사전 컴파일 단계가 있습니다. 즉, 다음으로 구분 된 특수 블록을 찾습니다.

#sql
{
    ...
}

JDBC 호출로 변환합니다. SQLJ를 사용하면 몇 가지 주요 이점이 있습니다.

• completely abstracts away the JDBC layer - programmers only need to think about Java and SQL
• the translator can be made to check your queries for syntax etc. against the database at compile time
• ability to directly bind Java variables in queries using the ":" prefix

There are implementations of the translator around for most of the major database vendors, so you should be able to find everything you need easily.

---

I am wondering if you are after something like Squiggle. Also something very useful is jDBI. It won't help you with the queries though.

---

I would have a look at Spring JDBC. I use it whenever I need to execute SQLs programatically. Example:

int countOfActorsNamedJoe
    = jdbcTemplate.queryForInt("select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});

It's really great for any kind of sql execution, especially querying; it will help you map resultsets to objects, without adding the complexity of a complete ORM.

---

I tend to use Spring's Named JDBC Parameters so I can write a standard string like "select * from blah where colX=':someValue'"; I think that's pretty readable.

An alternative would be to supply the string in a separate .sql file and read the contents in using a utility method.

Oh, also worth having a look at Squill: https://squill.dev.java.net/docs/tutorial.html

---

Why do you want to generate all the sql by hand? Have you looked at an ORM like Hibernate Depending on your project it will probably do at least 95% of what you need, do it in a cleaner way then raw SQL, and if you need to get the last bit of performance you can create the SQL queries that need to be hand tuned.

---

I second the recommendations for using an ORM like Hibernate. However, there are certainly situations where that doesn't work, so I'll take this opportunity to tout some stuff that i've helped to write: SqlBuilder is a java library for dynamically building sql statements using the "builder" style. it's fairly powerful and fairly flexible.

---

I have been working on a Java servlet application that needs to construct very dynamic SQL statements for adhoc reporting purposes. The basic function of the app is to feed a bunch of named HTTP request parameters into a pre-coded query, and generate a nicely formatted table of output. I used Spring MVC and the dependency injection framework to store all of my SQL queries in XML files and load them into the reporting application, along with the table formatting information. Eventually, the reporting requirements became more complicated than the capabilities of the existing parameter mapping frameworks and I had to write my own. It was an interesting exercise in development and produced a framework for parameter mapping much more robust than anything else I could find.

The new parameter mappings looked as such:

select app.name as "App", 
       ${optional(" app.owner as "Owner", "):showOwner}
       sv.name as "Server", sum(act.trans_ct) as "Trans"
  from activity_records act, servers sv, applications app
 where act.server_id = sv.id
   and act.app_id = app.id
   and sv.id = ${integer(0,50):serverId}
   and app.id in ${integerList(50):appId}
 group by app.name, ${optional(" app.owner, "):showOwner} sv.name
 order by app.name, sv.name

The beauty of the resulting framework was that it could process HTTP request parameters directly into the query with proper type checking and limit checking. No extra mappings required for input validation. In the example query above, the parameter named serverId would be checked to make sure it could cast to an integer and was in the range of 0-50. The parameter appId would be processed as an array of integers, with a length limit of 50. If the field showOwner is present and set to "true", the bits of SQL in the quotes will be added to the generated query for the optional field mappings. field Several more parameter type mappings are available including optional segments of SQL with further parameter mappings. It allows for as complex of a query mapping as the developer can come up with. It even has controls in the report configuration to determine whether a given query will have the final mappings via a PreparedStatement or simply ran as a pre-built query.

For the sample Http request values:

showOwner: true
serverId: 20
appId: 1,2,3,5,7,11,13

It would produce the following SQL:

select app.name as "App", 
       app.owner as "Owner", 
       sv.name as "Server", sum(act.trans_ct) as "Trans"
  from activity_records act, servers sv, applications app
 where act.server_id = sv.id
   and act.app_id = app.id
   and sv.id = 20
   and app.id in (1,2,3,5,7,11,13)
 group by app.name,  app.owner,  sv.name
 order by app.name, sv.name

I really think that Spring or Hibernate or one of those frameworks should offer a more robust mapping mechanism that verifies types, allows for complex data types like arrays and other such features. I wrote my engine for only my purposes, it isn't quite read for general release. It only works with Oracle queries at the moment and all of the code belongs to a big corporation. Someday I may take my ideas and build a new open source framework, but I'm hoping one of the existing big players will take up the challenge.

---

You can also have a look at MyBatis (www.mybatis.org) . It helps you write SQL statements outside your java code and maps the sql results into your java objects among other things.

---

Google provides a library called the Room Persitence Library which provides a very clean way of writing sql. Bellow is short code snippet from the official website:

@Dao
public interface UserDao {
    @Query("SELECT * FROM user")
    List<User> getAll();

    @Query("SELECT * FROM user WHERE uid IN (:userIds)")
    List<User> loadAllByIds(int[] userIds);

    @Query("SELECT * FROM user WHERE first_name LIKE :first AND "
           + "last_name LIKE :last LIMIT 1")
    User findByName(String first, String last);

    @Insert
    void insertAll(User... users);

    @Delete
    void delete(User user);
}

There are more examples and better documentation in the official docs for the library.

There is also one called MentaBean which is a Java ORM. It has nice features and seems to be pretty simple way of writing SQL.

---

Read an XML file.

You can read it from an XML file. Its easy to maintain and work with. There are standard STaX, DOM, SAX parsers available out there to make it few lines of code in java.

Do more with attributes

You can have some semantic information with attributes on the tag to help do more with the SQL. This can be the method name or query type or anything that helps you code less.

Maintaince

You can put the xml outside the jar and easily maintain it. Same benefits as a properties file.

Conversion

XML is extensible and easily convertible to other formats.

Use Case

Metamug uses xml to configure REST resource files with sql.

---

If you put the SQL strings in a properties file and then read that in you can keep the SQL strings in a plain text file.

That doesn't solve the SQL type issues, but at least it makes copying&pasting from TOAD or sqlplus much easier.

---

How do you get string concatenation, aside from long SQL strings in PreparedStatements (that you could easily provide in a text file and load as a resource anyway) that you break over several lines?

You aren't creating SQL strings directly are you? That's the biggest no-no in programming. Please use PreparedStatements, and supply the data as parameters. It reduces the chance of SQL Injection vastly.

참고URL : https://stackoverflow.com/questions/370818/cleanest-way-to-build-an-sql-string-in-java