Using multiple INSERT statements for mutiple tables

Question

So does mySQL do all the statements in its connection until it is closed? Or can it take other statement from other users while that connection is open?

Using multiple INSERT statements to update multiple tables, could the code to do this have a problem when a different user trys to connect to the mySQL server and almost interrupt or foil the code? (between each PreparedStatement)

Like one user also is created an entity(table), and for some reason the 2nd PreparedStatement is slow getting to the server. Will a faster but newer PreparedStatement from a different user foil the slower older connection?

        String entity_stmt = "INSERT INTO entity (type, firstName, lastName, phone, email) "
                    + "VALUES ('residential','" + firstName + "','" + lastName + "','"+ phone + "','" + email + "');";

    try {

        int gen_id_entity;
        int gen_address_id;

        Connection con = Database.getConnection();

        PreparedStatement entity_ps = con.prepareStatement(entity_stmt, Statement.RETURN_GENERATED_KEYS);
        ResultSet entity_rs;

        entity_ps.executeUpdate();
        entity_rs = entity_ps.getGeneratedKeys();
        entity_rs.next();

        gen_id_entity = entity_rs.getInt(1);

        entity_rs.close();
        entity_ps.close();



        String address_stmt = "INSERT INTO address "
                + "(fk_address_entityId, street, town, state, zip) "
                + "VALUES ('"+ gen_id_entity +"', '" + street + "', '" + town + "', '" + state + "', '" + zip + "');";


        PreparedStatement address_ps = con.prepareStatement(address_stmt, Statement.RETURN_GENERATED_KEYS);
        ResultSet address_rs;

        address_ps.executeUpdate();
        address_rs = address_ps.getGeneratedKeys();
        address_rs.next();

        gen_address_id = address_rs.getInt(1);

        entity_rs.close();
        address_ps.close();



        String billingAddress_stmt = "INSERT INTO billingAddress "
                + "(billingName, billingPhone, billingEmail, billingStreet, billingTown, billingState, billingZip, fk_billingAddress_addressId) "
                + "VALUES ('" + billingName + " ', '" + billingPhone + "', '" + billingEmail + "', '" + 
                billingStreet + "', '" + billingTown + "', '" + billingState + "', '" + billingZip + "', '" + gen_address_id +"');";

        PreparedStatement billingAddress_ps = con.prepareStatement(billingAddress_stmt);

        billingAddress_ps.executeUpdate();
        billingAddress_ps.close();


        con.close();

It's interesting you use a `preparedStatement` but do not [parameterize queries](https://stackoverflow.com/questions/3451269/parameterized-oracle-sql-query-in-java)! — Parfait, Jul 15 '17 at 00:00
@parfait yeah I need to do that. I didn't think of someone injected their own sql code! — , Jul 15 '17 at 00:10
Relational databases are designed to scale so can manage many connection points. And `insert` of one row value should be milliseconds. DBs usually only lock the row being edited. Slow downs might be another issue. You do not describe your architecture or application. As such this is a very broad question. — Parfait, Jul 15 '17 at 00:30
@Parfait like if 2 people are adding an entity to database 0.001 seconds apart. Could the 2nd persons statements interfere with the first? — , Jul 15 '17 at 01:12

Using multiple INSERT statements for mutiple tables

0 Answers0