View Javadoc

1   package org.jmux.app.component;
2   
3   import org.apache.commons.dbutils.QueryRunner;
4   import org.apache.commons.dbutils.handlers.ArrayListHandler;
5   import org.jmux.Log;
6   import org.jmux.app.component.jmx.JMX;
7   
8   import java.sql.SQLException;
9   import java.util.List;
10  
11  /******************************************************************************
12   jmux - Java Modules Using XML
13   Copyright © 2006 jmux.org
14  
15   This library is free software; you can redistribute it and/or
16   modify it under the terms of the GNU Lesser General Public
17   License as published by the Free Software Foundation; either
18   version 2.1 of the License, or (at your option) any later version.
19  
20   This library is distributed in the hope that it will be useful,
21   but WITHOUT ANY WARRANTY; without even the implied warranty of
22   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
23   Lesser General Public License for more details.
24  
25   You should have received a copy of the GNU Lesser General Public
26   License along with this library; if not, write to the Free Software
27   Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
28   *****************************************************************************/
29  
30  /***
31   * @author donaldw
32   */
33  public class PersisterUtil extends AbstractComponent {
34  
35      /***
36       * use for SQL command SELECT
37       */
38      public void query(String expression) throws SQLException {
39  
40          ArrayListHandler arrayListHandler = new ArrayListHandler();
41  
42          List results = (List) getQueryRunner().query(expression, arrayListHandler);
43  
44          dump(results);
45      }
46  
47      @JMX
48      @Wire
49      private Persister persister;
50  
51      private QueryRunner getQueryRunner() {
52          return persister.getQueryRunner();
53      }
54  
55      private void dump(List rs) {
56  
57          for (Object result : rs) {
58              Object[] resultsAsArray = (Object[]) result;
59  
60              StringBuilder sb = new StringBuilder();
61  
62              for (Object o : resultsAsArray) {
63                  sb.append(o);
64                  sb.append(' ');
65              }
66              log(sb.toString());
67          }
68      }
69  
70      @JMX
71      public void doIt() {
72  
73          try {
74              //make an empty table
75              //
76              // by declaring the id column IDENTITY, the db will automatically
77              // generate unique values for new rows- useful for row keys
78              getQueryRunner().update("CREATE TABLE "
79                      + "sample_table "
80                      + "( "
81                      + "id INTEGER IDENTITY, "
82                      + "str_col VARCHAR(256), "
83                      + "num_col INTEGER"
84                      + ")");
85          } catch (SQLException ex) {
86              // second time we run program
87              //  should throw execption since table
88              // already there
89          }
90  
91          try {
92              // add some rows - will create duplicates if run more then once
93              // the id column is automatically generated
94              getQueryRunner().update("INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100)");
95              getQueryRunner().update("INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200)");
96              getQueryRunner().update("INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300)");
97              getQueryRunner().update("INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400)");
98  
99              getQueryRunner().update("insert into sample_table(str_col,num_col) values (?,?)",
100                     new Object[]{"Ferrari", 5000});
101 
102             // do a query
103             query("SELECT * FROM sample_table");
104 
105         } catch (SQLException ex) {
106             Log.error("DB errror", ex);
107         }
108     }
109 
110     @JMX
111     public void trunc() {
112         try {
113             getQueryRunner().update("delete from sample_table");
114         } catch (SQLException e) {
115             logError("Update error:", e);
116         }
117     }
118 
119     @JMX
120     public void checkpoint() {
121         try {
122             getQueryRunner().update("checkpoint");
123         } catch (SQLException e) {
124             logError("Update error:", e);
125         }
126     }
127 }