jdbcTemplate execute example

最新推荐文章于 2024-03-31 15:20:42 发布
最新推荐文章于 2024-03-31 15:20:42 发布
阅读量75 收藏
点赞数
文章标签： java
原文链接：http://www.cnblogs.com/treasureNow/p/3449911.html
版权 
package com.material.dao;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.util.DateTransfer;
import com.util.Util;

public class JdbcDAO extends JdbcDaoSupport{

    
    public void insertMaterial(List<String> list, int i) throws Exception{
        /*if (i > 3000){
            throw new Exception("不好李树超睡着了");
        }*/
        String sql = "insert into MaterialStocked values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        getJdbcTemplate().update(sql,  list.toArray());  
    }
    
    public void deleteFormByNO(String FORMNO){
        String sql = "delete from  MaterialStocked  where FormNO = ?";
        getJdbcTemplate().update(sql,  new Object[]{FORMNO});  
    }
    
    /************************************************* for export material information ****************/    
    public List<String> listMaterials(){
        //String sql = "SELECT distinct MaterialCategory from MaterialStocked where MaterialCategory is not null";
        String sql = "SELECT  MaterialCategory from MaterialStocked where MaterialCategory is not null GROUP BY MaterialCategory order by cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) desc";
        return getJdbcTemplate().queryForList(sql, String.class);
    }
            
    public Map<String, Object> listTotalMaterial(String materialCategory){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, SUM(Cast(TotalMoneyOTax as decimal(18,2))) SUM_TotalMoneyOTax  from  MaterialStocked  where  MaterialCategory = ?";
        return getJdbcTemplate().queryForMap(sql, new Object[]{materialCategory});
    }
    
    public List<String> listCompanies(){
        String sql = "SELECT distinct Company from  MaterialStocked  where Company is not null";
        return getJdbcTemplate().queryForList(sql, String.class);
    }
    
    public List<Map<String, Object>> listCompanyDetailByMaterial(String material){
        String sql = "select * from (select SUM(Cast(Quantity as decimal(18,2))) SUM_QUANTITY, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, Company Company1 from MaterialStocked  where MaterialCategory = ?   group by Company) B right join (SELECT distinct Company  from MaterialStocked )A  on B.Company1  = Company";
        return getJdbcTemplate().queryForList(sql, new Object[]{material});
    }
    
    public List<Map<String, Object>> listCompanyDetailByMaterial(String material, String company, String startMonth, String endMonth){
        String sql = "select * from (select SUM(Cast(Quantity as decimal(18,2))) SUM_QUANTITY, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, Company Company1 from MaterialStocked  where  Convert(datetime, intime ) BETWEEN ? AND ? and  MaterialCategory = ? and Company in "+ company +"    group by Company) B right join (SELECT distinct Company  from MaterialStocked  where Convert(datetime, intime ) BETWEEN ? AND ? and  Company in "+ company +"  )A  on B.Company1  = Company";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth, endMonth, material,  startMonth, endMonth});
    }
    
    /*****************************************************supplier data for web ***********/    
    public List<Map<String, Object>>  listSummaryByCategory(){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, MaterialCategory from MaterialStocked group by MaterialCategory";
        return getJdbcTemplate().queryForList(sql);
    }
    
    //function in condition 3
    public List<Map<String, Object>>  listSummaryByCategory(String startMonth, String endMonth, String category){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, MaterialCategory from MaterialStocked  where   Convert(datetime, intime ) BETWEEN ? AND ? and  MaterialCategory in "+ category +"  group by MaterialCategory  order by  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) desc ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth});
    }
    
    public List<Map<String, Object>>  listSummaryByCategory(String startMonth, String endMonth, String category, String supplier, String tt){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, MaterialCategory from MaterialStocked  where   Convert(datetime, intime ) BETWEEN ? AND ? and  MaterialCategory in "+ category +" and  MaterialCategory in (select MaterialCategory from MaterialStock where Supplier like '%?%')  group by MaterialCategory";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{supplier, startMonth , endMonth});
    }
    
    ///function in condition 1
    public List<Map<String, Object>>  listSummaryByCategory(String category){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, MaterialCategory from MaterialStocked where MaterialCategory in "+ category +" group by MaterialCategory";        
        return getJdbcTemplate().queryForList(sql);
    }
    
    ///function in condition 2
    public List<Map<String, Object>>  listSummaryByCategory(String category, String company, String startMonth, String endMonth){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, MaterialCategory from MaterialStocked where  Convert(datetime, intime ) BETWEEN ? AND ? and  MaterialCategory in "+ category +" and Company in "+ company +"  group by MaterialCategory  order by  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) desc";        
        System.out.println(sql);
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth});
    }
    
    ///function in condition 3
    public List<String>  listSummaryByCategoryNoValue(String category, String company, String startMonth, String endMonth){
        String sql = "select MaterialCategory from MaterialStocked where  Convert(datetime, intime ) BETWEEN ? AND ? and  MaterialCategory in "+ category +" and Company in "+ company +" group by MaterialCategory";        
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth}, String.class);
    }
    
    public List<Map<String, Object>>  listDetailSuppliersByCategory(String material_category,  BigDecimal total_money_tax){
        //按照金额以及数量联合查询
        /*List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getMinNumOfMaterial(material_category);
        String sql = "select  Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ?  ORDER BY SUM_TotalMoneyOTax DESC ";
        list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, num});
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ?  ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, 1});
        }
        
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ?  ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, 0});
        }
        return list;*/
        
        //方案二 按照名次查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getRanking_UnDoneOfMaterial(material_category);
        String sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ?  ORDER BY SUM_TotalMoneyOTax DESC ";
        list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, 1});
    
        if (list == null || list.size() == 0){
            sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ?  ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, 0});
        }
        return list;
    }
    
    // function in condition1
    public List<Map<String, Object>>  listDetailSuppliersByCategory(String material_category,  BigDecimal total_money_tax, String supplier, String startMonth, String endMonth){
        //按照金额以及数量联合查询
        /*List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getMinNumOfMaterial(material_category);
        String sql = "select Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? and Supplier like '%?%'   and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, supplier, startMonth , endMonth, num});
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? and Supplier like '%?%'   and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, supplier, startMonth , endMonth, 1});
        }
        
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? and Supplier like '%?%'   and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, supplier, startMonth , endMonth, 0});
        }
        return list;*/
        
        //方案二 按照名次查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getRanking_UnDoneOfMaterial(material_category);
        String sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? and Supplier like '%?%'   and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, supplier, startMonth , endMonth, 1});

        if (list == null || list.size() == 0){
            sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  SUM(Cast(TotalMoneyOTax as decimal(18,2)))*100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ? and Supplier like '%?%'   and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, supplier, startMonth , endMonth, 0});
        }
        return list;
    }
    
    // function in condition2
    public List<Map<String, Object>>  listDetailSuppliersByCategory(String material_category,  BigDecimal total_money_tax, String startMonth, String endMonth){
        /*List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getMinNumOfMaterial(material_category);
        String sql = "select  Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, num});
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, num});
        }
        
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, num});
        }
        return list;*/
        
        //方案二 按照名次查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getRanking_UnDoneOfMaterial(material_category);
        String sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, 1});

        if (list == null || list.size() == 0){
            sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2),  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, 0});
        }
        return list;
    }
    
    // function in condition3
    public List<Map<String, Object>>  listDetailSuppliersByCategoryNoPercent(String material_category,  BigDecimal total_money_tax, String startMonth, String endMonth){
        //按照金额以及数量联合查询
        /*List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getMinNumOfMaterial(material_category);
        String sql = "select Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list = getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, num});
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, 1});
        }
        
        if (list == null || list.size() == 0){
            sql = "select top 40 Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, 0});
        }
        return list;*/
        
        //方案二 按照名次查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getRanking_UnDoneOfMaterial(material_category);
        String sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list = getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, 1});

        if (list == null || list.size() == 0){
            sql = "select top "+num+" Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from MaterialStocked where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, Supplier having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list = getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, 0});
        }
        return list;
    }
        
    public Map<String, Object> SummarySuppliers(){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax from MaterialStocked  ";
        return getJdbcTemplate().queryForMap(sql);
    }
    
    // child function 1
    public Map<String, Object> SummarySuppliers(String Category, String company, String startMonth, String endMonth){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax from MaterialStocked where  Convert(datetime, intime ) BETWEEN ? AND ? and MaterialCategory in "+ Category +" and Company in  " + company + " ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForMap(sql, new Object[]{startMonth , endMonth});
    }
    
    // child function 2
    public Map<String, Object> SummarySuppliers(String startMonth, String endMonth, String Category){        
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax from MaterialStocked where  Convert(datetime, intime ) BETWEEN ? AND ? and MaterialCategory in "+ Category ;
        System.out.println("sum-------"+  sql);
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForMap(sql, new Object[]{startMonth , endMonth});
    }
    
    public List<Map<String, Object>> SummaryGroupByCompanies(){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, Company from MaterialStocked group by Company";
        return getJdbcTemplate().queryForList(sql);
    }
    
    //
    public List<Map<String, Object>> SummaryGroupByCompanies(String Category, String company, String startMonth, String endMonth){
        String sql = "select SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) SUM_TotalMoneyOTax, Company from MaterialStocked   where  Convert(datetime, intime ) BETWEEN ? AND ?  and   MaterialCategory in "+ Category +"  and Company in  " + company + "  group by Company";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth});
    }
    
    //--------------- chart
    public List<Map<String, Object>> listCompanyMoneyByPeriodAndMaterial(String startMonth, String endMonth, String material){
        String sql= "select Company label,  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) value  from MaterialStocked where Convert(datetime, intime ) BETWEEN ? AND ?  and MaterialCategory  = ?  group by Company";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth, material});
    }
    
    public List<Map<String, Object>> listMaterialMoneyByPeriodAndCompany(String startMonth, String endMonth, String company){
        String sql= "select MaterialCategory label,  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) value  from MaterialStocked where Convert(datetime, intime ) BETWEEN ? AND ?  and Company  = ?  group by MaterialCategory";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth, company});
    }
    
    //同一材料不同时间段的引进情况
    public List<Map<String, Object>> listMaterialMoneyPerMonth(String startMonth, String endMonth, String material){
        String sql = "select substring(Intime,0, 8) label,  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) value  from MaterialStocked where Convert(datetime, intime ) BETWEEN ? AND ?  and MaterialCategory  = ?  group by  substring(Intime,0, 8)   order by label asc";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth, material});
    }
    
    //同一公司不同时间段的引进情况
    public List<Map<String, Object>> listCompanyMoneyPerMonth(String startMonth, String endMonth, String company){
        String sql = "select substring(Intime,0, 8) label,  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) value  from MaterialStocked where Convert(datetime, intime ) BETWEEN ? AND ?  and Company  = ?  group by  substring(Intime,0, 8)   order by label asc";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth, company});
    }
    
    //同一公司, 同一材料不同时间段的引进情况
    public List<Map<String, Object>> listCompanyAndMaterialPerMonth(String startMonth, String endMonth, String company, String material){
        String sql = "select substring(Intime,0, 8) label,  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) value  from MaterialStocked where Convert(datetime, intime ) BETWEEN ? AND ?  and Company  = ?  and MaterialCategory = ?  group by  substring(Intime,0, 8)   order by label asc";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        return getJdbcTemplate().queryForList(sql, new Object[]{startMonth , endMonth, company, material});
    }
    
    //录入时间
    public List<String> listInTime(){
        String sql = "select distinct Intime from MaterialStocked order by InTime asc";
        return getJdbcTemplate().queryForList(sql, String.class);
    }
    
    /****************------------------------settled supplier name------------------------------------******************************/        
    // function in condition2
    public List<Map<String, Object>>  listDetailSuppliersByCategorySettled(String material_category,  BigDecimal total_money_tax, String startMonth, String endMonth){
        System.out.println(material_category);
        //按照金额以及数量联合查询
        /*List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getMinNumOfMaterial(material_category);
        String sql = "select ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2), cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list =  getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, num});
        if (list == null || list.size() == 0){
             sql = "select top 20 ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2), cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC"; 
             list =  getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, 1});
        }
        
        if (list == null || list.size() == 0){
             sql = "select top 20 ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2), cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC"; 
             list =  getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, 0});
        }
        return list;*/
        
        //方案二 按照名次查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getRanking_DoneOfMaterial(material_category);        
        String sql = "";
        int money = 1;
        if (material_category.equals("铝单板")){
            money = 250;
        }
        
        sql = "select top "+num+" ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2), cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list =  getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, money});

        if (list == null || list.size() == 0){
            sql = "select top "+num+" ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,   Convert(decimal(18,2), cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) * 100/?)   SupplierPercent, MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ  having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC";
            list =  getJdbcTemplate().queryForList(sql, new Object[]{total_money_tax, material_category, startMonth , endMonth, 0});
        }
        return list;
    }
    
    // function in condition3
    public List<Map<String, Object>>  listDetailSuppliersByCategoryNoPercentSettled(String material_category,  BigDecimal total_money_tax, String startMonth, String endMonth){
        //按照金额以及数量联合查询
        /*List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getMinNumOfMaterial(material_category);
        String sql = "select ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01")    ;
        list =  getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, num});
        if (list == null || list.size() == 0){
             sql = "select top 20 ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
             list =  getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, 1});
        }
        
        if (list == null || list.size() == 0){
             sql = "select top 20 ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
             list =  getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, 0});
        }*/
        
        //方案二 按照名次查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int num = Util.getRanking_DoneOfMaterial(material_category);
        String sql = "";
        int money = 1;
        if (material_category.equals("铝单板")){
            money = 250;
        }
        sql = "select top "+num+" ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
        startMonth = DateTransfer.getFirstday_Month(startMonth + "/01") ;
        endMonth =     DateTransfer.getLastDay_Month(endMonth + "/01");
        list =  getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, money});
        if (list == null || list.size() == 0){
            sql = "select top "+num+" ColumnZ Supplier, SUM(Cast(Quantity as decimal(18,2))) SUM_Quantity, cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2))  SUM_TotalMoneyOTax,  MaterialCategory  from Material_View where  MaterialCategory = ?  and  Convert(datetime, intime ) BETWEEN ? AND ? GROUP BY MaterialCategory, ColumnZ having  cast(SUM(Cast(TotalMoneyOTax as decimal(18,2)))/10000 as   decimal(18,   2)) > ? ORDER BY SUM_TotalMoneyOTax DESC ";
            list =  getJdbcTemplate().queryForList(sql, new Object[]{material_category, startMonth , endMonth, 0});
        }
        
        return list;
    }
        
    public void testProcedure() { 
          List resultList = (List) getJdbcTemplate().execute( 
             new CallableStatementCreator() { 
                public CallableStatement createCallableStatement(Connection con) throws SQLException { 
                   String storedProc = "{call proc_get_user}";// 调用的sql 
                   CallableStatement cs = con.prepareCall(storedProc); 
                   return cs; 
                } 
             }, new CallableStatementCallback() { 
                public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException { 
                   List resultsMap = new ArrayList(); 
                   cs.execute(); 
                   ResultSet rs = (ResultSet) cs.executeQuery();// 获取游标一行的值 
                   while (rs.next()) {// 转换每行的返回值到Map中 
                      Map rowMap = new HashMap(); 
                      rowMap.put("id", rs.getString("id")); 
                      rowMap.put("UserName", rs.getString("UserName")); 
                      resultsMap.add(rowMap); 
                   } 
                   rs.close(); 
                   return resultsMap; 
                } 
          }); 
          
          for (int i = 0; i < resultList.size(); i++) { 
             Map rowMap = (Map) resultList.get(i); 
             String id = rowMap.get("id").toString(); 
             String name = rowMap.get("UserName").toString(); 
             System.out.println("id=" + id + ";UserName=" + name); 
          } 
    } 
}
　　

 

转载于:https://www.cnblogs.com/treasureNow/p/3449911.html

weixin_34212762
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
jdbcTemplate简单使用
08-03
jdbcTemplate.execute(() -> { jdbcTemplate.update("INSERT INTO users(name, email) VALUES (?, ?)", "Alice", "alice@example.com"); // 其他操作... return null; }); ``` `JdbcTemplate`的另一个优势是它的...
SpringBoot交互数据库之JdbcTemplate
01-07
SpringBoot交互数据库之JdbcTemplate 1，maven依赖 4.0.0 org.springframework.boot spring-boot-starter-parent 2.2.4.RELEASE com.example sqldemo 0.0.1-SNAPSHOT sqldemo Demo project for Spring ...
JdbcTemplate学习笔记
nvryy的专栏
10-10 815
本文来自：http://tmsoft.lsxy.com/index.php?load=read&amp;id=995   1、使用JdbcTemplate的execute()方法执行SQL语句    Java代码   jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))")...
JdbcTemplate类中的execute方法
weixin_45151960的博客
03-02 5354
使用execute方法建一张用户表，execute()方法里可以执行SQL语句. JdbcTemplateTest.java: package com.liu.jdbc; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApp...
项目实训记录（五）——JdbcTemplate中execute和update
pinkray_c的博客
06-03 3130
1.什么是JdbcTemplate 2.JdbcTemplate的准备 3.JdbcTemplate的使用——execute和update方法 4.execute和update方法的不同（参考） 5.最后怎么解决execute不能传参的问题呢？
2021-09-08 Spring JdbcTemplate的常用方法之execute
m0_60769905的博客
09-08 771
execute(String sql)：该方法能够完成执行SQL语句的功能。 第一步：在MySql中创建spring数据库 第二步：创建一个chapter04的Web项目，并导入Spring框架所需的5个基础包以及 MySQL数据库的驱动JAR包: mysql-connector-java-5.1.8-bin.jar Spring JDBC的JAR包：spring-jdbc-4.3.6.RELEASE.jar ...
Java 使用JdbcTemplate 中的queryForList发生错误解决办法
08-30
elementType the required type of element in the result list (for example, Integer.class) ``` 也就是说，queryForList 方法的第二个参数只能是简单类型 String 或 Integer。 解决方法有两种： 1. 使用 ...
9. 使用JdbcTemplate【从零开始学Spring Boot】
08-13
jdbcTemplate.execute(new TransactionCallbackWithoutResult() { @Override protected void doInTransactionWithoutResult(TransactionStatus status) { // 执行一系列操作 } }); ``` JdbcTemplate的使用极大...
spring jdbc mysql example
05-25
综上所述，"spring jdbc mysql example"这个主题涵盖了从配置数据源、使用JdbcTemplate执行SQL、处理结果集、管理事务到测试数据库操作等一系列关键步骤，这些都是在Java应用中使用Spring JDBC与MySQL数据库交互的...
Spring JdbcTemplate 的常用方法之execute update query方法
共勉
03-19 1498
在JdbcTemplate核心类中，提供了大量的更新和查询数据库的方法，我们就是使用的这些方法来操作数据库的。 常用方法如下图所示： 1.0 execute
JdbcTemplate基本使用
热门推荐
羊咩咩
03-13 19万+
JdbcTemplate基本使用 目标 能够掌握JdbcTemplate+Druid创建数据库 讲解 JdbcTemplate介绍 ​ JDBC已经能够满足大部分用户最基本的需求，但是在使用JDBC时，必须自己来管理数据库资源如：获取PreparedStatement，设置SQL语句参数，关闭连接等步骤。 ​ JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。J...
Spring JdbcTemplate的常用方法--execute()
最新发布
weixin_61144851的博客
03-31 387
四、在src目录下创建配置文件applicationContext.xml,在该文件中配置id为dataSource的数据源Bean和id为jdbcTemplate的JDBC模板Bean，并将数据源注入到JDBC模板中。五、在src目录下创建com.itheima.jdbc的包，在该包中创建测试类JdbcTemplateTest。二、创建动态项目File-->New-->Dynamic Web Project。execute(String sql)方法能够完成执行SQL语句的功能。
1.6初步了解JdbcTemplate操作
m0_65489276的博客
04-23 305
Spring对数据库的操作在JDBC上面做了深层次的封装，使用Spring的注入功能，可以把DataSource注册到JdbcTemplate之中，从而方便地实现对数据库的访问操作。
JdbcTemplate的使用案例
左耳的博客
10-30 1058
JdbcTemplate简单介绍 基本概念 ​ JDBC已经能够满足大部分用户最基本的需求，但是在使用JDBC时，必须自己来管理数据库资源如：获取PreparedStatement，设置SQL语句参数，关闭连接等步骤。 JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。他帮助我们避免一些常见的错误，比如忘了总要关闭连接。他运行核心的JDBC工作流，如Stat...
[springboot]学习- JdbcTemplate用法浅尝
海兰
06-09 3765
JdbcTemplate 是 Spring Framework 提供的一个非常强大的 JDBC 工具类，它可以显著简化 JDBC 编程的代码量，并提供了许多便捷的方法来执行 SQL 查询、更新等操作。
JdbcTemplate的基本使用
munangs的博客
09-15 1400
1.创建jdbcTemplate对象 //jdbcTemplate对象 private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource()); 2.创建表，创建库 使用jdbcTemplate.execute()方法 可用于创建数据库以及数据表 String sql = "CREATE TABLE user (id int primary key auto_increment,username varchar(25
Spring JDBCTemplate的常用方法
Altitude_的博客
09-03 1101
一、execute（）方法 execute()负责完成执行sql语句。 1.导入spring 的基本框架包（5个），spring-jdbc.jar、spring-tx.jar和连接数据库的驱动包 2.设置spring的配置文件，其中dataSource来自spring-jdbc.jar，JdbcTe...
JdbcTemplate常用方法
ningmeng666_c的博客
02-19 1866
摘自原文：https://www.iteye.com/blog/1358440610-qq-com-1826816 一、首先配置JdbcTemplate； 要使用Jdbctemplate 对象来完成jdbc 操作。通常情况下，有三种种方式得到JdbcTemplate 对象。 第一种方式：我们可以在自己定义的DAO 实现类中注入一个DataSource 引用来完 成JdbcTemplate 的实例化。也就是它是从外部“注入” DataSource 到DAO 中，然后 自己实例化JdbcTemplate，然后将
jdbctemplate.execute delete
05-18
`JdbcTemplate.execute()` 方法可以用来执行任何 SQL 语句，包括 `DELETE` 语句。如果你想删除一些数据，可以使用 `JdbcTemplate.execute()` 方法来执行 `DELETE` 语句。 下面是一个使用 `JdbcTemplate.execute()` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值