001    /*
002     @license.text@ 
003     */
004    package biz.hammurapi.sql;
005    
006    import java.sql.PreparedStatement;
007    import java.sql.ResultSet;
008    import java.sql.SQLException;
009    import java.util.ArrayList;
010    import java.util.Collection;
011    import java.util.Iterator;
012    
013    import javax.sql.DataSource;
014    
015    import biz.hammurapi.sql.hsqldb.HsqldbStandaloneDataSource;
016    
017    /**
018     * @author Pavel Vlasov
019     * @version $Revision: 1.6 $
020     */
021    public class Samples {
022    
023            public static void main(String[] args) throws Exception {
024                    sample1();
025            }
026    
027            private static final String CREATE_TABLE_SQL=
028                    "CREATE CACHED TABLE PERSON " +
029                    "(ID INTEGER IDENTITY NOT NULL PRIMARY KEY, " +
030                    "FIRST_NAME VARCHAR(30), LAST_NAME VARCHAR(30))";
031            
032            interface Person {
033                    String getFirstName();
034                    String getLastName();
035                    
036                    void setFirstName(String firstName);
037                    void setLastName(String lastName);
038            }
039    
040            private static void sample1() throws Exception {
041                    DataSource dataSource=new HsqldbStandaloneDataSource(
042                                    "People", 
043                                    new Transaction() {
044    
045                                            public boolean execute(SQLProcessor processor) throws SQLException {
046                                                    processor.processUpdate(CREATE_TABLE_SQL, null);
047                                                    return true;
048                                            }
049                                            
050                                    });
051                    
052                    SQLProcessor processor=new SQLProcessor(dataSource, null);
053    //              processor.setMeasurementConsumer(new SimpleMeasurementConsumer());              
054                    processor.processUpdate("INSERT INTO PERSON (FIRST_NAME, LAST_NAME) VALUES ('Pavel', 'Vlasov')", null);
055                    Iterator it= processor.project("SELECT * FROM PERSON", null, Person.class).iterator();
056                    while (it.hasNext()) {
057                            System.out.println(it.next());
058                    }
059                    
060                    sample2(processor);
061                    
062                    //System.out.println("SQL metrics: \n"+processor.getMeasurementConsumer());
063            }
064    
065            private static void sample2(SQLProcessor processor) throws SQLException {
066                    processor.processSelect("SELECT * FROM PERSON", null, new RowProcessor() {
067                            public boolean process(ResultSet rs) throws SQLException {
068                                    System.out.println(rs.getInt("ID")+" "+rs.getString("LAST_NAME"));
069                                    return true;
070                            }                       
071                    });
072            }
073            
074            private static String sample3(SQLProcessor processor) throws SQLException {
075                    final String[] ret={null};
076                    processor.processSelect(
077                            "SELECT * FROM PERSON WHERE LAST_NAME=?", 
078                            new Parameterizer() {
079                                    public int parameterize(PreparedStatement ps, int idx) throws SQLException {
080                                            ps.setString(idx++, "VLASOV");
081                                            return idx;
082                                    }
083                            }, 
084                            new RowProcessor() {
085                                    public boolean process(ResultSet rs) throws SQLException {
086                                            ret[0]=rs.getString("LAST_NAME");
087                                            return false;
088                                    }                       
089                    });
090                    return ret[0];
091            }
092            
093            private static void updateOrInsert(
094                            final SQLProcessor processor, 
095                            final String firstName, 
096                            final String lastName) 
097            throws SQLException {
098                    final Parameterizer parameterizer=new Parameterizer() {
099                            public int parameterize(PreparedStatement ps, int idx) throws SQLException {
100                                    ps.setString(idx++, firstName);
101                                    ps.setString(idx++, lastName);
102                                    return idx;
103                            }
104                    };
105                    
106                    processor.processSelect(
107                            "SELECT * FROM PERSON WHERE LAST_NAME=?", 
108                            new Parameterizer() {
109                                    public int parameterize(PreparedStatement ps, int idx) throws SQLException {
110                                            ps.setString(idx++, lastName);
111                                            return idx;
112                                    }
113                            }, 
114                            new RowProcessorEx() {
115                                    public boolean process(ResultSet rs) throws SQLException {
116                                            processor.processUpdate(
117                                                            "UPDATE PERSON SET FIRST_NAME=? WHERE LAST_NAME=?",
118                                                            parameterizer);                                 
119                                            return false;
120                                    }
121    
122                                    public void onEmptyResultSet() throws SQLException {
123                                            processor.processUpdate(
124                                                    "INSERT INTO PERSON (FIRST_NAME, LAST_NAME) VALUES (?, ?)",
125                                                    parameterizer);                                 
126                                    }                       
127                    });
128            }
129            
130            class Person2 {
131                    private String firstName;
132                    private String lastName;
133    
134                    public Person2(String firstName, String lastName) {
135                            this.firstName=firstName;
136                            this.lastName=lastName;
137                    }
138                    public String getFirstName() {
139                            return firstName;
140                    }
141                    public void setFirstName(String firstName) {
142                            this.firstName = firstName;
143                    }
144                    public String getLastName() {
145                            return lastName;
146                    }
147                    public void setLastName(String lastName) {
148                            this.lastName = lastName;
149                    }
150            }
151            
152            private static void projection(SQLProcessor processor, final int personId) throws Exception {
153                    // Default - database backed
154                    Collection c=processor.project("SELECT * FROM PERSON");
155                    
156                    // Constructor - torn off
157                    Projector projector=new ConstructorProjector(Person2.class.getConstructor(new Class[] {String.class, String.class}), null);
158                    c=processor.project("SELECT FIRST_NAME, LAST_NAME FROM PERSON", null, projector, new ArrayList());
159                    
160                    // Property - paged, database backed
161                    projector=new PropertyProjector(Person2.class, null, null);
162                    c=processor.project("SELECT FIRST_NAME, LAST_NAME FROM PERSON", null, projector);
163                    
164                    // Interface - paged, torn off
165                    c=processor.project("SELECT * FROM PERSON", null, Person.class, new ArrayList(), 3, 15);
166                    
167                    // Single object projection
168                    projector=new PropertyProjector(Person2.class, null, null);
169                    Person2 person2=(Person2) processor.projectSingleObject("SELECT * FROM PERSON WHERE ID=2", null, projector);            
170                    
171                    // Single interface projection
172                    Person person=(Person) processor.projectSingleObject(
173                                    "SELECT * FROM PERSON WHERE ID=?", 
174                                    new Parameterizer() {
175                                            public int parameterize(PreparedStatement ps, int idx) throws SQLException {
176                                                    ps.setInt(idx++, personId);
177                                                    return idx;
178                                            }
179                                    },
180                                    Person.class);
181            }       
182            
183            static class Person3 {
184                    private String firstName;
185                    private String lastName;
186    
187                    public String getFirstName() {
188                            return firstName;
189                    }
190                    public void setFirstName(String firstName) {
191                            this.firstName = firstName;
192                    }
193                    public String getLastName() {
194                            return lastName;
195                    }
196                    public void setLastName(String lastName) {
197                            this.lastName = lastName;
198                    }
199            }
200            
201            private static void injection(SQLProcessor processor, final int personId) throws Exception {
202                    Person3 person=new Person3();
203                    processor.inject("SELECT * FROM PERSON WHERE ID=2", null, null, person);                
204                    
205                    // Single interface projection
206                    processor.inject(
207                                    "SELECT * FROM PERSON WHERE ID=?", 
208                                    new Parameterizer() {
209                                            public int parameterize(PreparedStatement ps, int idx) throws SQLException {
210                                                    ps.setInt(idx++, personId);
211                                                    return idx;
212                                            }
213                                    },
214                                    null,
215                                    person);
216            }       
217    }