-
Notifications
You must be signed in to change notification settings - Fork 0
Databases and Manipulating Data
Now that we have a database to use we can now start to manipulate data in it. In SQL there are multiple ways to manipulate data. You can insert records, delete records and alter records.
A table is how all of the information in a database is stored so it is fundamental that you know how to use them. In Java to do anything in SQL, you have to use statements. Statements are variables that tell the compiler to do a certain thing such as create a table or insert a record. Statements also proceed with the keyword statement. Next, you have to give the statement some information to input into the table. This information will come in a string format. Finally, once we have the statement and the information we need to tell Java to execute it all. You can do this with the executeUpdate() method.
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "password");
System.out.println("Connected to the PostgreSQL server successfully.");
stmt = conn.createStatement();
String sql = "CREATE TABLE COMPANY " +
"(ID INT PRIMARY KEY NOT NULL," +
" NAME TEXT NOT NULL, " +
" AGE INT NOT NULL, " +
" ADDRESS CHAR(50), " +
" SALARY REAL)";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
System.out.println("Table created successfully");
}
Output
Connected to the PostgreSQL server successfully.
Table created successfully
Above you can see that a statement variable was created. Then the statement is given a string to input into the table. In the statement, the string is actually composed of the fields that the table will be composed of. You can also see that you also set what field you want to be the primary key. In the string statement, the not null which means that the field needs to have data in it. Lastly, the data type of field is given. SQL has different data types from java. Below is a table of some of the data types for SQL.
| Data Type | Meaning |
|---|---|
| char | A string value of a certain length |
| varchar | A string value of various lengths |
| integer | An integer numeric value |
| double | A double numeric value |
Inserting a record into a table is similar to creating a table. You still use the statement and the string variables, however, the value of the string will change depending on the record. First, you will use the keyword INSERT to tell Java that this is going to be inserted. Following the INSERT statement, you will specify what columns data will be inserted into. Lastly, you will specify what data will go into those columns. The string will look like this:
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
When you are doing this part it is important that specify the data in the same order that you specify the columns. In the above example, if you were to swap '1' and 'Paul' places then the insert will not work. This is because you cannot put a string like a name Paul into a number field like ID and vice versa.
When you SELECT a record from a table means that you are viewing a record and its attributes. Using a select statement is different from insert records or creating a table. Since data is only read and not set in any way the same statements are not used. For a select statement, you will use a while loop to read through each. A new variable that is included is the ResultSet variable. This ResultSet variable is used to run the select statement and get the information from the various fields. This while loop uses the next() method to tell if there are more records to read. Once the while loop reaches the end of the table then the while loop ends. The loops for a select statement for the table in this section would be:
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
Output
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
As you can see above the select statement returns the column name along with the value in that column. If I was to insert another record and run the same select statement then there would be two items returned instead of one.
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
Output
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
As with any records, they may change over time. If you have a database that holds product information in a store as time passes various values might change in the database, such as prices changing, more products being offered or the name of a product being changed. The UPDATE operation follows the same format of the INSERT statement.
stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 5000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();
As you can see the UPDATE statement has an extra commit() statement. This commit() statement tells the compiler to push those changes through to the database. Now if we run a select statement again we can see that the record has changed.
Output
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
In a database, some records will not last forever. Records may become out of date or not be needed in the database anymore. To handle this we can remove the records from the table using a DELETE statement.
Let's test this on the database we created in this section. First, let's add a third record.
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
+ "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
Then let's run a SELECT statement to make sure it is there.
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
Then let's run the DELETE statement to delete the middle record.
stmt = c.createStatement();
String sql = "DELETE from COMPANY where ID = 2;";
stmt.executeUpdate(sql);
c.commit();
Output
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0