Results 1 to 19 of 19
  1. #1
    biradarashish is offline Member
    Join Date
    Mar 2010
    Posts
    2
    Rep Power
    0

    Post I am not able to make entry into database

    Java Code:
    package candiDAO;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import genericDAO.GenericDAO;
    import candidate.Candidate;
    
    
    public class CandidateDAO extends GenericDAO{
    
    public void addCandidate(Candidate candidate)
    {
    try
    {
    
    String sql = "INSERT INTO candidate VALUES (";
    sql = candidate.getEnrollmentNumber() ",";
    sql = candidate.getDateOfEnrollment() ",";
    sql = candidate.getContactNumber() ",";
    sql = candidate.getGender() ",";
    sql = candidate.getMailingAddress() ",";
    sql = candidate.getEmailId() ",";
    sql = candidate.getClassOfVehicle() ",";
    sql = candidate.getTypeOfVehicle() ",";
    sql = candidate.getDateOfBirth() ",";
    sql = candidate.getBatchTime() ")";
    
    //executeQuery(sql);
    executeUpdate(sql);
    
    }catch(SQLException e){
    e.printStackTrace();
    }
    }
    }
    
    
    
    
    
    package candidate;
    
    public class Candidate {
    private int enrollmentNumber;	
    private String dateOfEnrollment;
    private String name;
    private int contactNumber;
    private String gender;
    private String mailingAddress;
    private String emailId;
    private String classOfVehicle;
    private String typeOfVehicle;
    private String dateOfBirth;
    private String batchTime;
    
    
    public Candidate()
    {
    
    }
    
    public Candidate(int enrollmentNumber,String dateOfEnrollment,String name,int contactNumber,String gender,String mailingAddress,String emailId,String classOfVehicle,String typeOfVehicle,String dateOfBirth,String batchTime){
    setEnrollmentNumber(enrollmentNumber);
    setDateOfEnrollment(dateOfEnrollment);
    setName(name);
    setContactNumber(contactNumber);
    setGender(gender);
    setMailingAddress(mailingAddress);
    setEmailId(emailId);
    setClassOfVehicle(classOfVehicle);
    setTypeOfVehicle(typeOfVehicle);
    setDateOfBirth(dateOfBirth);
    setBatchTime(batchTime);
    }
    
    public int getEnrollmentNumber(){
    return enrollmentNumber;
    }
    
    public void setEnrollmentNumber(int enrollmentNumber){
    this.enrollmentNumber=enrollmentNumber;
    }
    
    public String getDateOfEnrollment(){
    return dateOfEnrollment;
    }
    public void setDateOfEnrollment(String dateOfEnrollment){
    this.dateOfEnrollment=dateOfEnrollment;
    }
    
    public String getName()
    {
    return name;
    }
    public void setName(String name)
    {
    this.name=name;
    }
    
    public int getContactNumber()
    {
    return contactNumber;
    }
    
    public void setContactNumber(int contactNumber){
    this.contactNumber=contactNumber;
    }
    
    public String getGender()
    {
    return gender;
    }
    public void setGender(String gender)
    {
    this.gender=gender;
    }
    
    public String getMailingAddress()
    {
    return mailingAddress;
    }
    public void setMailingAddress(String mailingAddress)
    {
    this.mailingAddress=mailingAddress;
    }
    
    public String getEmailId()
    {
    return emailId;
    }
    public void setEmailId(String emailId)
    {
    this.emailId=emailId;
    }
    
    public String getDateOfBirth()
    {
    return dateOfBirth;
    
    }
    public void setDateOfBirth(String dateOfBirth){
    this.dateOfBirth=dateOfBirth;
    }
    
    public String getClassOfVehicle()
    {
    return classOfVehicle;
    
    }
    public void setClassOfVehicle(String classOfVehicle){
    this.classOfVehicle=classOfVehicle;
    }
    
    public String getTypeOfVehicle()
    {
    return typeOfVehicle;
    
    }
    public void setTypeOfVehicle(String typeOfVehicle){
    this.typeOfVehicle=typeOfVehicle;
    }
    
    public String getBatchTime()
    {
    return batchTime;
    
    }
    public void setBatchTime(String batchTime){
    this.batchTime=batchTime;
    }
    }
    
    
    
    package candiDAOTest;
    
    import candidate.Candidate;
    import candiDAO.CandidateDAO;
    
    public class CandidateDAOTest {
    
    public static void main(String args[])
    {
    Candidate newCandidate = new Candidate();
    CandidateDAO dao = new CandidateDAO();
    newCandidate.setEnrollmentNumber(123);
    newCandidate.setDateOfEnrollment("2010-20-3");
    newCandidate.setName("Ashish");
    newCandidate.setContactNumber(9860);
    newCandidate.setGender("male");
    newCandidate.setMailingAddress("karve nagar pune");
    newCandidate.setEmailId("biradarashish@gmail.com");
    newCandidate.setClassOfVehicle("motor with gear");
    newCandidate.setTypeOfVehicle("small");
    newCandidate.setDateOfBirth("1987-02-03");
    newCandidate.setBatchTime("8 am");
    
    dao.addCandidate(newCandidate);
    }
    }
    
    
    package genericDAO;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import com.mysql.jdbc.PreparedStatement;
    
    
    public abstract class GenericDAO {
    
    
    		private static Connection getConnection() throws Exception {
    				Class.forName("com.mysql.jdbc.Driver").newInstance();
    				Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/saimotor","root","ram");
    				System.out.println("connected");
    				return conn;
    		}
    
    	/*	public ResultSet executeQuery(String query) throws SQLException{
    
    				Connection con = null;
    				Statement statement = null;
    				ResultSet result = null;
                    int row=0;
    				try {
    
    					 con = getConnection();
    					 statement =con.createStatement();
    
    					 row = statement.executeUpdate(query);
    					
    
    				} catch(Exception e){
    					e.printStackTrace();
    				} finally {
    					close(statement, con);
    				}
    				return result;
    		}*/
    
    		 public static final int executeUpdate(String sql) 
    	        throws SQLException {
    	        
    	        Connection con=null ;
    	        PreparedStatement stmt=null;
    	        //ResultSet results = null;
    	        try {
    	        	 con=getConnection();
    	             stmt = (PreparedStatement) con.prepareStatement(sql);
    	            con.setAutoCommit(false);
    	           /* int index = 1;
    	            for (Object current : params) {
    	                stmt.setObject(index, current);  // Note you should check for nulls here
    	                index++;
    	            }
    	            int rowsUpdated = stmt.executeUpdate();
    	            con.commit();
    	           // return rowsUpdated;  */
    	        }
    	        catch (SQLException e) {
    	            e.printStackTrace();
    	        } catch (Exception e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    	        finally {
    	           // close(results);
    	            close(stmt,con);
    	           // close(conn);
    	        }
    			return 0;
    	    }
    
    		public static void close(Statement statement, Connection con){
    			try{
    					statement.close();
    					con.close();
    				}catch(SQLException e){
    						e.printStackTrace();
    				}
    		}
    }
    This is my code.. plz help me
    Last edited by Eranga; 03-26-2010 at 01:41 PM. Reason: added code tags

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,371
    Blog Entries
    1
    Rep Power
    20

    Default

    Did you get any error messages, or anything related. If so send it here. I don't think anyone wants to run your code fix the errors very beginning. :)

  3. #3
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by Eranga View Post
    Did you get any error messages, or anything related. If so send it here.

    right. perhaps biradarashish needs only a GRANT INSERT to solve the problem.

  4. #4
    biradarashish is offline Member
    Join Date
    Mar 2010
    Posts
    2
    Rep Power
    0

    Default error

    com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nagar pune,biradarashish,motor with gear,small,1987-02-03,8 am)' at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:2985)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:16 31)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java: 3277)
    at com.mysql.jdbc.Statement.executeUpdate(Statement.j ava:1402)
    at com.mysql.jdbc.Statement.executeUpdate(Statement.j ava:1317)
    at genericDAO.GenericDAO.executeUpdate(GenericDAO.jav a:56)
    at candiDAO.CandidateDAO.addCandidate(CandidateDAO.ja va:33)
    at candiDAOTest.CandidateDAOTest.main(CandidateDAOTes t.java:24)

  5. #5
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by biradarashish View Post
    com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: You have an error in your SQL syntax;

    can you post the original sql-statement you send to the database?

  6. #6
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    hallo biradarashish

    after have replaced the following code

    Java Code:
    String sql = "INSERT INTO candidate VALUES (";
    sql = candidate.getEnrollmentNumber() ",";
    sql = candidate.getDateOfEnrollment() ",";
    sql = candidate.getContactNumber() ",";
    sql = candidate.getGender() ",";
    sql = candidate.getMailingAddress() ",";
    sql = candidate.getEmailId() ",";
    sql = candidate.getClassOfVehicle() ",";
    sql = candidate.getTypeOfVehicle() ",";
    sql = candidate.getDateOfBirth() ",";
    sql = candidate.getBatchTime() ")";
    with this one

    Java Code:
    	String sql = "INSERT INTO candidate VALUES (" +
    	candidate.getEnrollmentNumber() + "," +
    	candidate.getDateOfEnrollment() + "," +
    	candidate.getContactNumber() + "," +
    	candidate.getGender() + "," +
    	candidate.getMailingAddress() + "," +
    	candidate.getEmailId() + "," +
    	candidate.getClassOfVehicle() + "," +
    	candidate.getTypeOfVehicle() + "," +
    	candidate.getDateOfBirth() + "," +
    	candidate.getBatchTime() + ")";
    i got the output for sql

    Java Code:
    INSERT INTO candidate VALUES 
    (123,2010-20-3,9860,male,karve nagar pune,biradarashish@gmail.com,motor with gear,small,1987-02-03,8 am)

    i don't have my mysql-manual here but verify

    1) that string values and calender-data must be single-quoted in the in the insert-statement
    2) if the table name should be full qualified, means with schema.tablename
    3) which format needs calender-data-values in insert-statements.
    4) it's much safer to use the syntax: INSERT INTO SCHEMA.TABLE (ATTRIB1, ATTRIB2 and so on ) VALUES (value1, value2 and so on)
    5) make sure, the candidate member you define as numeric in java are also defined as numeric on your database table. otherwise cast the java format to the needed database attribut format.

    hope this helps.
    Last edited by j2me64; 03-26-2010 at 03:54 PM.

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    In fact, use a PreparedStatement.
    String concatentation for sql statements is (usually) the work of the devil...

  8. #8
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by Tolls View Post
    In fact, use a PreparedStatement.
    String concatentation for sql statements is (usually) the work of the devil...

    yes, that's why you should send the PreparedStatement to the console and verify it very carefully before your send it to the database.

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    If you want to know if your SQL (in general) is OK, then log the String.

  10. #10
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by Tolls View Post
    If you want to know if your SQL (in general) is OK, then log the String.

    this is a good idea for other purposes, but logging the string don't give you any information if the sql is ok. or do i misunderstand something?

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    You can't log the PreparedStatement. Most of them will only give you the base string anyway.

    I mean, what do you mean by "send the PS to the console"? I assumed that meant println(ps).

    The SQL String you give to to make the PreparedStatement is just as useful.
    If you want to know the values supplied then print them out separately.

  12. #12
    gcalvin is offline Senior Member
    Join Date
    Mar 2010
    Posts
    953
    Rep Power
    5

    Default

    Everybody has been giving very good advice. I just wanted to point out that OP has a lot of
    Java Code:
        sql = ...
    where I am pretty sure he means
    Java Code:
        sql += ...
    Of course, fixing that only causes other problems, because as Tolls points out,
    String concatentation for sql statements is (usually) the work of the devil...
    -Gary-

  13. #13
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by gcalvin View Post
    Java Code:
        sql += ...
    Of course, fixing that only causes other problems

    what do you mean with "causes other problems"?

  14. #14
    gcalvin is offline Senior Member
    Join Date
    Mar 2010
    Posts
    953
    Rep Power
    5

    Default

    Quote Originally Posted by j2me64 View Post
    what do you mean with "causes other problems"?
    Just that building SQL statements with String concatenation is generally a bad idea, as Tolls had already mentioned. It tends to leave code vulnerable to SQL injection attacks, among other weaknesses.

    -Gary-

  15. #15
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by gcalvin View Post
    Just that building SQL statements with String concatenation is generally a bad idea, as Tolls had already mentioned.

    sorry, but i don't agree. building sql with strings you need 10% of brain and the rest of the 90% is copy/paste. if this approach is weak, what do you suggest to insert data in a database-table?

  16. #16
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    PreparedStatement, and binding the variables in (ie ps.setString() etc).

    Otherwise you are prone to the aforementioned SQL injection, also prone to errors involving escaping text and quotes...and finally, to get technical, performance issues for frequently run queries which will overload your SQL pool on the db eventually through fragmentation.

  17. #17
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    6

    Default

    Quote Originally Posted by Tolls View Post
    and finally, to get technical, performance issues for frequently run queries which will overload your SQL pool on the db eventually through fragmentation.

    maybe. but what do you suggest? you can't tell your users to avoid using sql because "eventually through fragmentation".

    i assume, that here we are not talking about a database with thousends of users but about simple insert statements running from one user/connection.

  18. #18
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    Doesn't matter on the sort of system.
    Using Statements like that is a good way to get into a bad habit.

    If you have variables in a SQL statement then those variables should be bound (with the odd exception), and in JDBC you do that with PreparedStatements, or CallableStatements.

  19. #19
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by j2me64 View Post
    maybe. but what do you suggest? you can't tell your users to avoid using sql because "eventually through fragmentation".

    i assume, that here we are not talking about a database with thousends of users but about simple insert statements running from one user/connection.
    Man, you need to learn some more Java before you start making some of these statements. Noone was suggesting to not use SQL. There was debate about Statement vs. PreparedStatement (these are Java classes that usually correspond to similar session/cursor types in the DB, but are not required to), and there was some critique on how the Strings that were used for the SQL were built, but not even the slightest indication that something other than SQL should be used.

Similar Threads

  1. Entry in table
    By Harsh_Verma in forum New To Java
    Replies: 2
    Last Post: 07-01-2009, 03:07 PM
  2. Make money from home, Home Typing Data Entry Partnerships
    By arturmoniswork in forum Reviews / Advertising
    Replies: 0
    Last Post: 12-30-2008, 06:55 AM
  3. make my database applicatin transportable
    By Farzaneh in forum JDBC
    Replies: 0
    Last Post: 08-25-2008, 10:32 AM
  4. Need Help On Entry Validation
    By obdi in forum New To Java
    Replies: 3
    Last Post: 07-25-2008, 07:39 AM
  5. How to prevent duplicate username entry in database?
    By anki1234 in forum JavaServer Pages (JSP) and JSTL
    Replies: 4
    Last Post: 01-09-2008, 09:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •