Download Java Database In Netbeans and more Exercises Computer Science in PDF only on Docsity!
Java and Databases
Databases: 1 2 3 4 5 6
In this section, you'll learn about Java databases. You'll create a simple database with one table,
and learn how to connect to it using Java code.
About Java and Databases
Java uses something called JDBC (Java Database Connectivity) to connect to databases. There's a JDBC API, which is the programming part, and a JDBC Driver Manager, which your programmes use to connect to the database.
JDBC allows you to connect to a wide-range of databases (Oracle, MySQL, etc), but we're going to use the in-built database you get with the Java/NetBeans software. The database is called Java DB, a version of Apache Derby. It runs on a virtual server, which you can stop and start from within NetBeans.
To check that have everything you need, have a look at the Services tab in NetBeans. If you can't see the Services tab, click Window from the NetBeans menu. From the Window menu, select Services. You should see something like this:
Expand the Databases item to see a Java DB item, and a Drivers section:
The idea is that you start the Java DB virtual server, and then create and manipulate databases on
the server. There should be a database called sample already set up: (But don't worry if it's not there as we'll create our own database.)
In the image above, there are three databases: one is called sample , one is called test1 , and the
other is called exams.
For the project in this section, we're going to set up a new database. You'll then learn how to connect to this database using Java code. The database we'll create will be a simple one-table affair, rather than multiple tables connected together. You can indeed create multiple tables with Java DB, but we don't want to complicate things unnecessarily.
Starting the Virtual Server
The first thing to do is to start the server. So right click on Java DB. You'll see a menu appear.
Select Start Server :
Have a look at the Output window and you'll see a few messages appear: (If you have a firewall running, you'll need to let the Java DB server through.)
Once the server is up and running you can create databases. You'll see how to do that in the next lesson.
Click OK to create your database. It should then appear on the list:
Creating a Table in the Database
Now that the database has been created, you need to create a table in the database. To do so, right click on your database. From the menu that appears select Connect :
When a connection is made, you'll see some default folders for Tables, Views, and Procedures (see further down if your screen is not like this):
To create a new table in your database, right click the Tables folder. From the menu that appears,
select Create Table :
If you don't see just the three folders above, but have something like this instead:
From here, you not only type a name for your table, but you also set up the columns for the table.
In the Table Name at the top, delete the default name of Untitled. Type a new name for your table. Call it Workers. You'll then have a table called Workers, which is in the Employees database.
But you can't click OK just yet as the table has no columns in it. We want to create columns with the following names:
ID First_Name Last_Name Job_Title
The ID column will hold a unique identifying number. This will identify a row in the table. A column with unique data in it is known as a Primary Ke y. Because it's the Primary Key, the column has to hold data: It can't hold a null value. (A null value just means there's no information there.)
If your Create Table dialogue box is like the first one, then put a tick in the box for Key. When you tick the Key box, check marks will also appear for Index and Unique. Now enter a title in the Column Name area. Type ID :
You now need to specify what kind of data is going in to the column. For our ID column, we'll
have Integers. So scroll along until you come to Data Type. Click on Data Type and a drop down list will appear. From the drop down list, select Integers:
If your dialogue box is like the second one, then you need to click the Add Column button to add
your first table column. You'll see another dialogue box appear. This one:
We now have enough for the ID column in the table. Click the Add Column button on the right
to add a new column to the table. Enter the following values for this column (VARCHAR means a variable number of characters):
Key: Unchecked Index: Unchecked Null : Unchecked Unique: Unchecked Column Name: First_Name Data Type : VARCHAR Size : 20
For the third column in your table, enter the following values:
Key: Unchecked Index: Unchecked Null: Unchecked Unique: Unchecked Column Name: Last_Name Data Type: VARCHAR Size: 20
For the final column, here are the values to enter:
Key: Unchecked Index: Unchecked Null: Unchecked Unique: Unchecked Column Name: Job_Title Data Type: VARCHAR Size: 40
When you're finished, your Table dialogue box should look like this:
Click OK when you've entered all the information. Your table and table columns will then be
created:
The next thing to do is to add some records to the database table. We'll do that next
Adding Records to a Java Database Table
Databases: 1 2 3 4 5 6
A database table is like a spreadsheet, in that it has rows and columns. Each row in our table has cells (fields) for an ID value, a First Name, a Last Name, and a Job Title. Shortly, you'll learn how to write code to add new rows of information to the table. But you can use the NetBeans IDE to add rows as well.
To add a new row to your table, right click on your table name. From the menu that appears,
select View Data :
When your click the new row icon, a dialogue box appears:
As you can see, there are text boxes for each column in our table. For the ID column, we'll use sequential numbering, starting with 1. The second row in the table will then have an ID of 2, the third row 3, etc. The numbers are not the row numbers: they are just unique values for each ID field. We could have easily started with a value of 100 as the first ID number. The second number would then be 101, the third 102, etc.
Enter the following data as the first row of your table:
ID: 1 First Name: Helen Last Name: James Job Title: IT Manager
Your dialogue box will then look like this:
Click OK when you're done and you'll be returned to the NetBeans window. The first row should
then be displayed:
Add three more rows with the following data:
ID: 2 First Name: Eric Last Name: Khan Job Title: Programmer ID: 3 First Name: Tommy Last Name: Lee Job Title: Systems Analyst
ID: 4
First Name: Priyanka Last Name: Collins Job Title: Programmer
When you've finished adding the new rows, your NetBeans window should look like this one:
SQL Commands
Databases: 1 2 3 4 5 6
In the previous lesson, you created records in your Java DB database table. In this lesson, you'll
learn a few SQL commands so that you can manipulate records in a table.
SQL stands for Structured Query Languag e, and is a way to query databases. You can select records, insert, delete, and update records, create tables, drop tables, and more besides. It's quite
a powerful tool.
SQL uses simple keywords to do the work. If you want to select all the records from a table, the words SELECT and FROM are used, along with the "all records" symbol, which is ***** :
SELECT * FROM table_name
If you look at the top half of the NetBeans window, you'll see that a SELECT statement has already been set up: (NOTE: SQL is not case sensitive)
select * from ADMIN.WORKERS
This says "Select all the records from the table called Workers". (The ADMIN part, before the dot of Workers, is something called a Schema. This describes the structure of the database, but also identifies things like users and the privileges they have. Don't worry about schemas, as we won't be going into them.)
In NetBeans, you run a SQL statement by clicking the Run button on the toolbar:
The results from the SQL statements are then displayed in the bottom half of the window:
The WHERE Clause
To narrow down your search results, you can use a WHERE clause with the SELECT statement:
SELECT * FROM table_name WHERE column_name=value
After the keyword WHERE you need the name of a column from your table. You then type an equals sign, followed by a value. As an example, here's a SQL statement that returns all the programmers in our table:
SELECT * FROM ADMIN.WORKERS WHERE JOB_TITLE='Programmer'
To try this SQL Statement out, right-click your table name in the Services area. From the menu that appears, select Execute Command :
When you click on Execute Command, a new window appears. Type the above SQL Statement, and then click the Run icon:
The results will be displayed in the bottom half of the window:
As you can see, two rows are returned from the query. You can also use the keyword LIKE with the WHERE clause. This then replaces the equals sign. LIKE is usually used with a wildcard character. The wildcard character % means "any characters", for example, while an underscore is used for just a single character. Instead of the equals sign or the keyword LIKE, you can also use the conditional operators (Greater Than, Less Than, etc.). If we had a salary column, we could search for all workers who are GETTING PAID more than 1000 a week: SELECT * FROM ADMIN.WORKERS WHERE SALARY > 1000
Exercise Try running the following SQL Statement:
SELECT * FROM ADMIN.WORKERS WHERE JOB_TITLE LIKE '%er'
How many results are displayed?
Connect to a Database Using Java Code
Databases: 1 2 3 4 5 6
In a later section, you'll create a Java form that loads information from a database. The form will
have Next and Previous to scroll through the data. Individual records will then be displayed in text fields. We'll also add button to Update a record, Delete a record, and create a new record in the database.
To get started, and for simplicity's sake, we'll use a terminal/console window to output the results from a database.
So start a new project for this by clicking File > New Project from the NetBeans menu. Create a Java Application. Call the package database_consol e, and the Main class DBConnect :
When you click Finish, your code should look like this:
Connecting to the Database
To connect to a database you need a Connection object. The Connection object uses a DriverManager. The DriverManager passes in your database username, your password, and the location of the database.
Add these three import statements to the top of your code:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException;
To set up a connection to a database, the code is this:
Connection con = DriverManager.getConnection( host, username, password );
So the DriverManager has a method called getConnection. This needs a host name (which is the location of your database), a username, and a password. If a connection is successful, a Connection object is created, which we've called con.
You can get the host address by looking at the Services tab on the left of NetBeans:
The address of the highlighted database above is:
jdbc:derby://localhost:1527/Employees
The first part, jdbc:derby://localhost , is the database type and server that you're using. The 1527 is the port number. The database is Employees. This can all go in a String variable:
String host = "jdbc:derby://localhost:1527/Employees";
Two more strings can be added for the username and password:
String uName = "Your_Username_Here"; String uPass= " Your_Password_Here ";
Add these three string before the connection object and your code would look like this:
