在mysql查询中传递java字符串变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24644882/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
passing java string variable in mysql query
提问by enjal
How to pass java string variable in sql query .I have done all the JDBC connection .
如何在sql查询中传递java字符串变量。我已经完成了所有的JDBC连接。
My sql database query is
我的 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";
It is not working. However if i do the following code its working
它不工作。但是，如果我执行以下代码，则其工作正常
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";
Now tell me how should i pass variable name to the sql query to execute this. Jst tell me how to pass the variable locationnames to comp.name.
现在告诉我应该如何将变量名传递给 sql 查询来执行它。请告诉我如何将变量 locationnames 传递给 comp.name。
My complete java function looks like this: locationCombo denotes item selected in combobox. CropCombo also denotes the same...
我完整的 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());
}
}
采纳答案by Elliott Frisch
Use a PreparedStatementand bind the Stringparameter,
使用 aPreparedStatement并绑定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) {
}
}
}
Edit(Based on your additional code, change it to something like)
编辑（根据您的附加代码，将其更改为类似）
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();
回答by Bishan
String locationnames = "taplejung";
String 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";
回答by user2108599
Whenever I have to make sql queries I use a library like jdbi to do it. This will allow you to create an interface with different queries. All you have to do is define the interface, create a POJO, and create a mapper between a SQL table and a Java POJO.
每当我必须进行 sql 查询时，我都会使用 jdbi 之类的库来执行此操作。这将允许您创建具有不同查询的界面。您所要做的就是定义接口，创建 POJO，并在 SQL 表和 Java POJO 之间创建映射器。
The interface would look something like this.
界面看起来像这样。
@RegisterMapper(ProductionMapper.class)
public interface ProductionDAO {
@SqlQuery("Select * from production AS cust INNER JOIN location AS comp ON cust.location_id = comp.location_id where comp.name = :name AND crop_id =1")
Production findRow(@Bind("name") String name);
}
The POJO would look something like this.
POJO 看起来像这样。
public class Production {
private VariableTypeA variableA;
// other variables
public Production(VariableTypeA variableA ....) {
this.variableA = variableA;
// set everything else
}
// getters and setters
}
The mapper would look something like this.
映射器看起来像这样。
public class ProductionMapper implements ResultSetMapper<Production> {
public Production map(int index, ResultSet r, StatementContext ctx) throws SQLException {
return new Production(r.getSomeType("columnName"), ...);
}
}
This design makes it really simple to interact with your database and pass variables as well as making it so that your classes dont violate the SRP
这种设计使得与数据库交互和传递变量变得非常简单，并且使您的类不会违反 SRP
回答by suneelpervaiz
Passing variable is quiet simple in mysql query using java.
在使用 java 的 mysql 查询中传递变量非常简单。
- Write your query
- and write the variable in ""
- In my case i am passing 'conition' and 'tablename' dynamically.
Thank you very much have a good day.
@Override public LinkedList getNameList(String condition, String tableName, String projectName) { // TODO Auto-generated method stub
String query = "select distinct("+condition+") as name from "+tableName+" "; //System.out.println(query); ResultSet rs = null; PreparedStatement preparedStatement = null; Connection connection = null; LinkedList finalList = new LinkedList(); try{ connection = dataSourceAbacus.getConnection(); preparedStatement = connection.prepareStatement(query); rs= preparedStatement.executeQuery(); while(rs.next()){ finalList.add(rs.getString("name")); } }catch(Exception e){ e.printStackTrace(); }finally{ if(connection !=null){ try { connection.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(preparedStatement != null){ try{ preparedStatement.close(); }catch(Exception e){ e.printStackTrace(); } } if(rs != null){ try{ rs.close(); }catch(Exception e){ e.printStackTrace(); } } } return finalList;}
- 写下您的查询
- 并将变量写入“”
- 就我而言，我正在动态传递“conition”和“tablename”。
非常感谢你有美好的一天。
@Override public LinkedList getNameList(String condition, String tableName, String projectName) { // TODO 自动生成的方法存根
String query = "select distinct("+condition+") as name from "+tableName+" "; //System.out.println(query); ResultSet rs = null; PreparedStatement preparedStatement = null; Connection connection = null; LinkedList finalList = new LinkedList(); try{ connection = dataSourceAbacus.getConnection(); preparedStatement = connection.prepareStatement(query); rs= preparedStatement.executeQuery(); while(rs.next()){ finalList.add(rs.getString("name")); } }catch(Exception e){ e.printStackTrace(); }finally{ if(connection !=null){ try { connection.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(preparedStatement != null){ try{ preparedStatement.close(); }catch(Exception e){ e.printStackTrace(); } } if(rs != null){ try{ rs.close(); }catch(Exception e){ e.printStackTrace(); } } } return finalList;}
