spring's JdbcTemplate, NamedParameterJdbcTemplate and SimpleJdbcTemplate(转)
参考：http://static.springsource.org/spring/docs/2.5.x/reference/jdbc.html#jdbc-JdbcTemplate-idioms
Use spring their operation on the database, you can use these three database operations template.
The method in JdbcTemplate main transmission sql, and array parameters, the method requires an array of sql parameters placeholder and position need to correspond to the reference code:
public class JdbcTemplateTest {
static JdbcTemplate jdbc = new JdbcTemplate(JdbcUtils.getDataSource());
/**
* @param args
*/
public static void main(String[] args) {
User user = findUser("zhangsan");
System.out.println("data:" + getData(1));
}
static int addUser(final User user) {
jdbc.execute(new ConnectionCallback() {
public Object doInConnection(Connection con) throws SQLException,
DataAccessException {
String sql = "insert into user(name,birthday, money) values (?,?,?) ";
PreparedStatement ps = con.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getName());
ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
ps.setFloat(3, user.getMoney());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next())
user.setId(rs.getInt(1));
return null;
}
});
return 0;
}
static Map getData(int id) {
String sql = "select id as userId, name, money, birthday from user where id="
+ id;
return jdbc.queryForMap(sql);
}
static String getUserName(int id) {
String sql = "select name from user where id=" + id;
Object name = jdbc.queryForObject(sql, String.class);
return (String) name;
}
static int getUserCount() {
String sql = "select count(*) from user";
return jdbc.queryForInt(sql);
}
static List findUsers(int id) {
String sql = "select id, name, money, birthday from user where id<?";
Object[] args = new Object[] { id };
int[] argTypes = new int[] { Types.INTEGER };
List users = jdbc.query(sql, args, argTypes, new BeanPropertyRowMapper(
User.class));
return users;
}
static User findUser(String name) {
String sql = "select id, name, money, birthday from user where name=?";
Object[] args = new Object[] { name };
Object user = jdbc.queryForObject(sql, args, new BeanPropertyRowMapper(
User.class));
return (User) user;
}
static User findUser1(String name) {
String sql = "select id, name, money, birthday from user where name=?";
Object[] args = new Object[] { name };
Object user = jdbc.queryForObject(sql, args, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setMoney(rs.getFloat("money"));
user.setBirthday(rs.getDate("birthday"));
return user;
}
});
return (User) user;
}
}
Description:
1, in the excute method can pass a ConnectionCallback callback interface, the interface method will obtain the connection object that can be customized to operate.
2, the result set of packages you can use the spring of RowMapper interface object, you can use rowBeanPropertyRowMapper, the only class of an object can be passed.
NamedParameterJdbcTemplate is JdbcTemplate was packaged, the main multi-layer analysis of the parameters, sql using special combinations of placeholder, the parameter mainly use map, so sql data placeholders and parameters do not need to be in the order of 11 correspondence, reference code:
public class NamedJdbcTemplate {
static NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(
JdbcUtils.getDataSource());
/**
* @param args
*/
public static void main(String[] args) {
User user = new User();
user.setMoney(10);
user.setId(2);
System.out.println(findUser1(user));
}
static void addUser(User user) {
String sql = "insert into user(name,birthday, money) values (:name,:birthday,:money) ";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
KeyHolder keyHolder = new GeneratedKeyHolder();
named.update(sql, ps, keyHolder);
int id = keyHolder.getKey().intValue();
user.setId(id);
Map map = keyHolder.getKeys();
}
static User findUser(User user) {
String sql = "select id, name, money, birthday from user "
+ "where money > :m and id < :id";
Map params = new HashMap();
// params.put("n", user.getName());
params.put("m", user.getMoney());
params.put("id", user.getId());
Object u = named.queryForObject(sql, params, new BeanPropertyRowMapper(
User.class));
return (User) u;
}
static User findUser1(User user) {
String sql = "select id, name, money, birthday from user "
+ "where money > :money and id < :id";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
Object u = named.queryForObject(sql, ps, new BeanPropertyRowMapper(
User.class));
return (User) u;
}
}
Key points Description: SqlParameterSource ps = new BeanPropertySqlParameterSource(user); can use SqlParameterSource to pass an object to a placeholder on the sql to fill in values, KeyHolder keyHolder = new GeneratedKeyHolder(); to capture the generated primary key value.
SimpleJdbcTemplate built on JDK1.5 version used above, which encapsulates a NamedParameterJdbcTemplate, mainly to add support for variable-length parameters.
public class NamedJdbcTemplate {
static NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(
JdbcUtils.getDataSource());
/**
* @param args
*/
public static void main(String[] args) {
User user = new User();
user.setMoney(10);
user.setId(2);
System.out.println(findUser1(user));
}
static void addUser(User user) {
String sql = "insert into user(name,birthday, money) values (:name,:birthday,:money) ";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
KeyHolder keyHolder = new GeneratedKeyHolder();
named.update(sql, ps, keyHolder);
int id = keyHolder.getKey().intValue();
user.setId(id);
Map map = keyHolder.getKeys();
}
static User findUser(User user) {
String sql = "select id, name, money, birthday from user "
+ "where money > :m and id < :id";
Map params = new HashMap();
// params.put("n", user.getName());
params.put("m", user.getMoney());
params.put("id", user.getId());
Object u = named.queryForObject(sql, params, new BeanPropertyRowMapper(
User.class));
return (User) u;
}
static User findUser1(User user) {
String sql = "select id, name, money, birthday from user "
+ "where money > :money and id < :id";
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
Object u = named.queryForObject(sql, ps, new BeanPropertyRowMapper(
User.class));
return (User) u;
}
}
