spring JdbcTemplate 操作实录

1. 单个插入获取id

    //插入并获取主键
    public int addAndGetId(Order order) {

        final String sql = "insert into orders(name, address,createtime,totalprice,status) values(?,?,?,?,?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

                ps.setObject(1, order.getName());

                ps.setObject(2, order.getAddress());

                ps.setObject(3, order.getCreateTime());

                ps.setObject(4, order.getTotalPrice());

                ps.setObject(5, order.getStatus());

                return ps;
            }

        }, keyHolder);

        return keyHolder.getKey().intValue();

2.1 批量插入-无返回id

//批量插入订单详情
    public void addBatch(List<OrderDetail> orderDetails) {

        List<Object[]> params = new ArrayList<>();

        for (OrderDetail orderDetail : orderDetails) {

            params.add(new Object[]{orderDetail.getProductName(), orderDetail.getProductPrice(),

                    orderDetail.getProductCount(), orderDetail.getParentid()});

        }

        final String sql = "insert into order_detail(pro_name,pro_price,pro_count,parent_id) values(?,?,?,?)";

        jdbcTemplate.batchUpdate(sql, params);


    }

通过对参数统一封装达到一次性插入的效果

2.2 批量插入，批量返回id

   //批量插入
    public List<Integer> addProduct(List<ProductBean> expList) throws SQLException {
        final List<ProductBean> tempexpList = expList;

        String sql="insert into product(id,s_id,status,datetime, count,o_id,reasons values(null,?,?,?,?,?,?)";

        //此方法为获取数据链接
        DbOperation dbOp = new DbOperation();
        dbOp.init();
        Connection con = dbOp.getConn();
        
        con.setAutoCommit(false);
        PreparedStatement pstmt = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
        for (ProductBean n : tempexpList) {
            pstmt.setInt(1,n.getSId());
            pstmt.setInt(2,n.getStatus());
            pstmt.setString(3,n.getDatetime());
            pstmt.setInt(4,n.getCount());
            pstmt.setInt(5,n.getOId());
            pstmt.setInt(6,n.getReasons());
            pstmt.addBatch();
        }
        pstmt.executeBatch();
        con.commit();
        ResultSet rs = pstmt.getGeneratedKeys(); //获取结果
        List<Integer> list = new ArrayList<Integer>();
        while(rs.next()) {
            list.add(rs.getInt(1));//取得ID
        }
        con.close();
        pstmt.close();
        rs.close();

        return list;

    }

3.1 JdbcTemplate查询-RowMapper返回自定义对象

使用步骤：
定义Product类
创建JdbcTemplate对象
编写查询的SQL语句
使用JdbcTemplate对象的query方法，并传入RowMapper匿名内部类
在匿名内部类中将结果集中的一行记录转成一个Product对象

案例代码：

// query使用rowMap做映射返回一个对象
public static void test06() throws Exception {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());

   // 查询数据的SQL语句
   String sql = "SELECT * FROM product;";

   List<Product> query = jdbcTemplate.query(sql, new RowMapper<Product>() {
      @Override
      public Product mapRow(ResultSet arg0, int arg1) throws SQLException {
         Product p = new Product();
         p.setPid(arg0.getInt("pid"));
         p.setPname(arg0.getString("pname"));
         p.setPrice(arg0.getDouble("price"));
         return p;
      }
   });

   for (Product product : query) {
      System.out.println(product);
   }
}

3.2JdbcTemplate查询-BeanPropertyRowMapper返回自定义对象

使用步骤
定义Product类
创建JdbcTemplate对象
编写查询的SQL语句
使用JdbcTemplate对象的query方法，并传入BeanPropertyRowMapper对象

案列：

// query使用BeanPropertyRowMapper做映射返回对象
public static void test07() throws Exception {
	JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());

	// 查询数据的SQL语句
	String sql = "SELECT * FROM product;";
	List<Product> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Product.class));

	for (Product product : list) {
		System.out.println(product);
	}
}