1 package ch.ige.edossier.transfer.server.dao;
2
3 import java.io.IOException;
4 import java.sql.*;
5 import java.util.ArrayList;
6 import java.util.List;
7 import ch.ige.edossier.transfer.vo.PrioritaetVO;
8 import ch.ige.edossier.transfer.vo.RegAdrVO;
9 import ch.ige.edossier.transfer.vo.SchutitVO;
10 import ch.ige.edossier.util.DBHelper;
11 import ch.ige.edossier.web.vo.ImageVO;
12
13
14
22 public class SchutitDAO
23 {
24
25
32 public SchutitVO select( int schutitKey ) throws SQLException
33 {
34 Connection con = null;
35 PreparedStatement ps = null;
36 ResultSet rs = null;
37 SchutitVO vo = null;
38
39 try
40 {
41 con = DBHelper.getInstance().getConnection();
42 ps = con.prepareStatement(
43 "SELECT a.schutit_key, " +
44 "a.sprache, " +
45 "a.gesuch_nr, " +
46 "a.schutit_nr, " +
47 "a.schutz_beginn, " +
48 "a.beschreibung, " +
49 "a.ihr_zeichen, " +
50 "b.mar_typ " +
51 "FROM schutit a, mar b " +
52 "WHERE a.schutit_key = ? AND a.schutit_key = b.schutit_key" );
53 ps.setInt( 1, schutitKey );
54 rs = ps.executeQuery();
55
56 if( rs.next() )
57 {
58 vo = new SchutitVO();
59
60 vo.schutitKey = rs.getInt( "schutit_key" );
61 vo.sprache = rs.getInt( "sprache" );
62 vo.gesuchNr = rs.getString( "gesuch_nr" );
63 vo.schutitNr = rs.getString( "schutit_nr" );
64 vo.schutzBegin = rs.getDate( "schutz_beginn" );
65 if( vo.schutzBegin == null )
66 {
67 vo.schutzBegin = null;
68 }
69 vo.beschreibung = rs.getString( "beschreibung" );
70 vo.ihrZeichen = rs.getString( "ihr_zeichen" );
71 vo.marTyp = rs.getInt( "mar_typ" );
72 }
73 return vo;
74 }
75 finally
76 {
77 DBHelper.getInstance().close( con, ps );
78 }
79 }
80
81
86 public ImageVO selectImage( int schutitKey ) throws SQLException, IOException
87 {
88 Connection con = null;
89 PreparedStatement ps = null;
90 ResultSet rs = null;
91 ImageVO img = null;
92
93 try
94 {
95 con = DBHelper.getInstance().getConnection();
96 ps = con.prepareStatement
97 ( "SELECT image FROM image " +
98 "WHERE schutit_key = ? AND bis_ereig_idat = 0" );
99 ps.setInt( 1, schutitKey );
100 rs = ps.executeQuery();
101
102 if( rs.next() )
103 {
104 img = new ImageVO();
105
106 byte[] bArray = rs.getBytes( "image" );
107 img.setImage( bArray );
108 }
109 return img;
110 }
111 finally
112 {
113 DBHelper.getInstance().close( con, ps );
114 }
115 }
116
117
122 public String selectColorClaim( int schutitKey ) throws SQLException, IOException
123 {
124 Connection con = null;
125 PreparedStatement ps = null;
126 ResultSet rs = null;
127 String farbAnspr = null;
128
129 try
130 {
131 con = DBHelper.getInstance().getConnection();
132 ps = con.prepareStatement
133 ( "SELECT farb_anspr, farb_anspr_long FROM marvers " +
134 "WHERE schutit_key = ? " );
135 ps.setInt( 1, schutitKey );
136 rs = ps.executeQuery();
137
138 if( rs.next() )
139 {
140 farbAnspr = rs.getString( "farb_anspr" );
141 if( farbAnspr == null )
142 {
143 farbAnspr = rs.getString( "farb_anspr_long" );
144 }
145 }
146 return farbAnspr;
147 }
148 finally
149 {
150 DBHelper.getInstance().close( con, ps );
151 }
152 }
153
154
160 public List selectRegAdrSchutit( int schutitKey ) throws SQLException
161 {
162 String query = null;
163 Connection con = null;
164 PreparedStatement ps = null;
165 ResultSet rs = null;
166 List list = new ArrayList();
167
168 try
169 {
170 query = "SELECT a.regadr_nr, a.bland_cd, a.adr_nr, a.reg_name, " +
171 "a.reg_strasse, a.reg_ort, b.adr_rolle_cd " +
172 "FROM regadr a, schutit_regadr b " +
173 "WHERE b.schutit_key=? AND b.regadr_nr = a.regadr_nr AND b.adr_rolle_cd IN (1,2,3)";
174
175 con = DBHelper.getInstance().getConnection();
176 ps = con.prepareStatement( query );
177 ps.setInt( 1, schutitKey );
178 rs = ps.executeQuery();
179
180 while( rs.next() )
181 {
182 RegAdrVO regAdrVO = new RegAdrVO();
183 regAdrVO.regadrNr = rs.getInt( "regadr_nr" );
184 regAdrVO.blandCd = rs.getString( "bland_cd" );
185 regAdrVO.adrNr = rs.getInt( "adr_nr" );
186 regAdrVO.regName = rs.getString( "reg_name" );
187 regAdrVO.regStrasse = rs.getString( "reg_strasse" );
188 regAdrVO.regOrt = rs.getString( "reg_ort" );
189 regAdrVO.regAdrRolle = rs.getInt( "adr_rolle_cd" );
190 list.add( regAdrVO );
191 }
192 }
193 finally
194 {
195 DBHelper.getInstance().close( con, ps );
196 }
197 return list;
198 }
199
200
206 public List selectPrioritaet( int schutitKey ) throws SQLException
207 {
208 String query = null;
209 Connection con = null;
210 PreparedStatement ps = null;
211 ResultSet rs = null;
212 List list = new ArrayList();
213
214 try
215 {
216 query = "SELECT prio_lnr, bland_cd, prio_dat " +
217 "FROM prioritaet " +
218 "WHERE schutit_key = ?";
219
220 con = DBHelper.getInstance().getConnection();
221 ps = con.prepareStatement( query );
222 ps.setInt( 1, schutitKey );
223 rs = ps.executeQuery();
224
225 while( rs.next() )
226 {
227 PrioritaetVO prioritaetVO = new PrioritaetVO();
228 prioritaetVO.prioLnr = rs.getInt( "prio_lnr" );
229 prioritaetVO.blandCd = rs.getString( "bland_cd" );
230 prioritaetVO.prioDat = rs.getDate( "prio_dat" );
231 list.add( prioritaetVO );
232 }
233 }
234 finally
235 {
236 DBHelper.getInstance().close( con, ps );
237 }
238 return list;
239 }
240
241
247 public List selectMarart( int schutitKey ) throws SQLException
248 {
249 String query = null;
250 Connection con = null;
251 PreparedStatement ps = null;
252 ResultSet rs = null;
253 List alMarart = new ArrayList();
254
255 try
256 {
257 query = "SELECT marart_nr " +
258 "FROM mar_marart " +
259 "WHERE schutit_key = ? AND marart_bis_ereig = 0";
260
261 con = DBHelper.getInstance().getConnection();
262 ps = con.prepareStatement( query );
263 ps.setInt( 1, schutitKey );
264 rs = ps.executeQuery();
265
266 while( rs.next() )
267 {
268 Integer marart = new Integer( rs.getInt( "marart_nr" ) );
269 alMarart.add( marart );
270 }
271 }
272 finally
273 {
274 DBHelper.getInstance().close( con, ps );
275 }
276 return alMarart;
277 }
278 }
279