Java Statement passing variables to MySQL Statement

[Joshepmichel]

Please to help me to following problem

I want to do this
1. create Table Name MEMBER on the Database Name "mytestdb",
2. Add the Values to the Table through the Key board Inputs during running the My Java Application.; Therefore I used this following codes, It Consists the seperate parts for the Raede and Member Class for the purposely I craetes.

3. I have problem to Parsing the values to SQL statement, that Consists on the Run Time values, It shows me in following codes Syntax error on this block of Codes

query = "INSERT INTO MEMBER(Member_ID,University_ID,Name," +
                          "Registration_Date,Catergory,Renewed_Date)" +
                          " VALUES(mbr.getMemberId(),mbr.getUniversityId()," +
                          "mbr.getName(),mbr.getRegisterdDate(),mbr.getRegisteredCategory()," +
                          "mbr.getRenewalDate() )";
          stm.executeUpdate(query);

So, My Question Is How Can I Pass the Values through the Variable on Run time to MYSQL

I need urgently to solve this problem.
USED OS Windows, ECLIPSE IDE Used
mbr.getRegisterdDate(), In here mbr is Member type Object That I created, it will give the String return Value.

Thank you.
Joshep

package pretest;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
 
//This is the library system Version 1.1.1.(20.02.2008)on Thursday
public class LibJDBCv1 {
 
    /**
     * @param args
     */
    public static void main(String[] args) {
        System.out.println("MySQL Connect Example Modfied version ."); 
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/";
        String dbName = "mytestdb";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "dbadmin"; 
        String password = "dbadmin";
        try {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(url+dbName,userName,password);
 
          System.out.println("Connected to the "+ dbName+" database");
          Statement stm = conn.createStatement();
 
          // CREATES Table Name Member
          String query;
          try{
              stm.executeUpdate("DROP TABLE MEMBER");
              System.out.println("Table Deletion process is completly successfully!");
            }
            catch(SQLException s){
              System.out.println("MEMBER Table is not exists!");
            }
 
 
 
          query = "CREATE TABLE MEMBER(Member_ID CHAR (10) NOT NULL, "
              +"University_ID CHAR (10),"+"Name VARCHAR (100),"
              +"Registration_Date DATE,"+ "Catergory CHAR(3) NOT NULL," 
              +"Renewed_Date DATE,"+"PRIMARY KEY (Member_ID))";
 
          stm.executeUpdate(query);
          /* Member database Table Created ......................................................*/
 
 
          System.out.println(" South Eastern University Library System \n");
 
            Member mbr= new Member();
 
            String nm = Reade.readentry("Enter Name                     :  ");
                        mbr.setName(nm); // mbr is an instace of the member Class
            nm = Reade.readentry("Enter Member ID                 :  ");
                        mbr.setMemberId(nm); // here we have to chang this, that as automatic adding the  Member ID
            nm = Reade.readentry("Enter Membership Catergory    :  ");
                        mbr.setRegisteredCategory(nm); 
            nm = Reade.readentry("Enter University ID             :  ");
                        mbr.setUniversityId(nm); 
            Calendar cal = Calendar.getInstance(); // Start date receiver
                        mbr.setRegisterdDate(cal.getTime()); // date set by the Date forMAT 
                         mbr.setRenewalDate( mbr.getRegisterdDate()); // Innitially Bot are Same
 
 
 
          // Inser the Database Values
        query = "INSERT INTO MEMBER(Member_ID,University_ID,Name," +
                          "Registration_Date,Catergory,Renewed_Date)" +
                          " VALUES(mbr.getMemberId(),mbr.getUniversityId()," +
                          "mbr.getName(),mbr.getRegisterdDate(),mbr.getRegisteredCategory()," +
                          "mbr.getRenewalDate() )";
          stm.executeUpdate(query);
 
 
 
          System.out.println("\n Inserted Values For the Member class table on  "+dbName  );
 
 
          // create user
          query = "SELECT *  FROM  MEMBER";
          ResultSet rs=stm.executeQuery(query);
          Member mbrQ = new Member();
          while (rs.next()){
                  mbrQ.setMemberId(rs.getString("Member_ID"));
                  mbrQ.setUniversityId(rs.getString("University_ID"));
                  mbrQ.setName(rs.getString("Name"));
                  mbrQ.setRegisterdDate(rs.getDate("Registration_Date"));
                  mbrQ.setRegisteredCategory(rs.getString("Catergory"));
                  mbrQ.setRenewalDate(rs.getDate("Renewed_Date"));
                  System.out.println("\tMember_ID = "+ mbrQ.getMemberId()
                                                  +"\tUniversity_ID=" + mbrQ.getUniversityId()
                                                  + "\tName=" + mbrQ.getName()
                                                  + "\tRegistration_Date="+  mbrQ.getRegisteredCategory()
                                                  + "\tCatergory="+ mbrQ.getRegisteredCategory()
                                                  +"\tRenewed_Date"+mbrQ.getRenewalDate());
          }
 
          System.out.println("\n User 'dbadmin' accessed database succesfully");
          System.out.println("\n God Thanks well come");
          conn.close();
          System.out.println("Disconnected from database");
        } catch (Exception e) {
          e.printStackTrace();
        }
    }
 
}

[ronverdonk]

This looks more like a Java problem and you'll probably have a better chance on help there.

So I will copy the thread to the Java forum and leave it here, hoping that someone can help you out.

Ronald

[BigDaddyLH]

I think your problem is that you are writing Java code inside strings. As a simple example:

String s = "z = 3*x+y";

Getting this to execute as Java code is not a simple matter.

Your solution is actually almost a standard response when people get confused about embedding SQL in Java: don't use java.sql.Statement, use java.sql.PreparedStatement. I go so far as to suggest that you never use just Statement. Take the Sun tutorial on JDBC and carefully note the section on PreparedStatement:

http://java.sun.com/docs/books/tutorial/jdbc/index.html

[Joshepmichel]

Thank you BiGDAD, I thing you suggests some thing related to my question, I'll try to see the link.

I think your problem is that you are writing Java code inside strings. As a simple example:

String s = "z = 3*x+y";

Getting this to execute as Java code is not a simple matter.

Your solution is actually almost a standard response when people get confused about embedding SQL in Java: don't use java.sql.Statement, use java.sql.PreparedStatement. I go so far as to suggest that you never use just Statement. Take the Sun tutorial on JDBC and carefully note the section on PreparedStatement:

http://java.sun.com/docs/books/tutorial/jdbc/index.html