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 }