Spring JDBC: Introduction to JdbcTemplate (II)--API Usage

1. queryForMap

public Map<String, Object> queryForMap(String sql);
public Map<String, Object> queryForMap(String sql, Object... args);
public Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes);
// The methods above are meant to be s single row query.

As we can see from API Doc:

1) Return type is Map<String, Object>. One entry for each column, using the column name as the key.

2) As we cannot have  duplicate keys, this query is meant to be a single row query.

3) This methods is appropriate when you don't have a domain model, 

    Otherwise, consider using one of the queryForObject() methods.

4) If the row count that matches the sql is bigger than one, IncorrectResultSizeDataAccessException will be thrown.

package edu.xmu.jdbc.dao;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import edu.xmu.jdbc.bean.Student;

public class JdbcTemplateDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
	this.jdbcTemplate = new JdbcTemplate(this.dataSource);

    }

    public void prepareData(List<Student> studentList) {
	String sql = "insert into student(name, age) values (?, ?)";
	for (Student student : studentList) {
	    jdbcTemplate
		    .update(sql,
			    new Object[] { student.getName(), student.getAge() },
			    new int[] { java.sql.Types.VARCHAR,
				    java.sql.Types.INTEGER });
	}
    }

    public Map<String, Object> queryForMap() {
	String sql = "select id, name, age from student";
	return jdbcTemplate.queryForMap(sql);
    }

    public Map<String, Object> queryForMap2(int id) {
	String sql = "select id, name, age from student where id=?";
	return jdbcTemplate.queryForMap(sql, new Object[] { id });
    }

    public Map<String, Object> queryForMap3(int id) {
	String sql = "select id, name, age from student where id<=?";
	return jdbcTemplate.queryForMap(sql, new Object[] { id },
		new int[] { java.sql.Types.INTEGER });
    }

    public void truncateTable() {
	String sql = "truncate table student";
	jdbcTemplate.execute(sql);
    }
}

package edu.xmu.jdbc.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import edu.xmu.jdbc.bean.Student;

public class JdbcTemplateTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private JdbcTemplateDao dao;

    @Before
    public void setUp() {
	dataSource = new DriverManagerDataSource(url, username, password);
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");

	dao = new JdbcTemplateDao();
	dao.setDataSource(dataSource);

	List<Student> studentList = new ArrayList<Student>();
	Student student = new Student("Davy", 24);
	studentList.add(student);

	dao.prepareData(studentList);
    }

    @Test
    public void queryForMapTest() {
	Map<String, Object> resultMap = dao.queryForMap();

	Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet();
	for (Entry<String, Object> entryMap : entrySet) {
	    System.out.println(entryMap.getKey() + "=" + entryMap.getValue());
	}
    }

    @Test
    public void queryForMap2Test() {
	Map<String, Object> resultMap = dao.queryForMap2(1);

	Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet();
	for (Entry<String, Object> entryMap : entrySet) {
	    System.out.println(entryMap.getKey() + "=" + entryMap.getValue());
	}
    }

    @Test
    public void queryForMap3Test() {
	Map<String, Object> resultMap = dao.queryForMap3(2);

	Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet();
	for (Entry<String, Object> entryMap : entrySet) {
	    System.out.println(entryMap.getKey() + "=" + entryMap.getValue());
	}
    }

    @After
    public void tearDown() {
	dao.truncateTable();
    }
}

 

2. queryForObject

public <T> T queryForObject(String sql, Class<T> requiredType);
public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType);
public <T> T queryForObject(String sql, Class<T> requiredType, Object... args);
// The methods above are meant to be a single row and single column query.
public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType);
public <T> T queryForObject(String sql, RowMapper<T> rowMapper);
public <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper);
public <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper);
//The methods above are meant to be a single row query.

As we can see from API Doc:

1) The methods without rowMapper params are meant to be a single row and single column query.

    Because the requiredType can only be java embedded type.

2) The methods with rowMapper params are meant to be a single row query.

package edu.xmu.jdbc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import edu.xmu.jdbc.bean.Student;

public class QueryForObjectDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
	this.jdbcTemplate = new JdbcTemplate(this.dataSource);

    }

    public void prepareData(List<Student> studentList) {
	String sql = "insert into student(name, age) values (?, ?)";
	for (Student student : studentList) {
	    jdbcTemplate
		    .update(sql,
			    new Object[] { student.getName(), student.getAge() },
			    new int[] { java.sql.Types.VARCHAR,
				    java.sql.Types.INTEGER });
	}
    }

    public Student queryForObject() {
	String sql = "select id from student";
	int id = jdbcTemplate.queryForObject(sql, Integer.class);

	sql = "select name from student";
	String name = jdbcTemplate.queryForObject(sql, String.class);

	sql = "select age from student";
	int age = jdbcTemplate.queryForObject(sql, Integer.class);

	return new Student(id, name, age);
    }

    public Student queryForObject2(int id) {
	String sql = "select name from student where id=?";
	String name = jdbcTemplate.queryForObject(sql, new Object[] { id },
		String.class);

	sql = "select age from student where id=?";
	int age = jdbcTemplate.queryForObject(sql, new Object[] { id },
		Integer.class);

	return new Student(id, name, age);
    }

    public Student queryForObject3(int id) {
	String sql = "select name from student where id=?";
	String name = jdbcTemplate.queryForObject(sql, new Object[] { id },
		new int[] { java.sql.Types.INTEGER }, String.class);

	sql = "select age from student where id=?";
	int age = jdbcTemplate.queryForObject(sql, new Object[] { id },
		new int[] { java.sql.Types.INTEGER }, Integer.class);

	return new Student(id, name, age);
    }

    /**
     * This sql is meant to be a single row result set. <br/>
     * If result set size is bigger than 1,
     * IncorrectResultSizeDataAccessException will be thrown.
     * 
     * @return
     */
    public Student queryForObject4() {
	String sql = "select id, name, age from student";

	Student student = jdbcTemplate.queryForObject(sql,
		new RowMapper<Student>() {

		    public Student mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			// int id = rs.getInt("id");
			int id = rs.getInt(1);
			// String name = rs.getString("name");
			String name = rs.getString(2);
			// int age = rs.getInt("age");
			int age = rs.getInt(3);

			return new Student(id, name, age);
		    }

		});

	return student;
    }

    public Student queryForObject5(int id) {
	String sql = "select id, name, age from student where id=?";
	Student student = jdbcTemplate.queryForObject(sql, new Object[] { id },
		new RowMapper<Student>() {

		    public Student mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			int id = rs.getInt(1);
			String name = rs.getString(2);
			int age = rs.getInt(3);
			return new Student(id, name, age);
		    }

		});

	return student;
    }

    public Student queryForObject6(int id) {
	String sql = "select id, name, age from student where id=?";
	Student student = jdbcTemplate.queryForObject(sql, new Object[] { id },
		new int[] { java.sql.Types.INTEGER }, new RowMapper<Student>() {

		    public Student mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			int id = rs.getInt(1);
			String name = rs.getString(2);
			int age = rs.getInt(3);
			return new Student(id, name, age);
		    }

		});

	return student;
    }

    public void truncateTable() {
	String sql = "truncate table student";
	jdbcTemplate.execute(sql);
    }
}

package edu.xmu.jdbc.dao;

import java.util.ArrayList;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import edu.xmu.jdbc.bean.Student;

public class QueryForObjectTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private QueryForObjectDao dao;

    @Before
    public void setUp() {
	dataSource = new DriverManagerDataSource(url, username, password);
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");

	dao = new QueryForObjectDao();
	dao.setDataSource(dataSource);

	List<Student> studentList = new ArrayList<Student>();
	Student student = new Student("Davy", 24);
	studentList.add(student);

	dao.prepareData(studentList);
    }

    @Test
    public void queryForObjectTest() {
	Student student = dao.queryForObject();
	System.out.println(student);
    }

    @Test
    public void queryForObject2Test() {
	Student student = dao.queryForObject2(1);
	System.out.println(student);
    }

    @Test
    public void queryForObject3Test() {
	Student student = dao.queryForObject3(1);
	System.out.println(student);
    }

    @Test
    public void queryForObject4Test() {
	Student student = dao.queryForObject4();
	System.out.println(student);
    }

    @Test
    public void queryForObject5Test() {
	Student student = dao.queryForObject5(1);
	System.out.println(student);
    }

    @Test
    public void queryForObject6Test() {
	Student student = dao.queryForObject6(1);
	System.out.println(student);
    }

    @After
    public void tearDown() {
	dao.truncateTable();
    }
}

 

3. queryForList

// Return a list of Map, with the key of column name, and value of column value.
// The methods below are especially userful when we don't have a domain model.
public List<Map<String, Object>> queryForList(String sql);
public List<Map<String, Object>> queryForList(String sql, Object... args);
public List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes);

// Return a list of Object, with the type of elementType.
// This method is meant to be a single column query.
// And the elementType can be only java embedded type.
public <T> List<T> queryForList(String sql, Class<T> elementType);
public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType);
public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args);
public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType);

// Although this method is not queryForList, but it is meant for quering for a 
// list of customized element.
public <T> List<T> query(String sql, RowMapper<T> rowMapper);

As we can see from API Doc:

1) If we want to return a list of primary element, we can use queryForList.

    But if we need return a list of customized element, we can use query(sql, rowMapper) instead.

2) The first segment of methods are appropriate when you don't have a domain model.

    They can be seen as an enhancement for queryForMap().

package edu.xmu.jdbc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import edu.xmu.jdbc.bean.Student;

public class QueryForListDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
	this.jdbcTemplate = new JdbcTemplate(this.dataSource);

    }

    public void prepareData(List<Student> studentList) {
	String sql = "insert into student(name, age) values (?, ?)";
	for (Student student : studentList) {
	    jdbcTemplate
		    .update(sql,
			    new Object[] { student.getName(), student.getAge() },
			    new int[] { java.sql.Types.VARCHAR,
				    java.sql.Types.INTEGER });
	}
    }

    public List<Map<String, Object>> queryForList(int id) {
	String sql = "select id, name, age from student where id<=?";
	List<Map<String, Object>> returnList = jdbcTemplate.queryForList(sql,
		new Object[] { id });

	return returnList;
    }

    public List<String> queryForList2(int id) {
	String sql = "select name from student where id<=?";
	List<String> returnList = jdbcTemplate.queryForList(sql,
		new Object[] { id }, String.class);
	return returnList;
    }

    /**
     * Although query is not query for list, <br/>
     * here it performs just like queryForList. <br/>
     * And we use queryForList can never get a list of customized bean.
     * 
     * @param id
     * @return
     */
    public List<Student> query(int id) {
	String sql = "select id, name, age from student where id<=?";

	List<Student> returnList = jdbcTemplate.query(sql, new Object[] { id },
		new RowMapper<Student>() {

		    public Student mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			int age = rs.getInt("age");
			return new Student(id, name, age);
		    }

		});

	return returnList;
    }

    public void truncateTable() {
	String sql = "truncate table student";
	jdbcTemplate.execute(sql);
    }
}

package edu.xmu.jdbc.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import edu.xmu.jdbc.bean.Student;

public class QueryForListTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private QueryForListDao dao;

    @Before
    public void setUp() {
	dataSource = new DriverManagerDataSource(url, username, password);
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");

	dao = new QueryForListDao();
	dao.setDataSource(dataSource);

	List<Student> studentList = new ArrayList<Student>();
	Student student = new Student("Davy", 24);
	studentList.add(student);
	student = new Student("Jones", 25);
	studentList.add(student);

	dao.prepareData(studentList);
    }

    @Test
    public void queryForListTest() {
	List<Map<String, Object>> resultList = dao.queryForList(2);

	for (Map<String, Object> resultMap : resultList) {
	    Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet();
	    for (Map.Entry<String, Object> entry : entrySet) {
		System.out.println(entry.getKey() + " = " + entry.getValue());
	    }
	}
    }

    @Test
    public void queryForList2Test() {
	List<String> nameList = dao.queryForList2(2);
	for (String name : nameList) {
	    System.out.println("name = " + name);
	}
    }

    @Test
    public void queryForList3Test() {
	List<Student> studentList = dao.query(2);
	for (Student student : studentList) {
	    System.out.println(student);
	}
    }

    @After
    public void tearDown() {
	dao.truncateTable();
    }
}

 

4. query

// ResultSetExtractor
public <T> T query(final String sql, final ResultSetExtractor<T> rse);
public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse);
public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse);

// RowMapper
public <T> List<T> query(String sql, RowMapper<T> rowMapper)
public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper);
public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper);

// RowCallBackHandler
public void query(String sql, RowCallbackHandler rch);
public void query(String sql, Object[] args, RowCallbackHandler rch);
public void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch);

We need to know the different representation of ResultSetExtractor & RowMapper & RowCallBackHandler

1> Example for ResultSetExtractor

2> Example for RowMapper

3> Example for RowCallbackHandler

package edu.xmu.jdbc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import edu.xmu.jdbc.bean.Student;

public class QueryDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
	this.jdbcTemplate = new JdbcTemplate(this.dataSource);

    }

    public void prepareData(List<Student> studentList) {
	String sql = "insert into student(name, age) values (?, ?)";
	for (Student student : studentList) {
	    jdbcTemplate
		    .update(sql,
			    new Object[] { student.getName(), student.getAge() },
			    new int[] { java.sql.Types.VARCHAR,
				    java.sql.Types.INTEGER });
	}
    }

    /**
     * If we want to get a Map<Integer, Student> <br/>
     * Key: StudentId, as it is identical <br/>
     * Value: Student <br/>
     * 
     * The problem is that if we use query(), <br/>
     * we will get List<Map<Integer, Student>> <br/>
     * Then we have to convert this structure to single map. <br/>
     * 
     * With ResultSetExtractor, we can avoid convertion <br/>
     * 
     * @param id
     */
    public Map<Integer, Student> queryWithResultSetExtractor(int id) {
	String sql = "select id, name, age from student where id <= ?";

	return jdbcTemplate.query(sql, new Object[] { id },
		new ResultSetExtractor<Map<Integer, Student>>() {

		    public Map<Integer, Student> extractData(ResultSet rs)
			    throws SQLException, DataAccessException {
			Map<Integer, Student> studentMap = new HashMap<Integer, Student>();

			while (rs.next()) {
			    int id = rs.getInt("id");
			    String name = rs.getString("name");
			    int age = rs.getInt("age");

			    Student student = new Student(id, name, age);
			    studentMap.put(id, student);
			}

			return studentMap;
		    }

		});
    }

    /**
     * Here we user RowMapper as a comparator with previous one. <br/>
     * We are sure that every map inside the list have only one entry. <br/>
     * 
     * @param id
     * @return
     */
    public List<Map<Integer, Student>> queryWithRowMapper(int id) {
	String sql = "select id, name, age from student where id<=?";

	return jdbcTemplate.query(sql, new Object[] { id },
		new RowMapper<Map<Integer, Student>>() {

		    public Map<Integer, Student> mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			Map<Integer, Student> map = new HashMap<Integer, Student>();

			int id = rs.getInt(1);
			String name = rs.getString(2);
			int age = rs.getInt(3);
			Student student = new Student(id, name, age);
			map.put(id, student);

			return map;
		    }

		});
    }

    public void truncateTable() {
	String sql = "truncate table student";
	jdbcTemplate.execute(sql);
    }
}

package edu.xmu.jdbc.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import edu.xmu.jdbc.bean.Student;

public class QueryDaoTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private QueryDao dao;

    @Before
    public void setUp() {
	dataSource = new DriverManagerDataSource(url, username, password);
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");

	dao = new QueryDao();
	dao.setDataSource(dataSource);

	List<Student> studentList = new ArrayList<Student>();
	Student student = new Student("Davy", 24);
	studentList.add(student);
	student = new Student("Jones", 25);
	studentList.add(student);

	dao.prepareData(studentList);
    }

    @Test
    public void queryWithResultSetExtractorTest() {
	Map<Integer, Student> studentMap = dao.queryWithResultSetExtractor(2);
	Set<Entry<Integer, Student>> entrySet = studentMap.entrySet();
	for (Entry<Integer, Student> entry : entrySet) {
	    int id = entry.getKey();
	    Student student = entry.getValue();
	    System.out.println("ID: " + id + ", Student: " + student);
	}
    }

    @Test
    public void queryWithRowMapperTest() {
	List<Map<Integer, Student>> list = dao.queryWithRowMapper(2);

	for (Map<Integer, Student> map : list) {
	    Set<Entry<Integer, Student>> set = map.entrySet();

	    for (Entry<Integer, Student> entry : set) {
		int id = entry.getKey();
		Student student = entry.getValue();
		System.out.println("ID: " + id + ", Student: " + student);
	    }
	}
    }

    @After
    public void tearDown() {
	dao.truncateTable();
    }
}

 

5. update

public int update(final String sql);
public int update(String sql, Object... args);
public int update(String sql, Object[] args, int[] argTypes);

1) Update can be used for CUD operation.

2) Return int represents the number of rows affected

package edu.xmu.jdbc.dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import edu.xmu.jdbc.bean.Student;

public class UpdateDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
	this.jdbcTemplate = new JdbcTemplate(this.dataSource);

    }

    public void prepareData(List<Student> studentList) {
	String sql = "insert into student(name, age) values (?, ?)";
	for (Student student : studentList) {
	    jdbcTemplate
		    .update(sql,
			    new Object[] { student.getName(), student.getAge() },
			    new int[] { java.sql.Types.VARCHAR,
				    java.sql.Types.INTEGER });
	}
    }

    public void updateForUpdate(int id, Student student) {
	String name = student.getName();
	int age = student.getAge();

	String sql = "update student set name=?, age=? where id=?";

	jdbcTemplate.update(sql, new Object[] { name, age, id });
    }

    public void updateForDelete(int id) {
	String sql = "delete from student where id=?";

	jdbcTemplate.update(sql, new Object[] { id });
    }

    public void truncateTable() {
	String sql = "truncate table student";
	jdbcTemplate.execute(sql);
    }
}

package edu.xmu.jdbc.dao;

import java.util.ArrayList;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import edu.xmu.jdbc.bean.Student;

public class UpdateDaoTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private UpdateDao dao;

    @Before
    public void setUp() {
	dataSource = new DriverManagerDataSource(url, username, password);
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");

	dao = new UpdateDao();
	dao.setDataSource(dataSource);

	List<Student> studentList = new ArrayList<Student>();
	Student student = new Student("Davy", 24);
	studentList.add(student);
	student = new Student("Jones", 25);
	studentList.add(student);

	dao.prepareData(studentList);
    }

    @Test
    public void updateForUpdateTest() {
	int id = 1;
	String updatedName = "Caly";
	int updatedAge = 22;

	Student student = new Student(updatedName, updatedAge);

	dao.updateForUpdate(id, student);
    }

    @Test
    public void updateForDeleteTest() {
	dao.updateForDelete(2);
    }

    @After
    public void tearDown() {
	dao.truncateTable();
    }
}

 

6. Depreciated methods

@Deprecated
public int queryForInt(String sql);
@Deprecated
public int queryForInt(String sql, Object... args);
@Deprecated
public int queryForInt(String sql, Object[] args, int[] argTypes)

@Deprecated
public long queryForLong(String sql);
@Deprecated
public long queryForLong(String sql, Object... args);
@Deprecated
public long queryForLong(String sql, Object[] args, int[] argTypes)

We can find the reason why they are depreciated by following url listed below.

 

7. DDL Execution

// This method is often used for DDL statements
public void execute(final String sql);

As we can infer from the official document that as convention, we use execute(String) method to execute ddl.

But executing DDL programatically is depreciated.

It is a good practice to only execute CRUD in program and left DDL to be executed by DBA.

package edu.xmu.jdbc.dao;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class DDLDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
	this.jdbcTemplate = new JdbcTemplate(this.dataSource);

    }

    public void createTable() {
	String sql = "create table student_bak (id int primary key auto_increment, name varchar(100), age int)";
	jdbcTemplate.execute(sql);
    }

    public void deleteTable() {
	String sql = "drop table student_bak";
	jdbcTemplate.execute(sql);
    }

    public void alterTable() {
	String sql = "alter table student_bak add score decimal, add gender varchar(100)";
	jdbcTemplate.execute(sql);
    }
}

package edu.xmu.jdbc.dao;

import org.junit.Before;
import org.junit.Ignore;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class DDLDaoTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private DDLDao dao;

    @Before
    public void setUp() {
	dataSource = new DriverManagerDataSource(url, username, password);
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");

	dao = new DDLDao();
	dao.setDataSource(dataSource);
    }

    @Test
    public void createTableTest() {
	dao.createTable();
    }

    @Test
    @Ignore
    public void deleteTableTest() {
	dao.deleteTable();
    }

    @Test
    public void alterTableTest() {
	dao.alterTable();
    }
}

 

 

Reference Links:

1) http://stackoverflow.com/questions/15661313/jdbctemplate-queryforint-long-is-deprecated-in-spring-3-2-2-what-should-it-be-r Why queryForInt/Long are depreciated?

2) http://docs.spring.io/spring/docs/3.1.x/spring-framework-reference/html/jdbc.html Spring JDBC Template official document.