I am not able to make entry into database

[biradarashish]

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

[Eranga]

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. :)

[j2me64]

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.

[biradarashish]

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)

[j2me64]

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?

[j2me64]

hallo biradarashish

after have replaced the following 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

	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

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.

[Tolls]

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

[j2me64]

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.

[Tolls]

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

[j2me64]

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?

[Tolls]

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.

[gcalvin]

Everybody has been giving very good advice. I just wanted to point out that OP has a lot of

    sql = ...

where I am pretty sure he means

    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-

[j2me64]

    sql += ...

Of course, fixing that only causes other problems

what do you mean with "causes other problems"?

[gcalvin]

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-

[j2me64]

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?

[Tolls]

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.

[j2me64]

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.

[Tolls]

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.

[masijade]

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.