How to keep the sql queries out of the java code

Question

I've written a small code which functions like follows:

Read an input file
Read the first line in it and check if it has value AAA at a certain position
if it satisfies the condition call the method insertAAAmethod and load the data to the oracle table
read the next line if it is record BBB call the insertBBBrmethod which has different insert query

The problem is I have 15 different records in the input file so I have 15 different methods like insertAAAmethod each with different insert query:

public static void insertAAARecord() throws Exception {

    String sqlQuery = "insert into my_table(ColumnA,ColumnB,ColumnC,ColumnD,ColumnE,ColumnF,ColumnG)"
            + "values (?,?,?,?,?,?,?)";

    try {

        pstmt = conn.prepareStatement(sqlQuery);

        pstmt.setString(1, "AAA");
        pstmt.setDate(2,
                StringtoDate("AAA", CurrentLine.substring(150, 158)));
        pstmt.setDate(3,
                StringtoDate("AAA", CurrentLine.substring(158, 166)));
        pstmt.setString(4, CurrentLine.substring(24, 34));
        pstmt.setString(5, CurrentLine.substring(37, 45));
        pstmt.setString(6, CurrentLine.substring(147, 150));
        pstmt.setDate(7, headerDate);

        pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace(System.out);

    } finally {

        pstmt.close();

    }

}

Is it possible to keep the sql query out of the java code? (Like in a properties file, for example)

Note: The insert query changes as per the record if it is record 'AAA' it should follow one insert query if the record is different insert query should change..

Let me know how to optimize my code.

can you add some examples of those SQL queries? you say they are different but not how much... — Alonso Dominguez, Feb 13 '13 at 16:44
I'm a big fan of JPA but in this case and with the little information we have I would not recommend it. — madth3, Feb 13 '13 at 17:03

score 2 · Accepted Answer · answered Feb 13 '13 at 17:00

2

Yes, you should create a properties file with all your queries and load them on startup using Properties object, then use the Properties to look them up. You can also you Spring to inject the queries into your configuration objects.

I have always built DB object that integrate your code to DB data and queries are kept there, it is much easier to debug and manage as everything you need is in one place.

Make your life simple, avoid ORMs and tune your SQL queries (or let DBAs do that that's what they do and they are good at it). However if you do not like SQL or don't care how efficient it is, then ORM like Hibernate may be what you need.

answered Feb 13 '13 at 17:00

Alex Chacha

423
2
7

Use a static block to load the properties file and assign each SQL query from it to a static member variable. – Sotirios Delimanolis Feb 13 '13 at 17:02
+1: this is the only answer that do not think ORM is the only solution to this kind of problems. – Luiggi Mendoza Feb 13 '13 at 17:25

score 1 · Answer 2 · answered Feb 13 '13 at 18:14

1

Maybe this will take more time than other approaches, but it would help you with your code optimization.

Every query/process have common attributes.

SQL Query
String pattern
Parameters

and each parameter has

Position
Datatype
etc

If you're using spring already, you'll be able to define these as beans in your config.xml. if not, you can use xml configuration anyway (instead of property files)

Then, you will have to create some class to parse these beans and create the custom queries.

Hope it helps.

answered Feb 13 '13 at 18:14

psabbate

767
1
5
22

Thanks for your answer..I am not using spring. Could you forward any link or tutorial on how to use xml configuration instead of property files.. Thanks so much – javanoob Feb 18 '13 at 15:28
There are several ways to do that, in this link are interesting options, let me know if you need any further help. [http://stackoverflow.com/questions/2333479/how-to-read-an-xml-file-with-java](http://stackoverflow.com/questions/2333479/how-to-read-an-xml-file-with-java) – psabbate Feb 18 '13 at 17:30

score 0 · Answer 3 · answered Feb 13 '13 at 16:44

0

Try to use any ORM framework like Hibernate/JPA, Ibatis?

answered Feb 13 '13 at 16:44

dillip

1,782
17
16

score 0 · Answer 4 · answered Feb 13 '13 at 16:58

You want to use JPA. I would recommend walking through a simple JPA tutorial such as the following: http://glassfish.java.net/javaee5/persistence/persistence-example.html

That tutorial will demonstrate the basics of how to create or modify objects and persist those changes in a database. Good luck.

How to keep the sql queries out of the java code

4 Answers4