Database Meta Data metadata

　This article describes the knowledge of the metadata (MetaData) of the database. Metadata is particularly important in building frameworks and architectures, and next we are copying the open Source database tool class Dbutils to use the metadata of the database to create a custom JDBC framework.

In front of us using JDBC to process the database interface mainly has three, namely connection,preparedstatement and resultset these three, but for these three interfaces, also can obtain the different kind of metadata, obtains some database information through these metadata classes.

Metadata (MetaData), which is the data that defines the data. For example, it is as if we want to search for a song (the song itself is data), and we can search by song name, author, album and other information, then these song name, author, album and so on is the song's metadata. Therefore, the metadata of the database is some data indicating the database information.

① the DatabaseMetaData object is obtained by the GetMetaData () method of the Connection object.

② the parametermetadata object is obtained by the Getparametermetadata () method of the PreparedStatement object.

③ the resultsetmetadata object is obtained by the GetMetaData () method of the ResultSet object.

These three types of metadata objects are briefly described below, where the database Tools class in the example can refer to the jdbcutils defined in the learning of JDBC Operations Database (2), or the previous few on connection pooling such as dbcp of the open source database connection pool or the Open source database connection pool c3p0 "and so on defined in the Jdbcutils, here the way to get the connection is not affected by this article, as long as we can get connection object.

First, DatabaseMetaData

DatabaseMetaData is obtained by the connection object through the GetMetaData method, the main encapsulation is to the database itself some general comprehensive information, such as the product name of the database, the database version number, the database URL, whether to support transactions and so on, More information can be obtained, specifically refer to the DatabaseMetaData API documentation.

Here are some common methods for DatabaseMetaData:

• Getdatabaseproductname: Gets the product name of the database

• Getdatabaseproductname: Gets the version number of the database

• GetUserName: Gets the user name of the database

• GetURL: Gets the URL of the database connection

• Getdrivername: Gets the drive name of the database

• DriverVersion: Gets the driver version number of the database

• IsReadOnly: See if the database allows only read operations

• Supportstransactions: Check whether the database supports transactions

Example 1:

Simple access to some commonly used database synthesis information.

1  Public voidTestdatabasemetadata ()throwsSQLException {2Connection conn =jdbcutils.getconnection ();3DatabaseMetaData Dbmetadata =Conn.getmetadata ();4         //Get database Product name5String ProductName =dbmetadata.getdatabaseproductname ();6         //Get Database version number7String productversion =dbmetadata.getdatabaseproductversion ();8         //Get database user name9String UserName =dbmetadata.getusername ();Ten         //get database connection URL OneString Userurl =Dbmetadata.geturl (); A         //Get Database driver -String drivername =dbmetadata.getdrivername (); -         //Get Database driver version number theString driverversion =dbmetadata.getdriverversion (); -         //To see if the database allows read operations -         BooleanIsReadOnly =dbmetadata.isreadonly (); -         //To see if a database supports transactional operations +         BooleanSupportstransactions =dbmetadata.supportstransactions (); -}

View Code

The console results are displayed as follows:

　　

Second, Parametermetadata

　　Parametermetadata is obtained by the PreparedStatement object through the Getparametermetadata method, mainly providing some information for the PreparedStatement object and its precompiled SQL command statement. , such as "INSERT into account (Id,name,money) VALUES (?,?,?)" Such a precompiled SQL statement, Parametermetadata can provide the number of placeholder parameters, get the specified position placeholder SQL type, and so on, the function is more, here is not listed, detailed see the API documentation about Parametermetadata.

Here are some common methods for Parametermetadata:

• GetParameterCount: Gets the number of placeholder parameters in a precompiled SQL statement

　　In my opinion, the Parametermetadata object can only use the GetParameterCount () method to get the number of arguments.

　　Note : Parametermetadata Many methods MySQL is not friendly to support, such as the Getparametertype method that gets the SQL type of the specified parameter, if the database-driven connection URL is simply "jdbc:mysql:/ /localhost:3306/jdbcdemo "Then MyEclipse throws the SqlException exception, you have to modify the URL to" Jdbc:mysql://localhost:3306/jdbcdemo? Generatesimpleparametermetadata=true "just fine. but like Getparametertype and so on and other methods are not very useful, because as in the following example, these methods seem to only consider all the parameters are string (VARCHAR) type .

Example 2:

Simply gets the metadata information for the parameters in the precompiled SQL, but finds that only the number of parameters is useful:

Create a user table in the database and create the following SQL script:

    Create Table User (        intprimarykey,        varchar(+),         int     );

The following is a demonstration of the Parametermetadata object for the user table above:

1  Public voidTestparametermetadata ()throwsSQLException {2Connection conn =NULL;3PreparedStatement st =NULL;4ResultSet rs =NULL;5         Try{6conn =jdbcutils.getconnection ();7String sql = "INSERT into user (Id,name,age) VALUES (?,?,?)";8St =conn.preparestatement (SQL);9St.setint (1, 1);TenSt.setstring (2, "Ding"); OneSt.setint (3, 25); A              -Parametermetadata Parammetadata =St.getparametermetadata (); -             //get the number of arguments the             intParamCount =Parammetadata.getparametercount (); -             //gets the SQL type of the specified argument as a string . -String paramtypename = parammetadata.getparametertypename (1); -             //returns the SQL type of the specified argument to the field representation of the Java.sql.Types class, where there is a problem +             intParamtype = Parammetadata.getparametertype (1); -             //returns the Java fully qualified name of the specified parameter type, where there is a problem +String paramclassname = parammetadata.getparameterclassname (1); A             //returns the modulus of the specified parameter, where there is a problem at             intParammode = Parammetadata.getparametermode (1); -             //returns the column size of the specified parameter, where there is a problem -             intPrecision = parammetadata.getprecision (1); -             //returns the number of digits to the right of the decimal point of the specified parameter, where there is a problem -             intScale = Parammetadata.getscale (1); -}

View Code

Note: The fully qualified name refers to the full Java name of the type, including the package name and type.

The results shown in the console are as follows:

　　

Because our SQL statement is "INSERT into user (Id,name,age) VALUES (?,?,?)", all of the information we use Parametermetadata queries, in addition to the number of parameters, is the information that queries the first parameter, that is, " ID column, and this "id" column we created is int integer, but the query result with Parametermetadata is displayed as a string type, so I have doubts about the functionality of Parametermetadata.

　Therefore, use the parameter metadata later Parametermetadata try to use its Getparamtercount () method to get the number of arguments, and use it sparingly for other methods of the object.

Third, ResultSetMetaData

　ResultSetMetaData is obtained by the ResultSet object through the GetMetaData method, primarily for some of the information provided in the result set object resultset obtained by the SQL script command executed by the database, such as the number of columns in the result set, Specifies the name of the column, the SQL type of the specified column, and so on, which can be said to be an important object for the framework. For additional specific features and methods of the result set metadata object, consult the API documentation for ResultSetMetaData.

Here are some common methods for ResultSetMetaData:

• getColumnCount: Gets the number of column items in the result set

• Getcolumntype: Gets the SQL type of the specified column corresponding to the field of the types class in Java

• Getcolumntypename: Gets the SQL type of the specified column

• GetClassName: Gets the specified column SQL type corresponding to the type in Java (package name plus class name)

Example 3:

Simply get the metadata in the result set object.

We create a table, which is the same as Example 2, and we will add 2 data items to this table:

    Create Table User(IDint Primary Key, namevarchar( +), ageint    ); Insert  into User(Id,name,age)Values(1,'Ding', -); Insert  into User(Id,name,age)Values(2,'LRR', -);

Next we'll get ResultSetMetaData in the program and use its methods:

1  Public voidTestresultsetmetadata ()throwsSQLException {2Connection conn =NULL;3PreparedStatement st =NULL;4ResultSet rs =NULL;5         Try{6conn =jdbcutils.getconnection ();7String sql = "SELECT * from User";8St =conn.preparestatement (SQL);9             Tenrs =st.executequery (); OneResultSetMetaData Resultmetadata =Rs.getmetadata (); A             //get the number of columns in the result set -             intColumnCount =Resultmetadata.getcolumncount (); -             //gets the name of the specified column theString columnName = resultmetadata.getcolumnname (1); -             //gets the SQL type of the specified column that corresponds to the field of the Java.sql.Types class -             intColumnType = Resultmetadata.getcolumntype (1); -             //gets the SQL type of the specified column +String columntypename = resultmetadata.getcolumntypename (1); -             //gets the type of the specified column SQL type that corresponds to Java +String classname= Resultmetadata.getcolumnclassname (1); A             //gets the name of the table that contains the specified column atString tableName = resultmetadata.gettablename (1); -}

View Code

The results shown in the console are as follows:

　　

You can see that the result set metadata Object ResultSetMetaData method, the number of columns obtained is really 3 (id,name,age), the first column is indeed an ID entry, and this ID entry is indeed an integer type.

Through this article we learned about the database in the database, precompiled SQL parameters and result set of three different metadata objects, metadata is the data defined data, through the metadata, we can use these definitions in the next article to build a custom JDBC framework.

Reference blog:

http://luyao123127.iteye.com/blog/678765

http://huweiyi.iteye.com/blog/2123295

http://jiauwu.iteye.com/blog/1307617

Http://www.cnblogs.com/xdp-gacl/p/4006830.html

Database Meta Data metadata