How to insert data into mysql using Java

Question

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 :)

Talking with a database from Java is much easier if you use an ORM framework like Hibernate instead of JDBC. — aroth
– aroth, Commented Oct 6, 2012 at 13:25

mstzn · Accepted Answer · 2012-10-06 13:27:21Z

4

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();

edited Oct 6, 2012 at 13:27

answered Oct 6, 2012 at 13:14

mstzn

2,9413 gold badges29 silver badges37 bronze badges

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

3 Comments

GreyBeardedGeek Over a year ago

Using bound parameters like this is really important in a real-world application, where the data is provided by an end user. Google "Little Bobby Tables" for a humorous look at why this is the case. Also, statement.executeUpdate() instead of execute() will give you count of the number of rows actually inserted, though this is more important when doing updates than when doing inserts.

black_belt Over a year ago

@mstzn Thank you very much. It is working now. Should I use statement.execute(); for updating database as well?

mstzn Over a year ago

statement.execute() => If you dont know which method to be used for executing SQL statements, this method can be used.statement.executeUpdate => Generally drob table or database, insert into table, update table , delete from table statements will be used in this.

Anshu · Accepted Answer · 2012-10-06 13:25:17Z

1

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();

answered Oct 6, 2012 at 13:25

Anshu

7,8635 gold badges33 silver badges41 bronze badges

Comments

Gianmarco · Accepted Answer · 2012-10-06 13:26:47Z

0

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 ->''

answered Oct 6, 2012 at 13:26

Gianmarco

2,55027 silver badges58 bronze badges

Comments

nagendra babu · Accepted Answer · 2020-02-10 03:06:57Z

@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();
    }

Collectives™ on Stack Overflow

How to insert data into mysql using Java

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related