Skip to content

Instantly share code, notes, and snippets.

@OlgaKulikova

OlgaKulikova/Main.java

Created December 17, 2014 01:23
Show Gist options
  • Save OlgaKulikova/3707942f634d21af9e28 to your computer and use it in GitHub Desktop.
Save OlgaKulikova/3707942f634d21af9e28 to your computer and use it in GitHub Desktop.
Download ZIP
Orders Database
Raw
Main.java
import java.sql.*;
public class Main {
private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/ordersdb";
private static final String DB_USER = "****";
private static final String DB_PASSWORD = "********";
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.executeUpdate("INSERT INTO Clients (f_name, l_name, phone) VALUES ('Pupkin', 'Vasya', '095-123-45-67')");
} finally {
if (st != null) {
st.close();
}
}
// Добавляем товар
conn.setAutoCommit(false);
try {
PreparedStatement ps = conn.prepareStatement("INSERT INTO Products (name, size, colour, price) VALUES (?, ?, ?, ?)");
try {
for (int i = 40; i <= 45; i++) {
ps.setString(1, "Shoes");
ps.setInt(2, i);
ps.setString(3, "black");
ps.setInt(4, 250);
ps.executeUpdate();
}
} finally {
conn.commit();
if (ps != null) {
ps.close();
}
}
} catch (Exception e) {
conn.rollback();
}
conn.setAutoCommit(true);
// Оформляем заказ
st = conn.createStatement();
try {
st.executeUpdate("INSERT INTO Orders VALUES (0, " +
"(SELECT id FROM Clients WHERE phone = '095-123-45-67'), " +
"(SELECT id FROM Products WHERE name = 'shoes' AND size = 42 AND " +
"colour = 'black' AND price = 250), 'New order')");
} finally {
if (st != null) {
st.close();
}
}
//Изменение статуса заказа
st = conn.createStatement();
try {
st.executeUpdate("UPDATE Orders SET status = 'Order closed' WHERE " +
"client_id = (SELECT id FROM Clients WHERE phone = '095-123-45-67') " +
"AND status = 'New order'");
} finally {
if (st != null) {
st.close();
}
}
} finally {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Raw
Result
mysql> select * from Orders;
+----+-----------+------------+--------------+
| id | client_id | product_id | status |
+----+-----------+------------+--------------+
| 4 | 9 | 51 | Order closed |
+----+-----------+------------+--------------+
1 row in set (0.00 sec)
mysql> select * from Products;
+----+-------+------+--------+-------+
| id | name | size | colour | price |
+----+-------+------+--------+-------+
| 49 | Shoes | 40 | black | 250 |
| 50 | Shoes | 41 | black | 250 |
| 51 | Shoes | 42 | black | 250 |
| 52 | Shoes | 43 | black | 250 |
| 53 | Shoes | 44 | black | 250 |
| 54 | Shoes | 45 | black | 250 |
+----+-------+------+--------+-------+
6 rows in set (0.00 sec)
mysql> select * from Clients;
+----+--------+--------+---------------+
| id | f_name | l_name | phone |
+----+--------+--------+---------------+
| 9 | Pupkin | Vasya | 095-123-45-67 |
+----+--------+--------+---------------+
1 row in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment