1package com.gams.examples.transport;
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.ResultSet;
7import java.sql.ResultSetMetaData;
8import java.sql.SQLException;
9import java.sql.Statement;
27 public static void main(String[] args) {
32 File workingDirectory =
new File(System.getProperty(
"user.dir"),
"Transport9");
33 workingDirectory.mkdir();
45 System.out.println(
"x(" + rec.getKey(0) +
"," + rec.getKey(1) +
"): level=" + rec.getLevel() +
" marginal=" + rec.getMarginal());
47 writeDataToAccess(ws, t9.
OutDB());
55 Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
58 Connection c = DriverManager.getConnection(
"jdbc:odbc:transportdsn",
"",
"");
61 readSet(c, db,
"SELECT Plant FROM Plant",
"i", 1,
"canning plants");
62 readSet(c, db,
"SELECT Market FROM Market",
"j", 1,
"markets");
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");
70 }
catch (ClassNotFoundException e) {
71 System.err.println(
"Error: Failed to find a driver for the database.");
74 }
catch (SQLException e) {
75 System.err.println(
"Error: Failed to retrieve data from the database.");
82 static void readSet(Connection c,
GAMSDatabase db, String queryString, String setName,
int setDimension, String setExplanatoryText)
throws SQLException {
83 Statement st = c.createStatement();
85 ResultSet rs = st.executeQuery(queryString);
86 ResultSetMetaData rsmd = rs.getMetaData();
88 if (rsmd.getColumnCount() != setDimension) {
89 System.err.println(
"Error: Number of fields in select statement does not match setDimemsion.");
94 GAMSSet set = db.
addSet(setName, setDimension, setExplanatoryText);
96 String[] keys =
new String[setDimension];
99 for (
int idx=0; idx < setDimension; idx++)
100 keys[idx] = rs.getString(idx+1);
106 static void readParameter(Connection c,
GAMSDatabase db, String queryString, String parName,
int parDimension, String parExplanatoryText)
throws SQLException {
107 Statement st = c.createStatement();
109 ResultSet rs = st.executeQuery(queryString);
110 ResultSetMetaData rsmd = rs.getMetaData();
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.");
121 String[] keys =
new String[parDimension];
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)) );
131 static void writeVariable(Connection c,
GAMSDatabase db, String varName, String ... domains)
throws SQLException {
135 System.err.println(
"Error: Number of column names does not match the dimension of the variable.");
140 Statement st = c.createStatement();
142 String sql =
"create table " + varName +
"(";
143 for (String dom : domains)
144 sql += dom +
" varchar(64), ";
145 sql +=
"lvl double)";
147 st.executeUpdate(sql);
150 sql =
"insert into " + varName +
"(";
151 for (String dom : domains)
153 sql +=
"lvl) values (";
154 for (String key : rec.getKeys())
155 sql +=
"'" + key +
"', ";
156 sql += rec.getLevel() +
")";
158 st.executeUpdate(sql);
166 Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
169 Connection c = DriverManager.getConnection(
"jdbc:odbc:transportdsn",
"",
"");
172 writeVariable(c, db,
"x",
"i",
"j");
175 }
catch (ClassNotFoundException e) {
176 System.err.println(
"Error: Failed to find a driver for the database.");
179 }
catch (SQLException e) {
180 System.err.println(
"Error: Failed to write data back to the database.");
186 static String model =
188 " i canning plants \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"+
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"+
202 " Parameter c(i,j) transport cost in thousands of dollars per case ; \n"+
204 " c(i,j) = f * d(i,j) / 1000 ; \n"+
207 " x(i,j) shipment quantities in cases \n"+
208 " z total transportation costs in thousands of dollars ; \n"+
210 " Positive Variable x ; \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"+
217 " cost .. z =e= sum((i,j), c(i,j)*x(i,j)) ; \n"+
219 " supply(i) .. sum(j, x(i,j)) =l= a(i) ; \n"+
221 " demand(j) .. sum(i, x(i,j)) =g= b(j) ; \n"+
223 " Model transport /all/ ; \n"+
225 " Solve transport using lp minimizing z ; \n"+
227 " Display x.l, x.m ; \n"+
GAMSSet addSet(String identifier, int dimension)
GAMSParameter addParameter(String identifier, int dimension)
GAMSVariable getVariable(String identifier)
void defines(String defStr, String asStr)
void setAllModelTypes(String value)
void setValue(double value)
T addRecord(Vector< String > keys)
void setSystemDirectory(String directory)
void setWorkingDirectory(String directory)
GAMSJob addJobFromString(String source)
GAMSDatabase addDatabase()
This example demonstrates how to retrieve an input for GAMS Transport Model from a MSAccess file (tra...
Provides package namespace for Java interface and examples to General Algebraic Model System (GAMS).