Sending HashMap to SQL DB

Question

My code is wrong somehow :/

    public static Connection con = null;
    public static void createConnection()
   {
     try
     {
       Class.forName("com.mysql.jdbc.Driver").newInstance();
       String str1 = "s1.infinitysrv.com:3306";
       String str2 = "sagesca1_hiscores";
       String str3 = "sagesca1_server";
       String str4 = "pass";
       con = DriverManager.getConnection("jdbc:mysql://" + str1 + "/" + str2, str3, str4);
       stmt = con.createStatement();
       System.out.println("Connection to SQL database successful!");
     } catch (Exception localException) {
       System.out.println("Connection to SQL database failed");
       localException.printStackTrace();
     }
   }
       public static Map<String, Clan> clans;
   public static boolean sendClans(){
    try {       
        try {
            clans = (HashMap<String, Clan>) XStreamUtil.getXStream().fromXML(new FileInputStream("data/clans.xml"));
        } catch (Exception e) {
            clans = new HashMap<String, Clan>();
        }
        for (Map.Entry<String, Clan> entries : clans.entrySet()) {
            final Clan clan = entries.getValue();
            clan.setTransient();
            PreparedStatement ps = con.prepareStatement("INSERT INTO clans(name,roomOwner) values (?, ?)");
            ps.setString(1, clan.getName());
            ps.setString(2, clan.getOwner());
            ps.executeUpdate();
        }
    } catch (SQLException e) {
        //e.printStackTrace();
        return false;
    }
    return true;
   }

Here's my ClanManager that calls the method sendClan

    private Map<String, Clan> clans;

@SuppressWarnings("unchecked")
public ClanManager() {
    Logger.getInstance().info("Loading clans....");
    try {
        clans = (HashMap<String, Clan>) XStreamUtil.getXStream().fromXML(new FileInputStream("data/clans.xml"));
    } catch (Exception e) {
        clans = new HashMap<String, Clan>();
    }
    for (Map.Entry<String, Clan> entries : clans.entrySet()) {
        final Clan clan = entries.getValue();
        clan.setTransient();
        //Hiscores.sendClans();
    }
    Logger.getInstance().info("Loaded " +clans.size()+ " SageScape clans.");
}

I'm not quite sure what the problem is but the error points to this when I run my server:

PreparedStatement ps = con.prepareStatement("INSERT INTO clans(name,roomOwner) values (?, ?)");

I don't know what's wrong with the code, if anyone could help me I would love you forever.

What is the mapping between the two columns that you've created and the Map that you have? (i.e. what does owner and name map to with respect to the clans Map that you have) — Sujay
– Sujay, Commented Aug 31, 2012 at 22:08
owner is the roomOwner and name is the roomName how would I make the table on the mysql db also, because I think it might be different somehow. — Brandon Tyler Jones
– Brandon Tyler Jones, Commented Aug 31, 2012 at 22:11
And so your Clan class is populated with this info? And you've appropriate getter/setter methods to access these values? — Sujay
– Sujay, Commented Aug 31, 2012 at 22:12
This is my Clan class I believe it sets those values. pastebin.com/TBKw2kN4 — Brandon Tyler Jones
– Brandon Tyler Jones, Commented Aug 31, 2012 at 22:17

Sujay · Accepted Answer · 2012-08-31 22:25:02Z

2

I am assuming that you've created a table in your DB that has a schema which is somewhat like this (this is a bit MySQL specific):

CREATE TABLE ClanTable(
    name        varchar(30),
    roomOwner   varchar(30)
)

Now for you to insert data into this table using JDBC, I would recommend creating a method for inserting data in your database using a PreparedStatement. If you're new to JDBC, obviously you would want to read through "JDBC Basics" to understand how to use JDBC in the first place.

Assuming a table structure as above, the PreparedStatement for this would be something like this:

PreparedStatement ps = connection.prepareStatement("INSERT INTO ClanTable(name,roomOwner) values (?, ?)");

You would then iterate through your map and execute this statement. Note that if your map has a less number of entries, it would be okay to execute something like this:

for(Map.Entry<String, Clan> entries: clans.entrySet()){
    Clan clan = entries.getValue();
    ps.setString(1, clan.getName());
    ps.setString(1, clan.getOwner());

    ps.executeUpdate();
}

answered Aug 31, 2012 at 22:25

Sujay

6,7812 gold badges32 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Brandon Tyler Jones Over a year ago

Would I keep the values as ?,?

Sujay Over a year ago

@BrandonTylerJones A little bit of reading on PreparedStatement might help you understand the significance of '?' over here

Brandon Tyler Jones Over a year ago

NVm - forgot to put it in a try and catch.

Brandon Tyler Jones Over a year ago

There is something wrong with my code my server wouldn't start back up with this: pastebin.com/8bKEfx3W

mtyson Over a year ago

Bleh, I hate seeing someone go to the effort of a good answer and then have it not accepted.

|

Collectives™ on Stack Overflow

Sending HashMap to SQL DB

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related