CODEWITHSUNDEEP
javamysql
black_belt
black_belt

Reputation: 6799

How to insert data into mysql using Java

I am trying to insert data into mysql database using Java. I am using the following code to fetch data from database and it is working fine.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DbTest {

public static void main (String[] args) throws Exception{


    // Accessing Driver From Jar File
    Class.forName("com.mysql.jdbc.Driver");

    //DB Connection
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");  

    // MySQL Query
    PreparedStatement statement= con.prepareStatement("SELECT * FROM jam WHERE id='1'");

    //Creating Variable to execute query
    ResultSet result=statement.executeQuery();

    while(result.next()){

        System.out.println(result.getString(2));

    }

  }


  }

To Insert data, I have tried the above code only replacing 

PreparedStatement statement= con.prepareStatement("SELECT * FROM jam 
 WHERE id='1'");

with

 PreparedStatement statement= con.prepareStatement("INSERT INTO jam(id,name) 
 VALUES ('','Charlie Sheen')");

But its showing some errors. Could you please tell me where is the problem in my code.

Thanks :)

Upvotes: 1

Views: 16618

Answers (4)

nagendra babu
nagendra babu

Reputation: 57

@PostMapping("/save")
public void save(@RequestBody EmployeeListModel employeeListModels) throws SQLException{

    Connection connection=MyDataSource.getConnection(); 

     try{

    connection.setAutoCommit(false);
    PreparedStatement statement = connection.prepareStatement("insert into employee(Id,first_name,last_name,email) values (?,?,?,?)");
    List<EmployeeModel> employeeModels = employeeListModels.getModels();

    Iterator<EmployeeModel> iterator = employeeModels.iterator();

    while(iterator.hasNext()){
        EmployeeModel model = iterator.next();
        statement.setInt(1, model.getId());
        statement.setString(2, model.getFirstName());
        statement.setString(3, model.getLastName());
        statement.setString(4, model.getEmail());
        statement.addBatch();

    }

    int[] updates = statement.executeBatch();

    for(int i=0;i<updates.length;i++){
        if(updates[i] == -2){
            System.out.println("executed fail"+i);
        }
        else{
            System.out.println("executed:"+i+"successful"+updates[i]+"updated");
        }


    }

    connection.commit();
    statement.close();
    connection.close();
     }catch (Exception e) {
        e.printStackTrace();
    }

Upvotes: 0

mstzn
mstzn

Reputation: 2931

If your primary key is auto increment,you can try this;

  PreparedStatement statement= con.prepareStatement("INSERT INTO jam(name) 
     VALUES (?)");

    statement.setString(1,"Charlie Sheen");

statement.execute();

Upvotes: 4

Gianmarco
Gianmarco

Reputation: 2552

I think you can also use this (if the ID is auto-increment)

"INSERT INTO jam(name) VALUES ('Charlie Sheen')"

in any case, If you do not know the value of a specific int field I suggest not to write it into the query, it will be setted by default.

I think, if you set and INTEGER column = '', it could be a problem to the mysql.

usually, I do not put numbers inside the ->''

Upvotes: 0

Anshu
Anshu

Reputation: 7853

If you have to insert lots of records, use batch insert

    import java.sql.Connection;
    import java.sql.PreparedStatement;
     
    //...
     
    String sql = "INSERT INTO jam(id, name) VALUES (?, ?)";
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");  
 PreparedStatement ps = connection.prepareStatement(sql);
     
    for (Employee employee: employees) {
        ps.setString(1, employee.getId());
        ps.setString(2, employee.getName());
        ps.addBatch();
    }
    ps.executeBatch();
    ps.close();
    connection.close();  

Upvotes: 1

Related Questions