p4gsxWLZ

1
2package demo.persistence;
3
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.SQLException;
7import java.sql.Statement;
8
9public class Database {
10
11	// JDBC driver name and database URL
12	static final String JDBC_DRIVER = "org.h2.Driver";
13	static final String DB_URL = "jdbc:h2:~/test-gorgeous-food";
14
15	// Database credentials
16	static final String USER = "sa";
17	static final String PASS = "";
18
19	static Connection conn = null;
20	
21	private static void initDatabase() {
22		Statement stmt = null;
23		try {
24			stmt = conn.createStatement();
25				String sqlMeal = "CREATE TABLE IF NOT EXISTS MEAL "
26									+ "mealId INTEGER auto_increment, "
27										+ "name VARCHAR(255) NOT NULL, "
28
29								
30													+ "averageWeight DOUBLE, "
31													+ "description VARCHAR(255), "
32													+ "minimumQuantity INTEGER, "
33								
34								
35					+ " PRIMARY KEY ( "
36									+ "mealId ), "
37					;
38				sqlMeal = sqlMeal.substring(0, sqlMeal.length()-2);
39				sqlMeal = sqlMeal + ")";
40				stmt.executeUpdate(sqlMeal);
41				String sqlIngredient = "CREATE TABLE IF NOT EXISTS INGREDIENT "
42									+ "ingredientId INTEGER auto_increment, "
43										+ "name VARCHAR(255) NOT NULL, "
44
45										+ "meal INTEGER NOT NULL, "
46
47								
48								
49													+ "description VARCHAR(255), "
50													+ "origin VARCHAR(255), "
51								
52					+ " PRIMARY KEY ( "
53									+ "ingredientId ), "
54					;
55				sqlIngredient = sqlIngredient.substring(0, sqlIngredient.length()-2);
56				sqlIngredient = sqlIngredient + ")";
57				stmt.executeUpdate(sqlIngredient);
58				String sqlMealItem = "CREATE TABLE IF NOT EXISTS MEALITEM "
59									+ "mealItemId INTEGER auto_increment, "
60										+ "name VARCHAR(255) NOT NULL, "
61
62										+ "meal INTEGER NOT NULL, "
63
64													+ "prodDate VARCHAR(255), "
65													+ "expDate VARCHAR(255), "
66								
67								
68								
69					+ " PRIMARY KEY ( "
70									+ "mealItemId ), "
71					;
72				sqlMealItem = sqlMealItem.substring(0, sqlMealItem.length()-2);
73				sqlMealItem = sqlMealItem + ")";
74				stmt.executeUpdate(sqlMealItem);
75			stmt.close();
76		} catch (SQLException se) {
77			// Handle errors for JDBC
78			se.printStackTrace();
79		} catch (Exception e) {
80			e.printStackTrace();
81		} finally {
82			// finally block used to close resources
83			try {
84				if (stmt != null)
85					stmt.close();
86			} catch (SQLException se2) {
87				se2.printStackTrace();
88			} // nothing we can do
89		} // end try
90	}
91
92	private static Connection initConnection(){
93		try {
94			// Register JDBC driver
95			Class.forName(JDBC_DRIVER);
96
97			// Open a connection
98			conn = DriverManager.getConnection(DB_URL, USER, PASS);
99			
100			initDatabase();
101
102		} catch (SQLException se) {
103			// Handle errors for JDBC
104			se.printStackTrace();
105			return null;
106		} catch (Exception e) {
107			// Handle errors for Class.forName
108			e.printStackTrace();
109			return null;
110		}
111
112		return conn;
113	}
114	
115	public static Connection getConnection(){
116		if (conn == null) {
117			conn = initConnection();
118		}
119		return conn;
120	}
121	
122	public static void closeConnection() {
123		if (conn != null) {
124			try {
125				conn.close();
126			} catch (SQLException se) {
127				// Handle errors for JDBC
128				se.printStackTrace();
129			} catch (Exception e) {
130				e.printStackTrace();
131			} finally {
132				conn = null;
133			}
134		}
135	}
136	
137}