Link to home
Start Free TrialLog in
Avatar of duta
duta

asked on

How to create mySQL database and how to put the user's input into the database and how to retrieve it?

Hi, experts!

I created a simple code with tons of you experts' help to get input from the user.

Now I would like to learn how to create a MySQL database (table), how to  connect to the dataabase, how to transfer the user's input into the database, and how to retrieve it.

Hope you may kindly show me how to do that as you have always done!

Below is my code:

Thanks a lot!

____________________ The beginning of the code _____________________

import javax.swing.JOptionPane;

public class SumQuiz
{
    static final float MAX = 100f;
   
     public static void main(String[] args)
     {
          SumQuiz duta = new SumQuiz();
          float quiz1 = duta.getScore("Quiz1 score?");
          float quiz2 = duta.getScore("Quiz2 score?");
          String name = duta.getName ("Your Name?");
          String ssn = duta.getSSN ("Your social securiy number?");
         
          duta.showMessage("Your name: " + name + ".\n Your SSN#: " + ssn
                   + ". \nYour total quize score is " + (quiz1 + quiz2));
          duta.issueWarning (quiz1 + quiz2);
      }

     float  getScore(String dialogMessage)
     {
          float input = MAX + 1;
          input = showInput(dialogMessage);
          while (input < 0 || input > MAX)
            {
                  input = showInput("Invalid data. " + dialogMessage);
            }
          return input;
     }
     
     String  getName(String dialogMessage)
     {
          String name;
          name = JOptionPane.showInputDialog (dialogMessage);
          int length = name.length ();
          while (length < 2 || length > 10)
            {
                  name = JOptionPane.showInputDialog("Invalid name. " + dialogMessage);
            length = name.length ();
            }
          return name;
     }
     
     String  getSSN(String dialogMessage)
     {
          String ssn;
          ssn = JOptionPane.showInputDialog (dialogMessage);
          int length = ssn.length ();
          while ( length != 9 )
            {
                  ssn = JOptionPane.showInputDialog("Invalid number. " + dialogMessage);
            length = ssn.length ();
            }
          return ssn;
     }

     float showInput (String mes)
     {
         float input;
         input = Float.parseFloat (JOptionPane.showInputDialog (mes));
         return input;
     }
     
     void showMessage(String message)
     {
          JOptionPane.showMessageDialog(null, message);
     }
 
      void issueWarning (float n)
     {
         float total = n;
         if (total >= 90)
         {  
             showMessage ("You got 'A'.");
         }
         else if (total >= 80)
         {
             showMessage ("You got 'B'.");
         }
         else if (total >= 70)
         {
             showMessage ("You got 'C'.");
         }
         
         else
         {
             showMessage ("You failed.");
         }
      }
}

___________________________ The end of the code ___________________________________
ASKER CERTIFIED SOLUTION
Avatar of Kannan Ekanath
Kannan Ekanath
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oops!! am extremely sorry for manyline breaks at the end.
But here are two examples,
http://javaalmanac.com/egs/java.sql/ConnectMySql.html   (For connecting to MYsQL)
http://javaalmanac.com/egs/java.sql/Insert.html (For inserting rows)
Avatar of duta
duta

ASKER

TO: kannan:

Thank you so much for your kind, wonderful tips.

On how to install MySQL, I know how to becuase I have used PHP and MySQL just for a while.

But I am totally new to how to create a database and table and transfer a data input from the user into the database using Java.

I am writing to you upon sighting your kind response. I am going to check every line and word that you kindly gave me, and will come back to you most likely some more questions.

Thanks a lot!

duta

Avatar of duta

ASKER

TO: kannan:

Hi again!

I checked the site you kindly gave me, and found the following links.

I have already the regular MySQL installed in my desktop. Do I need to download and install JDBC driver additionally?


Thanks a lot!

duta



----------------------------------------------- what duta found on the page------------------------------------------
e232. Getting JDBC Drivers for a Database
e233. Loading a JDBC Driver
e234. Listing All Loaded JDBC Drivers

Connections
--------------------------------------------------------------------------------
e235. Connecting to an Oracle Database
e236. Connecting to a MySQL Database
e237. Connecting to a SQLServer Database
e238. Listing All Available Parameters for Creating a JDBC Connection
e239. Determining If a Database Supports  
 _________________________________________________________
Avatar of mbvvsatish
yeah.
you need to download the JDBC drivers for mysql
and put the jar file in the classpath in order to use

you can download from this
http://dev.mysql.com/downloads/connector/j/3.1.html

check these url's using the driver
https://www.experts-exchange.com/questions/20952940/JDBC-driver-installation.html
https://www.experts-exchange.com/questions/20709641/JDBC-connection-with-JSP.html
Avatar of duta

ASKER

TO: mbvvsatish

Thanks a lot!

I will thoroughly check all the links that you kindly gave me.

I am totally new to this thing, and any tip will help me save tons of time and frustration.

Thanks again!

duta
after downloading the zip file from the above url.
take the mysql-connector-java-3.1.13-stable-bin.jar file and adding it in your classpath is important
Avatar of duta

ASKER

Thanks again, mbwsatish.

But it seems a little foggy to me yet. But I will do my best to get it done with your kind help.

duta
Avatar of duta

ASKER

TO:

Hi again!

While trying unzip mysql-connector-driver, I got the following error message:

Thanks a lot!

duta


______________________ The beginning of error message __________________________________________
WinZip
---------------------------
Error:  The system cannot find the path specified.
Cannot create C:\Documents and Settings\admim\Desktop\Java all\mysql-connector driver\mysql-connector-java-3.1.13\docs\release-test-output\4.0.26\Linux-i386-2.6.9.5.0.3.ELsmp\Sun_Microsystems_Inc.-1.4.2.06\unitregress\testsuite\regression\MicroPerformanceRegressionTest.html.
---------------------------
OK   Help  
---------------------------

________________________ The end of error message ________________________________________


Avatar of duta

ASKER


TO: All:

The unzipping stalled, but I found  "mysql-connector-java-3.1.13-bin-g.jar" among a few unzipped files. I copied it and pasted in  "C:\Program Files\Java\jdk1.5.0_07\jre\lib\ext".

Did I do alright?

Thanks!

duta
Avatar of duta

ASKER

TO: mbvvsatish"


YOU said: "after downloading the zip file from the above url.
take the mysql-connector-java-3.1.13-stable-bin.jar file and adding it in your classpath is important."

What do you mean by "adding it in your classpath"?  Where is the classpath?  Can you give me just a little bit more info?

Do I need to do this (after downloading the zip file from the above url.
take the mysql-connector-java-3.1.13-stable-bin.jar file and adding it in your classpath is import) in my workstation or in the web server or file server?  Currently I am installing this in my workstation.

Thanks!

duta
Avatar of duta

ASKER

TO: all:

I got the following script from "http://javaalmanac.com/egs/java.sql/ConnectSqlServer.html."

Where am I supposed to put the following script into my code (at the top of this page)?

Do I need to replace URL (in the following script) with my own?

Any tip will be greatly appreciated.

Thanks!

duta



________________ The script _______________


Connection connection = null;
    try {
        String driverName = "com.jnetdirect.jsql.JSQLDriver"; // NetDirect JDBC driver
        String serverName = "127.0.0.1";
        String portNumber = "1433";
        String mydatabase = serverName + ":" + portNumber;
        String url = "jdbc:JSQLConnect://" + mydatabase; // a JDBC url
        String username = "username";
        String password = "password";
   
        // Load the JDBC driver
        Class.forName(driverName);
   
        // Create a connection to the database
        connection = DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
        // Could not find the database driver
    } catch (SQLException e) {
        // Could not connect to the database
    }
_____________________________________________________________________
____________________________________________________________
Avatar of duta

ASKER

to ALL:

Currently I am here:

1. I downloaded "mysql-connect or-java-3.1.13-bin-g.jar" and put it in "C:/Program Files/Java/jdk1.5.0_7/jre/lib/ext/mysql-connect
or-java-3.1.13-bin-g.jar"

2. Then I made a code as the follows: Can you please kindly check to see what is left to do and what is the problem with the code?

Thanks!

duta

___________________ The beginning of the code _________________________________________________



import java.util.*;
import java.sql.*;
import java.sql.DriverManager;

import java.sql.*;
import javax.swing.JOptionPane;

public class TestDialog
{
    private static Connection conn;
    private static String url = "jdbc:mysql://localhost/java1?user=root&password=root";
    private static String user = "root";
    private static String pass = "root";
    private static Statement statement;
    static float total;
    static final float MAX = 100f;
   
    public static void main(String[] args)
     {
          TestDialog testDialog = new TestDialog();
          float test1 = testDialog.getScoreFromUser("Test1 score?");
          float test2 = testDialog.getScoreFromUser("Test2 score?");
         
          testDialog.showMessage("The total score is " + (test1 + test2));
                 
           try
        {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            statement = conn.createStatement();
        }
        catch (ClassNotFoundException e)
        {
            System.err.println("Driver Error " + e);
            conn = null;
        }
        catch (SQLException e)
        {
            System.err.println("SQL Error " + e);
        }
    }

     float getScoreFromUser(String dialogMessage)
     {
          float input = -1;
          while (input < 0 || input > MAX)
          {
               input = Float.parseFloat(JOptionPane.showInputDialog(dialogMessage));
               while (input < 0 || input > MAX)
               {
                  input = Float.parseFloat(JOptionPane.showInputDialog("Invalid data. " 
                          + dialogMessage));
               }
             
          }
          return input;
     }

     float showInput (String mes)
     {
         float input;
         input = Float.parseFloat (JOptionPane.showInputDialog (mes));
         return input;
     }
     
     void showMessage(String message)
     {
          JOptionPane.showMessageDialog(null, message);
     }
}

_________________________ The end of the code __________________________________________
Duta,
Guess you dint follow correctly. I posted a line on http://javaalmanac.com/egs/java.sql/Insert.html
try
        {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pass);
            statement = conn.createStatement();
            String sql = "INSERT INTO my_table (name, mark) VALUES('" + name + "', '" + totalMark + "')";
   
            statement.executeUpdate(sql);
}

Let me know if you are getting any exceptions ;)
>private static String url = "jdbc:mysql://localhost/java1?user=root&password=root";
this should be like this, where java1 is the name of the database that you are connecting
private static String url = "jdbc:mysql://localhost/java1";

check whether the password that you gave for root is correct or not.
Default installation of mysql doesn't have any password.

let us know if you get any exceptions...