coldfusion

tech2024-05-27 77

coldfusion

Okay, this is definitely the last you’ll hear from me about data structures. I have, however, saved one of the most useful data structures for last. Queries are an important part of Web development, whether you’re programming in ASP, JSP, PHP, or Coldfusion. Sometimes, they’re referred to as result sets, record sets, or, in the case of Coldfusion, queries.

好的，这绝对是您最后一次从我这里收到有关数据结构的信息。但是，我最后保存了最有用的数据结构之一。无论您是使用ASP，JSP，PHP还是Coldfusion进行编程，查询都是Web开发的重要组成部分。有时，它们被称为结果集，记录集，或者在Coldfusion的情况下称为查询。

Throughout this article, we’ll look at a few different topics related to queries, including database queries, file system queries, building a query manually, looping through them, and how you can actually run a query against an existing query. So, without further ado, let’s get started.

在本文中，我们将讨论与查询相关的一些不同主题，包括数据库查询，文件系统查询，手动构建查询，遍历它们，以及如何针对现有查询实际运行查询。因此，事不宜迟，让我们开始吧。

数据库查询 (Database Queries)

The most common way to get a query object is by querying a database using the Structured Query Language (SQL). To do this, we use the CFQUERY tag to retrieve our result set. It’s very simple, but you’ll first need to set up a data source using the sample database in the referenced article. Once you’ve got your data source set up, all you have to do is use a CFQUERY command with a bit of SQL in it:

获取查询对象的最常见方法是使用结构化查询语言(SQL)查询数据库。为此，我们使用CFQUERY标记来检索结果集。这非常简单，但是您首先需要使用参考文章中的示例数据库来设置数据源。设置好数据源之后，您要做的就是使用其中包含一些SQL的CFQUERY命令：

<cfquery name="GetUsers" datasource="MyDSN"> SELECT * FROM Users </cfquery>

Now, this query will instruct Coldfusion to access the database that is referenced by the data source name "MyDSN" and to open the table "Users". It will then retrieve all the rows and all the columns in that table. So, we now have our query object. It’s time to explore the ways in which we can use it.

现在，此查询将指示Coldfusion访问数据源名称"MyDSN"所引用的数据库并打开表"Users" 。然后它将检索该表中的所有行和所有列。因此，我们现在有了查询对象。现在该探讨我们可以使用它的方式了。

使用查询 (Using The Query)

The most common way to use a query is to loop over it or output it. You can do this using the QUERY attribute of either the CFOUTPUT or CFLOOP tags. For example, let’s say we want to display everything in the table on our page. It’s very simple with Coldfusion. All we have to do is:

使用查询的最常见方法是循环查询或输出查询。您可以使用CFOUTPUT或CFLOOP标记的QUERY属性来执行此操作。例如，假设我们要在页面的表中显示所有内容。 Coldfusion非常简单。我们要做的就是：

This will go through every row in the query and display each name on a separate line. There are a few different options that can be specified for the CFOUTPUT tag when you use the QUERY attribute. You can easily specify which row you want to start on, and how many rows to display. So, if we want to display only the first row, we can do this:

这将遍历查询的每一行，并在单独的行上显示每个名称。使用QUERY属性时，可以为CFOUTPUT标记指定几个不同的选项。您可以轻松指定要开始的行以及要显示的行数。因此，如果我们只想显示第一行，则可以执行以下操作：

Or, we can start at the second row:

或者，我们可以从第二行开始：

The two attributes can be used together to display a small section of a query at a time. This makes paging through queries extremely simple, with a little forethought and creativity.

这两个属性可以一起使用，以一次显示一小部分查询。这使得通过查询进行分页非常简单，并且需要一些前瞻性和创造力。

Now, the CFOUTPUT tag works well to display information, but if you’re simply performing some sort of logic on the rows in the query, you might want to use CFLOOP for better performance:

现在， CFOUTPUT标记可以很好地显示信息，但是如果您只是对查询中的行执行某种逻辑，则可能需要使用CFLOOP以获得更好的性能：

<cfloop query="GetUsers"> <cfif Name eq "John Doe"> <cfquery name="UpdateUser" datasource="MyDSN"> UPDATE Users SET State = 'TX' WHERE ID = #ID# </cfquery> </cfif> </cfloop>

This will loop over the query and move John Doe to Texas, but leave everyone else where they are.

这将遍历查询并将John Doe移至Texas，但将其他所有人都留在原处。

Now, when you run a query, Coldfusion attaches to the query several variables that you can access. First, there’s the RecordCount variable. This gives you a count of all the records in the query. You can use it in logic like:

现在，当您运行查询时，Coldfusion将几个您可以访问的变量附加到查询中。首先，有RecordCount变量。这样就可以对查询中的所有记录进行计数。您可以在逻辑中使用它，例如：

There’s also the CurrentRow variable. CurrentRow comes in very handy and in most cases, it keeps you from having to create your own counter. For example, let’s say that I’m outputting my users in a table and I want to alternate the row colors for readability. Here’s how I’d normally do this:

还有CurrentRow变量。 CurrentRow非常方便，在大多数情况下，它使您不必创建自己的计数器。例如，假设我要在表中输出用户，并且我想替换行颜色以提高可读性。这是我通常的做法：

<table> <cfoutput query="GetUsers"> <cfset bgcolor = "##FFFFF"> <cfif CurrentRow MOD 2> <cfset bgcolor = "##CCCCCC"> </cfif> <tr bgcolor="#bgcolor#"> <td>#Name#</td> <td>#State#</td> </tr> </cfoutput> </table>

Here, we set the variable every time we iterate over the loop so that the background color is white. Then, if the modulus of the current row divided by 2 is 1, we make the background color gray. The first time through the loop, CurrentRow will be equal to 1, so the background color will be gray.

在这里，每次迭代循环时都设置变量，以使背景色为白色。然后，如果当前行的模数除以2，我们将背景色设为灰色。第一次通过循环， CurrentRow等于1，因此背景颜色为灰色。

Another built-in variable you may find useful is the ColumnList variable. This is a comma delimited list of all the columns returned with a given query. So, I could run a query and dynamically output a table with the columns like this:

您可能会发现有用的另一个内置变量是ColumnList变量。这是用给定查询返回的所有列的逗号分隔列表。因此，我可以运行查询并动态输出带有以下列的表：

<table border="1"> <tr> <cfloop list="#GetUsers.ColumnName#" index="column"> <cfoutput><th>#column#</th></cfoutput> </cfloop> </tr> <cfoutput query="GetUsers"> <tr> <cfloop list="#GetUsers.ColumnName#" index="column"> <td>#Evaluate(column)#</td> </cfloop> </tr> </cfoutput> </table>

This will give you a nice, neat little table in which the column names appeared across the top of the table, and the data displayed in the appropriate column. The awesome thing about this is that you can use it to display any query you want. Just change the query name, and there you have it.

这将为您提供一个漂亮，整洁的小表，其中，表名出现在表的顶部，并且数据显示在适当的列中。令人敬畏的是，您可以使用它来显示所需的任何查询。只需更改查询名称，就可以了。

These are some of the most common uses for Coldfusion’s query object. You’ll probably find yourself using one of the above methods most of the time. But now, let’s look at a few other ways we can get and use query objects.

这些是Coldfusion的查询对象最常见的用法。大多数时候，您可能会发现自己使用上述方法之一。但是现在，让我们看一下获取和使用查询对象的其他几种方式。

目录列表和查询对象 (Directory Listings and Query Objects)

This is a short section, but it’s valuable nonetheless. Every programming language I’ve ever encountered had some means of access the file system. This includes creating, writing to, deleting, moving, copying, and reading files, and creating, deleting, renaming, and listing directories.

这是一个简短的部分，但是仍然很有价值。我遇到过的每种编程语言都有一些访问文件系统的方式。这包括创建，写入，删除，移动，复制和读取文件，以及创建，删除，重命名和列出目录。

Well, when we want to get a list of directories in Coldfusion, we don’t have to sweat too much to do the job! First, we simply get a directory listing:

好吧，当我们想在Coldfusion中获得目录列表时，我们不必费心去完成这项工作！首先，我们简单地得到一个目录清单：

This gives us a list of all the files in the directory with the current template. The name attribute specifies the name of the query object that will be returned. So, we can output the directory listing simply by looping over the query:

这为我们提供了带有当前模板的目录中所有文件的列表。 name属性指定将返回的查询对象的名称。因此，我们可以简单地通过遍历查询来输出目录列表：

Now, this query object contains several columns. You can access these with the ColumnList variable if you want, but I’ll go ahead and tell you what columns they are.

现在，此查询对象包含几列。您可以根据需要使用ColumnList变量访问它们，但是我将继续告诉您它们是什么列。

Name

名称 Size

尺寸 Type

类型 DateLastModified

DateLastModified Attributes

属性 Mode (for *IX systems only, i.e. "755", "466", etc.)

模式(仅适用于* IX系统，即“ 755”，“ 466”等)

There’s yet another way you can get a query object for use in your application. But, you don’t have to have access to the file system or a database to get a query object. You can build your own if you want!

还有一种方法可以获取要在应用程序中使用的查询对象。但是，您不必访问文件系统或数据库即可获取查询对象。如果需要，您可以自己构建！

手动建立查询 (Build a Query Manually)

If you really want to build a query manually, you can use a combination of several functions to do so. To start with, we’ll need to discuss some of the functions that are built into Coldfusion for manipulating queries.

如果您确实要手动构建查询，则可以使用多种功能的组合来进行构建。首先，我们需要讨论Coldfusion内置的一些用于处理查询的功能。

First of all, we have to create a query object. This is easily accomplished with the QueryNew() function. This function takes a comma delimited list of the columns that need to be in the query.

首先，我们必须创建一个查询对象。使用QueryNew()函数可以轻松完成此QueryNew() 。此函数以逗号分隔的列表包含需要查询的列。

Now, we have an empty query. It lists the columns in the function parameter, but lists no rows at all. A query without data isn’t much use to us, so we’ll add some data using the QuerySetCell() function. This function takes the name of the query, the name of the column, the value of the column, and the row number to insert this information into. We can add our first user like so:

现在，我们有一个空查询。它列出了function参数中的列，但根本不列出任何行。没有数据的查询对我们没有多大用处，因此我们将使用QuerySetCell()函数添加一些数据。该函数采用查询的名称，列的名称，列的值以及要将此信息插入其中的行号。我们可以这样添加我们的第一个用户：

After this, we can output our query to the screen and see that it really is a query object with the data we’ve entered.

此后，我们可以将查询输出到屏幕上，看看它确实是带有输入数据的查询对象。

<cfoutput query="myQuery"> #id# : #name# : #username# : #password#<br> </cfoutput>

Now, you may be thinking that you don’t see much of a point in creating your own query, and, in fact, in most cases you won’t need this. But, here’s a scenario in which you might find some use for this functionality. I won’t provide any code for this particular case, since it’s actually fairly involved, but I’m sure you can dream up all sorts of ways to apply this technique.

现在，您可能会认为创建自己的查询没有多大意义，实际上，在大多数情况下，您将不需要此查询。但是，在这种情况下，您可能会发现此功能的某些用途。因为它确实涉及到很多问题，所以我不会提供任何代码，但是我敢肯定，您可以想像出各种方法来应用这种技术。

Imagine that you are building an online store for an affiliate that provides product streams via a Web service (one very large affiliate comes to mind). When you call the Web service, an XML document is returned to you. When XML documents have been parsed, they are returned as structures nested inside arrays nested inside structures, etcetera, etcetera, etcetera.

想象一下，您正在为通过网络服务提供产品流的会员建立在线商店( 想到一个非常大的会员 )。当您调用Web服务时，将返回一个XML文档。解析XML文档后，它们将作为嵌套在结构等内部的数组中的结构返回，等等。

We can create a query object that will be easier to manipulate than a convoluted series of structures and arrays using the above methodology. In fact, I just happened to find this tutorial, which will show you how to accomplish this particular task (it doesn’t use Amazon’s Web services, but the concept is the same and easily portable).

使用上述方法，我们可以创建一个比一系列复杂的结构和数组更易于操作的查询对象。实际上，我只是偶然发现了本教程，该教程将向您展示如何完成此特定任务(它不使用Amazon的Web服务，但概念相同且易于移植)。

You can also add a column to a query using the QueryAddColumn() function. For this function, you specify the query name, the column name, and the name of an array that contains the data with which you want to populate the column. To prove that this actually does happen in real life, take a look at this forum post. This is a real life situation where the QueryAddColumn() function could come in handy (ignore any syntax errors in there. It was whipped up on the fly without testing…). So, there are definitely real life situations where you may need to use these functions.

您还可以使用QueryAddColumn()函数向查询中添加列。对于此功能，您可以指定查询名称，列名称以及包含要用来填充列的数据的数组的名称。要证明这确实在现实生活中确实发生，请查看此论坛帖子。这是现实生活中的情况，其中QueryAddColumn()函数可以派上用场(忽略此处的任何语法错误。它未经测试即被SwiftQueryAddColumn() ……)。因此，在现实生活中肯定需要使用这些功能。

查询查询 (Querying a Query)

Sometimes, you may need to pull a lot of detailed data from a database and then retrieve aggregate data based on the detailed information. In this case, Coldfusion’s ability to use a query as a data source comes in quite handy. Now, as forum member mrhatch pointed out to me in the previously referenced forum thread, this functionality is referred to as CFSQL (I just called it a query of a query before).

有时，您可能需要从数据库中提取大量详细数据，然后根据详细信息检索汇总数据。在这种情况下，Coldfusion使用查询作为数据源的功能非常方便。现在，正如论坛成员mrhatch在先前引用的论坛线程中向我指出的那样，此功能称为CFSQL (我之前称它为查询的查询)。

Let’s take a look at a situation in which this might be useful. In our existence as programmers, we often find ourselves creating reports. Yes, we hate to do it, but it’s a necessary evil if we want to pay our bills. In many cases, users want the ability to see aggregate data as well as detailed information. In this case, we have a few options.

让我们看一下这种情况可能有用的情况。作为程序员，我们经常发现自己正在创建报告。是的，我们讨厌这样做，但是如果我们想付账单，这是必然的恶行。在许多情况下，用户希望能够查看聚合数据以及详细信息。在这种情况下，我们有一些选择。

We can get a query containing the details and, as we output the query, we can pull aggregate data from the database using an identifier contained within the detail query.

我们可以获取包含详细信息的查询，并在输出查询时，可以使用包含在详细信息查询中的标识符从数据库中提取聚合数据。 We can output the query and manually compile our aggregate results as we do so, performing any needed calculations before finally outputting to the screen.

我们可以输出查询并手动编译汇总结果，在最终输出到屏幕之前执行任何需要的计算。

We can use CFSQL to avoid both of the above scenarios and, at the same time, retrieve the needed summary information.

我们可以使用CFSQL避免上述两种情况，并同时检索所需的摘要信息。

For our tutorial, we’ll go with the third option since, of course, it demonstrates the point here. We’ll say that we have a query contains the following data:

对于我们的教程，我们将选择第三个选项，因为它当然在这里证明了这一点。我们将说我们有一个包含以下数据的查询：

This data is in a query called GetDetails and it shows a detailed listing of both our company’s sales people, the orders they fulfilled, and the dates and clients for which the orders were fulfilled. What we want to see is a prettier version of this, in which each sales person’s name is a header and, beneath that, appears a list of their sales. Then, at the bottom of each section, we want to see the sales person’s total sales.

该数据在名为GetDetails的查询中，它显示了我们公司的销售人员，他们完成的订单以及订单的日期和客户的详细列表。我们想要看到的是一个更漂亮的版本，其中每个销售人员的名字都是标题，并在标题下方显示了他们的销售清单。然后，在每个部分的底部，我们要查看销售人员的总销售额。

Challenge: Try to create the query above using only the QueryNew, QueryAddRow, and QuerySetCell functions. This way, you don’t have to create a database, a data source, and the table in order to follow along with the other examples.

挑战：尝试仅使用QueryNew ， QueryAddRow和QuerySetCell函数创建以上查询。这样，您无需创建数据库，数据源和表即可跟随其他示例。

Let’s start by outputting our query. This will give us the detailed listing of how our sales guys did:

让我们从输出查询开始。这将为我们提供有关销售人员工作方式的详细列表：

<cfset curUserID = GetDetails.UserID> <cfoutput query="GetDetails"> <cfif CurrentRow lt RecordCount> <cfset nextUser = GetDetails.UserID[CurrentRow + 1]> <cfelse> <cfset nextUser = ""> </cfif> <cfif curUserID neq UserID or CurrentRow eq 1> <hr> #UserID# #UserName# (#FullName#) : <br> </cfif> <blockquote> #OrderID# - #CustomerName# was sent on #DateFormat(SentDate, "mm/dd/yyyy")# #TimeFormat(SentDate, "hh:mm:ss tt")#<br> </blockquote> <cfif NextUserID neq UserID> <cfquery name="GetCount" dbtype="query"> SELECT Count(OrderID) AS OrderCount FROM GetDetails WHERE UserID = #UserID# </cfquery> <blockquote> <strong>User Total: #GetCount.OrderCount#</strong> </blockquote> </cfif> <cfset curUserID = UserID> </cfoutput>

Okay, there’s a lot of code here to cover, so let’s get right into it. First, we only want to output each sales person’s name and ID once. So, we have to track what the current user ID is, and what the next user ID is. We track our current user with the curUserID variable. We then dive head-first into our query.

好的，这里有很多代码要覆盖，所以让我们开始吧。首先，我们只希望输出一次每个销售人员的姓名和ID。因此，我们必须跟踪当前的用户ID是什么，以及下一个用户ID是什么。我们使用curUserID变量跟踪当前用户。然后，我们首先深入查询。

The first CFIF statement looks to see if we’re on the last record in the query. If we are, our next user is a null string. If we’re not, we treat the UserID column as an array, and we get the value of the UserID column in the next row.

第一个CFIF语句用于查看我们是否在查询的最后一条记录上。如果是，则下一个用户为空字符串。如果不是，则将UserID列视为数组，然后在下一行获取UserID列的值。

Now, we want to know if it’s time to display the user’s name. We know that, if this is the first row in the query, we’ll need to. But, if the curUserID variable is not the same as the UserID variable (which comes straight from the query), this means we have advanced to another user’s records. At this point, I should emphasize the importance of ordering your columns by your identifier, in this case UserID. If we are on another user’s records, we output that person’s name, user name, and user ID.

现在，我们想知道是否该显示用户名了。我们知道，如果这是查询中的第一行，则需要这样做。但是，如果curUserID变量与UserID变量(直接来自查询)不同，则意味着我们已经前进到另一个用户的记录。在这一点上，我应该强调按标识符(在这种情况下为UserID对列进行排序的重要性。如果我们在另一个用户的记录上，则输出该人的姓名，用户名和用户ID。

Now, we simply display the order ID, customer name, and the date on which the order was sent. Notice that these bits of data are displayed no matter where we are in the query.

现在，我们只需显示订单ID，客户名称和发送订单的日期。请注意，无论我们在查询中的什么位置，都会显示这些数据位。

Next, we get to the interesting part: we look to see if this is the last row for this user. If it is, we use the magic CFSQL. Notice a couple of odd things about this query. First of all, we don’t specify the "datasource" attribute; we specify the "dbtype" attribute and assign it the "query" value. Second, we aren’t selecting from a table; we’re selecting from a query. But, when you think about it, there really isn’t a big difference between a table and a query. A query has the same structure as a table, with rows and columns.

接下来，我们进入有趣的部分：我们看看这是否是该用户的最后一行。如果是这样，我们将使用神奇的CFSQL 。请注意有关此查询的一些奇怪的事情。首先，我们不指定“数据源”属性。我们指定“ dbtype”属性并为其分配“ query”值。其次，我们不是从表中选择；我们从查询中选择。但是，当您考虑它时，表和查询之间实际上并没有太大的区别。查询具有与表相同的结构，但具有行和列。

Now, we can limit our record set from the CFSQL query just as we might limit our record set from a MySQL database or a SQL Server database. We use the WHERE clause to specify that we only want to get the current user’s records. Then, we output the record count of the query. Finally, we set the curUserID variable to the current UserID from the query.

现在，我们可以限制CFSQL查询中的记录集，就像我们可以限制MySQL数据库或SQL Server数据库中的记录集一样。我们使用WHERE子句指定仅希望获取当前用户的记录。然后，我们输出查询的记录数。最后，我们将curUserID变量设置为查询中的当前UserID 。

Using Coldfusion 5, I’ve personally had shaky results using aggregate functions like COUNT in combination with WHERE clauses in CFSQL queries. Coldfusion MX seems to have rectified the problem, since I tested all of this code on CFMX 6.1. If you’re using Coldfusion 5, you can just select all or a single field from the query, and use the (QueryName).RecordCount variable to figure out how many sales this user had, for instance, or whatever it is you’re trying to figure out.

使用Coldfusion 5，我个人使用诸如COUNT之类的聚合函数与CFSQL查询中的WHERE子句结合在一起时，得到了不稳定的结果。自从我在CFMX 6.1上测试了所有这些代码以来，Coldfusion MX似乎已经解决了该问题。如果您使用Coldfusion 5，则可以从查询中选择全部或单个字段，然后使用(QueryName) 。 RecordCount变量来计算该用户的销售量，例如您要计算的销售量。

结论 (Conclusion)

Queries are more than just the results you get from a database. In Coldfusion, queries offer very simple ways to display and manipulate data. You can convert other data structures (like XML documents) into queries, and display them in a much simpler fashion. You can glean aggregate data from detailed queries without additional database hits using CFSQL. You can access specific rows and columns in a query by treating it as a structure containing a series of arrays. You can display a query dump using the ColumnList variable attached to each query. And you can get a listing of directories and files and access it just as you would a query.

查询不仅仅是从数据库中获得的结果。在Coldfusion中，查询提供了非常简单的方式来显示和处理数据。您可以将其他数据结构(例如XML文档)转换为查询，并以更简单的方式显示它们。您可以使用CFSQL从详细的查询中收集汇总数据，而无需额外的数据库命中。通过将查询视为包含一系列数组的结构，可以访问查询中的特定行和列。您可以使用附加到每个查询的ColumnList变量来显示查询转储。您可以获得目录和文件的列表，并可以像查询一样访问它。

That concludes our Introduction to Coldfusion Data Structures series. There are, of course, all sorts of data structures that can be put to excellent use by a creative developer, and I hope that this series has helped you to see some of the ways in which Coldfusion can be ever-so-easily manipulated to meet today’s business challenges.

到此结束我们的Coldfusion数据结构简介系列。当然，创意开发人员可以很好地利用各种数据结构，我希望本系列文章可以帮助您了解可以如此轻松地操作Coldfusion的某些方法。应对当今的业务挑战。

翻译自: https://www.sitepoint.com/data-structures-4-queries/

coldfusion

相关资源：jdk-8u281-windows-x64.exe

最新回复(0)