/* A simple test for query on r_kardex table(2000000 rows)

   Tested with AMD2500 on 2006-05-14

    Select kxnombre from r_kardex where kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4) = '12151  3  24'"

 Spent time/row count:234ms/61

 Spent time/row count:16ms/61

*/

import java.sql.*;

import java.util.Properties;



public class testDBFSpeed2{

    public static void main(String argv[]){

        try{

            Class.forName("com.hxtt.sql.dbf.DBFDriver");





            String url="jdbc:dbf:////d:/test";



            Properties properties=new Properties();



            Connection con = DriverManager.getConnection(url,properties);



            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            String sql="create table if not exists r_kardex (kxseccion char(1),kxlibro numeric(4,0),kxfolio numeric(3,0), kxinscrip numeric(4,0), kxnombre varchar(10)); "

                ;

            boolean initDataBaseFlag=stmt.executeUpdate(sql)>0;

            stmt.close();



            if(initDataBaseFlag){

                sql="insert into r_kardex (kxseccion,kxlibro,kxfolio,kxinscrip,kxnombre) values(?,?,?,?,?);";

                PreparedStatement pstmt = con.prepareStatement(sql);

                for(int i=0;i<2000000;i++){

                    pstmt.setString(1,((char)('0'+(i%10)))+"");

                    pstmt.setInt(2,2000+(i%1000));

                    pstmt.setInt(3,i%12);

                    pstmt.setInt(4,i%33);

                    pstmt.setString(5,"abc"+i);

                    pstmt.executeUpdate();

                }

                pstmt.executeUpdate("create index aaa,bbb,ccc,ddd of r_kardex.cdx on r_kardex (kxseccion,kxlibro,kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4),kxinscrip);");





                pstmt.close();

            }





            sql="Select kxnombre from r_kardex where kxseccion+str(kxlibro,4)+str(kxfolio,3)+str(kxinscrip,4) = '12151  3  24'";

            PreparedStatement pstmt = con.prepareStatement(sql);

            for(int i=0;i<2;i++){

                long currentTime=System.currentTimeMillis();

                ResultSet rs = pstmt.executeQuery(sql);

                rs.beforeFirst();

                int rowCount=0;

                while (rs.next()) {

                    rs.getObject(1);

                    rowCount++;

                }

                currentTime=System.currentTimeMillis()-currentTime;



                rs.close();

                System.out.println("Spent time/row count:" +currentTime+"ms/"+rowCount );

            }



            pstmt.close();



            con.close();





        }

        catch( SQLException sqle )

        {

            do

            {

                System.out.println(sqle.getMessage());

                System.out.println("Error Code:"+sqle.getErrorCode());

                System.out.println("SQL State:"+sqle.getSQLState());

                sqle.printStackTrace();

            }while((sqle=sqle.getNextException())!=null);

        }

        catch( Exception e )

        {

            System.out.println(e.getMessage());

            e.printStackTrace();

        }

    }

}