📄 sqlservlet.java
字号:
/* * Copyright 2006-2007 Queplix Corp. * * Licensed under the Queplix Public License, Version 1.1.1 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/ * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the * License for the specific language governing permissions and limitations under * the License. */package com.queplix.core.modules.eqlext.www;import com.queplix.core.jxb.entity.types.SqlSType;import com.queplix.core.utils.StringHelper;import com.queplix.core.utils.SystemHelper;import com.queplix.core.utils.sql.SqlWrapper;import com.queplix.core.utils.sql.SqlWrapperFactory;import com.queplix.core.utils.www.AbstractServlet;import javax.servlet.ServletConfig;import javax.servlet.ServletException;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.PrintWriter;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Types;import java.util.Iterator;/** * Executes any SQL queries. * * @author Andrey L. Baranov [ALB] * @version $Revision: 1.3 $ $Date: 2006/05/22 11:08:07 $ */public class SqlServlet extends AbstractServlet { // =============================================================== Constants private static final int MAX_RECORDS = 200; // =============================================================== Servlet API methods /* (non-Javadoc) * @see HTTPServlet#init(ServletConfig) */ public void init( ServletConfig sc ) throws ServletException { super.init( sc ); /** @todo read parameters */ } /* (non-Javadoc) * @see HTTPServlet#service(HttpServletRequest, HttpServletResponse) */ public void service( HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException { // Check if the servlet is run from the localhost if ( ! request.getRemoteAddr().equalsIgnoreCase("127.0.0.1") ) throw new ServletException( "Cannot use this servlet." ); // Check permissions. if( SystemHelper.isProductionMode() ) { // [TN] { // check availability in production mode if( !new Boolean( this.getInitParameter( "allowInProductionMode" ) ).booleanValue() ) { throw new ServletException( "Cannot use this servlet in production mode." ); } // [TN] } } String result = null; long page_size = -1; // Get parameters: SQL query and page size. request.setCharacterEncoding( "UTF-8" ); String name = request.getParameter( "name" ); String sql = request.getParameter( "sql" ); String s = request.getParameter( "pageSize" ); if( s != null ) { page_size = Long.parseLong( s ); } if( sql != null ) { result = executeQuery( name, sql, page_size ); } else { result = "<b>EMPTY</b>"; } // Set HTTP headers. response.setContentType( "text/html;charset=UTF-8" ); response.setHeader( "Cache-Control", "no-cache" ); response.setHeader( "Pragma", "no-cache" ); // Print data. PrintWriter out = response.getWriter(); out.println( "<html>" ); out.println( "<body>" ); out.print( "<form method=\"post\" accept-charset=\"UTF-8\"" ); if( page_size >= 0 ) { out.print( "?pageSize=" + page_size ); } out.print( "\">" ); out.println( "<table cellspacing=\"10\" style=\"width:100%\">" ); out.println( "<tr><td colspan=\"2\"><label>SqlWrapper:</label> <select name=\"name\">" ); for( Iterator it = SqlWrapperFactory.getSqlWrapperNames().iterator(); it.hasNext(); ) { String sqlWrapperName = ( String ) it.next(); if( sqlWrapperName.equals( name ) ) { out.print( "<option selected=\"selected\" value=\"" ); } else { out.print( "<option value=\"" ); } out.print( sqlWrapperName ); out.print( "\">" ); out.print( sqlWrapperName ); out.println( "</option>" ); } out.println( "</select></td></tr>" ); out.println( "<tr><td><label>SQL:</label></td><td style=\"width:100%\">" ); out.print( "<textarea name=\"sql\" style=\"width:100%;height:300\">" ); if( sql != null ) { out.print( sql.trim() ); } out.println( "</textarea>" ); out.println( "</td></tr>" ); out.println( "<tr><td><input type=\"submit\" value=\"Go\">" ); out.println( "</td></tr>" ); out.println( "<tr><td></td><td>" ); out.println( result ); out.println( "</td></tr>" ); out.println( "</table>" ); out.println( "</form>" ); out.println( "</body>" ); out.println( "</html>" ); // Ok. logger.DEBUG( "Completed! Enc: " + response.getCharacterEncoding() ); } // ========================================================= Private methods // // Executes the given SQL query. // private String executeQuery( String sqlWrapperName, String sql, long page_size ) { // Initialization. SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper( sqlWrapperName ); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; ResultSetMetaData rsmd = null; StringBuffer sb = new StringBuffer(); // Check page size. if( page_size < 0 ) { page_size = MAX_RECORDS; } try { String msg = "Sql Wrapper Name: " + sqlWrapperName + ", SQL query (default): " + sql + "\n"; System.out.print( msg ); msg = "SQL query (Cp1251): " + sql + "\n"; System.out.write( msg.getBytes( "Cp1251" ) ); msg = "SQL query (UTF-8): " + sql + "\n"; System.out.write( msg.getBytes( "UTF-8" ) ); msg = "SQL query (CP866): " + sql + "\n"; System.out.write( msg.getBytes( "CP866" ) ); } catch( Exception ex ) { ex.printStackTrace(); } long time = System.currentTimeMillis(); try { con = sqlWrapper.doConnection(); System.out.println( "Got connection: " + con.getClass().getName() ); ps = sqlWrapper.doPreparedStatement( con, sql ); if( ps.execute() ) { time = System.currentTimeMillis() - time; rs = ps.getResultSet(); rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); // Show description. sb.append( "<br><label>" ).append( "SQL: " ).append( sql ).append( "</label><br>" ); sb.append( "<br><label>[" ).append( "PAGE SIZE: " ).append( page_size ).append( "]</label>" ); sb.append( "<label>[" ).append( "TIME: " ).append( time ).append( "(ms)]</label><br>" ); sb.append( "<table border=\"1\" style=\"width:100%\"><tr>" ); sb.append( "<td>#</td>" ); for( int i = 1; i <= cols; i++ ) { String val = rsmd.getColumnName( i ); sb.append( "<td><b>" ).append( val ).append( "</b></td>" ); } sb.append( "</tr>" ); // Show data. int count = 0; while( rs.next() && count < page_size ) { sb.append( "<tr>" ); sb.append( "<td>" ).append( count + 1 ).append( "</td>" ); for( int i = 1; i <= cols; i++ ) { int type = rsmd.getColumnType( i ); String val = getObject( sqlWrapper, rs, i, type ); sb.append( "<td>" ).append( val ).append( "</td>" ); } sb.append( "</tr>" ); count++; } sb.append( "</table>" ); } } catch( Exception ex ) { ex.printStackTrace(); sb = new StringBuffer(); sb.append( "<textarea style=\"width:100%\" name=\"error\">" ); sb.append( StringHelper.escape( ex.getMessage() ) ).append( "</textarea>" ); } finally { sqlWrapper.closeConnection( con, ps ); } return sb.toString(); } // // Retrieves an object from SQL ResultSet. // private String getObject( SqlWrapper sqlWrapper, ResultSet rs, int j, int type ) throws Exception { if( type < 0 ) { type = Types.VARCHAR; } else if( type == Types.CLOB ) { // clob mapping type = Types.VARCHAR; } SqlSType sqlSType = sqlWrapper.typeMapping( type, 0 ); if( sqlSType == null ) { // . not supported return "<pre><unknown></pre>"; } int sql_type = sqlSType.getType(); Object o; try { o = sqlWrapper.getParser( sql_type ).getObject( rs, j ); } catch( SQLException ex ) { logger.ERROR( "Cannot read value for column #" + j + "; type=" + type + "; sql type=" + sql_type ); throw ex; } if( o instanceof char[] ) { return "<pre>" + escape( new String( ( char[] ) o ) ) + "</pre>"; } else { return "<pre>" + escape( "" + o ) + "</pre>"; } } // // Escapes the string. // private String escape( String s ) { if( StringHelper.isEmpty( s ) ) { return s; } StringBuffer sb = new StringBuffer(); int size = s.length(); for( int i = 0; i < size; i++ ) { char c = s.charAt( i ); switch( c ) { case '<': sb.append( "<" ); break; case '>': sb.append( ">" ); break; default: if( c != 0 ) { sb.append( ( char ) c ); } } } return sb.toString(); }}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -