How to use java variable to insert values to mysql table?

Question

Hi i am trying to insert the values in to mysql table. i am trying this code. i have assigned values to variable and i want to pass that variable to that insert statement. Is this correct?

code
    int tspent = "1";
    String pid = "trng";
    String tid = "2.3.4";
    String rid = "tup";
    String des = " polish my shoes!";

    INSERT INTO `time_entry`(pid,tid,rid,tspend,description) VALUE ('"+pid+"','"+tid+"','"+rid+"',"+tspent+",'"+des+"');

here is what i have tried, but i am not able to insert values

try
       {
           conn=DBMgr.openConnection();     
           String sqlQuery = "INSERT INTO `time_entry`(pid,tid,rid,tspend,description) VALUE ('"+pid+"','"+tid+"','"+rid+"',"+tspent+",'"+des+"');";
           st = conn.createStatement();
           rs = st.executeQuery(sqlQuery); 
       }

@Sam... It's pretty obvious that this code is not even going to compile. — Menno
– Menno, Commented Jun 20, 2013 at 6:58

Rohit Jain · Accepted Answer · 2013-06-20 07:06:14Z

21

You should use executeUpdate() method whenever your query is an SQL Data Manipulation Language statement. Also, your current query is vulnerable to SQL Injection.

You should use PreparedStatement:

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO `time_entry`(pid,tid,rid,tspend,description) VALUES (?, ?, ?, ?, ?)");\

Then set the variables at those index:

pstmt.setString(1, pid);
// Similarly for the remaining 4 

// And then do an executeUpdate
pstmt.executeUpdate();

edited Jun 20, 2013 at 7:06

answered Jun 20, 2013 at 6:58

Rohit Jain

214k45 gold badges419 silver badges534 bronze badges

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

Comments

Ganesh Rengarajan · Accepted Answer · 2013-06-20 07:03:09Z

17

Try this,

    String driver="com.mysql.jdbc.Driver";
    String url="jdbc:mysql://localhost:3306/dbname";
    String uname="username";
    String pass="password";
    Class.forName(driver);
    Connection c=(Connection) DriverManager.getConnection(url,uname,pass);
    Statement s=c.createStatement();
    s.executeUpdate("INSERT INTO `time_entry`(pid,tid,rid,tspend,description) VALUE ('"+pid+"','"+tid+"','"+rid+"',"+tspent+",'"+des+"')");

answered Jun 20, 2013 at 7:03

Ganesh Rengarajan

2,00613 silver badges26 bronze badges

2 Comments

Manish Bhadani Over a year ago

you take all string. Is it workable for integer datatype variable?

Manuel Jordan Over a year ago

Do not use ('"+pid+"','"+tid+"','"+rid+"',"+tspent+",'"+des+"'), it is an open door for SQL Injection

AllTooSir · Accepted Answer · 2013-06-20 07:13:30Z

10

Use a PreparedStatement and set the values using its setXXX() methods.

PreparedStatement pstmt = con.prepareStatement("INSERT INTO `time_entry`
        (pid,tid,rid,tspend,description) VALUE 
        (?,?,?,?,?)");
pstmt.setString(1, pid );
pstmt.setString(2, tid);
pstmt.setString(3, rid);
pstmt.setInt(4, tspent);
pstmt.setString(5,des );
pstmt.executeUpdate();

edited Jun 20, 2013 at 7:13

answered Jun 20, 2013 at 6:57

AllTooSir

49.5k17 gold badges132 silver badges164 bronze badges

Comments

rahul kayal · Accepted Answer · 2017-01-05 07:41:07Z

import java.sql.*;  
class Adbs1{  
public static void main(String args[]){  
try{  
Class.forName("com.mysql.jdbc.Driver");  
Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/rk","root","@dmin");  
//here rk is database name, root is username and password  
Statement stmt=con.createStatement();  

stmt.executeUpdate("insert into emp values('rk11','Irfan')");
 // stmt.executeUpdate("delete from  emp where eid ='rk4'");
//stmt.executeUpdate("update emp set ename='sallu bhai' where eid='rk5'");

 ResultSet rs=stmt.executeQuery("select * from emp");  
   while(rs.next())  
    System.out.println(rs.getString(1)+"  "+rs.getString(2));  

con.close();  
      }catch(Exception e){ System.out.println(e);}  
    }  
}

Collectives™ on Stack Overflow

How to use java variable to insert values to mysql table?

4 Answers 4

Comments

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Linked

Related