mysql 单引号插入数据训_关于数据库插入单引号

今天遇到一个前台接受一个带单引号的数据 eg：'欧拉欧拉   插入数据库会报以下错误

org.springframework.dao.DataIntegrityViolationException:

### Error querying database. Cause: java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ''' not found.

### The error may exist in com/cn/persistence/KnowQuestionMapper.xml

### The error may involve com.cn.persistence.KnowQuestionMapper.selectKnowQuestionList

### The error occurred while executing a query

### Cause: java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ''' not found.

; SQL []; Invalid SQL statement or JDBC escape, terminating ''' not found.; nested exception is java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ''' not found.

解决方案 ：把单引号替换再加一个单引号转义

要插入的字符串，将其中的(’)替换成两个单引号(”)即可 。

public static void main(String[] args) throws Exception{

KnowQuestion ss=new KnowQuestion();

String str="I'm Iron Man" .replaceAll(" ' ", " '' ");;

String sql="insert into  knowQuestion VALUES(' "+str+" ');";

ss.operate(sql, 2);

System.out.println(str);

System.out.println(sql);

}

eg：'欧拉欧拉 替换 ''欧拉欧拉

tip：注意替换时，string是final修饰的，是不可变的

"I'm Iron Man" .replaceAll(" ' ", " '' ");不可这样写，一定要把此新对象的引用再重新指向str，否则不会改变

String str="I'm Iron Man" .replaceAll(" ' ", " '' ");