devbox
weixin_40725706
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

jdbc动态操pg数据库_jdbc pg

作者：weixin_40725706 | 2024-04-29 22:12:21

赞

踩

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);
			}
		}
	}
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154

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;
		}
	}
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242
  • 243
  • 244
  • 245
  • 246
  • 247
  • 248
  • 249
  • 250
  • 251
  • 252
  • 253
  • 254
  • 255
  • 256
  • 257
  • 258
  • 259
  • 260
  • 261
  • 262
  • 263
  • 264
  • 265
  • 266
  • 267
  • 268
  • 269
  • 270
  • 271
  • 272
  • 273
  • 274
  • 275
  • 276
  • 277
  • 278
  • 279
  • 280
  • 281
  • 282

二、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");

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/510050
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号