001: /*
002: * Created on 29/07/2004
003: *
004: * Swing Components - visit http://sf.net/projects/gfd
005: *
006: * Copyright (C) 2004 Igor Regis da Silva Simões
007: *
008: * This program is free software; you can redistribute it and/or
009: * modify it under the terms of the GNU General Public License
010: * as published by the Free Software Foundation; either version 2
011: * of the License, or (at your option) any later version.
012: *
013: * This program is distributed in the hope that it will be useful,
014: * but WITHOUT ANY WARRANTY; without even the implied warranty of
015: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: * GNU General Public License for more details.
017: *
018: * You should have received a copy of the GNU General Public License
019: * along with this program; if not, write to the Free Software
020: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
021: *
022: */
023:
024: package br.com.igor.db;
025:
026: import java.util.ArrayList;
027: import java.util.Map;
028:
029: /**
030: * @author Igor Regis da Silva Simoes
031: */
032: public abstract class AbstractSQLParser implements SQLParser {
033:
034: /**
035: * Cria uma nova instância de AbstractSQLParser
036: */
037: public AbstractSQLParser() {
038: //Não fazemos nada
039: }
040:
041: /**
042: * @see br.com.igor.db.SQLParser#getDeleteSQL(java.lang.String, java.util.Map, java.util.ArrayList, java.lang.Object[])
043: */
044: public ParsedPreparedStatement getDeleteSQL(String tableName,
045: Map data, ArrayList<String> columnsName,
046: ArrayList<CondicaoSQL> condicoesExtras,
047: ArrayList<String> pkNames) {
048: ParsedPreparedStatement statement = new ParsedPreparedStatement();
049: pkNames.trimToSize();
050: boolean usePk = false;
051: if (pkNames.size() >= 1) {
052: for (String pkName : pkNames) {
053: Object pk = data.get(pkName);
054: if (pk != null) {
055: usePk = true;
056: break;
057: }
058: }
059: }
060: String sql = "delete from " + ENVOLVE_NOME_TABELA + tableName
061: + ENVOLVE_NOME_TABELA + " ";
062: statement = getWhere(data, usePk ? pkNames : columnsName,
063: " and", condicoesExtras);
064:
065: statement.sql = sql + statement.sql + ";";
066: return statement;
067: }
068:
069: /**
070: * @see br.com.igor.db.SQLParser#getInsertSQL(java.lang.String, java.util.Map, java.util.ArrayList)
071: */
072: public ParsedPreparedStatement getInsertSQL(String tableName,
073: Map data, ArrayList<String> columnsName) {
074: String virgula = "";
075: ParsedPreparedStatement statement = new ParsedPreparedStatement();
076:
077: String sql = "insert into " + ENVOLVE_NOME_TABELA + tableName
078: + ENVOLVE_NOME_TABELA + " (";
079: ArrayList<Object> params = new ArrayList<Object>();
080:
081: for (int i = 0; i < columnsName.size(); i++) {
082: sql += virgula + ENVOLVE_COLUNA + columnsName.get(i)
083: + ENVOLVE_COLUNA;
084: virgula = ",";
085: }
086:
087: sql += ") Values(";
088:
089: virgula = "";
090:
091: for (String coluna : columnsName) {
092: Object dado = data.get(coluna);
093: if (dado != null) {
094: sql += virgula + "?";
095: params.add(dado);
096: } else {
097: sql += virgula + "?";
098: params.add("**NULL**");
099: }
100: virgula = ",";
101: }
102:
103: sql += ");";
104: statement.sql = sql;
105: statement.params = params;
106: return statement;
107:
108: }
109:
110: /**
111: * @see br.com.igor.db.SQLParser#getSelectSQL(java.lang.String, java.lang.String, java.util.Map, java.util.ArrayList, java.lang.String[], boolean[], java.lang.Object[])
112: */
113: public ParsedPreparedStatement getSelectSQL(
114: String resultadoDesejado, String tableName, Map data,
115: ArrayList<String> columnsName, String[] orderByColumnns,
116: boolean[] crescente, ArrayList<CondicaoSQL> condicoesExtras) {
117: ParsedPreparedStatement statement = new ParsedPreparedStatement();
118: ParsedPreparedStatement where = getWhere(data, columnsName,
119: " and", condicoesExtras);
120: String sql = "select "
121: + (resultadoDesejado == null ? "*" : resultadoDesejado)
122: + " from "
123: + ENVOLVE_NOME_TABELA
124: + tableName
125: + ENVOLVE_NOME_TABELA
126: + where.sql
127: + (resultadoDesejado == null ? getOrderBy(
128: orderByColumnns, crescente) : "") + ";";
129: statement.sql = sql;
130: statement.params = where.params;
131: return statement;
132: }
133:
134: /**
135: * @see br.com.igor.db.SQLParser#getUpdateSQL(java.lang.String, java.util.Map, java.util.ArrayList, java.util.ArrayList)
136: */
137: public ParsedPreparedStatement getUpdateSQL(String tableName,
138: Map data, ArrayList<String> columnsName,
139: ArrayList<String> pkNames) {
140: ParsedPreparedStatement statement = new ParsedPreparedStatement();
141: ArrayList<Object> params = new ArrayList<Object>();
142: String virgula = "";
143: String sql = "update " + ENVOLVE_COLUNA + tableName
144: + ENVOLVE_COLUNA + " set";
145:
146: for (String coluna : columnsName) {
147: Object dado = data.get(coluna);
148: if (dado != null) {
149: sql += virgula + " " + ENVOLVE_COLUNA + coluna
150: + ENVOLVE_COLUNA + " =? ";
151: params.add(dado);
152: } else {
153: sql += virgula + " " + ENVOLVE_COLUNA + coluna
154: + ENVOLVE_COLUNA + " =? ";
155: params.add("**NULL**");
156: }
157: virgula = ",";
158: }
159: ParsedPreparedStatement tempStatement = getWhere(data, pkNames,
160: " and ", null); //Montamos a clausula where
161: statement.sql = sql + tempStatement.sql + ";"; //Finalizamos o SQL com o where no final
162: statement.params = params;//Pegamos os valores para os sets
163: statement.params.addAll(tempStatement.params); //Apendamos os parametros para o where
164: return statement;
165: }
166:
167: /**
168: * Retorna um clausula where
169: * @param data Dados que comporão a clausula
170: * @param columnsName Nomes das colunas
171: * @param separator caracter separados entre as colunas
172: * @param condicoesExtras lista de consições extras
173: * @return clausula where
174: */
175: protected ParsedPreparedStatement getWhere(Map data,
176: ArrayList<String> columnsName, String separator,
177: ArrayList<CondicaoSQL> condicoesExtras) {
178: ParsedPreparedStatement ppStatement = new ParsedPreparedStatement();
179: ArrayList<Object> params = new ArrayList<Object>();
180: String virgula = "";
181: String sql = " where (";
182:
183: if (condicoesExtras != null)
184: for (CondicaoSQL<Object> condicao : condicoesExtras) {
185: sql += virgula + " " + condicao.getParsedString();
186: virgula = separator;
187: }
188:
189: Object dado = null;
190: for (String coluna : columnsName) {
191: if ((dado = data.get(coluna)) != null) {
192: if (dado instanceof String)
193: sql += virgula + " " + ENVOLVE_COLUNA + coluna
194: + ENVOLVE_COLUNA + " like ? ";
195: else
196: sql += virgula + " " + ENVOLVE_COLUNA + coluna
197: + ENVOLVE_COLUNA + " =? ";
198: params.add(dado);
199: virgula = separator;
200: }
201: }
202: sql += ")";
203:
204: ppStatement.sql = sql.indexOf('(') == sql.indexOf(')') - 1 ? ""
205: : sql;
206: ppStatement.params = params;
207: return ppStatement;
208: }
209:
210: /**
211: * Retorna uma clausula order by
212: * @param orderByColumnns Colunas que comporão a clausula
213: * @param crescente ondens de cada coluna
214: * @return clausula order by
215: */
216: protected String getOrderBy(String[] orderByColumnns,
217: boolean[] crescente) {
218: if (orderByColumnns == null)
219: return "";
220:
221: String sql = " order by ";
222: String virgula = "";
223:
224: for (int i = 0; i < orderByColumnns.length; i++) {
225: sql += virgula + ENVOLVE_COLUNA + orderByColumnns[i]
226: + ENVOLVE_COLUNA
227: + (crescente[i] ? " ASC" : " DESC");
228: virgula = ", ";
229: }
230:
231: return sql.equals(" order by ") ? "" : sql;
232: }
233:
234: /**
235: * @see br.com.igor.db.SQLParser#parse(java.lang.String)
236: */
237: public ParsedPreparedStatement parse(ParsedPreparedStatement sql) {
238: sql.sql = sql.sql.replace(SQLParser.ENVOLVE_NOME_TABELA, '"')
239: .replace(SQLParser.ENVOLVE_DATA, '\'');
240: //FIXME Descomente para debugar execução de SQL's
241: // System.out.println(sql.sql + " " + sql.params);System.out.flush();
242: // if (statement.sql.indexOf("TipoDeConta") != -1)
243: // Thread.dumpStack();
244: return sql;
245: }
246: }
|