JDBC 封装

最新推荐文章于 2024-08-16 23:08:28 发布

Sand_ink

最新推荐文章于 2024-08-16 23:08:28 发布

阅读量37

点赞数

文章标签： java jvm 数据库

本文链接：https://blog.csdn.net/qq_48992973/article/details/128353114

版权

该代码示例展示了如何使用Java的JDBC进行数据库连接，读取配置文件获取数据库连接参数，执行SQL语句。方法`jdbcToSQL`根据参数决定执行DDL（数据定义语言）或DQL（数据查询语言）。当执行DQL时，它返回查询结果并打印；当执行DDL时，检查执行结果并进行事务控制。在出现异常时，会进行回滚操作并关闭连接。

摘要由CSDN通过智能技术生成

JDBC 应用

//读取config文件，config文件定义数据库地址，数据库用户名及密码
private static List<String> getConfig(String configPath) {
	ResourceBundle bundle = ResourceBundle.getBundle(configPath);
	List<String> configList = new ArrayList<>();
	configList.add(bundle.getString("url"));	//jdbc:mysql://192.168.137.116:3306/zm?charactor
	configList.add(bundle.getString("username"));	//root
	configList.add(bundle.getString("password"));	//123
	return configList;	//["jdbc:mysql://192.168.137.116:3306/zm?charactor","root","123"]
}

public void jdbcToSQL(String configPath, boolean flag, String[] sqls, Object[]... objs) {
	if (sqls.length != objs.length) try {
		throw new CustomException("sql语句和传值数量不一致");
	} catch (CustomException e) {
		e.printStackTrace();
	}
	List<String> config = getConfig(configPath);
	Connection conn = null;
	
	try {
		conn = DriverManager.getConnection(config.get(0), config.get(1), config.get(2));
		conn.setAutoCommit(false);
		// 主要代码
		List<Map<String, Object>> maps;
		if (flag) {
			maps = jdbcToDDL(conn, sqls, objs);
			for (Map<String, Object> map : maps) {
				map.forEach((k, v) -> System.out.println(k + "=" + v));
				if ((int) map.get("result") <= 0) {
					throw new CustomException("sql语句执行无结果，直接回滚，数据库将不会发生变化");
				}
			}
		} else {
			maps = jdbcToDQL(conn, sqls, objs);
			for (Map<String, Object> map : maps) {
				map.forEach((k, v) -> System.out.println(k + "=" + v));
				List<List<String>> data = (List<List<String>>) map.get("data");
	
				for (List<String> strings : data) {
					System.out.println(strings);
				}
			}
	
		}
	
	
		conn.commit();
	} catch (SQLException | CustomException e) {
		e.printStackTrace();
		try {
			if (conn != null) {
				conn.rollback();
			}
		} catch (SQLException ex) {
			throw new RuntimeException(ex);
		}
	} finally {
	
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
private static List<Map<String, Object>> jdbcToDQL(Connection conn, String[] sqls, Object[][] objs) {
	List<Map<String, Object>> sqlList = new ArrayList<>();
	PreparedStatement ps = null;
	for (int i = 0; i < sqls.length; i++) {
		try {
			ps = conn.prepareStatement(sqls[i]);
			for (int j = 0; j < objs[i].length; j++) {
				if (objs[i][j] instanceof Integer) {
					ps.setInt(j + 1, (Integer) objs[i][j]);
				} else if (objs[i][j] instanceof String) {
					ps.setString(j + 1, (String) objs[i][j]);
				} else {
					throw new CustomException("sql'?'传输值格式不正确");
				}
			}
			ResultSet res = ps.executeQuery();
			ResultSetMetaData metaData = res.getMetaData();
			int columnCount = metaData.getColumnCount();
	
			Map<String, Object> sqlResult = new LinkedHashMap<>();
			List<String> columnList = new ArrayList<>();
			for (int j = 1; j <= columnCount; j++) {
				columnList.add(metaData.getColumnName(j));
			}
			sqlResult.put("column", columnList);
			List<List<String>> dataList = new ArrayList<>();
			while (res.next()) {
				List<String> data = new ArrayList<>();
				for (int j = 1; j <= columnCount; j++) {
					data.add(res.getString(j));
				}
				dataList.add(data);
			}
			sqlResult.put("data", dataList);
			sqlList.add(sqlResult);
	
		} catch (SQLException | CustomException e) {
			throw new RuntimeException(e);
		}
	
	}
	if (ps != null) {
		try {
			ps.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	return sqlList;
}
private static List<Map<String, Object>> jdbcToDDL(Connection conn, String[] sqls, Object[][] objs) {
	List<Map<String, Object>> sqlList = new ArrayList<>();
	PreparedStatement ps = null;
	for (int i = 0; i < sqls.length; i++) {
		Map<String, Object> sqlResult = new LinkedHashMap<>();
		try {
			ps = conn.prepareStatement(sqls[i]);
			for (int j = 0; j < objs[i].length; j++) {
				if (objs[i][j] instanceof Integer) {
					ps.setInt(j + 1, (Integer) objs[i][j]);
				} else if (objs[i][j] instanceof String) {
					ps.setString(j + 1, (String) objs[i][j]);
				} else {
					throw new CustomException("sql'?'传输值格式不正确");
				}
			}
			sqlResult.put("sql", ps.toString());
			sqlResult.put("result", ps.executeUpdate());
			sqlList.add(sqlResult);
		} catch (SQLException | CustomException e) {
			throw new RuntimeException(e);
		}
	
	}
	if (ps != null) {
		try {
			ps.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	return sqlList;
}

public static void main(String[] args) {
	JdbcTool jdbcTool = new JdbcTool();
	String configPath = "icu/sandink/resources/config";
	
	String[] sqls = {
			"select * from emp where DEPTNO = ?",
			"select * from emp where DEPTNO = ?"
	};
	Object[][] objs = {
			{10},
			{30}
	};
	jdbcTool.jdbcToSQL(configPath, false, sqls, objs);
}