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.
And here is its source code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<!DOCTYPE html> <html> <head> <title>Enter your email</title> </head> <body> <form action="http://localhost:8080/EmailSubscriptionService/AddEmailToList" method="post"> <input name="name" type="text" value="Name"><br> <input name="email" value="Email"><br> <input type="hidden" name="list_id" value="1"> <button>Send</button> </form> </body> </html> |
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:
|
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 |
package emailSubscriptionPackage; import java.io.IOException; import java.io.PrintWriter; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; /** * Servlet implementation class AddEmailToList */ @WebServlet("/AddEmailToList") public class AddEmailToList extends HttpServlet { private static final long serialVersionUID = 1L; /** * Default constructor. */ public AddEmailToList() { // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub System.out.println("inside doPost method"); String name = request.getParameter("name"); String email = request.getParameter("email"); int list_id = Integer.parseInt(request.getParameter("list_id")); System.out.printf("trying to add person with name: %s email: %s to list with list_id: %s\n",name,email,list_id); response.setContentType("text/html"); PrintWriter out = response.getWriter(); if(name.length() > 0 && email.length() > 0){ MySQLConnection.connect(); savePerson(name,email,list_id,out); MySQLConnection.closeConnection(); }else{ out.println("Name and/or email missing."); } } public static void savePerson(String name,String email,int list_id, PrintWriter out){ if(!MySQLConnection.listExistsWithId(list_id)){ out.println("The list you are trying to subscribe to doesn't exist"); return; } int person_id = MySQLConnection.findPersonWithEmail(email); if(person_id > 0){ //person exists in table System.out.println("person exists"); addExistingPersonToList(person_id,list_id,out); }else{ int new_person_id = MySQLConnection.insertPesronIntoPeopleTable(name, email); if(new_person_id > 0){ addExistingPersonToList(new_person_id,list_id,out); }else{ out.println("Something went wrong"); } } } public static void addExistingPersonToList(int person_id,int list_id, PrintWriter out){ boolean personIsAlreadySubscribedToList = MySQLConnection.personIsSubscribedToList(person_id, list_id); if(personIsAlreadySubscribedToList){ out.println("You are already subscribed to this list"); }else{ if(MySQLConnection.subscribePersonToList(person_id, list_id)){ out.println("You have successfully subscribed to this list"); }else{ out.println("An error occured while subscribing to the list"); } } } } |
And this is myMySQLConnection.java file:
|
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 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
package emailSubscriptionPackage; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; import java.util.*; import java.sql.*; public class MySQLConnection { public static Connection con; public static void connect(){ try{ Class.forName("com.mysql.jdbc.Driver"); System.out.println("Driver loading success!"); String url = "jdbc:mysql://localhost/email_subscription_bd?autoReconnect=false&useSSL=false"; String name = "root"; String password = "123"; try{ con = DriverManager.getConnection(url,name,password); System.out.println("Connected!"); }catch(SQLException e){ e.printStackTrace(); } }catch(ClassNotFoundException e){ e.printStackTrace(); } } public static void closeConnection(){ try{ con.close(); System.out.println("Disconnected"); }catch(SQLException e){ e.printStackTrace(); } } public static boolean listExistsWithId(int list_id){ try{ Statement st = con.createStatement(); String sqlCommand = String.format("select list_id from lists_table where list_id=%s",list_id); ResultSet rs = st.executeQuery(sqlCommand); if(rs.next()){ return true; } }catch(SQLException e){ e.printStackTrace(); } return false; } public static int findPersonWithEmail(String email){ System.out.println("finding person with email"); try{ Statement st = con.createStatement(); String sqlCommand = String.format("select person_id from people_table where person_email='%s';", email); ResultSet rs = st.executeQuery(sqlCommand); if(rs.next()){ int id = rs.getInt(1); return id; }else{ return 0; } }catch(SQLException e){ e.printStackTrace(); return 0; } } public static boolean personIsSubscribedToList(int person_id,int list_id){ System.out.println("inside personIsSubscribedToList method"); try{ Statement st = con.createStatement(); String sqlCommand = String.format("select person_id,list_id from subscriptions_table where person_id='%s' and list_id=%s;", person_id,list_id); ResultSet rs = st.executeQuery(sqlCommand); if(rs.next()){ System.out.println("person is subscribed to list"); return true; } }catch(SQLException e){ e.printStackTrace(); } return false; } public static boolean subscribePersonToList(int person_id,int list_id){ System.out.println("inside subscribePersonToList method"); try{ Statement st = con.createStatement(); String sqlCommand = String.format("INSERT INTO subscriptions_table (person_id, list_id) VALUES ('%s', '%s');", person_id,list_id); st.execute(sqlCommand); return true; }catch(SQLException e){ e.printStackTrace(); } return false; } public static int insertPesronIntoPeopleTable(String name, String email){ System.out.printf("saving person: %s with email: %s\n",name,email); int person_id = 0; try{ Statement savePersonStatement = con.createStatement(); String sqlCommand = String.format("INSERT INTO people_table (person_email, person_name) VALUES ('%s', '%s');", email,name); savePersonStatement.executeUpdate(sqlCommand,Statement.RETURN_GENERATED_KEYS); System.out.println("Person was added"); ResultSet rs = savePersonStatement.getGeneratedKeys(); if (rs.next()){ person_id = rs.getInt(1); } return person_id; }catch(SQLException e){ e.printStackTrace(); System.err.println("SQLState: " + ((SQLException)e).getSQLState()); System.err.println("Error Code: " + ((SQLException)e).getErrorCode()); System.err.println("Message: " + e.getMessage()); } return person_id; } } |
This is my lists_table with just one list in it:
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.
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:
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.
