001 package com.blogspot.pilloledijava.derby;
002
003 import java.io.File;
004 import java.math.BigDecimal;
005 import java.sql.Connection;
006 import java.sql.DriverManager;
007 import java.sql.PreparedStatement;
008 import java.sql.ResultSet;
009 import java.sql.SQLException;
010 import java.sql.Statement;
011
012 public class DerbyExample {
013
014 private static ExampleDbManager dbManager;
015
016 /**
017 * @param args
018 */
019 public static void main(String[] args) {
020 try {
021 dbManager = new ExampleDbManager(new File("data/example"));
022 dbManager.createTable();
023 dbManager.insertSample(100);
024 final String all = dbManager.getCheapest();
025 System.out.println("Cheapest item description: " + all);
026 final int count = dbManager.countCheaperThen(BigDecimal
027 .valueOf(5.0));
028 System.out.println("Cheap item: " + count);
029
030 System.out.println();
031 System.out
032 .println("Apache Derby example: http://pilloledijava.blogspot.com/");
033 System.out.println("Enjoy !!!!");
034
035 } catch (Exception e) {
036 e.printStackTrace();
037 System.out
038 .println("Apache Derby example: http://pilloledijava.blogspot.com/");
039 System.out.println("Something wrong, sorry...");
040 } finally {
041 try {
042 if (dbManager != null)
043 dbManager.close();
044 } catch (SQLException e) {
045 // nothing in finally statement
046 }
047 }
048
049 }
050
051 /**
052 * Derby connection wrapper
053 *
054 * @author pdj
055 *
056 */
057 public static class DbManager {
058
059 protected final Connection connection;
060
061 /**
062 * Embedded derby connection wrapper
063 *
064 * @param path
065 * database location
066 * @throws ClassNotFoundException
067 * @throws SQLException
068 */
069 public DbManager(final File path) throws ClassNotFoundException,
070 SQLException {
071 // load Derby driver
072 Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
073 // instantiate derby connection, if necessary creates database
074 connection = DriverManager.getConnection("jdbc:derby:"
075 + path.getAbsolutePath() + ";create=true");
076 }
077
078 /**
079 * Close connection
080 *
081 * @throws SQLException
082 */
083 public void close() throws SQLException {
084 connection.close();
085 }
086
087 }
088
089 /**
090 * Example query
091 *
092 * @author pdj
093 *
094 */
095 public static class ExampleDbManager extends DbManager {
096
097 public ExampleDbManager(final File path) throws ClassNotFoundException,
098 SQLException {
099 super(path);
100 }
101
102 /**
103 * Create item table
104 *
105 * run one time!
106 *
107 * @throws SQLException
108 */
109 public void createTable() throws SQLException {
110 Statement createStatement = null;
111 try {
112 createStatement = connection.createStatement();
113 createStatement
114 .execute("create table item(id integer primary key generated always as identity, description varchar(50) not null, price numeric(10,2)) ");
115 } finally {
116 if (createStatement != null)
117 createStatement.close();
118 }
119 }
120
121 /**
122 * Insert a lot of dummy item
123 *
124 * @param count
125 * @throws SQLException
126 */
127 public void insertSample(final int count) throws SQLException {
128 PreparedStatement insertStatement = null;
129 try {
130 insertStatement = connection
131 .prepareStatement("insert into item (description, price) values(?, ?)");
132 for (int i = 0; i < 100; i++) {
133 final String randomName = "item_" + Math.random();
134 insertStatement.setString(1, randomName);
135 final BigDecimal randomPrice = BigDecimal.valueOf(
136 (long) (Math.random() * 1000), 2);
137 insertStatement.setBigDecimal(2, randomPrice);
138 insertStatement.execute();
139 }
140 } finally {
141 if (insertStatement != null)
142 insertStatement.close();
143 }
144 }
145
146 /**
147 * Return the description of the cheapest item
148 *
149 * @return
150 * @throws SQLException
151 */
152 public String getCheapest() throws SQLException {
153 Statement createStatement = null;
154 try {
155 createStatement = connection.createStatement();
156 final ResultSet rs = createStatement
157 .executeQuery("SELECT description FROM item where price = (select min(price) from item)");
158 try {
159 return rs.next() ? rs.getString("description") : null;
160 } finally {
161 if (rs != null)
162 rs.close();
163 }
164 } finally {
165 if (createStatement != null)
166 createStatement.close();
167 }
168 }
169
170 /**
171 * Count item cheaper the threshold
172 *
173 * @param threshold
174 * @return
175 * @throws SQLException
176 */
177 public int countCheaperThen(final BigDecimal threshold)
178 throws SQLException {
179 PreparedStatement createStatement = null;
180 try {
181 createStatement = connection
182 .prepareStatement("select count(*) as c from item where price <= ?");
183 createStatement.setBigDecimal(1, threshold);
184 final ResultSet rs = createStatement.executeQuery();
185 try {
186 return rs.next() ? rs.getInt("c") : null;
187 } finally {
188 if (rs != null)
189 rs.close();
190 }
191 } finally {
192 if (createStatement != null)
193 createStatement.close();
194 }
195 }
196
197 }
198
199 }
|
Nessun commento:
Posta un commento