public static int addNullWarranty() {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Warranty w = new Warranty();
w.Note = "неопределено";
w.Warr = "нет";
db.insert(w);
w = db.from(w).orderByDesc(w.Kodwarr).selectFirst();
return w.Kodwarr;
}
public static int addWarranty(String warr) {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Warranty w = new Warranty();
if (warr.isEmpty()) {
w.Warr = "нет";
} else w.Warr = warr;
db.insert(w);
w = db.from(w).orderByDesc(w.Kodwarr).selectFirst();
return w.Kodwarr;
}
public static Map<Integer, String> getClientCarsList(int clientId) {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Car c = new Car();
Map<Integer, String> mp = new HashMap<Integer, String>();
List<Car> ls = db.from(c).where(c.Kodclient).is(clientId).select();
Iterator<Car> it = ls.iterator();
while (it.hasNext()) {
Car temp = (Car) it.next();
mp.put(temp.Kodavto, temp.Mark);
}
return mp;
}
public static void addClient(
String phone, String name, String middleName, String passport, String prava, String Surname) {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Clients c = new Clients();
c.Mphone = phone;
c.Name = name;
c.Ochestvo = middleName;
c.Passport = passport;
c.Prava = prava;
c.Surname = Surname;
db.insert(c);
}
private void testSimpleUpdateWithCombinedPrimaryKey() {
Order o = new Order();
Order ourOrder = db.from(o).where(o.orderDate).is(valueOf("2007-01-02")).selectFirst();
ourOrder.orderDate = valueOf("2007-01-03");
db.update(ourOrder);
Order ourUpdatedOrder = db.from(o).where(o.orderDate).is(valueOf("2007-01-03")).selectFirst();
assertTrue("updated order not found", ourUpdatedOrder != null);
// undo update
ourOrder.orderDate = valueOf("2007-01-02");
db.update(ourOrder);
}
public static Map<Integer, String> getClientsList() {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Clients c = new Clients();
Map<Integer, String> mp = new HashMap<Integer, String>();
List<Clients> ls = db.from(c).select();
Iterator<Clients> it = ls.iterator();
while (it.hasNext()) {
Clients temp = (Clients) it.next();
mp.put(temp.Kodclient, temp.Surname + " " + temp.Name + " " + temp.Ochestvo);
}
return mp;
}
@Override
public void test() throws Exception {
db = Db.open("jdbc:h2:mem:", "sa", "sa");
db.insertAll(Product.getList());
db.insertAll(Customer.getList());
db.insertAll(Order.getList());
testSimpleUpdate();
testSimpleUpdateWithCombinedPrimaryKey();
testSimpleMerge();
testSimpleMergeWithCombinedPrimaryKey();
testSetColumns();
db.close();
}
void delete(Db db, Object obj) {
if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) {
throw new IllegalStateException(
"No primary key columns defined "
+ "for table "
+ obj.getClass()
+ " - no update possible");
}
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("DELETE FROM ");
buff.append(db.getDialect().getTableName(schemaName, tableName));
buff.resetCount();
Object alias = ClassUtils.newObject(obj.getClass());
Query<Object> query = Query.from(db, alias);
boolean firstCondition = true;
for (FieldDefinition field : fields) {
if (field.isPrimaryKey) {
Object aliasValue = field.getValue(alias);
Object value = field.getValue(obj);
if (!firstCondition) {
query.addConditionToken(ConditionAndOr.AND);
}
firstCondition = false;
query.addConditionToken(new Condition<Object>(aliasValue, value, CompareType.EQUAL));
}
}
stat.setSQL(buff.toString());
query.appendWhere(stat);
StatementLogger.delete(stat.getSQL());
stat.executeUpdate();
}
@SuppressWarnings("unchecked")
static <T> Query<T> from(Db db, T alias) {
Query<T> query = new Query<T>(db);
TableDefinition<T> def = (TableDefinition<T>) db.define(alias.getClass());
query.from = new SelectTable<T>(db, query, alias, false);
def.initSelectObject(query.from, alias, query.aliasMap);
return query;
}
/**
* Join another table.
*
* @param alias an alias for the table to join
* @return the joined query
*/
@SuppressWarnings("unchecked")
public <U> QueryJoin innerJoin(U alias) {
TableDefinition<T> def = (TableDefinition<T>) db.define(alias.getClass());
SelectTable<T> join = new SelectTable(db, this, alias, false);
def.initSelectObject(join, alias, aliasMap);
joins.add(join);
return new QueryJoin(this, join);
}
public void fillingTable(Table table) {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Clients c = new Clients();
List<Clients> idd = db.from(c).select();
Iterator<Clients> it1 = idd.iterator();
while (it1.hasNext()) {
Clients temp = (Clients) it1.next();
TableItem item = new TableItem(table, SWT.NONE);
item.setText(0, temp.Surname);
item.setText(1, temp.Name);
item.setText(2, temp.Ochestvo);
item.setText(3, temp.Mphone);
item.setText(4, temp.Passport);
item.setText(5, temp.Prava);
}
}
public void setTableItem(String pasport, int column, String text) {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Clients c = new Clients();
switch (column) {
case 0:
db.from(c).set(c.Surname).to(text).where(c.Passport).is(pasport).update();
break;
case 1:
db.from(c).set(c.Name).to(text).where(c.Passport).is(pasport).update();
break;
case 2:
db.from(c).set(c.Ochestvo).to(text).where(c.Passport).is(pasport).update();
break;
case 3:
db.from(c).set(c.Mphone).to(text).where(c.Passport).is(pasport).update();
break;
case 4:
db.from(c).set(c.Passport).to(text).where(c.Passport).is(pasport).update();
break;
case 5:
db.from(c).set(c.Prava).to(text).where(c.Passport).is(pasport).update();
break;
default:
}
}
public static Boolean isNotNull(Object x) {
return Db.registerToken(
ClassUtils.newObject(Boolean.class),
new Function("", x) {
public <T> void appendSQL(SQLStatement stat, Query<T> query) {
query.appendSQL(stat, x[0]);
stat.appendSQL(" IS NOT NULL");
}
});
}
private void testSimpleMerge() {
Product p = new Product();
Product pChang = db.from(p).where(p.productName).is("Chang").selectFirst();
// update unitPrice from 19.0 to 19.5
pChang.unitPrice = 19.5;
// update unitsInStock from 17 to 16
pChang.unitsInStock = 16;
db.merge(pChang);
Product p2 = new Product();
Product pChang2 = db.from(p2).where(p2.productName).is("Chang").selectFirst();
assertEquals(19.5, pChang2.unitPrice);
assertEquals(16, pChang2.unitsInStock.intValue());
// undo update
pChang.unitPrice = 19.0;
pChang.unitsInStock = 17;
db.merge(pChang);
}
public static Boolean not(Boolean x) {
return Db.registerToken(
ClassUtils.newObject(Boolean.class),
new Function("", x) {
public <T> void appendSQL(SQLStatement stat, Query<T> query) {
stat.appendSQL("NOT ");
query.appendSQL(stat, x[0]);
}
});
}
@SuppressWarnings("unchecked")
void appendFromWhere(SQLStatement stat) {
stat.appendSQL(" FROM ");
from.appendSQL(stat);
for (SelectTable join : joins) {
join.appendSQLAsJoin(stat, this);
}
appendWhere(stat);
if (groupByExpressions != null) {
stat.appendSQL(" GROUP BY ");
int i = 0;
for (Object obj : groupByExpressions) {
if (i++ > 0) {
stat.appendSQL(", ");
}
appendSQL(stat, obj);
stat.appendSQL(" ");
}
}
if (!orderByList.isEmpty()) {
stat.appendSQL(" ORDER BY ");
int i = 0;
for (OrderExpression<T> o : orderByList) {
if (i++ > 0) {
stat.appendSQL(", ");
}
o.appendSQL(stat);
stat.appendSQL(" ");
}
}
if (limit > 0) {
db.getDialect().appendLimit(stat, limit);
}
if (offset > 0) {
db.getDialect().appendOffset(stat, offset);
}
StatementLogger.select(stat.getSQL());
}
public static Boolean like(String x, String pattern) {
Boolean o = ClassUtils.newObject(Boolean.class);
return Db.registerToken(
o,
new Function("LIKE", x, pattern) {
public <T> void appendSQL(SQLStatement stat, Query<T> query) {
stat.appendSQL("(");
query.appendSQL(stat, x[0]);
stat.appendSQL(" LIKE ");
query.appendSQL(stat, x[1]);
stat.appendSQL(")");
}
});
}
private void testSetColumns() {
Product p = new Product();
Product original = db.from(p).where(p.productId).is(1).selectFirst();
// update string and double columns
db.from(p)
.set(p.productName)
.to("updated")
.increment(p.unitPrice)
.by(3.14)
.increment(p.unitsInStock)
.by(2)
.where(p.productId)
.is(1)
.update();
// confirm the data was properly updated
Product revised = db.from(p).where(p.productId).is(1).selectFirst();
assertEquals("updated", revised.productName);
assertEquals(original.unitPrice + 3.14, revised.unitPrice);
assertEquals(original.unitsInStock + 2, revised.unitsInStock.intValue());
// restore the data
db.from(p)
.set(p.productName)
.to(original.productName)
.set(p.unitPrice)
.to(original.unitPrice)
.increment(p.unitsInStock)
.by(-2)
.where(p.productId)
.is(1)
.update();
// confirm the data was properly restored
Product restored = db.from(p).where(p.productId).is(1).selectFirst();
assertEquals(original.productName, restored.productName);
assertEquals(original.unitPrice, restored.unitPrice);
assertEquals(original.unitsInStock, restored.unitsInStock);
double unitPriceOld = db.from(p).where(p.productId).is(1).selectFirst().unitPrice;
// double the unit price
db.from(p).increment(p.unitPrice).by(p.unitPrice).where(p.productId).is(1).update();
double unitPriceNew = db.from(p).where(p.productId).is(1).selectFirst().unitPrice;
assertEquals(unitPriceOld * 2, unitPriceNew);
}
public static Boolean or(Boolean... x) {
return Db.registerToken(
ClassUtils.newObject(Boolean.class),
new Function("", (Object[]) x) {
public <T> void appendSQL(SQLStatement stat, Query<T> query) {
int i = 0;
for (Object o : x) {
if (i++ > 0) {
stat.appendSQL(" OR ");
}
query.appendSQL(stat, o);
}
}
});
}
/**
* INTERNAL
*
* @param stat the statement
* @param x the alias object
*/
public void appendSQL(SQLStatement stat, Object x) {
if (x == Function.count()) {
stat.appendSQL("COUNT(*)");
return;
}
Token token = Db.getToken(x);
if (token != null) {
token.appendSQL(stat, this);
return;
}
SelectColumn<T> col = aliasMap.get(x);
if (col != null) {
col.appendSQL(stat);
return;
}
stat.appendSQL("?");
stat.addParameter(x);
}
void merge(Db db, Object obj) {
if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) {
throw new IllegalStateException(
"No primary key columns defined "
+ "for table "
+ obj.getClass()
+ " - no update possible");
}
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("MERGE INTO ");
buff.append(db.getDialect().getTableName(schemaName, tableName)).append(" (");
buff.resetCount();
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
}
buff.append(") KEY(");
buff.resetCount();
for (FieldDefinition field : fields) {
if (field.isPrimaryKey) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
}
}
buff.append(") ");
buff.resetCount();
buff.append("VALUES (");
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append('?');
Object value = getValue(obj, field);
stat.addParameter(value);
}
buff.append(')');
stat.setSQL(buff.toString());
StatementLogger.merge(stat.getSQL());
stat.executeUpdate();
}
private <X> List<X> select(Class<X> clazz, X x, boolean distinct) {
List<X> result = New.arrayList();
TableDefinition<X> def = db.define(clazz);
SQLStatement stat = getSelectStatement(distinct);
def.appendSelectList(stat, this, x);
appendFromWhere(stat);
ResultSet rs = stat.executeQuery();
Statement s = null;
try {
s = rs.getStatement();
while (rs.next()) {
X row = ClassUtils.newObject(clazz);
def.readRow(row, rs);
result.add(row);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeSilently(rs);
JdbcUtils.closeSilently(s);
}
return result;
}
long insert(Db db, Object obj, boolean returnKey) {
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff = new StatementBuilder("INSERT INTO ");
buff.append(db.getDialect().getTableName(schemaName, tableName)).append('(');
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append(field.columnName);
}
buff.append(") VALUES(");
buff.resetCount();
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append('?');
Object value = getValue(obj, field);
stat.addParameter(value);
}
buff.append(')');
stat.setSQL(buff.toString());
StatementLogger.insert(stat.getSQL());
if (returnKey) {
return stat.executeInsert();
}
return stat.executeUpdate();
}
public void deleteTableItem(String pasport) {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Clients cc = new Clients();
db.from(cc).where(cc.Passport).is(pasport).delete();
}
public static Long count(Object x) {
return Db.registerToken(ClassUtils.newObject(Long.class), new Function("COUNT", x));
}
@SuppressWarnings("unchecked")
public static <T extends Number> T sum(T x) {
return (T) Db.registerToken(ClassUtils.newObject(x.getClass()), new Function("SUM", x));
}
public static Integer length(Object x) {
return Db.registerToken(ClassUtils.newObject(Integer.class), new Function("LENGTH", x));
}
public static void delWarranty(int id) {
DBWrapper dbw = new DBWrapper();
Db db = dbw.openConnection();
Warranty w = new Warranty();
db.from(w).where(w.Kodwarr).is(id).delete();
}
@SuppressWarnings("unchecked")
public static <X> X max(X x) {
Class<X> clazz = (Class<X>) x.getClass();
X o = ClassUtils.newObject(clazz);
return Db.registerToken(o, new Function("MAX", x));
}
TableDefinition<T> createTableIfRequired(Db db) {
if (!createTableIfRequired) {
// skip table and index creation
// but still check for upgrades
db.upgradeTable(this);
return this;
}
SQLDialect dialect = db.getDialect();
SQLStatement stat = new SQLStatement(db);
StatementBuilder buff;
if (memoryTable && dialect.supportsMemoryTables()) {
buff = new StatementBuilder("CREATE MEMORY TABLE IF NOT EXISTS ");
} else {
buff = new StatementBuilder("CREATE TABLE IF NOT EXISTS ");
}
buff.append(dialect.getTableName(schemaName, tableName)).append('(');
for (FieldDefinition field : fields) {
buff.appendExceptFirst(", ");
buff.append(field.columnName).append(' ').append(field.dataType);
if (field.maxLength > 0) {
buff.append('(').append(field.maxLength).append(')');
}
if (field.isAutoIncrement) {
buff.append(" AUTO_INCREMENT");
}
if (!field.allowNull) {
buff.append(" NOT NULL");
}
// default values
if (!field.isAutoIncrement && !field.isPrimaryKey) {
String dv = field.defaultValue;
if (!StringUtils.isNullOrEmpty(dv)) {
if (ModelUtils.isProperlyFormattedDefaultValue(dv)
&& ModelUtils.isValidDefaultValue(field.field.getType(), dv)) {
buff.append(" DEFAULT " + dv);
}
}
}
}
// primary key
if (primaryKeyColumnNames != null && primaryKeyColumnNames.size() > 0) {
buff.append(", PRIMARY KEY(");
buff.resetCount();
for (String n : primaryKeyColumnNames) {
buff.appendExceptFirst(", ");
buff.append(n);
}
buff.append(')');
}
buff.append(')');
stat.setSQL(buff.toString());
StatementLogger.create(stat.getSQL());
stat.executeUpdate();
// create indexes
for (IndexDefinition index : indexes) {
String sql = db.getDialect().getCreateIndex(schemaName, tableName, index);
stat.setSQL(sql);
StatementLogger.create(stat.getSQL());
stat.executeUpdate();
}
// tables are created using IF NOT EXISTS
// but we may still need to upgrade
db.upgradeTable(this);
return this;
}