1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
package YalpAuth.YalpPGSqlAuth;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;
import java.sql.*;
import YalpInterfaces.*;
public class YalpAuthInterfaceImpl {
private String db;
private String dbuser;
private String dbpasswd;
private Statement stat;
private Connection con;
/*
* Constructor establishes connection to Database Server
*
* @param db
* jdbc Connection to database
* @param dbYalpUser
* userName for database
* @param dbPasswd
* password for database
*/
public YalpAuthInterfaceImpl(String db, String dbYalpUser, String dbPasswd) {
try{
this.dbuser = dbYalpUser;
this.dbpasswd = dbPasswd;
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(db,dbuser,dbpasswd);
System.out.println("YalpAuth: PGSQL connection established");
this.stat= con.createStatement();
} catch (SQLException e) {
System.out.println("Exception in DbConnection Constructor: "+e);
} catch (ClassNotFoundException e) {
System.out.println("Exception in DbConnection Constructor: "+e);
}
}
/*
* checks if user exists in yalpYalpUser Database and what rights he has
*
* @param username
* username to check
* @param passwd
* password to check
*
* @return enum privilege level
*/
public int userVerify(String username,String passwd) {
try{
ResultSet result=stat.executeQuery("select * from \"user\" where \"username\" = '"+username+"' and \"passwd\" = '"+passwd+"';");
if (result.next()){
if (result.getBoolean(5)==true){
return 2;
}else return 1;
}else return 0;
}catch (SQLException e){
System.out.println("Exception in DbConnection.userVerify: "+e);
return 0;
}
}
/*
* returns an ArrayList with all yalpYalpUsers and Admins
*
* @return ArrayList<YalpUser>
* list with all YalpYalpUsers and Admins
*/
public void getUser(UsersHolder list, YalpErrorHolder err) {
try {
ArrayList<YalpUser> resultList =new ArrayList<YalpUser>();
YalpUser actUser = new YalpUser();
String query = "select * from \"user\"order by \"id\";";
Statement stat= con.createStatement();
ResultSet result=stat.executeQuery(query);
while(result.next())
{
/* t.b.d. - create YalpUser according to new database design */
// result.getInt(1),result.getString(2),result.getString(3),result.getString(4),result.getBoolean(5)))
resultList.add( actUser );
}
YalpUser[] u = new YalpUser[1];
list = new UsersHolder(resultList.toArray(u));
} catch (SQLException e) {
YalpError error = new YalpError();
error.code = YalpErrorCode.ERROR_SQL;
error.descr = e.toString();
error.level = YalpErrorLevel.ERROR_LEVEL_ERROR;
err = new YalpErrorHolder(error);
}
}
/*
* submits changes to yalpYalpUserDatabase
*
* @param change
* describes the change to commit
* @return int
* -1 if failed
*/
public void changeUser(YalpUser usr, String passwd, Action todo,
YalpError err) {
/* t.b.d. alter to new db design
try{
String sql;
switch (todo.type){
// if updateType is UPDATE
case Action._UPDATE:
sql="update \"user\" set \"username\"='"+usr.name+"', \"passwd\"='"+change.passwd+"', \"realname\"='"+change.realname+"', \"admin\"="+change.admin+" where \"id\"= "+change.id+" ;";
break;
// if updateType is INSERT INTO
case Action._INSERT:
sql="insert into \"user\" values(nextval('userId'), '"+usr+"','"+change.passwd+"','"+change.realname+"',"+change.admin+");";
break;
// if updateType is DELETE
case Action._DELETE:
sql="delete from \"user\" where \"id\"= "+change.id+" ;";
break;
default:
//errorhandling
}
// perform operation on table an return number of updated rows
System.out.println(sql);
return stat.executeUpdate(sql);
}catch(SQLException e){
System.out.println("Exception in DbConnection.changeYalpUser: "+e);
}
*/
}
}
|
