JdbcTemplate在Spring中的应用

提示：需要向tbl_user表中加入数据测试

sql语句如下：

 create table tbl_customer(
 customer_no int not null,
 customer_name varchar(300) null,
 constraint pk_tbl_customer primary key(customer_no));
 
 create table tbl_order(
 order_no int not null,
 product_no int null,
 puser_no int null,
 customer_no int null,
 order_quantity int null,
 transaction_price float null,
 constraint pk_tbl_order primary key(order_no));
 
create table tbl_product(
product_no int not null,
product_name varchar(300) null,
product_img varchar(300) null,
product_type int null,
constraint pk_tbl_product primary key(product_no));

create table tbl_user(
puser_no int not null,
puser_name varchar(100) null,
puser_level int null,
constraint pk_tbl_user primary key(puser_no));

alter table tbl_order
add constraint pk_tbl_order_reference_tbl_prod foreign key(product_no)
references tbl_product(product_no)

alter table tbl_order
add constraint pk_tbl_order_reference_tbl_user foreign key(puser_no)
references tbl_user(puser_no)

alter table tbl_order
add constraint pk_tbl_order_reference_tbl_customer foreign key(customer_no)
references tbl_customer(customer_no)

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

 <bean id="dataSource"
  class="org.apache.commons.dbcp.BasicDataSource" abstract="false"
  singleton="true" lazy-init="default" autowire="default"
  dependency-check="default">
  <property name="driverClassName">
   <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
  </property>
  <property name="url">
   <value>jdbc:sqlserver://localhost:1433;databaseName=sell</value>
  </property>
  <property name="username">
   <value>sa</value>
  </property>
  <property name="password">
   <value></value>
  </property>
 </bean>
 
 <bean id="jdbcTemplate"
  class="org.springframework.jdbc.core.JdbcTemplate" abstract="false"
  singleton="true" lazy-init="default" autowire="default"
  dependency-check="default">
  <property name="dataSource">
   <ref bean="dataSource" />
  </property>
 </bean>
 
 <bean id="userOimpl"
  class="com.studyspring.ch6.UserDAOImpl" abstract="false"
  singleton="true" lazy-init="default" autowire="default"
  dependency-check="default">
  <property name="jdbcTemplate">
   <ref bean="jdbcTemplate" />
  </property>
 </bean></beans>

IUserDAO.java

package com.studyspring.ch6;

import java.util.List;

public interface IUserDAO {
 List getUserList();
}
User.java

package com.studyspring.ch6;

public class User {
 private int uid;
 private String uname;
 private int ulevel;
 public int getUid() {
  return uid;
 }
 public void setUid(int uid) {
  this.uid = uid;
 }

 public int getUlevel() {
  return ulevel;
 }
 public void setUlevel(int ulevel) {
  this.ulevel = ulevel;
 }
 public String getUname() {
  return uname;
 }
 public void setUname(String uname) {
  this.uname = uname;
 }
 
}
UserDAOImpl.java
package com.studyspring.ch6;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

public class UserDAOImpl implements IUserDAO {
 JdbcTemplate jdbcTemplate;
 public List getUserList() {
  List list=jdbcTemplate.query(new UserPSCreator(), new UserReader());
  return list;
 }
 public JdbcTemplate getJdbcTemplate() {
  return jdbcTemplate;
 }
 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
  this.jdbcTemplate = jdbcTemplate;
 }

}

UserPSCreator.java

package com.studyspring.ch6;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.PreparedStatementCreator;

public class UserPSCreator implements PreparedStatementCreator {

 public PreparedStatement createPreparedStatement(Connection conn)
   throws SQLException {
  String sql="select * from tbl_user";
  PreparedStatement ps=conn.prepareStatement(sql);
  return ps;
 }

}

UserReader.java

package com.studyspring.ch6;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.ResultReader;
//ResultReader extends RowCallbackHandler
public class UserReader implements ResultReader {

 List users=new ArrayList();
 User user;
 public List getResults() {
  //该方法只用于返回对象
  return users;
 }

 public void processRow(ResultSet rs) throws SQLException {
  user=new User();
  user.setUid(rs.getInt("puser_no"));
  user.setUname(rs.getString("puser_name"));
  user.setUlevel(rs.getInt("puser_level"));
  users.add(user);
  //do{
  // System.out.println(rs.getInt(1));
  // System.out.println(rs.getString(2));
  // System.out.println(rs.getInt(3));
  //}while(rs.next());
 }
}

UserBasicORMapping.java

package com.studyspring.ch6;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
public class UserBasicORMapping {

 public static void main(String[] args) {
  ApplicationContext ctx=new FileSystemXmlApplicationContext("/src/applicationContext.xml");
  IUserDAO userDAO=(IUserDAO)ctx.getBean("userOimpl");
  List list=userDAO.getUserList();
  for(int i=0;i<list.size();i++){
   User user=(User)list.get(i);
   System.out.println("~~~~~~******~~~~~~~");
   System.out.println("第"+(i+1)+"位用户:"+user.getUname());
   System.out.println("~~~~~~******~~~~~~~");
  }
 }

}