Oracle query inside java

Question

String sql = "INSERT INTO Student_Info(name,roll_no,address,phone_no) VALUES('101', 1, 'Fatma', '25')";
String sql = "insert into Student_Info(name,roll_no,address,phone_no) VALUES("+student.getName()+","+student.getRoll_no()+","+student.getAddress()+","+student.getPhone_no()+")";

the last query shows an error:

java.sql.SQLException: ORA-00917: missing comma

at

statement.executeUpdate(sql);

Can anyone rule out where am I missing the comma?

• paulsm4: You're not missing a comma. You forgot to quote your variables (student.getName() etc.)
• Ken Keenan: You should use a PreparedStatement instead of concatenating strings; see docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Answer 1

You miss the single quotes around student.name, student.address and student.phone_no

String sql = "insert into Student_Info(name,roll_no,address,phone_no) VALUES('"+
 student.getName()+"',"+
 student.getRoll_no()+",'"+
 student.getAddress()+"','"+
 student.getPhone_no()+"')";

Do notice that this sql statement is vulnerable for sql injection attacks. Use a PreparedStatement.

 String sql = "insert into Student_Info(name,roll_no,address,phone_no) " +
 "VALUES(?,?,?,?)"; 
 addStudent = con.prepareStatement(sql);
 addStudent.setString(1, student.getName());
 addStudent.setInt(2, student.getRoll_no());
 addStudent.setString(3, student.getAddress());
 addStudent.setString(4, student.getPhone_no());
 addStudent.executeUpdate();
 con.commit();

Answer 2

Do it in this way:

String sql = "insert into Student_Info(name, roll_no, address, phone_no) 
 VALUES(?, ?, ?, ?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, value); // indexing starts from 1 (not from zero)
...
ps.executeUpdate();
// commit if you have set auto-commit to false

Never use raw statements but PreparedStatements¹. Raw statements have lower performance, are more vulnerable (SQL Injection attacks) and what is most important is readability of code that is on very low level (especially in case if you have more columns).

¹PreparedStatements are much more safer, pre-compiled, have better performance and are user-friedly readable and more...

Answer 3

rene's answer is correct. I would like to add, however:

It is much better practice to use Prepared Statements

Your code would look something like:

String sql = "INSERT INTO Student_Info(?,?,?,?) VALUES(?,?,?,?)"
PreparedStatement sql_prepared = connection_object.prepareStatement(sql)