Advanced_Topics_in_Databases/JDBController.java at master · vasilgeorge/Advanced_Topics_in_Databases · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
//package AuctionHouse;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author g_0zek
 *
 */
public class JDBController {
	String dbName;

	public JDBController(String dbName){
		this.dbName=dbName;
	}

	public boolean isDuplicate(String name) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		boolean result=false;
		ResultSet rset = connObject.SelectFromTable("Bidders", "bidder_name", "bidder_name='"+name+"'");
		if (rset.next())
			result=true;
		connObject.CloseConnection();
		return(result);
	}

	public boolean insertBidder(String name, int auctioneer) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		boolean result=false;
		result = connObject.InsertToTable("Bidders", "bidder_name,registered_to", "'"+name+"','"+auctioneer+"'");
		connObject.CloseConnection();
		return(result);
	}

	public ArrayList<String> itemsOfBidder(String name) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		ArrayList<String> resultList = new ArrayList<String>();
		ResultSet rset=null;
		rset = connObject.SelectFromTable("Items", "item_description", "item_owner='"+name+"'");
		while(rset.next()){
			resultList.add(rset.getString(1));
		}
		connObject.CloseConnection();
		return(resultList);
	}

	public ArrayList<Item> itemsList() throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		ArrayList<Item> resultList = new ArrayList<Item>();
		ResultSet rset=null;
		int item_id;
		String description;
		int startingPrice;
		rset = connObject.SelectFromTable("Items", "*", "item_owner!=' ' ");
		while(rset.next()){
			item_id=rset.getInt(1);
			description=rset.getString(2);
			startingPrice=rset.getInt(3);
			Item item = new Item(startingPrice,description,item_id);
			resultList.add(item);
		}
		connObject.CloseConnection();
		return(resultList);
	}

	public boolean deleteBidder(String name) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		boolean result=false;
		result = connObject.DeleteFromTable("Bidders","bidder_name='"+name+"'");
		connObject.CloseConnection();
		return(result);
	}

	public String getItemDescription(int id) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		ResultSet rset=null;
		String result="";
		rset = connObject.SelectFromTable("Items", "item_description", "item_id='"+id+"'");
		if (rset.next()){
			result = rset.getString(1);
		}
		connObject.CloseConnection();
		return(result);
	}

	public int getCurrentBid(String desc) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		ResultSet rset=null;
		int result=-1;
		rset = connObject.SelectFromTable("Items", "final_price", "item_description='"+desc+"'");
		if (rset.next()){
			result = rset.getInt(1);
		}
		connObject.CloseConnection();
		return(result);
	}

	public String getItemHolder(String desc) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		ResultSet rset=null;
		String result="no_holder";
		rset = connObject.SelectFromTable("Items", "item_owner", "item_description='"+desc+"'");
		if (rset.next()){
			result = rset.getString(1);
		}
		connObject.CloseConnection();
		return(result);
	}
	/*
	 * The following method assumes newBid is bigger than currentBid
	 */
	public boolean updateItemBid(String itemDescription, String bidder, int newBid) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		boolean result=false;
		result = connObject.UpdateTable("Items", "final_price ='"+newBid+"',item_owner ='"+bidder+"'", "item_description='"+itemDescription+"'");
		connObject.CloseConnection();
		return(result);
	}

	public boolean updateItemPrice(String itemDescription,int new_price) throws SQLException{
		JDBConnection connObject = new JDBConnection(dbName);
		boolean result=false;
		int final_price=new_price-1;
		result = connObject.UpdateTable("Items", "starting_price ='"+new_price+"',final_price ='"+final_price+"'", "item_description='"+itemDescription+"'");
		connObject.CloseConnection();
		return(result);
	}

}