how can i have a user input things into mysql using java

businessesatoz
businessesatoz used Ask the Experts™
on
Hello, i am trying to create a small program to learn with.. i have created a user input and made my connection to my database good. Although i want to populate my fields with user input using JAVA and MYSQL. so it will ask the user for "First Name" and then insert into first name.  below is my code so far. thank you all for your help.
'import javabook.*;
import java.io.*;

public class bhavdip 
  
{
  


public static void main(String [] args) {

  
        java.sql.Connection conn = null;

        System.out.println("SQL Test");

        try {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = java.sql.DriverManager.getConnection(
                        "jdbc:mysql://localhost/people");
 
        }
        catch (Exception e) {
                System.out.println(e);
                System.exit(0);
                }

        System.out.println("Connection established");
        
//get input statement:
        try{
System.out.print("Enter String:");
BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
String data= input.readLine();
System.out.print("You have entered: "+data);
}
catch(Exception e) {
e.printStackTrace();
}


try
{
  java.sql.Statement s = conn.createStatement ();
   int count;
count = s.executeUpdate(
                        "INSERT INTO customers (FirstName,LastName,Phone,Email)"
                        + "VALUES"
                       (data)); 
}  
catch (Exception e) {
                System.out.println(e);
                System.exit(0);
                }

        //this is the sql select statement.
        try {

                java.sql.Statement s = conn.createStatement();
                java.sql.ResultSet r = s.executeQuery ("SELECT FirstName,LastName,Phone  FROM customers");
                while(r.next()) {
                  System.out.println(
                  r.getString("FirstName")+" "+
                  r.getString("LastName")+"  "+
                  r.getString("Phone")
                 );
                        }
          
        }
        catch (Exception e) {
                System.out.println(e);
                System.exit(0);
                }
        
       
        }

}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Could you please explain what is not working?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
           Hi!

This will not work
count = s.executeUpdate(
                        "INSERT INTO customers (FirstName,LastName,Phone,Email)"
                        + "VALUES"
                       (data));

However this will work using Prepared statement
...      
      pstmt = conn.prepareStatement(INSERT INTO customers (FirstName,LastName,Phone,Email) values (?, ?, ?,?)");
      pstmt.setString(1, data_fn); //data_fn string that holds the firstname
      pstmt.setString(2, data_ln);//data_ln string that holds the lastname
      pstmt.setString(3, data_ph);//data_ph string that holds the phonenumber
      pstmt.setString(3, data_em);//data_em string that holds the email
      pstmt.executeUpdate();
...

Links/examples to look at
http://www.java2s.com/Code/Java/Database-SQL-JDBC/MySQL.htm
http://www.java2s.com/Code/Java/Database-SQL-JDBC/PreparedStatement.htm
http://www.java2s.com/Code/Java/Database-SQL-JDBC/CatalogDatabase-SQL-JDBC.htm

Regards,
     Tomas Helgi
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hmmm, yes why this will not work is that your sql - insert statement is wrong. You need to have as many values as there are columns you are inserting.
Like the insert statement int the pstmt class I showed you.
Then if you are only inserting into 1 column of a row and then do several updates where you put the values of the rest of the columns your table columns
need to allow nulls or have default values otherwise "insert into customers(firstname) values(data_fn) will fail.

Regards,
    Tomas Helgi
Description:

1) db connection
conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/people","root","admin");

3306 : mysql default port
people: name of the database
root: mysql username
admin: mysql password

2) make sure you have table  = customers with the field = FirstName,LastName,Phone,Email

import java.io.BufferedReader;
import java.io.InputStreamReader;

public class bhavdip
{
    public static void main(String[] args)
    {
        java.sql.Connection conn = null;
        System.out.println("SQL Test");
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","admin");
        }
        catch (Exception e)
        {
            System.out.println(e);
            System.exit(0);
        }

        System.out.println("Connection established");

        //get input statement:
        try
        {
            BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
            System.out.print("Enter First Name:");
            String fname = input.readLine();

            System.out.print("Enter Last Name:");
            String lname = input.readLine();

            System.out.print("Enter Phone:");
            String phone = input.readLine();

            System.out.print("Enter Email:");
            String email = input.readLine();

            java.sql.Statement s = conn.createStatement();
            int count;
            count = s.executeUpdate("INSERT INTO customers (FirstName,LastName,Phone,Email) VALUES('"+fname+"','"+lname+"','"+phone+"','"+email+"')");

            s.close();

            System.out.println("===== SHOWING RESULTS ============");
            s = conn.createStatement();
            java.sql.ResultSet r = s.executeQuery("SELECT FirstName,LastName,Phone  FROM customers");
            while (r.next())
            {
                System.out.println(">> "+
                        r.getString("FirstName") + " " +
                                r.getString("LastName") + "  " +
                                r.getString("Phone")
                );
            }

            s.close();
            r.close();
            conn.close();
        }
        catch (Exception e)
        {
            System.out.println(e);
            System.exit(0);
        }
    }
}

Open in new window

Author

Commented:
thank you, i can understand what you dd here. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial