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.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
