In this tutorial we are going to cover the basics of setting up and using a Java Database Connection (JDBC) and NetBeans as our developer environment. We are also going to review another method of storing data but is not as convenient. This method will be storing data to a text file and we will not retrieve any of the information we input as MySQL and databases in general are more widely used.  This will utilize a MySQL database where will will select, insert, update, and delete data demonstrating one at a time.  We will need to start by setting up our MySQL Database. The table to be set up can be done by running the code inside the JavaDataTest database query window.

--
-- Table structure for table `Test`
--

CREATE TABLE `Test` (
`id` int(11) NOT NULL default '1',
`firstname` varchar(255) default NULL,
`middlename` varchar(255) default NULL,
`lastname` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

After the MySQL database is set up we will need to download a connector for connecting to the MySQL database called connector/J. After this is downloaded we can extract it to any folder you like as long as you are able to remember its path. After the extract is complete we will need to reference this in our project so that we can utilize the MySQL features that the connector will give us. To do this we need to go to the right hand side of NetBeans, under the Projects pane we need to right click on the folder called “Libraries” and click “Add JAR/Folder”. After we click this a dialogue box will appear and we will navigate to the place where we installed the connector. We want to select the JAR file by double clicking it, which will add it to our project and take us back to the development window of the eclipse IDE.

We then want to start writing some code to first ask the user what he would like to do. We should give the user the ability to add and show all entries in this tutorial. To do this we first need to start by adding some imports, like the ones seen below.

package helloworlddatabase;

import java.sql.*;
import javax.sql.*;
import java.io.*;

These imports will allow us to utilize the SQL features needed to create a query, access the database and table, and ultimately manipulate some data. The “import java.io.*” will let us use input and output methods and variables. This import will be invaluable when it comes to asking the user what they would like to do. We then need to create three string variables in our main method; username, password and the url our database is located at. After these are added to your .java file we can then move to the main method and start taking some input from the user.

public class HelloWorldDatabase
{
private static String userName = "javadatatest";
private static String password = "Wasteland1";
private static String url = "jdbc:mysql: //javadatatest.db.7422315.hostedresource.com/javadatatest";

To do this we need to create a string variable to accept user input. This string will determine whether the user will show, delete, update or insert a row/rows of data. We then need to create a BufferedReader to be able to accept the string variable. We want to wrap all of this in a while loop so that we can continue to take input until the user enters “exit” into the string variable. After this we will create an if statement to decide what action to take based on the users input. To wrap everything up into a nice and safe bundle we will put a try catch statement around everything inside the main method.

// private static String enterItHere = "";

public static void main(String args[])
{
try
{
String enterItHere = "";
BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
while(!"exit".equals(enterItHere))
{
System.out.println("What do you want to do? (insert, update, delete, show, exit)");
enterItHere = in.readLine();

if("insert".equals(enterItHere))
{

}
else if("update".equals(enterItHere))
{

}
else if("delete".equals(enterItHere))
{

}
else if("show".equals(enterItHere))
{
getItems();
}
else
{

}
}
}
catch(IOException e )
{
e.getStackTrace();
}
catch(Exception e)
{
e.getStackTrace();
}
} //end main

We now want to fill in the methods we described above, starting with the insertItems method. For this method to function correctly we need to add three string variables named “first”, “middle” and “last”. These will be for getting the users first, middle and last names. Then we can make a string variable to create the SQL query. We then need to supply the username, password and url to connect to the database and then the table within the database using the DriverManager.getConnection method. After this, we want to execute the query buy using the executeUpdate method and adding in our SQL statement inside the brackets. We use the executeUpdate method as we will not be returning any values so we only need to update the MySQL Database. This will all be wrapped in a handy little try catch statement and will look to catch any errors that may be thrown.

public void insertItems(String first, String middle, String last)
{
try
{
String query = "INSERT INTO Test (firstname, middlename, lastname) VALUES (" + first + ", " + middle + ", " + last + ")";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, userName, password);
Statement stmt = conn.createStatement();
stmt.executeUpdate(query);

}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
}

We can now move on to the getItems method which will return all of the results in the Database. We user the same three variables to make the connection; username, password and url to make the connection to the database. We then need to create string variables for the persons first, middle and last names which will accepting input a little later on. This time we need to execute the executeQuery method which will return a ResultSet which we can use with our string variables. More specifically, we will return the results of each line to either the first, middle and last name variables then run a string to the console greeting them all.

public static void getItems()
{
try {
String first = "", middle = "", last = "";
String query = "Select * FROM Test";
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection (url, userName, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);

while (rs.next())
{
first = rs.getString(2);
middle = rs.getString(3);
last = rs.getString(4);
System.out.println("Hello " + first + " " + middle + " " + last);
} //end while

con.close();

} //end try
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
}

} //end class

After all of this is complete you have the better part of working with a MySQL database complete. In the next tutorial we will go over how to select certain records and edit or delete them. For those who are looking to get ahead and already know how a DELETE and UPDATE query works in SQL/MySQL then feel free to take the executeUpdate example and modify it. Until the next tutorial, Happy Hacking!

Share This