转载文章一:
1、使用JdbcTemplate的execute()方法执行SQL语句
- jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");
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() + "')");
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()});
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");
- }
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()));
- }
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);
- }
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);
- }
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);
- });
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);
- }
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);
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"));
- });
template.query("select * from web_person where id=?",Object[],new RowCallbackHandler(){ public void processRow(ResultSet rs) throws SQLException { person.setId(rs.getInt("id")); });
转载文章二:
首先在applicationContext.xml中配置数据源dataSource,jdbcTemplate,dao。
<!-- 配置数据源-->
<bean id="dataSource" scope="singleton" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://192.168.1.3:8088/test/>
<property name="username" value="root"/>
<property name="password" value="123"/>
............
</bean>
<!--配置jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" scope="singleton"/>
<!--配置DAO-->
<bean id="fillQuestionDAO" class="com.biao.dao.FillQuestionDAO" scope="singleton"/>
在具体DAO中:FillQuestionDAO
public class FillQuestionDAO implements IFillQuestionDAO{
/**
* 得到一个数据库连接
*/
private JdbcTemplate jdbcTemplate;
/**
* @return 获得 jdbcTemplate
*/
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
/**
* @param jdbcTemplate 设置 jdbcTemplate
*/
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
* 功能:增加新的临时填空题
*/
public Long addFillQuestion(final FillQuestion fillQuestion) {
final StringBuffer sql=new StringBuffer();
sql.append(" insert into filltemp");
sql.append("(contentTempID,fillTemp,answerOne,parseOne) ");
sql.append(" values(?,?,?,?); ");
//临时填空题表中的id为auto_increment,所以可以以下步骤
GeneratedKeyHolder gkHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator(){
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException {
PreparedStatement ps=con.prepareStatement(sql.toString());
ps.setLong(1, fillQuestion.getContentTempID());
ps.setString(2, fillQuestion.getFillTemp());
ps.setString(3, fillQuestion.getAnswerOne());
ps.setString(4, fillQuestion.getParseOne());
return ps;
}
},gkHolder);
return gkHolder.getKey().longValue();
}
//如果id不为自增,无返回值
public void addFillQuestion(final FillQuestion fillQuestion) {
final StringBuffer sql=new StringBuffer();
sql.append(" insert into filltemp");
sql.append("(contentTempID,fillTemp,answerOne,parseOne) ");
sql.append(" values(?,?,?,?); ");
jdbcTemplate.update(sql.toString(),new PreparedStatementSetter(){
public void setValues(PreparedStatement ps) throws SQLException {
ps.setLong(1, fillQuestion.getContentTempID());
ps.setString(2, fillQuestion.getFillTemp());
ps.setString(3, fillQuestion.getAnswerOne());
ps.setString(4, fillQuestion.getParseOne());
}
});
}
/* 功能:删除临时填空题
* @see com.huazuo.bw.dao.question1_5.IFillQuestionDAO#deleteFillQuestion(com.huazuo.bw.po.FillQuestion)
*/
public void deleteFillQuestion(final long fillQuestionID) {
String sql="delete from filltemp where fillTempID=?";
jdbcTemplate.update(sql,new PreparedStatementSetter(){
public void setValues(PreparedStatement ps) throws SQLException {
ps.setLong(1,fillQuestionID);
}
});
}
/**
* 功能:更新临时填空题
* @param fillQuestion
*/
public void updateFillQuestion(final FillQuestion fillQuestion) {
StringBuffer sql=new StringBuffer();
sql.append("update filltemp set fillTempID=?,contentTempID=?,fillTemp=?,answerOne=?,parseOne=?");
sql.append(" where fillTempID=?");
jdbcTemplate.update(sql.toString(),new PreparedStatementSetter(){
public void setValues(PreparedStatement pstmt) throws SQLException {
int index=1;
pstmt.setLong(index++, fillQuestion.getFillQuestionID());
pstmt.setLong(index++,fillQuestion.getContentTempID());
pstmt.setString(index++, fillQuestion.getFillTemp());
pstmt.setString(index++, fillQuestion.getAnswerOne());
pstmt.setString(index++, fillQuestion.getParseOne());
pstmt.setLong(index++,fillQuestion.getFilQuestionID());
}
});
}
/**
* 功能:通过一个临时填空题ID来查询这个临时填空
* @param fillQuestionID
* @return
*/
public FillQuestion findFillQuestion(final Long fillQuestionID) {
String sql="select fillTempID,contentTempID,fillTemp,answerOne,parseOne from fillTemp where fillTempID=?";
return (FillQuestion) jdbcTemplate.query(sql, new PreparedStatementSetter(){
public void setValues(PreparedStatement ps) throws SQLException {
ps.setLong(1, fillQuestionID);
}
},new RowMapper(){
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
FillQuestion fillQuestion=new FillQuestion();
fillQuestion.setFillQuestionID(rs.getLong(1));
fillQuestion.setContentTempID(rs.getLong(2));
fillQuestion.setFillTemp(rs.getString(3));
fillQuestion.setAnswerOne(rs.getString(4) );
fillQuestion.setParseOne(rs.getString(5));
return fillQuestion;
}
}).get(0);
}
/**功能:查询出所有的填空
* @return
*/
public List<FillQuestion> findAllFillQuestion() {
String sql="select fillTempID,contentTempID,fillTemp,answerOne,parseOne from fillTemp";
List<FillQuestion> list=null;
list=jdbcTemplate.query(sql, new PreparedStatementSetter(){
public void setValues(PreparedStatement pstmt) throws SQLException {
}},new RowMapper(){
public Object mapRow(ResultSet rs, int arg1)
throws SQLException {
FillQuestion fillQuestion=new FillQuestion();
fillQuestion.setFillQuestionID(rs.getLong(1));
fillQuestion.setContentTempID(rs.getLong(2));
fillQuestion.setFillTemp(rs.getString(3));
fillQuestion.setAnswerOne(rs.getString(4) );
fillQuestion.setParseOne(rs.getString(5));
return fillQuestion;
}
});
return list;
}
}
/**
* 功能:查询某个用户的购物点数余额
* @param userID:用户ID
* @return
*/
public double getShoppingPointsBalance(long userID) {
String sql="select shoppingPointsBalance from useraccount where userID=?";
String result=(String)jdbcTemplate.queryForObject(sql.toString(),new Object[]{userID},,java.lang.String.class);
double points=Double.parseDouble(result);
return points;
}
1)执行查询:
除了execute方法之外,还有其他大量的查询方法。在这些查询方法中,有很大一部分是用来查询单值的,比如返回一个汇总(count)结果或者从返回行结果中取得指定列的值。可以用queryForInt(..),queryForLong(..)或queryForObject(..)方法。 queryForObject方法用来将返回的JDBC类型对象转换成指定的java对象。
int count=jdbcTemplate.queryForInt("select count(*) from mytable");
String name=(String)jdbcTemplate.queryForObject("select name from mytable",String.class);
除了返回单值的查询方法,还有一组返回List结果的方法。如queryForList.
List rows=jdbcTemplate.queryForList("select * from mytable");
[{name=Bob,id=1},{name=Mary,id=2}]
2)更新数据库
jdbcTemplate.update("update mytable set name=? where id=?",new Object[]{name,new Integer(id)});