Loading...
Searching...
No Matches
Transport9.java
1package com.gams.examples.transport;
2
3import java.io.File;
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.ResultSet;
7import java.sql.ResultSetMetaData;
8import java.sql.SQLException;
9import java.sql.Statement;
10
11import com.gams.api.GAMSDatabase;
12import com.gams.api.GAMSGlobals;
13import com.gams.api.GAMSJob;
14import com.gams.api.GAMSOptions;
15import com.gams.api.GAMSParameter;
16import com.gams.api.GAMSSet;
17import com.gams.api.GAMSVariable;
18import com.gams.api.GAMSVariableRecord;
19import com.gams.api.GAMSWorkspace;
20import com.gams.api.GAMSWorkspaceInfo;
21
33public class Transport9 {
34
35 public static void main(String[] args) {
36 GAMSWorkspaceInfo wsInfo = new GAMSWorkspaceInfo();
37
38 if (args.length > 0) {
39 // check system directory info from the first command line argument
40 wsInfo.setSystemDirectory( args[0] );
41 }
42
43 File workingDirectory = new File(System.getProperty("user.dir"), "Transport9");
44 workingDirectory.mkdir();
45 wsInfo.setWorkingDirectory(workingDirectory.getAbsolutePath());
46
47 String strAccessConn = args[0] + GAMSGlobals.FILE_SEPARATOR + "apifiles"
48 + GAMSGlobals.FILE_SEPARATOR + "Data"
49 + GAMSGlobals.FILE_SEPARATOR + "transport.accdb";
50 GAMSWorkspace ws = new GAMSWorkspace(wsInfo);
51 GAMSDatabase db = readDataFromAccess(ws, strAccessConn);
52
53 GAMSOptions opt = ws.addOptions();
54 GAMSJob t9 = ws.addJobFromString( model );
55 opt.defines("gdxincname", db.getName());
56 opt.setAllModelTypes( "xpress" );
57 t9.run(opt, db);
58 for (GAMSVariableRecord rec : t9.OutDB().getVariable("x"))
59 System.out.println("x(" + rec.getKey(0) + "," + rec.getKey(1) + "): level=" + rec.getLevel() + " marginal=" + rec.getMarginal());
60
61 writeDataToAccess(ws, strAccessConn, t9.OutDB());
62 }
63
64 static GAMSDatabase readDataFromAccess(GAMSWorkspace ws, String location) {
65 GAMSDatabase db = ws.addDatabase();
66
67 try {
68 // loading the jdbc odbc driver
69 Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
70
71 // creating connection to database
72 String url = net.ucanaccess.jdbc.UcanaccessDriver.URL_PREFIX + location;
73 Connection c = DriverManager.getConnection(url, "", "");
74
75 // read GAMS sets
76 readSet(c, db, "SELECT Plant FROM Plant", "i", 1, "canning plants");
77 readSet(c, db, "SELECT Market FROM Market", "j", 1, "markets");
78
79 // read GAMS parameters
80 readParameter(c, db, "SELECT Plant, Capacity FROM Plant", "a", 1, "capacity of plant i in cases");
81 readParameter(c, db, "SELECT Market,Demand FROM Market", "b", 1, "demand at market j in cases");
82 readParameter(c, db, "SELECT Plant,Market,Distance FROM Distance", "d", 2, "distance in thousands of miles");
83
84 c.close();
85 } catch (ClassNotFoundException e) {
86 System.err.println("Error: Failed to find a driver for the database.");
87 e.printStackTrace();
88 System.exit(-1);
89 } catch (SQLException e) {
90 System.err.println("Error: Failed to retrieve data from the database.");
91 e.printStackTrace();
92 System.exit(-1);
93 }
94 return db;
95 }
96
97 static void readSet(Connection c, GAMSDatabase db, String queryString, String setName, int setDimension, String setExplanatoryText) throws SQLException {
98 Statement st = c.createStatement();
99
100 ResultSet rs = st.executeQuery(queryString);
101 ResultSetMetaData rsmd = rs.getMetaData();
102
103 if (rsmd.getColumnCount() != setDimension) {
104 System.err.println("Error: Number of fields in select statement does not match setDimemsion.");
105 c.close();
106 System.exit(-1);
107 }
108
109 GAMSSet set = db.addSet(setName, setDimension, setExplanatoryText);
110
111 String[] keys = new String[setDimension];
112
113 while (rs.next()) {
114 for (int idx=0; idx < setDimension; idx++)
115 keys[idx] = rs.getString(idx+1);
116 set.addRecord( keys );
117 }
118 st.close();
119 }
120
121 static void readParameter(Connection c, GAMSDatabase db, String queryString, String parName, int parDimension, String parExplanatoryText) throws SQLException {
122 Statement st = c.createStatement();
123
124 ResultSet rs = st.executeQuery(queryString);
125 ResultSetMetaData rsmd = rs.getMetaData();
126
127 int numberOfColumns = rsmd.getColumnCount();
128 if (numberOfColumns != (parDimension+1)) {
129 System.err.println("Error: Number of fields in select statement does not match parDimension.");
130 c.close();
131 System.exit(-1);
132 }
133
134 GAMSParameter parameter = db.addParameter(parName, parDimension, parExplanatoryText);
135
136 String[] keys = new String[parDimension];
137
138 while (rs.next()) {
139 for (int idx=0; idx < parDimension; idx++)
140 keys[idx] = rs.getString(idx+1);
141 parameter.addRecord( keys ).setValue( Double.valueOf(rs.getString(numberOfColumns)) );
142 }
143 st.close();
144 }
145
146 static void writeVariable(Connection c, GAMSDatabase db, String varName, String ... domains) throws SQLException {
147
148 GAMSVariable var = db.getVariable(varName);
149 if ( domains.length != var.getDimension() ) {
150 System.err.println("Error: Number of column names does not match the dimension of the variable.");
151 c.close();
152 System.exit(-1);
153 }
154
155 Statement st = c.createStatement();
156
157 String sql = "create table " + varName + "(";
158 for (String dom : domains)
159 sql += dom + " varchar(64), ";
160 sql += "lvl double)";
161
162 st.executeUpdate(sql);
163
164 for (GAMSVariableRecord rec : var) {
165 sql = "insert into " + varName + "(";
166 for (String dom : domains)
167 sql += dom + ", ";
168 sql += "lvl) values (";
169 for (String key : rec.getKeys())
170 sql += "'" + key + "', ";
171 sql += rec.getLevel() + ")";
172
173 st.executeUpdate(sql);
174 }
175 st.close();
176 }
177
178 static void writeDataToAccess(GAMSWorkspace ws, String location, GAMSDatabase db) {
179 try {
180 // loading the jdbc odbc driver
181 Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
182
183 // creating connection to database
184 String url = net.ucanaccess.jdbc.UcanaccessDriver.URL_PREFIX + location;
185 Connection c = DriverManager.getConnection(url,"","");
186
187 // write levels of variable x
188 writeVariable(c, db, "x", "i", "j");
189
190 c.close();
191 } catch (ClassNotFoundException e) {
192 System.err.println("Error: Failed to find a driver for the database.");
193 e.printStackTrace();
194 System.exit(-1);
195 } catch (SQLException e) {
196 System.err.println("Error: Failed to write data back to the database.");
197 e.printStackTrace();
198 System.exit(-1);
199 }
200 }
201
202 static String model =
203 " Sets \n"+
204 " i canning plants \n"+
205 " j markets \n"+
206 " \n"+
207 " Parameters \n"+
208 " a(i) capacity of plant i in cases \n"+
209 " b(j) demand at market j in cases \n"+
210 " d(i,j) distance in thousands of miles \n"+
211 " Scalar f freight in dollars per case per thousand miles /90/; \n"+
212 " \n"+
213 "$if not set gdxincname $abort 'no include file name for data file provided' \n"+
214 "$gdxin %gdxincname% \n"+
215 "$load i j a b d \n"+
216 "$gdxin \n"+
217 " \n"+
218 " Parameter c(i,j) transport cost in thousands of dollars per case ; \n"+
219 " \n"+
220 " c(i,j) = f * d(i,j) / 1000 ; \n"+
221 " \n"+
222 " Variables \n"+
223 " x(i,j) shipment quantities in cases \n"+
224 " z total transportation costs in thousands of dollars ; \n"+
225 " \n"+
226 " Positive Variable x ; \n"+
227 " \n"+
228 " Equations \n"+
229 " cost define objective function \n"+
230 " supply(i) observe supply limit at plant i \n"+
231 " demand(j) satisfy demand at market j ; \n"+
232 " \n"+
233 " cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ; \n"+
234 " \n"+
235 " supply(i) .. sum(j, x(i,j)) =l= a(i) ; \n"+
236 " \n"+
237 " demand(j) .. sum(i, x(i,j)) =g= b(j) ; \n"+
238 " \n"+
239 " Model transport /all/ ; \n"+
240 " \n"+
241 " Solve transport using lp minimizing z ; \n"+
242 " \n"+
243 " Display x.l, x.m ; \n"+
244 " \n";
245}
com::gams::api::GAMSDatabase::addSet
GAMSSet addSet(String identifier, int dimension)
com::gams::api::GAMSDatabase::addParameter
GAMSParameter addParameter(String identifier, int dimension)
com::gams::api::GAMSDatabase::getVariable
GAMSVariable getVariable(String identifier)
com::gams::api::GAMSJob::OutDB
GAMSDatabase OutDB()
com::gams::api::GAMSWorkspaceInfo::setSystemDirectory
void setSystemDirectory(String directory)
com::gams::api::GAMSWorkspaceInfo::setWorkingDirectory
void setWorkingDirectory(String directory)
com::gams::api::GAMSWorkspace::addJobFromString
GAMSJob addJobFromString(String source)
com::gams::examples.transport.Transport9
This example demonstrates how to retrieve an input for GAMS Transport Model from a MSAccess file (tra...
Definition Transport9.java:33