3 Replies Latest reply: Sep 20, 2012 1:04 AM by Kayaman RSS

    Custom Annotations for CRUD

    800839
      Hi,

      I want to perform the CRUD operations using java using custom annotation. I am using plain JDBC connection without any ORM.
      What I am looking for is rather than writing the select, insert, update and delete queries in a properties file. I am planning
      to use custom annotations to handle the queries. Like:-

      Rather than using the traditional way:-

      @Override
      public List<Employee> findByAgeOrLastName(int fromAge, int toAge, String lastName){
      return select("where age between ? and ? or last_name=?", fromAge, toAge, lastName);

      }

      I want to use :-

      @Select(sql="select * from #{tablename} where #{age} between ${fromAge} and ${toAge} or #{lastName}=${lastName}")
      public List<Employee> findByAgeOrLastName(int fromAge, int toAge, String lastName);


      For Delete:-

      @Delete(sql="delete from #{tablename} where #{age} between ${fromAge} and ${toAge} or #{lastName}=${lastN}")
      public abstract int delete(int fromAge, int toAge, String lastN);


      For Update:-

      @Update(sql="update #{tablename} {set #{lastName}=${lastN} where #{age} between ${fromAge} and ${toAge}")
      public abstract int updateLastName(int fromAge, int toAge, String lastN);

      Similarly for Insert as well.

      Please let me know how we can go about this? What are the advantages and disadvantages of this approach?
      Any links are really appreciated.

      Thanks.
        • 1. Re: Custom Annotations for CRUD
          Kayaman
          797836 wrote:
          Rather than using the traditional way:-

          @Override
          public List<Employee> findByAgeOrLastName(int fromAge, int toAge, String lastName){
          return select("where age between ? and ? or last_name=?", fromAge, toAge, lastName);

          }
          I'm sorry, but where did you get the idea that's "the traditional" way of doing a select query?

          Please let me know how we can go about this?
          Well, you need to read about annotations processing and decide on how you're planning to use the annotation data. Are you going to use AOP to intercept calls to those methods? How will you handle exceptions?
          What are the advantages and disadvantages of this approach?
          Advantages...I guess it could result in clearer code, depending on how you would write it normally. That's only an advantage if you write poor data access code normally, and if you do, you shouldn't be trying to work on that instead of coming up with your own persistence implementation.

          Disadvantages: Exception handling? Transaction handling? Duplicating the functionality of @NamedQuery?
          • 2. Re: Custom Annotations for CRUD
            939520
            Also your code may be exposed to sql injection attacks, depending on how you code it. Personally, I suggest you completely drop this approach and go with traditional approaches for JDBC, or bite the bullet and go with an ORM like Hibernate.
            • 3. Re: Custom Annotations for CRUD
              Kayaman
              936517 wrote:
              Also your code may be exposed to sql injection attacks, depending on how you code it.
              The way his select() method looks, there's a PreparedStatement under there.
              Personally, I suggest you completely drop this approach
              I agree wholeheartedly.