Specific Columns Selection

Here I am going to show you an example how to select only specific columns from a table. So you only need to select few columns from a table instead of selecting all columns. In other words you are going to fetch those columns which are required for your business purpose.

Prerequisites

Java 1.8+(19), Spring Boot 3.0.5, Maven 3.8.5, MySQL 8.0.31

Project Setup

You can use the following pom.xml file for your reference if you are using maven as a build tool.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.roytuts</groupId>
	<artifactId>spring-jdbctemplate-db-column-selection</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.0.5</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.31</version>
		</dependency>
		<!--required only if jdk 9 or higher version is used -->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Table And Data

Creating a table with few sample data to test the application right away.

CREATE DATABASE IF NOT EXISTS `roytuts`;
USE `roytuts`;

CREATE TABLE IF NOT EXISTS `item` (
  `item_id` int unsigned NOT NULL AUTO_INCREMENT,
  `item_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_desc` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `item_price` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `item` (`item_id`, `item_name`, `item_desc`, `item_price`) VALUES
	(1, 'CD', 'CD is a compact disk', 100),
	(2, 'DVD', 'DVD is larger than CD in size', 150),
	(3, 'ABC', 'ABC test description', 24),
	(4, 'XYZ', 'XYZ test description', 25.32),
	(5, 'CD Player', 'CD player is used to play CD', 30.02);

Database Config

Database configuration file for creating DataSource and JdbcTemplate instances.

@Configuration
public class DatabaseConfig {

	@Autowired
	private Environment environment;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();

		dataSource.setDriverClassName(environment.getProperty("jdbc.driverClassName"));
		dataSource.setUrl(environment.getProperty("jdbc.url"));
		dataSource.setUsername(environment.getProperty("jdbc.username"));
		dataSource.setPassword(environment.getProperty("jdbc.password"));

		return dataSource;
	}

	@Bean
	public JdbcTemplate getJdbcTemplate() throws ClassNotFoundException {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
		return jdbcTemplate;
	}

}

Model Class

Model class which will have fields to be mapped to the table columns.

public class Item {

	private int id;
	private String name;

        //...
}

Repository

Repository is generally a DAO class which will be used to perform database operations.

@Repository
public class ItemDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	private static final String SQL_FETCH_ITEM_SPECIFIC_COLS = "SELECT item_id, item_name FROM item";
	private static final String SQL_FETCH_ITEM_SPECIFIC_COLS_LIKE = "SELECT item_id, item_name FROM item WHERE item_desc LIKE ?";

	public List<Item> fetchItems() {
		List<Map<String, Object>> records = jdbcTemplate.queryForList(SQL_FETCH_ITEM_SPECIFIC_COLS);

		List<Item> items = records.stream().map(r -> {
			Item item = new Item();
			item.setId(Integer.valueOf(String.valueOf(r.get("item_id"))));
			item.setName(String.valueOf(r.get("item_name")));
			return item;
		}).collect(Collectors.toList());

		return items;
	}

	public List<Item> fetchItemsLike(final String desc) {
		List<Map<String, Object>> records = jdbcTemplate.queryForList(SQL_FETCH_ITEM_SPECIFIC_COLS_LIKE,
				new Object[] { "%" + desc + "%" });

		List<Item> items = records.stream().map(r -> {
			Item item = new Item();
			item.setId(Integer.valueOf(String.valueOf(r.get("item_id"))));
			item.setName(String.valueOf(r.get("item_name")));
			return item;
		}).collect(Collectors.toList());

		return items;
	}

}

Spring Boot Main Class

Spring boot main class to start the application.

@SpringBootApplication
public class App implements CommandLineRunner {

	@Autowired
	private ItemDao itemDao;

	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		System.out.println("----------------All Records----------------");
		itemDao.fetchItems().stream().forEach(i -> System.out.println(i));

		System.out.println();
		System.out.println();

		System.out.println("----------------Filtered Records----------------");
		itemDao.fetchItemsLike("CD").stream().forEach(i -> System.out.println(i));
	}

}

Testing – Specific Columns Selection

Here is the output when the spring boot main class is run.

----------------All Records----------------
Item [id=1, name=CD]
Item [id=2, name=DVD]
Item [id=3, name=ABC]
Item [id=4, name=XYZ]
Item [id=5, name=CD Player]


----------------Filtered Records----------------
Item [id=1, name=CD]
Item [id=2, name=DVD]
Item [id=5, name=CD Player]

Source Code

Download