Sending multiple rows to mysql database

Question

So I want to send a user to a mysql database with the columns: user, skill, time Now, I want it to be able to add more then one row for the same user but having different time and etc, how would I do this? Here's my code for sending it to the database:

   public static boolean recentActivity(Player paramPlayer){
    try {
        Statement statement = con.createStatement();
        ResultSet group = statement.executeQuery("SELECT * FROM recentactivity WHERE user = '"+ paramPlayer.getDisplayName() + "'");
        if (!group.next() && !paramPlayer.levelTime.equals(""))
                statement.execute("INSERT INTO `recentactivity`(`user`, `skill`, `time`) VALUES('"+ paramPlayer.getDisplayName() +"', '"+ paramPlayer.levelledSkill +"', '"+ paramPlayer.levelTime +"')");
    } catch (Exception localException) {
        return false;
    }
    return true;

} Then I have this to use it:

    java.util.Date dt = new java.util.Date();
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
    String currentTime = sdf.format(dt);
    player.levelledSkill = skill;
    player.levelTime = ""+currentTime+"";
    Hiscores.recentActivity(player);

Any help with this?

Schema:

CREATE TABLE IF NOT EXISTS recentactivity ( 
    user varchar(255) NOT NULL
    , skill int(11) NOT NULL
    , time varchar(255) NOT NULL
    , PRIMARY KEY (user) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

There is a lot of Java for what sounds like a MySQL query question. Could you shorten/clarify this a bit. I am not sure what you are asking. You can just put more rows in. What is stopping you? What is the structure of your SQL table? — thatidiotguy
– thatidiotguy, Commented Aug 27, 2012 at 22:16
What exactly is your problem with the way you're doing it now? — Nate C-K
– Nate C-K, Commented Aug 27, 2012 at 22:17
It only adds one row for the player. CREATE TABLE IF NOT EXISTS recentactivity ( user varchar(255) NOT NULL, skill int(11) NOT NULL, time varchar(255) NOT NULL, PRIMARY KEY (user) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; — Brandon Tyler Jones
– Brandon Tyler Jones, Commented Aug 27, 2012 at 22:18

FrankieTheKneeMan · Accepted Answer · 2012-08-27 22:21:42Z

From the documentation:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
        col_name=expr
            [, col_name=expr] ... ]

So, ignoring the things you're not using...

INSERT INTO tbl_name (col_name,...)
    VALUES | VALUE (expr,...),(...),...

By just adding a comma and a new set of parentheses, you can add additional records ad nauseum. For example:

"INSERT INTO `recentactivity`(`user`, `skill`, `time`) 
    VALUES('"+ paramPlayer.getDisplayName() +"', '"+ paramPlayer.levelledSkill +"', '"+ paramPlayer.levelTime +"')" 
    + ",('"+ paramPlayer2.getDisplayName() +"', '"+ paramPlayer2.levelledSkill +"', '"+ paramPlayer2.levelTime +"')"

Or you could iterate over a loop with a StringBuilder, or any number of things.

Simone Gianni · Accepted Answer · 2012-08-27 22:23:31Z

IIUC you want to add more than one row for the same user.

However, you have two places in you code that are preventing you from doing this.

The first piece is in java :

if (!group.next()

It searches if there is already a row for that user, and executes the INSERT only if there is none.

However, that code is there to prevent an SQL exception to occur. In your SQL schema, user is the primary key of the table, so you cannot insert two rows with the same user.

You should change these two places (remove the primary key, remove the !group.next), but first check if the rest of the application will still work with multiple rows pertaining to the same user, cause it seems like it has been designed to prevent that situation more than to allow it.

Moe · Accepted Answer · 2012-08-27 22:37:39Z

I think you can do that if you turn off the AutoCommit .

Auto-commit mode means that when a statement is completed, the method commit is called on that statement automatically. Auto-commit in effect makes every SQL statement a transaction. The commit occurs when the statement completes or the next statement is executed, whichever comes first. In the case of a statement returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed explicitly.

So after you create an instance of ResultSet write this :

group.AutoCommit(False);

write your SQL Inserts Statements after AutoCommit

after you done , and want to put them in database as One block call

group.commit();

Example about using AutoCommit

Collectives™ on Stack Overflow

Sending multiple rows to mysql database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related