如何从页面输入SQL查询条件得到后台结果返回前台页面

拼搏现实的明天。 2021-12-14 14:11 542阅读 0赞

转自： https://blog.csdn.net/yucongyuqian/article/details/77451578

使用Jsp编写网页并实现数据库的增删改查: https://blog.csdn.net/qq_32539825/article/details/70657340

JSP SQL语句大全: https://blog.csdn.net/fengyee_zju/article/details/25118683

开发一个用来执行sql的jsp页面: https://blog.csdn.net/qq_34928194/article/details/83412187

网页上直接sql查询操作数据库,并在网页上展示列表数据的工具页面: https://blog.csdn.net/yule117737767/article/details/83039509

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/yucongyuqian/article/details/77451578
一、页面：因为查询的表不固定，所以表格需要自助生成（这是利用了“jqGrid”）
注意：难点一、因为从数据库查询的表不同，所有前台页面遍历时的表头格式不固定
难点二、数据库连接源
若是不利用“jqGrid”也可以参考方法2.
方法1.


























<%—引入jqGerid—%>














方法2.动态生成表格，但是分页还需自己在写一下

参考网址：https://q.cnblogs.com/q/73869/

var table = “

“;
table += ““;
for (var i = 0; i < date.length; i++) {
table += ““;
}
table += ““;
for (var j = 0; j < department.length; j++) {
table += ““;
table += ““;
for (var n = 0; n < date.length; n++) {
table += ““;
}
}
table += ““;
table += “
“ + “” + ““ + date[i] + “
“ + department[j] + ““ + SeriesStr[0].data[n] + “
“;
$(“#Exceltable”).html(table);

效果：

二、controller
1）查询表头

@RequestMapping(value = “transactionFlow/informationQuery1”, method = RequestMethod.POST)

  1. public String informationQuery1(@ModelAttribute("form") @RequestParam (value = "sql") String sql, Model model) \{  
  2.     List<String> list3 = new LinkedList<>();  
  3.     String query1 = customerLedgersService.query(sql);  
  4.     Map map = new HashMap();  
  5.     if (query1 != null) \{
  7.         String\[\] key = query1.split(";");  
  8.         for (int i = 0, len = key.length; i < len; i++) \{  
  9.             list3.add(key\[i\]);  
  10.         \}
  12.         model.addAttribute("sql", sql);  
  13.         model.addAttribute("list3", list3);  
  14.         return "/complement/informationQuery";  
  15.     \} else \{  
  16.         model.addAttribute("sql", sql);
  18.         return "/complement/informationQuery";  
  19.     \}
  21. \}

2）查询分页信息

@RequestMapping(value = “transactionFlow/informationQuery”, method = RequestMethod.POST)
@ResponseBody
public GridData informationQuery(@RequestParam(value = “sql”, required = false) String sql,
@RequestParam(value = “page”, required = false) Integer page,
@RequestParam(value = “rows”, required = false) Integer rows
) {
List list7 = new LinkedList<>();
String queryValue = null;

  1.     String records1 = customerLedgersService.query1(sql);  
  2.     int records = Integer.parseInt(records1);  
  3.     GridData result = new GridData();
  5.     String listTotal = customerLedgersService.query2(sql, page, rows);  
  6.     String query2 = null;  
  7.     List<String> list6 = new ArrayList<>();  
  8.     List<String> list5 = new ArrayList();  
  9.     if (listTotal != null) \{  
  10.         String\[\] keyq3 = listTotal.split("%");  
  11.         for (int i = 0, len = keyq3.length; i < len; i++) \{  
  12.             list6.add(keyq3\[i\]);  
  13.         \}  
  14.         for (int m = 0; m < list6.size(); m++) \{  
  15.             queryValue = list6.get(m);  
  16.             String\[\] value = queryValue.split(";");  
  17.             List<String> list = Arrays.asList(value);  
  18.             ListIterator<String> li = list.listIterator();  
  19.             while (li.hasNext()) \{  
  20.                 Object obj = li.next();  
  21.                 if (obj.equals("0E-8")) \{  
  22.                     li.set("0.00000000");  
  23.                 \}  
  24.                 if (obj.equals("null")) \{  
  25.                     li.set("");  
  26.                 \}  
  27.             \}  
  28.             list7.add(list);
  30.         \}  
  31.     \}  
  32.     long totaPage = records % rows == 0 ? records / rows : records / rows + 1;  
  33.     result.setPage(page);  
  34.     result.setRecords(records);  
  35.     result.setTotal((int) totaPage);  
  36.     if (list7 != null) \{  
  37.         result.getRows().addAll(list7);  
  38.     \}  
  39.     return result;  
  40. \}

三、dao层（利用了JDBC原理）
需要考虑的是数据库连接问题

@Repository
public class QueryDaoImpl implements QueryDao {
private static Logger logger = LoggerFactory.getLogger(QueryDaoImpl.class);

  1. @Override  
  2. public String selectSql(String sql) \{  
  3.     DataSource ds = null;  
  4.     Connection conn = null;  
  5.     PreparedStatement pstmt = null;  
  6.     ResultSet rs = null;  
  7.     StringBuffer sb = new StringBuffer();  
  8.     int index = 1;  
  9.     //1 获得连接  
  10.     try \{
  12.         if(sql == null)\{  
  13.             return null;  
  14.         \}
  16.         InitialContext initialContext = new InitialContext();  
  17.         ds = (DataSource) initialContext.lookup("jdbc/account-structure");  
  18.         if (ds != null) \{  
  19.             conn = ds.getConnection();  
  20.         \}  
  21.         if(!sql.contains(";"))\{  
  22.             sql=sql+";";  
  23.         \}  
  24.         if(!sql.contains("limit") && !sql.contains("LIMIT"))\{  
  25.             if(sql.contains(";"))\{  
  26.                 sql = sql.replaceAll(";", "");  
  27.             \}  
  28.             sql = sql + " limit 0, 1;";  
  29.         \}  
  30.         pstmt = conn.prepareStatement(sql);  
  31.         rs = pstmt.executeQuery();  
  32.         List<LinkedHashMap<String, Object>> values = new ArrayList<>();  
  33.         ResultSetMetaData rsmd = rs.getMetaData();  
  34.         LinkedHashMap<String, Object> map = null;  
  35.         while (rs.next()) \{  
  36.             //把一条记录放入Map中  
  37.             map = new LinkedHashMap<String, Object>();  
  38.             for (int i = 0; i < rsmd.getColumnCount(); i++) \{  
  39.                 String columnLabel = rsmd.getColumnLabel(i + 1);  
  40.                 Object value = rs.getObject(i + 1);  
  41.                 map.put(columnLabel, value);  
  42.             \}  
  43.             values.add(map);  
  44.         \}
  46.         if (values.size() > 0) \{  
  47.             for (Map<String, Object> m : values) \{  
  48.                 Set<String> set = m.keySet();  
  49.                 for (String s : set) \{  
  50.                     sb.append(s + ";");  
  51.                 \}  
  52.             \}
  54.         \}  
  55.         return sb.toString();
  57.     \} catch (Exception e) \{  
  58.         e.printStackTrace();  
  59.     \} finally \{  
  60.         //6关闭资源

// JDBCUtils.close(conn, pstmt, rs);
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
try {
pstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
rs.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
return null;
}

  1. @Override  
  2. public String selectSql1(String sql) \{  
  3.     DataSource ds = null;  
  4.     Connection conn = null;  
  5.     PreparedStatement pstmt = null;  
  6.     ResultSet rs = null;  
  7.     StringBuffer sb = new StringBuffer();  
  8.     int index = 0;  
  9.     //1 获得连接  
  10.     try \{
  12.         if(sql == null)\{  
  13.             return null;  
  14.         \}
  16.         InitialContext initialContext = new InitialContext();  
  17.         ds = (DataSource) initialContext.lookup("jdbc/account-structure");  
  18.         if (ds != null) \{  
  19.             conn = ds.getConnection();  
  20.         \}  
  21.         if(!sql.contains(";"))\{  
  22.             sql=sql+";";  
  23.         \}  
  24.         if(!sql.contains("limit") && !sql.contains("LIMIT"))\{  
  25.             if(sql.contains(";"))\{  
  26.                 sql = sql.replaceAll(";", "");  
  27.             \}  
  28.             sql = sql + "  limit 0, 5000;";  
  29.         \}  
  30.         pstmt = conn.prepareStatement(sql);  
  31.         rs = pstmt.executeQuery();  
  32.         List<LinkedHashMap<String, Object>> values = new ArrayList<>();  
  33.         ResultSetMetaData rsmd = rs.getMetaData();  
  34.         LinkedHashMap<String, Object> map = null;  
  35.         while (rs.next()) \{  
  36.             //把一条记录放入Map中  
  37.             map = new LinkedHashMap<String, Object>();  
  38.             for (int i = 0; i < rsmd.getColumnCount(); i++) \{  
  39.                 String columnLabel = rsmd.getColumnLabel(i + 1);  
  40.                 Object value = rs.getObject(i + 1);  
  41.                 map.put(columnLabel, value);  
  42.             \}  
  43.             values.add(map);  
  44.         \}
  46.         if (values.size() > 0) \{  
  47.             for (Map<String, Object> m : values) \{  
  48.                 Set<String> set = m.keySet();  
  49.                 for (String s : set) \{  
  50.                     sb.append(s + ";");  
  51.                 \}  
  52.                 sb.append("|"+index++);  
  53.             \}
  55.         \}
  57.         return String.valueOf(index);
  59.     \} catch (Exception e) \{  
  60.         e.printStackTrace();  
  61.     \} finally \{  
  62.         //6关闭资源

// JDBCUtils.close(conn, pstmt, rs);
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
try {
pstmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
rs.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
return null;
}

  1. @Override  
  2. public String selectSq2(String sql, Integer page, Integer rows) \{  
  3.     DataSource ds = null;  
  4.     Connection conn = null;  
  5.     PreparedStatement pstmt = null;  
  6.     ResultSet rs = null;  
  7.     StringBuffer sb = new StringBuffer();  
  8.     StringBuffer sb1 = new StringBuffer();  
  9.     int index = 1;  
  10.     //1 获得连接  
  11.     try \{
  13.         InitialContext initialContext = new InitialContext();  
  14.         ds = (DataSource) initialContext.lookup("jdbc/account-structure");  
  15.         if (ds != null) \{  
  16.             conn = ds.getConnection();  
  17.         \}  
  18.         String sql1=null;  
  19.         StringBuffer sql2=null;  
  20.         if(!sql.contains(";"))\{  
  21.             sql=sql+";";  
  22.         \}  
  23.         sql1=sql.substring(0,sql.length()-1);
  25.         sql2=sb1.append(sql1+" "+"limit"+" "+"?"+","+"?;");  
  26.         pstmt = conn.prepareStatement(sql2.toString());  
  27.         int startIndex = (page - 1) \* rows;  
  28.         pstmt.setInt(1,startIndex);  
  29.         pstmt.setInt(2,rows);  
  30.         rs = pstmt.executeQuery();  
  31.         List<LinkedHashMap<String, Object>> values = new ArrayList<>();  
  32.         ResultSetMetaData rsmd = rs.getMetaData();  
  33.         LinkedHashMap<String, Object> map = null;  
  34.         while (rs.next()) \{  
  35.             //把一条记录放入Map中  
  36.             map = new LinkedHashMap<String, Object>();  
  37.             for (int i = 0; i < rsmd.getColumnCount(); i++) \{  
  38.                 String columnLabel = rsmd.getColumnLabel(i + 1);  
  39.                 Object value = rs.getObject(i + 1);  
  40.                 map.put(columnLabel, value);  
  41.             \}  
  42.             values.add(map);  
  43.         \}
  45.         if (values.size() > 0) \{  
  46.             for (Map<String, Object> m : values) \{  
  47.                 Set<String> set = m.keySet();
  49.                 for (String s : set) \{  
  50.                     sb.append(m.get(s) + ";");  
  51.                 \}  
  52.                 sb.append("%");  
  53.             \}  
  54.         \}  
  55.         return sb.toString();
  57.     \} catch (Exception e) \{  
  58.         e.printStackTrace();  
  59.     \} finally \{
  61.         try \{  
  62.             conn.close();  
  63.         \} catch (SQLException e) \{  
  64.             e.printStackTrace();  
  65.             try \{  
  66.                 pstmt.close();  
  67.             \} catch (SQLException e1) \{  
  68.                 e1.printStackTrace();  
  69.             \}  
  70.             try \{  
  71.                 rs.close();  
  72.             \} catch (SQLException e1) \{  
  73.                 e1.printStackTrace();  
  74.             \}  
  75.         \}  
  76.     \}  
  77.     return null;  
  78. \}

}

查询页面：

输入SQL语句：SELECT * from ledgers_entity where customer_business_id=’180050602775019520’;
查询结果：

-——————————
作者：yucongyuqian
来源：CSDN
原文：https://blog.csdn.net/yucongyuqian/article/details/77451578
版权声明：本文为博主原创文章，转载请附上博文链接！

发表评论

表情：
评论列表 （有 0 条评论，542人围观）

还没有评论，来说两句吧...

相关阅读