spring boot 多数据源 jdbcTemplate 操作数据库

最新推荐文章于 2023-10-17 21:39:26 发布
最新推荐文章于 2023-10-17 21:39:26 发布
阅读量276 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/az44yao/article/details/119814793
版权 
package com.nyzy.mai;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
//import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
//import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
//import org.springframework.cloud.openfeign.EnableFeignClients;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.EnableScheduling;

import net.sf.log4jdbc.Log4jdbcProxyDataSource;


@EnableScheduling
@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
//@EnableEurekaClient
//@EnableDiscoveryClient
//@EnableFeignClients
public class MaiApplication {
	private final static Logger LOG = LoggerFactory.getLogger(MaiApplication.class);
	public static void main(String[] args) {
		SpringApplication.run(MaiApplication.class, args);
	}


	@Bean(name = "dataSourceluge1")
	@Qualifier(value = "dataSourceluge1")
	@ConfigurationProperties(prefix = "spring.datasource.luge")
	public DataSource createDataSourceluge() {
		LOG.info("010 --> createDataSourceluge");
		return DataSourceBuilder.create().build();
	}

	@Primary
	@Bean(name="dataSourceluge")
	@Qualifier(value = "dataSourceluge")
	public Log4jdbcProxyDataSource createProxyDataSource1(@Qualifier("dataSourceluge1") DataSource dataSource) {
		LOG.info("020 --> createProxyDataSource");
		return new Log4jdbcProxyDataSource(dataSource);
	}
	
	@Primary
	@Bean(name = "jdbcTemplateluge")
	@Qualifier(value = "jdbcTemplateluge")
	public JdbcTemplate jdbcTemplate1(@Qualifier("dataSourceluge") DataSource dataSource) {
		LOG.info("030 --> jdbcTemplate");
		return new JdbcTemplate(dataSource);
	}
	

	
	@Bean(name = "dataSource1")
	@Qualifier(value = "dataSource1")
	@ConfigurationProperties(prefix = "spring.datasource.mai")
	public DataSource createDataSource() {
		LOG.info("010 --> createDataSourcemai");
		return DataSourceBuilder.create().build();
	}

	@Primary
	@Bean(name="dataSource")
	@Qualifier(value = "dataSource")
	public Log4jdbcProxyDataSource createProxyDataSource(@Qualifier("dataSource1") DataSource dataSource) {
		LOG.info("020 --> createProxyDataSource");
		return new Log4jdbcProxyDataSource(dataSource);
	}

	@Primary
	@Bean(name = "jdbcTemplate")
	@Qualifier(value = "jdbcTemplate")
	public JdbcTemplate jdbcTemplate(@Qualifier("dataSource") DataSource dataSource) {
		LOG.info("030 --> jdbcTemplate");
		return new JdbcTemplate(dataSource);
	}
	
	
	
	
	
	
	
	
	

}


package com.nyzy.maidev;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSONObject;

@CrossOrigin(origins="*",allowCredentials = "true")
@RestController
@RequestMapping("/dev")
public class DevController {

	@Autowired
	@Qualifier("dataSourceluge")
	private DataSource datasourceluge; 
	
	@Autowired
	@Qualifier("jdbcTemplateluge")
	private JdbcTemplate jdbcTemplateluge;
	
	@GetMapping("/index")
	public String maintaindayproduct() throws SQLException
	{
		JSONObject joJsonObject = new JSONObject();
		joJsonObject.put("kk", jdbcTemplateluge.queryForObject("select name from dev where id=1", String.class).toString());
		return joJsonObject.toJSONString();
	}
}

server: 
  port: 86
# jdbc_config   datasource
spring: 
  application:  
    name: mail #指定服务名
  datasource:
    luge:     
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/luge?useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT
      jdbc-url: jdbc:mysql://127.0.0.1:3306/luge?useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT
      username: root
      password: root
      connectionTestQuery: select * from dev
 


# jdbc_config   datasource
    mai:
      driver-class-name: net.sourceforge.jtds.jdbc.Driver
      jdbc-url: jdbc:jtds:sqlserver://192.168.0.100:9433/zuixin
      url=jdbc: jtds:sqlserver://192.168.0.100:9433/zuixin
      username: sa
      password: 123456
      connectionTestQuery: select * from srv_user
# Hikari will use the above plus the following to setup connection pooling


#中国营业执照

 
eureka:
  client:
    service-url:
      defaultZone: http://127.0.0.1:6868/eureka/
  instance:
   prefer-ip-address: true
   register-with-eureka: false
   fetch-registry: false


package com.nyzy.mai.controller;

import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import org.apache.catalina.loader.ParallelWebappClassLoader;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang3.StringUtils;
//import org.apache.commons.codec.binary.Base64;
//import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.ModelAttribute;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.nyzy.mai.tools.PageParameter;
import com.nyzy.mai.tools.QueryParameter;
import com.nyzy.mai.tools.SQLFilter;


public class BaseController {

	   protected HttpServletRequest request;
	   protected HttpServletResponse response;
	   protected HttpSession session;
	   protected ServletContext servletContext;
	 

	   @Autowired
		@Qualifier("jdbcTemplate")
		JdbcTemplate jdbcTemplate;
		
	   

		@Autowired
		ApplicationContext applicationContext;
	   @ModelAttribute
	   public void setReqAndRes(HttpServletRequest request, HttpServletResponse response){
	       this.request = request;
	       this.response = response;
	       this.session = request.getSession();
	       this.servletContext = request.getServletContext();
	       this.session.setMaxInactiveInterval(72000);
	   }
	 
	   /** 其他业务代码 */
	   public String getDefaultValueFromMap(Map<String,Object> reqMap,String key)
	   {
		   String	value="";
			if ((reqMap.get(key) != null)&&(!reqMap.get(key).equals("")) ) {
					value = SQLFilter.sqlInject(reqMap.get(key).toString());
				
			}
		   return value;
		   
	   }
	   
	   public String getDefaultStringFromMap(Map<String,Object> reqMap,String key)
	   {
		   String	value="";
		   if ((reqMap.get(key) != null)&&(!reqMap.get(key).equals("")) ) {
					value = SQLFilter.sqlInject(reqMap.get(key).toString());
				
			}
		   return value;
		   
	   }
	   public int getDefaultIntFromMap(Map<String,Object> reqMap,String key)
	   {
		   int value=0;
		   if ((reqMap.get(key) != null)&&(!reqMap.get(key).equals("")) ) {
				String svalue = SQLFilter.sqlInject(reqMap.get(key).toString());
				
				try {
					value=Integer.parseInt(svalue);
					
				} catch (Exception e) {
					// TODO: handle exception	
					value=0;
				}finally
				{
					
				}
				
			
		}
		   return value;
		   
	   }
	   
	   public PageParameter getPageParameterFromMap(Map<String,Object> reqMap) {
		   int pagesize=0;
			if ((reqMap.get("limit") != "") && (reqMap.get("limit") != null)) {
			String limit=SQLFilter.sqlInject(reqMap.get("limit").toString());
			
				pagesize= Integer.valueOf(limit).intValue();
			}
			int curpage=0;
			if ((reqMap.get("page") != "") && (reqMap.get("page") != null)) {
			String page=SQLFilter.sqlInject(reqMap.get("page").toString());
			
			
				curpage= Integer.valueOf(page).intValue();
			}
			int rows=0;
			if ((reqMap.get("total") != "") && (reqMap.get("total") != null)) {
			String total=SQLFilter.sqlInject(reqMap.get("total").toString());
				rows= Integer.valueOf(total).intValue();
			}
			PageParameter pp =  new PageParameter();
			pp.setCurpage(curpage);
			pp.setRows(rows);
			pp.setPagesize(pagesize);
			return pp;
			
		
	}
	   
	   public static boolean isNumericZidai(String str) {
	        for (int i = 0; i < str.length(); i++) {
	            System.out.println(str.charAt(i));
	            if (!Character.isDigit(str.charAt(i))) {
	                return false;
	            }
	        }
	        return true;
	    }
	   public Integer getrowscount(String sql) {
			
			
			 int count = jdbcTemplate.queryForObject(countSQL(sql), Integer.class);
			 System.out.println(countSQL(sql));
		 		 return count;

		}
	   public Integer getrowscount(String sql,Object args[]) {
			
			
			 int count = jdbcTemplate.queryForObject(countSQL(sql),args, Integer.class);
			 System.out.println("countSQL(sql):"+sql);
			
		 		 return count;

		}
	   public String countSQL(String sql)
	      {
	          String str = replaceAll2(sql, " from ", " from ");
	          int pos = str.indexOf(" from ");
	          if (pos > 0) str = str.substring(pos);
	          str = replaceAll2(str, " order ", " order ");
	          pos = str.lastIndexOf(" order ");
	          if (pos > 0) str = str.substring(0, pos);
	          System.out.println( String.format("select count(1) as rows %s", str));
	          return String.format("select count(1) as rows %s", str);
	      }
	 
	   //查询一个表的所有字段  
	   public String pagesql(String tablename,String keycolumn,String paixu,String where, int pagesize, int rows,int curpage) 
		{
			
			
			
			int pagetotal=0;
			
			 System.out.println("-----curpage-----");
			 System.out.println(curpage);
			if (pagesize < 3 || pagesize > 1000)
				{
				pagesize = 20;
				}
				
			
			if (rows < 1) {
				rows = getrowscount("select count(1) from ["+tablename+"] "+ where) ;
			}
			if (rows == 0) {
				curpage = 0;
				pagetotal=0;
				
			} else {
				int yushu = rows % pagesize;
				pagetotal = yushu == 0 ? (rows / pagesize) : ((rows / pagesize) + 1);			
			}
			 if (curpage < 1 || curpage > pagetotal) curpage = 1;
			 
			 
			//注意最后一项的top值  不能大于行数  
			 int t1= (curpage-1)*pagesize;
			 int t2=curpage*pagesize;
			 if (t2>rows)
			 {
				 t2=rows;
			 }
			 
			 System.out.println("-----t1-----");
			 System.out.println(t1);
			 System.out.println("-----t2-----");
			 System.out.println(t2);
			 
			String templatesql="SELECT w2.n, w1.* FROM {Tablename} w1, (SELECT TOP {t2} row_number() OVER (ORDER BY {paixu}) n, {keycolumn} FROM {Tablename} {where}) w2 " + 
					"WHERE w1.{keycolumn} = w2.{keycolumn} AND w2.n > {t1} ORDER BY w2.n ASC ";
			String sql= templatesql.replace("{Tablename}",tablename).replace("{paixu}",paixu).replace("{keycolumn}",keycolumn).replace("{where}",where).replace("{t1}",String.valueOf(t1)).replace("{t2}",String.valueOf(t2));
			
			
	         System.out.println(sql.toString());
	 		List<?> resultList = jdbcTemplate.queryForList(sql.toString());
	 		JSONObject jo = new JSONObject();
	 		jo.put("resultList",resultList);
	 		jo.put("pagesize",pagesize);
	 		jo.put("rows",rows);
	 		
	 		jo.put("curpage",curpage);
	 		Base64 base64 = new Base64();

	 		String base64Sign="";
			try {
				base64Sign = base64.encodeToString(sql.getBytes("UTF-8"));
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	 		jo.put("sql",base64Sign);
	 		//jo.put("sqlescape",escape(sql));
	 		
	        
	      
			 
			

	 		return JSON.toJSONString(jo);
		}
	   private String getFiedsfromfield(String fields)
	   {
			String[] fieldsarray = StringUtils.split(fields,",");
			
			String str="";
			for(int i=0;i<fieldsarray.length;i++)
			{
				if (i==0)
				{
					str="w1."+fieldsarray[i];
				}else
				{
					str=str+", "+"w1."+fieldsarray[i];
				}
			}
		   
		   return str;
	   }
	 //查询一个表的指定字段  
	   public String pagesql(String tablename,String fields,String keycolumn,String paixu,String where, int pagesize, int rows,int curpage) 
		{
			
			
			
			int pagetotal=0;
			
			 System.out.println("-----curpage-----");
			 System.out.println(curpage);
			if (pagesize < 3 || pagesize > 1000)
				{
				pagesize = 20;
				}
				
			
			if (rows < 1) {
				rows = getrowscount("select count(1) from ["+tablename+"] "+ where) ;
			}
			if (rows == 0) {
				curpage = 0;
				pagetotal=0;
				
			} else {
				int yushu = rows % pagesize;
				pagetotal = yushu == 0 ? (rows / pagesize) : ((rows / pagesize) + 1);			
			}
			 if (curpage < 1 || curpage > pagetotal) curpage = 1;
			 
			 
			//注意最后一项的top值  不能大于行数  
			 int t1= (curpage-1)*pagesize;
			 int t2=curpage*pagesize;
			 if (t2>rows)
			 {
				 t2=rows;
			 }
			 
			 System.out.println("-----t1-----");
			 System.out.println(t1);
			 System.out.println("-----t2-----");
			 System.out.println(t2);
			 
			String templatesql="SELECT w2.n, {fields} FROM {Tablename} w1, (SELECT TOP {t2} row_number() OVER (ORDER BY {paixu}) n, {keycolumn} FROM {Tablename} {where}) w2 " + 
					"WHERE w1.{keycolumn} = w2.{keycolumn} AND w2.n > {t1} ORDER BY w2.n ASC ";
			String sql= templatesql.replace("{Tablename}",tablename)
					.replace("{paixu}",paixu)
					.replace("{keycolumn}",keycolumn)
					.replace("{where}",where)
					.replace("{t1}",String.valueOf(t1))
					.replace("{t2}",String.valueOf(t2))
					.replace("{fields}",getFiedsfromfield(fields));
			
	         System.out.println(sql.toString());
	 		List<?> resultList = jdbcTemplate.queryForList(sql.toString());
	 		JSONObject jo = new JSONObject();
	 		jo.put("resultList",resultList);
	 		jo.put("pagesize",pagesize);
	 		jo.put("rows",rows);	 		
	 		jo.put("curpage",curpage);
	 		Base64 base64 = new Base64();

	 		String base64Sign="";
			try {
				base64Sign = base64.encodeToString(sql.getBytes("UTF-8"));
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	 		jo.put("sql",base64Sign);
	 		//jo.put("sqlescape",escape(sql));
	 		
	        
	      
			 
			

	 		return JSON.toJSONString(jo);
		}
	   
	   
	   //查询一个表的指定字段  使用?作参数
	   public String pagesql(String tablename,String fields,String keycolumn,String paixu,String where,Object args[], int pagesize, int rows,int curpage) 
		{
			
			
			
			int pagetotal=0;
			
			 System.out.println("-----curpage-----");
			 System.out.println(curpage);
			if (pagesize < 3 || pagesize > 1000)
				{
				pagesize = 20;
				}
				
			
			if (rows < 1) {
				rows = getrowscount("select count(1) from ["+tablename+"] "+ where,args) ;
			}
			if (rows == 0) {
				curpage = 0;
				pagetotal=0;
				
			} else {
				int yushu = rows % pagesize;
				pagetotal = yushu == 0 ? (rows / pagesize) : ((rows / pagesize) + 1);			
			}
			 if (curpage < 1 || curpage > pagetotal) curpage = 1;
			 
			 
			//注意最后一项的top值  不能大于行数  
			 int t1= (curpage-1)*pagesize;
			 int t2=curpage*pagesize;
			 if (t2>rows)
			 {
				 t2=rows;
			 }
			 
			 System.out.println("-----t1-----");
			 System.out.println(t1);
			 System.out.println("-----t2-----");
			 System.out.println(t2);
			 
			String templatesql="SELECT w2.n, {fields} FROM {Tablename} w1, (SELECT TOP {t2} row_number() OVER (ORDER BY {paixu}) n, {keycolumn} FROM {Tablename} {where}) w2 " + 
					"WHERE w1.{keycolumn} = w2.{keycolumn} AND w2.n > {t1} ORDER BY w2.n ASC ";
			String sql= templatesql.replace("{Tablename}",tablename)
					.replace("{paixu}",paixu)
					.replace("{keycolumn}",keycolumn)
					.replace("{where}",where)
					.replace("{t1}",String.valueOf(t1))
					.replace("{t2}",String.valueOf(t2))
					.replace("{fields}",getFiedsfromfield(fields));
			
	         System.out.println(sql.toString());
	 		List<?> resultList = jdbcTemplate.queryForList(sql.toString(),args);
	 		JSONObject jo = new JSONObject();
	 		jo.put("resultList",resultList);
	 		jo.put("pagesize",pagesize);
	 		jo.put("rows",rows);
	 		
	 		jo.put("curpage",curpage);
	 		Base64 base64 = new Base64();

	 		String base64Sign="";
			try {
				base64Sign = base64.encodeToString(sql.getBytes("UTF-8"));
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	 		jo.put("sql",base64Sign);
	 		//jo.put("sqlescape",escape(sql));
	 		
	        
	      
			 
			

	 		return JSON.toJSONString(jo);
		}
	   
	   
	   
	   //查询一个表的指定字段  使用?作参数 
	   public String pagesql(String tablename,String fields,String keycolumn,String paixu,String where,Object args[], PageParameter page) 
		{
			
			
			
			int pagetotal=0;
			
			 System.out.println("-----curpage-----");
			 System.out.println(page.getCurpage());
			if (page.getPagesize() < 3 || page.getPagesize() > 1000)
				{
				page.setPagesize(20);
				}
				
			
			if (page.getRows() < 1) {
				page.setRows(getrowscount("select count(1) from ["+tablename+"] "+ where,args)) ;
			}
			if (page.getRows() == 0) {
				page.setCurpage(0);
				pagetotal=0;
				
			} else {
				int yushu = page.getRows() % page.getPagesize();
				pagetotal = yushu == 0 ? (page.getRows() / page.getPagesize()) : ((page.getRows() / page.getPagesize()) + 1);			
			}
			 if (page.getCurpage() < 1 || page.getCurpage() > pagetotal) page.setCurpage(1);
			 
			 
			//注意最后一项的top值  不能大于行数  
			 int t1= (page.getCurpage()-1)*page.getPagesize();
			 int t2=page.getCurpage()*page.getPagesize();
			 if (t2>page.getRows())
			 {
				 t2=page.getRows();
			 }
			 
			 System.out.println("-----t1-----");
			 System.out.println(t1);
			 System.out.println("-----t2-----");
			 System.out.println(t2);
			 
			String templatesql="SELECT w2.n, {fields} FROM {Tablename} w1, (SELECT TOP {t2} row_number() OVER (ORDER BY {paixu}) n, {keycolumn} FROM {Tablename} {where}) w2 " + 
					"WHERE w1.{keycolumn} = w2.{keycolumn} AND w2.n > {t1} ORDER BY w2.n ASC ";
			String sql= templatesql.replace("{Tablename}",tablename)
					.replace("{paixu}",paixu)
					.replace("{keycolumn}",keycolumn)
					.replace("{where}",where)
					.replace("{t1}",String.valueOf(t1))
					.replace("{t2}",String.valueOf(t2))
					.replace("{fields}",getFiedsfromfield(fields));
			
	         System.out.println(sql.toString());
	 		List<?> resultList = jdbcTemplate.queryForList(sql.toString(),args);
	 		JSONObject jo = new JSONObject();
	 		jo.put("resultList",resultList);
	 		jo.put("pagesize",page.getPagesize());
	 		jo.put("rows",page.getRows());
	 		
	 		jo.put("curpage",page.getCurpage());
	 		Base64 base64 = new Base64();

	 		String base64Sign="";
			try {
				base64Sign = base64.encodeToString(sql.getBytes("UTF-8"));
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	 		jo.put("sql",base64Sign);
	 		//jo.put("sqlescape",escape(sql));
	 		
	        
	      
			 
			

	 		return JSON.toJSONString(jo);
		}
	   
	   
	   //查询一个表的指定字段  使用?作参数 
	   public JSONObject pagesql(QueryParameter qp,Object args[], PageParameter page) 
		{
			
		   String tablename=qp.getTablename();
		   String fields=qp.getFields();
		   String keycolumn= qp.getKeycolumn();
		   String paixu =qp.getPaixu();
		   String where = qp.getWhere();
			
			int pagetotal=0;
			
			 System.out.println("-----curpage-----");
			 System.out.println(page.getCurpage());
			if (page.getPagesize() < 3 || page.getPagesize() > 1000)
				{
				page.setPagesize(20);
				}
				
			
			if (page.getRows() < 1) {
				page.setRows(getrowscount("select count(1) from ["+tablename+"] "+ where,args)) ;
			}
			if (page.getRows() == 0) {
				page.setCurpage(0);
				pagetotal=0;
				
			} else {
				int yushu = page.getRows() % page.getPagesize();
				pagetotal = yushu == 0 ? (page.getRows() / page.getPagesize()) : ((page.getRows() / page.getPagesize()) + 1);			
			}
			 if (page.getCurpage() < 1 || page.getCurpage() > pagetotal) page.setCurpage(1);
			 
			 System.out.println("-----page.getCurpage()-----");
			 System.out.println(page.getCurpage());
			 System.out.println("-----page.getPagesize()-----");
			 System.out.println(page.getPagesize());
			 
			//注意最后一项的top值  不能大于行数  
			 int t1= (page.getCurpage()-1)*page.getPagesize();
			 int t2=page.getCurpage()*page.getPagesize();
			 if (t2>page.getRows())
			 {
				 t2=page.getRows();
			 }
			 
			 System.out.println("-----t1-----");
			 System.out.println(t1);
			 System.out.println("-----t2-----");
			 System.out.println(t2);
			 
			String templatesql="SELECT w2.n, {fields} FROM {Tablename} w1, (SELECT TOP {t2} row_number() OVER (ORDER BY {paixu}) n, {keycolumn} FROM {Tablename} {where}) w2 " + 
					"WHERE w1.{keycolumn} = w2.{keycolumn} AND w2.n > {t1} ORDER BY w2.n ASC ";
			String sql= templatesql.replace("{Tablename}",tablename)
					.replace("{paixu}",paixu)
					.replace("{keycolumn}",keycolumn)
					.replace("{where}",where)
					.replace("{t1}",String.valueOf(t1))
					.replace("{t2}",String.valueOf(t2))
					.replace("{fields}",getFiedsfromfield(fields));
			
	         System.out.println(sql.toString());
	 		List<?> resultList = jdbcTemplate.queryForList(sql.toString(),args);
	 		JSONObject jo = new JSONObject();
	 		jo.put("resultList",resultList);
	 		jo.put("pagesize",page.getPagesize());
	 		jo.put("rows",page.getRows());
	 		jo.put("curpage",page.getCurpage());
	 		Base64 base64 = new Base64();

	 		String base64Sign="";
			try {
				base64Sign = base64.encodeToString(sql.getBytes("UTF-8"));
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	 		jo.put("sql",base64Sign);
	 		//jo.put("sqlescape",escape(sql));	
	        
	      
			 
			

	 		return jo;
		}
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   //查询一个表的所有字段  pageparams 格式为为 （ “页面大小，当前页，总记录数”）
	   public String pagesql(String tablename,String keycolumn,String paixu,String where, String pageparams) 
	   {
	   	String[] pagearray = StringUtils.split(pageparams,",");
	   	
	   	int pagesize = 0;
	   	int rows = 0;
	   	int curpage = 0;
	   	int pagetotal=0;
	   	if (pagearray.length != 3) {

	   		pagesize = 0;
	   		curpage = 0;
	   		rows = 0;
	   	} else {
	   		pagesize = Integer.parseInt(pagearray[0]);
	   		curpage = Integer.parseInt(pagearray[1]);
	   		rows = Integer.parseInt(pagearray[2]);
	   		
	   	}
	   	 System.out.println("-----curpage-----");
	   	 System.out.println(curpage);
	   	if (pagesize < 3 || pagesize > 1000)
	   		{
	   		pagesize = 20;
	   		}
	   		
	   	
	   	if (rows < 1) {
	   		rows = getrowscount("select count(1) from ["+tablename+"] "+ where) ;
	   	}
	   	if (rows == 0) {
	   		curpage = 0;
	   		pagetotal=0;
	   		
	   	} else {
	   		int yushu = rows % pagesize;
	   		pagetotal = yushu == 0 ? (rows / pagesize) : ((rows / pagesize) + 1);			
	   	}
	   	 if (curpage < 1 || curpage > pagetotal) curpage = 1;
	   	 
	   	 
	   	//注意最后一项的top值  不能大于行数  
	   	 int t1= (curpage-1)*pagesize;
	   	 int t2=curpage*pagesize;
	   	 if (t2>rows)
	   	 {
	   		 t2=rows;
	   	 }
	   	 
	   	 System.out.println("-----t1-----");
	   	 System.out.println(t1);
	   	 System.out.println("-----t2-----");
	   	 System.out.println(t2);
	   	 
	   	String templatesql="SELECT w2.n, w1.*,CONVERT(varchar(100), w1.billdate, 23) as billdate2 FROM {Tablename} w1, (SELECT TOP {t2} row_number() OVER (ORDER BY {paixu}) n, {keycolumn} FROM {Tablename} {where}) w2 " + 
	   			"WHERE w1.{keycolumn} = w2.{keycolumn} AND w2.n > {t1} ORDER BY w2.n ASC ";
	   	String sql= templatesql.replace("{Tablename}",tablename).replace("{paixu}",paixu).replace("{keycolumn}",keycolumn).replace("{where}",where).replace("{t1}",String.valueOf(t1)).replace("{t2}",String.valueOf(t2));
	   	
	        System.out.println(sql.toString());
	   		List<?> resultList = jdbcTemplate.queryForList(sql.toString());
	   		JSONObject jo = new JSONObject();
	   		jo.put("resultList",resultList);
	   		jo.put("pagesize",pagesize);
	   		jo.put("rows",rows);
	   		
	   		jo.put("curpage",curpage);
	   		Base64 base64 = new Base64();

	   		String base64Sign="";
	   	try {
	   		base64Sign = base64.encodeToString(sql.getBytes("UTF-8"));
	   	} catch (UnsupportedEncodingException e) {
	   		// TODO Auto-generated catch block
	   		e.printStackTrace();
	   	}
	   		jo.put("sql",base64Sign);
	   		//jo.put("sqlescape",escape(sql));
	   		
	       
	     
	   	 
	   	

	   		return JSON.toJSONString(jo);
	   }
	   public String replaceAll2(String input,String regex,String replacement){  
	         Pattern p = Pattern.compile(regex,Pattern.CASE_INSENSITIVE);  
	            Matcher m = p.matcher(input);  
	            StringBuffer sb = new StringBuffer();  
	            boolean result = m.find();  
	            while (result)  
	            {  
	                m.appendReplacement(sb, replacement);  
	                result = m.find();  
	            }  
	            m.appendTail(sb);  
	            return sb.toString();  
	    }  
	  public String replaceAll(String input,String regex,String replacement){  
	        
	            return input.replaceAll(regex,replacement);  
	    } 
	  
	  
	  //三层查询分项可以多表查询 pageparams 格式为为 （ “页面大小，当前页，总记录数”）
		public String fetchpage(String sql, String pageparams) {
			String[] pagearray = StringUtils.split(pageparams,",");
					
					int pagesize = 0;
					int rows = 0;
					int curpage = 0;
					int pagetotal=0;
					if (pagearray.length != 3) {

						pagesize = 0;
						curpage = 0;
						rows = 0;
					} else {
						pagesize = Integer.parseInt(pagearray[0]);
						curpage = Integer.parseInt(pagearray[1]);
						rows = Integer.parseInt(pagearray[2]);
						
					}
					 System.out.println("-----curpage-----");
					 System.out.println(curpage);
					if (pagesize < 3 || pagesize > 1000)
						{
						pagesize = 20;
						}
						
					
					if (rows < 1) {
						rows = getrowscount(sql);
					}
					if (rows == 0) {
						curpage = 0;
						pagetotal=0;
						
					} else {
						int yushu = rows % pagesize;
						pagetotal = yushu == 0 ? (rows / pagesize) : ((rows / pagesize) + 1);			
					}
					 if (curpage < 1 || curpage > pagetotal) curpage = 1;
					 
					 int aa = curpage * pagesize;//当前页* 每页数量
			         int bb = aa < rows ? pagesize : (rows - aa);
			         if (bb < 1 || curpage < pagetotal) bb = pagesize;
			         System.out.println(String.format("### 计算 最多条数{0} 记录条数{1} 当前页pcur：{2} 每页数size：{3} ", aa, bb, curpage, pagesize));

			         String str1 = replaceAll2(sql, "^.* order ", " order ");//获取order by 子句
			         String str2 = replaceAll2(sql, " order [^\\)]*$", "");//获取order by 之前的
			         str2 = replaceAll2(str2, "^ *select ", "");  //字段  从from 等等  where 等等

			         StringBuilder sqlsb = new StringBuilder(1024);
			         sqlsb.append(" select * from (select top ").append(bb).append(" * from (select top ").append(aa);
			         sqlsb.append(" ").append(str2).append(" ").append(str1);
			         sqlsb.append(") T_01_ ");
			         String str1revs = revs(str1, "T_01_"); //第一翻转order by
			         sqlsb.append(str1revs).append(") T_02_ ");
			         String str1revsrevs = revs(str1revs, "T_02_");//第一翻转order by
			         sqlsb.append(str1revsrevs).append(" ");
			         String sqls = sqlsb.toString();//最终的分页语句
			         StringBuilder pageinfo = new StringBuilder(1024); ;//pages:rows,page,size,pcur 存分页参数
			         pageinfo.append(rows).append(",").append(pagetotal).append(",");
			         pageinfo.append(pagesize).append(",").append(curpage);
			         String pageinfos = pageinfo.toString();
			         //性能问题: 当大数据集1w以上在超出70%时，可以反向获取更少记录（待定），应需重新计算条数,获取数,排序 
			      
			         System.out.println(sqls.toString());
			 		List<?> resultList = jdbcTemplate.queryForList(sqls.toString());
			 		JSONObject jo = new JSONObject();
			 		jo.put("resultList",resultList);
			 		jo.put("pagesize",pagesize);
			 		jo.put("rows",rows);
			 		jo.put("pagetotal",pagetotal);
			 		jo.put("curpage",curpage);
			 		Base64 base64 = new Base64();

			 		String base64Sign="";
					try {
						base64Sign = base64.encodeToString(sql.getBytes("UTF-8"));
					} catch (UnsupportedEncodingException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			 		jo.put("sql",base64Sign);
			 		//jo.put("sqlescape",escape(sql));
			 		
			        
			       //  return String.format("{0}\"p4\":\"{1}\",\"qs\":\"{4}\",\"records\":[{2}]{3}", "{", pageinfos, jsons, "}", sql);
					 
					 
					

			 		return JSON.toJSONString(jo);
				}

				
				  public String revs(String str, String vn) //order by b.date desc,c.id asc     ； T_01_ 别名
			      {
					 
			          str = replaceAll2(str, " desc *", " @asc@ ");//order by b.date @asc@, c.id @desc@
			          str = replaceAll2(str, " asc *", " @desc@ ");
			          str =replaceAll2(str, " [^ ]*\\.", " ").replaceAll(" by ", " by@, ");//order by date @asc@, id @desc@  //order by@, date @asc@, id @desc@
			          str = replaceAll2(str, ", *([^ ]*) ", String.format(", %s.$1 ", vn));//string.Format 的结果",  T_01_.$1"   //order by@, T_01_.date @asc@, T_01_.id @desc@
			          return str.replaceAll("@, ", " ").replaceAll("@", "");   //order by  T_01_.date @asc T_01_.id @desc@  order by  T_01_.date asc T_01_.id desc
			      }
				  
				  public  String trim(String args,char beTrim) {

					  int st = 0;

					  int len = args.length();

					  char[] val = args.toCharArray();

					  char sbeTrim = beTrim;

					  while ((st < len) && (val[st] <= sbeTrim)) {

					  st++;

					  }

					  while ((st < len) && (val[len - 1] <= sbeTrim)) {

					  len--;

					  }

					  return ((st > 0) || (len < args.length())) ? args.substring(st, len) : args;

					  }
	   
	}


	 
	/*通过spring提供的RequestContextHolder在非contrller层获取request和response对象
		HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
		HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
		HttpServletResponse response = ((ServletWebRequest)RequestContextHolder.getRequestAttributes()).getResponse();
		ServletContext context = ContextLoader.getCurrentWebApplicationContext().getServletContext();
*/

package com.nyzy.mai.controller;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

@CrossOrigin(origins="*",allowCredentials = "true")
@RestController
@RequestMapping("/alert")
public class AlertController extends BaseController {
	
	@PostMapping("/alertcount")
	public String Alertcount(@RequestBody Map<String,Object> reqMap)
	{
	String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		//失效期商品报警
	int validdateproduct=	this.jdbcTemplate.queryForObject("select count(*) from srv_storehouse  where validdate < GETDATE() and validdate <> '1900-01-01 00:00:00.0' and validdate <> '1900-01-01 00:00:00.000' and validdate <> '' and srv_compid=?",aList.toArray(), Integer.class);
	//库存检查报警
	int maintaindayproduct=	this.jdbcTemplate.queryForObject("select count(*) from srv_storehouse  where DateADD(Day, Maintainday, Yhdate) < GETDATE() and validdate <> '1900-01-01 00:00:00.0' and validdate <> '1900-01-01 00:00:00.000' and validdate <> ''   and MaintainDay<>0 and srv_compid=?",aList.toArray(),  Integer.class);
    //商品gmp效期报警
	int gmpvalidproduct=	this.jdbcTemplate.queryForObject(" select count(*) from srv_products where  deleted = 0  and (GMPvalid <> '1900-01-01 00:00:00.0' ) and (GMPvalid <> '1900-01-01 00:00:00.000' ) and (GMPvalid <> '' ) and  GMPvalid <GETDATE() and srv_compid=?",aList.toArray(),  Integer.class);
	//商品注册证号报警
	int percodevalidproduct=this.jdbcTemplate.queryForObject(" select  count(*) from srv_products where  deleted = 0  and PerCodevalid <> '1900-01-01 00:00:00.0' and PerCodevalid <> '1900-01-01 00:00:00.000' and PerCodevalid <> '' and PerCodevalid < GETDATE() and srv_compid=?", aList.toArray(), Integer.class);
	
	//往来单位委托书报警
	int validwtorder=this.jdbcTemplate.queryForObject("  select count(*) from srv_wtorder where validDate< GETDATE() and validdate <> '1900-01-01 00:00:00.0' and validdate <> '1900-01-01 00:00:00.000' and srv_compid=? ", aList.toArray(), Integer.class);
	
	//往来单位gmp资质报警
	int gmpvalidclient=this.jdbcTemplate.queryForObject(" select count(*)  FROM [srv_gmpindex]  where   (validdate <> '1900-01-01 00:00:00.0' ) and (validdate <> '1900-01-01 00:00:00.000' ) and validdate <> '' and  validdate <GETDATE() and srv_compid=?",aList.toArray(),  Integer.class);
	
	//往来单位证照预警
	int report2client=this.jdbcTemplate.queryForObject(" select count(*)  FROM [srv_clientreport2] where (validTime <> '1900-01-01 00:00:00.0' ) and (validTime <> '1900-01-01 00:00:00.000' ) and validTime <> '' and  validTime <GETDATE() and srv_compid=?",aList.toArray(),  Integer.class);
	
	JSONObject jo1 = new JSONObject();
	jo1.put("name", "失效期商品报警");
	jo1.put("count", validdateproduct);
	jo1.put("subpath", "validdateproduct");
	
	JSONObject jo2 = new JSONObject();
	jo2.put("name", "库存检查报警");
	jo2.put("count", maintaindayproduct);
	jo2.put("subpath", "maintaindayproduct");
	
	JSONObject jo3 = new JSONObject();
	jo3.put("name", "商品gmp效期报警");
	jo3.put("count", gmpvalidproduct);
	jo3.put("subpath", "gmpvalidproduct");
	
	JSONObject jo4 = new JSONObject();
	jo4.put("name", "商品注册证号报警");
	jo4.put("count", percodevalidproduct);
	jo4.put("subpath", "percodevalidproduct");
	
	JSONObject jo5 = new JSONObject();
	jo5.put("name", "往来单位委托书报警");
	jo5.put("count", validwtorder);
	jo5.put("subpath", "validwtorder");
	
	JSONObject jo6 = new JSONObject();
	jo6.put("name", "往来单位gmp资质报警");
	jo6.put("count", gmpvalidclient);
	jo6.put("subpath", "gmpvalidclient");

	
	JSONObject jo7 = new JSONObject();
	jo7.put("name", "往来单位证照预警");
	jo7.put("count", report2client);
	jo7.put("subpath", "report2client");
	
	JSONArray jArray= new JSONArray();
	jArray.add(jo1);
	jArray.add(jo2);
	jArray.add(jo3);
	jArray.add(jo4);
	jArray.add(jo5);
	jArray.add(jo6);
	jArray.add(jo7);

	
	 JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", jArray);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();
		
	}
	
	@PostMapping("/validdateproduct")
	public String validdateproduct(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("select * from srv_storehouse  where validdate < GETDATE()  and validdate <> '1900-01-01 00:00:00.0' and validdate <> '1900-01-01 00:00:00.000' and validdate <> '' and srv_compid=?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}
	
	@PostMapping("/maintaindayproduct")
	public String maintaindayproduct(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("select * from srv_storehouse  where DateADD(Day, Maintainday, Yhdate) < GETDATE()   and MaintainDay<>0  and validdate <> '1900-01-01 00:00:00.0' and validdate <> '1900-01-01 00:00:00.000' and validdate <> ''  and srv_compid=?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}
	
	@PostMapping("/gmpvalidproduct")
	public String gmpvalidproduct(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("select * from srv_products where  deleted = 0  and (GMPvalid <> '1900-01-01 00:00:00.0' ) and (GMPvalid <> '1900-01-01 00:00:00.000' ) and (GMPvalid <> '' ) and  GMPvalid <GETDATE()  and srv_compid=?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}
	
	@PostMapping("/percodevalidproduct")
	public String percodevalidproduct(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("select  * from srv_products where  deleted = 0   and PerCodevalid <> '1900-01-01 00:00:00.0' and PerCodevalid <> '1900-01-01 00:00:00.000' and PerCodevalid <> ''  and PerCodevalid < GETDATE()  and srv_compid=?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}
	
	@PostMapping("/validwtorder")
	public String validwtorder(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("  select * from srv_wtorder where validDate< GETDATE() and validdate <> '1900-01-01 00:00:00.0' and validdate <> '1900-01-01 00:00:00.000' and srv_compid=?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}
	
	@PostMapping("/gmpvalidclient")
	public String gmpvalidclient(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("  select *  FROM [srv_gmpindex]  where   (validdate <> '1900-01-01 00:00:00.0' ) and (validdate <> '1900-01-01 00:00:00.000' ) and validdate <> '' and  validdate <GETDATE()  and srv_compid=?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}
	
	@PostMapping("/report2client")
	public String report2client(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("select *  FROM [srv_clientreport2] where (validTime <> '1900-01-01 00:00:00.0' ) and (validTime <> '1900-01-01 00:00:00.000' ) and validTime <> ''  and  validTime <GETDATE()  and srv_compid=?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}
	
	@PostMapping("/storezeroproduct")
	public String storeZeroProduct(@RequestBody Map<String,Object> reqMap)
	{
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_compid);
		aList.add(srv_compid);
		List<?> resultList = 	this.jdbcTemplate.queryForList("select s.quantity,p.* from (select srv_compid,p_id, COUNT(quantity)as quantity from srv_storehouse where srv_compid = ? group by srv_compid,p_id ) s,srv_products p " + 
				
				" where s.srv_compid = p.srv_compid and s.p_id= p.product_id and p.srv_compid = ?",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
	}

}

package com.nyzy.mai.controller;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSONObject;
import com.nyzy.mai.tools.PageParameter;
import com.nyzy.mai.tools.QueryParameter;

@CrossOrigin(origins="*",allowCredentials = "true")
@RestController
@RequestMapping("/bill")
public class BillController extends BaseController {

	@PostMapping("/getbilllist")
	public String getbilllist(@RequestBody Map<String,Object> reqMap) {
		
		String srv_compname =getDefaultValueFromMap(reqMap,"srv_compname");
		
		int srv_compid =getDefaultIntFromMap(reqMap,"srv_compid");
		String billnumber =getDefaultValueFromMap(reqMap,"billnumber");
		String billtype_name =getDefaultValueFromMap(reqMap,"billtype_name");
		String client_name =getDefaultValueFromMap(reqMap,"client_name");
		String begindate =getDefaultValueFromMap(reqMap,"begindate");
		String enddate =getDefaultValueFromMap(reqMap,"enddate");
		String upcompany =getDefaultValueFromMap(reqMap,"upcompany");
		String downcompany =getDefaultValueFromMap(reqMap,"downcompany");		
		String containred =getDefaultValueFromMap(reqMap,"containred");
		 System.out.println("-------containred------");
		 System.out.println(""+containred+"");
		
		
		String sort=getDefaultValueFromMap(reqMap,"sort");
		if (sort.equals("")) {
			sort="billdate desc";
		}
		
		QueryParameter qp= new QueryParameter();
		qp.setTablename("srv_billidx");		
		qp.setFields("*");	
		qp.setKeycolumn("srv_billidx");
		String where="";
		ArrayList<Object> aList = new ArrayList<Object>();
		
		if (containred.equals("true"))
		{
		where =" where srv_compid = ? and srv_compname like ? and billnumber like ? and billtype_name like ? and client_name like ?  and billdate >= ? and billdate <= ?  and upcompany like ? and downcompany like ? ";
		aList.add(srv_compid);
		aList.add("%"+srv_compname+"%");
		 System.out.println("-------srv_compname------");
		 System.out.println("%"+srv_compname+"%");
		aList.add("%"+billnumber+"%");
		 System.out.println("-------billnumber------");
		 System.out.println("%"+billnumber+"%");
		aList.add("%"+billtype_name+"%");
		 System.out.println("-------billtype_name------");
		 System.out.println("%"+billtype_name+"%");
		aList.add("%"+client_name+"%");
		 System.out.println("-------client_name------");
		 System.out.println("%"+client_name+"%");
		aList.add(begindate);
		 System.out.println("-------begindate------");
		 System.out.println(begindate);
		aList.add(enddate);
		 System.out.println("-------enddate------");
		 System.out.println(enddate);
		aList.add("%"+upcompany+"%");
		 System.out.println("-------upcompany------");
		 System.out.println("%"+upcompany+"%");
		aList.add("%"+downcompany+"%");	
		 System.out.println("-------downcompany------");
		 System.out.println("%"+downcompany+"%");
		}else {
			
			where =" where  srv_compid = ? and  srv_compname like ? and billnumber like ? and billtype_name like ? and client_name like ?  and billdate >= ? and billdate <= ?  and upcompany like ? and downcompany like ? and billstates ='0' ";
			aList.add(srv_compid);
			aList.add("%"+srv_compname+"%");
			 System.out.println("-------srv_compname------");
			 System.out.println("%"+srv_compname+"%");
			aList.add("%"+billnumber+"%");
			 System.out.println("-------billnumber------");
			 System.out.println("%"+billnumber+"%");
			aList.add("%"+billtype_name+"%");
			 System.out.println("-------billtype_name------");
			 System.out.println("%"+billtype_name+"%");
			aList.add("%"+client_name+"%");
			 System.out.println("-------client_name------");
			 System.out.println("%"+client_name+"%");
			aList.add(begindate);
			 System.out.println("-------begindate------");
			 System.out.println(begindate);
			aList.add(enddate);
			 System.out.println("-------enddate------");
			 System.out.println(enddate);
			aList.add("%"+upcompany+"%");
			 System.out.println("-------upcompany------");
			 System.out.println("%"+upcompany+"%");
			aList.add("%"+downcompany+"%");	
			 System.out.println("-------downcompany------");
			 System.out.println("%"+downcompany+"%");
			
		}
		
					
		qp.setWhere(where);		
		qp.setPaixu(sort);
			
			System.out.println("--------paixu1-------");
			System.out.println(qp.getPaixu());
		
		
		
       PageParameter pp= new PageParameter();
    
     
       pp.setCurpage(getDefaultIntFromMap(reqMap, "page"));
       
   
       
       System.out.println("--------limit-------:"+getDefaultIntFromMap(reqMap, "limit"));
       pp.setPagesize(getDefaultIntFromMap(reqMap, "limit"));
       pp.setRows(getDefaultIntFromMap(reqMap, "total"));
		JSONObject joJsonObject= pagesql(qp,aList.toArray(),pp);
		
		 System.out.println(joJsonObject.toJSONString());
		 
	        JSONObject jo= new JSONObject();
			jo.put("token",session.getId());
			jo.put("opstr", "ok");
			jo.put("pagedata", joJsonObject);
			jo.put("code",20000);
			 System.out.println(jo.toJSONString()); 
			return jo.toJSONString();	
	}
	@PostMapping("/getonebilldetail")
	public String getonebilldetail(@RequestBody Map<String,Object> reqMap) {
		int srv_billidx =getDefaultIntFromMap(reqMap,"srv_billidx");
		
		ArrayList<Object> aList = new ArrayList<Object>();
		aList.add(srv_billidx);
		List<?> resultList = 	this.jdbcTemplate.queryForList(" select * from srv_billdetail  where srv_billidx = ? ",aList.toArray());		
		JSONObject jo= new JSONObject();
		jo.put("token",session.getId());
		jo.put("opstr", "ok");
		jo.put("pagedata", resultList);
		jo.put("code",20000);
		 System.out.println(jo.toJSONString()); 
		return jo.toJSONString();	
		
	}
}

az44yao
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Spring Boot中使用jdbctemplate 操作MYSQL数据库实例
08-30
然后，为了能在Spring Boot应用中使用JdbcTemplate，我们需要在启动类或任何其他需要操作数据库的类中注入JdbcTemplate。这里是一个简单的启动类示例： ```java import org.springframework.beans.factory....
spring-boot 引用jdbcTemplate bean could not be found
Ntozot的博客
03-23 3494
项目结构如下： father: module A module B module A中引入jdbcTemplate 进行数据库操作； module B依赖于module A。 B打成jar包运行，报错： required a bean of type 'org.springframework.jdbc.core.JdbcTemplate' that could not be found....
Spring boot + jpa 配置多数据源
weixin_44712778的博客
06-08 2008
配置文件 # mian DB properties: main.datasource.jdbcUrl=jdbc:mysql://localhost:3306/emipe?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai main.datasource.username=root main.datasource.password=root main.datasource.d
一个诡异的A component required a bean of type异常
louis_lee7812的专栏
10-22 2万+
A component required a bean of type 'org.springframework.security.authentication.AuthenticationManager' that could not be found. springboot的主启动类，与包下各个组件的加载问题。因为启动类上面的@SpringBootApplication里面默认包含了一个@ComponentScan注解，这个注解默认情况下是扫描该类所属包下面的所有类，包含子目录中的类。
bug---Field jdbcTemplate in com.e.demo.controller.lianjie required a bean of type ‘org.springframewo
qw160的博客
04-28 810
错误 springboot连接阿里云rds数据库报错 Field jdbcTemplate in com.e.demo.controller.lianjie required a bean of type ‘org.springframework.jdbc.core.JdbcTemplate’ that could not be found.Consider defining a bean of type ‘org.springframework.jdbc.core.JdbcTemplate’ in you
SpringBoot引入jdbcTemplate时报错Field jdbcTemplate in com.x required a bean of type...could not be found
Hu1510592894的博客
03-27 1万+
在学习SpringBoot的时候，引入jdbcTemplate时出了一点错误 Field jdbcTemplate in com.hsy.service.UserServiceImpl required a bean of type 'org.springframework.jdbc.core.JdbcTemplate' that could not be found. 在网上找了很多资料，最...
Spring Boot 与 Kotlin 使用JdbcTemplate连接MySQL数据库的方法
08-28
接下来，我们将详细讨论如何在Spring Boot项目中配置数据源，并使用JdbcTemplate进行数据访问。 首先，我们需要在`build.gradle`文件中引入必要的依赖。对于Spring Boot，添加`spring-boot-starter-jdbc`以包含...
Spring Boot多数据源(JdbcTemplate)配置与使用
06-20
通过以上步骤，我们成功地在Spring Boot应用中配置了多数据源，并使用了`JdbcTemplate`进行数据库操作。这个过程的关键在于正确配置数据源，创建并注入相应的`JdbcTemplate` bean，然后在业务逻辑中选择合适的数据源...
Spring Boot+Jpa多数据源配置Demo（支持不同数据库）
04-11
Spring Boot结合JPA（Java Persistence API）和JdbcTemplate，为开发者提供了灵活且强大的多数据源配置能力。本示例将详细介绍如何在Spring Boot项目中实现这样的配置，以支持不同类型的数据库。 首先，我们要理解...
Spring Boot多数据源及其事务管理配置方法
08-30
Spring Boot多数据源及其事务管理配置是开发中常见的需求，特别是在大型系统中，可能需要连接多个数据库以实现数据隔离或负载均衡。以下是对这个主题的详细讲解。 **一、准备工作** 在开始配置多数据源之前，我们...
Parameter 0 of constructor in work.dao.impl.SqlDaoImpl required a bean of type 'org.springframework.
iyangijava的博客
07-24 4064
Parameter 0 of constructor in work.dao.impl.SqlDaoImpl required a bean of type 'org.springframework.jdbc.core.JdbcTemplate' that could not be found. - Bean method 'jdbcTemplate' in 'JdbcTemplateAutoCo...
springboot多数据源JdbcTemplate
虾米大王的学习历程
08-09 1448
所谓多数据源，就是一个Java EE项目中采用了不同数据库实例中的多个库，或者同一个数据库实例中多个不同的库。一般来说，采用MyCat等分布式数据库中间件是比较好的解决方案，这样可以把数据库读写分离、分库分表、备份等操作交给中间件去做，Java代码只需要专注于业务即可。不过，这并不意味着无法使用Java代码解决类似的问题，在Spring Framework中就可以配置多数据源，Spring Boot继承其衣钵，只不过配置方式有所变化。
【错误：A component required a bean of type ‘xxx‘ that could not be found.解决办法】
蛋饼吧的博客
04-23 7860
*************************** APPLICATION FAILED TO START *************************** Description: A component required a bean of type 'org.redisson.Redisson' that could not be found. Action: Consider defining a bean of type 'org.redisson.Red
A component required a bean of type ‘XXX‘ that could not be found 解决办法
热门推荐
littleox_frighting的博客
09-21 7万+
A component required a bean of type ‘XXX‘ that could not be found 解决办法
required a bean of type ‘org.springframework.web.client.RestTemplate‘ that could not be found 解决方法
程序员大佬超的博客
06-22 1万+
在使用SpringBoot时不能自动注入RestTemplate，具体报错信息如下 Field restTemplate in 'xxx' required a bean of type 'org.springframework.web.client.RestTemplate' that could not be found. The injection point has the following annotations: - @org.springframework.beans.factory.a
Consider defining a bean of type 'org.springframework.jdbc.core.JdbcTemplate' in your configuration
嘻哈熊的专栏
04-16 1万+
报错信息： Description: Field customerRepo in com.chnvas.tour.auth.controller.AuthController required a bean of type 'org.springframework.jdbc.core.JdbcTemplate' that could not be found. Action: Consi...
报错：A component required a bean of type ‘xxx.xxx.x.xxxx.‘ that could not be found
最新发布
m0_61843855的博客
10-17 9063
1、检查代码controller层、Service层、Dao层。哪怕你component就算定义了，没事别到，都是打瞎。3、以上都没错，说明最基本的代码没问题。如果还有问题，自求多福吧，怪我才疏学浅。原因：也就是没有成功将bean注入。然后就是启动类扫描问题。
常见问题(持续更新)
fishinair123的专栏
03-29 1万+
近期整理的初级开发遇到的问题，希望对大家有用。 1、Unsatisfied dependency expressed through field 'baseMapper' 于是在pom.xml中搜索mybatis关键字，发现的确有多个， 把其他有mybatis关键字的都删掉，只留下以下一份mybatis starter <!--mybatis-plus支持 -》 Mybatis-Plus学习官方文档：https://baomidou.oschina.io/mybatis-pl...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值