001: /**
002: * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
003: *
004: * Permission is hereby granted, free of charge, to any person obtaining a copy
005: * of this software and associated documentation files (the "Software"), to deal
006: * in the Software without restriction, including without limitation the rights
007: * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
008: * copies of the Software, and to permit persons to whom the Software is
009: * furnished to do so, subject to the following conditions:
010: *
011: * The above copyright notice and this permission notice shall be included in
012: * all copies or substantial portions of the Software.
013: *
014: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
015: * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
016: * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
017: * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
018: * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
019: * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
020: * SOFTWARE.
021: */
022:
023: import java.io.BufferedReader;
024: import java.io.File;
025: import java.io.FileInputStream;
026: import java.io.FileReader;
027: import java.io.IOException;
028:
029: import java.sql.Connection;
030: import java.sql.DriverManager;
031: import java.sql.PreparedStatement;
032: import java.sql.ResultSet;
033: import java.sql.ResultSetMetaData;
034: import java.sql.SQLException;
035: import java.sql.Statement;
036:
037: import java.util.ArrayList;
038: import java.util.Iterator;
039: import java.util.List;
040: import java.util.Properties;
041: import java.util.StringTokenizer;
042:
043: /**
044: * <a href="LiferaySQL.java.html"><b><i>View Source</i></b></a>
045: *
046: * @author Brian Wing Shun Chan
047: *
048: */
049: public class LiferaySQL {
050:
051: public static void main(String[] args) {
052: String sql = "";
053:
054: for (int i = 0; i < args.length; i++) {
055: sql += args[i] + " ";
056: }
057:
058: if (!sql.equals("")) {
059: new LiferaySQL(sql.trim());
060: }
061: }
062:
063: public LiferaySQL(String sql) {
064: System.out.println(sql);
065:
066: Connection con = null;
067: PreparedStatement ps = null;
068: ResultSet rs = null;
069:
070: try {
071: Properties props = new Properties();
072:
073: try {
074: props.load(new FileInputStream(
075: new File("db.properties")));
076: } catch (IOException ioe) {
077: ioe.printStackTrace();
078:
079: return;
080: }
081:
082: String driver = props.getProperty("driver");
083: String url = props.getProperty("url");
084: String user = props.getProperty("user");
085: String password = props.getProperty("password");
086:
087: Class.forName(driver);
088:
089: con = DriverManager.getConnection(url, user, password);
090:
091: con.setAutoCommit(false);
092:
093: File sqlFile = new File(sql);
094:
095: if (sqlFile.exists()) {
096: StringBuffer sb = new StringBuffer();
097:
098: BufferedReader br = new BufferedReader(new FileReader(
099: sqlFile));
100:
101: String line = null;
102:
103: while ((line = br.readLine()) != null) {
104: if (!line.startsWith("--")) {
105: sb.append(line);
106: }
107: }
108:
109: br.close();
110:
111: StringTokenizer st = new StringTokenizer(sb.toString(),
112: ";");
113:
114: while (st.hasMoreTokens()) {
115: line = st.nextToken();
116:
117: System.out.println(line + ";");
118:
119: ps = con.prepareStatement(line);
120: ps.executeUpdate();
121: }
122: } else if (sql.toLowerCase().startsWith("insert ")
123: || sql.toLowerCase().startsWith("update ")) {
124:
125: ps = con.prepareStatement(sql);
126: ps.executeUpdate(sql);
127: } else {
128: ps = con.prepareStatement(sql);
129: rs = ps.executeQuery(sql);
130: ResultSetMetaData rsmd = rs.getMetaData();
131:
132: int[] width = new int[rsmd.getColumnCount() + 1];
133:
134: StringBuffer sb = new StringBuffer();
135:
136: for (int i = 1; i <= rsmd.getColumnCount(); i++) {
137: width[i] = rsmd.getColumnLabel(i).length();
138: }
139:
140: List results = new ArrayList();
141:
142: while (rs.next()) {
143: String[] rowResult = new String[rsmd
144: .getColumnCount() + 1];
145:
146: for (int i = 1; i <= rsmd.getColumnCount(); i++) {
147: Object obj = rs.getObject(i);
148:
149: if (obj != null) {
150: rowResult[i] = obj.toString();
151:
152: int objWidth = obj.toString().length();
153:
154: if (width[i] < objWidth) {
155: width[i] = objWidth;
156: }
157: } else {
158: rowResult[i] = "";
159: }
160: }
161:
162: results.add(rowResult);
163: }
164:
165: _printLine(rsmd, width, sb);
166:
167: for (int i = 1; i <= rsmd.getColumnCount(); i++) {
168: String label = rsmd.getColumnLabel(i);
169:
170: sb.append("| ").append(label);
171:
172: for (int j = 0; j <= width[i] - label.length(); j++) {
173: sb.append(" ");
174: }
175: }
176:
177: sb.append("|\n");
178:
179: _printLine(rsmd, width, sb);
180:
181: Iterator itr = results.iterator();
182:
183: while (itr.hasNext()) {
184: String[] rowResult = (String[]) itr.next();
185:
186: for (int i = 1; i <= rsmd.getColumnCount(); i++) {
187: String s = rowResult[i];
188:
189: sb.append("| ").append(s);
190:
191: for (int j = 0; j <= width[i] - s.length(); j++) {
192: sb.append(" ");
193: }
194: }
195:
196: sb.append("|\n");
197: }
198:
199: _printLine(rsmd, width, sb);
200:
201: System.out.println(sb.toString());
202: }
203:
204: con.commit();
205: } catch (SQLException sqle) {
206: while (sqle != null) {
207: sqle.printStackTrace();
208:
209: sqle = sqle.getNextException();
210: }
211: } catch (Exception e) {
212: e.printStackTrace();
213: } finally {
214: _cleanUp(con, ps, rs);
215: }
216: }
217:
218: private void _cleanUp(Connection con, Statement s, ResultSet rs) {
219: if (rs != null) {
220: try {
221: rs.close();
222: } catch (Exception e) {
223: }
224: }
225:
226: if (s != null) {
227: try {
228: s.close();
229: } catch (Exception e) {
230: }
231: }
232:
233: if (con != null) {
234: try {
235: con.close();
236: } catch (Exception e) {
237: }
238: }
239: }
240:
241: private void _printLine(ResultSetMetaData rsmd, int[] width,
242: StringBuffer sb) throws SQLException {
243:
244: for (int i = 1; i <= rsmd.getColumnCount(); i++) {
245: sb.append("+");
246:
247: for (int j = 0; j <= width[i] + 1; j++) {
248: sb.append("-");
249: }
250: }
251:
252: sb.append("+\n");
253: }
254:
255: }
|