Advertisement
Guest User

Automatizar selects con DatabaseMetaData

a guest
Oct 4th, 2016
190
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.53 KB | None | 0 0
  1. esta es la parte del código en Model.java, lo que respectan los select automatizados:
  2.  
  3. public String select(String[] cols, String[][] tables, String[][] where,String[] group,String[][] having,String[][] order){
  4. String Consulta = "select ";
  5. if(cols == null){
  6. Consulta += "* ";
  7. } else {
  8. Consulta += this.createColumns(cols, tables);
  9. }
  10. LinkedList<String[]> tab = this.prepareTables(tables);
  11. Consulta += this.createFrom(tab);
  12. if(where != null){
  13. Consulta += " " + this.createConditions(tab,where);
  14. }
  15. if(group != null){
  16. Consulta += " " + this.createGroup(tab,group);
  17. }
  18. if(having != null){
  19. Consulta += " " + this.createHaving(tab,having);
  20. }
  21. if(order != null){
  22. Consulta += " " + this.createOrder(tab,order);
  23. }
  24. return Consulta;
  25. }
  26. private LinkedList<String[]> prepareTables(String[][] tables){
  27. LinkedList<String[]> tab = new LinkedList();
  28. String[] temp = { this.generateKey(tab, this.getTable()), this.getTable(),null};
  29. tab.add(temp);
  30. if(tables != null){
  31. for (String[] table : tables) {
  32. String[] temp2 = {this.generateKey(tab, table[0]), table[0], table[1]};
  33. tab.add(temp2);
  34. }
  35. }
  36. return tab;
  37. }
  38. private String generateKey(LinkedList<String[]> alias,String now){
  39. String d = "" + now.charAt(0);
  40. if(alias.isEmpty()){
  41. return d;
  42. } else {
  43. for (String[] alia : alias) {
  44. for (int x = 0; x < now.length(); x++) {
  45. if (alia[0].equals(d)) {
  46. x++;
  47. d += now.charAt(x);
  48. }
  49. }
  50. }
  51. return d;
  52. }
  53. }
  54. private String createColumns(String[] cols,String[][] tables){
  55. String columns = "";
  56. for(String c : cols){
  57. if(tables == null){
  58. columns += c;
  59. } else {
  60. LinkedList<String[]> tab = this.prepareTables(tables);
  61. for (String[] tab1 : tab) {
  62. LinkedList<String> temp = this.objbd.getColumns(tab1[1]);
  63. for (String c1 : temp) {
  64. if (c1.equals(c)) {
  65. columns += tab1[0] + "." + c;
  66. }
  67. }
  68. }
  69. }
  70. if(!cols[cols.length -1].equals(c)){
  71. columns += ",";
  72. } else {
  73. columns += " ";
  74. }
  75. }
  76. return columns;
  77. }
  78. private String createFrom(LinkedList<String[]> tables){
  79. String from = "from " + tables.getFirst()[1] + " " + tables.getFirst()[0] + " ";
  80. for(int i = 1; i < tables.size(); i++){
  81. from += tables.get(i)[2] + " join " + tables.get(i)[1] + " " + tables.get(i)[0] + " on";
  82. from += this.createCombos(
  83. new String[]{tables.get(i)[0],tables.get(i)[1]},
  84. new String[]{tables.get(i-1)[0],tables.get(i-1)[1]}
  85. );
  86. if(!tables.getLast()[1].equals(tables.get(i)[1])){
  87. from += " ";
  88. }
  89. }
  90. return from;
  91. }
  92. private String createCombos(String[] emisor, String[] receptor){
  93. String combo = " ";
  94. LinkedList<String> keys = this.objbd.getPrimaryKeys(emisor[1]);
  95. LinkedList<String> fays = this.objbd.getForeignKeys(receptor[1]);
  96. for(String f : fays){
  97. for(String k : keys){
  98. if(k.equals(f)){
  99. combo += receptor[0] +"."+k +" = "+ emisor[0] +"."+f;
  100. break;
  101. }
  102. }
  103. }
  104. return combo;
  105. }
  106.  
  107. private String createConditions(LinkedList<String[]> tables, String[][] where){
  108. String condition = "where ";
  109. for (String[] where1 : where) {
  110. for (String[] table : tables) {
  111. LinkedList<String> temp = this.objbd.getColumns(table[1]);
  112. for (String c1 : temp) {
  113. if (c1.equals(where1[0])) {
  114. where1[0] = table[0] + "." + where1[0];
  115. }
  116. }
  117. }
  118. }
  119. for (String[] where1 : where) {
  120. if (where1[1].equals("between") || where1[1].equals("not between")) {
  121. condition += where1[0] + " " + where1[1] + " ? and ?";
  122. } else if (where1[1].equals("not null") || where1[1].equals("null")) {
  123. condition += where1[0] + " " + where1[1];
  124. } else {
  125. condition += where1[0] + " " + where1[1] + " ?";
  126. }
  127. if (!where[where.length -1][0].equals(where1[0])) {
  128. condition += " " + where1[2] + " ";
  129. }
  130. }
  131. return condition;
  132. }
  133. private String createGroup(LinkedList<String[]> tables,String[] group){
  134. String groupby = "group by ";
  135. for (int i= 0; i< group.length; i++) {
  136. for (String[] table : tables) {
  137. LinkedList<String> temp = this.objbd.getColumns(table[1]);
  138. for (String c1 : temp) {
  139. if (c1.equals(group[i])) {
  140. group[i] = table[0] + "." + group[i];
  141. }
  142. }
  143. }
  144. }
  145. for (String group1 : group) {
  146. groupby += group1;
  147. if(!group[group.length -1].equals(group1)){
  148. groupby += ",";
  149. }
  150. }
  151. return groupby;
  152. }
  153. private String createHaving(LinkedList<String[]> tables,String[][] having){
  154. String hav = "having ";
  155. return hav;
  156. }
  157. private String createOrder(LinkedList<String[]> tables,String[][] order){
  158. String orderby = "order by ";
  159. for (String[] order1 : order) {
  160. for (String[] table : tables) {
  161. LinkedList<String> temp = this.objbd.getColumns(table[1]);
  162. for (String c1 : temp) {
  163. if (c1.equals(order1[0])) {
  164. order1[0] = table[0] + "." + order1[0];
  165. }
  166. }
  167. }
  168. }
  169. for (String[] order1 : order) {
  170. orderby += order1[0] + " " + order1[1];
  171. if(!order[order.length -1][0].equals(order1[0])){
  172. orderby += ",";
  173. }
  174. }
  175. return orderby;
  176. }
  177.  
  178. pero no sé cómo armar los parámetros del array en el having, en el main:
  179.  
  180. //ejemplo para el having
  181. System.out.println("");
  182. System.out.println(new CompradorModel().select(
  183. new String[]{"usuCI","usuNom1"},
  184. new String[][]{{"usuarios","left"},{"roles","inner"}},
  185. new String[][]{{"usuCI","like","and"},{"usuFecNac","between"}},
  186. new String[]{"usuCI"},
  187. null,
  188. new String[][]{{"usuCI","asc"},{"usuFecNac","desc"}}
  189. ));
  190.  
  191. Me falta pulir bien el tema de los havings automatizados, las serias subconsultas xq no sé cómo hacerlo, necesitaría una idea de cómo hacerlas automatizadas, y me falta pulir el createColumns en caso de renombrar campos y también si quiero usar funciones agregadas.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement