[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。
foreachの書き方
#foreach(変数１ in 変数２)
処理
#end
★javaソース
Manyo manyoA = new Manyo("manyoAAA");
Manyo manyoB = new Manyo("manyoBBB");
List<Manyo> list = new ArrayList<Manyo>();
list.add(manyoA);
list.add(manyoB);
VelocityContext vc = new VelocityContext();
vc.put("manyoList", list);
★テンプレート
#foreach($manyo in $manyoList)
$manyo.name
#end
$manyoList のなかに入っているオブジェクトを$manyo(Manyoクラス)で取り出す
$manyo は適当な変数。何でも良い。$xxx とかでも良い。
<!-- $sampleTempBean.title -->
<insert id="insert${sampleTempBean.historyTableName}MANYO" parameterClass="java.util.Map">
INSERT /* insert${sampleTempBean.historyTableName}MANYO */
INTO ${sampleTempBean.historyTableName}
(
#foreach($columName in $sampleTempBean.columNameList)
#if($velocityCount == 1)
$columName
#else
, $columName
#end
#end
)
select
#historyKey# AS SAMPLE_KEY
, to_char(#timeStamp#, 'YYYYMMDD') AS SAMPLE_YMD
, to_char(#timeStamp#, 'HH24MISS') AS SAMPLE_TRS
, '' AS SAMPLECD
#foreach($columName in $sampleTempBean.columNameList)
, $columName
#end
, #sampleId# AS SAMPLE_USR
, #timeStampr# AS SAMPLE_YMD
, #updateUserId# AS SAMPLE_USR
, #timeStamp# AS SAMPLE_TSTNP
from
$sampleTempBean.tableName
where
SAMPLE_KEY = #sampleKey#
</insert>
テンプレ2============================
// $sampleTempBean.title
tmp1.insert("insert${sampleTempBean.historyTableName}MANYO", param);
package jp.co.sample.main;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import jp.co.sample.bean.SampleTempBean;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ControllerExcel {
public static List<SampleTempBean> readExcel(String filepath)
throws FileNotFoundException, IOException {
File cdirectory = new File(filepath);
String[] xlsFilePaths = cdirectory.list();
List<SampleTempBean> SampleTempBeanList = new ArrayList<SampleTempBean>();
for (int i = 0; i < xlsFilePaths.length; i++) {
if (StringUtils.equals(xlsFilePaths[i], "CVS"))
continue;
POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(
filepath + xlsFilePaths[i]));
HSSFWorkbook wb = new HSSFWorkbook(filein);
// 総シート数取得
int sheets = wb.getNumberOfSheets();
for (int j = 0; j < sheets; j++) {// 各シートごとの処理
HSSFSheet sheet = wb.getSheetAt(j);
// 0行目から読み込み
for (int k =0; k<200; k++) {
HSSFRow row = sheet.getRow(k);
if (row == null){
continue;
}
HSSFCell messageB = row.getCell((short) 1);//フィールド名称
if (null != messageB){
SampleTempBean tempBean = new SampleTempBean();
// [start]======== 項目1 ==============
HSSFCell messageC = row.getCell((short) 2);//フィールド名称
tempBean.setTitle(messageC.getStringCellValue());// タイトル
// [ end ]======== 項目1 ==============
// [start]======== 項目2 ==============
HSSFRow row2 = sheet.getRow(++k);
HSSFCell messageC2 = row2.getCell((short) 2);//フィールド名称
tempBean.setTableName(messageC2.getStringCellValue());// テーブル
// [ end ]======== 項目2 ==============
// [start]======== 項目3 ==============
HSSFRow row3 = sheet.getRow(++k);
HSSFCell messageC3 = row3.getCell((short) 2);//フィールド名称
tempBean.setHistoryTableName(messageC3.getStringCellValue());
// [ end ]======== 項目3 ==============
// [start]======== 項目3 ==============
HSSFRow row4 = sheet.getRow(++k);
HSSFCell messageC4 = row4.getCell((short) 2);//フィールド名称
String temp = messageC4.getStringCellValue();
String[] strList = temp.split(",");
tempBean.setColumNameList(Arrays.asList(strList));
// [ end ]======== 項目3 ==============
SampleTempBeanList.add(tempBean);
}
}
}
}
return SampleTempBeanList;
}
}
package jp.co.sample.main;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.util.List;
import jp.co.sample.bean.SampleTempBean;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
public class SampleMain {
private static final String SQL_OUTPUT_PATH = "./sql/";
private static final String TABLE_TEIGI_PATH = "./xls/";
public static void main(String[] args) {
try {
/* テキストファイル読み込み */
List<SampleTempBean> sampleTempBeanList = ControllerExcel.readExcel(TABLE_TEIGI_PATH);
Velocity.init();// Velocity初期化
BufferedWriter writer = new BufferedWriter(new FileWriter(
SQL_OUTPUT_PATH + "動的SQL作成.txt"));
for (SampleTempBean sampleTempBean : sampleTempBeanList) {
VelocityContext ctx = new VelocityContext();
// テーブル名をセット
ctx.put("sampleTempBean", sampleTempBean);
Template template = Velocity
.getTemplate("./template/temp.vm");
template.merge(ctx, writer);
}
for (SampleTempBean sampleTempBean : sampleTempBeanList) {
VelocityContext ctx = new VelocityContext();
// テーブル名をセット
ctx.put("sampleTempBean", sampleTempBean);
Template template = Velocity
.getTemplate("./template/temp2.vm");
template.merge(ctx, writer);
}
writer.flush();
writer.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("致命的なエラーだ。大変だ・・・");
}
System.out.println("正常終了");
}
}
