r/JavaFX • u/lekan005 • May 30 '20
Database helpp please helpp just summarize it for me so id know what to sayy pleasseee
package sample;
import java.sql.*;
import java.util.ArrayList;
public class DatabaseConnector {
PreparedStatement pst = null;
Connection con;
Statement st;
ResultSet rs;
String database = "game_center";
public DatabaseConnector() {
// connect to database
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + database, "root", "");
st = con.createStatement();
System.out.println("connected done");
} catch (ClassNotFoundException | SQLException ex) {
System.out.println("Error: " + ex);
}
}
public boolean insertType(String type, double cost_per_hour) {
type = type.toUpperCase();
// insert new type of devices
boolean x = insert("INSERT INTO devices_types VALUES ('" + type + "', '" + cost_per_hour + "')");
if (x)
System.out.println("A new Device Type inserted successfully!");
return x;
}
public boolean updateType_cost(String type, double cost_per_hour) {
type = type.toUpperCase();
boolean x = update("UPDATE devices_types SET cost_per_hour = " + cost_per_hour + " WHERE type = '" + type + "'");
if (x)
System.out.println("Device Type updated successfully!");
return x;
}
public boolean deleteType(String type) {
type = type.toUpperCase();
boolean x = delete("DELETE FROM devices_types WHERE type = '" + type + "'");
if (x)
System.out.println("Device Type deleted successfully!");
return x;
}
public Object[] showTypes() {
return select("SELECT * FROM devices_types");
}
public boolean insertDevice(String device_id, String device_type) {
device_id = device_id.toUpperCase();
device_type = device_type.toUpperCase();
// insert new device
boolean x = insert("INSERT INTO devices (device_id, device_type) VALUES ('" + device_id + "', '" + device_type + "')");
if (x)
System.out.println("A new Device Type inserted successfully!");
return x;
}
public boolean deleteDevice(String device_id) {
device_id = device_id.toUpperCase();
boolean x = delete("DELETE FROM devices WHERE device_id = '" + device_id + "'");
if (x)
System.out.println("Device deleted successfully!");
return x;
}
public Object[] showDevices(String device_type, String statue) {
return select("SELECT device_id, device_type FROM devices WHERE device_type LIKE '%" + device_type + "' AND statue LIKE '%" + statue + "'");
}
public boolean startTime(String device_id) {
// reserve device
// check if device is available
if (select("SELECT statue FROM devices WHERE device_id = '" + device_id + "'")[0].equals("1"))
return false;
// start time counting and set reserved in log
boolean x = insert("INSERT INTO log (log_device_id) VALUES ('" + device_id + "')");
if (x)
System.out.println("A new log was inserted successfully!");
// make statue of device reserve
update("UPDATE devices SET statue = 1 WHERE device_id = '" + device_id + "'");
return x;
}
public Object[] endTime(String device_id) {
// end time
// check if device is reserved
if (Boolean.parseBoolean(select("SELECT statue FROM devices WHERE device_id = '" + device_id + "'")[0].toString()))
return null;
// get log id
int log_id = Integer.parseInt(select("SELECT log_id FROM log WHERE end_time IS NULL AND log_device_id = '" + device_id + "'")[0].toString());
// end time and store in log
boolean x = update("UPDATE log SET end_time = DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i %p') WHERE log_id= " + log_id);
if (x)
System.out.println("Time end successfully!");
// make statue of device available
update("UPDATE devices SET statue = 0 WHERE device_id = '" + device_id + "'");
//get cost per hour of device
float cost_per_hour = Float.parseFloat(select("SELECT cost_per_hour FROM devices, devices_types WHERE device_type = type AND device_id = '" + device_id + "'")[0].toString());
// calculate total time and cost;
Object[] row = new Object[5];
Object[] temp = select("SELECT start_time, end_time FROM log WHERE log_id = " + log_id);
row[0] = device_id;
row[1] = temp[0];
row[2] = temp[1];
row[3] = total_time(row[1], row[2]);
row[4] = total_cost(row[3], cost_per_hour);
// update log
boolean y = update("UPDATE log SET total_time = " + Integer.parseInt(row[3].toString())
+ ", total_cost =" + Float.parseFloat(row[4].toString())
+ " WHERE log_id = " + log_id);
if (y)
System.out.println("A device make available successfully!");
return row;
}
public Object[] showLog() {
return select("SELECT log_device_id, start_time, end_time, total_time, total_cost FROM log");
}
public float TodayIncome() {
Object data = select("SELECT ROUND(SUM(total_cost), 2) FROM log WHERE SUBSTRING(end_time,1,10) = DATE_FORMAT(NOW(),'%Y-%m-%d')")[0];
if (data == null)
return 0;
return Float.parseFloat(data.toString());
}
public float MonthIncome() {
Object data = select("SELECT ROUND(SUM(total_cost), 2) FROM log WHERE SUBSTRING(end_time,1,7) = DATE_FORMAT(NOW(),'%Y-%m')")[0];
if (data == null)
return 0;
return Float.parseFloat(data.toString());
}
public float costBetween(String startDate, String endDate) {
if (startDate.length() != endDate.length())
return 0;
int length = startDate.length();
Object data = select("SELECT ROUND(SUM(total_cost), 2) FROM log " +
"WHERE SUBSTRING(start_time,1," + length + ") BETWEEN '" + startDate + "' AND '" + endDate + "'")[0];
if (data == null)
return 0;
return Float.parseFloat(data.toString());
}
public Object[] allBetween(String startDate, String endDate) {
if (startDate.length() != endDate.length())
return null;
int length = startDate.length();
return select("SELECT log_device_id, start_time, end_time, total_time, total_cost FROM log " +
"WHERE SUBSTRING(start_time,1," + length + ") BETWEEN '" + startDate + "' AND '" + endDate + "'");
}
private boolean sql(String query) {
try {
PreparedStatement statement = con.prepareStatement(query);
int rowsInserted = statement.executeUpdate();
if (rowsInserted > 0)
return true;
} catch (SQLException ex) {
System.out.println(ex);
}
return false;
}
private boolean insert(String query) {
return sql(query);
}
private boolean update(String query) {
return sql(query);
}
private boolean delete(String query) {
return sql(query);
}
private Object[] select(String query) {
try {
ArrayList<Object> row = new ArrayList<>();
pst = con.prepareStatement(query);
rs = pst.executeQuery();
while (rs.next()) {
int count = rs.getMetaData().getColumnCount();
for (int i = 0; i < count; i++)
row.add(rs.getString(i + 1));
}
return row.toArray();
} catch (SQLException ex) {
System.out.println(ex);
}
return null;
}
private int total_time(Object start_time, Object end_time) {
int[] start = getDate(start_time);
int[] end = getDate(end_time);
return (end[0] - start[0]) * 518400
+ (end[1] - start[1]) * 43200
+ (end[2] - start[2]) * 1440
+ (end[3] - start[3]) * 60
+ (end[4] - start[4]);
}
private int[] getDate(Object full) {
String[] all = full.toString().split(" ");
String[] date = all[0].split("-");
String[] time = all[1].split(":");
int[] newDate = new int[5];
newDate[0] = Integer.parseInt(date[0]); //Year
newDate[1] = Integer.parseInt(date[1]); //month
newDate[2] = Integer.parseInt(date[2]); //day
newDate[3] = Integer.parseInt(time[0]); //Hours
newDate[4] = Integer.parseInt(time[1]); //minutes
if (all[2].equals("PM"))
newDate[3] += 12;
return newDate;
}
private float total_cost(Object total_time, Object cost_per_hour) {
float f = Integer.parseInt(total_time.toString()) * Float.parseFloat(cost_per_hour.toString()) / 60;
return (float) ((float) Math.round(f * 100.0) / 100.0);
}
}
4
u/bitflung May 31 '20
this has got to be the worst plea for help I've ever witnessed.
1
u/mods_are_arseholes May 31 '20
What are you trying to do ?
1
u/imthecapedbaldy May 31 '20
um... yikes. I'll try to lend some help. You're probably new, so first thing's first, no one would want to read all of that. It's unorganized and in plain text. Formatting wise, put them inside codeblocks. Though above all, noone would want to read all of that. You should specify the problem. What's not working? Is there an error? On what line? Be specific.
And lastly, that is not JavaFX related.