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

I believe that the implementation of dynamic columns has perplexed many people, a large amount of data, multi-field loading will be very time-consuming, and the data can not be truly dynamic and flexible. The existing ways are realized by hiding the change of direction and so on.

So how to solve it? Here to share the implementation of the sail soft report designer FineReport, combined with the relevant content encountered in the actual work.

My local commonly used database is MYSQL, so I know a little bit, so the implementation is also based on mysql. First, take a look at the use of the information_schema database that comes with MySQL.

When you install or use MYSQL, you will find that in addition to your own installed database, there is also an information_schema database. What is the use of the information_schema database? friends who use WordPress blogs may wonder, is it the database added by the installation template? After reading this article, you will know something about information_schema database.

The information_schema database is native to MySQL and provides a way to access database metadata. What is metadata? Metadata is data about data, such as database or table names, data types of columns, or access permissions. Sometimes other terms used to describe this information include "data dictionary" and "system catalog".

In MySQL, think of information_schema as a database, or rather an information database. It holds information about all other databases maintained by the MySQL server. Such as the name of the database, the table of the database, the data type and access of the table column, etc. In INFORMATION_SCHEMA, there are several read-only tables. They are actually views, not basic tables, so you won't be able to see any files associated with them.

Information_schema database table description:

SCHEMATA table: provides information about all databases in the current mysql instance. This table is taken from the results of show databases.

TABLES table: provides information about the tables in the database (including views). Detailed description of a table belongs to which schema, table type, table engine, creation time and other information. This table is taken from the results of show tables from schemaname.

COLUMNS table: provides column information in the table. Details all the columns of a table and the information for each column. This table is taken from the results of show columns from schemaname.tablename.

STATISTICS table: provides information about table indexes. This table is taken from the results of show index from schemaname.tablename.

USER_PRIVILEGES (user permissions) table: gives information about full permissions. This information is derived from the mysql.user authorization table. Is a non-standard table.

SCHEMA_PRIVILEGES (Scheme permissions) table: gives information about scheme (database) permissions. This information is from the mysql.db authorization table. Is a non-standard table.

TABLE_PRIVILEGES (Table permissions) Table: gives information about table permissions. This information is derived from the mysql.tables_priv authorization table. Is a non-standard table.

COLUMN_PRIVILEGES (column permissions) table: gives information about column permissions. This information is derived from the mysql.columns_priv authorization table. Is a non-standard table.

CHARACTER_SETS (character set) table: provides information about the character sets available to the mysql instance. This table is taken from the SHOW CHARACTER SET result set.

COLLATIONS table: provides comparative information about each character set.

COLLATION_CHARACTER_SET_ application table: indicates the character set that can be used for proofreading. These columns are equivalent to the first two display fields of SHOWCOLLATION.

TABLE_ constraints table: describes tables that have constraints. And the constraint type of the table.

KEY_COLUMN_ USAGE table: describes key columns with constraints.

ROUTINES table: provides information about storage subroutines (stored procedures and functions). At this point, the ROUTINES table does not contain custom functions (UDF). The column named "mysql.proc name" indicates the mysql.proc column corresponding to the INFORMATION_SCHEMA.ROUTINES table.

VIEWS table: gives information about the views in the database. Show views permission is required, otherwise the view information cannot be viewed.

TRIGGERS table: provides information about triggers. You must have super permission to view the table

Extended application

View the number of records in a database table

Selecttable_schema,table_name,table_rows from tables where TABLE_SCHEMA = 'database name' order by table_rowsdesc

View the space occupied by the database

Selectconcat (round (sum (data_length/1024/1024), 2), 'MB') as data_length_MB

Concat (round (sum (index_length/1024/1024), 2), 'MB') as index_length_MB

Frominformation_schema.tables where

Table_schema=' database name'

View the space occupied by a table

Selectconcat (truncate (sum (data_length) / 1024 sum 1024), 'MB') as data_size

Concat (truncate (sum (max_data_length) / 1024 sum 1024), 'MB') as max_data_size

Concat (truncate (sum (data_free) / 1024 sum 1024), 'MB') as data_free

Concat (truncate (sum (index_length) / 1024 sum 1024), 'MB') as index_size

Frominformation_schema.tables where TABLE_NAME = 'table name'

After reading it, I believe I may still be a little confused. That is to say, there is a system library information_schema in mysql. This library stores the database instance name, table name, table comments, field names, field comments, etc., of the mysql database. The existence of this library provides a basis for the implementation of our dynamic columns. (other databases such as oracle, sqlserver, db2, and so on also exist similarly.)

Then let's get back to the subject. I set up a local test library reporttest, test table report. Create a new library reporttest in the mysql library (or use an existing library, but if you use one that already exists, the internal sql content of the report needs to be changed. So it's best to create a new one), and then run the reporttest.sql file I provided with a tool like navicat, you can create the table and import the data, and then refresh it, and the data is ready.

The designer creates a data connection test, fills in the corresponding database information, and the test succeeds, that is, OK.

At this point, the library name is reporttest, the table name is report, and the data connection is test. At this point, directly preview the dynamic column implementation. CPT.

You can see the effect very clearly. If you choose which column, it will only query that column and show only the data of that column. This is the implementation scheme of dynamic column. According to my logic, you can replace it with your own data connection library and table data for testing and using experience!

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.