EJB3 and JPA Basics
SQL Code Example
SQL Code Example
CRUD statement
INSERT INTO person (person_id, fname, lname, gender, birth_date)
VALUES (null, 'William','Turner', 'M', '1972-05-27');
UPDATE person SET street = '1225 Tremont St.',
city = 'Boston', state = 'MA', country = 'USA', postal_code = '02138'
WHERE person_id = 1;
DELETE FROM person WHERE person_id = 2;
DROP TABLE favorite_food;
The group by and having Clauses[key point]
Count all the employees in each department and returns the names of those departments having more than two employees:
SELECT d.name, count(e.emp_id) num_employees
FROM department d INNER JOIN employee e
ON d.dept_id = e.dept_id
GROUP BY d.name
HAVING count(e.emp_id) > 2;
The order by Clause
Ascending Versus Descending Sort Order[Asc, Desc]
Filtering
Using Parentheses
Using the not Operator
Condition Types: =, <>, <, >
DELETE FROM account WHERE status = 'CLOSED' AND YEAR(close_date) = 2002;
The between operator(Number, String, Date)
Membership Conditions(in, not in)
Using wildcards(LIKE)
_ Exactly one character
% Any number of characters (including 0)
Using regular expressions
SELECT emp_id, fname, lname FROM employee WHERE lname REGEXP '^[FG]';
Null(IS [not] NULL)
Querying Multiple Tables[Join]
Self-Joins
List every employee’s name along with the name of his or her manager:
SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname
FROM employee e INNER JOIN employee e_mgr ON e.superior_emp_id = e_mgr.emp_id;
Using the Same Table Twice
SELECT a.account_id, e.emp_id, b_a.name open_branch, b_e.name emp_branch
FROM account a INNER JOIN branch b_a ON a.open_branch_id = b_a.branch_id
INNER JOIN employee e ON a.open_emp_id = e.emp_id
INNER JOIN branch b_e ON e.assigned_branch_id = b_e.branch_id
WHERE a.product_cd = 'CHK';
Using Subqueries As Tables
Non-Equi-Joins
List all possible chess matches:
SELECT e1.fname, e1.lname, 'VS' vs, e2.fname, e2.lname
FROM employee e1 INNER JOIN employee e2 ON e1.emp_id < e2.emp_id
WHERE e1.title = 'Teller' AND e2.title = 'Teller';
Grouping and Aggregates[key point]
SELECT open_emp_id, COUNT(*) how_many FROM account
GROUP BY open_emp_id HAVING COUNT(*) > 4;
Aggregate Functions
Max(), Min(),Avg(), Sum(), Count()
SELECT product_cd, MAX(avail_balance) max_balance,
MIN(avail_balance) min_balance, AVG(avail_balance) avg_balance,
SUM(avail_balance) tot_balance, COUNT(*) num_accts
FROM account GROUP BY product_cd;
Counting Distinct Values
SELECT COUNT(DISTINCT open_emp_id) FROM account;
SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance
FROM account GROUP BY product_cd, open_branch_id;
Grouping via Expressions
SELECT EXTRACT(YEAR FROM start_date) year, COUNT(*) how_many
FROM employee GROUP BY EXTRACT(YEAR FROM start_date);
Group Filter Conditions[having]
Subqueries
SELECT account_id, product_cd, cust_id, avail_balance
FROM account WHERE account_id = (SELECT MAX(account_id) FROM account);
Subquery Types
Noncorrelated Subqueries
Single-Row, Single-Column Subqueries
This type of subquery is known as a scalar subquery and can appear on either side of a condition using the usual operators (=, <>, <,>, <=, >=).
Multiple-Row, Single-Column Subqueries[in and not in, all, any]
List which employees supervise other employees:
SELECT emp_id, fname, lname, title FROM employee
WHERE emp_id IN (SELECT superior_emp_id FROM employee);
SELECT emp_id, fname, lname, title FROM employee
WHERE emp_id NOT IN (SELECT superior_emp_id
FROM employee WHERE superior_emp_id IS NOT NULL);
Find all employees whose employee IDs are not equal to any of the supervisor employee IDs:
SELECT emp_id, fname, lname, title FROM employee
WHERE emp_id <> ALL (SELECT superior_emp_id
FROM employee WHERE superior_emp_id IS NOT NULL);
Multicolumn Subqueries
Find all accounts whose balances don’t match the sum of the transaction amounts for that account.
Correlated Subqueries
List customers having exactly two accounts
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c
WHERE 2 = (SELECT COUNT(*) FROM account a WHERE a.cust_id = c.cust_id);
The exists Operator
Find all the accounts for which a transaction was posted on a particular day
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account a
WHERE EXISTS (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id AND t.txn_date = '2008-09-22');
UPDATE account a SET a.last_activity_date =
(SELECT MAX(t.txn_date) FROM transaction t WHERE t.account_id = a.account_id);
WHERE EXISTS (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id);
Remove data from the department table that has no child rows in the employee table:
DELETE FROM department WHERE NOT EXISTS
(SELECT 1 FROM employee WHERE employee.dept_id = department.dept_id);
Subqueries in Filter Conditions
Find the employee responsible for opening the most accounts:
SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_emp_id
HAVING COUNT(*) = (SELECT MAX(emp_cnt.how_many) FROM (SELECT COUNT(*) how_many
FROM account GROUP BY open_emp_id) emp_cnt);
Working with Sets
Set Operators[union, union all, intersect, except, ]
union all operator doesn’t remove duplicates.
SELECT 'IND' type_cd, cust_id, lname name FROM individual
UNION ALL
SELECT 'BUS' type_cd, cust_id, name FROM business;
Conditional Logic
CASE
WHEN C1 THEN E1
WHEN CN THEN EN
[ELSE ED]
END
CASE V0
WHEN V1 THEN E1
WHEN VN THEN EN
[ELSE ED]
END
SELECT
SUM(CASE WHEN EXTRACT(YEAR FROM open_date) = 2010 THEN 1 ELSE 0 END) year_2010,
SUM(CASE WHEN EXTRACT(YEAR FROM open_date) = 2011 THEN 1 ELSE 0 END) year_2011,
FROM account WHERE open_date > '1999-12-31' AND open_date < '2006-01-01';
Find accounts whose account balances don’t agree with the raw data in the transaction table.
SELECT CONCAT('ALERT! : Account #', a.account_id,
' Has Incorrect Balance!')
FROM account a
WHERE (a.avail_balance, a.pending_balance) <>
(SELECT
SUM(CASE
WHEN t.funds_avail_date > CURRENT_TIMESTAMP() THEN 0
WHEN t.txn_type_cd = 'DBT' THEN t.amount * −1
ELSE t.amount
END),
SUM(CASE WHEN t.txn_type_cd = 'DBT' THEN t.amount * −1 ELSE t.amount END)
FROM transaction t
WHERE t.account_id = a.account_id);
Show whether the customer has any checking accounts and whether the customer has any savings accounts:
SELECT c.cust_id, c.fed_id, c.cust_type_cd,
CASE
WHEN EXISTS (SELECT 1 FROM account a
WHERE a.cust_id = c.cust_id
AND a.product_cd = 'CHK') THEN 'Y'
ELSE 'N'
END has_checking,
CASE
WHEN EXISTS (SELECT 1 FROM account a
WHERE a.cust_id = c.cust_id
AND a.product_cd = 'SAV') THEN 'Y'
ELSE 'N'
END has_savings
FROM customer c;
Count the number of accounts for each customer, and then returns either 'None', '1', '2', or '3+':
SELECT c.cust_id, c.fed_id, c.cust_type_cd,
CASE (SELECT COUNT(*) FROM account a
WHERE a.cust_id = c.cust_id)
WHEN 0 THEN 'None'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
ELSE '3+'
END num_accounts
FROM customer c;
Division-by-Zero Errors
SELECT a.cust_id, a.product_cd, a.avail_balance /
CASE
WHEN prod_tots.tot_balance = 0 THEN 1
ELSE prod_tots.tot_balance
END percent_of_total
FROM account a INNER JOIN
(SELECT a.product_cd, SUM(a.avail_balance) tot_balance
FROM account a
GROUP BY a.product_cd) prod_tots
ON a.product_cd = prod_tots.product_cd;
Transactions
Lock Granularities:Table locks, Page locks, Row locks
SHOW TABLE STATUS LIKE 'transaction' \G
START TRANSACTION;
UPDATE product
SET date_retired = CURRENT_TIMESTAMP() WHERE product_cd = 'XYZ';
SAVEPOINT before_close_accounts;
UPDATE account
SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),
last_activity_date = CURRENT_TIMESTAMP()
WHERE product_cd = 'XYZ';
ROLLBACK TO SAVEPOINT before_close_accounts;
COMMIT;
Resource
GWT Basics
GWT Basics
1. You're write client side code in Java instead of javascript.
- We can use the reliable, strongly-typed language for development
- Reuse your skills of Java, no need skills in javascript and CSS.
2. We can use complex Java on the client
- We can use java class such as String, array, Math class, ArrayList, HashMap, custom classes, etc.
3. We can send complex Java types to and from the server.
4. GWT provides a number of widget.
-- Such as DatePicker, Tree, SuggestBox, RichTextArea, Grid, TabBar and etc.
5. Full IDE-based Java support for development/debugging.
-- Client side is java code, so we can use all Java IDE development/debugging features, Google also provides GWT designer, we can drag and drop control and widget to desgin the web UI.
6. Debugging
- We can debug client side code just like any other Java application, using our IDE's debugger. This approach is much better than JavaScript debugger.
Testabilty
- We can use java to write code to test client side code just like other java code.
GWT Disadvantages
1. Big learning curve, especailly for non-java developer.
2. Cumbersome deployment.
-- We have to translate client java code to javascript, then deploy to server.
Unusual approach
-- Fundamentally different strategy than all other Ajax environments makes evaluation and management buyoff harder.
Approaches to Communicate with the server
Making Remote Procedure Calls (GWT RPC)
If serve side can be implemented using java, we can use GWT RPC. Using it we can pass Java objects to and from a server and transparently make calls to Java servlets and let GWT take care of low-level details like object serialization.
Retrieving JSON Data via HTTP
RequestBuilder builder = new RequestBuilder(RequestBuilder.GET, url);
try {
Request request = builder.sendRequest(null, new RequestCallback() {
public void onError(Request request, Throwable exception) {displayError("Couldn't retrieve JSON");}
public void onResponseReceived(Request request, Response response) {
if (200 == response.getStatusCode()) {
updateTable(asArrayOfStockData(response.getText()));
} else {
displayError("Couldn't retrieve JSON (" + response.getStatusText()
+ ")");
}
}
});
} catch (RequestException e) {displayError("Couldn't retrieve JSON");}
Making Cross-Site Requests for JSONP
Writing Asynchronous Callbacks
All the calls you make from the HTML page to the server are asychronous, in client side, we need to implemente a function that would be called when request succeeds or fails(Using AsyncCallback).
@RemoteServiceRelativePath("greet")
public interface GreetingService extends RemoteService {
String greetServer(String name) throws IllegalArgumentException;
}
public interface GreetingServiceAsync {
void greetServer(String input, AsyncCallback
throws IllegalArgumentException;
}
private final GreetingServiceAsync greetingService = GWT.create(GreetingService.class);
greetingService.greetServer(textToServer, new AsyncCallback
public void onFailure(Throwable caught) {
// Show the RPC error message to the user
dialogBox.setText("Remote Procedure Call - Failure");
serverResponseLabel.addStyleName("serverResponseLabelError");
serverResponseLabel.setHTML(SERVER_ERROR);
dialogBox.center();
closeButton.setFocus(true);
}
public void onSuccess(String result) {
dialogBox.setText("Remote Procedure Call");
serverResponseLabel.removeStyleName("serverResponseLabelError");
serverResponseLabel.setHTML(result);
dialogBox.center();
closeButton.setFocus(true);
}
});
}
public class GreetingServiceImpl extends RemoteServiceServlet implements GreetingService {..} // at server side
Manage Events on the Client
GWT is event-based. Code executes in response to some event occurring.
This is just like Swing, we write handler to handle event.
HTML logout_link = new HTML("Sign Out");
logout_link.addClickListener(new ClickListener() {
public void onClick(Widget sender) {
if (Window.confirm("Do you really want to leave ?")) {
login_user = "";Window.Location.reload();
}
}
});
logout_link.addStyleName("hover-cursor");
Declarative Layout in XML with GWT UiBinder
API
GWT.log("")
Basic steps
Create application
webAppCreator -out MyWebApp -junit "C:\Documents and Settings\Administrator\.m2\repository\junit\junit\4.8.2\junit-4.8.2.jar" com.mycompany.mywebapp.MyWebApp
Run locally in development mode
ant devmode
Compile and run in production mode
ant build
ant war
Running unit tests
ant test.dev|test.prod|test
Code Example
Creating custom widgets
public class ImageButton extends Composite{
private HorizontalPanel hPanel;
private Image button_img;
private HTML button_text;
public ImageButton(String text, Image image) {
}
}
DateTimeSelection to set data and time.
Creating Composite
CreateAccount, ChangePasswd, Login
How to access image
public interface ImagePack extends ImageBundle, DisclosurePanelImages {
AbstractImagePrototype Logo();
AbstractImagePrototype delete();
}
public class ImageAccessor {
private static final ImagePack images = (ImagePack) GWT.create(ImagePack.class);
public static ImagePack getImages() {return images;}
}
titlePanel.add(ImageAccessor.getImages().Logo().createImage());
Resources:
http://code.google.com/webtoolkit
Google Web Toolkit: The new frontier of Rich Internet Applications