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


public class mysqlConnection {

	public static void main(String[] args)
	{
		 Connection conn;
		try
		{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception ex) {/* handle the error*/}

        try
        {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","Braude");
            //Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.3.68/test","root","Root");
            System.out.println("SQL connection succeed");
            //createTableCourses(conn);
            printCourses(conn);
            updateParisBefore15( conn);
            updatespec( conn, "Paris", "testing 9999");
     	} catch (SQLException ex)
     	    {/* handle any errors*/
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            }



   	}

	public static void printCourses(Connection con)
	{
		Statement stmt;
		try
		{
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM flights WHERE flgiht ='KU101';");
	 		while(rs.next())
	 		{
				 if(rs.getString(2).equals("KU101"))
				 {
					 System.out.println(""+rs.getTime(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getInt(5));
					 String s =""+ rs.getTime(1);
					 Statement stmtx;
					 stmtx = con.createStatement();
					 stmtx.executeUpdate("UPDATE flights SET delay='landed 13:13' WHERE flgiht='KU101';");

				 }
	 		}
			rs.close();
			//stmt.executeUpdate("UPDATE course SET semestr=\"W08\" WHERE num=61309");
		} catch (SQLException e) {e.printStackTrace();}
	}


	public static void createTableCourses(Connection con1){
		Statement stmt;
		try {
			stmt = con1.createStatement();
			stmt.executeUpdate("create table courses(num int, name VARCHAR(40), semestr VARCHAR(10));");
			stmt.executeUpdate("load data local infile \"courses.txt\" into table courses");

		} catch (SQLException e) {	e.printStackTrace();}

	}

	public static void updateParisBefore15(Connection con)
	{
		Statement stmt;
		try
		{
		stmt = con.createStatement();
		stmt.executeUpdate("UPDATE flights SET delay='test 13:13' WHERE frum ='Paris' && num<'15:00';");
		ResultSet rs = stmt.executeQuery("SELECT * FROM flights;");
		while(rs.next())
		{
			if(rs.getString(3).equals("Paris"))
				System.out.println(""+rs.getTime(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getInt(5));
		}
		}catch (SQLException e) {e.printStackTrace();}
	}

	public static void updatespec(Connection con,String frum,String delay)
	{
		PreparedStatement ps;
		try {
			ps = con.prepareStatement("UPDATE flights SET delay= ? WHERE frum = ?");
			ps.setString(2,frum);
			ps.setString(1,delay);
			ps.executeUpdate();


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}



	}


}