public static Vector<DBColumnModel> getColumnByCode(String tableCode) {
if (conn == null) conn = DBConnection.getConnection();
Vector<DBColumnModel> result = new Vector<DBColumnModel>();
String query = " SELECT * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableCode + "'";
Statement stmt;
try {
stmt = conn.createStatement();
stmt.executeQuery(query);
ResultSet rst = stmt.getResultSet();
while (rst.next()) {
DBColumnModel desc = new DBColumnModel();
desc.setKod(rst.getString(4));
String isNullable = rst.getString(7);
if (isNullable.equalsIgnoreCase("YES")) {
desc.setNullable(true);
} else {
desc.setNullable(false);
}
desc.setTip(rst.getString(8));
if (rst.getString(9) != null) {
desc.setDuzina(new Integer(rst.getString(9)));
}
result.add(desc);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void save(CurrentProduct currentProduct) {
con = dbCon.geConnection();
try {
pst = con.prepareStatement("insert into Products values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
pst.setString(1, null);
pst.setString(2, currentProduct.productId);
pst.setString(3, currentProduct.productName);
pst.setString(4, currentProduct.quantity);
pst.setString(5, currentProduct.description);
pst.setString(6, currentProduct.supplierId);
pst.setString(7, currentProduct.brandId);
pst.setString(8, currentProduct.catagoryId);
pst.setString(9, currentProduct.unitId);
pst.setString(10, currentProduct.pursesPrice);
pst.setString(11, currentProduct.sellPrice);
pst.setString(12, currentProduct.rmaId);
pst.setString(13, currentProduct.userId);
pst.setString(14, currentProduct.date);
pst.executeUpdate();
pst.close();
con.close();
} catch (SQLException ex) {
Logger.getLogger(CurrentProduct.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("Too Many Connection");
}
}
public Vector<Message> getInboxMessages(String username, int type) {
if (type == 4) username = "******";
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE toName = \""
+ username
+ "\" AND messageType = "
+ type
+ " ORDER BY timeStamp DESC");
Vector<Message> inbox = new Vector<Message>();
try {
while (rs.next()) {
inbox.add(
new Message(
rs.getString("fromName"),
username,
rs.getString("message"),
rs.getInt("messageType"),
rs.getTimestamp("timeStamp")));
}
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::getInboxMessages");
}
return inbox;
}
public boolean isNotSoled(CurrentProduct currentProduct) {
con = dbCon.geConnection();
boolean isNotSoled = false;
try {
pst = con.prepareStatement("select * from Sell where ProductId=?");
pst.setString(1, currentProduct.id);
rs = pst.executeQuery();
while (rs.next()) {
Dialogs.create()
.title("")
.masthead("Error")
.message("This product has been soled you can't delete it")
.styleClass(Dialog.STYLE_CLASS_UNDECORATED)
.showError();
return isNotSoled;
}
rs.close();
pst.close();
con.close();
isNotSoled = true;
} catch (SQLException ex) {
Logger.getLogger(CurrentProductGetway.class.getName()).log(Level.SEVERE, null, ex);
}
return isNotSoled;
}
public void searchByRMA(CurrentProduct currentProduct) {
con = dbCon.geConnection();
currentProduct.currentProductList.clear();
try {
pst = con.prepareStatement("select * from Products where RMAId=?");
pst.setString(1, currentProduct.rmaId);
rs = pst.executeQuery();
while (rs.next()) {
currentProduct.id = rs.getString(1);
currentProduct.productId = rs.getString(2);
currentProduct.productName = rs.getString(3);
currentProduct.quantity = rs.getString(4);
currentProduct.description = rs.getString(5);
currentProduct.supplierId = rs.getString(6);
currentProduct.brandId = rs.getString(7);
currentProduct.catagoryId = rs.getString(8);
currentProduct.unitId = rs.getString(9);
currentProduct.pursesPrice = rs.getString(10);
currentProduct.sellPrice = rs.getString(11);
currentProduct.rmaId = rs.getString(12);
currentProduct.userId = rs.getString(13);
currentProduct.date = rs.getString(14);
currentProduct.supplierName =
sql.getName(currentProduct.supplierId, currentProduct.supplierName, "Supplyer");
currentProduct.brandName =
sql.getName(currentProduct.brandId, currentProduct.brandName, "Brands");
currentProduct.catagoryName =
sql.getName(currentProduct.catagoryId, currentProduct.catagoryName, "Catagory");
currentProduct.unitName =
sql.getName(currentProduct.unitId, currentProduct.unitName, "Unit");
currentProduct.rmaName = sql.getName(currentProduct.rmaId, currentProduct.rmaName, "RMA");
currentProduct.userName =
sql.getName(currentProduct.userId, currentProduct.userName, "User");
currentProduct.currentProductList.addAll(
new ListProduct(
currentProduct.id,
currentProduct.productId,
currentProduct.productName,
currentProduct.quantity,
currentProduct.description,
currentProduct.supplierName,
currentProduct.brandName,
currentProduct.catagoryName,
currentProduct.unitName,
currentProduct.pursesPrice,
currentProduct.sellPrice,
currentProduct.rmaName,
currentProduct.userName,
currentProduct.date));
}
pst.close();
con.close();
rs.close();
} catch (SQLException ex) {
Logger.getLogger(CurrentProductGetway.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void view(CurrentProduct currentProduct) {
currentProduct.currentProductList.clear();
con = dbCon.geConnection();
try {
pst = con.prepareStatement("SELECT SQL_NO_CACHE * FROM Products");
rs = pst.executeQuery();
while (rs.next()) {
currentProduct.id = rs.getString(1);
currentProduct.productId = rs.getString(2);
currentProduct.productName = rs.getString(3);
currentProduct.quantity = rs.getString(4);
currentProduct.description = rs.getString(5);
currentProduct.supplierId = rs.getString(6);
currentProduct.brandId = rs.getString(7);
currentProduct.catagoryId = rs.getString(8);
currentProduct.unitId = rs.getString(9);
currentProduct.pursesPrice = rs.getString(10);
currentProduct.sellPrice = rs.getString(11);
currentProduct.rmaId = rs.getString(12);
currentProduct.userId = rs.getString(13);
currentProduct.date = rs.getString(14);
currentProduct.supplierName =
sql.getName(currentProduct.supplierId, currentProduct.supplierName, "Supplyer");
currentProduct.brandName =
sql.getName(currentProduct.brandId, currentProduct.brandName, "Brands");
currentProduct.catagoryName =
sql.getName(currentProduct.catagoryId, currentProduct.catagoryName, "Catagory");
currentProduct.unitName =
sql.getName(currentProduct.unitId, currentProduct.unitName, "Unit");
currentProduct.rmaName = sql.getName(currentProduct.rmaId, currentProduct.rmaName, "RMA");
currentProduct.userName =
sql.getName(currentProduct.userId, currentProduct.userName, "User");
currentProduct.currentProductList.addAll(
new ListProduct(
currentProduct.id,
currentProduct.productId,
currentProduct.productName,
currentProduct.quantity,
currentProduct.description,
currentProduct.supplierName,
currentProduct.brandName,
currentProduct.catagoryName,
currentProduct.unitName,
currentProduct.pursesPrice,
currentProduct.sellPrice,
currentProduct.rmaName,
currentProduct.userName,
currentProduct.date));
}
pst.close();
con.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(CurrentProduct currentProduct) {
con = dbCon.geConnection();
try {
pst = con.prepareStatement("delete from Products where id=?");
pst.setString(1, currentProduct.id);
pst.executeUpdate();
pst.close();
con.close();
} catch (SQLException ex) {
Logger.getLogger(CurrentProduct.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static void main(String[] args) {
DBConnection db = new DBConnection();
// set up jdbc connection
if (db.createConnection()) {
// read file to build auto
BuildAuto cars = new BuildAuto();
cars.buildAuto("Focus.txt");
cars.buildAuto("Prius.txt");
// create table from text file
ArrayList<String> sql = FileIO.readFile("sql.txt");
CreateTable table = new CreateTable(db, sql.get(0), sql.get(1), sql.get(2));
table.createTable();
// add new automobile to db
System.out.println("------------- add new automobile to database ---------------");
Automobile focus = cars.getAutoByModelname("Focus");
Automobile prius = cars.getAutoByModelname("Prius");
AddAuto add = new AddAuto(db);
add.addAuto(focus);
add.addAuto(prius);
String[] arr1 = {"model", "make", "base_price"};
String[] arr2 = {"model", "setname"};
String[] arr3 = {"model", "setname", "option_name", "price"};
int two = db.showAll("automobile", arr1);
if (two == 2) {
System.out.println("add automobile to database successfully");
} else {
System.out.println("test failed, add automobile error");
}
// update automobile
System.out.println(
"------------- update option price & optionSet name to database---------------");
UpdateDBAuto update = new UpdateDBAuto(db);
// update option price
update.updateOptionPrice("Focus", "Transmission", "Automatic", 20.0f);
update.updateOptionsetName("Prius", "PowerMoonroof", "Power");
db.showAll("optionset", arr2);
db.showAll("options", arr3);
// delete automobile
System.out.println("------------- delete automobile from database ---------------");
DeleteAuto delete = new DeleteAuto(db);
delete.deleteAuto(focus);
cars.delAutoByName("Focus");
int one = db.showAll("automobile", arr1);
if (one == 1) System.out.println("delete automobile from database successfully");
else System.out.println("test failed, delete automobile error");
System.out.println("");
db.closeConnection();
}
}
/**
* Puts a message in the database Note: the timestamp will be stored as the current time at which
* this method is called.
*/
public void storeMessage(Message message) {
con.updateDB(
"INSERT INTO "
+ MESSAGES
+ " VALUES ( \""
+ message.getFrom()
+ "\", \""
+ message.getTo()
+ "\", \""
+ message.getBody()
+ "\", "
+ null
+ ", "
+ message.getType()
+ ")");
}
public void cbSupplyer(CurrentProduct currentProduct) {
con = dbCon.geConnection();
try {
pst = con.prepareStatement("select * from Supplyer");
rs = pst.executeQuery();
while (rs.next()) {
currentProduct.supplyerList = rs.getString(2);
}
pst.close();
con.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* Puts a message in the database Note: the timestamp will be stored as the current time at which
* this method is called.
*/
public void storeMessage(
String from, String to, String message, int messageType, String timestamp) {
con.updateDB(
"INSERT INTO "
+ MESSAGES
+ " VALUES ( \""
+ from
+ "\", \""
+ to
+ "\", \""
+ message
+ "\", "
+ null
+ ", "
+ messageType
+ ")");
}
public boolean containsFriendRequest(String from, String to) {
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE fromName = \""
+ from
+ "\" AND toName = \""
+ to
+ "\" AND messageType = 2");
try {
if (rs.next()) return true;
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::containsFriendRequest");
}
return false;
}
public void sView(CurrentProduct currentProduct) {
con = dbCon.geConnection();
try {
pst = con.prepareStatement("select * from Products where ProductId=?");
pst.setString(1, currentProduct.productId);
rs = pst.executeQuery();
while (rs.next()) {
currentProduct.id = rs.getString(1);
currentProduct.productId = rs.getString(2);
currentProduct.productName = rs.getString(3);
currentProduct.quantity = rs.getString(4);
currentProduct.description = rs.getString(5);
currentProduct.supplierId = rs.getString(6);
currentProduct.brandId = rs.getString(7);
currentProduct.catagoryId = rs.getString(8);
currentProduct.unitId = rs.getString(9);
currentProduct.pursesPrice = rs.getString(10);
currentProduct.sellPrice = rs.getString(11);
currentProduct.rmaId = rs.getString(12);
currentProduct.userId = rs.getString(13);
currentProduct.date = rs.getString(14);
currentProduct.supplierName =
sql.getName(currentProduct.supplierId, currentProduct.supplierName, "Supplyer");
currentProduct.brandName =
sql.getName(currentProduct.brandId, currentProduct.brandName, "Brands");
currentProduct.catagoryName =
sql.getName(currentProduct.catagoryId, currentProduct.catagoryName, "Catagory");
currentProduct.unitName =
sql.getName(currentProduct.unitId, currentProduct.unitName, "Unit");
currentProduct.rmaName = sql.getName(currentProduct.rmaId, currentProduct.rmaName, "RMA");
currentProduct.userName =
sql.getName(currentProduct.userId, currentProduct.userName, "User");
currentProduct.rmaDayesss = sql.getDayes(currentProduct.rmaDayesss, currentProduct.rmaId);
long dateRMA = Long.parseLong(currentProduct.rmaDayesss);
currentProduct.warrentyVoidDate = LocalDate.now().plusDays(dateRMA).toString();
}
pst.close();
con.close();
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static boolean isForeignKey(String tableCode, String columnCode) {
if (conn == null) conn = DBConnection.getConnection();
String query = "SELECT * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;";
Statement stmt;
try {
stmt = conn.createStatement();
stmt.executeQuery(query);
ResultSet rst = stmt.getResultSet();
while (rst.next()) {
if (rst.getString(6).equalsIgnoreCase(tableCode)
&& rst.getString(7).equalsIgnoreCase(columnCode)
&& rst.getString(3).startsWith("PK")) {
return true;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public static Vector<String> getTableCodes() {
Vector<String> ret = new Vector<>();
String query = "SELECT * from INFORMATION_SCHEMA.TABLES;";
Statement stmt;
if (conn == null) conn = DBConnection.getConnection();
try {
stmt = conn.createStatement();
stmt.executeQuery(query);
ResultSet rst = stmt.getResultSet();
while (rst.next()) {
ret.add(rst.getString(3));
}
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return ret;
}
public static HashMap<String, String> getParentCodeAndColumnCodInParent(
String tableCode, String columnCode) throws SQLException {
if (conn == null) conn = DBConnection.getConnection();
if (dbMetadata == null) dbMetadata = conn.getMetaData();
HashMap<String, String> res = new HashMap<String, String>();
ResultSet result;
try {
result = dbMetadata.getImportedKeys(null, "dbo", tableCode);
while (result.next()) {
if (result.getString(8).equals(columnCode)) {
res.put(
result.getString(3), result.getString(4)); // 3 - naziv tabele iz koje je nasledjen
} // 4 - naziv koda u toj tabeli
}
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
/*
* Returns a Vector representing all messages in the user's outbox. The most recent
* messages are at the beginning, and the oldest ones are at the end.
*/
public Vector<Message> getOutboxMessages(String username) {
ResultSet rs =
con.queryDB(
"SELECT * FROM "
+ MESSAGES
+ " WHERE fromName = \""
+ username
+ "\" ORDER BY timeStamp DESC");
Vector<Message> outbox = new Vector<Message>();
try {
while (rs.next()) {
outbox.add(
new Message(
username,
rs.getString("toName"),
rs.getString("message"),
rs.getInt("messageType"),
rs.getTimestamp("timeStamp")));
}
} catch (SQLException e) {
throw new RuntimeException("SQLException in MessageDatabase::getOutboxMessages");
}
return outbox;
}
public void update(CurrentProduct currentProduct) {
con = dbCon.geConnection();
try {
pst =
con.prepareStatement(
"update Products set ProductId=?, ProductName=?, Quantity=?, Description=?, "
+ "SupplyerId=?, BrandId=?, CatagoryId=?,"
+ " UnitId=?, PursesPrice=?, SellPrice=?, RMAId=?, Date=? where Id=?");
pst.setString(1, currentProduct.productId);
pst.setString(2, currentProduct.productName);
pst.setString(3, currentProduct.quantity);
pst.setString(4, currentProduct.description);
pst.setString(5, currentProduct.supplierId);
pst.setString(6, currentProduct.brandId);
pst.setString(7, currentProduct.catagoryId);
pst.setString(8, currentProduct.unitId);
pst.setString(9, currentProduct.pursesPrice);
pst.setString(10, currentProduct.sellPrice);
pst.setString(11, currentProduct.rmaId);
pst.setString(12, currentProduct.date);
pst.setString(13, currentProduct.id);
pst.executeUpdate();
pst.close();
con.close();
rs.close();
Dialogs.create()
.lightweight()
.title("Update")
.masthead("Update Sucess")
.message("Update Success")
.showInformation();
} catch (SQLException ex) {
Logger.getLogger(CurrentProduct.class.getName()).log(Level.SEVERE, null, ex);
}
}
public DBQueryTable() {
conn = DBConnection.getConnection();
}
public MessageDatabase() {
con = DBConnection.getSharedConnection();
}