SpringJdbc
9081 ワード
、JdbcTemplate
, :
public Object queryForObject(String sql, Object[] args, RowMapper rowMapper), UserRowMapper 。
RowMapper BeanPropertyRowMapper, Java Bean( Java Bean , )。
public List query(String sql, Object[] args, RowMapper rowMapper) 。
public int queryForInt(String sql)( :select count(*) from user), String queryForObject 。
public Map queryForMap(String sql, Object[] args) Map(key: ,value: )。
public List queryForList(String sql, Object[] args) Map。
public int update(String sql, Object[] args)。
:
public Object execute(ConnectionCallback action)
public class JdbcTemplateTest {
// JdbcTemplate
static JdbcTemplate jdbc = new JdbcTemplate(JdbcUtils.getDataSource());
public static void main(String[] args) {
User user = findUser("zhangsan");
// System.out.println("user:" + user);
// System.out.println("users:" + findUsers(3));
// System.out.println("user count:" + getUserCount());
// System.out.println("user name:" + getUserName(1));
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 };
// BeanPropertyRowMapper , ( as xxx), ,
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;
}
}
、NamedParameterJdbcTemplate
lNamedParameterJdbcTemplate JdbcTemplate, JdbcTemplate NamedParameterJdbcTemplate ; NamedParameterJdbcTemplate JdbcTemplate 。
lpublic Object queryForObject(String sql, Map paramMap, RowMapper rowMapper)
lpublic Object queryForObject(String sql, SqlParameterSource paramSource, RowMapper rowMapper)
SqlParameterSource MapSqlParameterSource
BeanPropertySqlParameterSource
lpublic int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder) 。
public class NamedJdbcTemplate {
// JdbcTemplate
static JdbcTemplate jdbc = new JdbcTemplate(JdbcUtils.getDataSource());
static NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(
JdbcUtils.getDataSource());
public static void main(String[] args) {
User user=new User();
user.setMoney(10);
user.setId(2);
// System.out.println(findUser(user));
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);// user , sql
KeyHolder keyHolder=new GeneratedKeyHolder();
named.update(sql, ps, keyHolder);
int id=keyHolder.getKey().intValue();//
user.setId(id);
Map map=keyHolder.getKeys();
}
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;
}
static User findUser(User user) {
String sql = "select id, name, money, birthday from user where money>:m and id<:id";
Object[] args = new Object[] {user.getName(),user.getMoney(),user.getId() };
Map params=new HashMap();
params.put("m", user.getMoney());
params.put("id", user.getId());
Object u=named.queryForObject(sql, params, new BeanPropertyRowMapper(User.class));
return (User) u;
}
、 SimpleJdbcTemplate
SimpleJdbcTemplate NamedParameterJdbcTemplate; NamedParameterJdbcTemplate SimpleJdbcTemplate ,SimpleJdbcTemplate NamedParameterJdbcTemplate JDK5.0 。
public <T> List<T> query(String sql, ParameterizedRowMapper<T> rm, Object... args)
public <T> T queryForObject(String sql, ParameterizedRowMapper<T> rm, SqlParameterSource args)
public <T> List<T> query(String sql, ParameterizedRowMapper<T> rm, SqlParameterSource args)
getJdbcOperations JdbcOperations( JdbcTemplate)
getNamedParameterJdbcOperations NamedParameterJdbcOperations( NamedParameterJdbcTemplate)
public class UserDaoSpringImpl implements UserDao {
private SimpleJdbcTemplate simpleJdbcTemplate=new SimpleJdbcTemplate(JdbcUtils.getDataSource());
@Override
public void addUser(User user) {
String sql="insert into user (name,money,birthday) value (:name,:money,:birthday)";
SqlParameterSource param=new BeanPropertySqlParameterSource(user);
KeyHolder keyHolder=new GeneratedKeyHolder();
this.simpleJdbcTemplate.getNamedParameterJdbcOperations().update(sql, param, keyHolder);
user.setId(keyHolder.getKey().intValue());
}
@Override
public User getUser(int userId) {
String sql="selec id,name,money,birthday from where id=?";
return this.simpleJdbcTemplate.queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(User.class), userId);
}
@Override
public User findUser(String name, String password) {
String sql="selec id,name,money,birthday from where name=?";
return this.simpleJdbcTemplate.queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(User.class), name);
}
@Override
public void update(User user) {
// String sql="update user set name=?,birthday=?,money=? where id=?";
// this.simpleJdbcTemplate.update(sql, user.getName(),user.getBirthday(),user.getMoney(),user.getId());
String sql="update user set name=:name,birthday=:birthday,money=:money where id=:id";
this.simpleJdbcTemplate.update(sql,new BeanPropertySqlParameterSource(user));
}
@Override
public void delete(User user) {
String sql="delete from user where id="+user.getId();
this.simpleJdbcTemplate.update(sql, user.getId());
}
}