Question

Java里用INSERT INTO VALUES（）向mysql插入数据，VALUES里面的值是变量怎么办

location为sql里面的一个表，有两个字段longitude（经度）和latitude（纬度）。
由于经纬度要不断更新，所以想用变量来定义经度和纬度。想先测试一下VALUES里面怎么写变量，随便定义了两个值10和11
截取程序如下：
String mylongitude="10";
String mylatitude ="11";
String sql = "INSERT INTO location(longitude, latitude)"
+ " VALUES (mylongitude,mylatitude)";

运行显示：
插入数据失败 Unknown column 'mylongitude' in 'field list' 就是说VALUES (a,b)不能这么写，那应该怎样写，求助大牛！！~~谢谢~~~

Answer 1

VALUES 语句
具体语法：VALUES row_constructor_list[ORDER BY column_designator][LIMIT BY number] row_constructor_list: ROW(value_list)[, ROW(value_list)][, ...]value_list: value[, value][, ...]column_designator: column_index
VALUES 语句，用做功能展示或者快速造数据场景，结果列名字以 COLUMN_0 开头，以此类推，举个简单例子。单条 VALUES 语句mysql-(ytt/3305)->values row(1,2,3);+----------+----------+----------+| column_0 | column_1 | column_2 |+----------+----------+----------+| 1| 2| 3|+----------+----------+----------+1 row inset(0.00 sec)
多条 VALUES 语句mysql-(ytt/3305)->values row(1,2,3),row(10,9,8);+----------+----------+----------+| column_0 | column_1 | column_2 |+----------+----------+----------+| 1 | 2 | 3 || 10 | 9 | 8 |+----------+----------+----------+2 rows in set (0.00 sec)
多条 VALUES 联合 UNION ALLmysql-(ytt/3305)->values row(1,2,3),row(10,9,8) union all values \ row(-1,-2,0),row(10,29,30),row(100,20,-9);+----------+----------+----------+| column_0 | column_1 | column_2 |+----------+----------+----------+| 1 | 2 | 3 || 10 | 9 | 8 || -1 | -2 | 0 || 10 | 29 | 30 || 100 | 20 | -9 |+----------+----------+----------+5 rows in set (0.00 sec)
根据字段下标排序，从 1 开始mysql-(ytt/3305)->values row(1,2,3),row(10,9,8) union all values \ row(-1,-2,0),row(10,29,30),row(100,20,-9) order by 1 desc ;+----------+----------+----------+| column_0 | column_1 | column_2 |+----------+----------+----------+| 100 | 20 | -9 || 10 | 9 | 8 || 10 | 29 | 30 || 1 | 2 | 3 || -1 | -2 | 0 |+----------+----------+----------+5 rows in set (0.00 sec)
类型可以任意组合：bit,json,datetime,int,decimal 等mysql-(ytt/3305)->values row(100,200,300),\ row('2020-03-10 12:14:15','mysql','test'), \ row(16.22,TRUE,b'1'), \ row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble'));+---------------------+----------------------------+--------------------+| column_0 | column_1 | column_2 |+---------------------+----------------------------+--------------------+| 100 | 200 | 0x333030 || 2020-03-10 12:14:15 | mysql | 0x74657374 || 16.22 | 1 | 0x01 || c86fd1a7 | {"name":"lucy","age":"28"} | 0x3634363236433635 |+---------------------+----------------------------+--------------------+4 rows in set (0.00 sec)
新建表 t3，把刚才这些记录写进去mysql-(ytt/3305)->create table t3 (r1 varchar(100),r2 varchar(100),r3 varchar(100));Query OK, 0 rows affected (0.02 sec)
写入到表 t3mysql-(ytt/3305)->insert into t3 values row(100,200,300), \ row('2020-03-10 12:14:15','mysql','test'), \ row(16.22,TRUE,b'1'),\ row(left(uuid(),8),'{"name":"lucy","age":"28"}',hex('dble'));Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0

Answer 2

不要直接写到字符串里面，要改成拼接的String sql = "INSERT INTO location(longitude, latitude)"
+ " VALUES (“+mylongitude+","+mylatitude+")";如果字段是字符类型还要加上单引号

Answer 3

可以这样写
Connection conn=null;
PreparedStatement ps=null; //创建PreparedStatement 对象
String sql= String Sql = “ INSERT INTO 表名 VALUES（？，？，？，？，…………）”;
conn=DBConnUtil.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, user.getName());
ps.setString(2, user.getPwd());
int i=ps.executeUpdate();
if(i==0){
flag=false;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBConnUtil.closeAll(null, ps, conn);
}

Answer 4

"insert into location(longitude,latitude) values('mylongitude','mylatitude')",如果你的mylongitude是java变量应该"insert into location(longitude,latitude) values('"+mylongitude+"','"+mylatitude+"')"

Answer 5

String sql = "INSERT INTO location(longitude, latitude)"
+ " VALUES (“+mylongitude+","+mylatitude+")";