package DB_connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by Konstantin on 15.03.2018.
 */
public class ContectToDB {

  /*  private  static final String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=WINTOUR";
    private  static final String user = "";
    private  static final String password = "";

    // JDBC variables for opening and managing connection
    private static Connection con;
    private static Statement stmt;
*/

    public ContectToDB(){
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            System.out.println("Driver loading success!");
            String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=ElectronicVt";
            String user = "";
            String password = "";

            try {
                Connection con = DriverManager.getConnection(url, user, password); // Создание соединения с БД.
                System.out.println("Connected!");
                Statement st = con.createStatement();
                String query = "select * from people";
                ResultSet rs = st.executeQuery(query);

                System.out.println("Print result of our query.\n");
                printResult(rs);

                con.close(); //Закрытие подключение к БД.
                System.out.println("Disconnected!");
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("Something wrong with connection!");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static void printResult(ResultSet res)throws SQLException{
        String name, lname;
        int age, sex;
        String str_sex;

        while(res.next()){
            name = res.getString("Name");
            lname = res.getString("LName");
            age = res.getInt("Age");
            sex = res.getInt("Sex");
            if(sex==1)
                str_sex = "M";
            else {
                if (sex < 0)
                   str_sex = "NULL";
                else
                   str_sex = "Ж";
            }
            System.out.println("Name: "+name+", LName: "+lname+", Age:"+age+", Sex: "+str_sex+"\n");
        }


    }

    public static void main(String[] args) {
        ContectToDB  CTBD = new ContectToDB();
    }

}