JDBC 应用
private static List<String> getConfig(String configPath) {
ResourceBundle bundle = ResourceBundle.getBundle(configPath);
List<String> configList = new ArrayList<>();
configList.add(bundle.getString("url"));
configList.add(bundle.getString("username"));
configList.add(bundle.getString("password"));
return configList;
}
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);
}