The SQL script for creating the Employee Table : Java DB Derby « Database « Java Tutorial

1. Java Tutorial
2. Database
3. Java DB Derby

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