CODEWITHSUNDEEP
javamysqlrows
Brandon Tyler Jones
Brandon Tyler Jones

Reputation: 49

Sending multiple rows to mysql database

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;

Upvotes: 0

Views: 610

Answers (3)

Moe
Moe

Reputation: 21

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

Upvotes: 0

Simone Gianni
Simone Gianni

Reputation: 11662

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.

Upvotes: 0

FrankieTheKneeMan
FrankieTheKneeMan

Reputation: 6800

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.

Upvotes: 1

Related Questions