jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");
使用JdbcTemplate进行查询时,可以使用queryForXXX()等方法,例如使用queryForInt()方法传回user表格中的数据数目： jdbcTemplate.quertForInt("select count(*) from user"); 也可以使用queryForObject()传回一个查询后的对象,例如传回一个String对象：
String name=(String)jdbcTemplate.queryForObject("selcet name from user where id=?",new Object[]{id},java.lang.String.class);)
单独查询某个数据并赋值给特定对象时：
public BaseObj getBaseObj(final int ID) {
String sql = "select * from " + DB_TABLE_NAME + " where NewsId=" + ID;
BaseObj obj =(BaseObj) getJdbcTemplate().query(sql,new ResultSetExtractor(){
public Object extractData(ResultSet rs) throws SQLException,DataAccessException {
if (rs.next()) {
NewsObj news = new NewsObj();
news.setID(rs.getInt("NewsID"));
news.setTitle(rs.getString("NewsTitle"));
news.setBigClass(rs.getInt("BigClassId"));
news.setNewsContent(rs.getString("NewsContent"));
news.setNewsKey(rs.getString("NewsKey"));
news.setNewsAuthor(rs.getString("NewsAuthor"));
news.setImg(rs.getBoolean("isImg"));
news.setNewsFrom(rs.getString("NewsFrom"));
return news;
}
return null;
}
});
return obj;
}
上面两个例子都是传回单独一笔数据,如果要传回多笔数据,则可以使用queryForList()方法
例如：
List rows=jdbcTemplate().queryForList("select * from user where id="+id.intValue()); 传回的list中包括的是map对象,每个map对象代表查询结果中的一笔数据,每笔数据包括多个字段,要取得字段中的值,就要使用字段名作为key,
例如：
Iterator it=rows.iterator();
while(it.hasNext()){
Map result=(Map)it.next();
System.out.println(userMap.get("id"));
System.out.println(userMap.get("name"));
System.out.println(userMap.get("age"));
}
在查询的同时,你可以在返回结果之前先进行一些处理,这就要实现RowCallbackHandler接口
public User find(Integer id){
final User user=new User();
jdbcTemplate.query("select * from user where id=?",new Object[]{id},
new RowCallBackHandler(){
public void proccessRow(ResultSet rs){
user.setId(new Integer(rs.getInt("id")));
uset.setName(rs.getString("name"));
}});
return user;
}
如果一次要返回多个查询结果对象,则可以实现RowMapper接口
public class UserRowMapper implements RowMapper{
public Object MapRow(ResultSet rsmint rowNum) throws SQLException{
User user=new User();
user.setId(new Integer(rs.getInt("id")));
user.setName(rs.getString("name"));
return user;
}
}
使用RowMapper查询单笔数据
public User find(Integer id){
User user=(User)jdbcTemplate.queryForObject("select * from user where id=?",new Object[]{id},new UserRowMapper());
return user;
}
使用RowMapper查询多笔数据
class UserRowMapper implements RowMapper {
public Object mapRow(ResultSet rs,int index) throws SQLException
{
User u = new User();
u.setId(rs.getString(”ID”));
u.setName(rs.getString(”Name”));
u.setPassword(rs.getString(”Password”));
return u;
}
}
public List select(String where)
{
List list;
String sql = "select * from admin "+where;
list = jdbcTemplate.query(sql,new RowMapperResultReader(new UserRowMapper()));
return list;
}
传回的users对象中,包括了从数据库查询出来的结果,并已经封装成user对象
JdbcTemplate语句使用:
1、使用JdbcTemplate的execute()方法执行SQL语句
代码
jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");
2、如果是UPDATE或INSERT,可以用update()方法。
代码
jdbcTemplate.update("INSERT INTO USER VALUES('"
+ user.getId() + "', '"
+ user.getName() + "', '"
+ user.getSex() + "', '"
+ user.getAge() + "')");
3、带参数的更新
代码
jdbcTemplate.update("UPDATE USER SET name = ? WHERE user_id = ?", new Object[] {name, id});
代码
jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)", new Object[] {user.getId(), user.getName(), user.getSex(), user.getAge()});
4、使用JdbcTemplate进行查询时,使用queryForXXX()等方法
代码
int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER");
代码
String name = (String) jdbcTemplate.queryForObject("SELECT name FROM USER WHERE user_id = ?", new Object[] {id}, java.lang.String.class);
代码
List rows = jdbcTemplate.queryForList("SELECT * FROM USER");
代码
List rows = jdbcTemplate.queryForList("SELECT * FROM USER");
Iterator it = rows.iterator();
while(it.hasNext()) {
Map userMap = (Map) it.next();
System.out.print(userMap.get("user_id") + "\t");
System.out.print(userMap.get("name") + "\t");
System.out.print(userMap.get("sex") + "\t");
System.out.println(userMap.get("age") + "\t");
}
JdbcTemplate将我们使用的JDBC的流程封装起来,包括了异常的捕捉、SQL的执行、查询结果的转换等等。spring大量使用Template Method模式来封装固定流程的动作,XXXTemplate等类别都是基于这种方式的实现。
除了大量使用Template Method来封装一些底层的操作细节,spring也大量使用callback方式类回调相关类别的方法以提供JDBC相关类别的功能,使传统的JDBC的使用者也能清楚了解spring所提供的相关封装类别方法的使用。
JDBC的PreparedStatement
代码
final String id = user.getId();
final String name = user.getName();
final String sex = user.getSex() + "";
final int age = user.getAge();
jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)",
new PreparedStatementSetter() {
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, id);
ps.setString(2, name);
ps.setString(3, sex);
ps.setInt(4, age);
}
});
代码
final User user = new User();
jdbcTemplate.query("SELECT * FROM USER WHERE user_id = ?",
new Object[] {id},
new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
user.setId(rs.getString("user_id"));
user.setName(rs.getString("name"));
user.setSex(rs.getString("sex").charAt(0));
user.setAge(rs.getInt("age"));
}
});
代码
class UserRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int index) throws SQLException {
User user = new User();
user.setId(rs.getString("user_id"));
user.setName(rs.getString("name"));
user.setSex(rs.getString("sex").charAt(0));
user.setAge(rs.getInt("age"));
return user;
}
}
public List findAllByRowMapperResultReader() {
String sql = "SELECT * FROM USER";
return jdbcTemplate.query(sql, new RowMapperResultReader(new UserRowMapper()));
}
在getUser(id)里面使用UserRowMapper
代码
public User getUser(final String id) throws DataAccessException {
String sql = "SELECT * FROM USER WHERE user_id=?";
final Object[] params = new Object[] { id };
List list = jdbcTemplate.query(sql, params, new RowMapperResultReader(new UserRowMapper()));
return (User) list.get(0);
}
网上收集
org.springframework.jdbc.core.PreparedStatementCreator 返回预编译SQL 不能于Object[]一起用
代码
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
return con.prepareStatement(sql);
}
1.增删改
org.springframework.jdbc.core.JdbcTemplate 类(必须指定数据源dataSource)
代码
template.update("insert into web_person values(?,?,?)",Object[]);
或
代码
template.update("insert into web_person values(?,?,?)",new PreparedStatementSetter(){ 匿名内部类 只能访问外部最终局部变量
public void setValues(PreparedStatement ps) throws SQLException {
ps.setInt(index++,3);
});
org.springframework.jdbc.core.PreparedStatementSetter 接口 处理预编译SQL
代码
public void setValues(PreparedStatement ps) throws SQLException {
ps.setInt(index++,3);
}
2.查询JdbcTemplate.query(String,[Object[]/PreparedStatementSetter],RowMapper/RowCallbackHandler)
org.springframework.jdbc.core.RowMapper 记录映射接口 处理结果集
代码
public Object mapRow(ResultSet rs, int arg1) throws SQLException { int表当前行数
person.setId(rs.getInt("id"));
}
List template.query("select * from web_person where id=?",Object[],RowMapper);
org.springframework.jdbc.core.RowCallbackHandler 记录回调管理器接口 处理结果集
代码
template.query("select * from web_person where id=?",Object[],new RowCallbackHandler(){
public void processRow(ResultSet rs) throws SQLException {
person.setId(rs.getInt("id"));
});
Spring-JdbcTemplate 总结
1． 查询单个对象:
a) 基本类型
================================================
下面两个方法主要适用于静态sql
◆ public Object queryForObject(String sql, RowMapper rowMapper)
◆ public Object queryForObject(String sql, Class requiredType)
注意：requiredType 只能是基本类型的class,只针对基本类型有效
◆ public Object queryForObject(String sql, Object[] args, Class requiredType)
◆ public Object queryForObject(String sql, Object[] args, RowMapper rowMapper)
◆ public Object queryForObject(String sql, Object[] args, int[] argTypes, Class requiredType)
注：其中argTypes 可以使用java.sql.Types常量类中的常量值
◆ public Object queryForObject(String sql, Object[] args, int[] argTypes, RowMapper rowMapper)
注： rowMapper是一个回调接口
以下主要用于查询数值对象
◆ public int queryForInt(String sql, Object[] args)
◆ public long queryForLong(String sql, Object[] args)
==================================================
下面的方法将返回的各个列封装成map对象，key为列名，value为列值.只能一行数据，不能返回多行数据.
◆ public Map queryForMap(String sql, Object[] args)
b) 引用类型
◆ public Object queryForObject(String sql, RowMapper rowMapper)
◆ public Object queryForObject(String sql, Object[] args, RowMapper rowMapper)
◆ public Map queryForMap(String sql, Object[] args)
2． 查询多个对象
a) 基本类型:
◆ public List queryForList(String sql, Object[] args)
注：返回的list的每个元素都是一个map对象,该map对象封装了一行数据且只有一行数据，包括要查询的列字段
b) 引用类型:
◆ public List queryForList(String sql, Object[] args)
Iterator iter = this.jdbcTemplate.queryForList(sql,new Object[]{}).iterator();
List list = new ArrayList();
while(iter.hasNext()){
User user = new User();
Map map = (Map) iter.next();
Set<Entry<String, String>> set = map.entrySet();
Iterator<Entry<String, String>> iter2 = set.iterator();
Entry<String, String> entry = iter2.next();
user.setUsername(entry.getValue());
entry = iter2.next();
user.setPassword(entry.getValue());
list.add(user);
}
◆ public List query(String sql, Object[] args, RowMapper rowMapper)
注：返回的list 中的元素可以是引用类型的对象.
◆ public Object execute(ConnectionCallback action)
注：使用回调接口.将各个字段值封装成引用类型的对象，然后以list方式返回.
3． 修改操作.适用于增、删、改
◆ public int update(String sql, Object[] args)
4. 批处理
String sql = "insert into user(username,password) values(?,?)";
final List list = new ArrayList();
for(int i = 0;i < 5 ; i++){
User user = new User();
user.setPassword("dog" + i);
user.setUsername("dog" + i);
list.add(user);
}
int [] rst = this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
public int getBatchSize() {
return list.size();
}
public void setValues(PreparedStatement ps, int i)
throws SQLException {
User user = (User) list.get(i);
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
}
});
*************************************************************************************************************************************************
- 实现一、在内部建立内联类实现<SPANclass=hilite1>RowMapper</SPAN>接口
- packagehysteria.contact.dao.impl;
- importjava.sql.ResultSet;
- importjava.sql.SQLException;
- importjava.sql.Types;
- importjava.util.List;
- importorg.springframework.jdbc.core.JdbcTemplate;
- importorg.springframework.jdbc.core.<SPANclass=hilite1>RowMapper</SPAN>;
- importhysteria.contact.dao.ItemDAO;
- importhysteria.contact.domain.Item;
- publicclassItemDAOImplimplementsItemDAO {
- privateJdbcTemplate jdbcTemplate;
- publicvoidsetJdbcTemplate(JdbcTemplate jdbcTemplate) {
- this.jdbcTemplate = jdbcTemplate;
- }
- publicItem insert(Item item) {
- String sql ="INSERT INTO items(user_id,name,phone,email) VALUES(?,?,?,?)";
- Object[] params =newObject[]{item.getUserId(),item.getName(),item.getPhone(),item.getEmail()};
- int[] types =newint[]{Types.INTEGER,Types.VARCHAR,Types.CHAR,Types.VARCHAR};
- jdbcTemplate.update(sql,params,types);
- returnitem;
- }
- publicItem update(Item item) {
- String sql ="UPDATE items SET name = ?, phone = ?, email = ? WHERE id = ?";
- Object[] params =newObject[] {item.getName(),item.getPhone(),item.getEmail(),item.getId()};
- int[] types =newint[] {Types.VARCHAR,Types.CHAR,Types.VARCHAR,Types.VARCHAR,Types.INTEGER};
- jdbcTemplate.update(sql,params,types);
- returnitem;
- }
- publicvoiddelete(Item item) {
- String sql ="DELETE FROM items WHERE id = ?";
- Object[] params =newObject[] {item.getId()};
- int[] types =newint[]{Types.INTEGER};
- jdbcTemplate.update(sql,params,types);
- }
- publicItem findById(intid) {
- String sql ="SELECT * FROM items WHERE id = ?";
- Object[] params =newObject[] {id};
- int[] types =newint[] {Types.INTEGER};
- List items = jdbcTemplate.query(sql,params,types,newItemMapper());
- if(items.isEmpty()){
- returnnull;
- }
- return(Item)items.get(0);
- }
- publicList<Item> findAll() {
- String sql ="SELECT * FROM items";
- returnjdbcTemplate.query(sql,newItemMapper());
- }
- publicList<Item> findAllByUser(intuser_id) {
- String sql ="SELECT * FROM items WHERE user_id = ?";
- Object[] params =newObject[]{user_id};
- int[] types =newint[]{Types.INTEGER};
- List items = jdbcTemplate.query(sql,params,types,newItemMapper());
- returnitems;
- }
- protectedclassItemMapperimplements<SPANclass=hilite1>RowMapper</SPAN> {
- publicObject mapRow(ResultSet rs,introwNum)throwsSQLException {
- Item item =newItem();
- item.setId(rs.getInt("id"));
- item.setUserId(rs.getInt("user_id"));
- item.setName(rs.getString("name"));
- item.setPhone(rs.getString("phone"));
- item.setEmail(rs.getString("email"));
- returnitem;
- }
- }
- }
<转：http://hanhongke123.blog.163.com/blog/static/6222349420122135204201/>
扫一扫
350
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
