Programming in Java Netbeans - A Step by Step Tutorial for Beginners: Lesson 49
Lesson 49: Java NetBeans And SQL Commands
In the previous lesson, we did create Java DB database, a table and we also inserted a few records in the table. In this lesson, we shall learn few Structured Query Language (SQL) commands so that we can use them to manipulate records in the table.
Structured Query Language (SQL) is a database query language used to query the database and table records. With this language we can create tables, drop tables, select records based on some criteria, inserts records, update records, delete records etc.
Structured Query Language (SQL) is very easy to learn and use since it uses English-like keywords to manipulate database, tables and records.
In this lesson, we shall only learn about the basic SQL commands that you may require for this Java tutorial but I would recommend you learn more of SQL commands and their usage.
Note: Structured Query Language (SQL) is not case sensitive so you can use commands in lowercase or in uppercase in the same way.
SQL Commands: SELECT … FROM
Two of the most popular SQL commands that you should surely start with are SELECT and FROM. These two commands, when combined with SQL clauses, are used to select records from a given table. For examples, to select ALL (*) records from the table STUDENTS we use:
SELECT * FROM STUDENTS
Notice that the asterisks (*) symbol stands for ALL command in SQL. So, to select all records from a given table, we use SELECT * FROM table_name. When you select records from a table, it is a good SQL convection that you qualify the table i.e. specify its database schema. So a better SQL command would have been:
SELECT * FROM APP.STUDENTS
APP is the database schema while STUDENTS is the table name and these two are separated by a dot (.) in between. A database schema describes the structure of the database, database users, their privileges etc. Java NetBeans requires that you always qualify table using their schema in SQL statements.
Open the STUDENTS table we created in the previous lesson. To open the table, you have to start the server and then connect the table. We’ll use this table to try some of these SQL commands. Notice that there is SQL statement already at the top of the table and the results of this statement (i.e. ALL records) are displayed below it.
Note: To execute SQL statements in Java NetBeans, right click on the statement and select Run statement or click on the Run SQL button on the toolbar.
To select STDNAME column only from the STUDENTS table we specify STDNAME instead of (*) for ALL. In other words, we want to display the column with student names only.
SQL Clause: WHERE
The WHERE clause is used to specify a criteria that is used to filter records search. For example, if we want to select all students from Nairobi city we can use the following SQL statement
SELECT * FROM APP.STUDENTS WHERE CITY = 'Nairobi'
Notice that the clause WHERE is followed by an equal sign (=) and then the value we are looking for surrounded by single quotes, in this case the value is ‘Nairobi’ after the equal sign.
Other keywords that can be used together with WHERE clause instead of equal sign are LIKE and comparison operators (>,<,<=,>=,!=, <> etc.) LIKE keyword is used with search wildcards. For example, if we want to display all records where COURSE name begins with BA, we can use the following SQL statement:
SELECT * FROM APP.STUDENTS WHERE COURSE LIKE 'BA%'
The wildcard symbol % denotes "any characters."
Write and execute SQL statements to:-
1. Select all student names and their city
2. Select all student names that end with “hop”
3. Select all student names and address where the course begins with “BA”
4. Select all records where STDID is less than or equal to 2
As you can see, SQL is not really difficult to learn. Go ahead and discover as many SQL commands and statements as you can, this will help you to manipulated complex databases and tables. We’ll leave Structured Query Language at this point since we have already covered enough SQL commands for this tutorial. In the next lesson, we shall learn how to connect to a database in Java NetBeans programming.
- Arrays of string type in Java
In some cases in Java programming, you would need to store values of string type in an array like days of the week or Boolean (True or False) values. In this article, we shall learn how we can implement and work with arrays of string type in Java.
- How to search for a character or string in Java
Java programming language provides several methods of searching for a character or string in another string. In this particular article, we shall discuss the Java indexOf() method and how to use this method to search in a string variable.
- Working with GUI controls: Combo box
Java NetBeans has different Graphical User Interface (GUI) controls which can visually simplify programming and enhance the functioning and user-friendliness of the final application. This article explores different ways of manipulating Combo box.
- Programming In Java NetBeans - Table of Contents
Java is one of the most popular programming languages. This free programming in Java NetBeans tutorial provides step by step Java programming lessons covering on specialized Java programming topics aimed at providing the fundamental skills needed...