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