Atitit. design differences between Database-catalog and schema and obtaining all database tables in actual use of java jdbc p.

Source: Internet
Author: User
Atitit. database-the difference between catalog and schema design and the actual use of obtaining all database tables in the database java jdbc p. atitit. database ----- differences between catalog and schema design and how to obtain all database tables in the database using java jdbc php c #. net

1.-differences between catalog and schema design 1

2. methods for obtaining database metadata 2

2. 1. directly read the information_schema of mysql, SysDatabases 3 of mssql

2. use common interfaces such as jdbc 3

. Use a separate driver interface provided by the database, such as mysql_list_dbs 3 of mysql.

3. get the list of all databases in mysql. getCatalogs 3

3. 1. traverse all databases in the database 3

3. 2. Php version mysql_list_dbs () 4

3. 3 .. Net version SysDatabases 4

3. 4 .. Net version // use OleDbConnection's GetOleDbSchemaTable to obtain the database structure 4

4. get all tables of a database getTables 4

4. 1. Php implementation mysql_list_tables 5

4. 2 .. Read SysObjects table 5 from version N et

5. refer to 5

1.-differences between catalog and schema designs

According to the SQL standard, both Catalog and Schema are abstract concepts in the SQL environment. they can be understood as a layer in a container or database object namespace, it is mainly used to solve naming conflicts. Conceptually, a database system contains multiple Catalog S, each of which contains multiple schemas, and each Schema contains multiple database objects (tables, views, fields, etc ), in turn, a database object must belong to a Schema, and the Schema must belong to a Catalog, so that we can get the fully qualified name of the database object to solve the naming conflict problem; for example, the fully qualified name of the database object table can be expressed as the Catalog name. schema name. table name. Note that the SQL standard does not require that the fully qualified names of each database object be unique, just like domain names, each IP address can have multiple domain names.

From the implementation point of view, the support and implementation methods for Catalog and Schema vary widely in various database systems. for specific problems, refer to the specific product manual, A simple and common implementation method is to use the database name as the Catalog name and the user name as the Schema name. for details, see the following table:

Table 1 common databases

Supplier

Catalog support

Schema support

Oracle

Not supported

Oracle User ID

MySQL

Not supported

Database name

Ms SQL Server

Database name

The object owner name, which has changed since version 2005.

DB2

The Catalog part is omitted when the database object is specified.

Catalog owner name

Sybase

Database name

Database owner name

Informix

Not supported

No

PointBase

Not supported

Database name

Author: Nickname: old Wow's Paw (full name: Attilax akbar al rapanui Attila AKBA Arla Panui) Name: Ai long, EMAIL: 1466519819@qq.com

Reprinted please indicate Source: http://www.cnblogs.com/attilax/

The following describes how to read table information from the database.

It depends on a DatabaseMetaData class. this object can be obtained from a database connection. With it, everything is fine. you just need to know what to ask:

DatabaseMetaData databaseMetaData = conn. getMetaData ();

// Obtain all the ResultSet tableSet = databaseMetaData. getTables (null, "%", "%", new String [] {"TABLE "});

// Obtain the tableName table column information ResultSet columnSet = databaseMetaData. getColumns (null, "%", tableName, "% ");

2. methods for obtaining database metadata: 2.1. directly read the information_schema of the meta-table mysql, SysDatabases 2.2 of mssql. use common interfaces such as jdbc 2.3. use a separate driver interface provided by the database, such as mysql's mysql_list_dbs 3. obtains the list of all databases in mysql. getCatalogs 3.1. traverse all databases in the database

[

{"TABLE_CAT": "information_schema "},

{"TABLE_CAT": "8kbl "},

{"TABLE_CAT": "atiposdb "},

{"TABLE_CAT": "cyar "},

{"TABLE_CAT": "ecmdb "},

{"TABLE_CAT": "hxtaxi "},

{"TABLE_CAT": "iwbm2 "},

{"TABLE_CAT": "iwmshop "},

{"TABLE_CAT": "iwmshopnow "},

{"TABLE_CAT": "limesurvey "},

{"TABLE_CAT": "mysql "},

{"TABLE_CAT": "performance_schema "},

{"TABLE_CAT": "shopedb "},

{"TABLE_CAT": "shopnc "},

{"TABLE_CAT": "test "},

{"TABLE_CAT": "timerdb "},

{"TABLE_CAT": "vod2 "},

{"TABLE_CAT": "wechatdb "},

{"TABLE_CAT": "wordpress "},

{"TABLE_CAT": "wxb_site_new "},

{"TABLE_CAT": "wxmiqi "},

{"TABLE_CAT": "zuche5 "}

]

GetSchemas is empty ..

3.2. Php version mysql_list_dbs ()

$ Dbs = mysql_list_dbs (); // call the mysql_list_dbs function

3.3 .. Net version SysDatabases

1. obtain all database names: (1) Select Name FROM Master. dbo. SysDatabases orDER BY Name

3.4 .. Net version // use OleDbConnection's GetOleDbSchemaTable to obtain the database structure 4. get all the tables of a database getTables

DatabaseMetaData dbmd = dbx. getConnection (). getMetaData ();

// DatabaseMetaData. getColumns (localCatalog, localSchema,

// LocalTableName, null );

ResultSet rs = dbmd. getTables ("atiposdb", "%", "%", new String [] {"TABLE "});

[

{

"TABLE_NAME": "applications ",

"REMARKS ":"",

"TABLE_TYPE": "TABLE ",

"TABLE_SCHEM": null,

"TABLE_CAT": "atiposdb"

},

{

"TABLE_NAME": "attribute ",

"REMARKS ":"",

"TABLE_TYPE": "TABLE ",

"TABLE_SCHEM": null,

"TABLE_CAT": "atiposdb"

},

4.1. Php implementation mysql_list_tables

(PHP 3, PHP 4, PHP 5)

Mysql_list_tables -- list tables in the MySQL database

4.2 .. Read the SysObjects table in version N et.

2. obtain all table names: (1) Select Name FROM SysObjects Where XType = 'U' orDER BY Name

5. Reference

Asp.net: obtain all the database names of SQL, all table names, all field names, column descriptions-XMM_1030 columns-blog channel-CSDN.NET.htm

. NET method for obtaining the names of all tables in the database (reprinted)-guoxuefeng-blog. htm

Brief History of ODBC, OLEDB, ADO, and ADO. Net evolution-BobLiu-bo .htm

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.