jdbc动态操pg数据库

一、原生jdbc动态操作数据库

1、新建工具类

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.util.LinkedList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springblade.modules.datatrans.dto.DbTableCaptureDTO;

/**
 * @author Eric
 * @Time 2020年12月24日
 * @description 根据连接信息，操作数据库
 */
public class DataBaseUtil {

	private final static Logger LOGGER = LoggerFactory.getLogger(DataBaseUtil.class);

	/**
	* 根据驱动和连接信息获取conn
	*/
	public static Connection getConn(DbTableCaptureDTO dbTableCaptureDTO) {
		Connection conn = null;
		try {
//			Class.forName("org.postgresql.Driver");
//			String url = "jdbc:postgresql://127.0.0.1:5432/datatrans";
//			String user = "datatrans";
//			String pass = "datatrans";
			String forName = dbTableCaptureDTO.getForName();
			String url = dbTableCaptureDTO.getUrl();
			String userName = dbTableCaptureDTO.getUserName();
			String passwrod = dbTableCaptureDTO.getPassword();
			Class.forName(forName);
			conn = DriverManager.getConnection(url, userName, passwrod);
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	* 根据conn 和模式名（mysql没有模式，直接查表名即可）、表名获取所有元数据，
	* 元数据中包含了字段类型 长度等信息
	* 其中返回值rs 每个元素对应值如下：
	* 	2-数据库名称
	*	3-表名称
	*	4-字段名称   fieldName
	*	6-类型   fieldType
	*	7-长度
	*	12-注释   comment
	*	13-默认值   defaultValue
	*	18-是否为空  notNull
	*/
	public static ResultSet getMetaData(Connection conn, String schemaName, String tableName) {
		try {
			DatabaseMetaData metaData = conn.getMetaData();
			if (schemaName.equals("") || null == schemaName) {
				schemaName = "%";
			}
			ResultSet rs = metaData.getColumns(conn.getCatalog(), schemaName, tableName, null);
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	* 根据连接conn 模式名（mysql没有模式，直接查表名即可） 表名 
	* 获取此表所有主键 
	* 上面获取的元数据中没有是否为主键的信息，需要单独获取
	*/
	public static List<String> getPrimaryKeys(Connection conn, String schemaName, String tableName) {
		try {
			DatabaseMetaData metaData = conn.getMetaData();
			if (schemaName.equals("") || null == schemaName) {
				schemaName = "%";
			}
			ResultSet primaryKeys = metaData.getPrimaryKeys(conn.getCatalog(), schemaName, tableName);
			List<String> list = new LinkedList<String>();
			while (primaryKeys.next()) {
				list.add(primaryKeys.getString(4));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取数据库下的所有表名
	 */
	public static List<String> getTableNames(Connection conn) {
		List<String> tableNames = new LinkedList<>();
		ResultSet rs = null;
		try {
			// 获取数据库的元数据
			DatabaseMetaData db = conn.getMetaData();
			// 从元数据中获取到所有的表名
			rs = db.getTables(null, null, null, new String[] { "TABLE" });
			while (rs.next()) {
				tableNames.add(rs.getString(3));
			}
		} catch (SQLException e) {
			LOGGER.error("getTableNames failure", e);
		}
		return tableNames;
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	public static void closeConnection(Connection conn, ResultSet rs, ResultSet rs_dbTable) {

		if (conn != null) {
			try {
				rs_dbTable.close();
				rs.close();
				conn.close();
			} catch (SQLException e) {
				LOGGER.error("close connection failure", e);
			}
		}
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	public static void closeConnection(Connection conn, PreparedStatement ps) {

		if (conn != null) {
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				LOGGER.error("close connection failure", e);
			}
		}
	}
}

2、使用

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.springblade.modules.datatrans.dto.DbTableCaptureDTO;
import org.springblade.modules.datatrans.dto.DbTableCreateDTO;
import org.springblade.modules.datatrans.dto.DbTableDTO;
import org.springblade.modules.datatrans.dto.DbTablePageDTO;
import org.springblade.modules.datatrans.dto.DbTableUpdateDTO;
import org.springblade.modules.datatrans.dto.FieldCreateDTO;
import org.springblade.modules.datatrans.entity.DbTable;
import org.springblade.modules.datatrans.entity.Field;
import org.springblade.modules.datatrans.mapper.DbTableMapper;
import org.springblade.modules.datatrans.mapper.FieldMapper;
import org.springblade.modules.datatrans.service.IDbTableService;
import org.springblade.modules.datatrans.utils.DataBaseUtil;
import org.springblade.modules.datatrans.vo.DbTablePageVO;
import org.springblade.modules.datatrans.vo.DbTableVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

/**
 * 服务实现类
 *
 * @author chy created on 2020-10-29
 */
@Service
public class DbTableServiceImpl extends ServiceImpl<DbTableMapper, DbTable> implements IDbTableService {

	@Autowired
	DbTableMapper dbTableMapper;

	@Autowired
	FieldMapper fieldMapper;

	@Transactional
	@Override
	public boolean captureData(DbTableCaptureDTO dbTableCaptureDTO) {
		Connection conn = null;
		ResultSet rs = null;
		ResultSet rs_dbTable = null;
		try {
			conn = DataBaseUtil.getConn(dbTableCaptureDTO);

			// 存库ops_t_db_table
			DbTable dbTable = new DbTable();
			// 判断数据类型dataType是否为矢量或者栅格
			// 矢量类型有 point line lseg box path polygon circle
			rs_dbTable = DataBaseUtil.getMetaData(conn, dbTableCaptureDTO.getSchemaName(),
					dbTableCaptureDTO.getTableName());
			while (rs_dbTable.next()) {
				String dataType = rs_dbTable.getString(6);
				List<String> vectorList = Arrays.asList("point", "line", "lseg", "box", "path", "polygon", "circle");
				if (vectorList.contains(dataType)) {
					dbTable.setDataType("vector");
					break;
				} else {
					dbTable.setDataType("table");
				}
				dbTable.setTableCode(rs_dbTable.getString(3));
				dbTable.setTableName(rs_dbTable.getString(3));
			}
			dbTable.setDataId("1");// 暂时写死
			dbTable.setUpdateTime(new Date());
			dbTableMapper.insert(dbTable);
			rs = DataBaseUtil.getMetaData(conn, dbTableCaptureDTO.getSchemaName(), dbTableCaptureDTO.getTableName());
			List<String> primaryKeys = DataBaseUtil.getPrimaryKeys(conn, dbTableCaptureDTO.getSchemaName(),
					dbTableCaptureDTO.getTableName());
			while (rs.next()) {
				String dataType = rs.getString(6);
				// 存库ops_t_field
				Field field = new Field();
				field.setFieldCode(rs.getString(4));
				field.setFieldName(
						("".equals(rs.getString(12)) || rs.getString(12) == null || "null".equals(rs.getString(12)))
								? rs.getString(4)
								: rs.getString(12));
				field.setTableId(dbTable.getTableId());
				field.setFieldType("{\"fieldType\":\"" + dataType + "\",\"length\":\"" + rs.getString(7)
						+ "\",\"allowNull\":\""
						+ ((rs.getString(18).equals("NO") || rs.getString(18) == "NO") ? false : true)
						+ "\",\"isPrimaryKey\":\"" + ((primaryKeys.contains(rs.getString(4))) ? true : false) + "\"}");
				field.setDefaultValue(
						("null".equals(rs.getString(13)) || rs.getString(13) == null || "".equals(rs.getString(13)))
								? ""
								: rs.getString(13));
				field.setUpdateTime(new Date());
				fieldMapper.insert(field);
			}
		} catch (Exception e) {
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
			e.printStackTrace();
			return Boolean.FALSE;
		} finally {
			DataBaseUtil.closeConnection(conn, rs, rs_dbTable);
		}
		return Boolean.TRUE;
	}

	@Transactional
	@Override
	public boolean captureDatas(DbTableCaptureDTO dbTableCaptureDTO) {
		Connection conn = null;
		ResultSet rs_dbTable = null;
		ResultSet rs = null;
		try {
			conn = DataBaseUtil.getConn(dbTableCaptureDTO);
			List<String> tableNames = DataBaseUtil.getTableNames(conn);
			for (String tn : tableNames) {
				rs_dbTable = DataBaseUtil.getMetaData(conn, dbTableCaptureDTO.getSchemaName(), tn);
				// 存库ops_t_db_table
				DbTable dbTable = new DbTable();
				// 判断数据类型dataType是否为矢量或者栅格
				// 矢量类型有 point line lseg box path polygon circle
				while (rs_dbTable.next()) {
					String dataType = rs_dbTable.getString(6);
					List<String> vectorList = Arrays.asList("point", "line", "lseg", "box", "path", "polygon",
							"circle");
					if (vectorList.contains(dataType)) {
						dbTable.setDataType("vector");
					} else {
						dbTable.setDataType("table");
					}
					dbTable.setTableCode(rs_dbTable.getString(3));
					dbTable.setTableName(rs_dbTable.getString(3));
				}
				dbTable.setDataId("1");// 暂时写死
				dbTable.setUpdateTime(new Date());
				dbTableMapper.insert(dbTable);
				rs = DataBaseUtil.getMetaData(conn, dbTableCaptureDTO.getSchemaName(), tn);
				List<String> primaryKeys = DataBaseUtil.getPrimaryKeys(conn, dbTableCaptureDTO.getSchemaName(), tn);
				while (rs.next()) {
					String dataType = rs.getString(6);
					// 存库ops_t_field
					Field field = new Field();
					field.setFieldCode(rs.getString(4));
					field.setFieldName(
							("".equals(rs.getString(12)) || rs.getString(12) == null || "null".equals(rs.getString(12)))
									? rs.getString(4)
									: rs.getString(12));
					field.setTableId(dbTable.getTableId());
					field.setFieldType(
							"{\"fieldType\":\"" + dataType + "\",\"length\":\"" + rs.getString(7) + "\",\"notNull\":\""
									+ ((rs.getString(18).equals("NO") || rs.getString(18) == "NO") ? true : false)
									+ "\",\"isPrimaryKey\":\"" + (primaryKeys.contains(rs.getString(4)) ? true : false)
									+ "\"}");
					field.setDefaultValue(
							("null".equals(rs.getString(13)) || rs.getString(13) == null || "".equals(rs.getString(13)))
									? ""
									: rs.getString(13));
					field.setUpdateTime(new Date());
					fieldMapper.insert(field);
				}
			}
		} catch (Exception e) {
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
			e.printStackTrace();
			return Boolean.FALSE;
		} finally {
			DataBaseUtil.closeConnection(conn, rs, rs_dbTable);
		}
		return Boolean.TRUE;
	}

	@Transactional
	@Override
	public boolean createTable(DbTableCreateDTO dbTableCreateDTO) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			String schemaName = dbTableCreateDTO.getSchemaName();
			String tableName = dbTableCreateDTO.getDbTableCaptureDTO().getTableName();
			String sql = "DROP TABLE IF EXISTS \"" + schemaName + "\".\"" + tableName + "\";";
			sql += "CREATE TABLE \"" + schemaName + "\".\"" + tableName + "\" (";
			String sqlStr = "";
			List<FieldCreateDTO> fieldCreateDTOList = dbTableCreateDTO.toFieldCreateDTOList();
			for (int i = 0; i < fieldCreateDTOList.size(); i++) {
				sql += "\"" + fieldCreateDTOList.get(i).getFieldName() + "\" "
						+ fieldCreateDTOList.get(i).getFieldType();
				if (fieldCreateDTOList.get(i).isNotNull()) {
					sql += " NOT NULL";
				}
				if (null != fieldCreateDTOList.get(i).getDefaultValue()
						&& !("".equals(fieldCreateDTOList.get(i).getDefaultValue()))) {
					sql += " DEFAULT '" + fieldCreateDTOList.get(i).getDefaultValue() + "'";
				}
				if (fieldCreateDTOList.size() - 1 == i) {
					sql += " );";
				} else {
					sql += ",";
				}
				if (null != fieldCreateDTOList.get(i).getComment()
						&& !("".equals(fieldCreateDTOList.get(i).getComment()))) {
					sqlStr += "COMMENT ON COLUMN \"" + schemaName + "\".\"" + tableName + "\".\""
							+ fieldCreateDTOList.get(i).getFieldName() + "\" IS '"
							+ fieldCreateDTOList.get(i).getComment() + "';";
				}
				if (fieldCreateDTOList.get(i).isPrimaryKey()) {
					sqlStr += "ALTER TABLE \"" + schemaName + "\".\"" + tableName + "\" ADD CONSTRAINT \"" + tableName
							+ "_pkey\" PRIMARY KEY (\"" + fieldCreateDTOList.get(i).getFieldName() + "\");";
				}
			}
			sql += sqlStr;
			DbTableCaptureDTO dbTableCaptureDTO = dbTableCreateDTO.getDbTableCaptureDTO();
			conn = DataBaseUtil.getConn(dbTableCaptureDTO);// 获取连接，即连接到数据库
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			// 表创建成功后需要将相关信息同步写入ops_t_db_table和ops_t_field表
			// 直接调用捕获方法即可
			dbTableCaptureDTO.setTableName(tableName);
			dbTableCaptureDTO.setSchemaName(dbTableCreateDTO.getSchemaName());
			boolean captureData = captureData(dbTableCaptureDTO);
			if (captureData) {
				return Boolean.TRUE;
			} else {
				TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
				return Boolean.FALSE;
			}
		} catch (Exception e) {
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
			e.printStackTrace();
			return Boolean.FALSE;
		} finally {
			DataBaseUtil.closeConnection(conn, ps);
		}
	}

	@Transactional
	public boolean dropTable(DbTableCreateDTO dbTableCreateDTO) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			String schemaName = dbTableCreateDTO.getSchemaName();
			String tableName = dbTableCreateDTO.getDbTableCaptureDTO().getTableName();
			String sql = "DROP TABLE IF EXISTS \"" + schemaName + "\".\"" + tableName + "\";";
			DbTableCaptureDTO dbTableCaptureDTO = dbTableCreateDTO.getDbTableCaptureDTO();
			conn = DataBaseUtil.getConn(dbTableCaptureDTO);// 获取连接，即连接到数据库
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			return Boolean.TRUE;
		} catch (Exception e) {
			TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
			e.printStackTrace();
			return Boolean.FALSE;
		} finally {
			DataBaseUtil.closeConnection(conn, ps);
		}
	}

	@Transactional
	@Override
	public boolean updateTable(DbTableUpdateDTO dbTableUpdateDTO) {
		// 1、删除原来的信息
		// 2、新建
		String oldTableName = dbTableUpdateDTO.getDbTableCaptureDTO().getTableName();
		DbTableCreateDTO dbTableCreateDTO = new DbTableCreateDTO();
		DbTableCaptureDTO dbTableCaptureDTO = dbTableUpdateDTO.getDbTableCaptureDTO();
		dbTableCreateDTO.setDbTableCaptureDTO(dbTableCaptureDTO);
		dbTableCreateDTO.setSchemaName(dbTableUpdateDTO.getDbTableCaptureDTO().getSchemaName());
		boolean dropTable = dropTable(dbTableCreateDTO);
		int deleteById = dbTableMapper.deleteByTableId(Integer.parseInt(dbTableUpdateDTO.getTableId()));
		int delete = fieldMapper.deleteByTableId(dbTableUpdateDTO.getTableId());
		if (deleteById > 0 && delete > 0 && dropTable) {
			dbTableCreateDTO.setFieldCreateDTOJsonStr(dbTableUpdateDTO.getFieldUpdateDTOJsonStr());
			dbTableCaptureDTO.setTableName(dbTableUpdateDTO.getNewTableName());
			dbTableCreateDTO.setDbTableCaptureDTO(dbTableCaptureDTO);
			return createTable(dbTableCreateDTO);
		} else {
			return Boolean.FALSE;
		}
	}
}

二、hutool工具操作数据库

不介意引入第三方jar的推荐使用这个方式：

import cn.hutool.db.Db;
import cn.hutool.db.DbUtil;
import cn.hutool.db.ds.simple.SimpleDataSource;
/**conn={
    "databaseName": "datahub",
    "driver": "org.postgresql.Driver",
    "hostName": "127.0.0.1",
    "href": "jdbc:postgresql://",
    "password": "qQq314159@26",
    "port": 5432,
    "url": "jdbc:postgresql://127.0.0.1:5432/datahub",
    "username": "postgres"
}*/
SimpleDataSource simpleDataSource = new SimpleDataSource(conn.getString("url"), conn.getString("username"), conn.getString("password"));
Db use = DbUtil.use(simpleDataSource);
use.execute("select * from tablename");