Cleanest way to build an SQL string in Java

Question

I want to build an SQL string to do database manipulation  updates  deletes  inserts  selects  that sort of thing  - instead of the awful string concat method using millions of     s and quotes which is unreadable at best - there must be a better way     I did think of using MessageFormat - but its supposed to be used for user messages  although I think it would do a reasonable job - but I guess there should be something more aligned to SQL type operations in the java sql libraries   Would Groovy be any good

User · Answer

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

User · Answer

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 amp pasting from TOAD or sqlplus much easier

User · Answer

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

User · Answer

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

User · Answer

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 amp pasting from TOAD or sqlplus much easier

User · Answer

Google provides a library called the Room Persitence Library which provides a very clean way of writing SQL for Android Apps  basically an abstraction layer over underlying SQLite Database  Bellow is short code snippet from the official website    Dao public interface UserDao        Query  SELECT   FROM user       List lt User gt  getAll          Query  SELECT   FROM user WHERE uid IN   userIds        List lt User gt  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

User · Answer

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

User · Answer

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

User · Answer

First of all consider using query parameters in prepared statements   PreparedStatement stm   c prepareStatement  UPDATE user table SET name   WHERE id      stm setString 1   the name    stm setInt 2  345   stm executeUpdate      The other thing that can be done is to keep all queries in properties file  For example in a queries properties file can place the above query   update query UPDATE user table SET name   WHERE id     Then with the help of a simple utility class   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              you might use your queries as follows   PreparedStatement stm   c prepareStatement Queries getQuery  update query       This is a rather simple solution  but works well

User · Answer

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

User · Answer

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

User · Answer

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 amp pasting from TOAD or sqlplus much easier

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

One technology you should consider is SQLJ - a way to embed SQL statements directly in Java  As a simple example  you might have the following in a file called TestQueries sqlj   public class TestQueries       public String getUsername int id                String username           sql                       select username into  username             from users             where pkey    id                    return username            There is an additional precompile step which takes your  sqlj files and translates them into pure Java - in short  it looks for the special blocks delimited with   sql               and turns them into JDBC calls  There are several key benefits to using 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

User · Answer

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

User · Answer

Google provides a library called the Room Persitence Library which provides a very clean way of writing SQL for Android Apps  basically an abstraction layer over underlying SQLite Database  Bellow is short code snippet from the official website    Dao public interface UserDao        Query  SELECT   FROM user       List lt User gt  getAll          Query  SELECT   FROM user WHERE uid IN   userIds        List lt User gt  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

User · Answer

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

User · Answer

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 amp pasting from TOAD or sqlplus much easier

User · Answer

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

User · Answer

One technology you should consider is SQLJ - a way to embed SQL statements directly in Java  As a simple example  you might have the following in a file called TestQueries sqlj   public class TestQueries       public String getUsername int id                String username           sql                       select username into  username             from users             where pkey    id                    return username            There is an additional precompile step which takes your  sqlj files and translates them into pure Java - in short  it looks for the special blocks delimited with   sql               and turns them into JDBC calls  There are several key benefits to using 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

First of all consider using query parameters in prepared statements   PreparedStatement stm   c prepareStatement  UPDATE user table SET name   WHERE id      stm setString 1   the name    stm setInt 2  345   stm executeUpdate      The other thing that can be done is to keep all queries in properties file  For example in a queries properties file can place the above query   update query UPDATE user table SET name   WHERE id     Then with the help of a simple utility class   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              you might use your queries as follows   PreparedStatement stm   c prepareStatement Queries getQuery  update query       This is a rather simple solution  but works well

User · Answer

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

User · Answer

First of all consider using query parameters in prepared statements   PreparedStatement stm   c prepareStatement  UPDATE user table SET name   WHERE id      stm setString 1   the name    stm setInt 2  345   stm executeUpdate      The other thing that can be done is to keep all queries in properties file  For example in a queries properties file can place the above query   update query UPDATE user table SET name   WHERE id     Then with the help of a simple utility class   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              you might use your queries as follows   PreparedStatement stm   c prepareStatement Queries getQuery  update query       This is a rather simple solution  but works well

User · Answer

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

User · Answer

One technology you should consider is SQLJ - a way to embed SQL statements directly in Java  As a simple example  you might have the following in a file called TestQueries sqlj   public class TestQueries       public String getUsername int id                String username           sql                       select username into  username             from users             where pkey    id                    return username            There is an additional precompile step which takes your  sqlj files and translates them into pure Java - in short  it looks for the special blocks delimited with   sql               and turns them into JDBC calls  There are several key benefits to using 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

User · Answer

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

User · Answer

For arbitrary SQL  use jOOQ  jOOQ currently supports SELECT  INSERT  UPDATE  DELETE  TRUNCATE  and MERGE  You can create SQL like this   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      Instead of obtaining the SQL string  you could also just execute it  using jOOQ  See  http   www jooq org   Disclaimer  I work for the company behind jOOQ

User · Answer

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

User · Answer

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

User · Answer

First of all consider using query parameters in prepared statements   PreparedStatement stm   c prepareStatement  UPDATE user table SET name   WHERE id      stm setString 1   the name    stm setInt 2  345   stm executeUpdate      The other thing that can be done is to keep all queries in properties file  For example in a queries properties file can place the above query   update query UPDATE user table SET name   WHERE id     Then with the help of a simple utility class   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              you might use your queries as follows   PreparedStatement stm   c prepareStatement Queries getQuery  update query       This is a rather simple solution  but works well

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For arbitrary SQL  use jOOQ  jOOQ currently supports SELECT  INSERT  UPDATE  DELETE  TRUNCATE  and MERGE  You can create SQL like this   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      Instead of obtaining the SQL string  you could also just execute it  using jOOQ  See  http   www jooq org   Disclaimer  I work for the company behind jOOQ

User · Answer

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

User · Answer

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

User · Answer

One technology you should consider is SQLJ - a way to embed SQL statements directly in Java  As a simple example  you might have the following in a file called TestQueries sqlj   public class TestQueries       public String getUsername int id                String username           sql                       select username into  username             from users             where pkey    id                    return username            There is an additional precompile step which takes your  sqlj files and translates them into pure Java - in short  it looks for the special blocks delimited with   sql               and turns them into JDBC calls  There are several key benefits to using 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

User · Answer

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

User · Answer

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

[java] Cleanest way to build an SQL string in Java

Examples related to java

Examples related to sql

Examples related to oracle

Examples related to string