Java: Deleting a record from MySQL databbase

• Create a simple GUI using GridBagLayout
• Get input from the user to delete the corresponding record
• Delete a record from the database on pressing the button

We will delete a customer record from the database. User has to provide the customer account number in order to delete the customer. The program will contain a class for handling database transaction, one for creating the GUI and a driver program.

Here is the class that creates our GUI.

public class SimplePanel extends JFrame

{

JTextField accountNumber;

public SimplePanel()

{

setTitle("Delete record from Database");

accountNumber = new JTextField(10);

}

public void createAndDisplay()

{

JPanel panel = new JPanel(new GridBagLayout());

JPanel banner = new JPanel();

banner.add(new JLabel("Delete record from the database"), BorderLayout.CENTER);

banner.setBorder(BorderFactory.createLineBorder(Color.BLACK));

GridBagConstraints c = new GridBagConstraints();

c.gridx = 0;

c.gridy = 0;

c.gridwidth = 2;

panel.add(banner, c);

JLabel label = new JLabel("Customer Account Number");

c.gridx = 0;

c.gridy = 2;

c.gridwidth = 1;

panel.add(label, c);

c.gridx = 1;

c.gridy = 2;

c.gridwidth = 1;

panel.add(accountNumber, c);

JButton deleteBtn = new JButton("Delete");

deleteBtn.addActionListener(new ActionListener()

{

@Override

public void actionPerformed(ActionEvent arg0)

{

CustomerHandler handler = new CustomerHandler();

try

{

handler.deleteCustomer(Integer.valueOf(accountNumber.getText()));

}

catch (Exception e)

{

e.printStackTrace(); // handle the exception in a better way. this is just an example

}

}

});

c.gridx = 0;

c.gridy = 3;

panel.add(deleteBtn, c);

this.getContentPane().add(panel);

pack();

setVisible(true);

}

}

Here is the output window from the above program

For the DBConnection class have a look at this post. And the handler that will actually delete the record from the database

public class CustomerHandler

{

public void deleteCustomer(int customerAccouontNumer) throws Exception

{

DBConnection db = new DBConnection();

Connection conn = db.getConnection();

Statement st = null;

              // First check if the conn is null then throw an exception or handle in some other way

try

{

st = conn.createStatement();

st.execute("Delete from Customer where account_number = " + customerAccouontNumer + ";");

}

catch (Exception e1)

{

throw new Exception("Unable to delete customer!" + e1.getMessage());

}

finally

{

// close the connection and the statement

}

}

And the driver program

public class Driver

{

public static void main(String[] args)

{

javax.swing.SwingUtilities.invokeLater(new Runnable()

{

public void run()

{

SimplePanel p = new SimplePanel();

p.createAndDisplay();

}

});

}

}

Remember to do the imports. 

Java: Search a record from a database table

In this example we will search a customer's record from a database table named 'CUSTOMER'. The database used is MySQL and the code is written in Eclipse IDE.

To search the data from the table a database connection is needed. To get the connection to the database follow the instructions and use the code in this post -> Connecting Java and MySQL in Eclipse

Once the DBConnection  class is there we will make a CustomerHandler class that will query the database and return the searched record.

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

public class CustomerHandler

{

public CustomerHandler()

{

}

public Customer searchCustomer(int customerAccountNumber) throws Exception
{
Customer c = null;
DBConnection db = new DBConnection();
Connection conn = db.getConnection();
PreparedStatement st = null;

if (conn == null)
{
throw new Exception("Unable to connect to the database. conection = null!");
}
try
{
st = conn.prepareStatement("Select * from Customer where account_number = ?");
st.setInt(1, customerAccountNumber);
ResultSet rs = st.executeQuery();

if (rs.next())
{
c = new Customer();
c.setAccountNumber(rs.getInt("account_number"));
c.setDate(rs.getDate("date"));
                                c.setName(rs.getString("name"));
}
catch (Exception e1)
{
Logger.getGlobal().severe("Unable to retrieve customer data from the database: " + e1.getMessage());
System.out.println("SQLException: " + e1.getMessage());
e1.printStackTrace();
throw new Exception("Unable to retrieve customer data from the database!" + e1.getMessage());
}
finally
{
try
{
if (conn != null)
{
conn.close();
}
if (st != null)
{
st.close();
}
}
catch (SQLException e1)
{
Logger.getGlobal().severe("Error occured while closing the connection or statement: " + e1.getMessage());
System.out.println("SQLException: " + e1.getMessage());
e1.printStackTrace();
throw new SQLException("Error occured while closing the connection or statement. " + e1.getMessage());
}
}
return c;

}

}

To check the working of our class we will write a driver class 

public class Driver

{

public static void main(String[] args)

{

CustomerHandler custHandler = new CustomerHandler();

int customerAccountNumber = 1;

try

{

custHandler.searchCustomer(customerAccountNumber);

}

catch (Exception e)

{

e.printStackTrace();

}

        }

}

Finally the Customer class to save the results.

import java.util.Date;

public class Customer
{
private int accountNumber;
private Date date;
private String customerName;

public Customer()
{
}

public int getAccountNumber()
{
return accountNumber;
}

public void setAccountNumber(int accountNumber)
{
this.accountNumber = accountNumber;
}

public Date getDate()
{
return date;
}

public void setDate(Date date)
{
this.date = date;
}

public String getCustomerName()
{
return customerName;
}

public void setCustomerName(String customerName)
{
this.customerName = customerName;
}

}

MySQL: Add multiple columns to a table

How to add a column to an existing MySQL table. For example a table is created using the following query:

CREATE TABLE CUSTOMER (
account_number int  NOT NULL PRIMARY KEY,
date DATE,
name VARCHAR(20) NOT NULL,
address VARCHAR(60) NOT NULL,
advance INT,
nic_number VARCHAR(20)
);

Now execute the explain customer query:

The table has to be altered to include telephone, connection_type and connection_fee fields.


ALTER TABLE customer
    ADD COLUMN telephone int,
    ADD COLUMN connection_type VARCHAR(15),
    ADD COLUMN connection_fee int;

Now re-executing the explain customer query yields:

Similarly in order to add one column following query will work:

ALTER TABLE customer ADD COLUMN telephone int;

It can be mentioned that where this column should be added. If a new column need to be added before connection_fee and after connection_type, it can be mentioned using after keyword.

ALTER TABLE customer ADD COLUMN newField int AFTER connection_type;

MySQL create table query and Example

Database information is saved in a table. In order to create a table execute following query:

mysql> CREATE TABLE TABLE_NAME (
FIELD1 INT NOT NULL PRIMARY KEY,
FIELD2 CHAR2(25),
FIELD3 INT(9));

Explanation:

• The INT in the field1 shows that this field will contain only integers
• NOT NULL makes sure that this field is not left empty
• PRIMARY KEY keyword in the query makes the FIELD1 as the primary key of the table. The values in the primary key field should be unique.
• The CHAR(25) and INT(9) commands allow character type data in FIELD2 and integer type data in FIELD3 respectively. The numbers in the parenthesis are the maxium number of ints or chars in the fields.

Example:

CREATE TABLE CUSTOMER (
account_number int  NOT NULL PRIMARY KEY,
date DATE,
name VARCHAR(20) NOT NULL,
address VARCHAR(60) NOT NULL,
advance INT,
nic_number VARCHAR(20)
);

This query creates a table named "customer" with the primary key "account_number" of type int.
Other fields created in the table are:

•  'date' of data type date, 
• "name" field of datatype varchar with a size 20 characters max. It also declares that name cannot be null
• "address" field of datatype varchar with a size 60 characters max. It also declares that address cannot be null
• "advance" field of datatype int 
• "nice_number" field of datatype varchar with a size 20 characters max

In order to make sure that the table is created and has everything right, check the existing table in the database by

mysql>show tables;

mysql>explain customer;

MySQL select or use a database query

From the mysql command line execute the following query:

mysql> use databasename;

for example to use a database "test" type the query;

mysql> use test;

in order to check if your selected database is in use, execute the query:

mysql> select database();

we have to use the database() function.

MySQL Create Database Query Example

Go to Start Menu and find and run mysql:

on the mysql prompt first check if a database with the same name already exit.

• mysql> SHOW DATABASES;

If your database is not in the provided list then run the following query to create a database:

CREATE DATABASE <DATABASENAME>;

Replace the  <DATABASENAME> with your database name.

For example to create a database with the name test:

• mysql> CREATE DATABASE test;

In order to check if your database is created then run the show databases query again.

• Commands do not have to be entered in the upper case.
• All MySQL queries must end with the semicolon ";"

Java: Insert values into table using prepared statement Derby

Following method adds a record to the database table:
  
public void addPerson(Person p) {
        if (p == null || p.getName().equals("")) {
            return;
        }

        String name = p.getName();
        String address = p.getAddress();
        String phone = p.getPhone();

        Connection con = null;
        try {
            con = getConnection();
            if (con != null) {

                String sql = "INSERT INTO APP.ADDRESS_BOOK("
                        + "NAME,"
                        + "ADDRESS,"
                        + "PHONE) "
                        + "VALUES(?,?,?)";

                PreparedStatement pStmt = con.prepareStatement(sql);

                // Set the values
                pStmt.setString(1, name);
                pStmt.setString(2, address);
                pStmt.setString(3, phone);

                // Insert
                pStmt.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(PersonDAO.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex.getMessage());
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(PersonDAO.class.getName()).log(Level.SEVERE, null, ex);
                    System.out.println(ex.getMessage());
                }
            }
        }
    }

Connection String for different Databases

A very good link:

http://www.connectionstrings.com/