package org.maachang.mimdb.test ;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import org.maachang.mimdb.Embedded;

/**
 * [組み込み型][JDBC]郵便番号呼び出しサンプル.
 * 
 * @version 2014/01/20
 * @author masahito suzuki
 * @since MasterInMemDB 1.02
 */
public class ReadPostalJDBC {
    
    /** mimdbファイル格納先. **/
    private static final String FOLDER = "/C:/test/mimdbSample" ;
    
    /** main. **/
    public static final void main( String[] args ) throws Exception {
        
        ResultSet rs ;
        
        // mimdbインスタンスを初期化.
        Embedded.create( FOLDER ) ;
        
        // JDBCからのアクセス.
        Connection conn = driverConnection( 
            "org.maachang.mimdb.Driver",
            "jdbc:mimdb:embedded",
            null,null ) ;
        
        // 郵便番号.
        // 東京都千代田区=100-0000
        String zip = "1000000" ;
        
        // SQL実行.
        {
            
            // statemetモード.
            String sql = "select * from postal_master where id='"+zip+"';" ;
            
            Statement stmt = conn.createStatement() ; ;
            rs = stmt.executeQuery( sql ) ;
            
            // 結果表示.
            while( rs.next() ) {
                System.out.print( "code:" + rs.getObject( "code" ) ) ;
                System.out.print( " old_id:" + rs.getObject( "old_id" ) ) ;
                System.out.print( " id:" + rs.getObject( "id" ) ) ;
                System.out.print( " pref_kana:" + rs.getObject( "pref_kana" ) ) ;
                System.out.print( " city_kana:" + rs.getObject( "city_kana" ) ) ;
                System.out.print( " town_kana:" + rs.getObject( "town_kana" ) ) ;
                System.out.print( " pref:" + rs.getObject( "pref" ) ) ;
                System.out.print( " city:" + rs.getObject( "city" ) ) ;
                System.out.print( " town:" + rs.getObject( "town" ) ) ;
                System.out.println() ;
            }
            
        }
        {
            // Preparedモード.
            String sql = "select * from postal_master where id=? ;" ;
            
            PreparedStatement ps = conn.prepareStatement( sql ) ;
            
            // パラメータセット.
            ps.setString( 1, zip ) ;
            
            // 実行処理.
            rs = ps.executeQuery() ;
            
            // 結果表示.
            while( rs.next() ) {
                System.out.print( "code:" + rs.getObject( "code" ) ) ;
                System.out.print( " old_id:" + rs.getObject( "old_id" ) ) ;
                System.out.print( " id:" + rs.getObject( "id" ) ) ;
                System.out.print( " pref_kana:" + rs.getObject( "pref_kana" ) ) ;
                System.out.print( " city_kana:" + rs.getObject( "city_kana" ) ) ;
                System.out.print( " town_kana:" + rs.getObject( "town_kana" ) ) ;
                System.out.print( " pref:" + rs.getObject( "pref" ) ) ;
                System.out.print( " city:" + rs.getObject( "city" ) ) ;
                System.out.print( " town:" + rs.getObject( "town" ) ) ;
                System.out.println() ;
            }
            
        }
    }
    
    /** 読み込み専用コネクションオブジェクトを取得. **/
    private static final Connection driverConnection( String driver,String url,String user,String passwd )
        throws Exception {
        Connection ret ;
        Class.forName( driver ) ;
        Properties p = new java.util.Properties() ;
        
        if( user == null || user.length() <= 0 ) {
            p.put("user","") ;
            p.put("password","") ;
            ret = DriverManager.getConnection( url,p ) ;
        }
        else {
            p.put("user",user) ;
            p.put("password",passwd) ;
            ret = DriverManager.getConnection( url,p ) ;
        }
        return ret;
    }
    
}
