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 }