2016年1月26日火曜日

LocalSolver(2) : データベースの連携

LocalSolverとデータベースの連携をします。
LocalSolverではKnapsack問題を

8 : 拠点数
10 60 30 40 30 20 20 2 : weights
1 10 15 40 60 90 100 15 : values
102 : 制約条件

のようにファイルを取り込んでやっていますが、これを外部のデータベースに接続して持ってきます。
Exampleにあるknapsack.javaをknapsack01.javaとしてコピーし、コードを改良します。
$ /opt/localsolver_5_5/examples/knapsack
$ vi Knapsack01.java
$ sudo javac Knapsack01.java -cp /opt/localsolver_5_5/bin/localsolver.jar
sudo java -cp /Users/mizuno/Desktop/work/mysql-connector-java-5.1.21-bin.jar:/opt/localsolver_5_5/bin/localsolver.jar:. -Djava.library.path=/opt/localsolver_5_5/bin/ Knapsack01
[出力結果]
90 100 120 80 100 150 100 60 100 30 100 120 100 200 120 100 120 100 80 100 50 100 90 100 130 100 140 100 90 100 100 70 100 170 100 20 100 50 100 80 100 140
10 6 8 2 8 4 10 7 5 10 9 4 8 7 7 9 10 10 5 6 8 7 1 3 7 2 10 9 4 2 10 8 5 2 10 7 6 3 4 8 2 10
nbItems=42
knapsackBound=2000
Preprocess model 100% ...
Close model 100% ...
Initialize threads 100% ...
Push initial solutions 100% ...

Model:
  expressions = 154, operands = 254
  decisions = 42 (bool = 42, int = 0, float = 0),
  constraints = 1, objectives = 1, constants  = 25
  Preprocessing transformed 44 expressions

Param:
  time limit = 10 sec, no iteration limit
  seed = 0, nb threads = 2, annealing level = 1

Objectives:
  Obj 0: maximize, bound = 273

Phases:
  Phase 0: time limit = 10 sec, no iteration limit, optimized objective = 0


Phase 0:
[  0 sec,       0 itr] : obj =          191
[  1 sec,   82847 itr] : obj =          191
[  2 sec,  262753 itr] : obj =          191
[  3 sec,  444421 itr] : obj =          191
[  4 sec,  623025 itr] : obj =          191
[  5 sec,  741841 itr] : obj =          191
[  6 sec,  921965 itr] : obj =          191
[  7 sec, 1102912 itr] : obj =          191
[  8 sec, 1284998 itr] : obj =          191
[  9 sec, 1466748 itr] : obj =          191
[ 10 sec, 1587356 itr] : obj =          191
[ 10 sec, 1587356 itr] : obj =          191

1587356 iterations, 3174586 moves performed in 10 seconds
Feasible solution: obj =          191

[出力ファイル:result]
$ cat result
0 2 4 6 7 9 10 12 15 16 17 18 20 21 26 27 30 31 34 35 39 41 (拠点の配列インデックス)

[knapsack01.java]



/********** Knapsack01.java **********/
import java.sql.*;
import java.util.*;
import java.io.*;
import localsolver.*;

public class Knapsack01 {
    /* Number of items. */
    int nbItems;

    /* Items properties. */
    int[] weights;
    int[] values;

    public int[] getValues(){ return this.values;}
    public int[] getWeights(){ return this.weights;}

    /* Knapsack bound */
    int knapsackBound;

    /* Solver. */
    LocalSolver localsolver;

    /* LS Program variables. */
    LSExpression[] x;

    /* Solutions (classes at each position). */
    List solutions;

    public Knapsack01(int nbItems, int knapsackBound){
        MySQL mysql = new MySQL();
        ResultSet rs = mysql.selectFacilities();
        this.nbItems = nbItems;
        weights = new int[nbItems];
        values = new int[nbItems];
        this.knapsackBound = knapsackBound;
        int index = 0;
        try {
                while(rs.next()){
                        values[index] = rs.getInt("popularity");
                        weights[index] = rs.getInt("cost");
                        index++;
                }
        }catch (SQLException e) {
                        e.printStackTrace();
        }


    }


    /* Reads instance data. */
    void readInstance(String fileName) {
        try {
            Scanner input = new Scanner(new File(fileName));

            nbItems = input.nextInt();

            weights = new int[nbItems];
            for (int i = 0; i < nbItems; i++) {
                weights[i] = input.nextInt();
            }

            values = new int[nbItems];
            for (int i = 0; i < nbItems; i++) {
                values[i] = input.nextInt();
            }

            knapsackBound = input.nextInt();

            input.close();

        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    void solve(int limit) {
        try {
            /* Declares the optimization model. */
            localsolver = new LocalSolver();
            LSModel model = localsolver.getModel();
            x = new LSExpression[nbItems];

            System.out.println("nbItems="+nbItems);
            System.out.println("knapsackBound="+knapsackBound);

            // boolean variables x[i]
            for (int i = 0; i < nbItems; i++) {
                x[i] = model.boolVar();
                x[i].setName("x[" + i + "]");
            }

            // weight constraint
            LSExpression weightSum = model.sum();
            for (int i = 0; i < nbItems; i++) {
                LSExpression itemWeight = model.prod(x[i],weights[i]);
                weightSum.addOperand(itemWeight);
            }
            model.constraint(model.leq(weightSum,knapsackBound));

            // maximize value
            LSExpression valueSum = model.sum();
            for (int i = 0; i < nbItems; i++) {
                LSExpression itemValue = model.prod(x[i],values[i]);
                valueSum.addOperand(itemValue);
            }

            model.maximize(valueSum);
            model.close();

            /* Parameterizes the solver. */
            LSPhase phase = localsolver.createPhase();
            phase.setTimeLimit(limit);

            localsolver.solve();

            solutions = new ArrayList();
            for (int i = 0; i < nbItems; i++)
                if (x[i].getValue() == 1)
            solutions.add(i);

        } catch (LSException e) {
            System.out.println("LSException:" + e.getMessage());
            System.exit(1);
        }
    }

    /* Writes the solution in a file */
    void writeSolution(String fileName) {
        try {
            BufferedWriter output = new BufferedWriter(new FileWriter(fileName));

            for (int i = 0; i < solutions.size(); ++i)
                output.write(solutions.get(i) + " ");

            output.write("\n");

            output.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

     public static void main(String[] args) {

        String outputFile = "result";

        Knapsack01 model = new Knapsack01(42, 2000);
        //model.readInstance(instanceFile);
        String strTimeLimit = "10";
        int values[] = model.getValues();
        int weights[] = model.getWeights();
        for(int i = 0; i < 42; i++) System.out.print(weights[i]+" ");
        System.out.println("");
        for(int i = 0; i < 42; i++) System.out.print(values[i]+" ");
        System.out.println("");

        model.solve(Integer.parseInt(strTimeLimit));
        if(outputFile != null) {
            model.writeSolution(outputFile);
        }
    }
}

class MySQL {

        String driver;
        String server, dbname, url, user, password;
        Connection con;
        Statement stmt;
        ResultSet rs;

        public MySQL() {
                this.driver = "org.gjt.mm.mysql.Driver";
                this.server = "gardenkana.naisyo.com";
                this.dbname = "gardenkamo";
                this.url = "jdbc:mysql://" + server + "/" + dbname + "?useUnicode=true&characterEncoding=UTF-8";
                this.user = "garden?";
                this.password = "oshienaiyo";
                try {
                        this.con = DriverManager.getConnection(url, user, password);
                        this.stmt = con.createStatement ();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                try {
                        Class.forName (driver);
                } catch (ClassNotFoundException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }
        public void close(){
                try {
                        rs.close();
                        stmt.close();
                        con.close();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }

        public ResultSet selectFacilities(){

                String sql = "SELECT * FROM facilities";
                ResultSet rs = null;
                try {
                        rs = stmt.executeQuery (sql);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rs;
        }

}


1 件のコメント: