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