CODEWITHSUNDEEP
javamysqljdbc
Blanky
Blanky

Reputation: 251

Not actually inserting into MySQL but autoincrement field is increasing?

I really don't know what's going on. I'm new to Java, fairly new to MySQL.

I am trying to insert rows into a MySQL database in Java. It's not throwing any exceptions, and when I print out a SELECT * it's showing me the rows I updated. But those rows aren't sticking when I run the program again or in Workbench.

One of the columns (the pk) is an auto incremented field. This field is being incremented, but the data isn't there.

When I add data though Workbench, it sticks and I can retrieve it in my Java program when I do a SELECT *. It's only when I add it through the code below, that the data seems to disappear.

Here is the code. I followed a tutorial:

import java.sql.*;

public class SaveMap {  
public static boolean MapSave(String mapName) {
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    String url = "jdbc:mysql://127.0.0.1:3306/";
    String db = "game_Maps";
    String driver = "com.mysql.jdbc.Driver";
    String user = "root";
    String pass = "pass";

    try {
        Class.forName(driver);
        con = DriverManager.getConnection(url + db, user, pass);
        con.setAutoCommit(false);

        st = con.createStatement();

        System.out.println(st.executeUpdate("INSERT INTO Maps (mapName, mapSize) VALUES('DB Test', 5)"));
        st.executeBatch();



        String sql = "SELECT * FROM Maps";
        rs = st.executeQuery(sql);

        System.out.println("No \t Name");
        while(rs.next()){
            System.out.println(rs.getString(1) + " \t");
            System.out.println(rs.getString(2) + " \t");
            System.out.println(rs.getString(3) + " \t");
        }

        rs.close();
        st.close();
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

    return true;
}
}

Upvotes: 0

Views: 869

Answers (3)

SongGuang
SongGuang

Reputation: 71

you set con.setAutoCommit(false);

after st.executeBatch();

add con.commit();

Upvotes: 2

Andrew
Andrew

Reputation: 7768

Try using prepared statements - this way your DB is secured against sql injections. Something like this (fix to your values):

        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection con = (Connection) DriverManager.getConnection(DBurl, user, password);
        String read = "INSERT INTO Maps (mapName, mapSize) VALUES(?, ?)"
        PreparedStatement p = (PreparedStatement) con.prepareStatement(read);
        p.setString(1, "DB Test");
        p.setInt(2, 5);
        p.executeUpdate();
        long insertId = p.getLastInsertID();
        con.close();

Upvotes: 1

Corbin
Corbin

Reputation: 33437

It's because you set auto commit to false (con.setAutoCommit(false);), but didn't commit the transaction.

Non comitted inserts do not store the row, but they do increment auto increment fields.

You need to commit the transaction for the rows to stay after the transaction ends.

Upvotes: 5

Related Questions