CODEWITHSUNDEEP
javaoracle-databasejdbc
javanoob
javanoob

Reputation: 6410

How to keep the sql queries out of the java code

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

  1. Read an input file
  2. Read the first line in it and check if it has value AAA at a certain position
  3. if it satisfies the condition call the method insertAAAmethod and load the data to the oracle table
  4. 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.

Upvotes: 0

Views: 758

Answers (4)

psabbate
psabbate

Reputation: 777

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.

Upvotes: 1

Alex Chacha
Alex Chacha

Reputation: 423

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.

Upvotes: 2

mightyrick
mightyrick

Reputation: 910

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.

Upvotes: 0

dillip
dillip

Reputation: 1842

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

Upvotes: 0

Related Questions