Home > DeveloperSection > Forums > Insert Select Into in java using Prepared statement
ben reitman
ben reitman

Total Post:96

Points:676
Posted on    December-24-2015 2:44 AM

 Java Java  SQL  Database Connection 
Ratings:


 1 Reply(s)
 249  View(s)
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.


Mayank Tripathi
Mayank Tripathi

Total Post:397

Points:3117
Posted on    December-24-2015 3:04 AM

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 ?

Don't want to miss updates? Please click the below button!

Follow MindStick