How to insert values to mysql using java variables

Question

I am working with CLI Student Registration sample system. I want to connect the database with the CLI. I'm using MYSQL and Java for that. After running the below code an exception is shown like that:

public class Student_Registration {
    String name,uname,pwd;
    int age, select;
    public void input() {
        Scanner sc = new Scanner(System.in);
        System.out.println("Welcome to University Management System\n\n....\nStudent Registration");
        System.out.println("Please Submit the following information,\nName: ");
        name = sc.next();
        System.out.println("Username: ");
        uname = sc.next();
        System.out.println("Password: ");
        pwd = sc.next();
        System.out.println("Age: ");
        age = sc.nextInt();
        System.out.println("Select Course Number from following list");
        System.out.println("[1] SENG 11111 - Introduction to Programming");
        System.out.println("[2] SENG 11112 - Fundamentals of Engineering");
        System.out.println("[3] SENG 11113 - Data Structures and Algorithms");
        select = sc.nextInt();
    }
    public void add(){
        try{
           Class.forName("com.mysql.jdbc.Driver");
           Connection con = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/mydatabase", "root", "Luxan@22"
           );
           Statement st = con.createStatement();
           st.executeUpdate("INSERT INTO emp (Username, Name, Age, Password) VALUES ("+uname+","+ name +","+ age +","+ pwd+")");
           con.close();

        }catch (Exception e){System.out.println(e);}
    }
    public void Display() {
        System.out.println("You have successfully registered for the followuing course: ");
       try {
           Class.forName("com.mysql.jdbc.Driver");
           Connection con = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/mydatabase", "root", "Luxan@22"
           );
           Statement sta = con.createStatement();
        switch(select) {
            case 1:
                System.out.println("Subject: SENG 11111 - Introduction to Programming");
                sta.executeUpdate("INSERT INTO course (username,course) VALUES ("+uname+","+ "'Subject: SENG 11111 - Introduction to Programming')");
                break;
            case 2:
                System.out.println("Subject: SENG 11112 - Fundamentals of Engineering");
                sta.executeUpdate("INSERT INTO course (username,course) VALUES ("+uname+","+" 'Subject: SENG 11112 - Fundamentals of Engineering')");
                break;

            case 3:
                System.out.println("Subject: SENG 11113 - Data Structures and Algorithms");
                sta.executeUpdate("INSERT INTO course (username,course) VALUES ("+uname+","+" 'Subject: SENG 11113 - Data Structures and Algorithms')");
                break;
        }
        con.close();
    }catch(Exception e) {System.out.println(e);}
       System.out.println("Thank You");
    }
}

There is no issue with the no.of columns and related names in the table. The exception states as below:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'luxan' in 'field list'
You have successfully registered for the followuing course: 
Fri Nov 22 15:21:33 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Subject: SENG 11111 - Introduction to Programming
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'luxan' in 'field list'

The inputs I entered are:

Name: Thiluxan
Username: luxan
password: 1234
Age: 21

Karol Dowbecki · Accepted Answer · 2019-11-22 10:22:40Z

3

You are not escaping the values correctly which causes an error with the INSERT statement. In your example luxan should be quoted with ' as 'luxan' to indicate it's a text constant and not a column name. This lack of escaping also makes your code vulnerable to SQL Injection attack.

To address both problems you should use PreparedStatements as per docs:.

PreparedStatements st = con.prepareStatement(
    "INSERT INTO emp (Username, Name, Age, Password) VALUES (?, ?, ?, ?)");
st.setString(1, uname);
st.setName(2, name);
st.setInt(3, age);
st.setString(4, pwd);
st.executeUpdate();

edited Nov 22, 2019 at 10:22

answered Nov 22, 2019 at 10:12

Karol Dowbecki

45.3k9 gold badges83 silver badges118 bronze badges

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

Comments

Kavinithees · Accepted Answer · 2019-11-22 10:40:59Z

String SQL_INSERT = "INSERT INTO emp (Username, Name, Age, Password) VALUES (?,?,?,?);
try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydatabase", "root", "Luxan@22");
             PreparedStatement preparedStatement = conn.prepareStatement(SQL_INSERT)) {

            preparedStatement.setString(1, username);
            preparedStatement.setString(2, name);
            preparedStatement.setInt(3, age);
            preparedStatement.setString(4, pwd);

            int row = preparedStatement.executeUpdate();

            // rows affected
            System.out.println(row); //1

        } catch (SQLException e) {
            System.err.format("SQL State: %s \n %s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

no need to use class for name because in new version driver class will be automatically detected. use prepared statement instead of statement

Collectives™ on Stack Overflow

How to insert values to mysql using java variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related