TzM05G4A

1/*
2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
5 */
6package harjoitustyo;
7
8import java.sql.Connection;
9import java.sql.DriverManager;
10import java.sql.PreparedStatement;
11import java.sql.ResultSet;
12import java.sql.SQLException;
13import java.sql.Statement;
14
15/**
16 *
17 * @author hakli
18 */
19public class Harjoitustyo {
20
21    private static Connection openConnection(String connString) throws SQLException {
22        Connection conn = DriverManager.getConnection(connString);
23        System.out.println("\t>> Tietokantayhteys muodostettu");
24        return conn;
25    }
26
27    private static void closeConnection(Connection c) throws SQLException {
28        if (c != null) {
29            c.close();
30        }
31        System.out.println("\t>> Tietokantayhteys suljettu");
32    }
33    
34    private static void createDatabase(Connection c, String db) throws SQLException {
35        
36        Statement stmt = c.createStatement();
37        stmt.executeQuery("DROP DATABASE IF EXISTS " + db);
38        System.out.println("\t>> Tietokanta " + db + " tuhottu");
39        
40        stmt.executeQuery("CREATE DATABASE " + db);
41        System.out.println("\t>> Tietokanta " + db + " luotu");
42        
43        ResultSet executeQuery = stmt.executeQuery("USE DATABASE"  + db);
44        System.out.println("\t>> Käytetään tietokantaa " + db);
45        
46        
47    }
48    
49    private static void createTable(Connection c, String sql) throws SQLException {
50        
51        Statement stmt = c.createStatement();
52        stmt.executeQuery(sql);
53        System.out.println("\t>> Taulu luotu");
54        
55    }
56    
57    private static void addName(Connection c, int opiskelija_id, String etunimi, String sukunimi, String suorituspvm) throws SQLException {
58        
59        PreparedStatement ps = c.prepareStatement(
60        "INSERT INTO nimet (opiskelija_id, etunimi, sukunimi, suorituspvm) "
61        + "VALUES (?,?,?,STR_TO_DATE(?, '%d.%m.%Y'))"
62        );
63        
64        
65        ps.setInt(1, opiskelija_id);
66        ps.setString(2, etunimi);
67        ps.setString(3, sukunimi);
68        ps.setString(4, suorituspvm);
69        ps.execute();
70        
71        System.out.println("\t>> Lisätty " + etunimi);
72        
73    }
74    
75    private static void selectNames(Connection c) throws SQLException {
76        Statement stmt = c.createStatement();
77        ResultSet rs = stmt.executeQuery (
78        "SELECT opiskelija_id, etunimi, sukunimi, suorituspvm "
79                + "FROM nimet "
80                + "ORDER BY nimi"
81        );
82        
83        System.out.println("\nNimilista:\n===============");
84        
85        while(rs.next()) {
86            
87            System.out.println(
88            "[" + rs.getInt("opiskelija_id") + "] "
89             + rs.getString("etunimi" + "sukunimi") + ": "
90             + rs.getDate("suorituspvm")
91            
92            );
93        }
94        
95    }
96    
97
98    /**
99     * @param args the command line arguments
100     * @throws java.sql.SQLException
101     */
102    public static void main(String[] args) throws SQLException {
103        Connection conn = openConnection(
104                "jdbc:mariadb://maria.westeurope.cloudapp.azure.com:"
105                + "3306?user=opiskelija&password=opiskelija1"
106        );
107            
108        createDatabase(conn, "karelia_joonahak");
109        
110        createTable(conn,
111                "CREATE TABLE nimet ("
112                + "opiskelija_id INT NOT NULL PRIMARY KEY,"
113                + "etunimi VARCHAR (25),"
114                + "sukunimi VARCHAR (25),"
115                + "suorituspvm DATE)"
116        );
117        
118        
119              /* createTable(conn,
120                "CREATE TABLE kurssit ("
121                + "kurssin_nimi VARCHAR (40) NOT NULL PRIMARY KEY,"
122                + "laajuus VARCHAR (20)"
123        ); */
124              
125              
126              
127                /* createTable(conn,
128                "CREATE TABLE opiskelijat ("
129                + "opiskelijanro INT NOT NULL PRIMARY KEY,"
130                + "etunimi VARCHAR (20),"
131                + "sukunimi VARCHAR (25)"
132        ); */
133                
134                
135                
136                /* createTable(conn,
137                "CREATE TABLE suoritukset ("
138                + "suoritus_id INT NOT NULL PRIMARY KEY,"
139                + "opiskelijanro INT,"
140                + "arvosana INT,"
141                + "suorituspvm DATE,"
142                + "kurssin_nimi VARCHAR (40)"
143        ); */
144        
145        
146        
147        
148        addName(conn, 1, "Joona", "Hakli", "10.5.2021");
149        addName(conn, 2, "Jouni", "Kekkonen", "1.4.1999");
150        addName(conn, 3, "Mirkku", "Makkonen", "30.11.1981");
151        
152        // addSuoritus(conn, 2006300, 3, "10.5.2021", "Kayttoliittymaohjelmointi");
153        // addKurssi(conn, "Kayttoliittymaohjelmointi", "5OP");
154        // addOpiskelija (conn, 2006300, "Joona", "Hakli");
155        
156        
157        selectNames(conn);
158        // selectSuoritukset
159        // selectKurssit
160        
161        closeConnection(conn);
162
163    }
164
165}