· 9 years ago · Jun 15, 2017, 02:20 PM
1package EmpDatabase;
2
3import Employee.InternalEmp;
4
5import java.sql.*;
6
7import static com.sun.xml.internal.ws.spi.db.BindingContextFactory.LOGGER;
8
9/**
10 * Created by Daniel on 14.05.2017.
11 */
12public class JdbcStorageController {
13
14
15
16 // PersonalIntern Table Constants
17 private static final String TABLE_NAME_PERSONALINTERN = "PersonalIntern";
18 private static final String TABLE_PERSONALINTERN_COLUMN_EMPID = "empID";
19 private static final String TABLE_PERSONALINTERN_COLUMN_FIRSTNAME = "firstName";
20 private static final String TABLE_PERSONALINTERN_COLUMN_LASTNAME = "lastName";
21 private static final String TABLE_PERSONALINTERN_COLUMN_EMAIL = "eMail";
22 private static final String TABLE_PERSONALINTERN_COLUMN_LOGINNAME = "loginName";
23 private static final String TABLE_PERSONALINTERN_COLUMN_PASSWORD = "password";
24 private static final String TABLE_PERSONALINTERN_COLUMN_ACCESSLEVEL = "accessLevel";
25 private static final String TABLE_PERSONALINTERN_COLUMN_ONSTATUS = "OnStatus";
26
27 private static final String CREATE_PERSONALINTERN_TABLE =
28 "CREATE TABLE " + TABLE_NAME_PERSONALINTERN + " (" +
29 TABLE_PERSONALINTERN_COLUMN_EMPID + " int primary key auto_increment," +
30 TABLE_PERSONALINTERN_COLUMN_FIRSTNAME + " varchar(30)," +
31 TABLE_PERSONALINTERN_COLUMN_LASTNAME + " varchar(50)," +
32 TABLE_PERSONALINTERN_COLUMN_EMAIL + " varchar(50)," +
33 TABLE_PERSONALINTERN_COLUMN_LOGINNAME + " varchar(80)," +
34 TABLE_PERSONALINTERN_COLUMN_PASSWORD + " varchar(20)," +
35 TABLE_PERSONALINTERN_COLUMN_ACCESSLEVEL + " int," +
36 TABLE_PERSONALINTERN_COLUMN_ONSTATUS + " int" +
37 ")";
38
39 private static final String INSERT_PERSONALINTERN =
40 "INSERT INTO " + TABLE_NAME_PERSONALINTERN + " (" +
41 TABLE_PERSONALINTERN_COLUMN_FIRSTNAME + ", " +
42 TABLE_PERSONALINTERN_COLUMN_LASTNAME + ", " +
43 TABLE_PERSONALINTERN_COLUMN_EMAIL + ", " +
44 TABLE_PERSONALINTERN_COLUMN_LOGINNAME + ", " +
45 TABLE_PERSONALINTERN_COLUMN_PASSWORD + ", " +
46 TABLE_PERSONALINTERN_COLUMN_ACCESSLEVEL + ", " +
47 TABLE_PERSONALINTERN_COLUMN_ONSTATUS + " ) VALUES ( ?, ?, ?, ?, ?, ?, ? );";
48
49 private static final String SELECT_PERSONALINTERN_BY_LOGINNAME =
50 "SELECT * FROM " + TABLE_NAME_PERSONALINTERN +
51 "WHERE " + TABLE_PERSONALINTERN_COLUMN_LOGINNAME + " = ?";
52
53 // Database connection
54 private Connection connection;
55
56 private PreparedStatement insertPersonalInternStatement;
57 private PreparedStatement loadPersonalInternStatement;
58
59 protected JdbcStorageController()
60 {
61 try
62 {
63 this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/accountmanager", "root", "");
64
65 //create table if necessary
66 this.createTableIfItDoesNotExist( TABLE_NAME_PERSONALINTERN );
67 this.insertPersonalInternStatement = this.connection.prepareStatement( INSERT_PERSONALINTERN, Statement.RETURN_GENERATED_KEYS );
68 this.loadPersonalInternStatement = this.connection.prepareStatement( SELECT_PERSONALINTERN_BY_LOGINNAME );
69
70
71 }
72 catch ( SQLException e )
73 {
74 e.printStackTrace();
75 throw new InternalError( e.getMessage() );
76 }
77 }
78
79 public void createTableIfItDoesNotExist( String tableName ) throws SQLException
80 {
81 String createStatement = "";
82 switch ( tableName )
83 {
84 case TABLE_NAME_PERSONALINTERN: createStatement = CREATE_PERSONALINTERN_TABLE; break;
85 //next table case TABLE_NAME_PERSONALINTERN: createStatement = CREATE_PERSONALINTERN_TABLE; break;
86 }
87 DatabaseMetaData dbm = this.connection.getMetaData();
88
89 ResultSet tables = dbm.getTables(null, null, tableName, null);
90 if (tables.next())
91 {
92 LOGGER.info( "Table [" + tableName + "] exists." );
93 }
94 else
95 {
96 LOGGER.info( "Table [" + tableName + "] does not exist - creating it." );
97 LOGGER.info( "Using statement [" + createStatement + "]");
98
99 Statement statement = this.connection.createStatement();
100 statement.execute( createStatement );
101 }
102 }
103
104 public int saveEmployee( InternalEmp _emp ) throws SQLException
105 {
106 this.insertPersonalInternStatement.setString( 1, _emp.getFirstName());
107 this.insertPersonalInternStatement.setString( 2, _emp.getLastName());
108 this.insertPersonalInternStatement.setString( 3, _emp.geteMail());
109 this.insertPersonalInternStatement.setString( 4, _emp.getLoginName());
110 this.insertPersonalInternStatement.setString( 5, _emp.getPassword());
111 this.insertPersonalInternStatement.setInt( 6, _emp.getAccessLevel());
112 this.insertPersonalInternStatement.setBoolean( 7, _emp.getOnStatus());
113
114 this.insertPersonalInternStatement.executeUpdate();
115
116 ResultSet generatedKeys = this.insertPersonalInternStatement.getGeneratedKeys();
117 generatedKeys.next();
118 int empId = generatedKeys.getInt( 1 );
119
120 _emp.setEmpID( empId );
121
122 LOGGER.info( "Saved employee [" + _emp + "] with id [" + empId + "]" );
123
124 return empId;
125 }
126
127 public InternalEmp loadEmployee( String _loginName ) throws SQLException {
128 InternalEmp employee = null;
129
130 this.loadPersonalInternStatement.setString( 1, _loginName);
131
132 ResultSet resultSet = this.loadPersonalInternStatement.executeQuery();
133
134 if ( resultSet.next() )
135 {
136 employee = new InternalEmp(
137 resultSet.getString( TABLE_PERSONALINTERN_COLUMN_FIRSTNAME ),
138 resultSet.getString( TABLE_PERSONALINTERN_COLUMN_LASTNAME ),
139 resultSet.getString( TABLE_PERSONALINTERN_COLUMN_EMAIL ));
140
141 employee.setEmpID( resultSet.getInt( TABLE_PERSONALINTERN_COLUMN_EMPID ) );
142 employee.setPassword( resultSet.getString( TABLE_PERSONALINTERN_COLUMN_PASSWORD ) );
143 employee.setAccessLevel( resultSet.getInt( TABLE_PERSONALINTERN_COLUMN_ACCESSLEVEL ));
144 employee.setOnStatus( resultSet.getBoolean( TABLE_PERSONALINTERN_COLUMN_ONSTATUS ));
145
146 LOGGER.info("Loaded Employee from Database [" + employee + "]" );
147
148 }
149 return employee;
150 }
151
152 public static boolean login(InternalEmp _emp, String _password) {
153 if (_emp.getPassword() == _password)
154 {
155 return true;
156 }
157 else
158 {
159 return false;
160 }
161 }
162 public static void main(String[] args) throws SQLException {
163 InternalEmp Karl = new InternalEmp("Karl", "Nett", "dd@dd.de");
164 Karl.setAccessLevel(2);
165 Karl.setPassword("Blackjack");
166 Karl.setSalary(2000);
167 Karl.setOnStatus(true);
168 JdbcStorageController Controller = new JdbcStorageController();
169 System.out.println(Karl.getLoginName());
170
171 Controller.createTableIfItDoesNotExist("PersonalIntern");
172 try {
173
174 Controller.saveEmployee(Karl);
175 }
176
177 catch (SQLException e)
178 {
179 e.printStackTrace();
180 throw new InternalError( e.getMessage() );
181 }
182 Controller.loadEmployee("NettKarl");
183
184 }
185
186}