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();
        }

    }
}

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);
    }
}

The example demonstrated below will show you how to use the JdbcTemplate.update() method for updating records in database. This method returns an integer value indicating number of records updated in the database when the query is executed.

package org.kodejava.example.spring.jdbc;

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

import javax.sql.DataSource;
import java.sql.Types;
import java.util.Date;

public class UpdateDemo {
    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 =
            "UPDATE records SET title = ?, release_date = ? WHERE id = ?";

    private DataSource dataSource;

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

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

        Long id = 2L;
        String title = "The Beatles 1967 - 1970";
        Date releaseDate = new Date();
        demo.updateRecord(id, title, releaseDate);
    }

    public void updateRecord(Long id, String title, Date releaseDate) {
        //
        // Creates an instance of JdbcTemplate and set the DataSource.
        // We can use the template update() method to update records
        // in the database. Below we use an update() method that accepts
        // three parameters: the sql query, the parameter values and
        // the parameter data types.
        //
        JdbcTemplate template = new JdbcTemplate(this.dataSource);

        Object[] params = {title, releaseDate, id};
        int[] types = {Types.VARCHAR, Types.DATE, Types.BIGINT};

        int rows = template.update(UpdateDemo.QUERY, params, types);
        System.out.println(rows + " row(s) updated.");
    }

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

In this example you will learn how to create and configure a DriverManagerDataSource object that will be used by the JdbcTemplate object. There are some information required when creating a DataSource including the JDBC driver class, the JDBC Url of the target database, the username and password for connecting to database server.

package org.kodejava.example.spring.jdbc;

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

import javax.sql.DataSource;
import java.util.List;

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

    public static void main(String[] args) {
        //
        // Creates an instance of DriverManagerDataSource and pass
        // it to the JdbcTemplate.
        //
        DataSource source = getDataSource();
        JdbcTemplate template = new JdbcTemplate(source);

        //
        // After creating a template with a data source inject we
        // can do a database manipulation such as the CRUD operation.
        //
        System.out.println("DataSource = " + template.getDataSource());
        List records = template.queryForList("SELECT * FROM records");
        for (int i = 0; i < records.size(); i++) {
            System.out.println("Records = " + records.get(i));
        }
    }

    /**
     * Returns a DataSource object for connection to the database.
     *
     * @return a DataSource.
     */
    private static DataSource getDataSource() {
        //
        // Creates a new instance of DriverManagerDataSource and sets
        // the required parameters such as the Jdbc Driver class,
        // Jdbc URL, database user name and password.
        //
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(DataSourceDemo.DRIVER);
        dataSource.setUrl(DataSourceDemo.JDBC_URL);
        dataSource.setUsername(DataSourceDemo.USERNAME);
        dataSource.setPassword(DataSourceDemo.PASSWORD);
        return dataSource;
    }
}

The following example show you how to use the Spring’s JdbcTemplate class to insert a record into database.

package org.kodejava.example.spring.jdbc;

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

import javax.sql.DataSource;
import java.sql.Types;
import java.util.Date;

public class InsertDemo {
    private static final String sql =
            "INSERT INTO records (title, " +
            "    release_date, " +
            "    artist_id, " +
            "    label_id, " +
            "    created) " +
            "VALUES (?, ?, ?, ?, ?)";

    private DataSource dataSource;

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

    public void saveRecord(String title, Date releaseDate,
                           Integer artistId, Integer labelId) {
        //
        // Creates an instance of JdbcTemplate and passes a connection
        // to the database.
        //
        JdbcTemplate template = new JdbcTemplate(this.dataSource);

        //
        // Defines the query arguments and the corresponding SQL types
        // of the arguments.
        //
        Object[] params = new Object[] {
                title, releaseDate, artistId, labelId, new Date()
        };
        int[] types = new int[] {
                Types.VARCHAR,
                Types.DATE,
                Types.INTEGER,
                Types.INTEGER,
                Types.DATE
        };

        //
        // Calls JdbcTemplate.update() methods to create a new data
        // in the records table. The update method in general will
        // return number of row / rows processed by the executed query
        //
        int row = template.update(sql, params, types);
        System.out.println(row + " row inserted.");
    }

    public static DriverManagerDataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost/mediadb");
        dataSource.setUsername("root");
        dataSource.setPassword("");
        return dataSource;
    }

    public static void main(String[] args) {
        DataSource dataSource = getDataSource();

        InsertDemo demo = new InsertDemo(dataSource);
        demo.saveRecord("Rock Beatles", new Date(), 1, 1);
    }
}