1
2
3 package oscon2006.web;
4
5 import java.util.*;
6 import java.io.*;
7 import java.sql.*;
8 import org.apache.derby.jdbc.EmbeddedDriver;
9 import oscon2006.common.XlsBuilder;
10 import oscon2006.common.CsvBuilder;
11
12 public class DAO
13 {
14 static
15 {
16 try
17 {
18 DriverManager.registerDriver(new EmbeddedDriver());
19 }
20 catch (SQLException ex)
21 {
22 ex.printStackTrace();
23 }
24 }
25
26 public byte[] getEmployees(OutputFormat format)
27 {
28 Connection conn = null;
29 Statement st = null;
30 ResultSet rs = null;
31 byte[] result = null;
32
33 try
34 {
35 conn = getConnection();
36 st = conn.createStatement();
37
38 String sql = "SELECT LASTNAME, FIRSTNAME, JOBTITLE, EMAIL FROM EMPLOYEES";
39
40 rs = st.executeQuery(sql);
41
42 switch (format)
43 {
44 case EXCEL:
45 XlsBuilder xlsBuilder = new XlsBuilder();
46 xlsBuilder.setData(rs);
47 xlsBuilder.setColumnWidths(20, 15, 20, 30);
48 result = xlsBuilder.build();
49 break;
50 case CSV:
51 CsvBuilder builder = new CsvBuilder();
52 builder.setData(rs);
53 result = builder.build();
54 break;
55 default:
56 throw new RuntimeException("unknown output format: " + format);
57 }
58 return result;
59 }
60 catch (Exception ex)
61 {
62 throw new RuntimeException(ex);
63 }
64 finally
65 {
66 close(rs);
67 close(st);
68 close(conn);
69 }
70
71 }
72
73 public byte[] getProducts(OutputFormat format)
74 {
75 Connection conn = null;
76 Statement st = null;
77 ResultSet rs = null;
78 byte[] result = null;
79
80 try
81 {
82 conn = getConnection();
83 st = conn.createStatement();
84
85 String sql = "SELECT PRODUCTNAME, PRODUCTLINE, PRODUCTDESCRIPTION, QUANTITYINSTOCK AS QUANTITY, BUYPRICE AS PRICE FROM PRODUCTS";
86
87 rs = st.executeQuery(sql);
88
89 switch (format)
90 {
91 case EXCEL:
92 XlsBuilder xlsBuilder = new XlsBuilder();
93 xlsBuilder.setData(rs);
94 xlsBuilder.setColumnWidths(30, 18, 60, 10);
95 result = xlsBuilder.build();
96 break;
97 case CSV:
98 CsvBuilder builder = new CsvBuilder();
99 builder.setData(rs);
100 result = builder.build();
101 break;
102 default:
103 throw new RuntimeException("unknown output format: " + format);
104 }
105
106 return result;
107 }
108 catch (Exception ex)
109 {
110 throw new RuntimeException(ex);
111 }
112 finally
113 {
114 close(rs);
115 close(st);
116 close(conn);
117 }
118
119 }
120
121 protected void close(Connection c)
122 {
123 try
124 {
125 c.close();
126 }
127 catch (Exception ex)
128 {
129
130 }
131 }
132
133 protected void close(ResultSet rs)
134 {
135 try
136 {
137 rs.close();
138 }
139 catch (Exception ex)
140 {
141
142 }
143 }
144
145 protected void close(Statement st)
146 {
147 try
148 {
149 st.close();
150 }
151 catch (Exception ex)
152 {
153
154 }
155 }
156
157 protected Connection getConnection() throws SQLException
158 {
159 return DriverManager.getConnection("jdbc:derby:classpath:SampleDatabase");
160 }
161
162 public static void main(String[] args)
163 {
164 DAO d = new DAO();
165
166 byte[] excelData = null;
167
168 excelData = d.getEmployees(OutputFormat.EXCEL);
169
170 excelData = d.getProducts(OutputFormat.EXCEL);
171
172 }
173 }
174