public static String ClobToString(CLOB clob) {
String reString = "";
try {
BufferedReader br = new BufferedReader(clob.getCharacterStream());
String s = br.readLine();
StringBuffer sb = new StringBuffer();
while (s != null) {
sb.append(System.getProperty("line.separator"));
sb.append(s);
s = br.readLine();
}
reString = sb.toString();
} catch (SQLException | IOException e) {
e.printStackTrace();
}
return reString;
}
public int getCount(boolean sqlNeedDeal,String sql, Object... params) {
int result = 0;
if(sqlNeedDeal){
sql = "select count(0) from ("+sql+")";
}
try (Connection connection = ds.getConnection()) {
try (PreparedStatement pp = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
pp.setObject(i + 1, params[i]);
}
try (ResultSet rs = pp.executeQuery()) {
while (rs.next()) {
result = rs.getInt(1);
}
} catch (Exception e) {
System.err.println("getCount:数据库获取失败!");
e.printStackTrace();
}
} catch (SQLException e) {
System.err.println("getCount:数据库准备失败!");
e.printStackTrace();
}
} catch (SQLException e) {
System.err.println("getCount:数据库获取失败!");
e.printStackTrace();
}
return result;
}
public int executeUpdate(String sql, Object... params) throws SQLException{
int result = -1;
try (Connection connection = ds.getConnection()) {
try (PreparedStatement pp = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
pp.setObject(i + 1, params[i]);
}
result = pp.executeUpdate();
} catch (SQLException e) {
System.err.println("executeUpdate:数据库准备或者更新失败!");
e.printStackTrace();
throw(e);
}
} catch (SQLException e) {
System.err.println("executeUpdate:数据库获取失败!");
e.printStackTrace();
throw(e);
}
return result;
}
public List<LinkedHashMap<String, Object>> executeSelect(String sql,List<String> columns, Object... params){
List<LinkedHashMap<String, Object>> datas=new ArrayList<>();
try (Connection connection = ds.getConnection()) {
try (PreparedStatement pp = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
pp.setObject(i + 1, params[i]);
}
try (ResultSet rs = pp.executeQuery()) {
while (rs.next()) {
LinkedHashMap<String, Object> one = new LinkedHashMap<>();
for (String key : columns) {
one.put(key,rs.getObject(key));
}
if(one.size()>0){
datas.add(one);
}
}
} catch (Exception e) {
System.err.println("executeSelect:数据库获取失败!");
e.printStackTrace();
}
} catch (SQLException e) {
System.err.println("executeSelect:数据库准备失败!");
e.printStackTrace();
}
} catch (SQLException e) {
System.err.println("executeSelect:数据库获取失败!");
e.printStackTrace();
}
return datas;
}
public List<LinkedHashMap<String, Object>> executeSelect(String sql,Map<String, String> columns, Object... params){
List<LinkedHashMap<String, Object>> datas=new ArrayList<>();
try (Connection connection = ds.getConnection()) {
try (PreparedStatement pp = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
pp.setObject(i + 1, params[i]);
}
try (ResultSet rs = pp.executeQuery()) {
while (rs.next()) {
LinkedHashMap<String, Object> one = new LinkedHashMap<>();
for (Map.Entry<String,String> key : columns.entrySet()) {
Object value = rs.getObject(key.getValue());
one.put(key.getKey(),value==null?" ":value);
}
if(one.size()>0){
datas.add(one);
}
}
} catch (Exception e) {
System.err.println("executeSelect:数据库获取失败!");
e.printStackTrace();
}
} catch (SQLException e) {
System.err.println("executeSelect:数据库准备失败!");
e.printStackTrace();
}
} catch (SQLException e) {
System.err.println("executeSelect:数据库获取失败!");
e.printStackTrace();
}
return datas;
}
ArrayList<Object> params = new ArrayList<Object>();
List<LinkedHashMap<String, Object>> datas=new ArrayList<>();
LinkedHashMap<String, String> columns = new LinkedHashMap<String, String>();
columns.put("类型", "z_type");
columns.put("状态", "status");
datas = helper.executeSelect(sql, columns, params.toArray());
String jsonString=JSON.toJSONString(datas);