62,614
社区成员
发帖
与我相关
我的任务
分享一个奇怪的问题，java连接sql
我用java连接数据库执行如下语句，当然还有其他语句
其他语句都正常，但是这个语句，如果连续运行程序，就会出现，outdate字段无效，求高手啊。
use hrmscfg IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1 IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2 IF OBJECT_ID('tempdb..#temp3') IS NOT NULL DROP TABLE #temp3 IF OBJECT_ID('tempdb..#temp4') IS NOT NULL DROP TABLE #temp4 select replace(wewlftm,':','') as intim,replace (wewlttm,':','') as outtim,(case when wewlftm>wewlttm then replace((convert(char(10),(dateadd(day,1,cast(cast(wewldte as varchar) as datetime))),111)),'/','') end) as outdte, (case when wewlftm<=wewlttm then wewldte end)as indte, wewlwno as wno, wewljia as jia,wewldte into #temp from hrswewl update #temp set outdte = cast(wewldte as char(10)) where outdte is NULL update #temp set indte = cast(wewldte as char(10)) where indte is NULL select (cast(indte as char(8))+cast(intim as char(4))) intime,(cast(outdte as char(8))+cast(outtim as char(4))) outtime,wno as wno,jia as jia into #temp2 from #temp where outdte <>'0' select intime as intime,outtime as outtime,wno as wno,jia as jia into #temp3 from #temp2 where jia like 'A%' or jia like 'B%' or jia like 'C%' or jia like 'K%' select intime as intime,outtime as outtime,wno as wno,jia as jia into #temp4 from #temp2 where jia like 'D%' or jia like 'E%' select * from #temp3,#temp4 where #temp3.wno = #temp4.wno and ((#temp3.intime>#temp4.intime and #temp3.intime< #temp4.outtime) or (#temp3.intime<#temp4.outtime and #temp3.outtime>#temp4.intime))",
其他语句都正常，但是这个语句，如果连续运行程序，就会出现，outdate字段无效，求高手啊。
...全文
请发表友善的回复…
发表回复
lxpandsq 2013-04-08
- 打赏
- 举报
同样的代码执行前几次都正常，就是执行多了以后，就出问题啦。
大_爱 2013-04-08
- 打赏
- 举报
提示你啦，表中没有字段。你检查一下表的结构吧。
lxpandsq 2013-04-08
- 打赏
- 举报
我也头痛，#开头的是临时表，那个outdte是字段名，不是outdate，现在是执行的时候，才开始没有问题，后面就有问题啦
猫儿爷爷 2013-04-08
- 打赏
- 举报
set outdte =
应该写成set outdate=
你SQL太长了..看的头疼...话说楼主的SQL是什么的写法?
都是#开头的...
lxpandsq 2013-04-08
- 打赏
- 举报
奇怪，没换行，我把这个语句放在sql里面使劲执行，结果没报错，所以估计是我的程序有问题，放代码
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
public class chackSQL extends JFrame implements ActionListener
{
private static final long serialVersionUID = 1L;
static String dir="com.microsoft.jdbc.sqlserver.SQLServerDriver";//数据库连接驱动
static String conurl="jdbc:microsoft:sqlserver://10.10.3.76:1433;DatabaseName =TCDQC";//数据库连接
static String use="sa";//数据库账号
static String pws="23172153";//数据库密码
static String tishi="注意:如果结算日是2月26日到3月25日,必须在3月内运行,否则4月1日以后运行记录就会不准确.";
SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
Calendar rightNow = Calendar.getInstance();
String datenow=df.format(rightNow.getTime())+"25";
//当前年月
String dateN()
{
rightNow.add(Calendar.MONTH,-1);
return df.format(rightNow.getTime())+"26";
}
String sql[][]=
{
{"use hrmscfg select * from hrswewl where wewltyp='0' and wewldte>='"+dateN()+"'and wewldte<'"+datenow+"' order by wewldpl",
"加班及中夜贴未最终确认者."+tishi},
{"use hrmscfg IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp select wewlwno,wewldte,wewljia,wewlftm,wewlttm,count(*) as cou into #temp from hrswewl where wewljia like 'e%' group by wewlwno,wewldte,wewljia,wewlftm,wewlttm select * from #temp where cou>=2 ",
"中夜贴重复者."},
{"use hrmscfg IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp select wtbtwno,wtbtdte,wtbtusr,count(*) as cou into #temp from hrswtbt group by wtbtwno,wtbtdte,wtbtusr order by wtbtdte desc select * from #temp where cou>=2",
"排班重复."},
{"use hrmscfg IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1 select wtiawno,wtiadte,wtiaftm,wtiattm,wtiahus,wtiatyp ,count(*) as cou into #temp1 from hrswtia group by wtiawno,wtiadte,wtiaftm,wtiattm,wtiahus,wtiatyp select * from #temp1 where cou>=2 and wtiadte>='"+dateN()+"'",
"加班时间重复."+tishi},
{"use hrmscfg IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2 select wewlwno,wewldte,wewljia,wewlftm,wewlttm,count(*) as cou into #temp2 from hrswewl where wewljia like 'd%' group by wewlwno,wewldte,wewljia,wewlftm,wewlttm select * from #temp2 where cou>=2",
"发加班费加班时间重复."},
{"use hrmscfg IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1 IF OBJECT_ID('tempdb..#temp2') IS NOT NULL DROP TABLE #temp2 IF OBJECT_ID('tempdb..#temp3') IS NOT NULL DROP TABLE #temp3 IF OBJECT_ID('tempdb..#temp4') IS NOT NULL DROP TABLE #temp4 select replace(wewlftm,':','') as intim,replace (wewlttm,':','') as outtim,(case when wewlftm>wewlttm then replace((convert(char(10),(dateadd(day,1,cast(cast(wewldte as varchar) as datetime))),111)),'/','') end) as outdte, (case when wewlftm<=wewlttm then wewldte end)as indte, wewlwno as wno, wewljia as jia,wewldte into #temp from hrswewl update #temp set outdte = cast(wewldte as char(10)) where outdte is NULL update #temp set indte = cast(wewldte as char(10)) where indte is NULL select (cast(indte as char(8))+cast(intim as char(4))) intime,(cast(outdte as char(8))+cast(outtim as char(4))) outtime,wno as wno,jia as jia into #temp2 from #temp where outdte <>'0' select intime as intime,outtime as outtime,wno as wno,jia as jia into #temp3 from #temp2 where jia like 'A%' or jia like 'B%' or jia like 'C%' or jia like 'K%' select intime as intime,outtime as outtime,wno as wno,jia as jia into #temp4 from #temp2 where jia like 'D%' or jia like 'E%' select * from #temp3,#temp4 where #temp3.wno = #temp4.wno and ((#temp3.intime>#temp4.intime and #temp3.intime< #temp4.outtime) or (#temp3.intime<#temp4.outtime and #temp3.outtime>#temp4.intime))",
"得到请假人员申报加班或津贴，时段重叠者."},
{"use hrmscfg IF OBJECT_ID('tempdb..#gyh') IS NOT NULL DROP TABLE #gyh select wewldte,wewlwno,sum(wewlhus) a into #gyh from hrswewl where wewljia like 'a%' or wewljia like 'c%' or wewljia like 'b%' group by wewldte,wewlwno select * from #gyh where a>8",
"一天内换休时间超过8小时."},
{"use hrmscfg select wewldte from hrswewl where wewltyp=1 and wewldte<='"+datenow+"' group by wewldte",
"假勤未转档纪录,本查询仅给出信息人事自行判断正常与否."}
};
public static java.sql.Connection getConn()
{
java.sql.Connection conn=null;
try {
Class.forName(dir);
conn = DriverManager.getConnection(conurl,use,pws);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
JOptionPane.showMessageDialog(new JFrame(), "数据库连接异常，请联系电脑课！");
} catch (SQLException e) {
JOptionPane.showMessageDialog(new JFrame(), "数据库连接异常，请联系电脑课！");
}
return conn;
}
javax.swing.JLabel jl1=new javax.swing.JLabel("提示信息");
javax.swing.JButton jb1=new javax.swing.JButton("执行");
javax.swing.JTextArea ja1=new javax.swing.JTextArea(10,50);
JScrollPane jf1=new JScrollPane(ja1);
public chackSQL()
{
super();
this.setTitle("异常扫描");
//标题
this.setSize(500,500);
//尺寸
int w = (java.awt.Toolkit.getDefaultToolkit().getScreenSize().width - this.getWidth()) / 2;
//宽
int h = (java.awt.Toolkit.getDefaultToolkit().getScreenSize().height - this.getHeight()) / 2;
//长
this.setLocation(w, h);
//坐标
this.getContentPane().setLayout(null);
//不使用布局管理器
//出现滚动条的文本区域
jl1.setBounds(50, 25, 250, 100);
jb1.setBounds(350, 50, 80, 50);
jf1.setBounds(50,150,400,300);
//绝对位置
jl1.setOpaque(true);
//背景不透明
//jl1.setBackground(java.awt.Color.blue);
//背景颜色
jl1.setBorder(javax.swing.BorderFactory.createLineBorder(java.awt.Color.red));
this.getContentPane().add(jl1);
this.getContentPane().add(jb1);
this.getContentPane().add(jf1);
//布局
jb1.addActionListener(this);
//添加按钮监听事件
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
//关闭按钮
this.setVisible(true);
//窗体显示
}
public void actionPerformed(ActionEvent e)
{
try {
java.sql.Connection conn=null;
conn = getConn();
if(conn==null)
{
JOptionPane.showMessageDialog(new JFrame(), "数据库连接异常，请联系电脑课！");
}
else
{
for(int i=0;i<sql.length;i++)
{
if(JOptionPane.showConfirmDialog(null, "是否继续执行", "信息", JOptionPane.YES_NO_OPTION)==0)
{
java.sql.Statement stmt=null;
stmt = conn.createStatement();
java.sql.ResultSet rs = stmt.executeQuery(sql[i][0]);
//java.util.ArrayList<String> list = new java.util.ArrayList<String>();
int col=rs.getMetaData().getColumnCount();
ja1.setText("");
int z=0;
while(rs.next())
{
for(int k1=1;k1<=col;k1++)
{
ja1.append(rs.getString(k1)+",");
//list.add(rs.getString(k1)+",");
}
ja1.append("\n");
z++;
}
jl1.setText("<html>"+sql[i][1]+"</html>");
if(z==0)
{
ja1.setText("正常");
}
}
else
{
i=sql.length-1;
}
if(i==sql.length-1)
{
JOptionPane.showMessageDialog(new JFrame(), "查询完结退出！");
}
}
if(conn!=null)conn.close();
}
} catch (SQLException e1) {e1.printStackTrace();}
}
public static void main(String[] args)
{
new chackSQL();
}
}
最美的词 2013-04-08
- 打赏
- 举报
这么长的语句
