Page 1 of 1

Using MySQL with Java databases and SQL basics, and how to use with Java Rate Topic: ***-- 3 Votes

#1 alpha02  Icon User is offline

  • Sexy DIC God
  • member icon

Reputation: 47
  • View blog
  • Posts: 803
  • Joined: 20-May 06

Posted 28 August 2007 - 07:46 AM

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:\mysql\bin with this line:

mysqld --defaults-file=..\my.ini


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

mysqladmin -u root shutdown


Create admin.bat with this line:

mysql -h 127.0.0.1 -u root -p 


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

[mysqld]
# set basedir to your installation path
basedir=C:\mysql
# set datadir to the location of your data directory
datadir=C:\mysql\data


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.

Using the Java MySQL driver

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!

Opening a connection to the database

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.*;

public class SQLProject{
	public static void main (String[] args){
		try{
			Class.forName("org.gjt.mm.mysql.Driver"); //Load the driver
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/data", "root", ""); //Connect
		}
		catch (Exception err){}
	}
}


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.

How SQL syntax works

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:

CREATE DATABASE `<database name>`;


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:

CREATE TABLE `<table name>`(
`<column name>` <data type>([data modifier]) NOT NULL,
`<another column name>` <data type>([data modifier]) NOT NULL,
FULLTEXT(`<fulltext column name>`, `<another fulltext column>`));


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:

SELECT `<column name>`, `<another field name>` FROM `<table name>` WHERE <conditions>;


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:

DELETE FROM `<table name>` WHERE <conditions>;


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

TRUNCATE `<table name>`;


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

UPDATE `<table name>` SET `<field>` = '<some value>' WHERE <conditions>;


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

INSERT INTO `<table name>` (`<field 1>`, `<field 2>`) VALUES ('<value 1>', '<value 2>');


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.

Creating a 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:

Table: people
+------+-----------------------------+---------------------------+
| id   | name						| address				   |
+------+-----------------------------+---------------------------+
| 1	| Bob						 | 123 Fake Street		   |
| 2	| Roger					   | 666 Devil Street		  |
| 3	| John						| 325 Red Street			|
+------+-----------------------------+---------------------------+


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:

import java.sql.*;

public class SQLProject{
	public static void main (String[] args){
		try{
			Class.forName("org.gjt.mm.mysql.Driver"); //Load the driver
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/data", "root", ""); //Connect

				  conn.createStatement().execute("+
			+"CREATE TABLE `people` ("+
			+"`id` int(64) NOT NULL AUTO_INCREMENT,"+
			+"`name` varchar(255) NOT NULL,"+
			+"`address` varchar(255) NOT NULL,"+
			+"UNIQUE (`id`),"+
			+"FULLTEXT(`name`, `address`))");
		}
		catch (Exception err){}
	}
}


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

1) CREATE TABLE `people` (
2) `id` int(64) NOT NULL AUTO_INCREMENT,
3) `name` varchar(255) NOT NULL,
4) `address` varchar(255) NOT NULL,
5) UNIQUE (`id`),
6) FULLTEXT(`name`, `address`));


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.

Data types

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.

Inserting records in a table

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:

import java.sql.*;

public class SQLProject{
	public static void main (String[] args){
		try{
			Class.forName("org.gjt.mm.mysql.Driver"); //Load the driver
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/data", "root", ""); //Connect

				  conn.createStatement().execute(CREATE TABLE `people` ("
			+"`id` int(64) NOT NULL AUTO_INCREMENT,"
			+"`name` varchar(255) NOT NULL,"
			+"`address` varchar(255) NOT NULL,"
			+"UNIQUE (`id`),"
			+"FULLTEXT(`name`, `address`))"); //Create the table

			conn.createStatement().execute("INSERT INTO `people` (`name`, `address`) VALUES ('Bob', '123 Fake Street')"); //Insert a row
			conn.createStatement().execute("INSERT INTO `people` (`name`, `address`) VALUES ('Roger', '666 Devil Street')");
			conn.createStatement().execute("INSERT INTO `people` (`name`, `address`) VALUES ('John', '325 Red Street')");

		}
		catch (Exception err){}
	}
}


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.

Deleting a row in the table

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:

conn.createStatement().execute("DELETE FROM `people` WHERE `id` = '3' LIMIT 1");


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!

The LIMIT clause

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.

Printing a table column's contents in the console

In this example, we'll "read" the table and print the results in the console:
ResultSet rs = conn.createStatement().executeQuery("SELECT `name` FROM `people` WHERE `id` < 4 ORDER BY `id`");
while (rs.next() == true){
	System.out.println(rs.getString("name");
}


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:

Bob
Roger


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.

Security measures

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:

String input = textfield.getText().replace("'", "\\'");


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.

Putting it all together

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:

import java.sql.*;

public class SQLProject{
	public static void main (String[] args){
		try{
			Class.forName("org.gjt.mm.mysql.Driver"); //Load the driver
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/data", "root", ""); //Connect

				  conn.createStatement().execute("CREATE TABLE `people` ("
			+"`id` int(64) NOT NULL AUTO_INCREMENT,"
			+"`name` varchar(255) NOT NULL,"
			+"`address` varchar(255) NOT NULL,"
			+"UNIQUE (`id`),"
			+"FULLTEXT(`name`, `address`))"); //Create the table

			conn.createStatement().execute("INSERT INTO `people` (`name`, `address`) VALUES ('Bob', '123 Fake Street')"); //Insert a row
			conn.createStatement().execute("INSERT INTO `people` (`name`, `address`) VALUES ('Roger', '666 Devil Street')");
			conn.createStatement().execute("INSERT INTO `people` (`name`, `address`) VALUES ('John', '325 Red Street')");

			conn.createStatement().execute("DELETE FROM `people` WHERE `id` = '3' LIMIT 1"); //Delete John's row

			ResultSet rs = conn.createStatement().executeQuery("SELECT `name` FROM `people` WHERE `id` < 4 ORDER BY `id`"); //Select the rows
			while (rs.next() == true){ //Loop through results
				System.out.println(rs.getString("name")); //Print the result
			}

			rs.close(); //Close the result set
			conn.close(); //Close the connection
		}
		catch (Exception err){}
	}
}


If everything is OK, the output will be:

Bob
Roger


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!

Is This A Good Question/Topic? 4
  • +

Replies To: Using MySQL with Java

#2 dragon-slayer  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 17
  • View blog
  • Posts: 93
  • Joined: 06-May 07

Posted 29 August 2007 - 04:55 PM

Nice tutorial :^: I first did SQL for Java when I was making my mmorpg which has been put on hold for a while
Was This Post Helpful? 0
  • +
  • -

#3 1lacca  Icon User is offline

  • code.rascal
  • member icon

Reputation: 44
  • View blog
  • Posts: 3,822
  • Joined: 11-August 05

Posted 29 August 2007 - 11:42 PM

Just a little disclaimer: the Security measures section is a bit misleading, but I think this is due to the size of the tutorial. Simply escaping data like this is not a good way to fight SQL injection (actually cocatenating user input into the SQL query is a very bad thing). The preferred way is the usage of prepared statements, which escape every parameter, and this makes SQL injection impossible.
Was This Post Helpful? 1
  • +
  • -

#4 ossetia  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 18-June 09

Posted 18 June 2009 - 12:43 PM

Hi!
I need help! I tried to execute my admin.bat file and i didn't enter any password, i just pressed enter,but it doesn't invite me to mysql. I heard one short signal and the file closed. What's the trouble?
Thank in advance.
Was This Post Helpful? 0
  • +
  • -

#5 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Posted 09 August 2009 - 10:33 AM

Very nice tutorial alpha02

Thanks
Was This Post Helpful? 0
  • +
  • -

#6 NeoTifa  Icon User is offline

  • Whorediot
  • member icon





Reputation: 2794
  • View blog
  • Posts: 15,904
  • Joined: 24-September 08

Posted 10 August 2009 - 11:06 AM

It's about freaking time we got one of these. Kinda rushed imho. I still don't quite understand. You should make a part 2 or something.
Was This Post Helpful? 0
  • +
  • -

#7 ceycey  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 30-August 09

Posted 30 August 2009 - 08:22 PM

great,thank you very much :) :^: :^: :^:
Was This Post Helpful? 0
  • +
  • -

#8 circuspeanuts  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 224
  • Joined: 11-April 08

Posted 20 November 2009 - 02:05 PM

very nice! How do I wrap it all in a GUI and have it read/write?
Was This Post Helpful? 0
  • +
  • -

#9 DevWalt  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 09-May 08

Posted 11 December 2009 - 02:48 PM

Must run start.bat before admin.bat
Was This Post Helpful? 0
  • +
  • -

#10 swoop314  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 25-November 09

Posted 12 December 2009 - 11:28 PM

Nice Tutorial :D !

:^: But you should add mysql-connector-java-5.1.10-bin.jar that you can find in Java connector 5.0 to the following path :
C:\Program Files\Java\jdk1.6.0\jre\lib\ext

then and only then the connection will establish.

i've spent 4 hours to detect this error. :crazy:
Was This Post Helpful? 0
  • +
  • -

#11 vbabey  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 73
  • Joined: 30-July 12

Posted 13 September 2012 - 12:31 AM

It is a great work...Can understand easily. Keep it up...
And Thank you very much for these tutorials...
Was This Post Helpful? 0
  • +
  • -

#12 Takk  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 111
  • Joined: 08-March 13

Posted 18 March 2013 - 11:08 AM

Thanks for the tutorial and the additions the others have made. One more addition for those running Linux (Ubuntu more specifically)..

You'll need to install the mysql-client, mysql-server, and the libmysql-java packages also, Then...

Edit your /etc/environment to include the following:

CLASSPATH=".:/usr/share/java/mysql.jar"



Everything else should be the same.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1