Shulou(Shulou.com)06/03 Report--

With SQL query language, you can only query the data located in the database, but when the data you are faced with is in Excel table or Txt text format, is there any way to query the file directly?

Introduction to Zero

As a database developer, it is common to query data in SQL language. SQL is a query language based on database, which requires that the data to be queried can only be located in the database. But in practical work, we often encounter some data that is not from the database, especially a lot of data from some Excel tables or TXT text files. If you want to make SQL queries against them, it is common practice to create temporary tables in the database, then import the data, and then query them using SQL. There is nothing wrong with this in itself, but there are several inadequacies:

First of all, it is more troublesome, the data comes from a variety of sources, different formats, and different table structures. The import operation can only be maintained manually by the database administrator, and the complexity of the import operation can be imagined.

Second, databases often involve sensitive data or security considerations, and even if you are not afraid of trouble and are willing to maintain them, you may not have the authority to do so.

Finally, these data may be temporary and sudden, and it is not suitable to add to the database at all, otherwise the database will become more and more bloated and eventually lead to poor overall access performance.

With these inadequacies, programmers generally have to give up SQL-style query, can only achieve some simple keyword search and other functions through the program, after all, to achieve their own SQL syntax query is not only difficult but also completely unnecessary.

But SQL queries are really easy to use sometimes. So, is there a third-party software that can query text in SQL style, so that programmers do not have to consider the above troubles while enjoying the convenience?

The answer is, of course, yes, and that is what this article is going to introduce-the aggregator.

Start

Here's how to use the aggregator to implement SQL-style query text files in Java. Of course, the text file here does not refer to a completely free text file, but a formatted file similar to a datasheet.

Download and install the aggregator from the official website and add dm.jar and its dependent configuration file raqsoftConfig.xml to the classpath of the current program. Then, using the JDBC class provided by the aggregator, you can query the text file as if it were a data table in the database.

Two files are used in the example. The first student.txt data is as follows:

The data format is the name of the first behavior field, the subsequent rows are data, and the columns are separated by the Tab key. This table shows the basic information of the students in each class.

The second score.txt data is as follows:

This file records each student's scores in each subject through the two key fields of 'Class' and 'Student ID'.

Angular JDBC exampl

For students who are familiar with JDBC, it is very simple for Java to call the aggregator to use SQL to query the text. Here is a sample code:

Import java.sql.Connection

Import java.sql.DriverManager

Import java.sql.ResultSet

Import java.sql.ResultSetMetaData

Import java.sql.SQLException

Import java.sql.Statement

Public class SQLDemo {

Public static void main (String [] args) {

Connection con = null;// connection

Statement stmt = null;// execute statement

ResultSet rst = null;// result set

Try {

/ * Connect to the aggregator through JDBC * /

Class.forName ("com.esproc.jdbc.InternalDriver")

Con = DriverManager.getConnection ("jdbc:esproc:local://")

/ * execution statement method * /

Stmt = con.createStatement ()

Rst = stmt.executeQuery ("SELECT * FROM score.txt")

/ * end of execution statement method * /

ResultSetMetaData meta = rst.getMetaData ()

For (int I = 0; I

< meta.getColumnCount(); i++) { System.out.print(meta.getColumnName(i + 1) + "\t"); } System.out.println(); // 输出结果 while (rst.next()) { for (int i = 0; i < meta.getColumnCount(); i++) { System.out.print(rst.getObject(i + 1) + "\t"); } System.out.println(); } stmt.close(); con.close(); } catch (ClassNotFoundException cnf) { System.out.println("没找到驱动程序"); cnf.printStackTrace(); } catch (SQLException se) { se.printStackTrace(); } } } 示例中可以看到加粗的 SQL 语句跟普通 SQL 语法基本一致，仅仅是表名不一样，这里直接写 TXT 文件名。执行后结果如下：

As you can see, using the JDBC of the aggregator, you can query the text file with the table structure directly as a data table. The aggregator supports CSV, XLS, XLSX, and even JSON in addition to the TXT format. In addition, the aggregator itself has two data storage formats: BTX and CTX, which can also be queried directly.

Well, the further question is, to what extent can the aggregator support the various commands of SQL?

First of all, it should be noted that the aggregator is not a database product, so some database maintenance commands in SQL are not supported, such as Create, Delete and so on.

So to what extent can Select be supported?

ZIP group statistics

Let's take a look at the most common query in the SQL query, which associates student.txt with score.txt and calculates each student's total score in groups. Replace the bold SQL statement in the above sample code with the following statement:

SELECT A. Class, A. Name, sum (B. Total score FROM student.txt A JOIN score.txt B ON A. Class = B. Class AND A. Student ID=B. Student ID GROUP BY A. Class, A. Name

After implementation, get the total score table of each class:

As you can see, when the aggregator queries the text with SQL, it has no problem with regular grouping and table federation. So how to write a query with parameters?

Zero usage parameter

Simply replace the code in the execution statement method block with the following code:

/ * execution statement method * /

CallableStatement cs = null; / / define CallableStatement object String

String sql = "SELECT name, gender FROM student.txt WHERE class =?"

Cs = con.prepareCall (sql)

Cs.setString (1, Class 1)

Rst = cs.executeQuery ()

/ * end of execution statement method * /

After the change, be careful to introduce the CallableStatement class. The results are as follows:

The sentence with parameters is still fine, and the calling method is consistent with the database standard. However, in the above results, gender is displayed as numbers, can the aggregator SQL support the conversion syntax?

Z. CASE statement

Replace the above SQL statement with the SQL statement with CASE:

SELECT name, CASE gender WHEN 0 THEN 'male' ELSE 'female' END gender FROM student.txt WHERE class =?

The results are as follows:

As you can see from the above examples, the aggregator's support for common queries is relatively comprehensive. In view of the space, I will not continue to list them one by one here. For more detailed syntax and functions when querying files by the aggregator, please refer to the document: http://doc.raqsoft.com.cn/esproc/func/dbquerysql.html#db_sql_

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.