声明:学了几天终于将增删改查的操作掌握了，也发现了一些问题，所以总结一下.
重点:操作数据库主要用的是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 != )
{
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[];
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[]);
add.setString(, shuju[]);
add.setString(,shuju[]);
add.setString(,shuju[]);
add.setString(, shuju[]);
add.executeUpdate();
add.close();
}
public void deletesql(String sql) throws Exception{
int i = ;
PreparedStatement delete = conn.prepareStatement(sql);
try{
i = delete.executeUpdate();
}catch(Exception e){
if(i != )
{
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文件来运行