想請問我的SQL語句哪裡寫錯了?煩麻各位大大幫我解惑一下

mysql java sql語法錯誤

ztex030256412 2020-03-19 16:30:40 ‧ 2108 瀏覽

分享至

JButton btnNewButton = new JButton("\u8A08\u7B97");
btnNewButton.addActionListener(new ActionListener() {
@SuppressWarnings("unchecked")
public void actionPerformed(ActionEvent arg0) {
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
@SuppressWarnings("rawtypes")
ArrayList arr = new ArrayList();
arr.add(df.format(spinner.getValue()));
arr.add(df.format(spinner_1.getValue()));

			String sql = "SELECT SUM(worktime) FROM workform WHERE ddat BETWEEN ? AND ?";
			
			try(
		            Connection conn = DriverManager.getConnection(url, user, pass);
		            Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
					PreparedStatement ps = conn.prepareStatement(sql);
		            ResultSet rs = stmt.executeQuery(sql)) {
				for(int i = 0;i <= arr.size();i = i + 2) {
					 ps.setString(1,(String) arr.get(i));  
				    ps.setString(2,(String) arr.get(i+1));
				}
				   
		       
		            while(rs.next()){
		            	System.out.println(df.format(spinner.getValue()));

		            	lbtime.setText(rs.getString(1));
		            	int z = 0;
		            	z = Integer.parseInt(rs.getString(1));
		            	int x = 0;
		            	x = z * 3000;
		            	String y  = null;
		            	y = String.valueOf(x);
		            	lbercost.setText(y);
		            	System.out.println("good");
		            	
		            	
		             }
		        
		            
		            
		        } catch (SQLException ex) {
		            System.out.println(ex.getMessage()); 
		        }
			
		}
	});
    
   df.format(spinner.getValue())跟df.format(spinner_1.getValue())取得的資料都是資料庫內的格式YYYY-MM-DD，直接在SQL裡把?寫死寫成固定的日期就能使用，但如果使用變數的話，就會得到這個結果
   You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? AND ?' at line 1
   想請問問題是出在哪裡了
   我還只是一個小小SQL新手還請版上各位大大解惑，謝謝

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2020-03-19 22:34:41

試試看
把

    PreparedStatement ps = conn.prepareStatement(sql);
	ResultSet rs = stmt.executeQuery(sql)) {
	for(int i = 0;i <= arr.size();i = i + 2) {
        ps.setString(1,(String) arr.get(i));  
		ps.setString(2,(String) arr.get(i+1));
    }

改成

    PreparedStatement ps = conn.prepareStatement(sql);
	for(int i = 0;i <= arr.size();i = i + 2) {
        ps.setString(1,(String) arr.get(i));  
		ps.setString(2,(String) arr.get(i+1));
    }
	ResultSet rs = stmt.executeQuery(sql)) {