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:
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,
first varchar(255) NOT NULL,
last varchar(255) NOT NULL,
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","firstname.lastname@example.org");
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”,”email@example.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.