在mysql查询中传递java字符串变量
如何在sql查询中传递java字符串变量。我已经完成了所有JDBC连接。
我的SQL数据库查询是
sql = "Select * from production AS cust
INNER JOIN location AS comp
ON cust.location_id = comp.location_id
where comp.name = locationnames AND crop_id =1";
它不起作用。但是,如果我执行以下代码,其工作原理
sql = "Select * from production AS cust
INNER JOIN location AS comp
ON cust.location_id = comp.location_id
where comp.name = "\taplejung"\
AND crop_id =1";
现在告诉我如何将变量名传递给sql查询以执行此操作。Jst告诉我如何将变量locationnames传递给comp.name。
我完整的Java函数如下所示:locationCombo表示在组合框中选择的项目。CropCombo也表示相同的…
public void displayYearwise() throws SQLException, ClassNotFoundException{ //jComboBox4.setSelectedItem("Crops");
//DefaultCategoryDataset dataset = new DefaultCategoryDataset();
XYSeriesCollection dataset = new XYSeriesCollection();
XYSeries series = new XYSeries("production");
XYSeries series1 = new XYSeries("scat");
String JDBC_DRIVER="com.mysql.jdbc.Driver";
String DB_URL="jdbc:mysql://localhost/data2";
Connection conn;
Statement stmt;
String USER = "root";
String PASS = "";
Object cropname = CropCombo.getSelectedItem();
String cropnames = cropname.toString();
Object locationname = locationCombo.getSelectedItem();
// String locationnames = locationname.toString();
String locationnames = "taplejung";
String pd="paddy ";
System.out.println(cropnames.length()+" "+pd.length());
System.out.println(cropsList);
String sql=null;
if(cropnames.equals("paddy"))
{
//System.out.println();
sql="Select *
from production AS cust
INNER JOIN location AS comp
ON cust.location_id = comp.location_id
WHERE comp.name = "+locationnames+"
AND crop_id =1";
}
else{
sql="SELECT *
FROM `production`
WHERE crop_id = 4
AND location_id = 10";
}
try{
Class.forName(JDBC_DRIVER);
conn=DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Creating statement...");
stmt = conn.createStatement();
System.out.println(sql);
ResultSet rs=stmt.executeQuery(sql);
while (rs.next()){
//String student = rs.getString("studentname");
String yeartext = rs.getString("year_of_production");
//double value = Double.parseDouble(text);
String productiontext = rs.getString("production_amount");
Double yield = rs.getDouble("yield_amount");
double production = Double.parseDouble(productiontext);
double year = Double.parseDouble(yeartext);
series.add(year,production) ;
series1.add(year,yield) ;
//dataset.addSeries(series);
}
dataset.addSeries(series);
dataset.addSeries(series1);
chartArea.removeAll();
JFreeChart chart = ChartFactory.createScatterPlot("Scatter Plot","Year","Paddy Production", dataset);
// JFreeChart chart = ChartFactory.createScatterPlot("Scatter Plot","Year","Paddy Production", dataset, PlotOrientation.HORIZONTAL, rootPaneCheckingEnabled, rootPaneCheckingEnabled, rootPaneCheckingEnabled);
// CategoryPlot p = chart.getCategoryPlot();
//XYPlot xyplot = (XYPlot)jfreechart.getPlot();
//http://stackoverflow.com/questions/12417732/jfreechart-with-scroller
ChartPanel chartPanel = new ChartPanel(chart, false);
chartArea.setLayout(new BorderLayout());
chartArea.add(chartPanel, BorderLayout.EAST);
chartArea.add(chartPanel);
SwingUtilities.updateComponentTreeUI(this);
// p.setRangeGridlinePaint(blue);
chartArea.updateUI();
System.out.println("Database created successfully...");
}
catch(SQLException se)
{
//Handle errors for JDBC
System.out.println("Connect failed ! ");
se.printStackTrace();
// JOptionPane.showMessageDialog(MajorUI.this, err.getMessage());
}
}
回答:
使用PreparedStatement
并绑定String
参数,
final String sql = "select * from production AS cust INNER JOIN location" + " AS comp ON cust.location_id = comp.location_id where "
+ "comp.name = ? AND crop_id = 1";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
ps.setString(1, "taplejung");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (Exception ignored) {
}
}
}
(根据您的其他代码,将其更改为类似内容)
PreparedStatement ps = null;String sql = null;
if (cropnames.equals("paddy")) {
// System.out.println();
sql = "SELECT * FROM `production` AS cust INNER JOIN location AS comp "
+ "ON cust.location_id = comp.location_id WHERE comp.name = "
+ "? AND crop_id = 1";
} else {
sql = "SELECT * FROM `production` WHERE crop_id = 4 AND location_id = 10";
}
ps = conn.prepareStatement(sql);
if (cropnames.equals("paddy")) {
ps.setString(1, locationnames);
}
System.out.println(sql);
ResultSet rs = ps.executeQuery();
以上是 在mysql查询中传递java字符串变量 的全部内容, 来源链接: utcz.com/qa/419803.html