jdbc中mySQL语句单双引号_JDBC: 执行MySQL语句时，bit类型的数值在java代码中应该怎么表示？...

目录

Window’s Note

记录自己在实际操作中用到的问题以及最后的解决方案

前提描述

注册页面用户填写信息后需要执行insert()方法向数据库中插入新用户的信息，需要插入的用户信息变量在数据库中定义如下：

* mail varchar(255)

* pwd varchar(255)

* isManager bit(1)

* isNormal bit(1)

varchar类型直接对应java中String，bit类型对应java中boolean类型

问题

java中执行插入新用户的语句如下String sql = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('" + mail + "','" + pwd + "'," + false + "," + true + ")";

编译运行报错

Unknown column 'isManger' in 'field list'

尝试修改代码如下依旧报错如上String sql = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('" + mail + "','" + pwd + "','0'," + true + ")";

String sql = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('" + mail + "','" + pwd + "',0,1)" ;

String sql = "INSERT INTO household (mail, pwd, isManger, isNormal) VALUES ('" + mail + "','" + pwd + "'," + 0 + "," + 1 + ")";

解决问题思路梳理

报错的原因是因为上述几种方式java中变量sql中的isManager的value并不是真正sql可识别的bit类型

首先需要确定SQL中的布尔类型是不是bit

查阅发现MySQL中并不支持布尔类型, MySQL中tinyint(1)是变相的布尔类型, 默认值1, 0 分别对应true, false

修改数据库中isManager和isNormal的类型为tinyint(1)

执行上述几类代码后依旧报相同的错误

乌龙一场

我把sql中isManager写成了isManger, 大家还是要养成细心看报错的习惯啊

后续(值得小白一看)

把SQL中isManager和isNormal的数值类型修改为tinyint(1)后，sql中赋值’0’, 0和false都可以成功插入数据，且结果一致，具体java代码和数据库中如下：

String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser1','test',0,1)" ;

String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser2','test'," + 0 + "," + 1 + ")";

String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser3','test','0','1')" ;

String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser4','test',false,true)" ;

String sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser5','test'," + false + "," + true + ")";

数据库中结果如下

id

mail

pwd

isManager

isNormal

1

testUser1

test

0

1

2

testUser2

test

0

1

3

testUser3

test

0

1

4

testUser4

test

0

1

5

testUser5

test

0

1

注意执行以下代码会报错，因为传入的类型变为了varcharString sql0 = "INSERT INTO household (mail,pwd,isManager,isNormal) VALUES ('testUser4','test','false','true')" ;