How do I select records from database using JdbcTemplate?

In this example you will learn how to select records from the database using JdbcTemplate.queryForList() method. This method returns a List object which stores information selected from the table in a HashMap object. The key of the map is the table’s field names while the value of the map contains the corresponding table’s field value.

package org.kodejava.example.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.HashMap;
import java.util.List;

public class SelectDemo {
    public static void main(String[] args) {
        //
        // Creates a DataSource object.
        //
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost/mediadb");
        ds.setUsername("root");
        ds.setPassword("");

        //
        // Creates an instance of JdbcTemplate.
        //
        JdbcTemplate template = new JdbcTemplate(ds);

        //
        // Executes a select query using queryForList() method. This
        // method returns a List containing HashMap object. The key
        // of the map is the table's field name and the value is
        // the table's field value.
        //
        String query = "SELECT * FROM records";
        List results = template.queryForList(query);
        for (Object result : results) {
            HashMap map = (HashMap) result;
            for (Object key : map.keySet()) {
                System.out.print(key + " = " + map.get(key) + "; ");
            }
            System.out.println();
        }

    }
}

How do I delete records from database using JdbcTemplate?

The code below demonstrates on how to delete some records from database using the JdbcTemplate.

package org.kodejava.example.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

public class DeleteDemo {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost/mediadb";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "";

    public static final String QUERY = "DELETE FROM records WHERE id = ?";

    private DataSource dataSource;

    public DeleteDemo(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void deleteRecord(Long id) {
        //
        // Creates an instance of JdbcTemplate and supply a data
        // source object.
        //
        JdbcTemplate template = new JdbcTemplate(this.dataSource);

        //
        // Delete a student record from database where the students
        // id matches with the specified parameter.
        //
        Object[] params = {id};
        int rows = template.update(DeleteDemo.QUERY, params);
        System.out.println(rows + " row(s) deleted.");
    }

    /**
     * Returns a DataSource object.
     *
     * @return a DataSource.
     */
    public static DataSource getDataSource() {
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(DeleteDemo.DRIVER);
        ds.setUrl(DeleteDemo.URL);
        ds.setUsername(DeleteDemo.USERNAME);
        ds.setPassword(DeleteDemo.PASSWORD);
        return ds;
    }

    public static void main(String[] args) {
        DataSource ds = getDataSource();
        DeleteDemo demo = new DeleteDemo(ds);

        Long id = 1L;
        demo.deleteRecord(id);
    }
}