JDBC4.0-Dataset implementation of SQL using Annotations

Home Tutorial Java Jdbc JDBC4.0-Dataset implementation of SQL using Annotations

JDBC4.0-Dataset implementation of SQL using Annotations
Posted on: June 8, 2010 at 12:00 AM

In this section,we will discuss about Dataset implementation of SQL using Annotations.

JDBC4.0-Dataset implementation of SQL using Annotations

An annotation is a declarative programming model where comments, associated with a code element, are used to inject code at runtime.The Annotations allows developers to associate a SQL query with a Java class without writing a lot of code. We can also associate the SQL queries with Java objects specifying query input and output parameters ,by using the Generics and metadata APIs.We don't need to write all the code we usually write to populate the query result into a Java object. There are two main annotations when specifying SQL queries in Java code: Select and Update :

Select Annotation

The annotation solution consists of two elements. The first is the declaration of a Query Interface, extending an interface BaseQuery in the java.sql. package. And the second element is a QueryObject used to execute the query.

import java.sql.BaseQuery;
import java.sql.DataSet;
import java.sql.Select;
public interface QueryAnnotationExample extends BaseQuery {
@Select(sql="SELECT ID, MODEL, MODEL_YEAR FROM CAR WHERE MODEL_YEAR = ?1")
public DataSet<Car> getCarsModelYear( String year );
}

Next, use the object factory to create and execute this statement. That is, by passing the query interface as a parameter, all the work was done for you, and the results are mapped to the collection of objects you specified in the interface:

public void testQueryAnnotation(  ) {
   QueryAnnotationExample qae = null;
   try {
    String url = "jdbc:derby://localhost:1527/rose;create=true";
    Connection con = DriverManager.getConnection(url , "APP", "password");
    qae = con.createQueryObject(QueryAnnotationExample.class);
  } catch (SQLException e) {
     e.printStackTrace();
  }
   Collection<Car> cars = qae.getCarsModelYear("1999");

Here is a simple loop to print out the results of the query:

 for ( Car c : cars) {
  System.out.println(" car id=" + c.getId() + 
  " model="+c.getModel() +" year="+ c.getYear() );
  }
 }

output

car id=1 model=Honda Accord year=null

Another "Select" Annotation Example

import java.sql.BaseQuery;

			import java.sql.DataSet;

			import java.sql.Select;

			public interface UserQueries extends BaseQuery {

			

			

			    // Select all users

			    @Select (sql ="SELECT userId, firstName, lastName FROM Users",

			             readOnly=false, connected=false, tableName="Users")

			    DataSet<User> getAllUsers ();

			

			

			    // Select user by name */

			    @Select (sql ="SELECT userId, firstName, lastName FROM Users"

			             + "WHERE userName=?", readOnly=false, connected=false,

			             tableName ="Users")

			    DataSet<User> getUserByName(String userName);

			}

Update Annotation

The Update annotation is used to decorate a Query interface method to update one or more records in a database table. An Update annotation must include a sql annotation type element. Here's an example of Update annotation:

interface LoanAppDetailsQuery extends BaseQuery {
        @Update(sql="update LoanDetails set LoanStatus = ?1
                where loanId = ?2")
        boolean updateLoanStatus(String loanStatus, int loanId);
}

"Delete" in Update Annotation

import java.sql.BaseQuery;

							import java.sql.DataSet;

							import java.sql.Select;

							public interface UserQueries extends BaseQuery {

							

							

							    // Delete user

							    @Update ("DELETE Users WHERE firstName={firstName}" +

							             "AND lastName={lastName}")

							    int deleteUser (String firstName, String lastName);

							}

Related Tags for JDBC4.0-Dataset implementation of SQL using Annotations :

«Previous Index Next»

Ask Questions?

If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.

Ask your questions, our development team will try to give answers to your questions.

Tutorial Topics