CODEWITHSUNDEEP
javamysqldatabasejdbc
Crown
Crown

Reputation: 13

Failed to insert data into mysql using java

I try to use java code to insert data into the mysql database. I built a user table in the database. User table has 4 fields：id, username, password and address. 

mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(32) | YES  |     | NULL    |                |
| password | varchar(64) | YES  |     | NULL    |                |
| address  | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

This is my java code.

    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC";
        String mysqlUserName = "root";
        String mysqlPassword = "123456";
        conn = DriverManager.getConnection(url, mysqlUserName, mysqlPassword);
        String sql = "INSERT INTO user(username,password,address) VALUES (?,?,?)";
        stmt = conn.prepareStatement(sql);
        stmt.setString(1,"11dsa1");
        stmt.setString(2,"111");
        stmt.setString(3,"111");
        stmt.close();
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            if (conn!=null)
                conn.close();
        }catch (SQLException e){

        }
        try {
            if (stmt!=null)
                stmt.close();
        }catch (SQLException e){

        }
    }

No error occurred. But there is no data in my database.

mysql> select * from user; 
Empty set (0.01 sec)

But I can insert data by the shell.

mysql> insert into user(username,password,address) values ("aaa","aaa","aaa");
Query OK, 1 row affected (0.09 sec)

mysql> select * from user;
+----+----------+----------+---------+
| id | username | password | address |
+----+----------+----------+---------+
| 22 | aaa      | aaa      | aaa     |
+----+----------+----------+---------+
1 row in set (0.00 sec)

But the id is 22 not 1 !

Actually I have run this java code 21 times before. My understanding is that this java code can insert data into mysql, but immediately remove the data from the database. I really can't understand this ridiculous situation

Upvotes: 0

Views: 1825

Answers (3)

Yohan Malshika
Yohan Malshika

Reputation: 733

Before stmt.close();, add this stmt.executeupdate(); because you should update your table then you should update your queries.

Upvotes: 1

miiiii
miiiii

Reputation: 1620

Solution:

Before your stmt.close() add this..

stmt.executeUpdate();

It should work!!

Suggestion:

Also, if you are on Java version higher than 6, you should go for try-with resources approach that manages the crucial resource in smarter way. !!

Upvotes: 2

jjd
jjd

Reputation: 2288

Did not you forget to actually execute the statement?

Before closing it just put stmt.executeUpdate() so it is 

String sql = "INSERT INTO user(username,password,address) VALUES (?,?,?)";
stmt = conn.prepareStatement(sql);
stmt.setString(1,"11dsa1");
stmt.setString(2,"111");
stmt.setString(3,"111");
stmt.executeUpdate()

I also would advise you to close your prepared statement in a finally block or if you use java 7+ take a look how you can use prepared statements with try-with-resources here. Also if you close your connection in finally block, why would you want to close it in try block as well(last command)? It is absolutely redundant.

In general keeping you code clean would allow you to read it better and thus to better see possible issues.

Upvotes: 3

Related Questions