2

How to change the datatype from varchar to int using JDBC connection with Java of a table in MySQL?

I tried to use the query: 

Alter table table_name CHANGE 'col 1' 'col 1' int (5 );

The file I am importing using the import option has the values in varchar, but I only required integer values from that table. The values of the imported table are not in ordered but the integer values are useful for me.

When I used the alter query in the phpmyadmin it works absolutely fine, but when I tried to alter in jdbc it gives me the error: 

Incorrect integer value: at 'col 1';

jdbc code was as:

try {
    Connection co=DriverManager.getConnection("jdbc:mysql://localhost:3306/csv_db","root",null);
    String c= " ALTER TABLE tpf1 MODIFY `col 3` int(5)";  
    String t="Alter table table_name CHANGE 'col 1' 'col 1' int (5 );";
    Statement stmt=co.createStatement();

    stmt.executeUpdate(t);

} catch (SQLException ex) {
    JOptionPane.showMessageDialog(this, ex);
}

Edit

try {
    Connection co=DriverManager.getConnection("jdbc:mysql://localhost:3306/csv_db","root",null);
    String c= " ALTER TABLE tpf1 MODIFY `col ` int(20)";  

    Statement stmt=co.createStatement();

    stmt.executeUpdate(t);

} catch (SQLException ex) {
    JOptionPane.showMessageDialog(this, ex);
}

The table screenshot is in the Photo:MySQL TABLE

This is actually a pdf file and converted it into .csv and imported to my database And i only need integer values from the table not any other alphabets and symbol. This code is only for one column and i need to alter all the three columns.So what should i do to get only integer values using jdbc? :| :)

Improve this question
7
  • Just fire another query. See think link for appropriate query. stackoverflow.com/questions/21784201/… Commented Feb 11, 2017 at 10:55
  • Do you know how to change it all, or are you stuck with how to execute it with JDBC? Commented Feb 11, 2017 at 10:56
  • 1
    Instead of 'col 1', you need to use `col 1`, although it might as well mean that col 1 contains a value that is not convertible to an integer value. Commented Feb 11, 2017 at 12:30
  • or between two [col 1] right @MarkRotteveel Commented Feb 11, 2017 at 12:50
  • @YCF_L Does that work MySQL? I thought that was MS Access, SQL Server and Sybase specific. Commented Feb 11, 2017 at 13:18

2 Answers 2

Reset to default
2

Like @Mark Rotteveel said in comment, you have a problem in your Query so the name of your columns should be between two `` and not between two '' or "" this not work if your column name contain more then one part so to change the type of your column you should to use this :

//change column type
String query = "ALTER TABLE tablename MODIFY `col 1` INT(5)";
Statement stmt = co.createStatement();
int rslt = stmt.executeUpdate(query);

To change the name or your column use this :

//change column name
query = "ALTER TABLE tablename CHANGE `old name` `new name` INT(5);";
stmt = co.createStatement();
rslt = stmt.executeUpdate(query);

so you can check if your query executed successfully like this :

if (rslt > 0) {
    System.out.println("Success");
} else {
    System.out.println("Failed");
}

Note

If you change the type this will change all the column, and set it to 0

I suggest to avoid two use a name with two parts you can use col_name instead because this make a problem for example if you are using JPA.

EDIT

I try this piece of code and it work fine :

public class UpdateColumn {

    static String driver = "com.mysql.jdbc.Driver";
    static String DB_username = "root";
    static String DB_password = "password";
    static String DB_URL = "jdbc:mysql://localhost:3306/db_name";

    public static void main(String args[]) {
        /*read from the file*/
        try {
            Class.forName(driver);
            java.sql.Connection co = DriverManager.getConnection(DB_URL, DB_username, DB_password);
            //change column type
            String query = "ALTER TABLE tablename MODIFY `c 1` INT(5);";
            Statement stmt = co.createStatement();
            int rslt = stmt.executeUpdate(query);
            System.out.println(rslt);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

MySQL

create database db_name;

use db_name;

drop table t1;

create table tablename(
`c 1` varchar(5),
`c 2` int
);

INSERT INTO tablename values('java', 1);
INSERT INTO tablename values('mysql', 2);

Good luck.

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

12 Comments

Ok.Thanks for the reply but no change was there by changing from 'col 1' to col 1. The error remains the same. And i like to add another information that the col 1 contain the alphabets and integer as well like example : Subject, code , course ,1,2,3, 4. These values are in different rows respectively and i need only integer values :)
what did you mean remain the same what you want to change exactly the type or the name @AshbirDhiman
@AshbirDhiman if you want to change the name then this is not a problem, if you want to change the type, this can make a problem, so what you want to do, change the name or change the type?
@AshbirDhiman i edit my answer i post now all my code and SQL so i hope this work with you
can you post what you tried please? the complete code?
|
0 
statement.executeUpdate("ALTER TABLE table_name ALTER COLUMN column_name datatype");
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.