public void gen() {
try {
Template conftempalte = VelocityUtil.getTemplate("/gencode/conf/paginesql.vm");
VelocityContext context = new VelocityContext();
context.put("conditions", gencodeService.getConditions());
context.put("sorts", gencodeService.getSortFields());
context.put("needsort", gencodeService.needsort());
context.put("tableName", gencodeService.getModuleMetaInfo().getTableName());
if (gencodeService.isPagineWithDBRownumberOver()) {
if (sql.getOptype() == Constant.listquery) {
context.put("pagineWithDBRownumberOver", false);
context.put("pagineOrderBy", false);
} else if (sql.getOptype() == Constant.pagineOrderBy) {
context.put("pagineWithDBRownumberOver", true);
context.put("pagineOrderBy", true);
} else if (sql.getOptype() == Constant.paginequery) {
context.put("pagineWithDBRownumberOver", true);
context.put("pagineOrderBy", false);
}
} else {
context.put("pagineWithDBRownumberOver", false);
}
sql.setSql(
gencodeService.writetostring(
context, conftempalte, gencodeService.getModuleMetaInfo().getEncodecharset()));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* Executes a constant value
*
* @param sql
* @param qr
* @param constant
* @param row
* @return result
* @throws PageException
*/
private Object executeConstant(SQL sql, Query qr, ZConstant constant, int row)
throws PageException {
switch (constant.getType()) {
case ZConstant.COLUMNNAME:
{
if (constant.getValue().equals(SQLPrettyfier.PLACEHOLDER_QUESTION)) {
int pos = sql.getPosition();
sql.setPosition(pos + 1);
if (sql.getItems().length <= pos)
throw new DatabaseException("invalid syntax for SQL Statment", null, sql, null);
return sql.getItems()[pos].getValueForCF();
}
return qr.getAt(List.last(constant.getValue(), ".", true), row);
}
case ZConstant.NULL:
return null;
case ZConstant.NUMBER:
return Caster.toDouble(constant.getValue());
case ZConstant.STRING:
return constant.getValue();
case ZConstant.UNKNOWN:
default:
throw new DatabaseException("invalid constant value", null, sql, null);
}
}
private void jButton3ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton3ActionPerformed
// TODO add your handling code here:
String s = "REJECT";
String b = jTextArea2.getText();
String a = jTextField3.getText();
q.execute("update bill set status = '" + s + "' where bill_id = '" + a + "'");
q.execute("update meo set status = '" + s + "' where bill_id = '" + a + "'");
q.execute("update bill set mgr_cmmnt = '" + b + "' where bill_id = '" + a + "'");
q.execute("update emp_bill set status_or_id = '" + s + "' where bill_id = '" + a + "'");
q.execute("update emp_bill set status_bill_id = '" + s + "' where bill_id = '" + a + "'");
} // GEN-LAST:event_jButton3ActionPerformed
public Query execute(PageContext pc, SQL sql, String prettySQL, int maxrows)
throws PageException {
if (StringUtil.isEmpty(prettySQL)) prettySQL = SQLPrettyfier.prettyfie(sql.getSQLString());
ZqlParser parser = new ZqlParser(new ByteArrayInputStream(prettySQL.getBytes()));
Vector statements;
try {
statements = parser.readStatements();
} catch (Throwable t) {
throw Caster.toPageException(t);
}
return execute(statements, pc, sql, maxrows);
}
public void GetDishID() {
String recipe = txt_Recipe.getText();
String commandText = "SELECT ID from dish WHERE Recipe = " + "'" + recipe + "'";
ResultSet rs = SQL.ExecuteResultSet(commandText);
try {
while ((rs != null) && (rs.next())) {
dishID = rs.getInt("ID");
}
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, e.toString());
}
}
public View_OR(UserType usr) throws SQLException {
initComponents();
vw = usr;
if (vw.t == 1) {
jLabel6.setVisible(false);
} else if (vw.t == 0) {
jLabel6.setVisible(true);
jButton2.setVisible(false);
jButton3.setVisible(false);
jTextArea2.setVisible(false);
jLabel5.setVisible(false);
jTextField2.setText(vw.id);
} else {
jLabel6.setVisible(true);
jLabel5.setVisible(false);
jButton2.setVisible(false);
jButton3.setVisible(false);
jTextArea2.setVisible(false);
}
q.pst = q.conn.prepareStatement("select * from meo");
q.rs = q.pst.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(q.rs));
}
public void start() {
conn = SQL.getConn();
for (int i = 0; i <= 108233; i += 1000) {
String selectSql =
"SELECT DISTINCT urllong"
+ " FROM final_s_t_l2"
+ " ORDER BY urllong"
+ " limit "
+ i
+ ",1000;";
System.out.println("############" + i + "###########");
selectData(selectSql);
}
finished();
}
public void AddToDatabase() {
String recipe = txt_Recipe.getText();
String rating = cb_Rating.getSelectedItem().toString();
String genre = txt_Cousine.getText();
String commandText =
"INSERT INTO dish (Recipe,Rating,Genre) VALUES ("
+ "'"
+ recipe
+ "',"
+ "'"
+ rating
+ "',"
+ "'"
+ genre
+ "')";
SQL.UpdateResultSet(commandText);
GetDishID();
}
@Override
protected boolean save(final SQL conn) {
try {
this.cursValutar.setCurs(new BigDecimal(this.textCurs.getValue().toString()));
if (getViewMode() == AbstractView.MODE_ADD) {
this.cursValutar.setData(EncodeLive.getSQLDateLogin());
}
this.cursValutar.setIdBanca(this.comboBanca.getSelectedObjectId());
this.cursValutar.setIdValuta(this.comboValuta.getSelectedObjectId());
this.cursValutar.setIdUser(EncodeLive.getIdUserCurent());
if (this.cursValutar.getId() == 0) {
this.cursValutar.insert(conn);
} else {
this.cursValutar.update(conn);
}
conn.commit();
} catch (Exception exc) {
AbstractView.processSaveErr(exc, conn);
return false;
}
return true;
}
public static void main(String[] args) throws IOException {
SQL sql = new SQL();
File db = new File("src\\AP2014\\sql\\DB.dump.txt");
sql.importFromFile(db);
if (args.length > 0) for (String c : args) System.out.println(sql.query(c));
MyScanner s = new MyScanner(System.in);
String c = "";
while (c != "exit") {
for (Table t : sql.getDb().tables) System.out.println(t);
System.out.print("PooyaDB> ");
c = s.readString("\r\n");
if (c.toLowerCase().startsWith("exit")) break;
System.out.println(sql.query(c));
}
sql.exportToFile(db);
}
private Query testExecute(PageContext pc, SQL sql, Query qr, ZQuery query, int maxrows)
throws PageException {
int recCount = qr.getRecordcount();
Vector vSelects = query.getSelect();
int selCount = vSelects.size();
Map selects = new HashTable();
boolean isSMS = false;
// headers
for (int i = 0; i < selCount; i++) {
ZSelectItem select = (ZSelectItem) vSelects.get(i);
if (select.isWildcard()
|| (isSMS = select.getColumn().equals(SQLPrettyfier.PLACEHOLDER_ASTERIX))) {
if (!isSMS && !select.getColumn().equals("*"))
throw new DatabaseException(
"can't execute this type of query at the moment", null, sql, null);
String[] keys = qr.keysAsString();
for (int y = 0; y < keys.length; y++) {
selects.put(keys[y], keys[y]);
}
isSMS = false;
} else {
// if(SQLPrettyfier.PLACEHOLDER_COUNT.equals(select.getAlias())) select.setAlias("count");
// if(SQLPrettyfier.PLACEHOLDER_COUNT.equals(select.getColumn())) select.setExpression(new
// ZConstant("count",ZConstant.COLUMNNAME));
String alias = select.getAlias();
String column = select.getColumn();
if (alias == null) alias = column;
alias = alias.toLowerCase();
selects.put(alias, select);
}
}
String[] headers = (String[]) selects.keySet().toArray(new String[selects.size()]);
// aHeaders.toArray(new String[aHeaders.size()]);
QueryImpl rtn = new QueryImpl(headers, 0, "query");
rtn.setSql(sql);
// loop records
Vector orders = query.getOrderBy();
ZExp where = query.getWhere();
// print.out(headers);
// int newRecCount=0;
boolean hasMaxrow = maxrows > -1 && (orders == null || orders.size() == 0);
for (int row = 1; row <= recCount; row++) {
sql.setPosition(0);
if (hasMaxrow && maxrows <= rtn.getRecordcount()) break;
boolean useRow = where == null || Caster.toBooleanValue(executeExp(pc, sql, qr, where, row));
if (useRow) {
rtn.addRow(1);
for (int cell = 0; cell < headers.length; cell++) {
Object value = selects.get(headers[cell]);
rtn.setAt(
headers[cell], rtn.getRecordcount(), getValue(pc, sql, qr, row, headers[cell], value)
// executeExp(qr, selects[cell].getExpression(),row)
);
}
}
}
// Group By
if (query.getGroupBy() != null)
throw new DatabaseException("group by are not supported at the moment", null, sql, null);
// Order By
if (orders != null && orders.size() > 0) {
int len = orders.size();
for (int i = len - 1; i >= 0; i--) {
ZOrderBy order = (ZOrderBy) orders.get(i);
ZConstant name = (ZConstant) order.getExpression();
rtn.sort(
name.getValue().toLowerCase(),
order.getAscOrder() ? Query.ORDER_ASC : Query.ORDER_DESC);
}
if (maxrows > -1) {
rtn.cutRowsTo(maxrows);
}
}
// Distinct
if (query.isDistinct()) {
String[] keys = rtn.getColumns();
QueryColumn[] columns = new QueryColumn[keys.length];
for (int i = 0; i < columns.length; i++) {
columns[i] = rtn.getColumn(keys[i]);
}
int i;
outer:
for (int row = rtn.getRecordcount(); row > 1; row--) {
for (i = 0; i < columns.length; i++) {
if (!Operator.equals(columns[i].get(row), columns[i].get(row - 1), true)) continue outer;
}
rtn.removeRow(row);
}
}
// UNION // TODO support it
ZExpression set = query.getSet();
if (set != null) {
ZExp op = set.getOperand(0);
if (op instanceof ZQuery)
throw new DatabaseException("union is not supported at the moment", null, sql, null);
// getInvokedTables((ZQuery)op, tablesNames);
}
return rtn;
}
/**
* Executes a Expression
*
* @param sql
* @param qr
* @param expression
* @param row
* @return result
* @throws PageException
*/
private Object executeExpression(
PageContext pc, SQL sql, Query qr, ZExpression expression, int row) throws PageException {
String op = StringUtil.toLowerCase(expression.getOperator());
int count = expression.nbOperands();
if (op.equals("and")) return executeAnd(pc, sql, qr, expression, row);
else if (op.equals("or")) return executeOr(pc, sql, qr, expression, row);
if (count == 0 && op.equals("?")) {
int pos = sql.getPosition();
if (sql.getItems().length <= pos)
throw new DatabaseException("invalid syntax for SQL Statment", null, sql, null);
sql.setPosition(pos + 1);
return sql.getItems()[pos].getValueForCF();
}
// 11111111111111111111111111111111111111111111111111111
else if (count == 1) {
Object value = executeExp(pc, sql, qr, expression.getOperand(0), row);
// Functions
switch (op.charAt(0)) {
case 'a':
if (op.equals("abs")) return new Double(MathUtil.abs(Caster.toDoubleValue(value)));
if (op.equals("acos")) return new Double(Math.acos(Caster.toDoubleValue(value)));
if (op.equals("asin")) return new Double(Math.asin(Caster.toDoubleValue(value)));
if (op.equals("atan")) return new Double(Math.atan(Caster.toDoubleValue(value)));
break;
case 'c':
if (op.equals("ceiling")) return new Double(Math.ceil(Caster.toDoubleValue(value)));
if (op.equals("cos")) return new Double(Math.cos(Caster.toDoubleValue(value)));
break;
case 'e':
if (op.equals("exp")) return new Double(Math.exp(Caster.toDoubleValue(value)));
break;
case 'f':
if (op.equals("floor")) return new Double(Math.floor(Caster.toDoubleValue(value)));
break;
case 'i':
if (op.equals("is not null")) return Boolean.valueOf(value != null);
if (op.equals("is null")) return Boolean.valueOf(value == null);
break;
case 'u':
if (op.equals("upper") || op.equals("ucase")) return Caster.toString(value).toUpperCase();
break;
case 'l':
if (op.equals("lower") || op.equals("lcase")) return Caster.toString(value).toLowerCase();
if (op.equals("ltrim")) return StringUtil.ltrim(Caster.toString(value), null);
if (op.equals("length")) return new Double(Caster.toString(value).length());
break;
case 'r':
if (op.equals("rtrim")) return StringUtil.rtrim(Caster.toString(value), null);
break;
case 's':
if (op.equals("sign")) return new Double(MathUtil.sgn(Caster.toDoubleValue(value)));
if (op.equals("sin")) return new Double(Math.sin(Caster.toDoubleValue(value)));
if (op.equals("soundex")) return StringUtil.soundex(Caster.toString(value));
if (op.equals("sin")) return new Double(Math.sqrt(Caster.toDoubleValue(value)));
break;
case 't':
if (op.equals("tan")) return new Double(Math.tan(Caster.toDoubleValue(value)));
if (op.equals("trim")) return Caster.toString(value).trim();
break;
}
}
// 22222222222222222222222222222222222222222222222222222
else if (count == 2) {
if (op.equals("=") || op.equals("in")) return executeEQ(pc, sql, qr, expression, row);
else if (op.equals("!=") || op.equals("<>")) return executeNEQ(pc, sql, qr, expression, row);
else if (op.equals("<")) return executeLT(pc, sql, qr, expression, row);
else if (op.equals("<=")) return executeLTE(pc, sql, qr, expression, row);
else if (op.equals(">")) return executeGT(pc, sql, qr, expression, row);
else if (op.equals(">=")) return executeGTE(pc, sql, qr, expression, row);
else if (op.equals("-")) return executeMinus(pc, sql, qr, expression, row);
else if (op.equals("+")) return executePlus(pc, sql, qr, expression, row);
else if (op.equals("/")) return executeDivide(pc, sql, qr, expression, row);
else if (op.equals("*")) return executeMultiply(pc, sql, qr, expression, row);
else if (op.equals("^")) return executeExponent(pc, sql, qr, expression, row);
Object left = executeExp(pc, sql, qr, expression.getOperand(0), row);
Object right = executeExp(pc, sql, qr, expression.getOperand(1), row);
// Functions
switch (op.charAt(0)) {
case 'a':
if (op.equals("atan2"))
return new Double(Math.atan2(Caster.toDoubleValue(left), Caster.toDoubleValue(right)));
break;
case 'b':
if (op.equals("bitand"))
return new Double(
Operator.bitand(Caster.toDoubleValue(left), Caster.toDoubleValue(right)));
if (op.equals("bitor"))
return new Double(
Operator.bitor(Caster.toDoubleValue(left), Caster.toDoubleValue(right)));
break;
case 'c':
if (op.equals("concat")) return Caster.toString(left).concat(Caster.toString(right));
break;
case 'l':
if (op.equals("like")) return executeLike(pc, sql, qr, expression, row);
break;
case 'm':
if (op.equals("mod"))
return new Double(
Operator.modulus(Caster.toDoubleValue(left), Caster.toDoubleValue(right)));
break;
}
throw new DatabaseException("unsopprted sql statement [" + op + "]", null, sql, null);
}
// 3333333333333333333333333333333333333333333333333333333333333333333
else if (count == 3) {
if (op.equals("between")) return executeBetween(pc, sql, qr, expression, row);
}
if (op.equals("in")) return executeIn(pc, sql, qr, expression, row);
/*
addCustomFunction("cot",1);
addCustomFunction("degrees",1);
addCustomFunction("log",1);
addCustomFunction("log10",1);
addCustomFunction("pi",0);
addCustomFunction("power",2);
addCustomFunction("radians",1);
addCustomFunction("rand",0);
addCustomFunction("round",2);
addCustomFunction("roundmagic",1);
addCustomFunction("truncate",2);
addCustomFunction("ascii",1);
addCustomFunction("bit_length",1);
addCustomFunction("char",1);
addCustomFunction("char_length",1);
addCustomFunction("difference",2);
addCustomFunction("hextoraw",1);
addCustomFunction("insert",4);
addCustomFunction("left",2);
addCustomFunction("locate",3);
addCustomFunction("octet_length",1);
addCustomFunction("rawtohex",1);
addCustomFunction("repeat",2);
addCustomFunction("replace",3);
addCustomFunction("right",2);
addCustomFunction("space",1);
addCustomFunction("substr",3);
addCustomFunction("substring",3);
addCustomFunction("curdate",0);
addCustomFunction("curtime",0);
addCustomFunction("datediff",3);
addCustomFunction("dayname",1);
addCustomFunction("dayofmonth",1);
addCustomFunction("dayofweek",1);
addCustomFunction("dayofyear",1);
addCustomFunction("hour",1);
addCustomFunction("minute",1);
addCustomFunction("month",1);
addCustomFunction("monthname",1);
addCustomFunction("now",0);
addCustomFunction("quarter",1);
addCustomFunction("second",1);
addCustomFunction("week",1);
addCustomFunction("year",1);
addCustomFunction("current_date",1);
addCustomFunction("current_time",1);
addCustomFunction("current_timestamp",1);
addCustomFunction("database",0);
addCustomFunction("user",0);
addCustomFunction("current_user",0);
addCustomFunction("identity",0);
addCustomFunction("ifnull",2);
addCustomFunction("casewhen",3);
addCustomFunction("convert",2);
//addCustomFunction("cast",1);
addCustomFunction("coalesce",1000);
addCustomFunction("nullif",2);
addCustomFunction("extract",1);
addCustomFunction("position",1);
*/
// print(expression);
throw new DatabaseException(
"unsopprted sql statement (op-count:" + expression.nbOperands() + ";operator:" + op + ") ",
null,
sql,
null);
}
private void jButton1ActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton1ActionPerformed
// TODO add your handling code here:
String username = vw.username;
Emp_id = jTextField2.getText();
Bill_id = jTextField3.getText();
if (vw.t == 1) {
q.executeQuery("select mgr_id from man_id where username = '******'");
try {
if (q.rs.next()) {
String mgr_id = q.rs.getString("mgr_id");
q.executeQuery("select bill_id from bill where mgr_id = '" + mgr_id + "'");
while (q.rs.next()) {
System.out.println(q.rs.getString("Bill_id"));
if (Bill_id.equals(q.rs.getString("Bill_id"))) {
a = 0;
}
}
}
} catch (SQLException ex) {
Logger.getLogger(View_Mb.class.getName()).log(Level.SEVERE, null, ex);
}
q.executeQuery("select * from Bill where Bill_id = '" + Bill_id + "'");
if (a == 0) {
JOptionPane.showMessageDialog(this, "proceed to next pane");
try {
if (q.rs.next()) {
String binfo = q.rs.getString("Bill_Info");
String amt = q.rs.getString("amount");
String exp = q.rs.getString("expense");
String st = q.rs.getString("status");
System.out.println(binfo);
jTextArea1.setText(
"Bill_ID : "
+ Bill_id
+ "\n"
+ "Emp_ID : "
+ Emp_id
+ "\n\n"
+ "Amount : "
+ amt
+ "\n"
+ "Expense : "
+ exp
+ "\n"
+ "Status : "
+ st
+ "\n\n"
+ "Bill_Info : "
+ binfo);
}
} catch (SQLException ex) {
// Logger.getLogger(Mgrlogn.class.getName()).log(Level.SEVERE, null, ex);
JOptionPane.showMessageDialog(this, ex);
}
} else {
jButton2.setVisible(false);
jButton3.setVisible(false);
}
} else if (vw.t == 0) {
// String username = vw.username;
Emp_id = jTextField2.getText();
Bill_id = jTextField3.getText();
// if(vw.t == 0) {
// q.executeQuery("select emp_id from employee_id where username = '******'");
// try {
// if(q.rs.next())
// {
// String emp_id = q.rs.getString("emp_id");
q.executeQuery("select bill_id from meo");
try {
while (q.rs.next()) {
System.out.println(q.rs.getString("Bill_id"));
if (Bill_id.equals(q.rs.getString("Bill_id"))) {
a = 0;
}
}
// }
// } catch (SQLException ex) {
// Logger.getLogger(View_Mb.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(View_OR.class.getName()).log(Level.SEVERE, null, ex);
}
}
q.executeQuery("select * from Bill where Bill_id = '" + Bill_id + "'");
if (a == 0) {
JOptionPane.showMessageDialog(this, "proceed to next pane");
try {
if (q.rs.next()) {
String binfo = q.rs.getString("Bill_Info");
String amt = q.rs.getString("amount");
String exp = q.rs.getString("expense");
String st = q.rs.getString("status");
System.out.println(binfo);
jTextArea1.setText(
"Bill_ID : "
+ Bill_id
+ "\n"
+ "Emp_ID : "
+ Emp_id
+ "\n\n"
+ "Amount : "
+ amt
+ "\n"
+ "Expense : "
+ exp
+ "\n"
+ "Status : "
+ st
+ "\n\n"
+ "Bill_Info : "
+ binfo);
}
} catch (SQLException ex) {
// Logger.getLogger(Mgrlogn.class.getName()).log(Level.SEVERE, null, ex);
JOptionPane.showMessageDialog(this, ex);
}
} else {
jButton2.setVisible(false);
jButton3.setVisible(false);
}
} // GEN-LAST:event_jButton1ActionPerformed
public View_OR() throws SQLException {
initComponents();
q.pst = q.conn.prepareStatement("select * from meo");
q.rs = q.pst.executeQuery();
jTable1.setModel(DbUtils.resultSetToTableModel(q.rs));
}