001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.synth.thread;
007:
008: import java.math.BigDecimal;
009: import java.sql.Connection;
010: import java.sql.PreparedStatement;
011: import java.sql.ResultSet;
012: import java.sql.SQLException;
013: import java.util.Random;
014:
015: /**
016: * The operation part of {@link TestMulti}.
017: * Queries and updates two tables.
018: */
019: public class TestMultiOrder extends TestMultiThread {
020:
021: Connection conn;
022: PreparedStatement insertLine;
023: private static final String[] ITEMS = new String[] { "Apples",
024: "Oranges", "Bananas", "Coffee" };
025:
026: static int customerCount;
027: static int orderCount;
028: static int orderLineCount;
029:
030: TestMultiOrder(TestMulti base) throws SQLException {
031: super (base);
032: conn = base.getConnection();
033: }
034:
035: void begin() throws SQLException {
036: insertLine = conn
037: .prepareStatement("insert into orderLine(order_id, line_id, text, amount) values(?, ?, ?, ?)");
038: insertCustomer();
039: }
040:
041: void end() throws SQLException {
042: conn.close();
043: }
044:
045: void operation() throws SQLException {
046: if (random.nextInt(10) == 0) {
047: insertCustomer();
048: } else {
049: insertOrder();
050: }
051: }
052:
053: private void insertOrder() throws SQLException {
054: PreparedStatement prep = conn
055: .prepareStatement("insert into orders(customer_id , total) values(?, ?)");
056: prep.setInt(1, random.nextInt(getCustomerCount()));
057: BigDecimal total = new BigDecimal("0");
058: prep.setBigDecimal(2, total);
059: prep.executeUpdate();
060: ResultSet rs = prep.getGeneratedKeys();
061: rs.next();
062: int orderId = rs.getInt(1);
063: int lines = random.nextInt(20);
064: for (int i = 0; i < lines; i++) {
065: insertLine.setInt(1, orderId);
066: insertLine.setInt(2, i);
067: insertLine
068: .setString(3, ITEMS[random.nextInt(ITEMS.length)]);
069: BigDecimal amount = new BigDecimal(random.nextInt(100)
070: + "." + random.nextInt(10));
071: insertLine.setBigDecimal(4, amount);
072: total = total.add(amount);
073: insertLine.addBatch();
074: }
075: insertLine.executeBatch();
076: increaseOrderLines(lines);
077: prep = conn
078: .prepareStatement("update orders set total = ? where id = ?");
079: prep.setBigDecimal(1, total);
080: prep.setInt(2, orderId);
081: increaseOrders();
082: prep.execute();
083: }
084:
085: private void insertCustomer() throws SQLException {
086: PreparedStatement prep = conn
087: .prepareStatement("insert into customer(id, name) values(?, ?)");
088: int customerId = getNextCustomerId();
089: prep.setInt(1, customerId);
090: prep.setString(2, getString(customerId));
091: prep.execute();
092: }
093:
094: private String getString(int id) {
095: StringBuffer buff = new StringBuffer();
096: Random rnd = new Random(id);
097: int len = rnd.nextInt(40);
098: for (int i = 0; i < len; i++) {
099: String s = "bcdfghklmnprstwz";
100: char c = s.charAt(rnd.nextInt(s.length()));
101: buff.append(i == 0 ? Character.toUpperCase(c) : c);
102: s = "aeiou ";
103:
104: buff.append(s.charAt(rnd.nextInt(s.length())));
105: }
106: return buff.toString();
107: }
108:
109: synchronized int getNextCustomerId() {
110: return customerCount++;
111: }
112:
113: synchronized int increaseOrders() {
114: return orderCount++;
115: }
116:
117: synchronized int increaseOrderLines(int count) {
118: return orderLineCount += count;
119: }
120:
121: public int getCustomerCount() {
122: return customerCount;
123: }
124:
125: void first() throws SQLException {
126: Connection conn = base.getConnection();
127: conn.createStatement().execute("drop table customer if exists");
128: conn.createStatement().execute("drop table orders if exists");
129: conn.createStatement()
130: .execute("drop table orderLine if exists");
131: conn
132: .createStatement()
133: .execute(
134: "create table customer(id int primary key, name varchar, account decimal)");
135: conn
136: .createStatement()
137: .execute(
138: "create table orders(id int identity primary key, customer_id int, total decimal)");
139: conn
140: .createStatement()
141: .execute(
142: "create table orderLine(order_id int, line_id int, text varchar, amount decimal, primary key(order_id, line_id))");
143: conn.close();
144: }
145:
146: void finalTest() throws Exception {
147: conn = base.getConnection();
148: ResultSet rs = conn.createStatement().executeQuery(
149: "select count(*) from customer");
150: rs.next();
151: base.check(rs.getInt(1), customerCount);
152: // System.out.println("customers: " + rs.getInt(1));
153:
154: rs = conn.createStatement().executeQuery(
155: "select count(*) from orders");
156: rs.next();
157: base.check(rs.getInt(1), orderCount);
158: // System.out.println("orders: " + rs.getInt(1));
159:
160: rs = conn.createStatement().executeQuery(
161: "select count(*) from orderLine");
162: rs.next();
163: base.check(rs.getInt(1), orderLineCount);
164: // System.out.println("orderLines: " + rs.getInt(1));
165:
166: conn.close();
167: }
168: }
|