通过随机选择表执行SELECT sql [英] Execute SELECT sql by randomly picking tables

查看：138
本文介绍了通过随机选择表执行SELECT sql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在一个项目中，我有两个表在不同的数据库与不同的模式。这意味着我有两个不同的连接参数为这两个表使用JDBC连接 -



让我们假设下面是 config.property file - 

  TABLES：table1 table2 
 
 #For Table1 
 table1 .url：jdbc：mysql：// localhost：3306 / garden 
 table1.user：gardener 
 table1.password：shavel 
 table1.driver：jdbc-driver 
 table1.percentage ：80 
 
 
 
 #For Table2 
 table2.url：jdbc：mysql：// otherhost：3306 / forest 
 table2.user：forester 
 table2.password：ax 
 table2.driver：jdbc-driver 
 table2.percentage：20

下面的方法将会读取上面的 config.property文件，并为每个方法创建一个 ReadTableConnectionInfo对象 

  private static HashMap< String，ReadTableConnectionInfo> tableList = new HashMap< String，ReadTableConnectionInfo>（）; 
 
 private static void readPropertyFile（）throws IOException {
 
 prop.load（Read.class.getClassLoader（）。getResourceAsStream（config.properties））; 
 
 tableNames = Arrays.asList（prop.getProperty（TABLES）。split（））; 
 
 for（String arg：tableNames）{
 
 ReadTableConnectionInfo ci = new ReadTableConnectionInfo（）; 
 
 String url = prop.getProperty（arg +.url）; 
 String user = prop.getProperty（arg +.user）; 
 String password = prop.getProperty（arg +.password）; 
 String driver = prop.getProperty（arg +.dr​​iver）; 
 double percentage = Double.parseDouble（prop.getProperty（arg +.percentage））; 
 
 ci.setUrl（url）; 
 ci.setUser（user）; 
 ci.setPassword（password）; 
 ci.setDriver（driver）; 
 ci.setPercentage（percentage）; 
 
 tableList.put（arg，ci）; 
} 
 
}

c> ReadTableConnectionInfo 类，它将保存特定表的所有表连接信息。

  public class ReadTableConnectionInfo {
 
 public String url; 
 public String user; 
 public String password; 
 public String driver; 
 public String percentage; 
 
 public String getUrl（）{
 return url; 
} 
 
 public void setUrl（String url）{
 this.url = url; 
} 
 
 public String getUser（）{
 return user; 
} 
 
 public void setUser（String user）{
 this.user = user; 
} 
 
 public String getPassword（）{
 return password; 
} 
 
 public void setPassword（String password）{
 this.password = password; 
} 
 
 public String getDriver（）{
 return driver; 
} 
 
 public void setDriver（String driver）{
 this.driver = driver; 
} 
 
 public double getPercentage（）{
 return percentage; 
} 
 
 public void setPercentage（double percentage）{
 this.percentage = percentage; 
} 
}


$ b $ p现在我为指定数量的线程创建ExecutorService将此 tableList对象传递给 ReadTask 的构造函数

  //创建给定大小的线程池
 ExecutorService service = Executors.newFixedThreadPool（10）; 
 
 for（int i = 0; i <10; i ++）{
 service.submit（new ReadTask（tableList））; 
}

下面是我的 ReadTask 实现 Runnable interface ，其中每个线程都应该为每个表建立连接。 

  class ReadTask implements Runnable {
 
 private final HashMap< String，XMPReadTableConnectionInfo> tableLists; 
 
 public ReadTask（HashMap< String，ReadTableConnectionInfo> tableList）{
 this.tableLists = tableList; 
} 
 
 
 @Override 
 public void run（）{
 
 int j = 0; 
 dbConnection = new Connection [tableLists.size（）]; 
 statement = new Statement [tableLists.size（）]; 
 
 //循环映射值并创建连接列表
 for（ReadTableConnectionInfo ci：tableLists.values（））{
 
 dbConnection [j] = getDBConnection （ci.getUrl（），ci.getUser（），ci.getPassword（），ci.getDriver（））; 
 statement [j] = dbConnection [j] .createStatement（）; 
 
 j ++; 
} 
 
 while（System.currentTimeMillis（）<= 60分钟）{
 
 / *生成随机数并检查该随机数是否
 *在1和80之间，如果是，然后选择table1 
 *，然后使用我做的上面的table1连接和语句，并对该表做一个SELECT *。 
 *如果该随机数在81和100之间，那么选择table2 
 *然后使用table2连接和语句，并对该表做一个SELECT * 
 * / 
 $ b b ResultSet rs = statement [what_table_statement] .executeQuery（selectTableSQL）; 
 
} 
} 
}

有两个表，这意味着每个线程将为每个表创建两个连接，然后根据随机生成数，使用该特定表连接对该表执行SELECT *。



算法：


  1. 生成1到100之间的随机数。

  2. 如果该随机数小于 table1.getPercentage（），则选择 table1
    ，然后使用 table1语句对象可以对该数据库进行 SELECT sql调用。

  3. 否则选择 table2 ，然后使用 table2语句对象 code> SELECT sql call 到该数据库。

strong>



我很难说出如何应用上述算法，以及如何比较随机数每个表百分比，然后决定哪个表我需要使用，然后确定哪个表连接和语句我需要使用 SELECT sql调用。



这意味着我需要检查 getPercentage（）方法，并将它们与随机数进行比较。



现在我只有两个表我可以有三个表，百分比分布可能是 80 10 10 。



更新： 

  class ReadTask implements Runnable {
 
 private Connection [] dbConnection = null; 
 private ConcurrentHashMap< ReadTableConnectionInfo，Connection> tableStatement = new ConcurrentHashMap< ReadTableConnectionInfo，Connection>（）; 
 
 public ReadTask（LinkedHashMap< String，XMPReadTableConnectionInfo> tableList）{
 this.tableLists = tableList; 
} 
 
 
 @Override 
 public run（）{
 
 int j = 0; 
 dbConnection = new Connection [tableLists.size（）]; 
 
 //循环映射值并创建连接列表
 for（ReadTableConnectionInfo ci：tableLists.values（））{
 
 dbConnection [j] = getDBConnection （ci.getUrl（），ci.getUser（），ci.getPassword（），ci.getDriver（））; 
 tableStatement.putIfAbsent（ci，dbConnection [j]）; 
 
 j ++; 
} 
 
随机随机= new SecureRandom（）; 
 
 while（<60分钟）{
 
 double randomNumber = random.nextDouble（）* 100.0; 
 ReadTableConnectionInfo table = selectRandomConnection（randomNumber）; 
 
 for（Map.Entry< ReadTableConnectionInfo，Connection> entry：tableStatement.entrySet（））{
 
 if（entry.getKey（）。getTableName（）。equals .getTableName（）））{
 
 final String id = generateRandomId（random）; 
 final String selectSql = generateRandomSQL（table）; 
 
 preparedStatement = entry.getValue（）。prepareCall（selectSql）; 
 preparedStatement.setString（1，id）; 
 
 rs = preparedStatement.executeQuery（）; 
} 
} 
} 
} 
 
 
 
 private String generateRandomSQL（ReadTableConnectionInfo表）{
 
 int rNumber = random.nextInt（table.getColumns（）。size（））; 
 
 List< String> shuffledColumns = new ArrayList< String>（table.getColumns（））; 
 Collections.shuffle（shuffledColumns）; 
 
 String columnsList =; 
 
 for（int i = 0; i  columnsList + =（，+ shuffledColumns.get（i））; 
} 
 
 final String sql =SELECT ID+ columnsList +from
 + table.getTableName（）+where id =？ 
 
 return sql; 
} 
 
 
 private ReadTableConnectionInfo selectRandomConnection（double randomNumber）{
 
 double limit = 0; 
 for（ReadTableConnectionInfo ci：tableLists.values（））{
 limit + = ci.getPercentage（）; 
 if（random.nextDouble（）< limit）{
 return ci; 
} 
 throw new IllegalStateException（）; 
} 
 return null; 
} 
}


解决方案

可以认为它是可用连接的一个循环，如下所示：

  public run（）{
 ... 
随机随机= new SecureRandom（）; 
 
 while（<60分钟）{
 double randomNumber = random.nextDouble（）* 100.0; 
 ReadTableConnectionInfo tableInfo = selectRandomConnection（randomNumber）; 
 
 // do query ... 
} 
} 
 
 
 private ReadTableConnectionInfo selectRandomConnection（double randomNumber）{
 double limit = 0; 
 for（ReadTableConnectionInfo ci：tableLists.values（））{
 limit + = ci.getPercentage（）; 
 if（randomNumber< limit）{
 return ci; 
} 
 throw new IllegalStateException（）; 
}

只要randomNumber的最大值小于sum（percent）



我想到的另一件事：如果你最终有这么多可能的查询，循环查找成为一个问题，您可以构建一个查找表：创建一个数组，使数组的总大小包含足够的条目，以便查询的相对权重可以用整数表示。



对于您的三个查询示例，80:10:10，有一个10个条目的数组 ReadTableConnectionInfo 八个引用指向table1，一个指向table2，一个指向table3。然后简单地缩放你的随机数为 0< = rand< 10 （例如（int）（Math.random（）* 10），并使用它来索引到数组。

I am working on a project in which I have two tables in a different database with different schemas. So that means I have two different connection parameters for those two tables to connect using JDBC-

Let's suppose below is the config.property file-

TABLES: table1 table2

#For Table1
table1.url: jdbc:mysql://localhost:3306/garden
table1.user: gardener
table1.password: shavel
table1.driver: jdbc-driver
table1.percentage: 80



#For Table2
table2.url: jdbc:mysql://otherhost:3306/forest
table2.user: forester
table2.password: axe
table2.driver: jdbc-driver
table2.percentage: 20

Below method will read the above config.property file and make a ReadTableConnectionInfo object for each tables.

private static HashMap<String, ReadTableConnectionInfo> tableList = new HashMap<String, ReadTableConnectionInfo>();

private static void readPropertyFile() throws IOException {

    prop.load(Read.class.getClassLoader().getResourceAsStream("config.properties"));

    tableNames = Arrays.asList(prop.getProperty("TABLES").split(" "));

    for (String arg : tableNames) {

        ReadTableConnectionInfo ci = new ReadTableConnectionInfo();

        String url = prop.getProperty(arg + ".url");
        String user = prop.getProperty(arg + ".user");
        String password = prop.getProperty(arg + ".password");
        String driver = prop.getProperty(arg + ".driver");
        double percentage = Double.parseDouble(prop.getProperty(arg + ".percentage"));

        ci.setUrl(url);
        ci.setUser(user);
        ci.setPassword(password);
        ci.setDriver(driver);
        ci.setPercentage(percentage);

        tableList.put(arg, ci);
    }

}

Below is the ReadTableConnectionInfo class that will hold all the table connection info for a particular table.

public class ReadTableConnectionInfo {

    public String url;
    public String user;
    public String password;
    public String driver;
    public String percentage;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public double getPercentage() {
        return percentage;
    }

    public void setPercentage(double percentage) {
        this.percentage = percentage;
    }
}

Now I am creating ExecutorService for specified number of threads and passing this tableList object to constructor of ReadTask class-

        // create thread pool with given size
        ExecutorService service = Executors.newFixedThreadPool(10);

        for (int i = 0; i < 10; i++) {
            service.submit(new ReadTask(tableList));
        }

Below is my ReadTask that implements Runnable interface in which each thread is supposed to make a connection for each tables. 

class ReadTask implements Runnable {

    private final HashMap<String, XMPReadTableConnectionInfo> tableLists;

public ReadTask(HashMap<String, ReadTableConnectionInfo> tableList) {
    this.tableLists = tableList;
}


@Override
public void run() {

    int j = 0;
    dbConnection = new Connection[tableLists.size()];
    statement = new Statement[tableLists.size()];

    //loop around the map values and make the connection list
    for (ReadTableConnectionInfo ci : tableLists.values()) {

        dbConnection[j] = getDBConnection(ci.getUrl(), ci.getUser(), ci.getPassword(), ci.getDriver());
        statement[j] = dbConnection[j].createStatement();

        j++;
    }

    while (System.currentTimeMillis() <= 60 minutes) {

    /* Generate random number and check to see whether that random number
     * falls between 1 and 80, if yes, then choose table1
     * and then use table1 connection and statement that I made above and do a SELECT * on that table.
     * If that random numbers falls between 81 and 100 then choose table2 
     * and then use table2 connection and statement and do a SELECT * on that table
     */

    ResultSet rs = statement[what_table_statement].executeQuery(selectTableSQL);

    }
     }
}

Currently I have two tables, that means each thread will make two connections for each table and then use that particular table connection for doing SELECT * on that table depending on the random generation number.

Algorithm:-

  1. Generate Random number between 1 and 100.
  2. If that random number is less than table1.getPercentage() then choose table1 and then use table1 statement object to make a SELECT sql call to that database.
  3. else choose table2 and then use table2 statement object to make a SELECT sql call to that database.

My Question-

I am having hard time in figuring out how should apply the above algorithm and how should I compare the random number with each tables percentage and then decide which table I need to use and after that figure out which table connection and statements I need to use to make a SELECT sql call.

So that means I need to check getPercentage() method of each table and them compare with the Random Number.

Right now I have only two tables, in future I can have three tables, with percentage distribution might be as 80 10 10.

UPDATE:-

class ReadTask implements Runnable {

    private Connection[] dbConnection = null;
    private ConcurrentHashMap<ReadTableConnectionInfo, Connection> tableStatement = new ConcurrentHashMap<ReadTableConnectionInfo, Connection>();

    public ReadTask(LinkedHashMap<String, XMPReadTableConnectionInfo> tableList) {
        this.tableLists = tableList;
    }


    @Override
    public run() {

    int j = 0;
    dbConnection = new Connection[tableLists.size()];

    //loop around the map values and make the connection list
    for (ReadTableConnectionInfo ci : tableLists.values()) {

    dbConnection[j] = getDBConnection(ci.getUrl(), ci.getUser(), ci.getPassword(), ci.getDriver());
    tableStatement.putIfAbsent(ci, dbConnection[j]);

    j++;
    }

      Random random = new SecureRandom();

      while ( < 60 minutes) {

        double randomNumber = random.nextDouble() * 100.0;
        ReadTableConnectionInfo table = selectRandomConnection(randomNumber);

        for (Map.Entry<ReadTableConnectionInfo, Connection> entry : tableStatement.entrySet()) {

            if (entry.getKey().getTableName().equals(table.getTableName())) {

                final String id = generateRandomId(random);
                final String selectSql = generateRandomSQL(table);

                preparedStatement = entry.getValue().prepareCall(selectSql);
                preparedStatement.setString(1, id);

                rs = preparedStatement.executeQuery();
            }
        }
      }
    }



        private String generateRandomSQL(ReadTableConnectionInfo table) {

        int rNumber = random.nextInt(table.getColumns().size());

        List<String> shuffledColumns = new ArrayList<String>(table.getColumns());
        Collections.shuffle(shuffledColumns);

        String columnsList = "";

        for (int i = 0; i < rNumber; i++) {
            columnsList += ("," + shuffledColumns.get(i));
        }

        final String sql = "SELECT ID" + columnsList + "  from "
                + table.getTableName() + " where id = ?";

        return sql;
    }


    private ReadTableConnectionInfo selectRandomConnection(double randomNumber) {

        double limit = 0;
        for (ReadTableConnectionInfo ci : tableLists.values()) {
            limit += ci.getPercentage();
            if (random.nextDouble() < limit) {
                return ci;
            }
            throw new IllegalStateException();
        }
        return null;
    }
    }

解决方案

You could think of it as a loop over the available connections, something like the following:

public run() {
  ...
  Random random = new SecureRandom();

  while ( < 60 minutes) {
    double randomNumber = random.nextDouble() * 100.0;
    ReadTableConnectionInfo tableInfo = selectRandomConnection(randomNumber);

    // do query...
  }
}


private ReadTableConnectionInfo selectRandomConnection(double randomNumber) {
  double limit = 0;
  for (ReadTableConnectionInfo ci : tableLists.values()) {
    limit += ci.getPercentage();
    if (randomNumber < limit) {
      return ci;
  }
  throw new IllegalStateException();
}

As long as randomNumber has a maximum value of less then sum(percentage), that'll do the job.

One other thing I thought of: if you're going to end up having so many possible queries that the a looping lookup becomes an issue, you could build a lookup table: create an array such that the total size of the array contains enough entries so that the relative weightings of the queries can be represented with integers.

For your example of three queries, 80:10:10, have a 10-entry array of ReadTableConnectionInfo with eight references pointing to table1, one to table2, and one to table3. Then simply scale your random number to be 0 <= rand < 10 (eg (int)(Math.random() * 10), and use it to index in to your array.

这篇关于通过随机选择表执行SELECT sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆