本文共 8909 字,大约阅读时间需要 29 分钟。
一、首先配置JdbcTemplate;
要使用Jdbctemplate 对象来完成jdbc 操作。通常情况下,有三种种方式得到JdbcTemplate 对象。
第一种方式:我们可以在自己定义的DAO 实现类中注入一个DataSource 引用来完 成JdbcTemplate 的实例化。也就是它是从外部“注入” DataSource 到DAO 中,然后 自己实例化JdbcTemplate,然后将DataSource 设置到JdbcTemplate 对象中。
第二种方式: 在 Spring 的 IoC 容器中配置一个 JdbcTemplate 的 bean,将 DataSource 注入进来,然后再把JdbcTemplate 注入到自定义DAO 中。
第三种方式: Spring 提供了 org.springframework.jdbc.core.support.JdbcDaoSupport 类 , 这 个 类 中 定 义 了 JdbcTemplate 属性,也定义了DataSource 属性,当设置DataSource 属性的时候,会创 建jdbcTemplate 的实例,所以我们自己编写的DAO 只需要继承JdbcDaoSupport 类, 然后注入DataSource 即可。
提倡采用第三种方法。虽然下面的用法中采用了前两种方法
配置方法有3种:
1、
- publicclassUserServiceImplimplementsUserService{
-
- privateJdbcTemplatejdbcTemplate;
-
- publicJdbcTemplategetJdbcTemplate(){
- returnjdbcTemplate;
- }
-
-
- publicvoidsetJdbcTemplate(JdbcTemplatejdbcTemplate){
- this.jdbcTemplate=jdbcTemplate;
- }
-
-
- }
spring配置文件为:
- <beanid="jdbcTemplate"class="org.springframework.jdbc.core.JdbcTemplate">
- <propertyname="dataSource"ref="dataSource">
- </bean>
- <beanid="userService"class="com.hxzy.account.jdbcTemplate.UserServiceImpl">
- <propertyname="jdbcTemplate"ref="jdbcTemplate"/>
- </bean>
方法2、
- publicclassUserServiceImplimplementsUserService{
-
- privateJdbcTemplatejdbcTemplate;
-
-
- publicvoidsetDataSource(DataSourcedataSource){
- this.jdbcTemplate=newJdbcTemplate(dataSource);
- }
-
-
- }
spring配置文件为:
- <beanid="userService"class="com.hxzy.account.jdbcTemplate.UserServiceImpl">
- <propertyname="dataSource"ref="dataSource"/>
- </bean>
方法3:继承JdbcDaoSupport,其内部有个JdbcTemplate ,需要注入DataSource 属性来实例化。
- publicclassUserDaoImplextendsJdbcDaoSupportimplementsUserDao{
-
- @Override
- publicvoidsave(Useruser){
- Stringsql=null;
- this.getJdbcTemplate().update(sql);
- }
-
- }
spring配置文件:
- <beanid="userDao"class="com.hxzy.account.jdbcTemplate.UserDaoImpl">
- <propertyname="dataSource"ref="dataSource"/>
- </bean>
二、常用方法使用
【注意:】jdbcTemplate 中的sql均是用“?”做占位符的
domain User:
- publicclassUser{
- privateintid;
- privateStringusername;
- privateStringpassword;
- privateStringsex;
-
-
- }
UserServiceImpl :
如果采用第三种方式,则下面的用法中将方法中的 jdbcTemplate 换成 this.getJdbcTemplate()即可。
-
-
-
- publicvoidcreate(StringtableName){
- jdbcTemplate.execute("createtable"+tableName+"(idinteger,user_namevarchar2(40),passwordvarchar2(40))");
- }
-
-
-
-
-
-
- publicvoidsave3(Useruser){
- Assert.isNull(user,"userisnotnull");
- jdbcTemplate.update("insertintotb_test1(name,password)values(?,?)",
- newObject[]{user.getUsername(),user.getPassword()});
- }
-
-
-
-
-
-
- @Override
- publicvoidsave(Useruser){
- Assert.isNull(user,"userisnotnull");
- jdbcTemplate.update(
- "insertintotb_test1(name,password)values(?,?)",
- newObject[]{user.getUsername(),user.getPassword()},
- newint[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR}
- );
- }
-
-
- publicvoidsave2(finalUseruser){
- Assert.isNull(user,"userisnotnull");
-
- jdbcTemplate.update("insertintotb_test1(name,password)values(?,?)",
- newPreparedStatementSetter(){
-
- @Override
- publicvoidsetValues(PreparedStatementps)throwsSQLException{
- ps.setString(1,user.getUsername());
- ps.setString(2,user.getPassword());
- }
- });
-
- }
-
- publicvoidsave4(Useruser){
- Assert.isNull(user,"userisnotnull");
- jdbcTemplate.update("insertintotb_test1(name,password)values(?,?)",
- newObject[]{user.getUsername(),user.getPassword()});
- }
-
-
- publicListsave5(finalUseruser){
-
- KeyHolderkeyHolder=newGeneratedKeyHolder();
-
- jdbcTemplate.update(newPreparedStatementCreator(){
-
- @Override
- publicPreparedStatementcreatePreparedStatement(Connectionconnection)throwsSQLException{
- PreparedStatementps=connection.prepareStatement("insertintotb_test1(name,password)values(?,?)",newString[]{ "id"});
- ps.setString(1,user.getUsername());
- ps.setString(2,user.getPassword());
- returnps;
- }
- },
- keyHolder);
-
- returnkeyHolder.getKeyList();
- }
-
- @Override
- publicvoidupdate(finalUseruser){
- jdbcTemplate.update(
- "updatetb_test1setname=?,password=?whereid=?",
- newPreparedStatementSetter(){
- @Override
- publicvoidsetValues(PreparedStatementps)throwsSQLException{
- ps.setString(1,user.getUsername());
- ps.setString(2,user.getPassword());
- ps.setInt(3,user.getId());
- }
- }
- );
- }
-
- @Override
- publicvoiddelete(Useruser){
- Assert.isNull(user,"userisnotnull");
- jdbcTemplate.update(
- "deletefromtb_test1whereid=?",
- newObject[]{user.getId()},
- newint[]{java.sql.Types.INTEGER});
- }
-
- @Deprecated
- publicintqueryForInt1(){
- returnjdbcTemplate.queryForInt("selectcount(0)fromtb_test1");
- }
-
- publicintqueryForInt2(Useruser){
- returnjdbcTemplate.queryForInt("selectcount(0)fromtb_test1whereusername=?",
- newObject[]{user.getUsername()});
- }
-
-
- publicintqueryForInt3(Useruser){
- returnjdbcTemplate.queryForInt("selectcount(0)fromtb_test1whereusername=?",
- newObject[]{user.getUsername()},
- newint[]{java.sql.Types.VARCHAR});
- }
-
-
- @Deprecated
- publicStringqueryForObject1(Useruser){
- return(String)jdbcTemplate.queryForObject("selectusernamefromtb_test1whereid=100",
- String.class);
- }
-
-
- @Deprecated
- publicUserqueryForObject2(Useruser){
- return(User)jdbcTemplate.queryForObject("select*fromtb_test1whereid=100",User.class);
- }
-
- @Deprecated
- publicUserqueryForObject3(Useruser){
- return(User)jdbcTemplate.queryForObject("select*fromtb_test1whereid=100",
- newRowMapper(){
-
- @Override
- publicObjectmapRow(ResultSetrs,introwNum)throwsSQLException{
- Useruser=newUser();
- user.setId(rs.getInt("id"));
- user.setUsername(rs.getString("username"));
- user.setPassword(rs.getString("password"));
- returnuser;
- }
- }
- );
- }
-
- publicUserqueryForObject4(Useruser){
- return(User)jdbcTemplate.queryForObject("select*fromtb_test1whereid=?",
- newObject[]{user.getId()},
- User.class);
- }
-
- publicUserqueryForObject5(Useruser){
- return(User)jdbcTemplate.queryForObject(
- "select*fromtb_test1whereid=?",
- newObject[]{user.getId()},
- newRowMapper(){
-
- @Override
- publicObjectmapRow(ResultSetrs,introwNum)throwsSQLException{
- Useruser=newUser();
- user.setId(rs.getInt("id"));
- user.setUsername(rs.getString("username"));
- user.setPassword(rs.getString("password"));
- returnuser;
- }
-
- });
- }
-
- @Override
- publicUserqueryForObject(Useruser){
-
- return(User)jdbcTemplate.queryForObject("select*fromtb_test1whereid=?",
- newObject[]{user.getId()},
- newint[]{java.sql.Types.INTEGER},
- newRowMapper(){
-
- @Override
- publicObjectmapRow(ResultSetrs,introwNum)throwsSQLException{
- Useruser=newUser();
- user.setId(rs.getInt("id"));
- user.setUsername(rs.getString("username"));
- user.setPassword(rs.getString("password"));
- returnuser;
- }
- }
- );
- }
-
- @SuppressWarnings("unchecked")
- publicList<User>queryForList1(Useruser){
- return(List<User>)jdbcTemplate.queryForList("select*fromtb_test1whereusername=?",
- newObject[]{user.getUsername()},
- User.class);
- }
-
- @SuppressWarnings("unchecked")
- publicList<String>queryForList2(Useruser){
- return(List<String>)jdbcTemplate.queryForList("selectusernamefromtb_test1wheresex=?",
- newObject[]{user.getSex()},
- String.class);
- }
-
- @SuppressWarnings("unchecked")
-
- publicList<User>queryForList3(Useruser){
- return(List<User>)jdbcTemplate.queryForList("select*fromtb_test1whereusername=?",
- newObject[]{user.getUsername()},
- newint[]{java.sql.Types.VARCHAR},
- User.class);
- }
-
-
- publicUserqueryUserById4(Stringid){
- finalUseruser=newUser();
-
-
- this.jdbcTemplate.query("select*fromtb_test1whereid=?",
- newObject[]{id},
- newRowCallbackHandler(){
-
- @Override
- publicvoidprocessRow(ResultSetrs)throwsSQLException{
- Useruser=newUser();
- user.setId(rs.getInt("id"));
- user.setUsername(rs.getString("username"));
- user.setPassword(rs.getString("password"));
- }
- });
-
- returnuser;
- }
-
- @SuppressWarnings("unchecked")
- @Override
- publicList<User>list(Useruser){
- returnjdbcTemplate.query("select*fromtb_test1whereusernamelike'%?%'",
- newObject[]{user.getUsername()},
- newint[]{java.sql.Types.VARCHAR},
- newRowMapper(){
-
- @Override
- publicObjectmapRow(ResultSetrs,introwNum)throwsSQLException{
- Useruser=newUser();
- user.setId(rs.getInt("id"));
- user.setUsername(rs.getString("username"));
- user.setPassword(rs.getString("password"));
- returnuser;
- }
- });
- }
-
-
- publicint[]batchUpdate(finalListusers){
-
- int[]updateCounts=jdbcTemplate.batchUpdate(
- "updatetb_test1setusername=?,password=?whereid=?",
- newBatchPreparedStatementSetter(){
-
- @Override
- publicvoidsetValues(PreparedStatementps,inti)throwsSQLException{
- ps.setString(1,((User)users.get(i)).getUsername());
- ps.setString(2,((User)users.get(i)).getPassword());
- ps.setLong(3,((User)users.get(i)).getId());
- }
-
- @Override
- publicintgetBatchSize(){
- returnusers.size();
- }
- }
- );
-
- returnupdateCounts;
- }
-
-
- publicvoidcallProcedure(intid){
- this.jdbcTemplate.update("callSUPPORT.REFRESH_USERS_SUMMARY(?)",newObject[]{Long.valueOf(id)});
- }
其中,batchUpdate适合于批量增、删、改操作;
update(…):使用于增、删、改操作;
execute():执行一个独立的sql语句,包括ddl语句;
queryForInt :查询出一个整数值
转载地址:http://kifnn.baihongyu.com/