JSPservlet删除，和数据库交互
1、import java.io.IOException;import java.io.PrintWriter;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;
import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;
public class DeleteEmpServlet extends HttpServlet{ @Override
2、protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //解决中文乱码问题 request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); //连接数据库 Connection con=null; String sql=null;
3、 PreparedStatement pstm=null; int id=Integer.parseInt(request.getParameter("id")); try {
数据库的链接，DBUtil是之前建立的链接数据库的类，这里直接使用
con=DBUtil.getConnection();
使用PreparedStatement可以预编译sql语言，减少频繁的访问数据库，使性能降低
4、 sql="delete from t_emp where id=?"; pstm=con.prepareStatement(sql); pstm.setInt(1, id); pstm.executeUpdate(); //重定向 response.sendRedirect("listEmp"); } catch (SQLException e) { System.out.println("连接异常"); }finally { DBUtil.closeConnection(con); } }}
5、public class DBUtil { private static String driver; private static String url; private static String username; private static String password; private static String initialSize; private static String maxActive; private static String maxIdle; private static String minIdle; private static String maxWait; private static BasicDataSource dataSource=null; private static Properties db=null; public static void init(){ db=new Properties(); try { db.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties")); driver=db.getProperty("driver"); url=db.getProperty("url"); username=db.getProperty("username"); password=db.getProperty("password"); initialSize=db.getProperty("initialSize"); maxActive=db.getProperty("maxActive"); maxIdle=db.getProperty("maxIdle"); minIdle=db.getProperty("minIdle"); maxActive=db.getProperty("maxWait"); //通过DataSource加载相关参数值 dataSource=new BasicDataSource(); dataSource.setDriverClassName(driver); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); if (initialSize!=null) { dataSource.setInitialSize(Integer.parseInt(initialSize)); } if (maxActive!=null) { dataSource.setMaxActive(Integer.parseInt(maxActive)); } if (maxIdle!=null) { dataSource.setMaxIdle(Integer.parseInt(maxIdle)); } if (minIdle!=null) { dataSource.setMinIdle(Integer.parseInt(minIdle)); } if (maxWait!=null) { dataSource.setMaxWait(Integer.parseInt(maxWait)); } } catch (IOException e) { System.out.println("读取文件失败"); throw new RuntimeException(e); }}
6、 //建立连接数据库的方法 public static Connection getConnection() throws SQLException{ if (dataSource==null) { init(); } Connection con=null; if (dataSource!=null) { con=dataSource.getConnection(); } return con; } //关闭数据库 public static void closeConnection(Connection con){ try { con.close(); } catch (SQLException e) { System.out.println("关闭连接失败"); } }}
DBUtil文件，这是一个静态类，我们可以在需要使用的时候直接调用里面的方法，
