Skip to content

Instantly share code, notes, and snippets.

@OlgaKulikova

OlgaKulikova/Flat.java

Last active August 29, 2015 14:11
Show Gist options
  • Save OlgaKulikova/0101ba33aa8f326680a8 to your computer and use it in GitHub Desktop.
Save OlgaKulikova/0101ba33aa8f326680a8 to your computer and use it in GitHub Desktop.
Download ZIP
FlatJDBC
Raw
Flat.java
public class Flat {
private String district;
private String address;
private double area;
private int rooms;
private int price;
public Flat(String district, String address, double area, int rooms, int price) {
this.district = district;
this.address = address;
this.area = area;
this.rooms = rooms;
this.price = price;
}
public String getDistrict() {
return district;
}
public String getAddress() {
return address;
}
public double getArea() {
return area;
}
public int getRooms() {
return rooms;
}
public int getPrice() {
return price;
}
}
Raw
Main.java
import java.sql.*;
import java.util.ArrayList;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Scanner;
public class Main {
private final static String DB_DRIVER = "com.mysql.jdbc.Driver";
private final static String DB_CONNECTION = "jdbc:mysql://localhost:3306/Real_Estate";
private final static String DB_USER = "****"; // Вставить имя пользователя
private final static String DB_PASSWORD = "********"; // Вставить пароль
private final static String CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS Flats (" +
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
"district VARCHAR(128) DEFAULT NULL," +
"address VARCHAR(128) DEFAULT NULL," +
"area DOUBLE NOT NULL," +
"rooms INT NOT NULL," +
"price$ INT NOT NULL )";
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
return null;
}
return dbConnection;
}
public static void main(String[] args) {
Connection conn = getDBConnection();
if (conn == null) {
System.out.println("Error creating connection");
return;
}
try {
try {
//Создаем таблицу
Statement st = conn.createStatement();
try {
st.execute(CREATE_TABLE_SQL);
} finally {
if (st != null) {
st.close();
}
}
//Заполняем таблицу
PreparedStatement prSt = conn.prepareStatement("INSERT INTO Flats (district, address, area, rooms, price$)" +
" VALUES (?, ?, ?, ?, ?)");
try {
prSt.setString(1, "Оболонь");
prSt.setString(2, "Героев Сталинграда, 2, кв.3");
prSt.setDouble(3, 65.2);
prSt.setInt(4, 3);
prSt.setInt(5, 79000);
prSt.executeUpdate();
} finally {
if (prSt != null) {
prSt.close();
}
}
//Заполняем таблицу с консоли
Scanner scanner = new Scanner(System.in);
List<Flat> list = new ArrayList<Flat>();
while (true){
try {
System.out.println("Введите район");
String district = scanner.nextLine();
System.out.println("Введите адрес");
String address = scanner.nextLine();
System.out.println("Введите площадь квартиры");
double area = scanner.nextDouble();
System.out.println("Введите количество комнат");
int rooms = scanner.nextInt();
System.out.println("Введите цену в $");
int price = scanner.nextInt();
Flat flat = new Flat(district, address, area, rooms, price);
list.add(flat);
System.out.println("Нажмите Y, чтобы продолжить. Нажмите Enter на пустой строке, чтобы закончить.");
scanner.nextLine();
String s = scanner.nextLine();
if (s.equals("")) {
break;
}
} catch (InputMismatchException e) {
System.out.println("Неверный формат ввода, попробуйте снова.");
scanner.nextLine();
}
}
conn.setAutoCommit(false);
try {
prSt = conn.prepareStatement("INSERT INTO Flats (district, address, area, rooms, price$)" +
" VALUES (?, ?, ?, ?, ?)");
try {
for (Flat f : list) {
prSt.setString(1, f.getDistrict());
prSt.setString(2, f.getAddress());
prSt.setDouble(3, f.getArea());
prSt.setInt(4, f.getRooms());
prSt.setInt(5, f.getPrice());
prSt.executeUpdate();
}
} finally {
conn.commit();
if (prSt != null) {
prSt.close();
}
}
} catch (Exception e) {
conn.rollback();
}
conn.setAutoCommit(true);
//Выборка со всеми параметрами
prSt = conn.prepareStatement("SELECT * FROM Flats");
try {
ResultSet rs = prSt.executeQuery();
try {
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); i++) {
System.out.print(md.getColumnName(i) + "\t\t\t");
}
System.out.println();
while (rs.next()) {
for (int i = 1; i <= md.getColumnCount(); i++) {
System.out.print(rs.getString(i) + "\t\t\t");
}
System.out.println();
}
} finally {
rs.close();
}
} finally {
if (prSt != null) {
prSt.close();
}
}
//Выборка по отдельным параметрам
prSt = conn.prepareStatement("SELECT area, price$ FROM Flats WHERE district = 'Оболонь' ORDER BY price$");
try {
ResultSet rs = prSt.executeQuery();
try {
ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); i++) {
System.out.print(md.getColumnName(i) + "\t\t\t");
}
System.out.println();
while (rs.next()) {
for (int i = 1; i <= md.getColumnCount(); i++) {
System.out.print(rs.getString(i) + "\t\t\t");
}
System.out.println();
}
} finally {
rs.close();
}
} finally {
if (prSt != null) {
prSt.close();
}
}
} finally {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment