So far, we have created a screen with an input field and 3 buttons for registering, expensesand report. Register shows the registered users in the database. However these users wereregistered using MySQL from the command prompt.
The next step is to try to register a user from the browser instead which is a reasonableexpectation for a user of the web application. Updating the database and dealing with userinput brings up several new issues, such as;1. Every time a new user is requested to be registered, check if the username alreadyexists and they should choose a new username.2. Enter expenses: If, by mistake, an extra or incorrect expense gets added to the expensetable, an extra functionality of being able to edit or delete a previously entered rowmust be designed.
To achieve this, we are going to use a combination of database facilities and make certainchanges to the existing app.
Consider the following simple test program: insertdb.java, where we try to insert records intothe database.
Most of the statements are familiar.● We create a database connection using the database driver and statement has thequery to be executed on the database nptel specified in the url using jdbc standard.● The try block has rv=stmt.executeUpdate(“insert into users2values(0,2,’f1’)”);● Though this is a simple update query, the important difference from earlier scenariosis errors might arise, since we are updating the database ( unlike querying where weonly read from the database). We need to catch exceptions using the standardSQLException object which has a lot of information.● SQLException object has a message, SQL state, error code and a cause field. SQLstate is a SQL language standard.
● In the MySQL web page, we can find mapping of MySQL error numbers to SQL statecode.● We will encounter 42S02 in the webapp created till now, whichER_BAD_TABLE_ERROR.● We print out the message and close the database.This is just a test program. Compile the insertdb.java file using javac insertdb.javaand execute using java insertdb. We get this error message:
The error message: Table ‘nptel.users2’ doesn’t exist.This is because we have not created it yet. This reason to run it before creating was todemonstrate the description of the error.
The highlighted text has a message field of SQLException object. The state is a standardfrom SQL, value is 42S02 as predicted. MariaDB’s MySQL also has status codes that explainthe type of error, in this case its value is 1146.
To fix the error, create a table users2.
Now run insertdb using java insertdb, it outputs rv=1. This is the number of rows in
users2. This means insertion query in the insertdb.java was successful.
The left command prompt executes insertdb after table users2 is created, the updated table isdisplayed on the right command prompt as output for the query select * from users2.
To ensure that one cannot enter the same username is using unique constraint, just likeprimary keys are required to be unique, we can also ensure that the user name is unique.
Now if we attempt to insert the same row, there is an exception which is that the duplicatekey f1 is entered for uname and this time the status code changes to 23,000 and the mariadbcode is 1062. 23000 is the standard error code for ER_DUP_ENTRY.
The usual way to fix errors is to look up the status code (here, ER_DUP_ENTRY whichmeans that a duplicate entry has been made).
TIP:When we use websites, almost all of them require users to register and identify themselvessince name, city, etc., is not necessarily unique or it can also be the case that somebody mighteasily impersonate you knowing your details. So instead, we create a new identity or you usean identity which is already known to be unique such as Yahoo email address where somethird party like Yahoo or Google have taken care that the email ids are unique to finally reacha stable notion of user identity for your app.
In the previous code, we created a table without a unique constraint and then added it byaltering it. The alternative is to use this unique declaration right in the schema of the table asshown below.
So for example if you want to see how this uniqueness is achieved use show indexes fromusers2;
Register page of the website needs to have the functionality of displaying errors. We insteadcreate a new screen with a different url for displaying queries which lead to an error.
The right place to return an error like this is a matter of UI design.
In the register page itself, we have the input field which corresponds to the input field shownhere, but in the register error page we add another row(highlighted in the above figure)besides the input field.
A designer might prefer the error message to be centered or aligned to the left hand side ofthe input field. All these effects can be achieved, but it is ugly to achieve just with tables.CSS is better but it needs a lot more knowledge of CSS than what we have learnt.
The error page with an extra style called outline:1px solid blue. This is done to show
how the first row has a table with 2 rows, so there is a table inside a table.
As we know the primary part of the structure is a row of buttons, a row with the input field,but then this input field in turn actually consists of a table which itself contains the input fieldand the second row has the error message text. Doing this and sizing them can be tedious.
CSS does make it a lot easier to create applications which work for mobiles, tablets as well asnormal websites.
User interface gets affected based on the input to the input field. The user interface becomesmore dynamic as the app has to detect the presence or absence of errors and output differenthtml files.We want to see the following output when an already created user abc2 is registered again:
We are going to add two things to the FairShareDb.java file:
● The update part is done in a function called registerUser(req,out) whichtakes the request and the output. This output is the PrintWriter which we get from theresponse object.● Get all the registered users in the query part and this is included in the template as wehave seen before.
The unix timestamp gets evaluated on the server side, this is a SQL function:unix_timestamp(). howmany field stores the number of affected rows from the previouslyexecuted register query.
When there is an exception, SQL state is checked for duplicate entry usingsqlState.equals(SQLSTATEDUP). We are going to set the attribute in the request thatsays “yes”, there is a duplicate user and we are also going to send the username that isattempted to be duplicated, so that the error message can contain what user exactly wasduplicated.
If the SQL state code is something else, we are simply going to dump the SQL exception andthen close the statement. We are going to take action with duplicate entry error types.
Execute the query and result is a list of maps, set the user data attribute and go to theregister.jsp function. One novelty in here is that this template does 2 different things based onwhether there was a duplicate user or not. As we saw, the goal is to add a row containing theerror message only if there is a duplicate user and if there is such a user, then you shouldmention what this is.
You might have noticed that the error was printed in red colored text using CSS class:tdError.
tdError class: Color should be red, font style is italic, font size is larger i.e. just larger thandefault font, align to the center. The above two CSS classes were used to get the table layoutfrom last time.
Validation starts becoming very important when updations are made. To demonstrate, clickregister user with no input entered in the input field. Since username !=null is present in thecode, if there is no username, there is a new row in the table with a blank name though theexpected behavior as an end user of the website might be to not add any row to the table.
There is much more to user validation than the simple example. To demonstrate this, we firstmake a change to reject the empty string if the username is empty.
We need to ensure nothing fails when the table is empty, but our website displays an error.
The testing of a database should be automated but it is a characteristic feature of modern webdevelopment where most of them are tested in some version.
In our case, we could use embedded tomcat but there are many ways to automate. Oncechanging, entering and checking the details starts becoming the feature of your application,life does get considerably harder. Even adding a single quote(‘) to the input throws anexception which our app cannot handle, it just dumps the stack trace. So just a bad input is
hard to handle.
We use the concept of prepared statements to achieve better testing. Under certaincircumstances they can be more efficient as well, especially when used in loops. It specifiesthe database which part of the statement is specified by developer and which part is comingas input from the users, so that the database will treat it not as something that whose syntaxthe database has to understand, it will clean the isolate user input and then store it separatelywithout letting it affect the rest of the statement.
In our example, nothing is irretrievably broken, only the request gets affected . So we canreload the page to erase the previous execution.
Instead of creating just a statement, we are going to create a prepared statement where at thesame place the query is also supplied. The prepared statement will contact the database,compile the prepared statement, this is another reason because of pre-compilation thatprepared statements can be more efficient and in the prepared statement we are going to leavea hole, a parameter where the user value will be plugged in. The usual way to do that is tohave a ? to say that this is where we are going to plug in a value. The rest remains the same asbefore.
So you get the userName as before, complete your validation, and then say upd.setString(1, username );and because the query is already known all you have to dois execute the update as shown above.The idea is you do not specify a bad input looks like, you only specify what a good input is,So even if the database, the standard SQL might not approve when written down in a stringsyntax,
You can use the same idea for example for expenses. At this point we will actually havenearly a full database app in which the only thing that is different is that different userscannot access the system at the same time just yet.
So at this point we you have a reasonably complete app.
We however do not have delete operations such as removing a user but it is no different fromwhat we have done so far. Just as we did register, create another button called unregisterwhich makes the system delete the row from the database with the provided user name.
Once we add login and cookies, our app will essentially be complete and you will have seenall the pieces including various debugging scenarios that show up when a web app has to bedeveloped.
Log in to save your progress and obtain a certificate in Alison’s free MySql, Java and URL in Web Application online course
Sign up to save your progress and obtain a certificate in Alison’s free MySql, Java and URL in Web Application online course
Please enter you email address and we will mail you a link to reset your password.