spring jdbcTemplate 增删改查

xiaoxiao2021-03-25 58

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/>

转载请注明原文地址: https://ju.6miu.com/read-35083.html

技术

最新回复(0)