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

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

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

 

一:前提知识:PreparedStatement

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

举例: 

1 PreperedStatement mi = Connection.preparedStatement();
2 try{
3 i = mi.executeUpdate();
4  }catch(Exception e){
5  if(i != 1)
6 {
7  System.out.println("修改不成功");
8  }
9  }

二:实际例子:

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

  1 package com.ll;
  2 
  3 import java.io.IOException;
  4 import java.sql.*;
  5 import java.util.ArrayList;
  6 import java.util.List;
  7 
  8 import javax.servlet.ServletException;
  9 import javax.servlet.annotation.WebServlet;
 10 import javax.servlet.http.HttpServlet;
 11 import javax.servlet.http.HttpServletRequest;
 12 import javax.servlet.http.HttpServletResponse;
 13 
 14 import dao.Studentdao2;
 15 
 16 /**
 17  * Servlet implementation class GoodsServlet
 18  */
 19 @WebServlet("/GoodsServlet")
 20 public class GoodsServlet extends HttpServlet {
 21     private static final long serialVersionUID = 1L;
 22        
 23     /**
 24      * @see HttpServlet#HttpServlet()
 25      */
 26     public GoodsServlet() {
 27         super();
 28         // TODO Auto-generated constructor stub
 29     }
 30 
 31     /**
 32      * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
 33      */
 34     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 35         // TODO Auto-generated method stub
 36         String action = request.getParameter("action");
 37         if(action.equals("zongjin")){
 38             
 39             try {
 40                 query(request,response);
 41             } catch (Exception e) {
 42                 // TODO Auto-generated catch block
 43                 e.printStackTrace();
 44             }
 45         }
 46         
 47         
 48         
 49     }
 50 
 51     /**
 52      * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 53      */
 54     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 55         // TODO Auto-generated method stub
 56     }
 57     
 58     
 59     public void query(HttpServletRequest request, HttpServletResponse response) throws Exception{
 60         Studentdao2 conn = new Studentdao2();
 61         String sql = "Select *from Student";
 62     
 63          /*
 64         //调用函数去向数据库中添加数据
 65           String addsql = "于海-19-软件1501-男";
 66           conn.addsql(addsql);
 67          */ 
 68         
 69         
 70         /*
 71          //删除数据库中的数据
 72         String strdelete = "Delete from Student Where 姓名 = '于海'";
 73         conn.deletesql(strdelete);
 74         */
 75         
 76         /*
 77          //修改数据库中的数据
 78         String update = "Update Student set 姓名='宗进' Where 姓名 = '小明' ";
 79         conn.updateSql(update);
 80         */
 81         
 82         
 83         ResultSet rs =conn.executeQuery1(sql);//调用自制函数来接受查询的结果
 84         List list = new ArrayList();
 85         while(rs.next())
 86         {
 87             Student f = new Student();
 88             //equals比较的结果为false是因为从数据库中返回的字符串为"宗进          "    
 89             f.setBanji(rs.getString("班级"));
 90             f.setName(rs.getString("姓名"));
 91             f.setSex(rs.getString("性别"));
 92             f.setTime(rs.getString("年龄"));        
 93             
 94             list.add(f);
 95             
 96         }
 97         
 98         request.setAttribute("list", list);
 99         request.getRequestDispatcher("sql2.jsp").forward(request, response);
100         
101         
102         
103         
104     }
105     public String cat(String str){
106         String[] arr = str.split(" ");
107         String s = arr[0];
108         return s;
109         
110     }
111 
112 }

3.Servlet的web.xml文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <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">
 3   <display-name>JSTL</display-name>
 4   <welcome-file-list>
 5     <welcome-file>index.html</welcome-file>
 6     <welcome-file>index.htm</welcome-file>
 7     <welcome-file>index.jsp</welcome-file>
 8     <welcome-file>default.html</welcome-file>
 9     <welcome-file>default.htm</welcome-file>
10     <welcome-file>default.jsp</welcome-file>
11   </welcome-file-list>
12   <jsp-config>
13     <taglib>
14       <taglib-uri>http://java.sun.com/jstl/fmt</taglib-uri>
15       <taglib-location>/WEB-INF/tld/fmt.tld</taglib-location>
16     </taglib>
17     <taglib>
18       <taglib-uri>http://java.sun.com/jstl/core</taglib-uri>
19       <taglib-location>/WEB-INF/tld/c.tld</taglib-location>
20     </taglib>
21     <taglib>
22       <taglib-uri>http://java.sun.com/jstl/sql</taglib-uri>
23       <taglib-location>/WEB-INF/tld/sql.tld</taglib-location>
24     </taglib>
25     <taglib>
26       <taglib-uri>http://java.sun.com/jstl/x</taglib-uri>
27       <taglib-location>/WEB-INF/tld/x.tld</taglib-location>
28     </taglib>
29   </jsp-config>
30   
31   <servlet>
32       <servlet-name>GoodsServlet</servlet-name>
33       <servlet-class>com.ll.GoodsServlet</servlet-class>
34   </servlet>
35   
36   <servlet-mapping>
37        <servlet-name>GoodsServlet</servlet-name>
38        <url-pattern>/com.ll.GoodsServlet</url-pattern>
39   </servlet-mapping>
40   
47   
48 </web-app>

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

 1 package dao;
 2 
 3 import java.io.InputStream;
 4 import java.sql.Connection;
 5 import java.sql.DriverManager;
 6 import java.sql.PreparedStatement;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 import java.sql.Statement;
10 import java.util.Properties;
11 
12 public class Studentdao2 {
13      public Connection conn = null;
14      public String url = null;
15      public Statement stmt = null;
16      public ResultSet rs = null;
17      public static String propFilename = "Studentdao2.properties";
18      private static Properties prop = new Properties();
19      private static String  dbClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
20      private static String dbUrl = "jdbc:sqlserver://localhost:1433;DatabaseName=StudentManagement";
21      private static String  addSql = "insert into Student values(?,?,?,?)";
22      private static String updateSql = "update Student set 姓名=?,年龄=?,班级=?,性别=?";//用于整体修改数据库中的值
23      public Studentdao2(){
24          try{
25             // InputStream in = getClass().getResourceAsStream(propFilename);
26             // prop.load(in);
27             // dbClassName = prop.getProperty("DB_URL",dbUrl);
28             // dbUrl = prop.getProperty("DB_URL", dbUrl);
29              Class.forName(dbClassName);
30              conn = DriverManager.getConnection(dbUrl, "sa", "zongjin123");
31              if(conn != null)
32              {
33                  System.out.println("连接成功");
34              }
35              else{
36                  System.out.println("连接失败");
37              }
38              stmt = conn.createStatement();
39              
40          }catch(Exception e){
41              e.printStackTrace();
42          }
43          
44      }
45     public ResultSet executeQuery1(String sql) throws Exception {
46         // TODO Auto-generated method stub
47         this.rs = this.stmt.executeQuery(sql);
48         return this.rs;
49     }
50     public void addsql(String sql) throws Exception{
51         PreparedStatement add = conn.prepareStatement(addSql);
52         String[] shuju = sql.split("-");
53         System.out.println(shuju[0]);
54         add.setString(1, shuju[0]);
55         add.setString(2,shuju[1]);
56         add.setString(3,shuju[2]);
57         add.setString(4, shuju[3]);
58         add.executeUpdate();
59         add.close();    
60     }
61     public void deletesql(String sql) throws Exception{
62         int i = 0;
63         PreparedStatement delete = conn.prepareStatement(sql);
64         try{
65            i = delete.executeUpdate();
66         }catch(Exception e){
67             if(i != 1)
68             {
69                 System.out.println("删除失败");
70             }
71         }
72         delete.close();
73     }
74     public void updateSql(String sql)throws Exception
75     {
76         PreparedStatement update = conn.prepareStatement(sql);
77         //用于整体修改数据库中的值，但是一般直接用sq语句来进行操作数据库
78         //update.setString(2, str);
79         //String[] shuju = sql.split("-");
80         //System.out.println(shuju[0]);
81         //update.setString(1, shuju[0]);
82         //update.setString(2,shuju[1]);
83         //update.setString(3,shuju[2]);
84         //update.setString(4, shuju[3]);
85         update.executeUpdate();
86         update.close();
87     }
88     
89 }

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

 1 package com.ll;
 2 
 3 public class Student {
 4     private String name;
 5     private String time;
 6     private String banji;
 7     private String sex;
 8     public String getBanji() {
 9         return banji;
10     }
11     public void setBanji(String banji) {
12         this.banji = banji;
13     }
14     
15     public String getName() {
16         return name;
17     }
18     public void setName(String name) {
19         this.name = name;
20     }
21     public String getTime() {
22         return time;
23     }
24     public void setTime(String time) {
25         this.time = time;
26     }
27     
28     public String getSex() {
29         return sex;
30     }
31     public void setSex(String sex) {
32         this.sex = sex;
33     }
34     
35 
36 }

6运行index.jsp文件来运行

posted @ 2017-09-12 19:15  胖子胡八一  阅读(6803)  评论(0)  编辑  收藏  举报