使用 Java 将 MySQL 查询结果存储在 ArrayList 中

Storing MySQL query results in an ArrayList using Java

提问by PeerPressure

The following java code takes in a POST request from a JSP file and passes back the out ArrayList as output. The problem I'm having is figuring out how to read the output into the Arraylist properly, so they I can grab each element from the Arraylist and display it in my JSP.

以下 java 代码接收来自 JSP 文件的 POST 请求，并将 out ArrayList 作为输出传回。我遇到的问题是弄清楚如何正确地将输出读入 Arraylist，这样我就可以从 Arraylist 中获取每个元素并将其显示在我的 JSP 中。

How can I read in the column names as the first array of strings, and then each row of data as the following arrays of strings, so that I have one giant array list that contains the entire results of the query in an organized manner, and will allow me to read the results onto my JSP page?

如何将列名作为第一个字符串数组读入，然后将每一行数据作为以下字符串数组读入，以便我有一个巨大的数组列表，以有组织的方式包含查询的整个结果，以及可以让我将结果读到我的 JSP 页面上吗？

EDIT: So the only problem I'm having now is that when executing a command like SELECT * FROM table; it only shows the column names. But when I execute a command like SELECT columnName FROM table; it displays the column perfectly. The code below has been updated to reflect where I am at now.

编辑：所以我现在唯一的问题是在执行像 SELECT * FROM table; 这样的命令时。它只显示列名。但是当我执行像 SELECT columnName FROM table; 这样的命令时；它完美地显示了该列。下面的代码已更新以反映我现在的位置。

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;

@SuppressWarnings("serial")

public class databaseServlet extends HttpServlet {
    private Connection conn;
    private Statement statement;

    public void init(ServletConfig config) throws ServletException {
        try {
            Class.forName(config.getInitParameter("databaseDriver"));
            conn = DriverManager.getConnection(
                    config.getInitParameter("databaseName"),
                    config.getInitParameter("username"),
                    config.getInitParameter("password"));
            statement = conn.createStatement();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void doPost (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        ArrayList<String[]> out = new ArrayList<String[]>();
        ArrayList<String> columns = new ArrayList<String>();

        String query = request.getParameter("query");

        if (query.toString().toLowerCase().contains("select")) {
            //SELECT Queries
            //try {
                ResultSet resultSet = statement.executeQuery(query.toString());
                ResultSetMetaData metaData = resultSet.getMetaData();
                int numberOfColumns = metaData.getColumnCount();
                for(int i = 1; i<= numberOfColumns; i++){
                    columns.add(metaData.getColumnName(i));
                }

                while (resultSet.next()){
                    String[] row = new String[numberOfColumns];
                    for (int i = 0; i < numberOfColumns; i++){
                        row[i] = (String) resultSet.getObject(i+1);
                    }
                    out.add(row);
                 }
            //}
            //catch (Exception f) {
                //f.printStackTrace();
            //}
        }
        else if (query.toString().toLowerCase().contains("delete") || query.toLowerCase().contains("insert")) {
            //DELETE and INSERT commands
            //try {
                conn.prepareStatement(query.toString()).executeUpdate(query.toString());
                columns.add("\t\t Database has been updated!");
            //}
            //catch (Exception l){
                //l.printStackTrace();
            //}
        }
        else {
            //Not a valid response
            columns.add("\t\t Not a valid command or query!");
        }
        request.setAttribute("query", query);
        request.setAttribute("resultSize",  out.size());
        request.setAttribute("queryResults", out);
        request.setAttribute("queryColumns", columns);
        RequestDispatcher dispatcher = request.getRequestDispatcher("/dbServlet.jsp");
        dispatcher.forward(request,  response);
    }
}

Here is my .JSP file, and right now it is only printing [] with nothing in it when I execute a command. I know that commands are working because of previous tests, but the array is not displaying properly.

这是我的 .JSP 文件，现在它只在我执行命令时打印 [] 里面没有任何内容。我知道由于先前的测试，命令正在运行，但数组显示不正确。

<?xml version = "1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- dbServlet.html -->
<html xmlns = "http://www.w3.org/1999/xhtml">
<%@ page import="java.util.ArrayList" %>
<head>
    <title>MySQL Servlet</title>
    <style type="text/css">
        body{background-color: green;}
    </style>
</head>
<body>
    <h1>This is the MySQL Servlet</h1>
    <form action = "/database/database" method = "post">
    <p>
        <label>Enter your query and click the button to invoke a MySQL Servlet
            <textarea name = "query" cols="20" rows="5"></textarea>
            <input type = "submit" value = "Run MySQL Servlet" />
            <input type = "reset" value = "Clear Command" />
        </label>
    </p>
    </form>
    <hr>
    <TABLE id="results">
        <%
            ArrayList<String> columns = (ArrayList<String>)request.getAttribute("queryColumns");
            ArrayList<String[]> results = (ArrayList<String[]>)request.getAttribute("queryResults"); 
            out.println("<TR>");
            if(columns != null && !columns.isEmpty()){
                for(String columnName: columns ){
                   out.println("<TD>"+columnName+"</TD>");
                }
            }
            out.println("</TR>");
            //print data
            if(results != null && !results.isEmpty()){
                for(String[] rowData: results){
                   out.println("<TR>");
                   for(String data: rowData){
                      out.println("<TD>"+data+"</TD>");
                   }
                   out.println("</TR>");
                }
            }
        %>
    </TABLE>
    <%= request.getAttribute("query") %>
    <%= request.getAttribute("resultSize") %>
</body>
</html>

回答by Yogendra Singh

Define one list for columns as well.

也为列定义一个列表。

            ArrayList<String[]> results= new ArrayList<String[]>(); 
            ArrayList<String> columns= new ArrayList<String>();

Populate the list of columns as:

将列列表填充为：

            for(int i = 1; i<= numberOfColumns; i++){
                columns.add(metaData.getColumnName(i));
            }

Populate the list of with results row as:

将结果行的列表填充为：

            while (resultSet.next()){ 
                String[] row = new String[numberOfColumns];
                for (int i = 1; i <= numberOfColumns; i++){
                    row[i] = (String) resultSet.getObject(i);
                }
                results.add(row);
             }

in the end:

到底：

            request.setAttribute("queryResults", results);
            request.setAttribute("queryColumns", columns);

回答by Rohit Jain

First of all, why are you adding a toStringcall on a String typevariable?

首先，为什么要添加toString对String type变量的调用？

Now, as for your issue, I would rather create a Class, for the tables in database, with the columns as attributes. And for each row, create an instance of that class from the columns, and add it to the ArrayList.

现在，对于您的问题，我宁愿Class为数据库中的表创建一个, 并将列作为属性。对于每一行，从列中创建该类的一个实例，并将其添加到ArrayList.

So, your ArrayList declaration might be like this: -

所以，你的 ArrayList 声明可能是这样的：-

List<TargetTable> records = new ArrayList<TargetTable>();

And then: -

接着： -

while (res.next()) {

    records.add(new TargetTable(res.getString(1), res.getString(2), ...));
}

I hope you know the number of columns in your table.

我希望您知道表格中的列数。

回答by Shamis Shukoor

ArrayList is not the best data structure for this purpose.

ArrayList 不是用于此目的的最佳数据结构。

You can use

您可以使用

 Table<Integer, String, Object>  

which is available in google guava library. It is really easy to use as well

可在谷歌番石榴库中找到。它也很容易使用

回答by Srinivas B

U can have two arraylists as,

你可以有两个数组列表，

ArrayList<String> columnNames = new ArrayList<String>();
ArrayList<String> out = new ArrayList<String>();

Iterate through ResultSetMetadata and add the columns to the columnNames ArrayList.

遍历 ResultSetMetadata 并将列添加到 columnNames ArrayList。

Then add the columnNames to out :

然后将 columnNames 添加到 out ：

out.add(columNames);

Then Create a VO class with all the column names in the table you are accessing. create all the getters and setters for the class names.

然后使用您正在访问的表中的所有列名称创建一个 VO 类。为类名创建所有的 getter 和 setter。

Then iterate through the Resultset,

然后遍历结果集，

set all the values from the result set into that VO class.

将结果集中的所有值设置到该 VO 类中。

add this VO class to the out arrayList.

将此 VO 类添加到 out arrayList。

ResultVO vo ;
while (resultSet.next()){
vo = new ResultVO();
vo.setColumnName1(resultSet.getString(1));
vo.setColumnName2(resultSet.getString(2));
out.add(vo);
}