· 9 years ago · Dec 12, 2016, 11:48 AM
1package jdbcSklepRowerowy;
2
3import java.sql.*;
4import java.util.ArrayList;
5import java.util.List;
6
7public class jdbcConnection {
8 private String url = "jdbc:mysql://localhost/hipcio";
9 private String pass = "123456";
10 private String user = "root";
11
12 private String sqlCreateDatabase = "CREATE DATABASE IF NOT EXISTS rowerek";
13 private String sqlUseUser = "USE rowerek";
14 private String sqlCreateTable = "CREATE TABLE IF NOT EXISTS user (id MEDIUMINT NOT NULL AUTO_INCREMENT, login VARCHAR(20), password VARCHAR(20), "
15 + "email VARCHAR(25), rights VARCHAR(20), PRIMARY KEY(id))";
16 private String sqlCreateTableOfProducts = "CREATE TABLE IF NOT EXISTS products (id MEDIUMINT NOT NULL AUTO_INCREMENT, productName VARCHAR(25), productQuantity VARCHAR(5),"
17 + " PRIMARY KEY(id))";
18 private String sqlList = "SELECT * FROM products";
19
20 private Statement statement = null;
21 private PreparedStatement prepareListProducts = null;
22 private PreparedStatement prepareLogin = null;
23 private Connection connection;
24
25 public jdbcConnection(){}
26
27 public void conneectToDatabase(){
28 try{
29 connection = DriverManager.getConnection(url, user, pass);
30 statement = connection.createStatement();
31 }catch (Exception e){
32 e.printStackTrace();
33 }
34 }
35
36 public boolean createDatabase(){
37 conneectToDatabase();
38 try{
39 if(statement.execute(sqlCreateDatabase)){
40 return true;
41 }else{
42 return false;
43 }
44 }catch(Exception e){
45 e.printStackTrace();
46 }
47 return true;
48 }
49
50 public boolean createTable(){
51 conneectToDatabase();
52 try{
53 statement.execute(sqlUseUser);//mean database
54 if(statement.execute(sqlCreateTable)){
55 return true;
56 }else{
57 return false;
58 }
59 }catch(Exception e){
60 e.printStackTrace();
61 }
62 return true;
63 }
64
65 public boolean createTableOfProducts(){
66 conneectToDatabase();
67 try{
68 statement.execute(sqlUseUser);//mean database
69 if(statement.execute(sqlCreateTableOfProducts)){
70 return true;
71 }else{
72 return false;
73 }
74 }catch(Exception e){
75 e.printStackTrace();
76 }
77 return true;
78 }
79
80 public boolean addUser(String login, String pass, String email){
81 String sqlCreateUser = "INSERT INTO user (login, password, email, rights) VALUES('"+login+"','"+pass+"','"+email+"', 'user');";
82 try{
83 statement.execute(sqlCreateUser);
84 }catch(Exception e){
85 e.printStackTrace();
86 return false;
87 }
88 return true;
89 }
90
91 public boolean login(String login, String password){
92 String sqlLogin = "SELECT * FROM user WHERE login = '" + login + "'AND password = '" + password + "'";
93 try{
94 prepareLogin = connection.prepareStatement(sqlLogin);
95 ResultSet result = prepareLogin.executeQuery();
96 result.absolute(1);
97 String right = result.getString("rights");
98 if(right.equals("admin")){
99 System.out.println("Hello Admin");
100 Admin admin = new Admin();
101 admin.menu();
102 prepareLogin.close();
103 }else{
104 System.out.println("Hello User");
105 User user = new User();
106 user.menu();
107 prepareLogin.close();
108 }
109 prepareLogin.close();
110 return true;
111 }catch(Exception e){
112 e.printStackTrace();
113 System.out.println("Wrong password or login");
114 return false;
115 }
116 }
117
118 public List<String> listProducts(){
119 List<String> listOfProducts = new ArrayList<>();
120 try{
121 prepareListProducts = connection.prepareStatement(sqlList);
122 ResultSet productResult = prepareListProducts.executeQuery();
123 while(productResult.next()){
124 listOfProducts.add(productResult.getString("productName"));
125 System.out.println(productResult.getString("productName"));
126 }
127 }catch(Exception e){
128 e.printStackTrace();
129 }
130 return listOfProducts;
131 }
132
133 public void statTest(){
134 String slqTest = "SELECT productName FROM products WHERE id=?";
135 try{
136 PreparedStatement preparedStatement = connection.prepareStatement(slqTest);
137 preparedStatement.setLong(1, 1);
138 ResultSet result = preparedStatement.executeQuery();
139 result.next();
140 String txt = null;
141 System.out.println(txt = result.getString(1));
142 }catch(Exception e){
143 e.printStackTrace();
144 }
145 System.out.println("hello programmer!!");
146 }
147}