Insert Select Into in java using Prepared statement

I am doing INSERT INTO SELECT which are inserting into in 1 table by selecting specific data in columns from 2 tables. But, it will involve with user input from JTextField as well. I have searched for many solutions but still got an error and I just dunno what else to do. I'm using Java as PL and Oracle as DB. This is what I have got so far :

Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","ghost","slayer");
stmt = con.createStatement();
String sbjC = sbjCode.getText(); //textfield for subjectCode
String sbjN = sbjName.getText(); //textfield for subjectName
String matricsno = textstudentid.getText(); //textfield for matrics number
String sbjG = sbjGrade.getText(); //textfield for subjectGrade (not gonna be use in db, just for comparison)
String sql1 = "INSERT INTO transferred (subjectCode,subjectName,credit,prequisite,matricsNo) "
    + "SELECT b.subjectCode,b.subjectName,b.credit,b.prequisite,s.matricsNo "
    + "FROM bitm b, student s "
    + "WHERE b.subjectCode = '"+sbjC+"' AND b.subjectName = '"+sbjN+"' AND s.matricsNo = '"+matricsno+"'";
/* table Transferred has 5 column which are subjectCode,subjectName,credit,prequisite,matricsNo [matricsno as FK]
 * table bitm has 5 column [subjectCode as PK]
 * table student has 6 column [matricsno as PK]
 */
ps = con.prepareStatement(sql1);
ps.setString(1, sbjC);
ps.setString(2, sbjN);
ps.setString(3, "SELECT credit FROM bitm WHERE subjectCode = '"+sbjC+"' AND subjectName = '"+sbjN+"'");
ps.setString(4, "SELECT prequisite FROM bitm WHERE subjectCode = '"+sbjC+"' AND subjectName = '"+sbjN+"'");
ps.setString(5, "SELECT matricsno FROM student WHERE matricsno = '"+matricsno+"'");
ps.executeUpdate(sql1);

The only error I have got after executing and insert all data needed into JTextField is java.sql.SQLException : Invalid column index.

The SQL statement has been test in SQL Developer and succeed. Just I'm bit confused on how to do it on Java. Thank you for all of your response and time. I'm a newbie in Java.

String sql1 = "INSERT INTO transferred (subjectCode,subjectName,credit,prequisite,matricsNo) "
+ "SELECT b.subjectCode,b.subjectName,b.credit,b.prequisite,s.matricsNo "
+ "FROM bitm b, student s "
+ "WHERE b.subjectCode = ? AND b.subjectName = ? AND s.matricsNo = ? ";

ps = con.prepareStatement(sql1);
ps.setString(1, sbjC);
ps.setString(2, sbjN);
ps.setString(3,matricsno);
ps.executeUpdate ()

forum

Insert Select Into in java using Prepared statement

Anonymous User

1 Answers

Liked By