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 }