INSERT SELECT INTO IN JAVA USING PREPARED STATEMENT

ben reitman

Total Post:96

Points:676
Posted by  ben reitman
 540  View(s)
Ratings:
Rate this:
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.
  1. Mayank Tripathi

    Post:397

    Points:3117
    Re: Insert Select Into in java using Prepared statement

    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 ()
    Error was came from giving parameters (setString...) without matching ?

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!