自动生成代码工具 数据库工具类

                                                    自动生成代码工具 数据库工具类

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ijwt.element.Constants;

/**
 * @author InJavaWeTrust
 */
public class UtilDB {
	
	private static Connection conn      = null;
	private static Statement st         = null;
	private static ResultSet rs         = null;
	private static DatabaseMetaData dmd = null;
	private static Logger log = LoggerFactory.getLogger(UtilDB.class);
	
	private UtilDB() {}
	
	/**
	 * 连接数据库
	 * @return
	 */
	public static Connection connection() {
		try {
			Class.forName(Constants.DRIVER);
			conn = DriverManager.getConnection(Constants.URL, Constants.USER, Constants.PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			log.error(e.getMessage());
		}
		return conn;
	}
	
	/**
	 * 关闭链接
	 * @param rs
	 * @param st
	 * @param conn
	 */
	public static void release(ResultSet rs, Statement st, Connection conn) {
		try {
			if (null != rs) {
				rs.close();
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		try {
			if (null != st) {
				st.close();
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		try {
			if (null != conn) {
				conn.close();
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		}
	}
	
	/**
	 * 根据表名查询表中列注释
	 * @param tableName 表名
	 * @return List<String>
	 */
	public static List<String> getColumnCommentByTableName(String tableName) {
		List<String> columnComments = new ArrayList<>();
		connection();
		try {
			dmd = conn.getMetaData();
			rs = dmd.getColumns(null, dmd.getUserName(), tableName, null);
			while (rs.next()) {
				columnComments.add(rs.getString("REMARKS"));
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		release(rs, st, conn);
		return columnComments;
	}
	
	/**
	 * 根据表名查询表中列名
	 * @param tableName 表名
	 * @return List<String>
	 */
	public static List<String> getColumnNameByTableName(String tableName) {
		List<String> columnComments = new ArrayList<>();
		connection();
		try {
			dmd = conn.getMetaData();
			rs = dmd.getColumns(null, dmd.getUserName(), tableName, null);
			while (rs.next()) {
				columnComments.add(rs.getString("COLUMN_NAME"));
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		release(rs, st, conn);
		return columnComments;
	}
	
	/**
	 * 根据表名查询表中列类型
	 * @param tableName 表名
	 * @return List<String>
	 */
	public static List<String> getColumnTypeByTableName(String tableName) {
		List<String> tableNames = new ArrayList<>();
		connection();
		try {
			dmd = conn.getMetaData();
			rs = dmd.getColumns(null, dmd.getUserName(), tableName, null);
			while (rs.next()) {
				tableNames.add(rs.getString("TYPE_NAME"));
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		release(rs, st, conn);
		return tableNames;
	}
	
	/**
	 * 得到数据库中所有表名
	 * @return List<String>
	 */
	public static List<String> getAllTableName() {
		List<String> tableNames = new ArrayList<>();
		connection();
		try {
			dmd = conn.getMetaData();
			rs = dmd.getTables(null, dmd.getUserName(), null, null);
			while (rs.next()) {
				tableNames.add(rs.getString("TABLE_NAME"));
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		release(rs, st, conn);
		return tableNames;
	}
	
	/**
	 * 得到数据库表注释
	 * @param tableName 表名
	 * @return 表注释
	 */
	public static String getTableCommentByTableName(String tableName) {
		String comment  = "";
		connection();
		PreparedStatement pstmt = null;
		try {
			String sql = "SHOW TABLE STATUS WHERE NAME = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tableName);
			rs = pstmt.executeQuery();
			boolean has = rs.next();
			if(has) {
				comment = rs.getString("Comment");
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			try {
				if (null != pstmt) {
					pstmt.close();
				}
			} catch (SQLException e) {
				log.error(e.getMessage());
			}
		}
		release(rs, st, conn);
		return comment;
	}
	
	public static void main(String[] args) {
		String list = getTableCommentByTableName("dept");
		System.out.println(list);
	}
}