Java/JDK 6/JDBC 4 Annotations

JDBC Annotations

   <source lang="java">

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);

}

       </source>
   
  
 
  

The SQL script for creating the Employee Table

   <source lang="java">

CREATE TABLE Employee (

   Employee_ID int NOT NULL,
   NAME        varchar (50) NOT NULL,
   DESCRIPTION varchar (250) NOT NULL

) //////////////////////////////////////////////////////// public class Employee {

   public int employeeId;
   public String name;
   public String description;

   public String toString () {
       StringBuilder buf = new StringBuilder ();
       buf.append ("\n\tEmployee ID = ") .append (employeeId);
       buf.append ("\n\tName = ").append (name);
       buf.append ("\n\tDescription = ").append (description);
       return buf.toString ();
    }

} /////////////////////////////////////////////////////// import java.sql.BaseQuery; import java.sql.DataSet; import java.sql.Select; public interface EmployeeQueries extends BaseQuery {

   // Select all employees
   @Select (sql = "SELECT Employee_ID, NAME, DESCRIPTION FROM Employee", readOnly=false, connected=false)
   DataSet<Employee> getAllEmployees();
   // Select employee by name
   @Select ("SELECT Employee_ID, NAME, DESCRIPTION FROM Employee where NAME = ?")
   DataSet<Employee> getEmployeeByName(String name);

}

import java.sql.Connection; import java.sql.DataSet; import java.sql.DriverManager; import java.sql.SQLException; public class QueryDemo {

   public static void main (String [] args) {
       Connection connection = null;
       try {
           String url = "jdbc:inetdae7:localhost:1433?database=Adaptor";
           String login = "sa";
           String password "admin";
           connection = DriverManager.getConnection(url, login,password);
           EmployeeQueries qo = connection.createQueryObject(EmployeeQueries.class); 
           // Get all employees
           DataSet<Employee> rows = qo.getAllEmployees ();
           for (Employee employee : rows) {
               System.out.println (employee);
           }
           // Create new Employee object
           if (! rows.isReadOnly () ) {
               System.out.printIn ("\nCreate new employee");
               Employee r = new Employee ();
               r.Employee_ID = 12345;
               r.name = "Supervisor";
               r.description = "Do monitoring job";
               boolean insertResult = rows. insert (r);
               rows.sync (connection);
               System.out.println ("\tInserted: " + insertResult);
         }
         // Retrieve Employee by name
         System.out.println ("\nGet employee by name:");
         DataSet<Employee> rows2 = qo.getEmployeeByName ("Supervisor");
         Employee employee = rows2.get (0);
         System.out.println (Employee);
         if (Employee != null) {
             // Modify Role
             System.out.println ("\nModify current Employee:");
             employee.description = "Do supervising job";
             boolean modifyResult = rows2.modify (employee);
             rows2.sync (connection);
             System.out.println ("\tModified: " + modifyResult);
         }
     } catch (SQLException e) {
         for (Throwable t : e) {
              t.printStackTrace ();
         }
      } finally {
           // Close connection
           try {
               connection.close ();
           } catch (SQLException e) {
               e.printStackTrace ();
           } 
        }
   }

}

       </source>
   
  
 
  

Update annotation

   <source lang="java">

                                
                                       

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);

}

       </source>
   
  
 
  

Use JDBC Chained Exception

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class ChainedExceptionDemo {

 static Connection conn;
 public static void main(String[] args) {
   String driver = "com.mysql.jdbc.Driver";
   String connectionURL = "jdbc:mysql://127.0.0.1:3306/sample";
   try {
     Class.forName(driver);
   } catch (java.lang.ClassNotFoundException e) {
     e.printStackTrace();
   }
   try {
     conn = DriverManager.getConnection(connectionURL);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM NONEXISTINGTABLE");
     rs.next();
     rs.close();
   } catch (SQLException sx) {
     for (Throwable e : sx) {
       System.err.println("Error encountered: " + e);
     }
   }
 }

}

       </source>