I am trying to build registration system with java servlet. And insert data into a mySQL database. But I get a syntax error. I just finished reading Wiley mySQL and Java developers guide book.
And I am kinda new to servlet programming, so if there is easy way to do things I do please tell me.
package com.app.base;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.app.pojo.*;
public class RegisterServlet extends HttpServlet{
MySqlDB mysql;
@Override
public void init() throws ServletException {
// TODO Auto-generated method stub
mysql = new MySqlDB();
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
PrintWriter out = null;
//Connection connection = null;
//Statement statement;
//ResultSet rs;
resp.setContentType("text/html");
out = resp.getWriter();
try{
mysql.createConnection();
}catch(Error e){
out.write("Couldn't connect to mysql");
}
String fname = req.getParameter("fname");
String lname = req.getParameter("lname");
String email = req.getParameter("email");
String password = req.getParameter("password");
String city = req.getParameter("city");
String country = req.getParameter("country");
if(fname == null){
String destination = "signup.jsp?error=Complete All Fields";
RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
rd.forward(req, resp);
}else if(lname == null){
String destination = "signup.jsp?error=Complete All Fields";
RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
rd.forward(req, resp);
}else if(email == null){
String destination = "signup.jsp?error=Complete All Fields";
RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
rd.forward(req, resp);
}else if(password == null){
String destination = "signup.jsp?error=Complete All Fields";
RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
rd.forward(req, resp);
}else if(city == null){
String destination = "signup.jsp?error=Complete All Fields";
RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
rd.forward(req, resp);
}else if(country == null){
String destination = "signup.jsp?error=Complete All Fields";
RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
rd.forward(req, resp);
}else{
String sql = "INSERT INTO main.users(first_name, last_name, email, password, city, country, registered_time) VALUES("
+ fname +", "+ lname + ", "+ email +", " + password +", " + city +"," + country + ",Now());";
int answer = mysql.insertSQL(sql);
if(answer == 1){
resp.sendRedirect( "index.jsp?registered=true");
//String destination = "index.jsp?registered=true";
//RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
//rd.forward(req, resp);
}
}
}
}
And this is the MySql Class to connect.
package com.app.pojo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySqlDB{
private static String username = "root", password = "root";
public Connection createConnection(){
Connection connection = null;
try{
//Load the JDBC driver
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306,/main", username, password);
//Create a connection to the database
}catch(SQLException ex){
System.out.println(ex);
}catch(ClassNotFoundException e){
System.out.println(e);
}
return connection;
}
public void runSqlStatement(String sql){
try{
Statement statement = createConnection().createStatement();
//statement executeQuery(Query)
boolean rs = statement.execute(sql);
}catch(SQLException ex){
System.out.println(ex);
}
}
public ResultSet executeSQL(String sql){
Statement statement = null;
ResultSet rs = null;
try{
statement = createConnection().createStatement();
rs = statement.executeQuery(sql);
/*while(rs.next()){
System.out.println(rs.getString(1));
}*/
// rs.close();
// statement.close();
}catch (SQLException e) {
System.out.println(e);
}
return rs;
}
public int insertSQL(String sql){
int rs;
try{
Statement statement = createConnection().createStatement();
rs = statement.executeUpdate(sql);
return rs;
}catch(SQLException ex){
System.out.println(ex);
return 0;
}
}
}
This is the tomcat console
INFO: Reloading Context with name [/Map] has started
Apr 21, 2012 12:59:14 AM org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
SEVERE: The web application [/Map] registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
Apr 21, 2012 12:59:17 AM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/Map] is completed
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '********,Colombo,Sri Lanka,Now())' at line 1
