Introduction

In almost every good Java program, data storage is used. Have you ever wondered how this is done? If yes, you'll be satisfied, beacause this tutorial will cover databases in Java: how to set up one, access it, modify data... A database is 100 times faster than using plain text files to manage your data, and it allows to search and order the results using SQL syntax (don't worry if it is unknown to you, it will be covered later). First of all, we need to set up your database. You need to have some basic knowledge about Java and packages to be able to follow.

Setting a database

In the vast world of databases, there are quite a lot of database systems which can be used: MySQL, Oracle, SQLServer... This tutorial will expalin MySQL. If you've ever used PHP before, you may be familiar with MySQL. First of all, let's get MySQL since it is not included with Java (and it is in no way related to Java, we will need a driver). The URL of the MySQL downloads page is http://www.mysql.org/downloads/. Get the most recent version with no installation. Don't put any password for the "root" user because we will only use this for testing purposes. For the tutorial, let's suppose the install directory is C:mysql. Unzip the downloaded archive in your C:mysql directory, then create a batch file named start.bat in C:mysqlbin with this line:

Then create stop.bat in the same directory with that line:

Create admin.bat with this line:

In the C:mysql directory, create a file named my.ini, and write:

Run admin.bat and don't enter any password. When the invite turns to mysql>, type "CREATE DATABASE `data`;". This will create a database named data and it will be used in this tutorial. That's all, our database is prepared. Now let's get the driver to allow Java to communicate with the database.

In the MySQL download page, get MySQL Connector/J and put the packages in your Java project directory. You need to have 2 directories: com and org. Depending on which version of the driver you get, these 2 directories may be in a jar archive, so unzip it to get the packages. Once you have your packages in your directory, we're ready to dig in programming!

Execute the file start.bat that you created earlier. Let the command window open to ensure MySQL is running. Create a class in your project and add the main method to it. You need to put a few instructions, i'll explain just after:

import java.sql.*;

What does all that stuff mean? We need to create our class and the main method, this requires no explanation. We open a try block, because every statement related to SQL may throw a SQLException, so we have to handle it. Since Class.forName("") also throws a ClassNotFoundException, and error handling is not the goal of the tutorial, let's just catch Exception to make the compiler happy. The first instruction loads the driver. You only need to put it once in your whole program and the driver will be loaded. The org.gjt.mm.mysql.Driver is in the org directory we extracted from the downloaded archive. The other statement opens a connection to the database and returns it as a java.sql.Connection object. This method takes 3 parameters: host, username, password. The 3 parameters are pretty self-explanatory. We specified the "data" database in the host parameter. Now that our connection is set, we are ready to execute some statements with SQL syntax.

Every query (command sent to the server) has a syntax. SQL syntax is not the main purpose of this tutorial, however a few commands will be covered here. First of all, to create a database, the syntax is:

This was the statement we used earlier to create our "data" database. Each database must have tables to store data. Each table can have its own fields, data types, values and so on. To create a table, the following will be used:

To select some lines from a table, the following is used. However, the WHERE clause is optional if you wish to select the whole table:

To remove some lines from a table, we use this syntax, here again the WHERE clause is optional but be careful! Deleted data can't be brought back, so always use the WHERE clause:

If you really want to empty the table, you can use this shortcut (again, the lost data is lost forever so be careful):

If we want to change a certain value in a table, we shall use:

Finally, to insert some data in a table, we'll use this code. Make sure the values are matching the data types:

Don't worry if it seems complicated for now, we'll practice them by giving examples of how it's done. Remember, SQL syntax is not case sensitive but it is recommended to use the same case as in the examples above. Let's create our first table.

In order to be able to store anything in our database, we need to have tables. We'll make a table that will look like that:

As you probably noticed, this table has 3 columns: id, name, address. It has 3 records (rows) in it. Every column has a data type. In this example, an appropriate structure of the data types should be as follow: ID is an int, name and address are Strings. In SQL language, the word "String" does not exist, it is a varchar. To send a statement to the MySQL server in Java, we need to use conn.createStatement().execute("<some command>"). Let's take a look at the Java code to create the table:

As you noticed, we sent this query to the database (the lines have been numbered):

If you read the basic SQL syntax part above, this command may seem familiar to you. Let's look at each line:

Line 1: Nothing special here, we tell MySQL that we want to create a table names "people".

Line 2: We add a column named "id" having a int(64) data type (explained later). Something is new here: AUTO_INCREMENT. If you want to make any software that stores record, this flag is a way to make each line (each item) have its own unique ID. No need to specify a value for this line, it is automatically set when you add a record! In my opinion, almost all tables should have a column like that (but it is not needed).

Line 3: We add a "name" column with a varchar(255) type, explained later.

Line 4: Nothing special here.

Line 5: This is an important part. Remember we set the "id" column to AUTO_INCREMENT? Well you must tell here which column is the autoindex one. In this case, it is "id".

Line 6: All columns containing text (varchar and text data types) must be declared here. In this case, "name" and "address" are like that.

Choosing the correct data type for a column can cause headaches if you're not familiar with them. Here's the ones you'll most likely use:

int(N): An integer (number without decimals) with a size of N. Example: 45425

varchar(N): A string (chain of characters) with a max character limit of N. Example: "Hello world"

double: A number that can have decimals. Example: 3.1416

text: The name says it all, it is text. This can store a textarea's contents. Example: "This is some text..."

tinyint(1): A boolean value which can be either 0 or 1. Example: 1

For a column, many data types could fit, but only one can be chosen. Pick the one that's most likely to ensure maximum compatibility. If you try to insert a line with a bad data type (e.g. Inserting "Hello" in a int column) then the SQL server will send an error and the line won't be inserted. Remember, you can change the data type later whenever you want.

Now, we're at the point where we are ready to insert our data in the table. We already know which data we want to insert (refer at the table drawn above). We will use the INSERT statement. Here's the full code to insert the rows:

All right, we specified to add "Bob" and "123 Fake Street" in the respective fields "name" and "address". But, wait a minute you'd say, what about the "id" field? Remember we set an AUTO_INCREMENT flag? We don't need to specify a value for that field, it will be automatically set to the autoindex value. With the only piece of code above, we have our table with its 3 rows inserted.

Now, let's say we don't want John in the table. We have many ways to delete his row, here's the safest one:

This removed the row containing John. We have only 2 rows remaining: Bob and Roger, and they have IDs 1 and 2. If we would insert another row, it would have an ID of 4, even if we deleted the 3. The autoindex value never goes down, so EVERY row will be unique!

In the statement above, you noticed the LIMIT keyword. This is a simple thing, as it allows to limit the number of rows affected (and at the same time speed up the query time). If we put only one number (e.g. LIMIT 2), the number of rows affected will be of that number (e.g. 2). If, in the other hand, we use a SELECT statement and we want, let's say, to page our results in pages of 100 elements, and we're at page 3, we'd use LIMIT 200, 100. The first number decided with row to start at (0 is the first row of all, so 200 is the 201st element) and the second number tell how many rows we want to return, which is 100 in this case. It is important that you understand the LIMIT clause before making any SQL-based program. Finally, the LIMIT clause is never required, but recommended.

In this example, we'll "read" the table and print the results in the console:

When you select some lines from a table, use the Statement.executeQuery("") method which returns a ResultSet containing the results. By reading the above line, you can tell that we want to select the "name" column from the "people" table where the "id" value is less than 4, and we order the results by the "id" column. Since all IDs are lesser than 4, all rows are returned (the two remaining rows, because we deleted John's row). Now, we have the 2 rows stored in the java.sql.ResultSet object "rs". The next() method of ResultSet returns a boolean that tells if another row is present, and if yes it jumps to that row. The rs.getString("name") statement returns the string (varchar) stored in the "name" column of the current ResultSet's line. In that case the output will be:

You can do anything you want with these results. I suggest you to explore the java.sql.ResultSet methods to find out how you can treat your results.

Have you ever heard about SQL injections? Well when a SQL statement contains some input from the user, he might enter a part of SQL statement to screw up the table! You can prevent a lot of these by "escaping" a ' character by using the following:

Now the "input" string is safe and can be added in a column. This is one thing that quite a lot of programmers forget to do.

All right, we're near the end. To make sure you understood all, we're going to test the code. Start your server (remember: start.bat) then run the following code:

If everything is OK, the output will be:

You can run stop.bat to shutdown the server, we're finished with it.

Conclusion

Yeah, that's it, the tutorial is over. SQL and databases together make a vast world from which we have barely scratched the surface. Remember, if you have trouble with SQL syntax, there are tons of tutorials floating over about it, like the MySQL official website. I hope the tutorial helped you to learn the basics of databases in Java. Good luck if you're making a SQL-based application! Never use text files to store your data, you must learn SQL, so it is important you fully understand this tutorial!

Kaynak www.dreamincode.net/forums/showtopic32360.htm