Creating an email subscription web service (Part 2)

In the previous post of this series I decided what tables I’m going to have in my database. I created those tables (people_table, lists_table, subscriptions_table) using Workbench. Then I had to spend one evening trying to fix an issue with Russian language in MySQL. I just couldn’t save entries to the database, if those entries had Russian symbols in them. I was trying to find a solution that would fix this issue once and for all, like maybe changing some setting in MySQL or in Workbench. But I couldn’t.

So, for now the only way for me to use Russian symbols in my database is to set the collation of each table at the time of its creation to utf8-default. If the table already exists and it has wrong collation, I don’t know how to change it. If you know how to set text encoding globally in MySQL, please, tell me in the comments section below.

I have read something about JavaMail and about setting up a mailing server in general. It all seemed complicated, so I’m not sure if I’ll ever complete this project. I will definitely read some more on this topic, though. For now, I decided to do without the mailing part of an app. What I want to do now, is create a web form with two fields – name and email – and a server side which will get the information from a web form and save it to the database.

So, I created the form. This is what it looks like.

webform

And here is its source code.

Notice the third input – list_id. It has type “hidden”  so it isn’t visible on the page, but the value of the list_id parameter will be sent to the server. This is how the server will know which list the user is subscribing to. The value of this parameter is set manually for now. 

I also created a Web Project in Eclipse called EmailSubscriptionService. To use JDBC in it I downloaded Connector/J from https://dev.mysql.com/downloads/connector/j/

I needed to put the mysql-connector-java-5.1.39-bin.jar file from the archive somewhere into my project. I didn’t know what the right place for it was, so I just put it into WebContent/WEB-INF/lib folder. I’m almost sure (after googling a little) that it is not the best place for this file. But it works, and that’s what is most important for me right now.

I created two files: AddEmailToList.java and MySQLConnection.java. Then I spent two evenings coding. Finally, I have all the main functionality. I can add people to the list from the web form.

This is my AddEmailToList.java file:

 

And this is myMySQLConnection.java file:

 

This is my lists_table with just one list in it:

lists_table

After I get the request from the web form I first check if the list with a specified id exists. Just in case.

Then I add a person to the people_table, if that person is not already in the table, which can be checked by the email: each email is unique in the people_table.

people_table

After that I get the person_id from the table and add it to the subscriptions_table along with the list_id, which is 1 all the time (it was hardcoded into the web form, as I showed above). This is my subscriptions_table:

subscriptions_table

What’s next

Since I am more interested in learning databases (and a little bit of web development) than in learning the mailing stuff, I will postpone the learning of JavaMail until better times. Instead, I will create an admin panel for my app. It will allow me to see my lists of subscribers from the browser.

To be continued.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *