Little knowledge series: query the meta information of database data

Introduction

I believe everyone is familiar with the operation of the database in java. JDK provides the java.sql package to standardize various operations on the database. Our most commonly used operation is to get data from the ResultSet of the database. In fact, there is a very useful class in this package called ResultSetMetaData. You can use this class to get the metadata of the query data. Let's take a look.

Use ResultSet

java.sql.ResultSet is a general specification used to represent the data obtained from the database.

Generally speaking, we create Statement through connection, and then get it by executing query statement:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_UPDATABLE);
         ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");

The resultSet provides various getter methods to obtain various data in the result set. It can be obtained by index or column name.

Of course, it is more efficient to use index, and index starts from 1. If the data is obtained by column name, the column name passed in is case-insensitive. If there are multiple matching classes in the result data, the first matching column will be returned.

During the get process, the JDBC driver will try to convert the database type of the result data to the corresponding java type.

JDBC 2.0 API, ResultSet can also be updated and inserted. Maybe we rarely do this. Generally, we first construct the data and insert it directly.

First look at the update operation:

         rs.absolute(5); // 将游标移动到第5行
         rs.updateString("SITE", "www.flydean.com"); // 将SITE更新为www.flydean.com
         rs.updateRow(); // 更新到数据库中

Look at the insert operation again:

  
         rs.moveToInsertRow(); // 将游标移动到插入行
         rs.updateString(1, "www.flydean.com"); // 将插入行的第一列更新为www.flydean.com
         rs.updateInt(2,35); // 更新第二列为35
         rs.updateBoolean(3, true); // 更新第三列为true
         rs.insertRow();
         rs.moveToCurrentRow();

Use ResultSetMetaData

With ResultSet, we can get the metadata of the result set through its getMetaData method.

What is metadata? Metadata is also called Metadata, which is data used to describe data attributes.

   ResultSetMetaData getMetaData() throws SQLException;

Give a specific example:

       ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
       ResultSetMetaData rsmd = rs.getMetaData();
       int numberOfColumns = rsmd.getColumnCount();
       boolean b = rsmd.isSearchable(1);

ResultSetMetaData provides many very useful metadata detection methods:

We can get a lot of meaningful data such as column name, type, field length, whether it is empty or not.

What is the use of this metadata?

Through metadata, we can get the description file of the database, which can automatically create the mapping relationship of the corresponding database table, thereby reducing the input of manual code, which is very convenient.

Friends who have used MybatisPlus may know that it provides an AutoGenerator that can automatically generate mapper objects and corresponding xml files, which is very easy to use, and you can try it.

Summarize

The above is the introduction of ResultSet and ResultSetMetaData, have you learned it?

This article has been included in http://www.flydean.com/02-db-resultsetmetadata/

The most popular interpretation, the most profound dry goods, the most concise tutorial, and many tips you don't know are waiting for you to discover!

Welcome to pay attention to my official account: "Program those things", know technology, know you better!