create database tools_db;

create table zipcode( 
  num INT UNSIGNED,
  pref TEXT,
  city TEXT,
  address TEXT,
  index(num), 
  FULLTEXT INDEX(pref,city,address) COMMENT 'parser "TokenMecab"' 
) engine=mroonga DEFAULT CHARSET utf8;

package ROOT;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import slib.SDB;

public class UpdateZipcode {

	public static void main(String[] args)
	{
		// "d:\\zipcode\\ken_all.csv"
		String strCSV = args[0];
		
		List<String> listSQL = new ArrayList<String>();
		{
			//01101,"060  ","0600007","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ7ｼﾞｮｳﾆｼ","北海道","札幌市中央区","北七条西",0,0,1,0,0,0
			Pattern pattern = Pattern.compile("^.+?,.+?,\"([0-9]+)\",.+?,.+?,.+?,\"(.+?)\",\"(.+?)\",\"(.+?)\",.*$");
			
			File file = new File(strCSV);
			if (file.canRead())
			{
				System.out.println("Start reading the CSV.");
				
				FileReader fileReader = null;
				BufferedReader bufferedReader = null;
					
				try
				{
					fileReader = new FileReader(file);
					bufferedReader = new BufferedReader(fileReader);
					
					int nValues = 0;
					StringBuffer bufSQL = new StringBuffer();

					while(true)
					{
						String strLine = bufferedReader.readLine();
						if (strLine != null)
						{
						    Matcher matcher = pattern.matcher(strLine);
						    if (matcher.find()) 
						    {
						    	String strZipcode = matcher.group(1);
						    	String strPref = matcher.group(2);
						    	String strCity = matcher.group(3);
						    	String strAddress = matcher.group(4);
						    	if (strAddress.startsWith("以下に掲載がない場合"))
						    	{
						    		strAddress = "";
						    	}
						    	
						    	if (bufSQL.length() != 0)
						    	{
						    		bufSQL.append(",");
						    	}
						    	
						    	bufSQL.append("(");
						    	bufSQL.append(strZipcode);
						    	bufSQL.append(",\"");
						    	bufSQL.append(strPref);
						    	bufSQL.append("\",\"");
						    	bufSQL.append(strCity);
						    	bufSQL.append("\",\"");
						    	bufSQL.append(strAddress);
						    	bufSQL.append("\")");
						    	nValues++;
						    }
						}
						
				    	if (nValues == 100 || strLine == null)
				    	{
				    		listSQL.add(bufSQL.toString());
				    		nValues = 0;
				    		bufSQL = new StringBuffer();
				    	}
				    	
				    	if (strLine == null)
				    	{
				    		break;
				    	}
					}
				}
				catch (Exception ex)
				{
					ex.printStackTrace();
				}
				finally
				{
					try
					{
						if (bufferedReader != null)
						{
							bufferedReader.close();
							bufferedReader = null;
						}
						
						if (fileReader != null)
						{
							fileReader.close();
							fileReader = null;
						}
					}
					catch (IOException e)
					{
						e.printStackTrace();
					}
				}			
			}
		}
			
		if (listSQL.size() != 0)
		{
			SDB db = new SDB();
			Boolean bConnect = db.connectDirect("jdbc:mysql://192.168.24.6:3306/tools_db", "root", password);
			if (bConnect)
			{
				System.out.println("Delete all records.");
				db.executeUpdate("delete from zipcode");
				
				for (int n = 0; n < listSQL.size(); n++)
				{
					String strSQL = listSQL.get(n);
					int nRet = db.executeUpdate("insert into zipcode (num, pref, city, address) values " + strSQL);
					if (nRet == 0)
					{
						System.out.println("Error");
					}
				}	
				
				System.out.println("Finished.");
				db.close();
			}
		}
	}
}

select * from zipcode where num=4820003;
select * from zipcode where match(pref,city,address) against('岩倉市');