Java/JDK 6/JDBC 4 Annotations

JDBC Annotations

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

}

The SQL script for creating the Employee Table

 

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

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

Use JDBC Chained Exception

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