一、ORM物件關系映射

• 資料庫中的一個表：Java中的一個類
• 資料庫中的一個欄位：Java中的一個屬性
• 資料表中的一條資料：Java中的一個物件

二、表結構和表對應的物體類

CREATE TABLE `databaseInfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `database_name` varchar(100) NOT NULL COMMENT '資料庫名稱',
  `table_name` varchar(100) NOT NULL COMMENT '表名',
  `table_comment` varchar(300) DEFAULT NULL COMMENT '表的描述',
  `table_rows` bigint(20) DEFAULT 0 COMMENT '表的行數',
  `table_key` varchar(100) NOT NULL COMMENT '表的主鍵',
  `create_time` datetime DEFAULT NULL COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_databasename_tablename` (`database_name`,`table_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='源資料庫表資訊';

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;

import java.io.Serializable;

/**
 * 資料庫元資訊
 */
@Data
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@ToString
public class DatabaseInfo implements Serializable {

    /**
     * 資料庫名稱
     */
    private String databaseName;

    /**
     * 資料表全稱,格式：dbName.tableName
     */
    private String tableName;

    /**
     * 表的描述
     */
    private String tableComment;

    /**
     * 表的行數
     */
    private Long tableRows;

    /**
     * 表的主鍵
     */
    private String tableKey;
}

三、查詢mysql資料庫元資訊ORM映射到物體類中

    public DatabaseInfo get() throws SQLException, IOException, ClassNotFoundException {

        DatabaseInfo databaseInfo = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.獲取連接

            conn = JdbcUtils.getConnection();
            //2.獲取PreparedStatement，用于發送sql
            String sql = "select t.TABLE_SCHEMA as DATABASE_NAME,CONCAT_WS('.',t.TABLE_SCHEMA,t" +
                    ".TABLE_NAME) as TABLE_NAME,\n" +
                    "t.TABLE_COMMENT,t.TABLE_ROWS,k.COLUMN_NAME as TABLE_KEY \n" +
                    "from INFORMATION_SCHEMA.TABLES as t\n" +
                    "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k\n" +
                    "on k.TABLE_NAME=t.TABLE_NAME and CONSTRAINT_NAME = 'PRIMARY' and t.TABLE_SCHEMA " +
                    "= ?";
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            ps.setString(1, "dw");

            //4.執行sql,獲取ResultSet
            rs = ps.executeQuery();

            //5.獲取結果集中的資料
            if (rs.next()) {
                String datbase_name = rs.getString("DATABASE_NAME");
                String table_name = rs.getString("TABLE_NAME");
                String table_comment = rs.getString("TABLE_COMMENT");
                Long table_rows = rs.getLong("TABLE_ROWS");
                String table_key = rs.getString("TABLE_KEY");

                //6.將結果集中獲取的散列資料封裝進物件
                databaseInfo = new DatabaseInfo(datbase_name, table_name, table_comment, table_rows,
                        table_key);
            }
        }catch (Exception e){
                e.printStackTrace();
            }finally {
            //7.關閉連接
                JdbcUtils.release(conn,ps,rs);
            }
        return databaseInfo;

    }

四、測驗類

    @Test
    public void test4() throws SQLException, IOException, ClassNotFoundException {
        DatabaseInfo databaseInfo = get();
        if(databaseInfo == null){
            System.out.println("無法獲取資料庫元資訊");
        }else{
            System.out.println("查詢到指定mysql資料庫元資訊");
            System.out.println(databaseInfo);
        }
    }

五、完整代碼

import com.bigdata.plus.Entity.DatabaseInfo;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/*
    使用PrepareStatement完成增刪改
*/
public class PrepareStatementTest {

    @Test
    public void test4() throws SQLException, IOException, ClassNotFoundException {
        DatabaseInfo databaseInfo = get();
        if(databaseInfo == null){
            System.out.println("無法獲取資料庫元資訊");
        }else{
            System.out.println("查詢到指定mysql資料庫元資訊");
            System.out.println(databaseInfo);
        }
    }

    public DatabaseInfo get() throws SQLException, IOException, ClassNotFoundException {

        DatabaseInfo databaseInfo = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            //1.獲取連接

            conn = JdbcUtils.getConnection();
            //2.獲取PreparedStatement，用于發送sql
            String sql = "select t.TABLE_SCHEMA as DATABASE_NAME,CONCAT_WS('.',t.TABLE_SCHEMA,t" +
                    ".TABLE_NAME) as TABLE_NAME,\n" +
                    "t.TABLE_COMMENT,t.TABLE_ROWS,k.COLUMN_NAME as TABLE_KEY \n" +
                    "from INFORMATION_SCHEMA.TABLES as t\n" +
                    "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k\n" +
                    "on k.TABLE_NAME=t.TABLE_NAME and CONSTRAINT_NAME = 'PRIMARY' and t.TABLE_SCHEMA " +
                    "= ?";
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            ps.setString(1, "dw");

            //4.執行sql,獲取ResultSet
            rs = ps.executeQuery();

            //5.獲取結果集中的資料
            if (rs.next()) {
                String datbase_name = rs.getString("DATABASE_NAME");
                String table_name = rs.getString("TABLE_NAME");
                String table_comment = rs.getString("TABLE_COMMENT");
                Long table_rows = rs.getLong("TABLE_ROWS");
                String table_key = rs.getString("TABLE_KEY");

                //6.將結果集中獲取的散列資料封裝進物件
                databaseInfo = new DatabaseInfo(datbase_name, table_name, table_comment, table_rows,
                        table_key);
            }
        }catch (Exception e){
                e.printStackTrace();
            }finally {
            //7.關閉連接
                JdbcUtils.release(conn,ps,rs);
            }
        return databaseInfo;

    }
}

六、運行測驗類輸出如下所示