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();
}
}