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); }