Use JSP to implement simple SQL reports

Source: Internet
Author: User

This content is also prepared according to the requirements of enterprise students. In fact, this small project was just completed at graduation. Many times we want to execute the following SQL/HQL and then get an HTML table output:

Input: select ID as number, NAME as NAME, AGE as AGE from XXX

Output:

No. Name Age
     

The requirement is that if the SQL statement changes, all alias field information and data will still be displayed.

Currently, Hibernate is widely used, so Hibernate is the preferred method. The result shows that if it is an object ing query statement, it can be easily used: List Query. getReturnAliases () is used to obtain the alias. However, we know that the statement is complex when querying, not all HQL. When using SQLQuery, it is surprising that this method has not been implemented (whether or not the latest version of Hibernate 3.3 has been implemented and not tested). The version used is Hibernate 3.2, and the corresponding code is:

DAO

/*** Return the result based on the query statement and the column name containing the result * @ param hql * @ param args * @ return */public List queryAllForReport (final String hql, final Object... args) {List list = gethibernatetemplate(cmd.exe cuteFind (new HibernateCallback () {public Object doInHibernate (Session session) throws HibernateException, SQLException {Query query Query = session. createQuery (hql); for (int I = 0; I <args. length; I ++) {query. setParameter (I, args [I]);} List list = query. list (); list. add (0, query. getReturnAliases (); return list ;}}); // The return result of the count calculation by Hibernate is generally the return list object ;}

Test code:

List
     
      
List = dao. queryAllForReport ("select id as number, name as login name, address as address, realName from User"); System. out. println (list. size (); for (Object [] row: list) {for (Object v: row) {System. out. print (v + "\ t");} System. out. println ();}
     

Finally, we had to go back to JDBC and use ResultSet and ResultSetMetaData to implement this function. The detailed Code (which can be implemented by yourself) is as follows:

<% @ Page language = "java" import = "java. util. *, java. SQL. *" pageEncoding = "UTF-8" %>
<%@ Taglib prefix = "c" uri = "http://java.sun.com/jsp/jstl/core" %>

<! Doctype html public "-// W3C // dtd html 4.01 Transitional // EN">
<Html>
<Head>

<Title> SQL Report </title>

<Meta http-equiv = "pragma" content = "no-cache">
<Meta http-equiv = "cache-control" content = "no-cache">
<Meta http-equiv = "expires" content = "0">


</Head>

<Body>
<Form action = "">
<Textarea name = SQL cols = 80 rows = 10 >$ {param. SQL} </textarea> <br>
<Input type = submit value = query>
</Form>

<C: if test = "$ {! Empty param. SQL} ">

<%
// New oracle. jdbc. driver. OracleDriver ();
Connection conn = DriverManager. getConnection ("jdbc: oracle: thin: @ 192.168.1.41: 1521: xe", "hr", "hr ");
Statement stmt = conn. createStatement ();
ResultSet rs = stmt.exe cuteQuery (request. getParameter ("SQL "));

If (rs = null ){
Stmt. close ();
Conn. close ();
Return;
}

// Retrieve the column name
ResultSetMetaData meta = rs. getMetaData ();
Int cols = meta. getColumnCount ();
ArrayList colNames = new ArrayList ();

For (int I = 1; I <= cols; I ++ ){
ColNames. add (meta. getColumnLabel (I ));
}

Request. setAttribute ("colNames", colNames );
%>
<Table border = "1" cellpadding = "0" style = "border-collapse: collapse;" width = "100%" bordercolor = "#000000" align = center>

<Tr>
<C: forEach items = "$ {colNames}" var = "c">
<Td >$ {c} </td>
</C: forEach>
</Tr>

<%
While (rs. next ()){
ColNames. clear ();

For (int I = 1; I <= cols; I ++ ){
Object value = rs. getObject (I );
System. out. println (value. getClass ());

// TODO more formatting Control
If (value instanceof java. SQL. Date ){
Value = rs. getTimestamp (I); // retrieve the exact date
Java. text. SimpleDateFormat df = new java. text. SimpleDateFormat ("MM dd, yyyy, HH: mm: MM: ss second EEE ");
Value = df. format (value );
}

If (value instanceof java. math. BigDecimal ){
Java. math. BigDecimal v = (java. math. BigDecimal) value;
Value = v. doubleValue ();
// Requires that at least two decimal places be displayed in the output, and at most three decimal places can be output.
Java. text. NumberFormat format = java. text. NumberFormat. getInstance (); // only format Decimals
Format. setMaximumFractionDigits (2); // a maximum of three decimal places
Format. setMinimumFractionDigits (1); // at least two decimal places
Value = format. format (value); // => String
}

ColNames. add (value );
}

Request. setAttribute ("colNames", colNames );

%>
<Tr>
<C: forEach items = "$ {colNames}" var = "c">
<Td >$ {c} </td>
</C: forEach>
</Tr>
<%

}

Rs. close ();
Stmt. close ();
Conn. close ();
%>
</Table>
</C: if>
</Body>
</Html>

 

JSTL and EL are used. In general, it is very convenient to change the version. however, the running of Tomcat requires a relatively high version, such as 5.5 or above, and the JSTL class library is required. however, there should be many open-source frameworks for similar template projects, such as many Report frameworks.

  1. JSP implements WEB-based database image storage and Dynamic Display
  2. Easily implement data pie charts on JSP pages
  3. Use JSP to store and display database images

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.