matchselectbean.java

用于按照xml文件配置直接生成SQL脚本文件

//匹配查询的xml配置解析器
//数据库bean管理

package find;


import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.ServletException;
import javax.servlet.RequestDispatcher;
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.util.Properties;

import org.xml.sax.Attributes;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.SAXException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.net.URL;
import java.net.URLDecoder;
import java.io.File;


public class MatchSelectBean extends DefaultHandler{
	
	//private String currentSet;
	//private String currentName;
	private int step;//用于xml元素解析步骤
	
	private StringBuffer currentValue = new StringBuffer();
	
	//匹配值
	private String matchString;//匹配查询所需要的字符串
	
	private String sql;//sql语句
	private String title;//页面标题
	private String matchColumn;//匹配列
	private String hide;//隐藏列
	private int iMatch;//匹配列的标号
	private int iHide;//隐藏列的标号
	private int iRSCol;//结果集的column数
	private String label;//标题名
	private String[] labels;//
	private int p_label;//指针
	private int pX;
	private int pY;
	private String column;//列名
//	private String[][] value;//
//	private String[] hideValue;//隐藏值
	private String back;//返回组件
	private String xmlFile;//xml配置文件路径
	
	//jsp表现层用
	private int iCol;//页面上表格的column数
	private int iLine;//页面上表格的行数
	private String hideValue;//隐藏列的值;
	private String matchValue;//匹配列的值;
	private boolean noResult = false;//没有结果
	
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	private ResultSetMetaData rsmd;
	
	
	public MatchSelectBean(){
		this.clear();
	}
	
	//清空
	public void clear(){
		this.step = 1;
		this.label = "";
		this.column = "";
	}
	
	//get和set方法
	
	public String getMatchString(){
		return this.matchString;
	}
	
	public void setMatchString(String s){
		this.matchString = s.replaceAll("'","_");
		
	}

	public String getSql(){
		return this.sql;
	}
	
	public void setSql(String s){
		this.sql = s;
	}
	
	public String getXmlFile(){
		return this.xmlFile;
	}
	
	public void setXmlFile(String s){
		this.xmlFile = s;
	}
	
	public String getTitle(){
		return this.title;
	}
	
	public void setTitle(String s){
		this.title = s;
	}
	
	public String getMatchColumn(){
		
		return this.matchColumn;
	}
	
	public void setMatchColumn(String s){
		
		this.matchColumn = s;
	}
	
	public String getHide(){
		return this.hide;
	}
	
	public void setHide(String s){
		this.hide = s;
	}
	
	public String[] getLabel(){
		return this.label.split(";");
	}
	
	public void setLabel(String s){
		this.label = this.label+s+";";
	}
	
	public String[] getColumn(){
		return this.column.split(";");
	}
	
	public void setColumn(String s){
		this.column = this.column+s+";";
	}
	
	public String getBack(){
		return this.back;
	}
	
	public void setBack(String s){
		this.back = s;
	}
	
	public int getICol(){
		return this.iCol;
	}
	
	public void setICol(int i){
		this.iCol=i;
	}
	
	//定义开始解析元素的方法. 这里是将<xxx>中的名称xxx提取出来.
	
	public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
		currentValue.delete(0, currentValue.length());
		if(qName.equals("标题名")) this.step = 5;
	
	}
	
	//这里是将<xxx></xxx>之间的值加入到currentValue
	
	public void characters(char[] ch, int start, int length) throws SAXException {
		currentValue.append(ch, start, length);
	}
	
	//在遇到</xxx>结束后,
	
	public void endElement(String uri, String localName, String qName) throws SAXException {
		switch(this.step){
			case 1:
				this.setSql(this.currentValue.toString().trim());
				break;
			case 2:
				this.setTitle(this.currentValue.toString().trim());
				break;
			case 3:
				this.setMatchColumn(this.currentValue.toString().trim());
				break;
			case 4:
				this.setHide(this.currentValue.toString().trim());
				break;
			case 5:
				this.setLabel(this.currentValue.toString().trim());
				break;
			case 6:
				this.setColumn(this.currentValue.toString().trim());
				break;
			case 7:
				break;
			case 8:
				this.setBack(this.currentValue.toString().trim());
				break;	
			default:
				;
		}
		this.step++;
		//props.put(qName.toLowerCase(), currentValue.toString().trim());

	}
	
	
	public void parse(String filename) throws Exception {
		
		SAXParserFactory factory = SAXParserFactory.newInstance();
		//factory.setNamespaceAware(false);
		//factory.setValidating(false);
		SAXParser parser = factory.newSAXParser();
		
		
		try{
			
			parser.parse(new File(filename).toURL().toString(), this); 
		}finally{
			factory=null;
			parser=null;
		}
	
	}
	
	public void work() throws Exception{
		
		//清空元素
		this.clear();
		
		//解析xml文件
		this.parse(this.getXmlFile());
		//取连接
		conn = new SqlConnection().getSQLConnection();
		
		/*
		 *step 1:填充表头阵列fillheadline
		 *step 2: 取行数
		 *step 3:取结果集
		 */
		 
		//step 1
		this.fillHeadline();
		
		//step 2
		this.iLine = this.dbgetLine();
		if(this.iLine==0){
			this.setMatchString("");
			this.iLine = this.dbgetLine();
			if(this.iLine==0)this.noResult=true;
		}
		
		
		//step 3:
		this.dbgetRS();

		//关闭;	
		this.conn = null;
	}
	
	//根据已有的sql生成符合模块要求的sql
	
	public String makeSql(String s){
		//插入sql条件
		String a,b;
		String upper = s.toUpperCase();
		if(sql.matches("(?i).* ORDER BY .*")){
			a = s.substring(0,upper.lastIndexOf(" ORDER BY "));
			b = s.substring(upper.lastIndexOf(" ORDER BY "),s.length());
		}else{
			a = s;
			b = "";
		}
		if(a.matches("(?i).* WHERE .*")){
			a = a + " and ";
		}else{
			a = a + " where ";
		}
		a = a + this.getMatchColumn()+" like '%"+this.getMatchString()+"%'";
		s = a + b;
		return s;
	}
	
	
	//下面是数据库操作函数
	//得到表的行数
	int dbgetLine(){
		try{
			
			
			//初期设定
			
			String sql = this.sql;
			
			sql = "SELECT count(*)" + sql.substring(sql.toLowerCase().indexOf(" from "),sql.length());
			if(sql.matches("(?i).* ORDER BY .*"))
			sql = sql.substring(0,sql.toUpperCase().lastIndexOf(" ORDER BY "));
			String key = sql.matches("(?i).* WHERE .*")?" and ":" where ";
			sql = sql+key+this.getMatchColumn()+" like "+"'%"+this.getMatchString()+"%'";
			
			//System.out.println(sql);
			
			//准备
			stmt = conn.prepareStatement(sql);
			
			//设定
			
			
			//查询
			rs = stmt.executeQuery();
			
			//取参数
			
			if(rs.next()){
				int i = rs.getInt(1); 
				//System.out.print("需要打印的行数:");
				//System.out.print(i);
				return i;
			}
			else return -1;
			/*
			rs.last();
			
			System.out.println(rs.getRow());
			rs.beforeFirst();
			
			while(rs.next()){
				System.out.println(rs.getString(4));
			}
			*/
			
		}catch(Exception e){
			e.printStackTrace();
			return -2;
		}
	}
	//取结果及。
	int dbgetRS(){
		try{
			
			
			//初期设定		

			
				//如果行数为0，则无正确匹配值，将匹配字串设成""
				//最后设定sql
			this.setSql(this.makeSql(this.getSql()));
			System.out.println(this.sql);	
			
					
			String sql = this.sql;
			this.pX = 1;			
			//准备
			stmt = conn.prepareStatement(sql);//ResultSet.TYPE_SCROLL_SENSITIVE ,ResultSet.CONCUR_UPDATABLE);
			
			//设定
			
			
			//查询
			rs = stmt.executeQuery();
			//取参数
			/*
			 *1。标记隐藏列
			 *2。取真实结果集列数
			 */
			this.iMatch = rs.findColumn(this.getMatchColumn());
		
			this.iHide = this.getHide().equals("")?-1:rs.findColumn(this.getHide());
			this.iRSCol = rs.getMetaData().getColumnCount();
			
			this.iCol = this.iHide==-1?this.iRSCol:this.iRSCol-1;
			
			rs.next();
			

			return 0;
			
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}
	//数据库操作取match值
	public String dbgetMValue() throws Exception{
		this.matchValue = new String(rs.getString(this.iMatch).getBytes("ISO-8859-1"));
		return this.matchValue;
	}
	
	//数据库操作取hide值
	public String dbgetHValue() throws Exception{
		if(this.iHide<1){
			this.hideValue = "";
		}else this.hideValue = new String(rs.getString(this.hide).getBytes("ISO-8859-1"));
		return this.hideValue;
	}
	
	public String dbgetTValue() throws Exception{
		String s = "";
		//隐藏值要跳过
		if(this.pX==this.iHide) this.pX++;
		if(this.pX!=this.iMatch){
			s = new String(rs.getString(this.pX++).getBytes("ISO-8859-1"));
			
			 
		}
		else{
			this.pX++;
			s = this.matchValue;
		}
		while(this.pX==this.iHide){
			this.pX++;
		}
		
		//大于总列数要到下一行
		if(this.pX>this.iRSCol){
			this.pX = 1;
			rs.next();
		}
		
		return s;
	}	
	/*
	 *
	 *
	 */
	//填充表头
	void fillHeadline(){
		this.labels = this.getLabel();
		this.p_label = 0;
	}
	
	
	
	//下面是jsp表现层函数
	//打印表头
	
	public String printHeadline(){
		return this.labels[this.p_label++];
	}
	
	//取打印行数
	
	public int getPrintLine(){
		
		return this.iLine;
	}
	
	
	//取表中的匹配列值
	public String getMatchValue() throws Exception{
		
		return this.dbgetMValue();
	}
	
	//取表中的hide值
	public String getHideValue() throws Exception{
		return this.dbgetHValue();
	}
	
	//取表中的其它数据值
	public String getTValue() throws Exception{
		return this.dbgetTValue();
	}

	//读title
	public String showTitle(){
		return this.getTitle();
	}
	
	//
	public String hasResult(){
		return this.noResult?"对不起，没有结果":"";
	}
	
}