package qc.autofun.java.extfun.sql;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.testng.annotations.DataProvider;
import qc.autofun.java.web.base.TableInfo;

public class OperDb {

    List<TableInfo> tableInfoList = new ArrayList<TableInfo>();

    //链接数据库
    public static Connection getConnection() throws SQLException, java.lang.ClassNotFoundException {
        String dburl = "jdbc:mysql://127.0.0.1:3306/xxh";
        Class.forName("com.mysql.jdbc.Driver");
        String userName = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(dburl, userName, password);
        return con;
    }

    //读取数据库并存放到list中
    @DataProvider(name = "userpwd")
    public List<TableInfo> readData() {
        try {
            Connection con = getConnection();
            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery("select * from userpwd");

            while (rs.next()) {
                TableInfo ti = new TableInfo();
                ti.setCaseId(rs.getInt("caseID"));
                ti.setAccount(rs.getString("uName"));
                ti.setPassword(rs.getString("uPwd"));
                ti.setExpDialog(rs.getString("expDialog"));
                tableInfoList.add(ti);
            }
            stmt.close();
            con.close();
        } catch (java.lang.ClassNotFoundException e) {
            System.err.println("ClassNotFoundException:" + e.getMessage());
        } catch (SQLException ex) {
            System.err.println("SQLException:" + ex.getMessage());
        }
        return tableInfoList;
    }

    //根据VerifyResult来写数据库
    public void updateData(String tag, int caseId) {
        try {
            Connection con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select * from userpwd where caseID = " + caseId);
            rs.first();
            String s = rs.getString("expDialog");
            stmt.executeUpdate("update userpwd set actDialog = '" + tag + "' where caseID = " + caseId);

            if (s.equals(tag)) {
                System.out.println("exptag:"+s);
                stmt = con.createStatement();
                stmt.executeUpdate("update userpwd set result = 'Pass' where caseID = " + caseId);
            } else {
                System.out.println("exptag:"+s);
                stmt = con.createStatement();
                stmt.executeUpdate("update userpwd set result = 'Fail' where caseID = " + caseId);
            }
            rs.close();
            stmt.close();
            con.close();
        } catch (java.lang.ClassNotFoundException e) {
            System.err.println("ClassNotFoundException:" + e.getMessage());
        } catch (SQLException ex) {
            System.err.println("SQLException:" + ex.getMessage());
        }

    }

}