package com.webank.wedatasphere.linkis.ujes.jdbc;

/**
 * Created by leebai on 2019/8/23.
 */

/*
 * Notice:
 * if you want to test this module,you must rewrite default SQL we used for local test
 * */
import org.junit.*;

import java.sql.SQLException;

/*
 * Notice:
 * if you want to test this module,you must rewrite default parameters and SQL we used for local test
 * */

public class UJESSQLPreparedStatementTest {
    private static UJESSQLConnection conn;
    private UJESSQLPreparedStatement preStatement;
    @BeforeClass
    public static void getConnection(){
        try {
            conn = JDBCSpiTest.getConnection();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void crud(){
        preStatement = conn.prepareStatement("");
        preStatement.executeUpdate("CREATE TABLE if not exists johnnwang_qml.test1236 as select * from ai_fmi_ods.1000_10");
        preStatement.executeUpdate("insert into johnnwang_qml.test1236 select * from ai_fmi_ods.1000_10");
      //  preStatement.executeUpdate("update johnnwang_qml.test1236 set label=6 where label=1");
        preStatement.executeUpdate("select * from johnnwang_qml.test1236");
        UJESSQLResultSet resultSet = preStatement.getResultSet();
        showResult(resultSet);
        preStatement.execute("drop table johnnwang_qml.test1236");
        Assert.assertTrue(resultSet.isAfterLast());
    }

    @AfterClass
    public static void closeConnection(){
        conn.close();
    }


    @Test
    public void setObject() {
        preStatement = conn.prepareStatement("? ?");
        preStatement.setObject(1,"show");
        preStatement.setObject(2,"tables");
        Assert.assertTrue(preStatement.execute());
    }

    @Test
    public void execute() {
        preStatement = conn.prepareStatement("show tables");
        Assert.assertTrue(preStatement.execute());
    }

    @Test
    public void selectTest(){
        preStatement = conn.prepareStatement("select * from bdp_test");
        UJESSQLResultSet resultSet = preStatement.executeQuery();
        showResult(resultSet);
        Assert.assertTrue(resultSet.isAfterLast());
    }

    private void showResult(UJESSQLResultSet resultSet){
        while (resultSet.next()) {
            UJESSQLResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                System.out.print(metaData.getColumnName(i) + ":" +metaData.getColumnTypeName(i)+": "+ resultSet.getObject(i) + "    ");
            }
            System.out.println();
        }
    }

    @Test
    public void executeUpdate(){
        preStatement = conn.prepareStatement("show tables");
        Assert.assertEquals(preStatement.executeUpdate(),0);
    }


    @Test
    public void executeQuery() {
        preStatement = conn.prepareStatement("show tables");
        Assert.assertTrue(preStatement.executeQuery() instanceof UJESSQLResultSet);
    }
}