Android/Java JDBC MySQL-获取行-有效算法 [英] Android/Java JDBC MySQL - fetch rows - effective algorithm

问题描述

我试图以mysql客户端的身份编写一个应用程序，但遇到了麻烦，我不想遵循，因为我认为，即使有可能，这也不是一件好事.

I am trying to write an app as an mysql client and I ran into way, which I don't want to follow, because I think, it is not a good one, even if it is possible.

我的项目中有JDBC驱动程序，我想获取所有行并将其写入用户界面-只需将数据绘制到表中即可.

I have JDBC driver in my project, and I want to fetch all rows and write them to user interface - simply draw a data into table.

一切以通常的方式开始:

Everything starts in an usual way:

    Statement stmt = DataStore.DataStoreClass.mysqlConn.createStatement() ; 
   /*I have static class for some data*/
    String query = "SELECT * FROM "+ DataStore.DataStoreClass.currentTable;
    ResultSet res = stmt.executeQuery(query);

现在，我们在

while (res.next()) {

}

到目前为止，这就是基础知识，一切正常. 但是绝对不行，在res.next()中不是类型的数据，而在数据库中. 它们是二进制形式. 看起来是这样的:

So thats the basics, up to here, everything works fine. But what is definitely not fine, in res.next() are not data in type, in which are in database. They are in binary form. This is how it looks like:

所以.如果要读取表的行/列，则需要了解一件事-数据类型. 称为

So. If you want to read lines/columns of table, you need to know one thing - data type. This is called by

res.getMetaData().getColumnTypeName();

返回带有名称的字符串.不幸的是，MySQL使用22种数据类型，并且其中至少5种(所有int变体，例如tinyint，bigint ...)可以像"tinyint"或"tinyint unsigned"一样返回.这意味着，我们有27件事，可以通过此命令返回.

which returns a string with a name. Unforunately, MySQL uses 22 data types, and at least 5 of them (all variations of int, like tinyint, bigint...) can return just like "tinyint" or "tinyint unsigned". That means, we have 27 things, that can be returned by this command.

因此，您有一个二进制字段，并且想要收集数据.好吧，是的，这很简单...至少，如果您知道，会发生什么...

So, you have a binary field, and you want to gather data. Well, yes, that is simple... at least if you know, what to expect...

 /*This is inside while cycle with res.next()*/
    int columnIndex=1;
    int z = res.getInt(columnIndex);

好吧，但我不知道会发生什么. 以我的方式，这将非常复杂.

Well, but I dont know, what to expect. By my way, it would be very complicated.

说，我们已经获取了表中使用的所有列类型，我们要写下来，并且我们有一个String数组.此外，通过这种方式，我们还知道表的大小(例如列数)，因此我们声明了具有正确大小的新String数组以用于将来的结果

Lets say, we have already fetched ALL column types used in table, we want to write down, and we have an String array. Also, by that way, we also know the size of table, like number of columns, so we have declared new String array with correct size for future results

现在是编程的事情:

        while (res.next()){
          String[] row = new String[res.getMetaData.getColumnCount()];
            for(int i=1; i<=res.getMetaData.getColumnCount(); i++){
                String monthString;
                switch (arrayWithDataTypeAtColumPosition[i]) {
                    case "Tinyint":  row[i] = Integer.toString(res.getInt(i));
                             break;
                    case "String":   row[i] = res.getString(i);
                             break;
                    case "Byte":     row[i] = Byte.toString(res.getByte(i));
                             break;
                    /*next all 24 other cases*/
                    }

        sendRowToDisplay(row);
        /*SendRowToDisplay(row); only for illustration purposes only, 
        in real, this is in AsyncTask in doInBackground, 
       so you will use publishProgress(row); to send in to 
       @Override protected void onProgressUpdate (String[] s){}*/

            }
        }

这个……很糟糕.您拥有数据，但必须决定如何将其转换为易于理解的结果. 所以我的问题是: 有什么办法，如何在没有这么长时间的情况下直接获取类型?

And this ... is quite bad. You have the data, but you have to decide, how you convert it to some human readable result. So my question is: Is there any way, how to get directly the type without this long long swithch?

类似的东西(当然这不存在，就像显示了思维方式):

Something like (of course this does not exists, it is like to show the way of thinking):

while (res.next){
   for(int i=1; i<=res.getMetaData.getColumnCount(); i++){
   res.getDataByMetaDataColumnType().toString();
  }
}

此外，这对我来说非常有用，因为仅写下一行对我来说还不够.我想点击该行，然后在下一个活动中编辑该行，这意味着发送SELECT * FROM Table WHERE column1 = fetchedValue 然后执行相同的操作，我将使用相同的开关来声明TextEdit的类型和值，这些值应加载到TextEdits中.然后，进行下一个相同的开关，它将只从我在TextEdits中拥有的字符串中提取正确的数据...

What more, this would be very useful for me, because just write down a row is not enough to me. I want to tap the row and in next activity, edit the row, which means, send SELECT * FROM Table WHERE column1=fetchedValue and then do the same thing, I will have same damn switch for declaring type of TextEdit and the values, which should be loaded into the TextEdits. And then, the next same switch, which will just do the correct data from strings I have in TextEdits...

推荐答案

如果只需要所有列中所有值的字符串表示形式，则可以使用ResultSet getString(int index)方法.无论列的类型如何，这些值都将转换为String.

If you only need the string representation of all the values in all the columns, you can use the ResultSet getString(int index) method. The values will be converted to String no matter the type of the columns.

这里是一个例子:

    try (Connection con = getDatabaseConnection();) {
        try (Statement st = con.createStatement();) {
            try (ResultSet rs = st.executeQuery("SELECT * FROM TableName");) {
                ArrayList<ArrayList<String>> rows = new ArrayList<ArrayList<String>>();
                while (rs.next()) {
                    ArrayList<String> row = new ArrayList<String>();
                    for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                        row.add(rs.getString(i + 1));
                    }
                    rows.add(row);
                }
                System.out.println(rows);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

这篇关于Android/Java JDBC MySQL-获取行-有效算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！