Tag Archives: servlets

MySQL, YourSQL, Everybody’s SQL

I hope you all got to read my post last week about servlets.  This week’s post is going to cover the next topic in the natural progression of web services topics, and that of course means databases. As usual, let me give you all a little background information about databases in general.

What is a database? From our perspective as a developer, a database is a big grouping of tables that can hold information. The columns in a table are the fields that are to be kept track of in the table, and the rows are the different entries that have been made to the database. For example, we might have a database called m5 and a table called staff, with columns for first name, last name, email, and so on.

Why do we care? Databases are convieniant ways of storing information that your application can use and serve to a user. On top of this, connecting to a database such as a MySQL database has become increasingly simplified, especially using languages such as Java.

To start, we need to install MySQL on our local machine.  This can be obtained on the MySQL website:

http://dev.mysql.com/downloads/mysql/.

Run through that installer, and make sure to take note of the root username and password.

Once installed, we can use MySQL via the command prompt to create a database and some tables. To do this, enter the windows command prompt and type mysql -u ROOTUSERNAME -p and hit enter. If it prompts you for a password, you installed mysql correctly.   If it doesnt, try typing mysql –version.  If this doesn’t return anything you probably need to add the mysql directory to your path enviroment variable (use Google to figure this out).  Once inside, you should see something like this:Once logged into MySQl, we can create a database by using the create database NAMEOFDB; command.  This will make a database with the name you provide.

Now we want to add tables to that database. To show a list of available databases enter show databases;.  To select one of them to modify, use the command use NAMEOFDB;.  After doing this, any tables created will automatically be added to the database you are currently using.

Lets make a table.  As a test, we can make the table we described earlier that has 3 fields: a first name, a last name and an email.  To do this, use the create table command which looks like this:

create table NAMEOFTABLE(
key INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(key),
first varchar(255) NOT NULL,
last varchar(255) NOT NULL,
email varchar(255)
);

In the above, code we can see firstly that I created four fields and not three.  This is because in practice it is usually good to create an integer field that indexes your database and use this as your primary search key, hence the second line PRIMARY KEY (key).  That tells MySQL that this is my primary indexing field for the database. The modifiers NOT NULL simply mean that when updating/inserting rows, this field must exist; and AUTO_INCREMENT means that it will automatically generate a number that is one greater than the last index created for your. The email variable character array of length 255 does not have the NOT NULL modifier, which means it is possible for someone to specify a person who doesn’t have an email.  Once you enter this and run the code, you can see a list of available tables by typing show tables;.  Also you can see the columns used by the table by typing desc NAMEOFTABLE;.  To add items to a table, use the INSERT command.  An example INSERT into this table is provided below:

INSERT INTO NAMEOFTABLE (first,last,email) values ("John","Doe","jdoe@imaginary.com");

This line tells MySQL to insert this row into NAMEOFTABLE, then provide these fields (first,last,email), and then these are the values that correspond to those fields (“John”,”Doe”,”jdoe@imaginary.com”).

To query the table to find entries, enter:

SELECT * from NAMEOFTABLE;

This command will list all entries from table NAMEOFTABLE.  If we appended where first="John" to that line, it displays entries that have “John” in the “first” field.

Now you know the very basics of MySQL.  For further reading, explore the MySQL website and use Google.  Also, I am always here as a resource, so feel tree to drop me a line on the website, on facebook, or via email and I’ll do my best to get back to you quickly.

TJ

Let the Servlet’s Serve Us – Java Servlets 101

Yo yo yo… Hope everyone is having a grand old Thursday. Actually if you are reading this I am currently on a plane to Seattle…. can u say WIN :). O

Ok.. so as per the title above this post is about servlets. Now since I know you all know everything there is to know about the Internet, networking, http, etc. I am just going to skip to the good stuff… just kidding.

So it all starts with understanding the idea of a website, specifically http. OK, so you want to go to google.com, your browser or whatever application that is going to get the site for you sends an application layer (wikipedia: OSI layers) http request to the webserver that google.com points to (wikipedia: DNS). All this is done via socket connections, normally over port 80, the default http port.  Magically this request gets to the server and the server’s web hosting application must process it. This application interprets the request and then responds by sending back a stream of text, the html code of the webpage. Then your browser parses this code and finds other neccessary files. For example, it may pull images from other webpages in order to render the page. It requests these files from the server and the server responds in turn. So we have this sequence of events:
User ->(GIVE ME GOOGLE.COM)->browser Request index.html from google.com -> Server browser
<- Response index.html as text stream
browser Request img1.jpg from google.com -> Server User <- GOOGLE.COM <- browser <- Response img1.jpg as byte stream

This request/response structure is how all webpages are built by your browser… ok, wait, weren’t we talking about servlets? Right, so where do they fit in? Servlets can be thought of as mini-page servers, so if we take our block diagram for website hosting and we insert a java program that can handle these requests on the server by executing you write you would have a servlet container. I will show you.

So your Java code can run within the servlet container and serve web pages to your user. There are two ways to approach servlets, creating actual java servlets, extending that class from the api etc or you can use pages called jsp’s. Jsp’s stand for JavaServer Pages. These pages are java code are compiled by the container when a request for them is made, and can be very useful and simple to implement.

Ok before I wrap up a quick example on how/why to use a JSP. Say we want a website that can keep track of how many users have visited it, this would be a great place to use a jsp. First off install tomcat or another servlet/jsp hosting software on your local machine. I personally like tomcat, here is a link on how to get started with that http://tomcat.apache.org/

Next we have to write a jsp. So once you have tomcat running you will have to make a JSP file in a new web-application folder. To do this make a new file called index.jsp in new folder called watever you like, i will call mine bob in this directory TOMCAT_INSTALL_DIRECTORY/web-apps/. Now open index.jsp in your favorite text editor I recommend notepad++(google is your friend). Once in there paste in this code

<%!
int count = 0;
%>
<%
count++;
out.println(count);
%> 

First thing to note about above that all code between the <% %> is java code that will be compiled by tomcat when your webpage is polled. the <%! means that block is global and that variable will reside between webpage calls on the server and can be updated. So this page when called will simply print out the value of count after it is incremented, visit http://localhost:8080/bob/index.html  in your browser to see this effect. out is the default print out stream for an http response. Every jsp is passed impliciet information from the request and is given the ability to modify its response to that request. This is done by interacting with the request and the response objects that you magically have access to in your jsp. To see kinda what im talking about add the line out.println(request.getParameter(“HELLOWORLD”)); after the print out of count. Now when you call your webpage in tomcat at http://localhost:8080/bob/index.html it should print out the number and null. If you modify this page request with the parameter HELLOWORLD you can have it print out what you pass. So try this, http://localhost:8080/bob/?HELLOWORLD=OMG_IT_WORKS!!!

Hope you enjoyed getting your feet wet in Servlets, hit me up on facebook or at m5 with any questions

-TJ http://umassamherstm5.org/about/people/staffers/tj-pavlu