jsourcecodewizardprocessorsql.java

用免疫遗传算法解决TSP问题

/**
 * IgaLib -> wizard
 * Copyright (C) 2001  Tosiki IGA , IgaLib project member
 *   (http://homepage2.nifty.com/igat/igapyon/index.html)
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

package jp.ne.nifty.iga.tosiki.wizard;

import jp.ne.nifty.iga.tosiki.string.*;
import java.io.*;
import java.util.*;

/**
 * JSourceCodeWizardProcessor 偺僿儖僷乕僋儔僗<BR>
 * SQL晹暘偱偡<BR>
 *
 * @author Tosiki IGA
 */
public class JSourceCodeWizardProcessorSql
{
	/**
	 * 僿僢僟傪憓擖偟傑偡丅
	 */
	public static final void writeHeadder(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		out.println("<H3>"+dataChannel.getName()+"("+dataChannel.getTitle()+")"+(dataChannel.getDescription()==null?"":" : "+dataChannel.getDescription())+"</H3>");
		out.println("<TABLE border=\"1\">");
		out.println("<TBODY>");
		out.println("<TR>");
		out.println("<TD bgcolor=\"#ffff00\">崁栚柤徧</TD>");
		out.println("<TD bgcolor=\"#ffff00\">崁栚愢柧</TD>");
		out.println("<TD bgcolor=\"#ffff00\">宆</TD>");
		out.println("<TD bgcolor=\"#ffff00\">挿偝</TD>");
		out.println("<TD bgcolor=\"#ffff00\">彫悢晹</TD>");
		out.println("<TD bgcolor=\"#ffff00\">Primary</TD>");
		out.println("<TD bgcolor=\"#ffff00\">NOT NULL</TD>");
		out.println("<TD bgcolor=\"#ffff00\">徻嵶</TD>");
		out.println("</TR>");
		for(int index=0;index<wizard.getField().size();index++)
		{
			JSourceCodeWizardField field=(JSourceCodeWizardField)wizard.getField().get(index);
			out.println("<TR> <TD>"+field.getName()+"</TD> <TD>"+field.getTitle()+"</TD> <TD>"+field.getSqlTypeInDdl()+"</TD> <TD>"+(field.getLength()>=0?""+field.getLength():"")+"</TD> <TD>"+(field.getFractionLength()>=0?""+field.getFractionLength():"")+"</TD> <TD>"+(field.isPrimary()?"Yes":"")+"</TD> <TD>"+(field.isNotNull()?"Yes":"")+"</TD> <TD>"+(field.getDescription()==null?" ":field.getDescription())+"</TD> </TR>");
		}
		out.println("</TBODY>");
		out.println("</TABLE>");
		out.println("<UL><LI>"+getCreateTable(wizard)+"</UL>");
	}

	public static final void write(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		writeParse(wizard,dataChannel,vecItem,out);
		writeSqlCreateTable(wizard,dataChannel,vecItem,out);
		writeSqlDropTable(wizard,dataChannel,vecItem,out);
		writeSqlInsertInto(wizard,dataChannel,vecItem,out);
		writeSqlSelect(wizard,dataChannel,vecItem,out);
		writeSqlSelectList(wizard,dataChannel,vecItem,out);
		if(getPrimaryKey(vecItem).length()>0)
		{
			writeSqlSelect1(wizard,dataChannel,vecItem,out);
		}
		writeSqlSelectWhere(wizard,dataChannel,vecItem,out);
		writeSqlDelete(wizard,dataChannel,vecItem,out);
		writeSqlUpdate(wizard,dataChannel,vecItem,out);
	}

	protected static final String getCreateTable(JSourceCodeWizard wizard)
	{
		StringBuffer strbufSql=new StringBuffer();
		StringBuffer strbufPrimaryKey=new StringBuffer();
		strbufSql.append("CREATE TABLE "+wizard.getChannel().getName()+" (");

		boolean isFirstColumn=true;
		for(int index=0;index<wizard.getField().size();index++)
		{
			JSourceCodeWizardField item=(JSourceCodeWizardField)wizard.getField().get(index);

			if(item.isAvailableSql()==false)
			{
				continue;
			}

			if(isFirstColumn)
			{
				isFirstColumn=false;
			}
			else
			{
				strbufSql.append(',');
			}

			strbufSql.append(item.getName());
			strbufSql.append(" "+item.getSqlTypeInDdl());

			if(item.isAvailableSql()
			&& item.isPrimary())
			{
				if(strbufPrimaryKey.toString().length()==0)
				{
					strbufPrimaryKey.append(",PRIMARY KEY(");
				}
				else
				{
					strbufPrimaryKey.append(",");
				}
				strbufPrimaryKey.append(item.getName());
			}

			if(item.getNotNull())
			{
				strbufSql.append(" NOT NULL");
			}
		}

		if(strbufPrimaryKey.toString().length()>0)
		{
			strbufPrimaryKey.append(")");
		}

		strbufSql.append(strbufPrimaryKey.toString());
		strbufSql.append(")");
		return strbufSql.toString();
	}

	public static final void writeSqlCreateTable(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		out.addIndent(1);

		out.beginComment();
		out.println(dataChannel.getName()+"("+dataChannel.getTitle()+") 偺DB昞嶌惉<BR>");
		out.println("");
		out.println("  @param Connection 愙懕僐僱僋僔儑儞");
		out.println("  @throws SQLException");
		out.endComment();
		out.println("public static void sqlCreateTable(java.sql.Connection conn)");
		out.println(1,"throws java.sql.SQLException");
		out.println("{");

		out.addIndent(1);
		out.println("java.sql.Statement stmt=conn.createStatement();");
		out.println("try{");
		out.addIndent(1);
		out.println("stmt.executeUpdate(\""+getCreateTable(wizard)+"\");");
		out.addIndent(-1);
		out.println("}finally{");
		out.println(1,"stmt.close();");
		out.println("}");

		if(wizard.getChannel().getIndex()!=null)
		{
			for(int index=0;index<wizard.getChannel().getIndex().size();index++)
			{
				String strIndexColumn=(String)wizard.getChannel().getIndex().get(index);
				String strSql="CREATE INDEX ix_"+wizard.getChannel().getName()+(index+1)+" ON "+wizard.getChannel().getName()+" ("+strIndexColumn+")";
				out.println("stmt=conn.createStatement();");
				out.println("try{");
				out.println(1,"stmt.executeUpdate(\""+strSql+"\");");
				out.println("}finally{");
				out.println(1,"stmt.close();");
				out.println("}");
			}
		}

		out.addIndent(-1);

		out.println("}");

		out.addIndent(-1);
		out.println("");
	}

	public static final void writeSqlDropTable(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		out.addIndent(1);

		out.beginComment();
		out.println(dataChannel.getName()+"("+dataChannel.getTitle()+") 偺DB昞嶍彍<BR>");
		out.println("");
		out.println("  @param Connection 愙懕僐僱僋僔儑儞");
		out.println("  @throws SQLException");
		out.endComment();
		out.println("public static void sqlDropTable(java.sql.Connection conn)");
		out.println(1,"throws java.sql.SQLException");
		out.println("{");

		out.addIndent(1);
		out.println("java.sql.Statement stmt=conn.createStatement();");

		out.println("try{");

		StringBuffer strbufSql=new StringBuffer();
		strbufSql.append("stmt.executeUpdate(\"DROP TABLE "+dataChannel.getName());

		strbufSql.append("\");");

		out.println(1,strbufSql.toString());

		out.println("}finally{");
		out.println(1,"stmt.close();");

		out.println("}");

		out.addIndent(-1);

		out.println("}");

		out.addIndent(-1);
		out.println("");
	}

	public static final void writeSqlInsertInto(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		out.addIndent(1);

		out.beginComment();
		out.println(dataChannel.getName()+"("+dataChannel.getTitle()+") 偺DB昞憓擖<BR>");
		out.println("");
		out.println("  @param Connection 愙懕僐僱僋僔儑儞");
		out.println("  @return 僥乕僽儖憓擖偵惉岟偟偨審悢");
		out.println("  @throws SQLException");
		out.endComment();
		out.println("public int sqlInsertInto(java.sql.Connection conn)");
		out.println(1,"throws java.sql.SQLException");
		out.println("{");

		out.addIndent(1);

		StringBuffer strbufSql=new StringBuffer();
		strbufSql.append("java.sql.PreparedStatement stmt=conn.prepareStatement(");
		strbufSql.append("\"INSERT INTO "+dataChannel.getName()+" VALUES (");

		boolean isFirstColumn=true;
		for(int index=0;index<vecItem.size();index++)
		{
			JSourceCodeWizardField item=(JSourceCodeWizardField)vecItem.get(index);
			if(item.isAvailableSql()==false)continue;
			if(isFirstColumn)
			{
				isFirstColumn=false;
			}
			else
			{
				strbufSql.append(',');
			}
			strbufSql.append("?");
		}

		strbufSql.append(")\");");

		out.println(strbufSql.toString());

		out.println("try{");
		out.addIndent(1);

		out.println("int iColumn=1;");

		for(int index=0;index<vecItem.size();index++)
		{
			JSourceCodeWizardField item=(JSourceCodeWizardField)vecItem.get(index);
			if(item.isAvailableSql()==false)continue;
			item.writeSqlSetParam(null,out);
		}

		out.println("return stmt.executeUpdate();");

		out.addIndent(-1);
		out.println("}finally{");
		out.println(1,"stmt.close();");
		out.println("}");

		out.addIndent(-1);
		out.println("}");
		out.addIndent(-1);
		out.println("");
	}

	public static final void writeSqlSelect(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		out.addIndent(1);

		out.beginComment();
		out.println(dataChannel.getName()+"("+dataChannel.getTitle()+") 偺DB昞慡専嶕<BR>");
		out.println("");
		out.println("  @param Connection 愙懕僐僱僋僔儑儞");
		out.println("  @return 専嶕寢壥");
		out.println("  @throws SQLException");
		out.endComment();
		out.println("public static java.sql.PreparedStatement sqlSelect(java.sql.Connection conn)");
		out.println(1,"throws java.sql.SQLException");
		out.println("{");

		out.addIndent(1);

		StringBuffer strbufSql=new StringBuffer();
		strbufSql.append("java.sql.PreparedStatement stmt=conn.prepareStatement(");
		strbufSql.append("\"SELECT "+getColumnAll(vecItem)+" FROM "+dataChannel.getName()+" ORDER BY "+getPrimaryKey(vecItem)+"\");");

		out.println(strbufSql.toString());

		out.println("return stmt;");

		out.addIndent(-1);
		out.println("}");
		out.addIndent(-1);
		out.println("");
	}

	public static final void writeSqlSelectList(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		out.addIndent(1);

		out.beginComment();
		out.println(dataChannel.getName()+"("+dataChannel.getTitle()+") 偺DB昞慡専嶕 List栠傝抣斉<BR>");
		out.println("");
		out.println("  @param Connection 愙懕僐僱僋僔儑儞");
		out.println("  @return ArrayList 専嶕寢壥");
		out.println("  @throws SQLException");
		out.endComment();
		out.println("public static ArrayList sqlSelectList(java.sql.Connection conn)");
		out.println(1,"throws java.sql.SQLException");
		out.println("{");

		out.addIndent(1);

		out.println("ArrayList vecResult=new ArrayList();");
		out.println("java.sql.PreparedStatement stmt=sqlSelect(conn);");

		out.println("try{");
		out.addIndent(1);

		out.println("java.sql.ResultSet resultSet=stmt.executeQuery();");
		out.println("for(;resultSet.next();)");
		out.println("{");
		out.addIndent(1);
		out.println(JSourceCodeWizardProcessor.getMyClassName(wizard,dataChannel)+" data"+dataChannel.getName()+"=new "+JSourceCodeWizardProcessor.getMyClassName(wizard,dataChannel)+"();");
		out.println("data"+dataChannel.getName()+".parse(resultSet);");
		out.println("vecResult.add(data"+dataChannel.getName()+");");
		out.addIndent(-1);
		out.println("}");
		out.println("return vecResult;");

		out.addIndent(-1);
		out.println("}finally{");
		out.println(1,"stmt.close();");
		out.println("}");

		out.addIndent(-1);
		out.println("}");
		out.addIndent(-1);
		out.println("");
	}

	public static final void writeSqlSelect1(
		JSourceCodeWizard wizard,JSourceCodeWizardChannel dataChannel,ArrayList vecItem,IndentWriter out)
		throws IOException
	{
		out.addIndent(1);

		out.beginComment();
		out.println(dataChannel.getName()+"("+dataChannel.getTitle()+") 偺DB昞 1峴専嶕<BR>");
		out.println("");
		out.println("  @param Connection 愙懕僐僱僋僔儑儞");

		for(int index=0;index<vecItem.size();index++)
		{