KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > codewiz > SQLGenerator


1 package codewiz;
2
3 import java.sql.*;
4 import dinamica.*;
5
6 /**
7  * Generates SQL code for INSERT and UPDATE statements
8  *
9  * <br>
10  * Creation date: 12/10/2003<br>
11  * Last Update: 12/10/2003<br>
12  * (c) 2003 Martin Cordova<br>
13  * This code is released under the LGPL license<br>
14  * @author Martin Cordova
15  * */

16 public class SQLGenerator extends GenericTransaction
17 {
18
19     /* (non-Javadoc)
20      * @see dinamica.GenericTransaction#service(dinamica.Recordset)
21      */

22     public int service(Recordset inputs) throws Throwable JavaDoc
23     {
24         
25         int rc = super.service(inputs);
26         
27         DatabaseMetaData md = getConnection().getMetaData();
28         ResultSet cols = md.getColumns(null, (String JavaDoc)inputs.getValue("schema"), (String JavaDoc)inputs.getValue("table"), "%");
29         Recordset rs = new Recordset(cols);
30         cols.close();
31
32         String JavaDoc sqlIns = sqlInsert(inputs, (String JavaDoc)inputs.getValue("table"), rs);
33         String JavaDoc sqlUpd = sqlUpdate((String JavaDoc)inputs.getValue("table"),(String JavaDoc)inputs.getValue("pkey"), rs);
34         String JavaDoc sqlInsP = sqlInsertP(inputs, (String JavaDoc)inputs.getValue("table"), rs);
35         String JavaDoc sqlUpdP = sqlUpdateP((String JavaDoc)inputs.getValue("table"),(String JavaDoc)inputs.getValue("pkey"), rs);
36         String JavaDoc javaCode = getArrayParams((String JavaDoc)inputs.getValue("pkey"), rs);
37
38         inputs.setValue("sql_insert", sqlIns);
39         inputs.setValue("sql_update", sqlUpd);
40         inputs.setValue("sql_insertp", sqlInsP);
41         inputs.setValue("sql_updatep", sqlUpdP);
42         inputs.setValue("javacode", javaCode);
43                 
44         return rc;
45         
46     }
47
48     /**
49      * Create sql insert statement
50      */

51     String JavaDoc sqlInsert(Recordset inputs, String JavaDoc tableName, Recordset rs) throws Throwable JavaDoc
52     {
53         
54         
55         String JavaDoc temp = "insert into " + tableName + " ";
56         String JavaDoc temp1 = "";
57         String JavaDoc temp2 = "";
58         String JavaDoc useSequence = (String JavaDoc)inputs.getValue("use_sequence");
59         String JavaDoc sequenceName = (String JavaDoc)inputs.getValue("sequence_name");
60         String JavaDoc pkey = (String JavaDoc)inputs.getValue("pkey");
61         pkey = pkey.toLowerCase();
62
63         int i;
64         
65         for (i=0; i<rs.getRecordCount()-1; i++)
66         {
67
68             rs.setRecordNumber(i);
69             
70             String JavaDoc colName = String.valueOf(rs.getValue("column_name")).toLowerCase();
71             String JavaDoc colExpr = "${fld:" + colName + "}";
72             
73             if (useSequence!=null && useSequence.equals("y") && (pkey.equals(colName)))
74                 colExpr = "${seq:nextval@" + sequenceName + "}";
75             
76             temp1 = temp1 + "\t" + colName + ",\r\n";
77             temp2 = temp2 + "\t" + colExpr + ",\r\n";
78             
79         }
80         
81         rs.last();
82         temp1 = temp1 + "\t" + String.valueOf(rs.getValue("column_name")).toLowerCase() + "\r\n";
83         temp2 = temp2 + "\t" + "${fld:" + String.valueOf(rs.getValue("column_name")).toLowerCase() + "}\r\n";
84         temp = temp + " \r\n(\r\n" + temp1 + ")\r\nvalues \r\n(\r\n" + temp2 + ")";
85         
86         return temp;
87
88     }
89
90     /**
91      * Create sql insert prepared statement
92      */

93     String JavaDoc sqlInsertP(Recordset inputs, String JavaDoc tableName, Recordset rs) throws Throwable JavaDoc
94     {
95         
96         
97         String JavaDoc temp = "insert into " + tableName + " ";
98         String JavaDoc temp1 = "";
99         String JavaDoc temp2 = "";
100         String JavaDoc useSequence = (String JavaDoc)inputs.getValue("use_sequence");
101         String JavaDoc sequenceName = (String JavaDoc)inputs.getValue("sequence_name");
102         String JavaDoc pkey = (String JavaDoc)inputs.getValue("pkey");
103         pkey = pkey.toLowerCase();
104
105         int i;
106         
107         for (i=0; i<rs.getRecordCount()-1; i++)
108         {
109
110             rs.setRecordNumber(i);
111             
112             String JavaDoc colName = String.valueOf(rs.getValue("column_name")).toLowerCase();
113             String JavaDoc colExpr = "?";
114             
115             if (useSequence!=null && useSequence.equals("y") && (pkey.equals(colName)))
116                 colExpr = "${seq:nextval@" + sequenceName + "}";
117             
118             temp1 = temp1 + "\t" + colName + ",\r\n";
119             temp2 = temp2 + "\t" + colExpr + ",\r\n";
120             
121         }
122         
123         rs.last();
124         temp1 = temp1 + "\t" + String.valueOf(rs.getValue("column_name")).toLowerCase() + "\r\n";
125         temp2 = temp2 + "\t" + "?\r\n";
126         temp = temp + " \r\n(\r\n" + temp1 + ")\r\nvalues \r\n(\r\n" + temp2 + ")";
127         
128         return temp;
129
130     }
131
132     
133     /**
134      * Create sql update statement
135      */

136     String JavaDoc sqlUpdate(String JavaDoc tableName, String JavaDoc primaryKey, Recordset rs) throws Throwable JavaDoc
137     {
138         
139         
140         String JavaDoc temp = "update " + tableName + " set \r\n\r\n";
141         String JavaDoc temp1 = "";
142
143         primaryKey = primaryKey.toLowerCase();
144
145         int i;
146         
147         for (i=0; i<rs.getRecordCount()-1; i++)
148         {
149             rs.setRecordNumber(i);
150             String JavaDoc colName = (String JavaDoc)rs.getValue("column_name");
151             colName = colName.toLowerCase();
152             if (!colName.equals(primaryKey))
153                 temp1 = temp1 + "\t" + colName + " = " + "${fld:" + colName + "},\r\n";
154         }
155         
156         rs.last();
157         String JavaDoc colName = (String JavaDoc)rs.getValue("column_name");
158         colName = colName.toLowerCase();
159         if (!colName.equals(primaryKey))
160             temp1 = temp1 + "\t" + colName + " = " + "${fld:" + colName + "}\r\n";
161         temp = temp + temp1 + "\r\nwhere\r\n\t" + primaryKey + " = ${fld:" + primaryKey + "}";
162         
163         return temp;
164
165     }
166
167     /**
168      * Create sql update prepared statement
169      */

170     String JavaDoc sqlUpdateP(String JavaDoc tableName, String JavaDoc primaryKey, Recordset rs) throws Throwable JavaDoc
171     {
172         
173         
174         String JavaDoc temp = "update " + tableName + " set \r\n\r\n";
175         String JavaDoc temp1 = "";
176         primaryKey = primaryKey.toLowerCase();
177
178         int i;
179         
180         for (i=0; i<rs.getRecordCount()-1; i++)
181         {
182             rs.setRecordNumber(i);
183             String JavaDoc colName = (String JavaDoc)rs.getValue("column_name");
184             colName = colName.toLowerCase();
185             if (!colName.equals(primaryKey))
186                 temp1 = temp1 + "\t" + colName + " = " + "?,\r\n";
187         }
188         
189         rs.last();
190         String JavaDoc colName = (String JavaDoc)rs.getValue("column_name");
191         colName = colName.toLowerCase();
192         if (!colName.equals(primaryKey))
193             temp1 = temp1 + "\t" + colName + " = " + "?\r\n";
194         temp = temp + temp1 + "\r\nwhere\r\n\t" + primaryKey + " = ?";
195         
196         return temp;
197
198     }
199
200     /**
201      * Create java code to set parameters array
202      */

203     String JavaDoc getArrayParams(String JavaDoc primaryKey, Recordset rs) throws Throwable JavaDoc
204     {
205         
206         String JavaDoc temp = "String[] params =\r\n"
207                     + "{\r\n";
208
209         String JavaDoc temp1 = "";
210
211         int i;
212         
213         primaryKey = primaryKey.toLowerCase();
214         
215         for (i=0; i<rs.getRecordCount()-1; i++)
216         {
217             rs.setRecordNumber(i);
218             String JavaDoc colName = (String JavaDoc)rs.getValue("column_name");
219             colName = colName.toLowerCase();
220             if (!colName.equals(primaryKey))
221                 temp1 = temp1 + "\t\"" + colName + "\",\r\n";
222         }
223         
224         rs.last();
225         String JavaDoc colName = (String JavaDoc)rs.getValue("column_name");
226         colName = colName.toLowerCase();
227         if (!colName.equals(primaryKey))
228         temp1 = temp1 + "\t\"" + colName + "\"\r\n";
229         
230         temp = temp + temp1 + "\r\n};";
231         
232         return temp;
233
234     }
235     
236 }
237
Popular Tags