How to add integer and Varchar(20) and store as Varchar(20) in mysql

Question

I have db named spareparts in which all values are stored as varchar(20). I am taking input from the user through jtextfield as a number then convert into integer. s1(which is itemcode) and s2(which is quantity to be added to actual quantity) are the input Now I want to add this input into 'quantity'(which is also varchar) as:

public class AddStockDAO{
   Connection connection;
   PreparedStatement preparedStatement;
   ResultSet resultSet;
   Statement statement;

   public AddStockDAO(String s1,String s2)
   {
       int num=Integer.parseInt(s2);
       String sql= "select "+
             " cast(quantity as INT) from spareparts"+
             "set quantity+= "+num+
             " cast(quantity as varchar(20))"+
             "where itemcode='?'";
       try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "")) {
           try (PreparedStatement stmt = conn.prepareStatement(sql)) {
               stmt.setString(1, s1);

               stmt.executeUpdate();
           }
       } catch (SQLException ex) {   
           Logger.getLogger(AddProductGUI.class.getName()).log(Level.SEVERE, null, ex);
       }

   } 
}

Could you clarify? You want to save that number into "NUM_EnteredByUser" or you want to get that number from DB select? What is the code example for? — Mike B, Apr 24 '19 at 17:51
I believe op is trying to take two values from a user: the number to add to quantity and the itemcode. — Matt, Apr 24 '19 at 17:54
User will enter some number is jtextfield, which I will get from this class's constructor. The s1 is the itemcode entered by the user and the s2 is the quantity entered by the user. I want to add this quantity into original database where id is the id which user entered — , Apr 24 '19 at 17:56

score 0 · Answer 1 · answered Apr 24 '19 at 17:38

0

I would not recommend this. How would you know if an Integer + Varchar(20) is more than 20 characters?

answered Apr 24 '19 at 17:38

Connor

365
3
10

It can't be. See [max value of integer](https://stackoverflow.com/questions/15004944/max-value-of-integer) – Matt Apr 24 '19 at 17:41
@Matt I know that in Java there is a max value for integer, but the number in the DB is a varchar(20). I would recommend using a BigInteger for the addition. – Connor Apr 24 '19 at 17:41
I just want to add small values, actually it is a database which will contain quantity of products which can be in thousands at max – Apr 24 '19 at 17:48
User will enter some number in jtextfield, which I will get from this class's constructor. The s1 is the itemcode entered by the user and the s2 is the quantity entered by the user. I want to add this quantity into original database where id is the id which user entered – Apr 24 '19 at 17:57

Matt · Answer 2 · 2019-04-24T17:55:23.303

0

As @Connor has said, there is no way to make sure the result is less than 20 characters, so using a BigInteger field is much better, but if the field MUST be a varchar(20), I would recommend selecting that field from the database, doing the addition in java, and then storing the result back in the database.

edited Apr 24 '19 at 17:55

answered Apr 24 '19 at 17:44

Matt

902
7
20

Mike B · Accepted Answer · 2019-04-24T18:24:55.677

0

If I understand the problem correctly, you want to update the record in the DB according to the itemcode. So you just cast the existing value to number, add your additional value (with +) and cast it back.

int num = 0;
try {
    num = Integer.parseInt(s2);
} catch (Exception e) {
    System.out.println('Nope, that was not a number');
    //Do something else?
}
String insertQuery = "UPDATE spareparts SET quantity = cast(cast(quantity as integer)+"+num+" as char(20)) where itemcode = "+s1+";";

try {
    connection = DriverManager.getConnection(...); //add your data
    preparedStatement = connection.prepareStatement(sql)
    preparedStatement.executeUpdate();
} catch (Exception e) {
    //Something broke
}

Also - You should use the class variables you've defined at the top of your class, so you don't have to recreate the connection later on.
But If you add more AddStockDAO, each of them will create new connection, so I'd suggest you create a database class where you setup a connection and just pass some data or a query to a method to run it on the existing connection.

edited Apr 24 '19 at 18:24

answered Apr 24 '19 at 18:04

Mike B

2,756
2
16
28

This query is just updating value I want this query to add value into already existing value – Apr 24 '19 at 18:16
Oh, so if there is a value of 3, and person put 4, you want to update to 7? – Mike B Apr 24 '19 at 18:17
I mean if the value of quantity is 15 and user enters 10 it should save it as quantity=25 – Apr 24 '19 at 18:17
@SYEDSHEHROZSOHAIL - see my changed `insertQuery` – Mike B Apr 24 '19 at 18:25
@SYEDSHEHROZSOHAIL accept this as the correct answer please – Mike B Apr 24 '19 at 18:34

How to add integer and Varchar(20) and store as Varchar(20) in mysql

3 Answers3