欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-04-27 21:18:12
...

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://*.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.