Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.*;
- public class Appartments {
- private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
- private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/mydb";
- private static final String DB_USER = "root";
- private static final String DB_PASSWORD = "";
- private static final String CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS Appartments (" +
- "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
- "region VARCHAR(100) NOT NULL, " +
- "address VARCHAR(100) NOT NULL, " +
- "space INT(10) NOT NULL, " +
- "rooms INT(10) NOT NULL, " +
- "price INT(10) NOT NULL)";
- public static void main(String[] args) {
- Connection conn = getDBConnection();
- if (conn == null) {
- System.out.println("Error creating connection!");
- return;
- }
- try {
- Statement st = conn.createStatement();
- st.execute(CREATE_TABLE_SQL);
- //fillDb(st, conn);
- selectDb(st, conn, "*", "appartments", "price", "2000", "4000");
- selectDb(st, conn, "address", "appartments", "rooms='2'");
- selectDb(st, conn, "id", "appartments");
- sortDb(st, conn, "price");
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- private static void sortDb(Statement st, Connection conn, String sort) throws SQLException {
- st = conn.createStatement();
- try {
- ResultSet rs = st.executeQuery("SELECT * FROM appartments ORDER BY "+sort+";");
- ResultSetMetaData md = rs.getMetaData();
- for (int i = 1; i <= md.getColumnCount(); i++)
- System.out.print(md.getColumnName(i) + "\t\t");
- System.out.println();
- while (rs.next()) {
- for (int i = 1; i <= md.getColumnCount(); i++)
- System.out.print(rs.getString(i) + "\t\t");
- System.out.println();
- }
- } finally {
- if (st != null) st.close();
- }
- }
- private static void selectDb(Statement st, Connection conn, String... array) throws SQLException {
- String arr[] = array;
- st = conn.createStatement();
- try {
- ResultSet rs;
- if(arr.length == 5)
- rs = st.executeQuery("SELECT "+arr[0]+" FROM "+arr[1]+" WHERE " +arr[2]+
- " BETWEEN "+Integer.parseInt(arr[3])+" AND "+Integer.parseInt(arr[4])+";");
- else if(arr.length == 3)
- rs = st.executeQuery("SELECT "+arr[0]+" FROM "+arr[1]+" WHERE " +arr[2]+";");
- else
- rs = st.executeQuery("SELECT "+arr[0]+" FROM "+arr[1]+";");
- ResultSetMetaData md = rs.getMetaData();
- for (int i = 1; i <= md.getColumnCount(); i++)
- System.out.print(md.getColumnName(i) + "\t\t");
- System.out.println();
- while (rs.next()) {
- for (int i = 1; i <= md.getColumnCount(); i++)
- System.out.print(rs.getString(i) + "\t\t");
- System.out.println();
- }
- } finally {
- if (st != null) st.close();
- }
- }
- private static void fillDb(Statement st, Connection conn) throws SQLException {
- try {
- st = conn.createStatement();
- st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
- "VALUES ('Darnitsa', 'Grirorenko 15/7', '55', '2', '4000');");
- st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
- "VALUES ('Darnitsa', 'Ahmatova 156/88', '27', '1', '2000');");
- st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
- "VALUES ('Darnitsa', 'Ahmatova 1a/77', '28', '1', '3000');");
- st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
- "VALUES ('Darnitsa', 'Bazana 13/3', '40', '2', '5000');");
- st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
- "VALUES ('Darnitsa', 'Bazana 15/99', '60', '3', '55000');");
- st.executeUpdate("INSERT INTO Appartments (region, address, space, rooms, price) " +
- "VALUES ('Darnitsa', 'Bazana 21/123', '67', '3', '5300');");
- } catch (SQLException ex) {
- ex.printStackTrace();
- } finally {
- if (st != null) st.close();
- }
- }
- 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;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement