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.hypersonic.HypersonicStandaloneDataSource;
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 HypersonicStandaloneDataSource(
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 void parameterize(PreparedStatement ps) throws SQLException {
080                                            ps.setString(1, "VLASOV");
081                                    }
082                            }, 
083                            new RowProcessor() {
084                                    public boolean process(ResultSet rs) throws SQLException {
085                                            ret[0]=rs.getString("LAST_NAME");
086                                            return false;
087                                    }                       
088                    });
089                    return ret[0];
090            }
091            
092            private static void updateOrInsert(
093                            final SQLProcessor processor, 
094                            final String firstName, 
095                            final String lastName) 
096            throws SQLException {
097                    final Parameterizer parameterizer=new Parameterizer() {
098                            public void parameterize(PreparedStatement ps) throws SQLException {
099                                    ps.setString(1, firstName);
100                                    ps.setString(2, lastName);
101                            }
102                    };
103                    
104                    processor.processSelect(
105                            "SELECT * FROM PERSON WHERE LAST_NAME=?", 
106                            new Parameterizer() {
107                                    public void parameterize(PreparedStatement ps) throws SQLException {
108                                            ps.setString(1, lastName);
109                                    }
110                            }, 
111                            new RowProcessorEx() {
112                                    public boolean process(ResultSet rs) throws SQLException {
113                                            processor.processUpdate(
114                                                            "UPDATE PERSON SET FIRST_NAME=? WHERE LAST_NAME=?",
115                                                            parameterizer);                                 
116                                            return false;
117                                    }
118    
119                                    public void onEmptyResultSet() throws SQLException {
120                                            processor.processUpdate(
121                                                    "INSERT INTO PERSON (FIRST_NAME, LAST_NAME) VALUES (?, ?)",
122                                                    parameterizer);                                 
123                                    }                       
124                    });
125            }
126            
127            class Person2 {
128                    private String firstName;
129                    private String lastName;
130    
131                    public Person2(String firstName, String lastName) {
132                            this.firstName=firstName;
133                            this.lastName=lastName;
134                    }
135                    public String getFirstName() {
136                            return firstName;
137                    }
138                    public void setFirstName(String firstName) {
139                            this.firstName = firstName;
140                    }
141                    public String getLastName() {
142                            return lastName;
143                    }
144                    public void setLastName(String lastName) {
145                            this.lastName = lastName;
146                    }
147            }
148            
149            private static void projection(SQLProcessor processor, final int personId) throws Exception {
150                    // Default - database backed
151                    Collection c=processor.project("SELECT * FROM PERSON");
152                    
153                    // Constructor - torn off
154                    Projector projector=new ConstructorProjector(Person2.class.getConstructor(new Class[] {String.class, String.class}), null);
155                    c=processor.project("SELECT FIRST_NAME, LAST_NAME FROM PERSON", null, projector, new ArrayList());
156                    
157                    // Property - paged, database backed
158                    projector=new PropertyProjector(Person2.class, null, null);
159                    c=processor.project("SELECT FIRST_NAME, LAST_NAME FROM PERSON", null, projector);
160                    
161                    // Interface - paged, torn off
162                    c=processor.project("SELECT * FROM PERSON", null, Person.class, new ArrayList(), 3, 15);
163                    
164                    // Single object projection
165                    projector=new PropertyProjector(Person2.class, null, null);
166                    Person2 person2=(Person2) processor.projectSingleObject("SELECT * FROM PERSON WHERE ID=2", null, projector);            
167                    
168                    // Single interface projection
169                    Person person=(Person) processor.projectSingleObject(
170                                    "SELECT * FROM PERSON WHERE ID=?", 
171                                    new Parameterizer() {
172                                            public void parameterize(PreparedStatement ps) throws SQLException {
173                                                    ps.setInt(1, personId);
174                                            }
175                                    },
176                                    Person.class);
177            }       
178            
179            static class Person3 {
180                    private String firstName;
181                    private String lastName;
182    
183                    public String getFirstName() {
184                            return firstName;
185                    }
186                    public void setFirstName(String firstName) {
187                            this.firstName = firstName;
188                    }
189                    public String getLastName() {
190                            return lastName;
191                    }
192                    public void setLastName(String lastName) {
193                            this.lastName = lastName;
194                    }
195            }
196            
197            private static void injection(SQLProcessor processor, final int personId) throws Exception {
198                    Person3 person=new Person3();
199                    processor.inject("SELECT * FROM PERSON WHERE ID=2", null, null, person);                
200                    
201                    // Single interface projection
202                    processor.inject(
203                                    "SELECT * FROM PERSON WHERE ID=?", 
204                                    new Parameterizer() {
205                                            public void parameterize(PreparedStatement ps) throws SQLException {
206                                                    ps.setInt(1, personId);
207                                            }
208                                    },
209                                    null,
210                                    person);
211            }       
212    }