Derby Example

Create a new Maven project with the command below from the terminal:

mvn archetype:generate -DgroupId=com.mbcc.tutorial -DartifactId=my-app -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.4 -DinteractiveMode=false

Add the below dependency to your pom.xml file:

    <dependency>
		<groupId>org.apache.derby</groupId>
		<artifactId>derby</artifactId>
		<version>10.14.2.0</version>
	</dependency>

Now, add the EmailDTO class in the com.mbcc.tutorial package:

package com.mbcc.tutorial;

public class EmailDTO {

	private String subject;
	private String emailText;
	private String recipientCategory;
	private String testerEmail;

	public EmailDTO(String subject, String emailText, String recipientCategory, String testerEmail) {

		if (subject == null)
			throw new IllegalArgumentException("subject cannot be null");

		this.subject = subject;
		
		if (emailText == null)
			throw new IllegalArgumentException("emailText cannot be null");
		
		this.emailText = emailText;
		

		if (recipientCategory == null)
			throw new IllegalArgumentException("Category cannot be null");

		this.recipientCategory = recipientCategory;
		
		this.testerEmail = testerEmail;
	}

	public String getSubject() {
		return subject;
	}

	public String getEmailText() {
		return emailText;
	}

	public String getRecipientCategory() {
		return recipientCategory;
	}

	public String getTesterEmail() {
		return testerEmail;
	}

	@Override
	public String toString() {
		return "EmailDTO [subject=" + subject + ", emailText=" + emailText + ", recipientCategory=" + recipientCategory
				+ ", testerEmail=" + testerEmail + "]";
	}

}

Next, add the DerbyDao class in the com.mbcc.tutorial package:

package com.mbcc.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class DerbyDao {

	/**
	 * For DerbyDB, you might have to run Tomcat with this VM setting for Windows
	 * -Dderby.system.home=C:\derby 
	 * as by default, Derby seems to create files in the restricted system32 folder.
	 */

	public boolean insertEmail(EmailDTO email) {

		Connection conn = null;
		Statement stmt = null;

		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		try {
			conn = DriverManager.getConnection("jdbc:derby:mailer;create=true");
			stmt = conn.createStatement();

			String dbString = "insert into Email (email_text, recipient_category, subject) values ('";
			dbString += email.getEmailText();
			dbString += "', '";
			dbString += email.getRecipientCategory();
			dbString += "', '";
			dbString += email.getSubject();
			dbString += "')";

			stmt.executeUpdate(dbString);

			return true;

		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			ex.printStackTrace();
			return false;

		} finally {
			if (stmt != null)
				try {
					stmt.close();

					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	public ArrayList<EmailDTO> getEmails() {

		Connection conn = null;
		Statement stmt = null;

		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		try {
			conn = DriverManager
					.getConnection("jdbc:derby:mailer;create=true;");

			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery("SELECT * FROM Email");

			ArrayList<EmailDTO> results = new ArrayList<EmailDTO>();

			while (rs.next()) {

				String emailText = rs.getString("email_text");
				String recipientCategory = rs.getString("recipient_category");
				String subject = rs.getString("subject");
				String testerEmail = rs.getString("tester_email");
				EmailDTO email = new EmailDTO(subject, emailText, recipientCategory, testerEmail);
				results.add(email);
			}
			return results;

		} catch (Exception ex) {
			System.out.println(ex);
		}

		return null;

	}

	/** 
	 * Call this createTable only once.
	 */

	public void createTable() throws SQLException {

		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		Connection conn = DriverManager.getConnection("jdbc:derby:mailer;create=true");
 
		Statement stmt = conn.createStatement();
		try {
			stmt.execute("drop table email");
		} catch (Exception e) {
			// Ignore exception if table doesn't exist
		}

		String emailTableSql = "CREATE TABLE email (" + "  id int NOT NULL GENERATED ALWAYS AS IDENTITY,"
				+ "  email_text clob NOT NULL," + "  recipient_category varchar(255)  NOT NULL,"
				+ "  subject varchar(255)  NOT NULL," + "  tester_email varchar(255) DEFAULT NULL,"
				+ "  email_date timestamp DEFAULT NULL," + "  PRIMARY KEY (id)" + ")";

		
		stmt = conn.createStatement();
		stmt.execute(emailTableSql);

	}

	public static void main(String[] args) throws SQLException {

		DerbyDao dao = new DerbyDao();
		dao.createTable();
        
	}
}

Note on Security: The insertEmail method above uses string concatenation to build a SQL query. This practice is highly vulnerable to SQL Injection attacks. In a real-world application, you should always use PreparedStatement to safely handle user-provided data.

Exercise

• Write test cases for all the methods of this class (you can exclude the createTable method).
• Ensure all the edge cases are covered with a test case.
• Clean up both the implementation and test cases by pulling all the repeating code blocks into separate methods.
• Refactor to use try-with-resources for managing database connections.