KeYSuBgg

1package database;
2import java.sql.Connection;
3import java.sql.DatabaseMetaData;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.sql.Statement;
8import java.util.ArrayList;
9import java.util.List;
10
11import it.polito.latazza.data.Beverage;
12import it.polito.latazza.data.Beverage;
13public class InterfaceDB {
14	
15	private static Connection conn;
16	
17	 public static void createNewDatabase() {
18		 
19	        String url = "jdbc:sqlite:latazza.db";
20	        Statement statement = null;
21	 
22	        try (Connection conn = DriverManager.getConnection(url)) {
23	            if (conn != null) {
24	                DatabaseMetaData meta = conn.getMetaData();
25	                System.out.println("The driver name is " + meta.getDriverName());
26	                System.out.println("A new database has been created.");
27	                
28	               statement=conn.createStatement();
29	                String sql = "CREATE TABLE IF NOT EXISTS EMPLOYEE" +
30	                "(ID INT PRIMARY KEY NOT NULL,"+
31	                		"NAME TEXT NOT NULL,"+
32	                "SURNAME TEXT NOT NULL,"+
33	                		"BALANCE INT NOT NULL)";
34	                statement.executeUpdate(sql);
35	                statement.close();
36	                
37	                statement=conn.createStatement();
38	                sql = "CREATE TABLE IF NOT EXISTS BEVERAGE" +
39	                "(ID INT PRIMARY KEY NOT NULL,"+
40	                		"NAME TEXT NOT NULL,"+
41	                "PRICE INT NOT NULL,"+
42	                		"CAPSULES_BOX INT NOT NULL,"+
43	                "BOX_PRICE INT NOT NULL,"+
44	                		"QUANTITY INT NOT NULL)";
45	                statement.executeUpdate(sql);
46	                statement.close();
47	                
48	                statement=conn.createStatement();
49	                sql = "CREATE TABLE IF NOT EXISTS LATAZZA_ACCOUNT" +
50	                "(BALANCE INT PRIMARY KEY NOT NULL)";
51	                statement.executeUpdate(sql);
52	                statement.close();
53
54	            }
55	 
56	        } catch (SQLException e) {
57	            System.out.println(e.getMessage());
58	        }
59	    }
60	 
61	public static void connect() {
62        conn = null;
63        try {
64            // db parameters
65        	String url = "jdbc:sqlite:latazza.db";
66            // create a connection to the database
67            conn = DriverManager.getConnection(url);
68            
69            System.out.println("Connection to SQLite has been established.");
70            
71        } catch (SQLException e) {
72            System.out.println(e.getMessage());
73        } /*finally {
74            try {
75                if (conn != null) {
76                    conn.close();
77                }
78            } catch (SQLException ex) {
79                System.out.println(ex.getMessage());
80            }
81        }*/
82    }
83	
84	public static void insertEmployee(Integer id, String name, String surname, Integer balance)
85	{
86		try
87		{
88			Statement statement=conn.createStatement();
89	        String sql="INSERT INTO EMPLOYEE (ID,NAME,SURNAME,BALANCE) "
90	        		+"VALUES(" +id +",'"+name+"','"+surname+"',"+balance+");";
91	        statement.executeUpdate(sql);
92	        statement.close();
93	        
94	        statement=conn.createStatement();
95            sql="SELECT * FROM EMPLOYEE";
96            ResultSet rs = statement.executeQuery(sql);
97           while(rs.next())
98           {
99        	   System.out.println(rs.getInt("id")+"\t"+rs.getString("name"));
100           }
101            statement.close();
102		}catch (SQLException e)
103		{
104			System.out.println(e.getMessage());
105		}
106		
107	}
108	
109	
110	public static void insertBeverage(Integer id, Integer price, Integer quantity, Integer capsulesPerBox, Integer boxPrice, String name)
111	{
112		try
113		{
114			Statement statement=conn.createStatement();
115	        String sql="INSERT INTO BEVERAGE (ID,NAME,PRICE,CAPSULES_BOX,BOX_PRICE,QUANTITY)"
116	        		+"VALUES(" +id +",'"+name+"',"+price+","+capsulesPerBox+","+boxPrice+","+quantity+");";
117	        statement.executeUpdate(sql);
118	        statement.close();
119	        
120		}catch (SQLException e)
121		{
122			System.out.println(e.getMessage());
123		}
124		
125	}
126	
127	public static void updateBeverage(Integer id, Integer capsulesPerBox, Integer boxPrice, String name)
128	{
129		try
130		{
131			Statement statement=conn.createStatement();
132	        String sql="UPDATE BEVERAGE SET NAME = '"+name+"', CAPSULES_BOX = "+capsulesPerBox+", BOX_PRICE = "+boxPrice+" WHERE ID = "+id+";"; 
133	        statement.executeUpdate(sql);
134	        statement.close();
135	        
136		}catch (SQLException e)
137		{
138			System.out.println(e.getMessage());
139		}
140		
141	}
142
143	public static List<Beverage> getBeverages() {
144		List<Beverage> beverages = new ArrayList<Beverage>();
145		String sql="SELECT * FROM BEVERAGE";
146		Statement statement = null;
147		try{
148			statement=conn.createStatement();
149					ResultSet rs = statement.executeQuery(sql);
150	        while(rs.next())
151	        {
152	     	   Beverage b = new Beverage(rs.getInt("ID"),rs.getString("NAME"),rs.getInt("CAPSULES_BOX"),rs.getInt("BOX_PRICE"),rs.getInt("QUANTITY"),rs.getInt("PRICE"));
153	     	  beverages.add(b);
154	        }
155	         statement.close();
156			
157		} catch (SQLException e) {
158			System.out.println(e.getMessage());
159		}
160		
161		return beverages;
162	}
163	
164}