What is prompt engineering?

Prompt engineering is a technique used to train large language models (LLMs) by supplying these models with specific prompts to improve the accuracy of responses returned. This engineering technique relies heavily on deliberate actions such as providing detailed instructions, context, and input data to the language model.

Prompting techniques

Several prompting techniques exist, such as the zero-shot, few-shot, chain-of-thought, etc. Each of these techniques has unique features that set them apart.

Zero-shot technique

The zero-shot technique responds to prompts containing no context by relying on knowledge acquired during the training of related tasks. For example, the prompt below is supplied to ChatGPT:

—

Classify the language into compiled or interpreted

Language: Python

Interpreted

—

In the prompt above, no further context on what languages are or what compiled or interpreted means in this scenario, yet the model was able to return the correct answer. LLMs are trained on large datasets, allowing them to analyze and match prompts.

However, this is only sometimes the case for every prompt, as responses from prompts powered by the zero-shot technique may need more detail. In such cases, the few-shot technique should be applied.

Few-shot technique

In this technique, the prompt engineer supplies a prompt with more context to aid the model in returning a proper response. An example of the few-shot technique is providing the model with a sample summarized paragraph before providing an essay to summarize. For example, we provided the summary for this blog post 3 Design Skills You Should Look For In Front-End Candidates to ChatGPT and asked it to summarize the article. The summarized article returned is:

The article emphasizes the importance of design skills for front-end engineers and provides recommendations on how to find engineers who can create visually appealing and user-friendly applications. It highlights three crucial skill sets to prioritize during interviews:

1. Knowledge of semantic layout: Understanding and using semantic HTML elements to enhance accessibility and facilitate code maintenance.

2. Attention to detail: Focusing on small design elements that contribute to a friendly and accessible user interface.

3. Proficiency in using design libraries: Being adept at utilizing popular design libraries like Bootstrap, Material UI, and Tailwind CSS to avoid reinventing existing features.

The essay also acknowledges that while these skills are essential, candidates without all three can still be considered based on their other strengths, adjacent skills, ability to learn quickly, and interest in improving their design skills. Overall, the essay emphasizes the significance of design in front-end development and encourages a thorough assessment of candidates' design abilities during the hiring process.

Chain-of-thought technique

As the name implies, in this technique, the prompt engineer supplies instructions and provides more context around a prompt, creating a pattern for the model to analyze before responding. The chain-of-thought technique requires the prompt engineer to walk through the query. An example is providing examples of arithmetic operations before asking the model for an answer to an arithmetic operation. An example of this prompting technique is:

Provide me some detailed questions containing equal parts of fix the code and theoretical questions and answers for a junior Golang developer. These questions should cover the basics of Golang. 

Use the format below: 

Question: 

Answer:

Here’s an example:
Question: What is the difference between a slice and an array?

Answer: Arrays have a fixed size and are less flexible, while slices provide dynamic sizing and are more versatile for working with collections of elements.

There are other techniques a prompt engineer employs for accurate or near-accurate answers from AI models.

Applications of prompt engineering

Prompt engineering finds its application in areas such as generating data and generating code and technical interviews.

The most popular use case for prompt engineering is generating data such as essays, answers to questions, etc. This application of prompt engineering plays a crucial role in the AI industry as language models can be trained to generate texts based on the inputs given. For example, GPT can write the Zen of Python:

The Zen of Python is a collection of guiding principles and aphorisms that serve as a philosophy for writing computer programs in the Python language. It encapsulates the core values and best practices that Python developers strive to adhere to. Here is the Zen of Python:

Beautiful is better than ugly.

Explicit is better than implicit.

Simple is better than complex.

Complex is better than complicated.

Flat is better than nested.

Sparse is better than dense.

Readability counts.

Special cases aren't special enough to break the rules.

Although practicality beats purity.

Errors should never pass silently.

Unless explicitly silenced.

In the face of ambiguity, refuse the temptation to guess.

There should be one—and preferably only one—obvious way to do it.

Although that way may not be obvious at first unless you're Dutch.

Now is better than never.

Although never is often better than *right* now.

If the implementation is hard to explain, it's a bad idea.

If the implementation is easy to explain, it may be a good idea.

Namespaces are one honking great idea—let's do more of those!

These guiding principles encourage Python developers to prioritize code readability, simplicity, and maintainability. The Zen of Python emphasizes the importance of clear and explicit code over clever or convoluted solutions. It promotes the idea that code should be easy to understand and follow, even at the expense of brevity. By adhering to these principles, Python developers can write code that is more efficient, easier to debug, and more enjoyable to work with.

ChatGPT’s response to my input adds an extra paragraph to tell us what the Zen of Python is about. 

Code generation is a close second as developers utilize the availability of LLMs to provide working code samples based on the context provided. Prompt engineering is crucial to achieving this task, as the language models require adequate context and instructions to return proper responses. For example, we can ask ChatGPT to generate a function that says my name:

package main

import "fmt"

func sayMyName(name string) {

    fmt.Println("Hello, my name is", name)

}

func main() {

    myName := "Coderpad"

    sayMyName(myName) // Prints out Hello, my name is Coderpad

}

Shortfalls

The significant shortfall of prompt engineering is the inaccuracy of some responses from language models. We must remember that these models are trained based on datasets and inputs supplied to them. As a result, we must review the responses from AI tools like ChatGPT and Bard.

Other shortfalls of prompt engineering are the time taken to refine a prompt to return accurate results and the limited knowledge base of the language model. For example, ChatGPT models are trained up to 2021, thereby being unable to provide accurate results on questions involving events from the year 2021 upwards. Luckily, the language models quickly learn how to return results from previous training via prompt engineering techniques. For example, the clause “Using previous criteria” on new prompts instructs the language model to return results based on the criteria defined in earlier prompts.

Conclusion

Prompt engineering plays a crucial role in improving the quality of responses from AI models such as ChatGPT and Bard. Prompt engineers can train language models to generate precise and relevant responses by providing more context to prompts. This can be achieved via the various techniques mentioned in this article.

The applications of prompt engineering are diverse, with data generation and code generation being prominent examples. Prompt engineering enables the generation of essays, answers to questions, and other text-based content, providing valuable assistance in various domains. Moreover, developers can utilize prompt engineering to generate functional code snippets based on specific requirements and context.

However, it is important to acknowledge the limitations of prompt engineering. Language models are not infallible and may occasionally provide inaccurate responses. Therefore, it is crucial to review and validate the outputs generated by AI tools to ensure their reliability.

In this post, we review how to use React’s useCallback() hook. But for you to comfortably follow this guide, we first need to define referential equality and callback functions.

What Is Referential Equality?

Referential equality compares two variables pointing to the same object in memory. This is determined using the strict equality === operator in JavaScript.

On the other hand, structural equality is the comparison of two objects to see if they have the same value. This means that we consider two objects equal if they have the same properties and values, even if they are stored in different places in memory. This is determined using the loose equality == operator.

For example, the following two objects are equal in structure but not in reference:

const person = { name: "Doe" };

const anotherPerson = { name: "Doe" };

person === anotherPerson; // false

Structural equality can only be true when both objects have the same content or values. Referential equality is more flexible—it only requires that both variables point to the exact memory location, even if they don’t have all the same content.

What Is a Callback Function in JavaScript?

A callback function is a function passed as an argument of another function. JavaScript executes the callback after the first JavaScript function executes. For example, let’s say you have a function that retrieves some data from a remote server. This operation could take some time to complete, and you don’t want your program to block or freeze while waiting for the data to be returned. Here, you could use a callback function to specify what should happen once you’ve retrieved the data. The callback function would be executed after the data has been successfully returned, allowing you to handle it and continue with the rest of your program.

import React, { useState, useEffect } from 'react';

function MyComponent(props) {

  const [data, setData] = useState(null);

  useEffect(() => {

    // Send a GET request to the server to retrieve the data

    fetch('/api/data')

      .then(response => response.json())

      .then(data => {

        setData(data);

        if (props.onDataLoaded) {

          // Invoke the callback function passed as a prop

          props.onDataLoaded(data);

        }

      });

  }, []);

  return (

    <div>

      {data ? <p>{data}</p> : <p>Loading data...</p>}

    </div>

  );

}

We often use callback functions to handle asynchronous events. For example, click events. A primary function could pass another function that handles a button click event as an argument. When you click the button, you trigger the callback function, which handles the click event.

What Is a useCallback() Hook?

A react useCallback hook is a callback that takes the components you want to optimize and a callback variable. JavaScript memoizes the variable for you and creates it on each render to remain the same. This eliminates the need to recalculate values unnecessarily.

The useCallback hook takes two arguments: a memoization function and an array of dependencies. When elements in the dependency array change, the memoized function is recreated.

You can use useCallback to control when things happen. One useful case is preventing idempotent mutations, such as rendering a component more than once in the same request.

How to Use useCallback() Hook

You can use the useCallback hook to memoize a function so that it is only recreated if one of the dependencies has changed. This is useful if you have a performance-intensive function that you call often. See the example below of how to use the useCallback hook with a functional component:

import {useState, useEffect} from 'react'


function App(){
    const [word,setWord]=useState("doe")
    const say = ()=>console.log(`Your word is: ${word})

    useEffect(()=>{
        say()
    },[say])
    return <div>Welcome!</div>
}

In this example, the useEffect hook takes the say function as a dependency. This means that you should only call useEffect when the function changes. However, since react uses referential equality checks, the say function will evaluate to true upon every rerender, even when there’s no change. As such, the useEffect callback gets invoked on every render. This is not optimal for performance, and now we have to find a way to fix this.

One approach is to move the function to the useEffect block. This would solve the problem, but it’s not optimal because you couldn’t use that function anywhere else.

import {useState, useEffect}from 'react' 

function App({

    const [word,setWord]=useState("doe")

    const say = ()=> console.log(`Your word is: ${word}`)
    useEffect(()=>{ say() },
        [say]
    ) 
    return <div>Welcome!</div> 
}

The other option is to use the useCallback hook. To do this, wrap it around your say function. You should note that the useCallback function, like useEffect, takes a dependency as a second value. If your function accepts variables, you’ll pass them in the array; otherwise, you can leave it blank. For our case, since the function depends on the word, we’ll pass it.

import {useState, useEffect,useCallback} from 'react'

function App(){

    const [word,setWord]=useState("doe")

    const say = useCallback(()=>console.log(`Your word is: ${word}`),[word])

    useEffect(()=>{

        say() 

    },[say]) 

    return <div>Welcome!</div> 
}

Best Practices to Follow When Using useCallback() Hook

1. Only use useCallback when you actually need to memoize a function. Memoization can improve the performance of your application, but it comes at a cost in terms of added complexity and potential bugs. So, only use useCallback if you have a specific need for it.
2. Pass a callback to useCallback as a reference, rather than defining it inline. This allows useCallback to track the dependencies of the callback and determine when it needs to be recreated.
3. Be aware of your callback’s dependencies. When you pass a callback to useCallback, you also need to pass a list of its dependencies as the second argument. Ensure this list is accurate and up-to-date so that the callback is only recreated when necessary.
4. Avoid passing expensive functions to useCallback. Because useCallback will only recreate the function when one of its dependencies has changed, it’s important to avoid passing expensive functions that may need to execute frequently. This can cause performance issues, as the function must be recreated and re-executed on every render.

When Should You Avoid Using useCallback() Hook?

You should not use the useCallback hook if you have no dependencies in your callback function. For example, if you have a callback function that only uses a prop from the parent component, you don’t need to use it.

const MyComponent = props => {

    const handleClick = () => {

    // write handleclick logic  
    };

    return (

        <div>

            <Button onClick={handleClick} />

        </div>

  );

};

In the above example, the handleClick function only uses handle click logic, which is passed in from the parent component. Since there are no dependencies in the handleClick function, you don’t need to use the useCallback hook.

React’s useCallback() Hook: Final Thoughts

This guide showed you how to use React useCallback to improve performance and avoid re-rendering issues. UseCallback allows you to memoize functions that are only recreated if one dependency or state has changed. This can be a useful optimization technique, particularly when working with expensive functions. However, only use it when necessary, and be aware that useCallback will prevent your function from garbage collection if the function is not being used. useCallback is a powerful tool that can improve performance in React applications but it should be used judiciously.

This post was written by Mercy Kibet. Mercy is a full-stack developer with a knack for learning and writing about new and intriguing tech stacks.

MySQL is an example of a relational database. It is open source, which means everyone may use it for free. MySQL, also known as My Structured Query Language, is one of the oldest database management systems (DBMSs) and uses row-and-column tables to store data.

On the other hand, we have PostgreSQL. It is a compelling and open-source DBMS introduced on January 29, 1996. PostgreSQL works on widely used operating systems, such as Linux, Unix, Windows, and Mac OS.

In this article, we will discuss the features of both databases and outline their respective advantages and disadvantages. Afterward, we will compare both databases against some critical factors. By the end of this article, you will understand which database is better for your use case.

MySQL Database

Features of MySQL

• MySQL stores data and information and efficiently manages memory using CRUD operations. Storing information is the primary feature of MySQL.
• MySQL is an open-source relational DBMS, which means anyone can download it from the official website and modify the code according to their needs.
• MySQL supports General Public License (GPL) guidelines, which state what can be done with the application and what is prohibited.
• It supports several data types, such as INT, CHAR, VARCHAR, DOUBLE, etc.
• It supports client-server architecture. The server often comes with client utility programs as well.

Advantages

• Scalability: MySQL handles a vast amount of data. Furthermore, owing to multithreading support, it structures this data in almost 50 million rows. The default size of a file in MySQL is 4 GB, but this can be extended to 8 TB.
• Speed: It is considered one of the fastest DBMSs.
• Compatibility: It is compatible with most operating systems, such as Windows, Linux, and Unix.
• High flexibility: It supports widely used embedded applications. This is what makes MySQL a highly flexible DBMS.
• Easy availability: MySQL is open-source and free-to-use software. One does not need to pay anything to use and implement this database to their code.
• Secured: It comprises various highly embedded algorithms, which enhances this DBMS’s trustworthiness regarding data security.
• Efficiency in memory management: MySQL manages data memory so well that it only needs low-grade maintenance for memory management and uses what it needs efficiently.
• Better productivity: It uses tools like views, triggers, and joins that allow the development team to achieve higher performance with better productivity.
• Easy UI support: MySQL Workbench is a database tool that fully supports a graphical user interface. It consists of data development, management, and many other administrative agencies that make it easy for users to understand the data and the database’s internal workflow.
• Platform independency: Because it is compatible with most operating systems, it is inherently platform-independent.

Disadvantages

• MySQL is usually preferred for small databases, as large database sizes are not supported.
• MySQL does not support procedures like role, store, and commit.
• MySQL does not support more than 4,096 columns in a single table.
• Some of its stored procedures are not developed for high-end business logic.
• MySQL does not handle data transactions well, which can introduce big issues like memory leakage, slow performance, or even data corruption.

PostgreSQL Database

Features of PostgreSQL

• PostgreSQL is also an open-source database system.
• It combines the object-relational architecture of DBMSs.
• It supports most SQL standards, such as views, triggers, transactions, complex SQL queries, hot standby, and foreign keys.
• It also supports different data formats, such as images, videos, sounds, etc.
• It can be used to develop different products and services; additionally, built-in fault tolerance increases data integrity for any use case you are working on.

Advantages

• Low maintenance: PostgreSQL does not need much maintenance because it utilizes write-ahead logging. This means that PostgreSQL can replay changes when a system does not switch off correctly.
• Easy to use: It is so easy to use that new users do not need prior knowledge or training.
• Free to use: It is free-to-use software.
• Large community: It has extensive community support, so if any doubt or issue arises during usage or development, users can quickly check to see if there are solutions to their problem already published on the internet.
• Supports ACID: It supports ACID: atomicity, consistency, isolation, and durability. These four properties are essential when working on crucial and sensitive data.
• User-defined data types: PostgreSQL supports user-defined data types using commands like CREATE DOMAIN and CREATE TYPE.

Disadvantages

• The architecture of PostgreSQL separates all services from each other. This leads to more memory utilization.
• In terms of performance, PostgreSQL is not a sound DBMS.
• The speed of this DBMS is not very practical or attractive.
• It can become complex to work with.
• The installation process is not easy for beginners.

MySQL vs. PostgreSQL

Now, let’s see a comparison between MySQL and PostgreSQL based on the features of an ideal DBMS.

Which One is Best for You: PostgreSQL or MySQL?

After exploring both DBMSs, we can easily determine which one will better suit your needs. Because PostgreSQL uses more advanced technologies and tools and is more complex, it is the go-to option for complex and highly advanced data operations. PostgreSQL handles extraordinary situations more effectively and has fully developed tools, security, and other algorithms. So, when working with cloud technology, machine learning, and artificial intelligence, PostgreSQL is an excellent choice for use in these areas.

On the other hand, regular applications, such as e-commerce websites, desktop applications, logging applications, and data warehousing, have fewer requirements for data consistency. In these areas, MySQL would be a good fit. Because these aren’t complex, the amount of data management is also comparably lower.

Conclusion

To conclude, here are a few takeaways:

• We introduced MySQL and PostgreSQL and discussed many points of comparison.
• MySQL is a relational DBMS that uses CRUD operations to implement its working to the code.
• PostgreSQL is an object-relational database system that allows object-oriented data structuring by creating customized data types.
• MySQL is an open-source, easy-to-use DBMS, whereas PostgreSQL is a complex yet accessible and open-source DBMS.
• MySQL is suitable for normal development and desktop applications.
• PostgreSQL is meant for advanced data operations because it is more complex and requires users to possess more skills and training.

This post was written by Gourav Bais. Gourav is an applied machine learning engineer skilled in computer vision/deep learning pipeline development, creating machine learning models, retraining systems, and transforming data science prototypes to production-grade solutions.

If you want to learn more about PostgreSQL LIKE, you’ve come to the right place, since this post is all about this operator. By the end of the post, you’ll have learned:

• what PostgreSQL LIKE does, and why you’d want to use it
• how it differs from the LIKE operators in different database engines
• how it differs from the ILIKE operator

To paraphrase that famous movie, though, only theory and no practice make this a dull post. So, before wrapping up, we’ll walk you through several examples of the LIKE operator in practice.

Requirements

If you want to follow along with the practical portion of the post, keep in mind that the post assumes the following:

• there is a functional instance of PostgreSQL installed on your machine
• you can connect to the said instance through a client
• you have at least a bit of experience with the PostgreSQL database and the SQL language

Instead of actually installing PostgreSQL,  I recommend using the CoderPad MySQL Sandbox to quickly and easily get started writing SQL  as it’ll be your MySQL client for this article.

PostgreSQL LIKE: The Fundamentals

With the requirements out of the way, let’s start by covering some fundamentals about today’s topic.

What Is PostgreSQL LIKE?

PostgreSQL LIKE is PostgreSQL’s implementation of the LIKE operator from the SQL language. You use LIKE when you want to retrieve rows in which one or more of their textual columns match a given pattern. In the introduction, I gave an example: retrieving students whose last name starts with a given letter. So, let’s see a real query that does just that using PostgreSQL LIKE:

SELECT id, first_name, last_name, email FROM students WHERE last_name LIKE 'M%';

The query above retrieves some columns from the students table, but only the rows which match the condition: the value from the last_name column has to start with the letter “M”. There are some interesting points to notice from this first example:

• You use the LIKE operator in the WHERE clause of a query
• It goes on the same spot you’d put any comparison operator such as “==”, “>”, “<“, and so on
• The percentage character (%) is used as a wildcard to match any number of characters

What if you wanted only students whose first name ended with a “k”? Simple, just swap the letter and the wildcard:

SELECT id, first_name, last_name, email FROM students WHERE first_name LIKE '%k';

Another example: let’s match students whose email addresses contain the word “gmail”:

SELECT id, first_name, last_name, email FROM students WHERE email LIKE '%gmail%';

That’s right: if you want to match values that contain a given string, you surround the string with percentage signs (%).

What Is the Difference Between LIKE and ILIKE in Postgresql?

When reviewing PostgreSQL code in the wild, you might encounter queries that use the ILIKE operator instead of LIKE and get confused. The difference is that the LIKE operator is case sensitive—i.e. it distinguishes between lowercase and uppercase letters. On the other hand, the ILIKE operator is case insensitive—hence the i. It does not distinguish between upper and lowercase letters, and therefore you should use it when you don’t care about the case.

An important thing to keep in mind: the ILIKE operator doesn’t exist in ANSI SQL; instead, it’s a specific extension of PostgreSQL. That means that employing ILIKE makes your SQL code less portable, in case you have the need to change your database engine.

PostgreSQL Like: Practical Use Cases

Having covered the basics of PostgreSQL LIKE, let’s now see several usage examples. We’ll begin by preparing the database for the tests.

Preparing the Database

Using your preferred client, connect to your PostgreSQL instance, making sure there’s a database you can connect to for the tests.

Let’s start by creating a table and inserting some rows into it:

CREATE TABLE albums (
  id              SERIAL PRIMARY KEY,
  title           VARCHAR(250) NOT NULL,
  artist  		  VARCHAR(250) NOT NULL,
  release_year int NOT NULL);

INSERT INTO albums (title, artist, release_year) VALUES('Abbey Road', 'The Beatles', 1969);
INSERT INTO albums  (title, artist, release_year) VALUES('Construção', 'Chico Buarque', 1971);
INSERT INTO albums  (title, artist, release_year) VALUES('The Dark Side of the Moon', 'Pink Floyd', 1973);
INSERT INTO albums  (title, artist, release_year) VALUES('Reckless', 'Bryan Adams', 1984);
INSERT INTO albums  (title, artist, release_year) VALUES('Ryan Adams', 'Ryan Adams', 2014);

Matching a Single Character

As you’ve seen, the table now contains albums by both Bryan and Ryan Adams. That’s not by coincidence. First, use the following query to retrieve albums by the two singers:

SELECT * FROM albums WHERE artist LIKE '%yan%';

The query above matches all artists which contain the string “yan” anywhere in their name. Here’s what the result looks like to me:

 id |   title    |   artist    | release_year 
----+------------+-------------+--------------
  4 | Reckless   | Bryan Adams |         1984
  5 | Ryan Adams | Ryan Adams  |         2014
(2 rows)

The LIKE operator offers an additional wildcard, the underscore character (_) which matches a single character. That way, it’s possible to rewrite the query in such a way that only Ryan Adams is returned:

SELECT * FROM albums WHERE artist LIKE '_yan%';

And here, again, is the result:

 id |   title    |   artist   | release_year 
----+------------+------------+--------------
  5 | Ryan Adams | Ryan Adams |         2014
(1 row)

Negative Matches

When working with database tables, you often need to retrieve data that doesn’t match a given pattern. For that, you can use the NOT LIKE operator. For instance, let’s get only artists that don’t start with “The”:

SELECT * FROM albums WHERE artist NOT LIKE 'The%';

Since the only artist starting with “The” are The Beatles, they won’t be fetched by the query above.

Matching Without Case Sensitivity

As mentioned earlier, the ILIKE operator is a special PostgreSQL extension to LIKE that matches in a case-insensitive way. Let’s see that in practice. First, let’s use the LIKE operator to get all artists which contain an uppercase “A”:

SELECT * FROM albums WHERE artist LIKE '%A%';

Only “Bryan Adams” and “Ryan Adams” are returned, which makes sense:

 id |   title    |   artist    | release_year 
----+------------+-------------+--------------
  4 | Reckless   | Bryan Adams |         1984
  5 | Ryan Adams | Ryan Adams  |         2014
(2 rows)

Let’s now replace the LIKE operator with the ILIKE one and rerun the query:

SELECT * FROM albums WHERE artist ILIKE '%A%';

 id |   title    |    artist     | release_year 
----+------------+---------------+--------------
  1 | Abbey Road | The Beatles   |         1969
  2 | Construção | Chico Buarque |         1971
  4 | Reckless   | Bryan Adams   |         1984
  5 | Ryan Adams | Ryan Adams    |         2014
(4 rows)

Now, all artists are returned, except for Pink Floyd.

Escaping the WildCards

For this example, let’s update two rows:

UPDATE albums SET artist = 'The%Beatles' WHERE id = 1;

UPDATE albums SET artist = 'Chico_Buarque' WHERE id = 2;

Now, let’s say we want to retrieve albums from artists whose name contains a percentage sign or an underscore:

SELECT * FROM albums WHERE artist LIKE '%%%';

SELECT * FROM albums WHERE artist LIKE '%_%';

As you’ve probably imagined, both queries don’t work: they return all rows. Fortunately, there’s a way around this problem: it’s possible to escape a wildcard so we can use it as a normal character. You simply put a backslash before the offending character:

SELECT * FROM albums WHERE artist LIKE '%\%%';

And here’s the result:

 id |   title    |    artist     | release_year 
----+------------+---------------+--------------
  1 | Abbey Road | The Beatles   |         1969
(1 rows)

The same would work for the underscore character. But before wrapping up, let’s complicate things just a bit more—because, why not?

First, let’s update the “Abbey Road” row once more:

UPDATE albums SET artist = 'The\Beatles' WHERE id = 1;

Now, in order to use LIKE to retrieve a row containing the backslash character, you’d have to write this query:

SELECT * FROM albums WHERE artist LIKE '%\\%';

You have to escape the escape character. It’s not that terrible, but you might want to write a more readable version. If that’s the case, it’s possible to choose a different character as the escape character: just use the ESCAPE clause:

SELECT * FROM albums WHERE artist LIKE '%$\%' ESCAPE '$';

With the ESCAPE clause, you can choose a different character as the escape character. That way—at least in my opinion—the resulting query is more explicit in the way it escapes the wildcard.

Experiment with the LIKE operator in the sandbox below:

Conclusion: PostgreSQL LIKE: Learn It, Leverage It

In this post, you’ve learned about the LIKE operator in PostgreSQL: what it is, what’s used for, and how it works along several examples. As you’ve seen, the operator’s working is quite easy once you understand how the wildcards work. We’ve also walked you through some usage examples, like matching against a single character and performing negative matches.

Where should you go now?  For starters, continue to learn about pattern matching on PostgreSQL. Here are some topic suggestions for you:

• SIMILAR TO, a more recent clause
• the substring() function
• the regexp_like() function

Additionally, the site you’re on right now has great resources for people who want to learn more not only about PostgreSQL but databases in general. Here are some posts that are worth your time:

• How to Get Metadata from PostgreSQL System Catalogs
• Why & How to Use Window Functions to Aggregate Data in Postgres
• How to Interact with Databases using SQLAlchemy with PostgreSQL
• SQL Functions and Techniques Every Data Person Should Know

Thanks for reading, and until next time!

In the agile world, it’s especially important to test early and often, as changes are made frequently and new features are added constantly. By testing regularly, developers can catch bugs early and prevent them from becoming major issues later on. 

There are many tools and techniques available for testing. In this post, we’ll discuss how to use DataProvider with TestNG. 

Prerequisites

Before we get started with the guide, it’s important that you fulfill all the prerequisites. This will ensure that you have all the necessary tools and knowledge to complete the guide successfully. You’ll need 

• an understanding of the Java programming language,
• Java IDE such as NetBeans (or online IDE like Coderpad), and
• a basic understanding of testing frameworks like JUnit or NUnit.

Once you’ve fulfilled all of the prerequisites, you’re ready to get started with the guide. 

What Is TestNG?

TestNG is a testing framework for the Java programming language. The name “TestNG” is a play on the word “testing.” The initials “NG” stand for “next generation.” 

The goal of TestNG is to help developers perform a wider range of test cases—unit, functional, end-to-end, integration, etc.—with more ease and flexibility. 

TestNG is an open-source project hosted on GitHub and is released under the Apache software license. It was developed by Cédric Beust and was first released in July 2004. 

TestNG enables a user to create tests that are more maintainable and easier to understand. It also provides support for a variety of features, such as data-driven testing, parameterized testing, and multi-threaded testing. 

Understanding TestNG With an Example

Let’s check out an example of how to use TestNG to write a test case using the following code snippet: 

@Test

public void TestLogin(){

    WebDriverManager.chromedriver().setup();

    WebDriver driver = new ChromeDriver();

    driver.manage().window().maximize();

    String username = "username", password = "*****";

    driver.get("<https://demosite.executeautomation.com/>");

    driver.findElement(By.name("UserName")).sendKeys(username);

    driver.findElement(By.name("Password")).sendKeys(password);

    driver.findElement(By.name("Login")).submit();

    Assert.assertEquals("User Form", driver.findElement(By.tagName("h2")).getText());

    driver.quit();

}

In the above code snippet, we’re going to the URL https://demosite.executeautomation.com, which has a login page that accepts any username and password. 

There, we’ll assert user form text from the next page after a successful login. 

The goal of the above code is to understand how to use the @Test annotation from TestNG and write an automation test case using Selenium in Java. 

How to Perform Parameterization Using TestNG

To perform parameterization, we use the @Parameters({"parameter1", "parameter2", ..}) annotation. 

Test methods will read these parameters from XML files, as shown below: 

<?xml version="1.0" encoding="UTF-8"?>

<suite name="TestSuite">

    <parameter name="username" value="username" />

    <parameter name="password" value="*****" />

    <test name="TestLogin">

        <classes>

            <class name="DataProvider.TestNg" />

        </classes>

    </test>

</suite>

Here, the parameter’s name from the XML file should match the name we mentioned in the annotation in the test method above. 

@Test

@Parameters({"username", "password"})

public void TestLogin(String username, String password){

    WebDriverManager.chromedriver().setup();

    WebDriver driver = new ChromeDriver();

    driver.manage().window().maximize();

    driver.get("<https://demosite.executeautomation.com/>");

    driver.findElement(By.name("UserName")).sendKeys(username);

    driver.findElement(By.name("Password")).sendKeys(password);

    driver.findElement(By.name("Login")).submit();

    Assert.assertEquals("User Form", driver.findElement(By.tagName("h2")).getText());

    driver.quit();

}

The use of parameterization in TestNG gives you the power to perform data-driven testing more efficiently. 

What Is DataProvider in Java?

DataProvider is like a container that passes the data to our test methods so that our single test method can execute itself with multiple data sets. 

To make any method act as a DataProvider, we need to use this annotation— @DataProvider —which will return a 2D array of an object where columns are the arguments needed in one test execution and rows are the number of data passed in each execution. 

@Test(dataProvider="LoginDataProvider")

public void TestLogin(String username, String password){

    WebDriverManager.chromedriver().setup();

    WebDriver driver = new ChromeDriver();

    driver.manage().window().maximize();

    driver.get("<https://demosite.executeautomation.com/>");

    driver.findElement(By.name("UserName")).sendKeys(username);

    driver.findElement(By.name("Password")).sendKeys(password);

    driver.findElement(By.name("Login")).submit();

    Assert.assertEquals("User Form", driver.findElement(By.tagName("h2")).getText());

    driver.quit();

}

@DataProvider(name = "LoginDataProvider")

public Object[][] LoginData(){

return new Object[][] 

{

        { "abc", "***" },

        { "xyz", "***" },

        { "mno", "***" }

};

}

Now, when we run the test above, the same test will run the method TestLogin() three times as LoginData returns three rows of data. 

If you don’t specify any name while declaring the DataProvider method and simply use @DataProvider while calling it in the test, we need to use the method’s name as a data provider name. In the above case, it’ll be @Test(dataProvider="LoginData"). 

Also, if DataProvider is created in a different class, then we need to include the dataProviderClass argument: 

@Test(dataProvider="<name>",dataProviderClass=<dataProvider_ClassFileName>.class)

Here are a few samples: DataSets.java:

public class DataSets{

@DataProvider(name = "LoginDataProvider")

public Object[][] LoginData(){

    return new Object[][] {

        { "abc", "***" },

        { "xyz", "***" },

        { "mno", "***" }

    };

}

}

TestAuthorization.java 

public class TestAuthorization{

@Test(dataProvider="LoginDataProvider", dataProviderClass="DataSets.class")

public void TestLogin(String username, String password){

    WebDriverManager.chromedriver().setup();

    WebDriver driver = new ChromeDriver();

    driver.manage().window().maximize();

    driver.get("<https://demosite.executeautomation.com/>");

    driver.findElement(By.name("UserName")).sendKeys(username);

    driver.findElement(By.name("Password")).sendKeys(password);

    driver.findElement(By.name("Login")).submit();

    Assert.assertEquals("User Form", driver.findElement(By.tagName("h2")).getText());
    driver.quit();
    }

}

Why Use DataProvider in TestNG?

If we have to test the same test case for multiple data (username and password) without using DataProvider, it’ll look as shown below. 

@Test

public void TestLogin(){

    WebDriverManager.chromedriver().setup();

    driver = new ChromeDriver();

    driver.manage().window().maximize();

    Login("abc", "***");

    Login("mno", "***");

    Login("xyz", "***");

    Login("def", "***");

    Login("dod", "***");

   driver.quit();
}

public void Login(String username, String password){

    driver.get("<https://demosite.executeautomation.com/>");

    driver.findElement(By.name("UserName")).sendKeys(username);

    driver.findElement(By.name("Password")).sendKeys(password);

    driver.findElement(By.name("Login")).submit();

    Assert.assertEquals("User Form", driver.findElement(By.tagName("h2")).getText());

}

Conclusion

We hope you enjoyed reading about DataProvider in testing with examples. 

TestNG is a testing framework that you can use with any Java project. It’s used in Agile methodology to test any application’s functionality.  

DataProvider is a feature of the TestNG library that allows a developer to run the same suite of test cases with different data sets. It helps the developer to test the application with different values. You can use this to test the business logic of any application. 

We hope that you found this post informative and helpful. 

This post was written by Keshav Malik. Keshav is a full-time developer who loves to build and break stuff. He is constantly on the lookout for new and interesting technologies and enjoys working with a diverse set of technologies in his spare time. He +loves music and plays badminton whenever the opportunity presents itself.

What are Django migrations?

Django migrations are a way of handling the application of changes to a database schema.

Among others, here are some of the many changes you might want to make to your database schema: 

• create a new database table
• add new fields to database tables
• modify constraints in a database table

Django’s documentation explains Django migrations as analogous to a version control system (or VCS for short). It explains that you can use makemigrations to create individual migration files, similar to commits. Migration files contain changes you’ll make to your models. The migrate command implements those changes to your database. 

Purpose of migration

As you probably know, Django offers a way to manage databases without having to write raw Structured Query Language (SQL). The object-relational mapper (ORM) Django provides eliminates writing SQL statements, so you can interact with the database using Python code. In other words, you can create a database table by creating a simple model class. 

However, because SQL is the standard language for interacting with databases (i.e., storing, manipulating, and retrieving data), Django still uses SQL to manage the database. But Django turns the ORM codes and models them into valid SQL statements to simplify things for you. So, how does this model class in Python translate into or become a database schema change? Django migrations takes care of that. 

Now that you know why and when to perform Django migrations, let’s discuss how. 

How to run Django migrations

There are four main commands for handling migrations and database schemas in Django. 

• showmigrations: shows the list of all migrations in a project and their current status (i.e., changes applied or not).
• sqlmigrate: displays the SQL statements that will be executed for a specific migration.
• migrate: implements the changes contained in migration files and updates the database schemas accordingly.
• makemigrations: makes new migration files according to the modifications made to the models.

If you paid attention to the above commands, you noticed that two unique keywords persist: migrate and migrations. That’s because migrations require two phases (or steps). 

• In the first step, you make migrations (i.e., create migration files), as you can infer from the command’s name (makemigrations).
• In the second step, you apply the migrations (migrate).

In order to illustrate and demonstrate the Django migration workflow, let’s work with a Django project. 

Create a Django project and a new app called blog by running this command: 

python manage.py startapp blog

Step 1: Go to the blog app directory, and enter the following to create the Blog model in the models.py file. 

from django.db import models

from django.contrib.auth import get_user_model

User = get_user_model()

class Blog(models.Model):

""" Blog Model that represents Blog Table """

    title = models.CharField(max_length=100, unique=True)

    slug = models.SlugField(max_length=50, blank=True)

    author = models.ForeignKey(User, on_delete=models.CASCADE)

    description = models.CharField(max_length=1000)

    date_created = models.DateTimeField(auto_now_add=True)

    last_modified = models.DateTimeField(auto_now=True)

def __str__(self):

    return "self.title[:20]"

Step 2: Go to the command line interface (CLI for short), and in the root directory of the Django project folder, run this command. 

Note: Ensure that the manage.py file is present in your current working directory in order to avoid getting the No such file or directory error. 

$ python manage.py makemigrations

The image above shows the output of running the above command. 

Common Problem 1: Not adding the Django App in installed_apps in the Django project’s settings. 

The part labeled 1 in the image above is the output you’ll get when you run this command without adding the blog app as part of the installed apps. The output is “No changes detected” as no database fields or tables have been added or modified as far as Django is concerned. 

Fix for Common Problem 1: 

Remember to add the newly created app blog to the installed_apps list in the Django project’s settings. 

Also, in the image, the part labeled 2 is the output you get when you run this command after adding the blog app as part of the installed apps. In the output in the image above, you can see the name of the migration file created and its path. 

Overview of Migration File

Now that you know how to create a Django migration file, we’ll briefly explain the Django migration file. 

The migration file contains a set of statements that instruct Django on the necessary database schema changes. Django uses these instructions to generate SQL statements that make the schema changes. These migration files are usually stored in the app folder, which sits in the migrations folder. Moreover, the migration file is a Python file containing two important things. 

• Operations
• Dependencies

You need to pay attention to your Python code, but you also need to familiarize yourself with the migration file. To this end, you can open the migration file to see the list of operations and identify the create blog model operation in the list, as shown in the CLI output.  

You can override the name of the package that contains the migrations on a per-app basis. 

Remember that the database table corresponding to our model has not actually been created yet. As a matter of fact, you can check the db.sqlite3 file to confirm this. This makes sense because you haven’t yet run the migrate command. 

Run the command below actually to apply the schema changes to the database (i.e., to create a new blog database table). In other words, perform the operations defined in the migrations files. 

$ python manage.py migrate

You’ll see output similar to the output in the image below. 

Breakdown of the migrate command output: 

1. The apps you’re applying the migrations to
2. The migrations files list

The migrations files not highlighted are those that come default with Django. 

If you check the database at this point, you’ll see that several tables have been created, as shown in the image below. 

The tables not highlighted are those created by Django by default. 

Adding Dummy Data for Testing

Now that you’ve created your database tables, let’s add some data for testing. 

1. To simplify adding data, you can use the Django admin dashboard. But first, you need to register the blog model in the blog/admin.py file:

from django.contrib import admin

from .models import Blog

# Register your models here.

@admin.register(Blog)

class BlogAdmin(admin.ModelAdmin):

    list_display = ["id", "title", "author", "date_created"]

2.  Next, run this command to create a superuser that can access the admin dashboard:

$ python manage.py createsuperuser

3. Start the development server:

python manage.py runserver

4. Navigate to http://127.0.0.1:8000/admin, then log in.
5. Click on the Add button under Blogs and add some dummy data for testing.

Making Changes to Existing Models

Software requirements change from time to time, and some changes necessitate adding additional fields or changing existing fields. As a result, you’ll need to make changes to your models. Let’s see how you can propagate those changes to your database schema. 

Imagine that the new change we need to make requires you to store the number of views for each blog post. 

To store the number, you need to add a field to the existing Blog model. 

...

class Blog(models.Model):

""" Blog Model that represents Blog Table """

...

    no_of_views = models.PositiveIntegerField() # new field

Django Migrations Default Value

Of course, you need to perform the two-step migration workflow before the new field can appear in the database. 

1. Run the makemigration command.

The image above shows the output of running the makemigrations command. 

The message tells you to specify a default value for the newly added field in the existing rows of the database. 

You have two options: 

1. You can provide a one-off default for all existing rows.
2. You can abort and add a default value into the field as an argument.

For this example, let’s provide a one-off default value by entering 1 as the chosen option and then specifying 0 as the one-off default value. 

If you check the migration file created earlier, you’ll see the AddFeld operation and the default value 0 specified. 

Before applying the changes to the database schema, you can inspect the SQL statements for the migration by running the sqlmigrate command. 

Note: Unlike the two migrations commands you’ve used so far, sqlmigrate requires two arguments: app_label and migration_name. 

$ python manage.py sqlmigrate blog 0002_blog_no_of_views

The output should be similar to the one in the image below.

2. Run the migrate command. Afterward, you can view the new fields added and the default value specified for the existing data in the database.

Reversing Django Migration

From the previous section, you learned that Django keeps track of changes made to the database by storing a migration file. As a result, you can revert to the previous database schema. Think of this as resetting your codebase to a previous commit in case of a glitch or other issue. 

Now, let’s say you decided you no longer want to store the number of times a blog was viewed. In this case, you need to remove it, but you must remember that you added the no_of_views field separately after the model had already been created. As a result, all you need to do is to roll back to your initial migration, when the model was created (i.e., the first migration within the blog app). To do that, run this command: 

$ python manage.py migrate blog 0001_initial

If you check your database table, you’ll see that the no_of_views column was dropped even though: 

• The migration file for adding the no_of_views field still exists in the migrations folder, and
• The definition of the no_of_views field still exists as part of the blog model attributes in models.py.

What’s the implication? 

Remember the showmigrations command from the previous section? Run it here. 

As the image above shows, only 0002_blog_no_of_views looks odd (i.e., it has no times (X) symbol). 

The times (X) symbol indicates the migration files were applied to the database schema. 

Common Problem 2: Reversing migration without deleting the migration file. 

If you try to run the migrate command, Django applies the pending migration file (i.e., 0002_blog_no_of_views), even though you don’t want it to. 

Fix for Common Problem 2 

You need to delete the migration file that contains changes you don’t want. See the next section for how to do that! 

How to Delete a Django Migration?

Deleting a migration requires two steps: 

1. Delete the migration file associated with the change you no longer need.

$ rm blog/migrations/0002_blog_no_of_views.py

2. Change your model to match the latest migration file you created or whichever you want to keep.

Common Problem 3: Django won't apply migration error. 

If you try to run a migrate command without performing second step, you’ll get the error depicted in the image below. 

This happens because the migrate command scans and then compares the models to the versions currently contained in the migration file. If it detects changes that don’t have an associated migration file, the database won’t know how to handle the changes made to the model and you’ll get an error. This error occurs because the changes made to the models aren’t reflected in the migration files and hence, the database can’t keep track of the changes and apply them. 

Fix for Common Problem 3: 

Perform second step mentioned above by removing the entire no_of_views attribute in the Blog class. 

How to Make Fake Migrations in Django

As with all commands generally, the Django migration commands take extra arguments. Extra arguments allow you to add customizations and configurations to the migration. One of these extra arguments is fake, which allows you to do fake migrations. The --fake argument in Django migrations allows you to mark one or multiple migrations as already applied without actually running their SQL statements. This can be useful in cases where you want to see what changes a migration would make to your database, but you don’t want any actual changes to the database schema. 

Django Migration Best Practices

Best practices you should adhere to when working with Django migrations include the following. 

1. Run migration files consistently across all environments. Migration files are part of the codebase. As a result, you’d commit and distribute them as part of the codebase. It is essential to ensure you run the migration files consistently across all environments, such as development, staging, and production to avoid inconsistencies in the database schema.
2. Test your migrations thoroughly. Make sure to test your migrations on a test database before deploying them to production. This ensures that there are no issues with the migration that could cause data loss or other problems.
3. Use the –database option when applying migrations. This allows you to apply migrations to specific databases, which can be useful when working with multiple databases in a single project.
4. Never edit your migration files manually. Always use the `makemigrations` command to create new migrations.
5. Be mindful of the order of migrations. Apply migrations in the order of their dependence on each other.

Conclusion

Migrations are an indispensable part of Django. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects. Additionally, always strive to follow best practices. 

This post was written by Boluwatife Fayemi. Boluwatife is a Full Stack Web Developer, proficient in Javascript, Python, and their frameworks. His curiosity makes him explore and gain knowledge about a variety of topics. Boluwatife is passionate about teaching and writing, and this makes writing technical articles an enjoyable process for him.

In this post, you’ll learn how to write a parameterized test in JUnit, with plenty of examples. To extract the most value from the post, you should be a Java developer with some experience writing JUnit tests and know how to manage dependencies using Gradle or Maven. It should go without saying, but an installation of Java is essential.

Let’s dig in.

What is a JUnit parameterized test?

In JUnit, a parameterized test is a test method in which the data used in the test comes from parameters instead of being hardcoded in the method itself. There’s a special syntax—in the form of annotations—that allows you to pass a set of values to the test method. When JUnit runs the test, it will execute one test for each set of data passed to the method.

The separation of concerns that parameterized tests provide brings benefits such as:

• Simpler and more readable test code because instead of being hardcoded, many values inside the test are now well-named parameters
• Less test duplication because you can have a single method give the origin to many tests
• More test coverage because the friction to add a new set of data is way smaller than adding a whole new test method

With that out of the way, let’s get started.

Starting with the production code

For you to write tests, you need production code in place. So, start a new Java project using the tools you’re most comfortable with. Make sure to install JUnit. Then, create a class called FizzBuzzer with the following code:

public class FizzBuzzer {

    public String convert(int number) {

        if (number <= 0 || number > 1000)

            return "N/A";

        if (number % 15 == 0)

            return "FizzBuzz";

        if (number % 3 == 0)

            return "Fizz";

        if (number % 5 == 0)

            return "Buzz";

        return Integer.toString(number);

    }

}

Writing a few traditional tests

Let’s start by writing a few regular, non-parameterized tests. Under src/test/java, create a class called FizzBuzzerTest. Paste the following code into it:

import org.example.FizzBuzzer;

import org.junit.jupiter.api.Test;

import static org.junit.Assert.*;

public class FizzBuzzerTest {

    @Test

    public void convert_zeroResultInNonApplicableResponse() {

        var sut = new FizzBuzzer();

        assertEquals("N/A", sut.convert(0));

    }

    @Test

    public void convert_negativeNumbersResultInNonApplicableResponse() {

        var sut = new FizzBuzzer();

        assertEquals("N/A", sut.convert(-1));

    }

    @Test

    public void convert_10001ResultsInNonApplicableResponse() {

        var sut = new FizzBuzzer();

        assertEquals("N/A", sut.convert(1001));

    }

}

The tests above test the method’s sad path. They verify that passing zero, negative numbers, or numbers greater than 1000 result in “N/A” being returned as we expect.

Let’s now add more tests to verify the happy path of the method. We’ll start by showing that the method returns the number itself if it is not a multiple of three or five.

@Test

public void convert_1ResultsInOne() {
    var sut = new FizzBuzzer();

    assertEquals("1", sut.convert(1));
}

@Test

public void convert_2ResultsInTwo() {

    var sut = new FizzBuzzer();

    assertEquals("2", sut.convert(2));

}

After that, let’s verify the three results in “Fizz,” five in “Buzz,” and fifteen in “FizzBuzz”:

@Test

public void convert_3ResultsInFizz() {

    var sut = new FizzBuzzer();

    assertEquals("Fizz", sut.convert(3));

}

@Test

public void convert_5ResultsInBuzz() {

    var sut = new FizzBuzzer();

    assertEquals("Buzz", sut.convert(5));

}

@Test

public void convert_15ResultsInFizzBuzz() {

    var sut = new FizzBuzzer();

    assertEquals("FizzBuzz", sut.convert(15));

}

All tests pass, but are you really satisfied with them? I know I’m not. And that’s because the tests above doesn’t offer comprehensive coverage. Someone playing devil’s advocate could easily write a perverse implementation that passes that tests while not delivering the right answer in all scenarios.

Let’s add more tests covering multiples of three:

@Test

public void convert_6ResultsInFizz() {

    var sut = new FizzBuzzer();

    assertEquals("Fizz", sut.convert(6));

}

@Test

public void convert_33ResultsInFizz() {

    var sut = new FizzBuzzer();

    assertEquals("Fizz", sut.convert(33));

}

Then, the same for multiples of five:

@Test

public void convert_10ResultsInBuzz() {

    var sut = new FizzBuzzer();

    assertEquals("Buzz", sut.convert(10));

}

@Test

public void convert_35ResultsInBuzz() {

    var sut = new FizzBuzzer();

    assertEquals("Buzz", sut.convert(35));

}

And finally, the same for numbers that are multiples of both three and five—or, in other words, multiples of fifteen:

@Test

public void convert_30ResultsInFizzBuzz() {

    var sut = new FizzBuzzer();

    assertEquals("FizzBuzz", sut.convert(30));

}

@Test

public void convert_45ResultsInFizzBuzz() {

    var sut = new FizzBuzzer();

    assertEquals("FizzBuzz", sut.convert(45));

}

All tests pass, which means we’re ready for the next step.

How do you write a JUnit parameterized test?

As you’ve noticed, there’s a lot of repetition in the tests above. For instance, the first three tests, in which the result is “N/A,” are essentially the same. Now it’s at this point where many developers fall into the trap of using a loop to iterate over the input values (in an array, for instance) and doing the assertion in the loop’s body.

Don’t do that.

Unit tests should be as simple as possible. As a guideline, keep their cyclomatic complexity at one. Resist the urge to introduce loops or conventional branching inside tests, because that will increase the complexity of your test method and the likelihood it contains a bug.

The better course of action is to parameterize the test. Let’s do that for the first three methods by adding the following new test:

@Test

public void convert_invalidInputResultsInNonApplicableResponse(int input) {

    var sut = new FizzBuzzer();

    assertEquals("N/A", sut.convert(input));

}

The new test method receives an int as an argument, and passes it in turn to the sut.convert() method. But something is missing: the value has to come from somewhere. Let’s solve that in four steps:

• Add the junit-jupiter-params dependency to your project
• Add import org.junit.jupiter.params.provider.ValueSource; to the top of your class
• Replace the @Test annotation of the new method with the @ParameterizedTest one
• Add this new annotation to the method: @ValueSource(ints = {0, -1, 1001})

The @ValueSource annotation is what provides the values for the input parameter. For each value defined there, JUnit will execute one test. Now you can eliminate the three original tests that verified the “N/A” scenario.

Parameterizing the “happy path” tests

It’s now time to parameterize the happy path tests. First, let’s parameterize the multiples-of-three scenario:

@ParameterizedTest

@ValueSource(ints = {3, 6, 9, 12, 33, 36, 39, 42})

public void convert_multiplesOfThreeResultsInFizz(int input) {

    var sut = new FizzBuzzer();

    assertEquals("Fizz", sut.convert(input));

}

Notice that I added more input values than the ones I used before. Since it’s so easy, nothing is stopping me from adding as many values as I feel necessary.

Now, the same for the multiples of five:

@ParameterizedTest

@ValueSource(ints = {5, 10, 20, 25, 35, 40, 50 })

public void convert_multiplesOfFiveResultsInBuzz(int input) {

    var sut = new FizzBuzzer();

    assertEquals("Buzz", sut.convert(input));

}

And then, the multiples of fifteen:

@ParameterizedTest

@ValueSource(ints = {15, 30, 45, 60, 75, 90 })

public void convert_multiplesOfFifteenResultsInBuzz(int input) {

    var sut = new FizzBuzzer();

    assertEquals("FizzBuzz", sut.convert(input));

}

Now we need to cover the scenario of numbers that aren’t multiples of either three or five. In those cases, the numbers themselves should be returned.

@ParameterizedTest

@ValueSource(ints = {1, 2, 4, 7, 8, 11, 13, 14})

public void convert_otherNumbersResultInTheNumbersThemselves(int input) {

    var sut = new FizzBuzzer();

    assertEquals(Integer.toString(input), sut.convert(input));

}

Now that we have parameterized all the tests, don’t forget to delete the old ones! The updated  test class should look like this:

@ParameterizedTest

@ValueSource(ints = {0, -1, 1001})

public void convert_invalidInputResultsInNonApplicableResponse(int input) {

    var sut = new FizzBuzzer();

    assertEquals("N/A", sut.convert(input));

}

@ParameterizedTest

@ValueSource(ints = {3, 6, 9, 12, 33, 36, 39, 42})

public void convert_multiplesOfThreeResultsInFizz(int input) {

    var sut = new FizzBuzzer();

    assertEquals("Fizz", sut.convert(input));

}

@ParameterizedTest

@ValueSource(ints = {5, 10, 20, 25, 35, 40, 50 })

public void convert_multiplesOfFiveResultsInBuzz(int input) {

    var sut = new FizzBuzzer();

    assertEquals("Buzz", sut.convert(input));

}

@ParameterizedTest

@ValueSource(ints = {15, 30, 45, 60, 75, 90 })

public void convert_multiplesOfFifteenResultsInBuzz(int input) {

    var sut = new FizzBuzzer();

    assertEquals("FizzBuzz", sut.convert(input));

}

@ParameterizedTest

@ValueSource(ints = {1, 2, 4, 7, 8, 11, 13, 14})

public void convert_otherNumbersResultInTheNumbersThemselves(int input) {

    var sut = new FizzBuzzer();

    assertEquals(Integer.toString(input), sut.convert(input));

}

JUnit parameterized tests: A step beyond

You might look at the tests above and think there’s quite a lot of repetition. Though I don’t disagree, I’d leave them as they are because each test clearly represents a specific scenario.

But let’s say you wanted to remove the duplication from the tests above, keeping a single test for all scenarios. You may think you could write a test like this:

@ParameterizedTest


@ValueSource(ints = {1, 2, 3, 4, 5}, strings = {"1", "2", "Fizz", "4", "Buzz"})
public void convert(int input, String expectedResult) {
	var sut = new FizzBuzzer();
	assertEquals(expectedResult, sut.convert(input));
}

However that test wouldn’t work, because the @ValueSource annotation doesn’t support passing more than one parameter. Luckily, there’s a way out. First, add the following two imports:

private static Stream<Arguments> inputsAndResults() {


	return Stream.of(
			Arguments.of(-1, "N/A"),
			Arguments.of(0, "N/A"),
			Arguments.of(1001, "N/A"),
			Arguments.of(1, "1"),
			Arguments.of(2, "2"),
			Arguments.of(3, "Fizz"),
			Arguments.of(4, "4"),
			Arguments.of(5, "Buzz"),
			Arguments.of(6, "Fizz"),
			Arguments.of(10, "Buzz"),
			Arguments.of(15, "FizzBuzz"),
			Arguments.of(30, "FizzBuzz"),
			Arguments.of(33, "Fizz"),
			Arguments.of(45, "FizzBuzz")
	);
}

The method above will act as a source of data for the new test method you’ll create. Finally, add the test method:

@ParameterizedTest


@MethodSource("inputsAndResults")
public void convert(int input, String expectedResult) {
	var sut = new FizzBuzzer();
	assertEquals(expectedResult, sut.convert(input));
}

It’s the same test from before, the one I said wouldn’t work. But now, instead of the @ValueSource annotation, it uses @MethodSource. As the argument for the annotation, we pass the method’s name created in the previous step – and now it works.For each pair of arguments, JUnit will generate and execute a dedicated test.

JUnit parameterized tests: A path to cleaner test code

Your programming language of choice can be Java, JavaScript, Python, or any other, it makes no difference: unit testing is essential. And unit test code is code like any other; any strategies to make it cleaner, simpler, easier to navigate, and easier to maintain are welcome.

How about you try out JUnit in the sandbox below?

Conclusion

In this post, you’ve learned how to write a JUnit parameterized test. As you’ve seen, it’s easy to get started with, but the benefits are invaluable. By parameterizing tests, you remove code duplication, make the maintenance of test code easier, and reduce the complexity of test methods, improving their reliability.

What should your next steps be? As it turns out, software testing is a huge field with plenty to learn. Here’s a suggestion: read up on the difference between unit and integration tests and the role that each plays in a well-balanced software quality strategy. Thanks for reading!

This post was written by Carlos Schults. Carlos is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality.

This will be a practical post, but we’ll start with a brief theoretical digression to explain what “assert” means in JUnit. Then, it’s time to roll up your sleeves. You’ll learn the following:

• The process for installing JUnit 5
• How to create a sample project for testing
• What the main assertion methods are and how they work

Let’s get started.

What is Assert in JUnit?

In JUnit, Assert is a class that contains many assertion methods you can use when writing unit tests. To understand what that means, you must take a step back and learn about the structure of unit tests first.

Though this is far from being a rule, a unit test typically contains three phases:

• First, you prepare the values for your test.
• Then, you execute the action you want to test.
• Finally, you compare the results you got to what you expected to get.

Unit test frameworks enable you to perform said comparisons by using assertions. An assertion is a type of special syntax you can use to express an expectation in your test code. If the expectation proves true, your test passes; otherwise, it fails. That’s why the structure above is known as the arrange-act-assert pattern.

So, the JUnit Assert class is what allows you to write assertions in your JUnit tests. There are many assertions available; the main ones enable you to verify the following:

• The equality of two values
• Whether an object is null
• The logical value of a boolean variable
• Whether two variables point to the same object in memory

Let’s see how to use assertions in practice.

Installing JUnit 5 and creating a sample project

Start by creating a new Java project and open it with the IDE or text editor you’re most comfortable with. The next step is to install JUnit 5 on your project. JUnit 5 is the latest major version of the framework, and it’s a complete rewrite. Rather than being a simple component, JUnit 5 is made up of several independent components. To be able to write tests, you need at least JUnit Platform and JUnit Jupiter. Since I’m using Maven, I’ll add the following to my pom.xml file:

<dependency>

<groupId>org.junit.jupiter</groupId>

<artifactId>junit-jupiter-engine</artifactId>

<version>5.2.0</version>

<scope>test</scope>

</dependency>

<dependency>

<groupId>org.junit.platform</groupId>

<artifactId>junit-platform-runner</artifactId>

<version>1.2.0</version>

<scope>test</scope>

</dependency>

Now, go under src/test/java and create a new Java class. Call it DemoTest and paste the following code on it:

import org.junit.Test;

import static org.junit.Assert.*;

public class DemoTest {

}

The main JUnit Assert methods

With your demo project in place, it’s time to start writing some assertions.

assertEquals()

assertEquals() is probably the most used assertion. With it, you can compare two values and see whether they’re equal. Here’s an example of assertEquals() that uses two overloads of the method, one for integers and the other for strings:

@Test
    public void assertEquals_demo() {
        // integers
        int expected = 4;
        int actual = 2 + 2;
        assertEquals(expected, actual);


        // strings
        assertEquals("JUnit5", "JUnit" + "5");
    }

Generally speaking, you should avoid more than one assertion per test method, but this is just an example here. 

Also, remember that argument order matters when using assertEquals(). The first argument represents the result you expect to get after the “act” phase of your test, and the second one is what you actually got. Those values are then used in the error message when the test fails, so mixing them up can throw off your test results

Let’s see a test failure example. I’ll go back to the previous example, and in the line where I assign the expected variable, I’ll pass 5 to it instead of 4. When I run the test, I get this result:

java.lang.AssertionError: 

Expected: 5

Actual: 4

A warning on reference types

Before moving on, there’s something you need to be aware of when dealing with reference types. For that, consider a simple class:

public class Person {

    private final String name;

    private final int age;

    public Person(String name, int age) {

        this.name = name;

        this.age = age;

    }

}

Now, consider the following test:

p1 = new Person("John Doe", 32);

var p2 = new Person("John Doe", 32);

assertEquals(p1, p2);

You might think that such a test would pass, but it fails:

java.lang.AssertionError: 

Expected :Person@17d99928

Actual   :Person@3834d63f

<Click to see difference>

That’s because classes are reference types. As such, when you use assertEquals, only the references of the objects are compared by default. Even though p1 and p2 have the same values, they refer to different objects in the heap memory, so they’re considered different.

However, we really want to compare those two objects by their values, not their references. In other words, we want structural equality rather than referential equality. Achieving that is easy: you’d have to override the equals() method on the Person class.

@Override

public boolean equals(Object obj) {

if (obj == this)

return true;

if (!(obj instanceof Person)) {

return false;

}

Person other = (Person) obj;

return other.name.equals(this.name) && other.age == this.age;

}

A useful overload

There are multiple versions (overloads) of the assertEquals() method that can be used with different data types such as long, String, double, float, int, etc. Each of these versions also has a variant that includes an additional String parameter called message. This variant can be used to specify a message to be displayed if the test fails. This can be useful when multiple tests fail simultaneously, as it allows for faster identification of the issue.

By the way, all assertion methods have an overload like that. So it’d be redundant to provide an example for each. You’ll see an example of the message parameter being used soon.

assertNotEquals()

It’s common for assertion methods to have an inverse method. assertNotEquals() asserts that two objects are different. Like its counterpart, you should pay attention to the order of the parameters: 

• First comes the unexpected value
• Then the actual value

assertTrue()

The assertTrue() method asserts that a given boolean value is true. As an example, let’s rewrite the previous assertion, replacing assertEquals with assertTrue:

assertTrue(p1.equals(p2));

This is just an example. In real test code, it’s better to use assertEquals over assertTrue when comparing two values. That’s because assertEquals will give you a more useful error message when a failure occurs, following the pattern “expected: x; actual: y.”

Use assertTrue(), for instance, to assert the return value of a method of type boolean. Here’s an example:

@Test
public void assertTrue_demo() {
	int yearsOfEmployment = 5;
	String name = "John Doe";
	Employee emp = new Employee(name, yearsOfEmployment);
	assertTrue(emp.isEligibleForSabbatical());
}

This is the code for the Employee class:

public class Employee {

    private final String name;

    private final int yearsOfEmployment;

    public Employee(String name, int yearsOfEmployment) {

        this.name = name;

        this.yearsOfEmployment = yearsOfEmployment;

    }

    public boolean isEligibleForSabbatical() {

        return yearsOfEmployment >= 5;

    }

}

assertTrue() has a useful overload in which you can provide a message when the test fails:

@Test
public void assertTrue_demo() {
	int yearsOfEmployment = 5;
	String name = "John Doe";
	Employee emp = new Employee(name, yearsOfEmployment);
	assertTrue(
			"Employees with 5 or more years of employment are eligible for a sabbatical",
			emp.isEligibleForSabbatical());
}

I’ll go into the production method and add a bug:

return yearsOfEmployment >= 6;

Now the test fails with a helpful message:

java.lang.AssertionError: Employees with 5 or more years of employment are eligible for a sabbatical

at org.junit.Assert.fail(Assert.java:89)

at org.junit.Assert.assertTrue(Assert.java:42)

at DemoTest.assertTrue_demo(DemoTest.java:10)

[...]

assertFalse()

As its name suggests, the assertFalse() method is the opposite of assertTrue() – the test passes when its parameter evaluates to false. 

assertSame()

When discussing assertEquals(), you’ve learned that reference types (objects) are compared regarding their references. That’s why assertEquals() fails for objects that don’t override the equals() method. What if you really wanted to verify reference equality? In this case, you’d use the assertSame() method:

var p1 = new Person("Jane Doe", 20);

var p2 = p1;

assertSame(p1, p2);

assertNotSame()

If you wanted to assert that two variables don’t point to the same object, you’d use the opposite method: assertNotSame(). 

assertNull()

You use the assertNull() method to verify whether the specified parameter contains a null reference:

PhoneNumber candidate = PhoneNumber.tryParse("this isn't a phone number");
assertNull(candidate);

assertNotNull()

You should know the drill by now: this is the inverse of the previous one, and I won’t bore you with an example. 

Just a personal anecdote: I rarely use assertNotNull() when writing my tests. Most of the time, I find that there are more useful assertions I can write. For instance, I often have a specific value I want to compare the result of the operation to, in which case assertEquals() is the answer.

assertThrows()

When writing tests, not only must you test the scenario in which everything works fine—i.e. the happy path—but you should also test the sad path scenarios.

Languages like Java use the concept of exceptions to express unintended behaviors and errors in methods. So you’ll often have to write tests that verify whether a given exception happens.

Let’s go back to the Person class from previous examples and change its constructor, adding two guard clauses:

public Person(String name, int age) {
	if (name == null || name.trim().isEmpty())
		throw new IllegalArgumentException("Name shouldn't be empty!");


	if (age < 0)
		throw new IllegalArgumentException("Age can't be negative!");


	this.name = name;
	this.age = age;
}

You’ll agree with me that it’s reasonable to prohibit empty names and negative ages. But now it’d be great to have tests for these scenarios for two reasons:

• Documentation: The tests will serve as a specification of the class’s behavior.
• Code quality: The tests will prevent the guard clauses from being removed or changed.

Let’s write a test for the negative age scenario:

@Test
public void negativeAgesNotAllowed() {
	int age = -1;
	String name = "John Doe";
	assertThrows(IllegalArgumentException.class, () -> new Person(name, age));
}

As you can see, using assertThrows isn’t hard: you pass the expected exception class as the first argument and a lambda expression representing the action to be executed as the second one. 

As an exercise, try implementing tests for these scenarios for the name argument:

• An empty string
• A string with spaces
• null

How about you try out JUnit in the sandbox below?

Assert your way to better software

In this post, you’ve learned what assertions are, why they’re crucial for unit testing, and how to write assertions using the JUnit Assert class.

Where should you go next? My suggestion is for you to explore the Assert class in more depth. There are many assertions available. The ones we’ve explored barely scratch the surface, despite being some of the most important ones. The more you master the Assert class, the more expressive your unit tests will be.

Thanks for reading!

This post was written by Carlos Schults. Carlos is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality. 

In short, a relational database works with tables and relations between them. Meanwhile, we have several elements in the object-oriented paradigm, like classes, properties, visibility, inheritance, and interfaces. This difference in the structure of the two paradigms makes it challenging to represent the data and the model. This is where ORM comes into play to solve this problem. 

In this article, we will talk about TypeORM, one of the main tools used today. 

What Is an ORM?

ORM, or object-relational mapping, aims to create a mapping layer between our object model (application) and our relational model (database) in order to abstract access to the database. 

In summary, it is a technique used to map between object-oriented systems and relational databases. Database tables represented in classes and table records would be instances of these classes. Therefore, in the acronym “ORM,” each of the terms is related to a part of the process: 

• Object: the part used with your programming language
• Relational: a relational database management system. Although there are various kinds of databases, relational databases are the most common.
• Mapping: where the developer makes a bridge between their objects and their tables

The ORM defines a technique to perform the conciliation between the two models. One of the core parts is mapping rows to objects. 

That is, the ORM defines how data will be mapped between environments and how it will be accessed and recorded. This cuts down on development time because you don’t have to spend time doing this setup. Another advantage is the adaptation of new team members. Since many projects use the same tool, finding members who are already used to the work pattern is possible. 

ORM vs. Query Builder vs. Native Driver

A native driver is at the lowest level; that is, it’s closest to the database. You connect to the database, write SQL queries in string form, and tell the database to execute. Executing queries is an asynchronous operation, so they return a promise. 

One level of abstraction above is the query builder. The main difference is that here you write the queries programmatically using functions, and the library takes care of generating the native query. An advantage of this approach is that the library handles possible syntax differences between different databases. This allows you to switch banks more easily in your application, making little or no code changes. One of the most popular query builder libraries on Node.js is Knex. 

Object-relational mapping, as the name implies, is a pattern where the relational structure of the database is mapped into objects in the language in question. Tables turn into objects, rows turn into object attributes, and relations between tables become relations between objects. This is the highest level of abstraction. You don’t even have to think about SQL queries. 

TypeORM

Now that we understand the function of an ORM, let’s focus on this framework. According to its documentation, TypeORM is an ORM that can run on Node.js, Browser, Cordova, Ionic, React Native, NativeScript, Expo, and Electron platforms and can be used with both TypeScript and JavaScript. 

Entity

An entity is what you use to relate your data to a table in the database. Let’s see an example of an entity in the code below: 

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"

@Entity()

export class Professor {

    @PrimaryGeneratedColumn()

    id: number

    @Column()

    name: string

    @Column()

    address: string

    @Column()

    isActive: boolean

}

Using the @Entity decorator, we define our new entity. In this case, it is called “Professor” and has the following attributes: ID, which is a number type with auto-incremental values; name, which is a string; address, which is also a string; and isActive, which is a boolean. 

Notice the similarity between this model and a database table. This type of representation makes it much easier to visualize the entities we are working with. 

You can read more about entities in the official TypeORM documentation. 

Migrations

During the life of an application, and especially during its initial development stage, it is very common to have demands to migrate the database with the application so that both structures are synchronized. This process, done manually, depends on close communication with the development team so that there are no disagreements. 

Another very useful feature of TypeORM is the so-called migrations. 

We can create migrations that produce several tables and their attributes, so just run the migrations, and we will automatically create our tables. 

import { MigrationInterface, QueryRunner, Table } from "typeorm";

export default class CreateUser implements MigrationInterface {

  public async up(queryRunner: QueryRunner): Promise<void> {

    await queryRunner.createTable(

      new Table({

        name: "users",

        columns: [

          {

            name: "id",

            type: "uuid",

            isPrimary: true,

            generationStrategy: "uuid",

            default: "uuid_generate_v4()"

          },

          {

            name: "name",

            type: "varchar(200)"

          },

          {

            name: "profile_id",

            type: "uuid"

          }

        ]

      })

    );

  }

  public async down(queryRunner: QueryRunner): Promise<void> {

    await queryRunner.dropTable("users");

  }

}

Notice that there are two methods for each migration: up and down. You must insert the code to carry out the migration in the up method. In the down method, you must insert the necessary code if you want to revert the last migration. 

Relations

Relations are a core concept for understanding relational databases. Therefore, relational databases are based on the relational model of organization and are made to represent and organize data in tables. 

In this sense, we can have access to different types of data, such as the financial data of a company, patient information from a hospital, or the names of students at a university. 

Also, when we refer to this relational model structure, in this type of database, each row in the table will represent a unique ID, which we will call a key. In addition to this, it is worth mentioning other characteristics that are part of the relational database model, such as columns, which contain attributes of each piece of data and can also have a value. 

This helps build relations that interconnect the data structure in a database. 

Let’s imagine we have two entities, Blog and Post, and we want to create a relationship between them. We have a one-to-many relationship between Blog and Post. This relationship is done in TypeORM using the @OneToMany annotation like this: 

@OneToMany(() => Post, (post) => post.blog)

public posts: Post[];

On the side of the Post class, it looks like this, following the same parameters as @OneToMany:

@ManyToOne(() => Blog, (blog) => blog.posts)

public blog: Blog;

Keep in mind that we can also use TypeORM with nonrelational databases such as MongoDB. 

Pros and cons

The first point to have in mind when considering using TypeORM is remembering that you’re using an ORM, so if your project relies heavily on a high level of query efficiency, it might not be the ideal option. 

Sometimes it’s better to work with a tool that the team has already mastered than to spend time learning another one. It all depends on the specifics of the project. 

However, when using TypeORM you will have access to a series of abstractions that will facilitate development and save time. In addition, one of the great benefits is the possibility of creating scalable and high-quality applications. 

TypeORM also supports multiple databases, whether SQL or NoSQL. Some databases you can use are PostgreSQL, MySQL, MariaDB, and MongoDB. 

Conclusion

Within a software development environment that works on a relational basis, it is interesting to consider the use of an ORM to abstract the mapping of application objects versus database objects. 

The level of abstraction of this approach is relatively high, but that doesn’t mean you shouldn’t know what happens at the lowest level. The problem happens when the developer skips steps and uses an abstraction without understanding either what it abstracts or the technology behind it. 

Therefore, before using TypeORM or any other ORM, it is recommended to analyze the needs of the project. 

So, in a nutshell, TypeORM is a great tool for dealing with databases in the process of software development if used wisely. So make sure to really understand the necessities of the application to know where to focus more. 

This post was written by Rhuan Souza. Rhuan is a software engineer who has experience with infrastructure. Rhuan is currently working as a full-stack web developer. He’s a passionate developer who focuses not only on code, but also wants to help change processes, and make people’s lives easier.

The JDBC API provides the Statement object to execute queries, updates, and DDL statements on data in a database. The PreparedStatement is a subclass of` Statement that provides better security, portability across vendors, and performance.

The prepared in the name comes from the fact that it’s prepared/compiled initially and then reused rather than having to be compiled for each execution.

This post will explain why and how to work with PreparedStatements.

Usage

Since a PreparedStatement is a subclass of Statement, it can do what a Statement can do, plus more.

The fact that the PreparedStatement is pre-compiled is transparent to its users. Where it differs in usage is in its ability to specify parameters.

The JDBC driver handles these parameters and thus, the handling is naturally compatible with the database we’re using. This leads to some benefits, as we will see later.

Below is an example of using the PreparedStatement with two parameters:

PreparedStatement insStm = conn.prepareStatement("insert into emp values(?,?)");

insStm.setInt(1, 1);

insStm.setString(2, "Harry Potter");

insStm.execute();

As shown above, we’re inserting records into the emp table. In the SQL command string, we denote the places where a parameter should be with a question mark. When specifying parameter values, we identify a parameter by its position in the SQL command.

The starting position is 1 instead of the usual 0. If we use the same parameter value in multiple positions, we’ll have to set it for each position.

The first parameter is an integer, so we use the setInt method. The second is a string, so we use setString. Using the strongly typed methods ensures that we won’t pass the wrong type of parameter by mistake.

But if we were getting the parameters dynamically, say, from an array or collection, it would be cumbersome to check the type of each parameter and then use the corresponding set method. The setObject method is helpful here. Using it, we can set a parameter of any supported type.

A PreparedStatement object is not thread-safe; we should not use the same instance concurrently from multiple threads.

Improper usage

We can only use parameters where we would use values. If we try to use parameters to build other parts of the SQL command, it won’t work. For example, what we see here would fail with an SQLSyntaxErrorException:

PreparedStatement pStm = conn.prepareStatement("select * from ?");

pStm.setString(1, "emp");

pStm.execute();

Here, we’re trying to provide the table name as a parameter. However, this isn’t allowed, nor would any other syntax part be, like column names, where clause, group by, order by, etc.

Security

A PreparedStatement provides better security against certain hacking attacks compared with its parent, the Statement. The Statement object executes a string command. If it needs to use some parameters, we must concatenate them as strings with the SQL command. This creates a risk of the command being changed to something else using the parameters.

Consider the query below using string concatenation to validate a user’s login:

"select * from user where user_name = '" + usrName + "'"

Here, usrName is a string that the application’s user has provided. If a malicious user were to enter usrName as ' or '1'='1 (note the single quotes used), the query string now becomes

select * from user where user_name = '' or '1'='1'

This SQL command now circumvents the check for user_name, since the added OR condition is always true. If the database allows us to run multiple commands in a go (separated by a semicolon, for example), then a hacker could append a destructive command like delete or drop as well. This type of hacking attack using injected parameters to alter the SQL command is called an SQL injection attack.

Now, let’s try using a PreparedStatement instead. Using PreparedStatement, the above code would become

PreparedStatement loginStm = conn.prepareStatement("select * from user where user_name = ?");

loginStm.setInt(1, usrName);

loginStm.execute();

What’s different here? The JDBC driver is handling the parameters. It’ll send the parameters to the database as variables bound to the pre-compiled SQL command. The database will escape the special character within them if needed. For example, it usually escapes a single quote with an additional single quote. Thus, the malicious value remains a value, and it cannot change the SQL command.

Note that we can use a Statement and escape the values ourselves before concatenating, but the escaping syntax can differ from database to database and hence, it’s best to let the JDBC driver handle it.

Also, there are still other advantages to using a PreparedStatement. Read on.

Portability and ease of use

When we use string concatenation to inject parameters into the SQL command, we have to format and escape the values too. Also, this part could be database specific, making our code less portable across different database implementations.

We’ve seen an example of this in the SQL injection section above. Parameters in PreparedStatement make our life easier and keep the code portable since the JDBC driver handles them.

Let’s see another example using dates. Let’s assume the user has entered a date and wants to filter records by that date. If we were building the query as a string, then we would need to convert the date into a string:

"select * from sales where sale_date > '" + user_date_string + "'"

What should be the format of the date string? For example, should it be dd-MM-YY or MM-dd-YY, etc.?

This would work only if the format of user_date_string matches the default date format for the database. The default date format can vary from one database instance to another, so we cannot be sure about it.

We could use some database function like TO_DATE that will parse the user_date_string with the given format and convert it back into the date.

"select * from sales where sale_date > TO_DATE( 'DD-MM-YYYY', '" + user_date_string + "')"

However, the syntax of functions like TO_DATE is specific to a database vendor; others might not have the same name or parameters. Some functions are supported by the JDBC spec using the fn keyword, but many JDBC drivers don’t have implementations for these.

So, it’s PreparedStatement to the rescue:

PreparedStatement dateFilterStm = conn.prepareStatement("select * from sales where sale_date > ?)";

dateFilterStm.setDate( 1, usrDate);

All we need to do is pass the date as java.util.Date or java.sql.Date to the PreparedStatement. There’s no worrying about escaping or formatting or portability.

PreparedStatement rocks!

Performance

Pre-compilation

We mentioned at the start that the SQL command used in a PreparedStatement is pre-compiled or prepared initially and then reused.

What does pre-compile mean, and who does it? When we run an SQL command on the database, the database will first validate, parse, and build an execution plan to run it. These steps come under pre-compiling.

When the JDBC driver creates a prepared statement, it will ask the database to pre-compile that SQL command. Then, the database will store the pre-compiled command in a cache for the current database connection. If the same SQL command is to be run multiple times, using the pre-compiled version is better for performance since the compilation part has to be done only once.

The database has a limited cache for pre-compiled SQL commands and hence, we cannot have too many of them. Defining parameters helps make the command reusable and therefore reduces the number of distinct commands.

If the parameters weren’t separate, the same command with different parameters would each become a separate command.

For example:

select * from emp where name = 'Goldstein';

select * from emp where name = 'Mukherjee';

select * from emp where name = ?;

In the above examples, the first two SQL commands are distinct since we didn’t define the parameters separately. Whereas, if we had used a PreparedStatement as in the third, then they could have used the same SQL command.

Reusing

Using the same PreparedStatement multiple times in the same database session/connection will be better for performance. Each time, we need to set the parameters and execute the PreparedStatement. For example, we could use it to repeatedly insert data into a table

PreparedStatement insStm = conn.prepareStatement("insert into emp values(?,?)");

insStm.setInt(1, 1);

insStm.setString(2, "Harry Potter");

insStm.execute();

insStm.setInt(1, 2);

insStm.setString(2, "Tom Riddle");

insStm.execute();

insStm.close();

or we could do the set parameters and execute() inside a loop. This is still not the best solution since each execute() will mean a round trip to the database.

Batching

We can send n number of statements in a single batch to the database, where n is up to us to decide. This will reduce the round trips and greatly improve performance. Below is an example using batching.

PreparedStatement insStm = conn.prepareStatement("insert into emp values(?,?)");

List<String> names = List.of( "Wareen Buffet", "Steve Jobs", "Bill Gates");

boolean moreRows = false;

int i=0;

for( i=0; i< names.size(); i++) {

  insStm.setInt(1, i+1);
  
  insStm.setString(2,names.get(i));

  insStm.addBatch();

  if( i % 100 == 0) {

    // execute every 100 rows

    int[] rowCounts = insStm.executeBatch();
  }

}

if( i % 100 != 0) {

  // lef tover rows

  int[] rowCounts = insStm.executeBatch();

}

insStm.close();

Here, we call set parameters and addBatch() for each row in the loop. The addBatch() will store the current parameters for later execution.

When the counter is a multiple of 100, we’ll execute the batched rows in one go. In our case, that means 100 at a time.

executeBatch() returns an array of integers corresponding to the number of rows affected by each statement in the batch. The SQL command needs to be sent to the database only once for pre-compilation. The batch needs to send a reference to the pre-compiled command and the parameters for all the rows in the batch.

Conclusion

In this post, we’ve seen when and why we should use PreparedStatements, along with examples using code.

PreparedStatements are the preferred way to execute SQL commands using the JDBC API since they offer better security, ease of use, portability, and performance.

This post was written by Manoj Mokashi. Manoj has more than 25 years of experience as a developer, mostly on java, web technologies, and databases. He’s also worked on PHP, Python, Spark, AI/ML, and Solr and he really enjoys learning new things.