JavaWeb程序对SQLserver数据库的增删改查操作

声明:学了几天终于将增删改查的操作掌握了，也发现了一些问题，所以总结一下.

重点:操作数据库主要用的是SQL语句跟其他无关.

一:前提知识:PreparedStatement

PreperedStatement是Statement的子类，它的实例对象可以通过调用Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避免SQL注入的问题。
　　Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

举例:

PreperedStatement mi = Connection.preparedStatement();
try{
i = mi.executeUpdate();
 }catch(Exception e){
 if(i != 1)
{
 System.out.println("修改不成功");
 }
 }

二:实际例子:

1: 编写index.jsp文件通过JSTL中的<c:redirect>标签指向servlet

package com.ll;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.Studentdao2;

/**
 * Servlet implementation class GoodsServlet
 */
@WebServlet("/GoodsServlet")
public class GoodsServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GoodsServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        String action = request.getParameter("action");
        if(action.equals("zongjin")){
            
            try {
                query(request,response);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
        
        
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }
    
    
    public void query(HttpServletRequest request, HttpServletResponse response) throws Exception{
        Studentdao2 conn = new Studentdao2();
        String sql = "Select *from Student";
    
         /*
        //调用函数去向数据库中添加数据
          String addsql = "于海-19-软件1501-男";
          conn.addsql(addsql);
         */ 
        
        
        /*
         //删除数据库中的数据
        String strdelete = "Delete from Student Where 姓名 = '于海'";
        conn.deletesql(strdelete);
        */
        
        /*
         //修改数据库中的数据
        String update = "Update Student set 姓名='宗进' Where 姓名 = '小明' ";
        conn.updateSql(update);
        */
        
        
        ResultSet rs =conn.executeQuery1(sql);//调用自制函数来接受查询的结果
        List list = new ArrayList();
        while(rs.next())
        {
            Student f = new Student();
            //equals比较的结果为false是因为从数据库中返回的字符串为"宗进          "    
            f.setBanji(rs.getString("班级"));
            f.setName(rs.getString("姓名"));
            f.setSex(rs.getString("性别"));
            f.setTime(rs.getString("年龄"));        
            
            list.add(f);
            
        }
        
        request.setAttribute("list", list);
        request.getRequestDispatcher("sql2.jsp").forward(request, response);
        
        
        
        
    }
    public String cat(String str){
        String[] arr = str.split(" ");
        String s = arr[0];
        return s;
        
    }

}

3.Servlet的web.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:jsp="http://java.sun.com/xml/ns/javaee/jsp" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>JSTL</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <jsp-config>
    <taglib>
      <taglib-uri>http://java.sun.com/jstl/fmt</taglib-uri>
      <taglib-location>/WEB-INF/tld/fmt.tld</taglib-location>
    </taglib>
    <taglib>
      <taglib-uri>http://java.sun.com/jstl/core</taglib-uri>
      <taglib-location>/WEB-INF/tld/c.tld</taglib-location>
    </taglib>
    <taglib>
      <taglib-uri>http://java.sun.com/jstl/sql</taglib-uri>
      <taglib-location>/WEB-INF/tld/sql.tld</taglib-location>
    </taglib>
    <taglib>
      <taglib-uri>http://java.sun.com/jstl/x</taglib-uri>
      <taglib-location>/WEB-INF/tld/x.tld</taglib-location>
    </taglib>
  </jsp-config>
  
  <servlet>
      <servlet-name>GoodsServlet</servlet-name>
      <servlet-class>com.ll.GoodsServlet</servlet-class>
  </servlet>
  
  <servlet-mapping>
       <servlet-name>GoodsServlet</servlet-name>
       <url-pattern>/com.ll.GoodsServlet</url-pattern>
  </servlet-mapping>
  
  
</web-app>

4.创建一个用于连接数据库并且处理的类

package dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Studentdao2 {
     public Connection conn = null;
     public String url = null;
     public Statement stmt = null;
     public ResultSet rs = null;
     public static String propFilename = "Studentdao2.properties";
     private static Properties prop = new Properties();
     private static String  dbClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
     private static String dbUrl = "jdbc:sqlserver://localhost:1433;DatabaseName=StudentManagement";
     private static String  addSql = "insert into Student values(?,?,?,?)";
     private static String updateSql = "update Student set 姓名=?,年龄=?,班级=?,性别=?";//用于整体修改数据库中的值
     public Studentdao2(){
         try{
            // InputStream in = getClass().getResourceAsStream(propFilename);
            // prop.load(in);
            // dbClassName = prop.getProperty("DB_URL",dbUrl);
            // dbUrl = prop.getProperty("DB_URL", dbUrl);
             Class.forName(dbClassName);
             conn = DriverManager.getConnection(dbUrl, "sa", "zongjin123");
             if(conn != null)
             {
                 System.out.println("连接成功");
             }
             else{
                 System.out.println("连接失败");
             }
             stmt = conn.createStatement();
             
         }catch(Exception e){
             e.printStackTrace();
         }
         
     }
    public ResultSet executeQuery1(String sql) throws Exception {
        // TODO Auto-generated method stub
        this.rs = this.stmt.executeQuery(sql);
        return this.rs;
    }
    public void addsql(String sql) throws Exception{
        PreparedStatement add = conn.prepareStatement(addSql);
        String[] shuju = sql.split("-");
        System.out.println(shuju[0]);
        add.setString(1, shuju[0]);
        add.setString(2,shuju[1]);
        add.setString(3,shuju[2]);
        add.setString(4, shuju[3]);
        add.executeUpdate();
        add.close();    
    }
    public void deletesql(String sql) throws Exception{
        int i = 0;
        PreparedStatement delete = conn.prepareStatement(sql);
        try{
           i = delete.executeUpdate();
        }catch(Exception e){
            if(i != 1)
            {
                System.out.println("删除失败");
            }
        }
        delete.close();
    }
    public void updateSql(String sql)throws Exception
    {
        PreparedStatement update = conn.prepareStatement(sql);
        //用于整体修改数据库中的值，但是一般直接用sq语句来进行操作数据库
        //update.setString(2, str);
        //String[] shuju = sql.split("-");
        //System.out.println(shuju[0]);
        //update.setString(1, shuju[0]);
        //update.setString(2,shuju[1]);
        //update.setString(3,shuju[2]);
        //update.setString(4, shuju[3]);
        update.executeUpdate();
        update.close();
    }
    
}

5.编写用于存储数据的student类

package com.ll;

public class Student {
    private String name;
    private String time;
    private String banji;
    private String sex;
    public String getBanji() {
        return banji;
    }
    public void setBanji(String banji) {
        this.banji = banji;
    }
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getTime() {
        return time;
    }
    public void setTime(String time) {
        this.time = time;
    }
    
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    

}

6运行index.jsp文件来运行