Приложение б
Исходный код класса, реализующего доступ к БД
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
public class ConnectDatabase {
public static Connection connect(){
Connection conn = null;
String dbConnect = "jdbc:oracle:thin:@localhost:15210:XE";
String dbUsername = "COM";
String dbPassword = "111";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(dbConnect, dbUsername, dbPassword);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return conn;
}
public static Object[][] getDataFromTable(String query) {
int rows = 0;
int columns = 0;
Object dataFromTable[][] = null;
query = getQuery(query);
Connection conn = connect();
try {
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet result = statement.executeQuery(query);
columns = result.getMetaData().getColumnCount();
while (result.next()) {
rows++;
}
result.beforeFirst();
dataFromTable = new Object[rows][columns];
for (int i = 0; result.next(); i++) {
for (int j = 0; j < columns; j++) {
dataFromTable[i][j] = result.getObject(j + 1);
}
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return dataFromTable;
}
public static Vector getListForCBox(String query){
Vector list = new Vector();
query = getQuery(query);
Connection conn = connect();
try {
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet result = statement.executeQuery(query);
while (result.next()){
list.add(result.getString(1));
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public static Vector getListForCBox(String query, String clause){
Vector list = new Vector();
query = getQuery(query) + clause + "'";
Connection conn = connect();
try {
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet result = statement.executeQuery(query);
while (result.next()){
list.add(result.getString(1));
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public static void insertRowFromDB(String query){
query = getQuery(query);
Connection conn = connect();
try {
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
statement.executeQuery(query);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void updateRowFromDB(String query){
insertRowFromDB(query);
}
public static void deleteRowFromDB(String query, Object id){
query = getQuery(query) + id;
Connection conn = connect();
try {
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
statement.executeQuery(query);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static int getID(String query){
int id = 0;
query = getQuery(query);
Connection conn = connect();
try {
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet result = statement.executeQuery(query);
if (result.next()){
id = result.getInt(1);
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return id;
}
public static float getPrice(String query){
float price = 0;
Connection conn = connect();
try {
Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet result = statement.executeQuery(query);
if (result.next()){
price = result.getFloat(1);
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return price;
}
private static String getQuery(String query) {
if (query.equals("get_data_orders")) {
query = "SELECT orders.order_id, orders.o_date, client.c_name, "
+ "orders.o_price, agent.a_surname, orders.o_share_agent, "
+ "broadcast.b_title, advertising.adv_title "
+ "FROM advertising "
+ "INNER JOIN client ON advertising.client_id = client.client_id "
+ "INNER JOIN orders ON advertising.advertising_id = orders.advertising_id "
+ "INNER JOIN broadcast ON orders.broadcast_id = broadcast.broadcast_id "
+ "INNER JOIN agent ON orders.agent_id = agent.agent_id "
+ "ORDER BY orders.order_id";
} else if (query.equals("get_data_broadcast")) {
query = "SELECT broadcast_id, b_title, b_rating, b_price_minute "
+ "FROM broadcast ORDER BY broadcast_id";
} else if (query.equals("get_data_advertising")) {
query = "SELECT advertising_id, adv_title, adv_length, c_name "
+ "FROM advertising JOIN client ON advertising.client_id=client.client_id "
+ "ORDER BY advertising_id";
} else if (query.equals("get_data_agent")) {
query = "SELECT agent_id, a_firstname, a_surname, a_address, a_phone "
+ "FROM agent ORDER BY agent_id";
} else if (query.equals("get_data_client")) {
query = "SELECT client_id, c_name, c_requisites, c_phone, c_contact_name "
+ "FROM client ORDER BY client_id";
}
else if (query.equals("get_list_client")) {
query = "SELECT c_name FROM client";
}
else if (query.equals("get_list_agent")) {
query = "SELECT a_surname FROM agent";
}
else if (query.equals("get_list_broadcast")) {
query = "SELECT b_title FROM broadcast";
}
else if (query.equals("get_list_advertising")) {
query = "SELECT adv_title FROM advertising";
}
else if (query.equals("update_list_advertising")) {
query = "SELECT adv_title FROM advertising JOIN client "
+ "ON advertising.client_id=client.client_id WHERE client.c_name='";
}
else if (query.equals("delete_row_orders")) {
query = "DELETE FROM orders WHERE order_id=";
}
else if (query.equals("delete_row_broadcast")) {
query = "DELETE FROM broadcast WHERE broadcast_id=";
}
else if (query.equals("delete_row_advertising")) {
query = "DELETE FROM advertising WHERE advertising_id=";
}
else if (query.equals("delete_row_agent")) {
query = "DELETE FROM agent WHERE agent_id=";
}
else if (query.equals("delete_row_client")) {
query = "DELETE FROM client WHERE client_id=";
}
else if (query.equals("get_max_orders")) {
query = "SELECT MAX(order_id) FROM orders";
}
else if (query.equals("get_max_broadcast")) {
query = "SELECT MAX(broadcast_id) FROM broadcast";
}
else if (query.equals("get_max_advertising")) {
query = "SELECT MAX(advertising_id) FROM advertising";
}
else if (query.equals("get_max_agent")) {
query = "SELECT MAX(agent_id) FROM agent";
}
else if (query.equals("get_max_client")) {
query = "SELECT MAX(client_id) FROM client";
}
return query;
}
}