tabla - Java: almacenamiento de instrucciones SQL en un archivo externo

mostrar datos de una tabla mysql en java netbeans

Question

Estoy buscando una biblioteca / framework / técnica Java para almacenar declaraciones SQL en un archivo externo. El equipo de soporte (incluidos los DBA) debería poder modificar (ligeramente) la declaración para mantenerlos sincronizados en caso de que el esquema de la base de datos cambie o se ajuste.

Aquí están los requisitos:

• El archivo debe ser legible desde una aplicación Java pero también debe ser editable por el equipo de soporte sin la necesidad de editores sofisticados
• Idealmente, el archivo debe estar en formato de texto plano, pero XML también está bien
• Permitir que se almacenen / recuperen las sentencias DML y DDL
• Se pueden agregar nuevas declaraciones en una etapa posterior (la aplicación es lo suficientemente flexible como para recogerlas y ejecutarlas)
• Las declaraciones pueden ser agrupadas (y ejecutadas como un grupo por la aplicación)
• Las declaraciones deben permitir los parámetros

Notas:

• Una vez recuperado, las declaraciones se ejecutarán utilizando Spring''s JDBCTemplate
• No se usará el contenedor IOC de Hibernate o Spring

Hasta el momento, logré encontrar las siguientes bibliotecas de Java, que usan archivos externos para almacenar declaraciones de SQL. Sin embargo, estoy interesado principalmente en el almacenamiento en lugar de una biblioteca que oculta todas las "complejidades" de JDBC.

• Axamol SQL Library

  Contenido de archivo de muestra:

  <s:query name="get_emp">
    <s:param name="name" type="string"/>
    <s:sql databases="oracle">
      select    *
      from      scott.emp
                join scott.dept on (emp.deptno = dept.deptno)
      where     emp.ename = <s:bind param="name"/>
    </s:sql>
  </s:query>

• iBATIS

  Contenido de archivo de muestra:

  <sqlMap namespace="Contact"">
      <typeAlias alias="contact"
          type="com.sample.contact.Contact"/">
      <select id="getContact"
          parameterClass="int" resultClass="contact"">
              select CONTACTID as contactId,
                     FIRSTNAME as firstName,
                     LASTNAME as lastName from
                     ADMINISTRATOR.CONTACT where CONTACTID = #id#
      </select>
  </sqlMap>
  <insert id="insertContact" parameterClass="contact">
  INSERT INTO ADMINISTRATOR.CONTACT( CONTACTID,FIRSTNAME,LASTNAME)
          VALUES(#contactId#,#firstName#,#lastName#);
   </insert>
  <update id="updateContact" parameterClass="contact">
  update ADMINISTRATOR.CONTACT SET
  FIRSTNAME=#firstName# ,
  LASTNAME=#lastName#
  where contactid=#contactId#
  </update>
  <delete id="deleteContact" parameterClass="int">
  DELETE FROM ADMINISTRATOR.CONTACT WHERE CONTACTID=#contactId#
  </delete>

• WEB4J

  -- This is a comment 
   ADD_MESSAGE   {
   INSERT INTO MyMessage -- another comment
    (LoginName, Body, CreationDate)
    -- another comment
    VALUES (?,?,?)
   }
  
  -- Example of referring to a constant defined above.
  FETCH_RECENT_MESSAGES {
   SELECT 
   LoginName, Body, CreationDate 
   FROM MyMessage 
   ORDER BY Id DESC LIMIT ${num_messages_to_view}
  }

¿Alguien puede recomendar una solución probada y probada?

Answer 1

Es simple y confiable de usar clases de Spring. Tome sus archivos SQL y guárdelos en alguna ubicación en su classpath. Esto puede ser en un archivo JAR que solo contiene SQL si lo desea. Luego, use ClassPathResource de Spring para cargar el archivo en una secuencia y use Apache IOUtils para convertirlo a String. A continuación, puede ejecutar el SQL utilizando SimpleJdbcTemplate , o el código DB de su elección.

Sugiero que cree una clase de utilidad que tome una clase Java simple con campos String públicos que correspondan a los nombres de archivos SQL siguiendo una convención de su elección. A continuación, utilice la reflexión junto con la clase ClassPathResource para buscar los archivos SQL que se ajusten a su convención de nomenclatura y asígnelos a los campos String. Después de eso solo refiérase a los campos de clase cuando necesite el SQL. Es simple, funciona muy bien y logra el objetivo que desea. También usa clases y técnicas bien gastadas. Nada sofisticado. Lo hice hace un par de años. Funciona genial. Demasiado perezoso para ir a buscar el código. No tendrás tiempo para descubrirlo tú mismo.

Answer 2

La biblioteca de ElSql proporciona esta funcionalidad.

ElSql consiste en un pequeño archivo jar (seis clases públicas) que permite cargar un archivo SQL externo (elsql). El archivo usa un formato simple para proporcionar un comportamiento ligeramente más simple que simplemente cargar un archivo:

-- an example comment
@NAME(SelectBlogs)
  @PAGING(:paging_offset,:paging_fetch)
    SELECT @INCLUDE(CommonFields)
    FROM blogs
    WHERE id = :id
      @AND(:date)
        date > :date
      @AND(:active)
        active = :active
    ORDER BY title, author
@NAME(CommonFields)
  title, author, content

// Java code:
bundle.getSql("SelectBlogs", searchArgs);

El archivo se divide en bloques @NAME que se puede hacer referencia desde el código. Cada bloque está definido por una indentación significativa de espacio en blanco. @PAGING insertará el código necesario para paginación como FETCH / OFFSET. @AND solo se @AND si existe la variable especificada (lo que ayuda a generar búsquedas dinámicas). El DSL también maneja LIKE vs = para comodines en las búsquedas. El objetivo de las etiquetas DSL opcionales es proporcionar los conceptos básicos comunes que suelen aparecer al intentar crear SQL dinámico de forma neutral para las bases de datos.

Más información en el blog o guía del usuario .

Answer 3

Le recomiendo que use procedimientos almacenados. Este tipo de cosas es exactamente para lo que son.

Answer 4

Pegando aquí mi answer a Limpiar forma de externalizar de largo (+20 líneas sql) cuando se utiliza jdbc de primavera? :

Me enfrenté al mismo problema hace un tiempo, y se me ocurrió YAML. Admite valores de propiedad de cadena multilínea, por lo que puede escribir algo como esto en sus archivos de consulta:

selectSomething: >
  SELECT column1, column2 FROM SOMETHING

insertSomething: >
  INSERT INTO SOMETHING(column1, column2)
  VALUES(1, ''1'')

Aquí, seleccionar selectSomething e insertSomething son nombres de consulta. Por lo tanto, es muy conveniente y contiene muy pocos caracteres especiales. Las consultas están separadas por líneas en blanco, y cada texto de consulta debe estar sangrado. Tenga en cuenta que las consultas pueden contener absolutamente la sangría propia, por lo que el siguiente es perfectamente válido:

anotherSelect: <
  SELECT column1 FROM SOMETHING
  WHERE column2 IN (
    SELECT * FROM SOMETHING_ELSE
  )

A continuación, puede leer los contenidos del archivo en un hash-map con la ayuda de la biblioteca SnakeYAML, utilizando el siguiente código:

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.FileUtils;
import java.io.FileReader;

import org.yaml.snakeyaml.Yaml;
import java.io.File;
import java.io.FileNotFoundException;

public class SQLReader {
  private Map<String, Map> sqlQueries = new HashMap<String, Map>();

  private SQLReader() {
    try {
      final File sqlYmlDir = new File("dir_with_yml_files");
      Collection<File> ymlFiles = FileUtils.listFiles(sqlYmlDir, new String[]{"yml"}, false);
      for (File f : ymlFiles) {
        final String fileName = FilenameUtils.getBaseName(f.getName());
        Map ymlQueries = (Map)new Yaml().load(new FileReader(f));
        sqlQueries.put(fileName, ymlQueries);
      }
    }
    catch (FileNotFoundException ex) {
      System.out.println("File not found!!!");
    }
  }
}

En el ejemplo anterior, se crea un mapa de mapas, asignando cada archivo YAML a un mapa que contiene nombres / cadenas de consulta.

Answer 5

Puede usar Spring y tener sus instrucciones sql almacenadas en su archivo beans que se inyectan cuando obtiene la clase de su fábrica de beans. Esa clase también puede usar una instancia de SimpleJDBCTemplate que se puede configurar a través del archivo Bean para ayudarlo a simplificar su código.

Answer 6

Puede usar las instalaciones de localización para hacer esto. A continuación, utiliza el nombre de la base de datos como configuración regional para obtener la versión "oraclish" de "insert-foo-in-bar" en lugar de la versión en inglés o francés.

Las traducciones generalmente se almacenan en archivos de propiedad, y hay buenas herramientas para localizar aplicaciones al permitir la edición de estos archivos de propiedades.

Answer 7

Puede usar velocity para tener plantillas sql "scriptables" que puede usar para trabajar con los archivos de una manera flexible. Tiene instrucciones primitivas como condicionales y bucles para construir sus comandos sql.

Pero sugiero usar declaraciones preparadas y / o procedimientos almacenados. Construir su SQL de la manera que está planeando lo hará vulnerable a la inyección SQL, el servidor DB no podrá almacenar en caché las consultas sql (lo que generará un mal rendimiento).

Por cierto: también puede almacenar la definición de las declaraciones preparadas en archivos. Esta no es la mejor solución, pero está muy cerca y se beneficia de la protección y el rendimiento de la inyección SQL.

Cuando su esquema SQL no está construido para funcionar con declaraciones preparadas o procedimientos almacenados, es posible que desee replantear su esquema. Tal vez deba ser refactorizado.

Answer 8

Si debe hacer esto, debe mirar el proyecto MyBatis . No lo he usado, pero lo he escuchado recomendar varias veces.

La separación de SQL y Java no es mi enfoque favorito, ya que SQL es en realidad código, y está estrechamente relacionado con el código Java que lo llama. Mantener y depurar el código separado puede ser un desafío.

Absolutamente no utilice procs almacenados para esto. Solo deben usarse para mejorar el rendimiento al reducir el tráfico entre el DB y la aplicación.

Answer 9

Simplemente cree un archivo de propiedades Java simple con pares clave-valor como este:

users.select.all = select * from user

Declare un campo privado de tipo Propiedades en su clase DAO e inyéctelo utilizando la configuración de Spring que leerá los valores del archivo.

ACTUALIZACIÓN : si desea soportar instrucciones SQL en múltiples líneas, use esta notación:

users.select.all.0 = select *
users.select.all.1 = from   user

Answer 10

También puede usar la clase QueryLoader en Apache Commons DbUtils , que leerá el sql de un archivo de propiedades. Sin embargo, tendrá que usar DbUtils que sirve para el mismo propósito que JDBCTemplate.

Answer 11

Una solución simple que hemos implementado al enfrentar esto fue externalizar el SQL / DML en un archivo (mySql.properties), luego usar MessageFormat.format (String [] args) para inyectar propiedades dinámicas en el SQL.

Por ejemplo: mySql.properties:

select    *
    from      scott.emp
              join scott.dept on (emp.deptno = dept.deptno)
    where     emp.ename = {0}

Métodos de utilidad:

public static String format(String template, Object[] args) {
    String cleanedTemplate = replaceSingleQuotes(template);
    MessageFormat mf = new MessageFormat(cleanedTemplate);
    String output = mf.format(args);
    return output;
}
private static String replaceSingleQuotes(String template) {
    String cleaned = template.replace("''", "''''");
    return cleaned;
}

Entonces úsalo así:

String sqlString = youStringReaderImpl("/path/to/file");
String parsedSql = format(sqlString, new String[] {"bob"});

Answer 12

dynamic-query es un buen marco de código abierto para aquellos que quieren algo entre JDBC y ORM.

1 SQL simple. - Guarda sql simple en archivos externos. no hay etiquetas redundantes, admite comentarios.

/* It also supports comment.
This code is in an external file ''sample.sql'', Not inisde java code.*/
listUsers : select * from user_table
where user_id= $$;  /* $$ will automatically catch a parameter userId */

2 SQL expandible. -Se admite parámetros, incluidos otros archivos y sub-consulta.

listUsers:
select
    id, amount, created
    @checkEmail{ ,email } 
from user_table
where amount > $amt and balance < $amt
    @checkDate { and created = $$ }
    @checkEmail{ and email in (
        select email from vip_list ) } ;        
/* Above query can be four queries like below.
1. listUsers
2. listUsers.checkDate 
3. listUsers.checkEmail
4. listUsers.checkDate.checkEmail 
*/



-- It can include other files like below
& ../hr/additional hr.sql ; 
& ../fi/additional fi.sql ;

Código de ejemplo de Java usando arriba. estableciendo valores a db.

QueryUtil qu = qm.createQueryUtil("selectAll");
try {
    qu.setConnection(conn);

    // with native jdbc
    qu.setString("alpha");
    qu.setDouble(10.1);
    qu.executeQuery();

    // or with bean
    qu.executeQuery(new User("alpha", 10.1));

    // or with map
    Map<String, Object> map=new HashMap<String, Object>();
    map.put("userName", "alpha");
    map.put("amt", 10.1);
    qu.executeQuery(map);

    // or with array
    qu.executeQueryParameters("alpha", 10.1);

Código de ejemplo de Java usando arriba. obteniendo valores de db.

    while (qu.next()) // == qu.rs.next()
    {
        // native jdbc
        String usreName = qu.getString("user_name"); 
        double amt = qu.getDouble("amt");

        // or bean
        User user = new User();
        qu.updateBean(user);

        // or array
        Object[] values = qu.populateArray();
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    qu.closeJust();
}