public static int insert(User user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"INSERT INTO customer (first_name, last_name, phone_number, address, city, state, zip_code) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getPhone());
ps.setString(4, user.getAddress());
ps.setString(5, user.getCity());
ps.setString(6, user.getState());
ps.setString(7, user.getZipCode());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static int update(User user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"UPDATE customer SET "
+ "first_name = ?, "
+ "last_name = ?, "
+ "phone_number = ?, "
+ "address = ?, "
+ "city = ?, "
+ "state = ?, "
+ "zip_code = ? ";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getPhone());
ps.setString(4, user.getAddress());
ps.setString(5, user.getCity());
ps.setString(6, user.getState());
ps.setString(7, user.getZipCode());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// get a connection
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
String sqlStatement = request.getParameter("sqlStatement");
String sqlResult = "";
try {
// create a statement
Statement statement = connection.createStatement();
// parse the SQL string
sqlStatement = sqlStatement.trim();
if (sqlStatement.length() >= 6) {
String sqlType = sqlStatement.substring(0, 6);
if (sqlType.equalsIgnoreCase("select")) {
// create the HTML for the result set
ResultSet resultSet = statement.executeQuery(sqlStatement);
sqlResult = SQLUtil.getHtmlTable(resultSet);
resultSet.close();
} else {
int i = statement.executeUpdate(sqlStatement);
if (i == 0) {
sqlResult = "<p>The statement executed successfully.</p>";
} else { // an INSERT, UPDATE, or DELETE statement
sqlResult = "<p>The statement executed successfully.<br>" + i + " row(s) affected.</p>";
}
}
}
statement.close();
connection.close();
} catch (SQLException e) {
sqlResult = "<p>Error executing the SQL statement: <br>" + e.getMessage() + "</p>";
} finally {
pool.freeConnection(connection);
}
HttpSession session = request.getSession();
session.setAttribute("sqlResult", sqlResult);
session.setAttribute("sqlStatement", sqlStatement);
String url = "/index.jsp";
getServletContext().getRequestDispatcher(url).forward(request, response);
}
