derby db prepared stmt insert properties

[ceyesuma]

I have been trying to find how to set a property for a derby connection to
have a prepared statement perform several INSERT queries.
it was suggested that this may be possible.

Are you trying to execute multiple statements with a single sql string? If so, have you activated the "allow multiple statements" property?

I have not found anything that would show me how to do this.
Can anyone show some info on how to run many INSERT queries in one prepared statement of an idea on the most efficient means to do so.
thanks

[masijade]

Sorry about that. That was a MySQL Connection URL property "allowMultiQueries".

From what I saw of your thread, you would be better off using Stement and the addBatch(String) method.

[ceyesuma]

Hello
I have not looked at addBatch() or callable proceedures yet I had these done and though not

conventional methods I suppose
It presented the question of why is there only one result when the table has 2 records?
see output code
output

run:
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
in bCreatedTables:
created table: createAdminTable
created table: createPayeeTable
created table: createStudentTable
created table: createInstructorTable
created table: createBookingTable
created table: createInstrAvailableTable
created table: createInstrumentTable
created table: createLocationTable
created table: createPaymentTable
created table: createPayrollTable
created table: createUserTable
created table: createUserGroupTable
created table: createUserGroupMappingTable
driver tableName from xml: org.apache.derby.jdbc.EmbeddedDriver
db and tables created: 
in bInsertedIntoTables:
inserted data into table: INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES 

('ag','Garth','a')
inserted data into table: INSERT INTO admin (admin_uid,admin_fname,admin_password) VALUES 

('aw','Wayne','a')
inserted data int: insertAdminTable
in bInsertedIntoTables:
inserted data into table: INSERT INTO payee (payee_uid,payee_fname,payee_password) VALUES 

('ps','Suzies mom','p') 
inserted data into table: INSERT INTO payee (payee_uid,payee_fname,payee_password) VALUES 

('pj','Johnie dad','p') 
inserted data int: insertPayeeTable
[b]
in bInsertedIntoTables:
inserted data into table: INSERT INTO student (stu_uid,stu_fname,stu_password) VALUES 

('sj','Johnny','s') 
inserted data into table: INSERT INTO student (stu_uid,stu_fname,stu_password) VALUES 

('ss','Suzie','s') 
inserted data int: insertStudentTable
[/b]
in bInsertedIntoTables:
inserted data into table: INSERT INTO instructor (instr_uid,instr_fname,instr_password) 

VALUES ('ij','Johanne','i') 
inserted data into table: INSERT INTO instructor (instr_uid,instr_fname,instr_password) 

VALUES ('im','Mozart','i') 
inserted data int: insertInstructorTable
in bInsertedIntoTables:
inserted data into table: INSERT INTO payment (pymt_method) VALUES ('cash')
inserted data into table:  INSERT INTO payment (pymt_method) VALUES ('credit') 
inserted data int: insertPaymentTable
in bInsertedIntoTables:
inserted data into table: INSERT INTO usergroup_mapping VALUES 

('aw','adm'),('sj','std'),('im','instr'),('ps','pye'),('ag','adm') 
inserted data int: insertUserGroupMappingTable
CONECTION IS GOOD FOR A TEST
test string query: SELECT * FROM student

[b]
Why only one output ?: sj

[/b]

additionally,
will it be possible to write everything differnt later to simply put many statements as a

string in one

<entry key="easier cdata string for a batch()??">
INSERT INTO x VALUES(etc,etc)
INSERT INTO x VALUES(etc,etc)
INSERT INTO x VALUES(etc,etc)
INSERT INTO x VALUES(etc,etc)
</entyr>

as so? or can the way I did it be suitable?
What is more conventional so as to be able to create stmts that can be used globally?
Thanks
class that is passed the string to access the xml.properties and insert initial data
so I can use it for now

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author depot
 */
public class DerbyInsertTest extends ConnectDerbyDAO {

    public int config;

    public DerbyInsertTest() throws FileNotFoundException, IOException, SQLException {
        conn = getConn();
    }

    public boolean bInsertedIntoTables(Connection conn, String tableName) throws 

FileNotFoundException, IOException,
            SQLException {
        this.conn = conn;
        System.out.println("in bInsertedIntoTables:");
        bInsertedIntoTables = false;
        String[] queryArray = constructXMLElementArray(tableName);
        for (int i = 0; i < queryArray.length; i++) {

            PreparedStatement ps = null;

            String query = "";
            String badInsert = "";
            try {
                badInsert = queryArray[i];
                query = queryArray[i];
                if (conn != null) {
                    ps = (PreparedStatement) conn.prepareStatement(query);
                    ps.executeUpdate();
                    bInsertedIntoTables = true;
                    ps.close();
                    System.out.println("inserted data into table: " + query);
                }
            } catch (SQLException ex) {
                System.out.println("Error at : " + tableName);
                String value = dbProperties.getProperty(badInsert);
                System.out.println(value);
                bInsertedIntoTables = false;
                ps.close();
                ex.printStackTrace();
            }
        }
        return bInsertedIntoTables;
    }

    public String[] constructXMLElementArray(String tableName) throws 

FileNotFoundException,
            IOException {
        if (tableName != null) {
            String config = null;
            config = dbProperties.getProperty(tableName + SUFFIX);
            int size = Integer.parseInt(config.trim());
            elementNameArray = new String[size];
            for (int j = 0; j < elementNameArray.length; j++) {
                temp = tableName + "[" + j + "]";
                String load = dbProperties.getProperty(temp);
                elementNameArray[j] = load;
            }
        }
        return elementNameArray;
    }

    public void testOutput(Connection conn) {
        PreparedStatement ps = null;
        String query = "";

        if (conn != null) {
            System.out.println("CONECTION IS GOOD FOR A TEST");
            try {
                query = dbProperties.getProperty("test");
                System.out.println("test string query: " + query);
                ps = (PreparedStatement) conn.prepareStatement(query);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    String temp = rs.getString(1);
                    System.out.println("Why only one output ?: " + temp);
                }
                ps.close();
                System.out.println();
            } catch (SQLException ex) {

                String value = dbProperties.getProperty(query);
                System.out.println(value);
                //ex.printStackTrace();
            }
        }
        if (conn == null) {
            System.out.println("CONNECT NOT GOOD");
        }
    }
}

xml.properties

<entry key="insertAdminTableConfig">2</entry>
  <entry key="insertAdminTable[0]">INSERT INTO admin (admin_uid,admin_fname,admin_password) 

VALUES ('ag','Garth','a')</entry>
  <entry key="insertAdminTable[1]">INSERT INTO admin (admin_uid,admin_fname,admin_password) 

VALUES ('aw','Wayne','a')</entry>
  <entry key="insertBookingTable"> </entry>
  <entry key="insertInstructorTableConfig">2</entry>
  <entry key="insertInstructorTable[0]">INSERT INTO instructor 

(instr_uid,instr_fname,instr_password) VALUES ('ij','Johanne','i') </entry>
  <entry key="insertInstructorTable[1]">INSERT INTO instructor 

(instr_uid,instr_fname,instr_password) VALUES ('im','Mozart','i') </entry>
  <entry key="insertInstrumentTable"> </entry>
  <entry key="insertLocationTable"> </entry>
  <entry key="insertInstrAvailableTable"> </entry>
  <entry key="insertPayeeTableConfig">2</entry>
  <entry key="insertPayeeTable[0]">INSERT INTO payee (payee_uid,payee_fname,payee_password) 

VALUES ('ps','Suzies mom','p') </entry>
  <entry key="insertPayeeTable[1]">INSERT INTO payee (payee_uid,payee_fname,payee_password) 

VALUES ('pj','Johnie dad','p') </entry>
  <entry key="insertPaymentTableConfig">2</entry>
  <entry key="insertPaymentTable[0]">INSERT INTO payment (pymt_method) VALUES 

('cash')</entry>
  <entry key="insertPaymentTable[1]"> INSERT INTO payment (pymt_method) VALUES ('credit') 

</entry>
  <entry key="insertPayrollTable"> </entry>


[b]
  <entry key="insertStudentTableConfig">2</entry>
  <entry key="insertStudentTable[0]">INSERT INTO student (stu_uid,stu_fname,stu_password) 

VALUES ('sj','Johnny','s') </entry>
  <entry key="insertStudentTable[1]">INSERT INTO student (stu_uid,stu_fname,stu_password) 

VALUES ('ss','Suzie','s') </entry>
  [/b]


  <entry key="insertUserTableConfig">1</entry>
  <entry key="insertUserTable[0]">INSERT INTO user 

VALUES('aw','a'),('sj','s'),('im','i'),('ps','p')</entry>
  
  
  <entry key="insertUserGroupTableConfig">1</entry>
  <entry key="insertUserGroupTable[0]">INSERT INTO usergroup 

VALUES('adm','admin'),('std','student'),('instr','instructor'),('pye','payee')</entry>
  
  <entry key="insertUserGroupMappingTableConfig">1</entry>
  <entry key="insertUserGroupMappingTable[0]">INSERT INTO usergroup_mapping VALUES 

('aw','adm'),('sj','std'),('im','instr'),('ps','pye'),('ag','adm') </entry>
  [b]
  <entry key ="test">SELECT * FROM student</entry>
[/b]

[masijade]

To do the addBatch simply do addBatch rather than execute and then include an if statement inside the for loop so that you can for, say, every 500 statements do executeBatch (i.e. if ((i % 500) == 0)). Also, as to why you only see one result, it is because you are only checking for one row. You are doing if(rs.next()) when you should be doing while(rs.next())