x1vc67LT

1package chessleague;
2
3import java.io.IOException;
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9
10public class FullJoinPlayerClub {
11    
12    public static void main(String[] args) throws SQLException, IOException { 
13    try 
14    {
15      Class.forName ("com.mysql.jdbc.Driver");
16        try ( //username = root, there is no password
17                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/chessleague","root","")) {
18            Statement stmnt = con.createStatement();
19            
20            String createProcedure = null;
21            String queryDrop = "DROP PROCEDURE IF EXISTS SELECT_CLUBS";
22            createProcedure = "create procedure SELECT_CLUBS()"
23                + "begin"
24                + "select tblClub.ClubName"
25                + "from tblClub"
26                + "order by ClubName;"
27                + "end";
28            Statement stmt = null;
29            Statement stmtDrop = null;
30            try {
31                System.out.println("Calling DROP Procedure");
32                stmtDrop = con.createStatement();
33                stmtDrop.execute(queryDrop);
34            } catch (SQLException e) {
35                //JDBCTutorialUtilities.printSQLException(e);
36
37            } finally {
38                if (stmtDrop != null)
39                {
40                    stmtDrop.close();
41                }
42            }
43            try {
44                stmt = con.createStatement();
45                stmt.executeUpdate(createProcedure);
46            } catch (SQLException e) {
47                //JDBCTutorialUtilities.printSQLExceptions(e);
48
49            } finally {
50                if (stmt != null) { stmt.close(); }
51            }
52            
53            //Select all players from Player table
54
55//            ResultSet resultSelect=stmnt.executeQuery("SELECT 'Creating procedure JOIN_PLAYERS' AS ' '|" +
56//                                "create procedure if not exists SHOW_SUPPLIERS()" +
57//                                "begin" +
58//                                "select SUPPLIERS.SUP_NAME," +
59//                                "COFFEES.COF_NAME" +
60//                                "from SUPPLIERS, COFFEES" +
61//                                "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID" +
62//                                "order by SUP_NAME;" +
63//                                "end|");
64//            System.out.println("Results: "); 
65//            while(resultSelect.next())
66//            {
67//                System.out.println(
68//                                "PlayerName: "+resultSelect.getString(1)+"  "+
69//                                        "DOB: "+resultSelect.getDate(2)+"    "+
70//                                                "FIDERating: "+resultSelect.getInt(3)+ " "+
71//                                                        "FIDETitle: "+resultSelect.getString(4)+ "  "+
72//                                                                "ClubName: "+resultSelect.getString(5)+ "  "+ 
73//                                                                        "Address: "+resultSelect.getString(6)+ "  "+
74//                                                                                "Founded: "+resultSelect.getDate(7));
75//            }
76            
77//            ResultSet resultSelect=stmnt.executeQuery("SELCECT player.playername, player.fiderating, player.fidetitle, club.clubname"
78//                    + " INNER JOIN player ON club.clubname = player.clubname");
79//            System.out.println("Results: "); 
80//            while(resultSelect.next())
81//            {
82//                System.out.println(
83//                                "PlayerName: "+resultSelect.getString(1)+"  "+
84//                                        "DOB: "+resultSelect.getDate(2)+"    "+
85//                                                "FIDERating: "+resultSelect.getInt(3)+ " "+
86//                                                        "FIDETitle: "+resultSelect.getString(4)+ "  "+
87//                                                                "ClubName: "+resultSelect.getString(5));
88//            }
89            
90            con.close();
91        }
92    }
93    catch (ClassNotFoundException | SQLException e){ System.out.println(e); 
94    } 
95    }
96}