使用Spring的JdbcTemplate和BeanPropertyRowMapper完成的JDBC

先道要加上两个包：Spring2.5下面的: spring.jar和commons-logging.jar

我用的DBCP数据源,Connection工具类我就没写了

具体的实例说明：

 

public class StuDaoImple implements StuDaointer {

 

    private JdbcTemplate jdbctemp = null;

 

    public StuDaoImple() {

       jdbctemp = new JdbcTemplate(SQLConnUtil.getDataSource());

    }

 

    // 所有添，删，改的方法都可以用jdbctemp.update();方法

    public void addStu(Stu stu) {

       String sql = "insert into stu values(?,?,?)";

       Object[] obj = new Object[] { stu.getSname(), stu.getSsex(),new java.sql.Date(stu.getSbrith().getTime()) };

       jdbctemp.update(sql, obj);// 可以传两个参数，第一个参数是SQL语句，第二个参数是SQL语句的参数值

    }

    public void delStu(int sid) {

       String sql = "delete stu where s_id=" + sid;

       jdbctemp.update(sql);// 可以传一个参数，就是没有参数的SQL语句

    }

 

    public void updStu(Stu stu) {

       String sql = "update stu set s_name=?,s_sex=?,s_brith=? where s_id=?";

       Object[] obj = new Object[] { stu.getSname(), stu.getSsex(),new java.sql.Date(stu.getSbrith().getTime()), stu.getSid() };

       jdbctemp.update(sql, obj, new int[] { Types.VARCHAR, Types.VARCHAR,Types.DATE, Types.INTEGER });

       // 可以传两个参数，第一个参数是SQL语句，第二个参数是SQL语句的参数值，第三个参数是SQL语句参数值的SQL类型

    }

    //查询1

    public List getAllStu() {

       String sql="select s_id,s_name,s_sex,s_brith from stu";

       List list=jdbctemp.query(sql, new RowMapper(){

 

           public Object mapRow(ResultSet arg0, int arg1) throws SQLException {

              Stu stu=new Stu();

              if(arg0!=null){

                  stu.setSid(arg0.getInt("s_id"));

                  stu.setSname(arg0.getString("s_name"));

                  stu.setSsex(arg0.getString("s_sex"));

                  stu.setSbrith(arg0.getDate("s_brith"));

              }

              return stu;

           }

          

       });

       return list;

    }

   

    //查询2

    public List getAllStu(int id) {

       String sql="select s_id,s_name,s_sex,s_brith from stu where s_id<?";

       Object[] obj=new Object[]{id};

       //使用内部类

       List list=jdbctemp.query(sql,obj,new RowMapper(){

 

           public Object mapRow(ResultSet arg0, int arg1) throws SQLException {

              Stu stu=new Stu();

              if(arg0!=null){

                  stu.setSid(arg0.getInt("s_id"));

                  stu.setSname(arg0.getString("s_name"));

                  stu.setSsex(arg0.getString("s_sex"));

                  stu.setSbrith(arg0.getDate("s_brith"));

              }

              return stu;

           }

          

       });

       return null;

    }

    //查询方式3(查询一个对象)

    public Stu getOneStu(int id) {

       String sql="select s_id as sid,s_name as sname,s_sex as ssex,s_brith as sbrith from stu where s_id=?";

       Object[] obj=new Object[]{id};

       Object stu=jdbctemp.queryForObject(sql, obj,new BeanPropertyRowMapper(Stu.class));

       //可以不用内部类，但是类的属性字段名称要数据库中的字段名称一样或者数据库字段名为s_id类的名称为SId,

       //如果你没有遵守这个规范则可以在select语句后面给数据库字段名取别名

       //上面的查询也可以这种方式

       return (Stu)stu;

    }

    //查询方式4(查询一个字段)

    public String getStuName(int sid) {

       String sql="select s_name as sname from stu where s_id=?";

       Object[] obj=new Object[]{sid};

       Object stuname=jdbctemp.queryForObject(sql, obj, String.class);

       //String.class即是指明一下结果的类型

       return (String) stuname;

    }

    //查询方法5

    public int count(){

       String sql="select count(*) from stu";

       int m=jdbctemp.queryForInt(sql);

       return m;

    }

    //查询方法6 查询结果返回的是map,map中的key为select中的列名(若有别名是是别名)value是该列所对应的值

    public Map getStuinMap(int sid){

       String sql="select s_id as sid,s_name as sname,s_sex as ssex,s_brith as sbrith from stu where s_id=?";

       Object[] obj=new Object[]{sid};

       return jdbctemp.queryForMap(sql, obj);

    }

    //查询方法7 若你不想用spring封装好了的操作,你可以自己写.Connection的打开和关闭它已经写好了,其它的操作自己可以去完成

    public int getMyQuery(final Stu stu){

      

       Object obj=jdbctemp.execute(new ConnectionCallback(){

 

           public Object doInConnection(Connection conn) throws SQLException, DataAccessException {

              String sql="insert into stu values(?,?,?)";

              PreparedStatement pre=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//返回主键自动

              pre.setString(1, stu.getSname());

              pre.setString(2, stu.getSsex());

              pre.setDate(3, new java.sql.Date(stu.getSbrith().getTime()));

              pre.executeUpdate();

              ResultSet rs=pre.getGeneratedKeys();

              int m=0;

              if(rs.next()){

                  m=rs.getInt(1);

              }

              return m; 

           }     

       });

       return (Integer)obj;

    }

}