Exemple #1
0
 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;
 }
Exemple #2
0
 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;
 }
Exemple #3
0
 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;
 }
Exemple #4
0
 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);
  }
Exemple #6
0
  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();
 }
Exemple #9
0
 @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;
 }
Exemple #10
0
 /**
  * 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);
 }
Exemple #11
0
 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);
   }
 }
Exemple #12
0
 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:
   }
 }
Exemple #13
0
 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);
  }
Exemple #15
0
 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]);
         }
       });
 }
Exemple #16
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());
 }
Exemple #17
0
 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);
  }
Exemple #19
0
 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);
           }
         }
       });
 }
Exemple #20
0
 /**
  * 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();
 }
Exemple #22
0
 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();
 }
Exemple #24
0
 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();
 }
Exemple #25
0
 public static Long count(Object x) {
   return Db.registerToken(ClassUtils.newObject(Long.class), new Function("COUNT", x));
 }
Exemple #26
0
 @SuppressWarnings("unchecked")
 public static <T extends Number> T sum(T x) {
   return (T) Db.registerToken(ClassUtils.newObject(x.getClass()), new Function("SUM", x));
 }
Exemple #27
0
 public static Integer length(Object x) {
   return Db.registerToken(ClassUtils.newObject(Integer.class), new Function("LENGTH", x));
 }
Exemple #28
0
 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();
 }
Exemple #29
0
 @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;
  }