JDBC连接数据库

来源：互联网发布：java软件系统技术合同编辑：程序博客网时间：2024/04/30 17:11

本例两个页面，一个页面接受数据1.htm，一个页面接受数据并存入数据库1.jsp

新建的MySQl数据库person表
create table person
(
id int(3) auto_increment not null primary key,
name varchar(10),
password varchar(20),
age int(3)
);
1.htm代码
<form action="1.jsp" method="post">
姓名：<input type="text"name="uname"><br>
密码：<input type="password"name="upass"><br>
年龄：<input type="text"name="age"><br>
<input type="submit" value="增加">
</form>

2.jsp代码
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<%!
    String DBDRIVER = "com.mysql.jdbc.Driver" ;
    String DBURL ="jdbc:mysql://127.0.0.1:3306/demo_db?user=root&password=123456" ;

    Connection conn = null ;
    Statement stmt = null ;
    PreparedStatement pstmt = null ;

    String sql = null ;
%>
<%
    request.setCharacterEncoding("GB2312") ;
    String name = request.getParameter("uname") ;
    String password = request.getParameter("upass") ;
    int age = 0 ;
    try
    {
        age =Integer.parseInt(request.getParameter("age")) ;
    }
    catch(Exception e)
    {}

    sql = "INSERT INTO person (name,password,age) VALUES(?,?,?)" ;
    sql1 = "INSERT INTO person (name,password,age) VALUES('"+name+"','"+password+"',"+age+")";

    try
    {
        Class.forName(DBDRIVER) ;//步骤一、加载驱动程序
        conn = DriverManager.getConnection(DBURL);//步骤二、连接数据库
        pstmt = conn.prepareStatement(sql) ;//步骤三、操作数据库,添加数据
        pstmt.setString(1,name) ;
        pstmt.setString(2,password) ;
        pstmt.setInt(3,age) ;
        pstmt.executeUpdate() ;
        // 或者
       stmt = conn.createStatement();
       stmt.executeUpdate(sql1);
       out.println("数据添加成功败！！！") ;
    }
    catch(Exception e)
    {
        out.println("数据库驱动程序加载失败！！！") ;
        e.printStackTrace();
    }

    try
    {
        pstmt.close() ;//步骤四：关闭数据库
        stmt.close();
        conn.close() ;
    }
    catch(Exception e)
    {
        out.println("数据库关闭失败！！！") ;
        e.printStackTrace();
    }
%>

步骤一、加载驱动程序
Class.forName(DBDRIVER);
步骤二、连接数据库
Connection conn=DriverManager.getConnection(DBURL);
步骤三、操作数据库
Statement 类一般常用三个方法executeUpdate(),executeQuery(),execute().
executeUpdate()执行增、删、改
executeQuery()执行查询，返回为结果集

stmt = conn.createStatement();
String sql1 = "INSERT INTO person (name,password,age) VALUES('casa2','123456',28)" ;
String sql2 = "SELECT FROM　person";
stmt.executeUpdate(sql1);
ResultSet rs = stmt.executeQuery(sql2);

String sql3= "INSERT INTO person (name,password,age) VALUES (?,?,?)";
pstmt = conn.preparedStatment(sql3)
pstmt.setString(1,"casa2");
pstmt.setString(2,"123456");
pstmt.setInt(3,28);
pstmt.executeUpdate();

步骤四、关闭数据库

JDBC连接数据库之二：列表显示数据

本例将数据库中的数据列表显示，只有一个jsp文件2.jsp
2.jsp
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*"%>
<%--
    此代码主要进行查询操作
--%>
<%!
    String DBDRIVER = "com.mysql.jdbc.Driver";
    String DBURL ="jdbc:mysql://127.0.0.1:3306/demo_db?user=root&password=123456" ;
    Connection conn = null ;
    Statement stmt = null ;
    String sql = null ;
    ResultSet rs = null ;
%>
<%
    try
    {
        Class.forName(DBDRIVER) ;//步骤一、加载驱动程序
        conn = DriverManager.getConnection(DBURL);//步骤二、连接数据库
        stmt = conn.createStatement() ;//步骤三、操作数据库,返回结果集
        sql = "SELECT id,name,password,ageFROM person" ;
        rs = stmt.executeQuery(sql) ;
%>
    <table border="1" width="80%">
    <tr>
        <td>ID</td>
        <td>姓名</td>
        <td>密码</td>
        <td>年龄</td>
    </tr>
<%
        while(rs.next())
        {
            int id = rs.getInt(1);
            String name =rs.getString(2) ;
            String password =rs.getString(3) ;
            int age = rs.getInt(4);
%>
            <tr>
               <td><%=id%></td>
               <td><%=name%></td>
               <td><%=password%></td>
               <td><%=age%></td>
            </tr>
<%
        }
%>
    </table>
<%
    }
    catch(Exception e)
    {
        out.println("操作数据库失败！！！") ;
    }
    try
    {
        rs.close() ;//步骤四：关闭数据库
        stmt.close() ;
        conn.close() ;
    }
    catch(Exception e)
    {
        out.println("数据库关闭失败！！！") ;
    }
%>

JDBC连接数据库之三：JavaBean实现增、删、改、查的一个实例

本实例通过JavaBean进行数据的增、删、改、查，使用到的页面和JavaBean见下表所示：
源码下载ch06-3（749.74K）

序号

文件名

类型

功能描述

index.html

页面

显示主菜单

addStudent.jsp

页面

添加学生信息页面

addStudent_do.jsp

页面

调用JavaBean处理添加

deleteStudent.jsp

页面

列表显示所以学生，选择删除的学生

deleteStudent_do.jsp

页面

调用JavaBean处理删除

modifyStudent.jsp

页面

列表显示所以学生，选择修改的学生

modifyStudent_pro.jsp

页面

修改某一学生信息

modifyStudent_do.jsp

页面

调用JavaBean处理修改

viewStudent.jsp

页面

列表显示所有学生信息

searchStudentBySid.jsp

页面

根据学号进行查询

DataBaseConnection.java

JavaBean

连接数据库

Student.java

JavaBean

封装学生信息的JavaBean

StudentUtil.java

JavaBean

实现学生信息的增、删、改、查

一、首先建立数据库：
在MySQL数据里建立jsp_db数据库-student表
USE jsp_db;
DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid varchar(20) NOT NULL,
name varchar(30) default NULL,
sex int(1) default NULL,
phone varchar(16) default NULL,
birth date default NULL,
Constraint primary key pk_student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

二、在Eclipse里新建web工程ch6-3；
       在src文件新建ch6包；
       把三个Java类拷贝到ch6里，更改DataBaseConnection.java的连接MySQL字符串；
       把mysql-connector-java-5.1.5-bin.jar拷贝到WEB-INF/lib文件夹里；
      其他JSP页面文件拷贝到WebRoot里，部署项目，重启Tomcat服务器，实例代码就能运行了。

三、代码分析
1.index.html
<html>
<head>
<title>学生管理</title>
<meta http-equiv="Content-Type" content="text/html;charset=gb2312">
</head>

<body>
<center>
<hr>
<h1>学生管理</h1>
<a href="addStudent.jsp">增加学生</a><br>
<a href="deleteStudent.jsp">删除学生</a><br>
<a href="modifyStudent.jsp">更改学生信息</a><br>
<a href="viewStudent.jsp">查看所有学生</a><br>
<hr>
<form action="searchStudentBySid.jsp" method=post>
按学生的学号查询：<input type=text name=sid>
<input type=submit value="查询">
</form>
</center>
</body>
</html>

2.addStudent.jsp
<%@ page contentType="text/html;charset=gb2312" language="java" %>
<html>
<head>
<title>增加学生</title>
</head>
<body>
<script language="JavaScript">

</script>
<% request.setCharacterEncoding("gb2312");%>
<center>
<h1>增加一个新的学生</h1>
<form action="addStudent_do.jsp" method="post"name="student">
<table border="1" >
<tr><td>学号：<input type="text"name="sid"></td></tr>
<tr><td>姓名：<input type="text"name="name"></td></tr>
<tr><td>性别： <INPUT type=radio CHECKEDvalue=1 name=sex> 男   
               <INPUTtype=radio value=0 name=sex> 女</td></tr>
<tr><td>电话：<input type="text"name="phone"></td></tr>
<tr><td>出生日期：<input type="text"name="birth">日期格式：1996-08-11</td></tr>
<tr><td><input type=submit value=提交onClick="return RgTest()"></td></tr>
</table>
</form>
</center>
</body>
</html>

3.addStudent_do.jsp
<%@ page contentType="text/html;charset=gb2312" language="java" import="ch6.*" %>
<html>
<head>
<title>增加学生</title>
</head>
<% request.setCharacterEncoding("gb2312");%>
//id:表示所建的JavaBean实例的名称；class：表示这个JavaBean的类名
<jsp:useBeanid="student" class="ch6.Student" scope="page">
//通过内省机制，根据请求的参数名称，自动设定与JavaBean相同属性名称的值
<jsp:setPropertyname="student" property="*"/>
</jsp:useBean>

<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<%studentUtil.addStudent(student);%>
<center>
<h1>增加学生成功</h1>
<a href="index.html">返回</a>
</center>
</body>
</html>

4.deleteStudent.jsp
<%@ page contentType="text/html;charset=gb2312" language="java"import="ch6.*,java.util.*" %>
<html>
<head>
<title>删除学生</title>
</head>
<% request.setCharacterEncoding("gb2312");%>
<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<center>
<h1>删除一个学生</h1>
<table width=80% border="1" >
<tr>
<td>学号</td><td>姓名</td><td>性别</td><td>电话</td><td>出生日期</td><td>删除< /td>
</tr>
<%
Collection students=studentUtil.getAllStudent();
Iterator it=students.iterator();
while(it.hasNext())
{
    Student temp=(Student)it.next();
    out.println("<tr>");
   out.println("<td>"+temp.getSid()+"</td>");
    try
   {
        String sex = (temp.getSex()==1)?"男":"女";
       out.println("<td>"+temp.getName()+"</td>");
        out.println("<td>"+sex+"</td>");
       out.println("<td>"+temp.getPhone()+"</td>");
       out.println("<td>"+temp.getBirth()+"</td>");
    }
    catch(Exception e)
   {
           e.printStackTrace();
   }
    out.println("<td><ahref='deleteStudent_do.jsp?sid="+temp.getSid()+"'>删除</a>");
    out.println("</tr>");
}
%>
</table>
<a href="index.html">返回</a>
</center>
</body>
</html>

5.deleteStudent_do.jsp
<%@ page contentType="text/html;charset=gb2312" language="java" import="ch6.*" %>
<html>
<head>
<title>删除成功</title>
</head>
<% request.setCharacterEncoding("gb2312");%>
<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<%
studentUtil.deleteStudent((String)request.getParameter("sid"));
response.sendRedirect("deleteStudent.jsp");
%>
<center>
</center>
</body>
</html>

6.modifyStudent.jsp
<%@ page contentType="text/html;charset=gb2312" language="java"import="ch6.*,java.util.*" %>
<html>
<head>
<title>选择要更改的学生</title>
</head>
<% request.setCharacterEncoding("gb2312");%>
<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<center>
<h1>选择要更改的学生</h1>
<table width=80% border="1" >
<tr>
<td>学号</td><td>姓名</td><td>性别</td><td>电话</td><td>出生日期</td><td>更改< /td>
</tr>
<%
Collection students=studentUtil.getAllStudent();
Iterator it=students.iterator();
while(it.hasNext())
{
    Studenttemp=(Student)it.next();
    out.println("<tr>");
   out.println("<td>"+temp.getSid()+"</td>");
    try
   {
           String sex =(temp.getSex()==1)?"男":"女";
       out.println("<td>"+temp.getName()+"</td>");
       out.println("<td>"+sex+"</td>");
       out.println("<td>"+temp.getPhone()+"</td>");
       out.println("<td>"+temp.getBirth()+"</td>");
    }
    catch(Exception e)
   {
           e.printStackTrace();
   }
    out.println("<td><ahref='modifyStudent_pro.jsp?sid="+temp.getSid()+"'>更改</a>");
    out.println("</tr>");
}
%>
</table>
<a href="index.html">返回</a>
</center>
</body>
</html>

7.modifyStudent_pro.jsp
<%@ page contentType="text/html;charset=gb2312" language="java" import="ch6.*" %>
<% request.setCharacterEncoding("gb2312");%>
<html>
<head>
<title>更改学生信息</title>
</head>
<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<center>
<%
String sid=request.getParameter("sid");
Student student=studentUtil.getStudentInfo(sid);
%>
<h1>更改一个学生</h1>
<form action="modifyStudent_do.jsp" method="post">
<table border="1" >
<tr><td>学号：<input type="text"name="sid" readonly="" value="<%=student.getSid()%>"></td></tr>
<tr><td>姓名：<input type="text"name="name" value="<%=student.getName()%>"></td></tr>
<tr><td>性别：
<%if(student.getSex()==1){%>
<INPUT type=radio CHECKED value=1 name=sex> 男   
               <INPUTtype=radio value=0 name=sex> 女
<%}else{%>
<INPUT type=radio value=1 name=sex> 男  
               <INPUTtype=radio CHECKED value=0 name=sex> 女
<%}%>
</td></tr>
<tr><td>电话：<inputtype="text" name="phone" value="<%=student.getPhone()%>"></td></tr>
<tr><td>出生日期：<inputtype="text" name="birth" value="<%=student.getBirth()%>">日期格式：1996-08-11</td></tr>
<tr><td><input type=submit value=提交></td></tr>
</table>
</form>
</center>
</body>
</html>

8.modifyStudent_do.jsp
<%@ page contentType="text/html; charset=gb2312"language="java" import="ch6.*" %>
<html>
<head>
<title>更改学生</title>
</head>
<% request.setCharacterEncoding("gb2312");%>
<jsp:useBean id="student"class="ch6.Student" scope="page">
<jsp:setProperty name="student"property="*"/>
</jsp:useBean>
<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<%studentUtil.modifyStudent(student);%>
<center>
<h1>更改学生成功</h1>
<a href="index.html">返回</a>
</center>
</body>
</html>

源码下载ch06-3（749.74K）

JDBC连接数据库之三：JavaBean实现增、删、改、查的一个实例（续前）

9.viewStudent.jsp
<%@ page contentType="text/html; charset=gb2312"language="java" import="ch6.*,java.util.*" %>
<html>
<head>
<title>查看所有的学生</title>
</head>
<% request.setCharacterEncoding("gb2312");%>
<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<center>
<h1>查看所有的学生</h1>
<table width=80% border="1" >
<tr>
<td>学号</td><td>姓名</td><td>性别</td><td>电话</td><td>出生日期</td>
</tr>
<%
Collection students=studentUtil.getAllStudent();
Iterator it=students.iterator();
while(it.hasNext())
{
    Student temp=(Student)it.next();
    out.println("<tr>");
   out.println("<td>"+temp.getSid()+"</td>");
    try
   {
        String sex = (temp.getSex()==1)?"男":"女";
       out.println("<td>"+temp.getName()+"</td>");
       out.println("<td>"+sex+"</td>");
       out.println("<td>"+temp.getPhone()+"</td>");
       out.println("<td>"+temp.getBirth()+"</td>");
    }
    catch(Exception e)
   {
           e.printStackTrace();
   }
    out.println("</tr>");
}
%>
</table>
<a href="index.html">返回</a>
</center>
</body>
</html>

10.searchStudentBySid.jsp
<%@ page contentType="text/html; charset=gb2312"language="java" import="ch6.*,java.util.*" %>
<html>
<head>
<title>搜索结果</title>
</head>
<% request.setCharacterEncoding("gb2312");%>
<jsp:useBean id="studentUtil"class="ch6.StudentUtil" scope="page"/>
<body>
<center>
<% Stringsid=request.getParameter("sid");%>
<h1>查看学号为<fontcolor=red><%=sid%></font>的学生</h1>
<table width=80% border="1" >
<tr>
<td>学号</td><td>姓名</td><td>性别</td><td>电话</td><td>出生日期</td>
</tr>
<%
Collectionstudents=studentUtil.getStudentBySid(sid);
Iterator it=students.iterator();
while(it.hasNext())
{
    Student temp=(Student)it.next();
    out.println("<tr>");


   out.println("<td>"+temp.getSid()+"</td>");
    try
   {
        String sex = (temp.getSex()==1)?"男":"女";
       out.println("<td>"+temp.getName()+"</td>");
       out.println("<td>"+sex+"</td>");
        out.println("<td>"+temp.getPhone()+"</td>");
       out.println("<td>"+temp.getBirth()+"</td>");
    }
    catch(Exception e)
   {
           e.printStackTrace();
   }
    out.println("</tr>");
}

%>
</table>
<a href="index.html">返回</a>
</center>
</body>
</html>

11.DataBaseConnection.java
package ch6;
import java.sql.*;
//连接数据库的工具类。
public class DataBaseConnection
{
    /**
    *一个静态方法，返回一个数据库的连接。
    *这样达到了对数据库连接统一控制的目的。
    */
    public static Connection getConnection()
    {
        Connection con=null;
        StringCLASSFORNAME="com.mysql.jdbc.Driver";
        StringserverInfo="jdbc:mysql://127.0.0.1:3306/jsp_db?user=root&password=1234&useUnicode=true&characterEncoding=gb2312";

        try
        {
           Class.forName(CLASSFORNAME);
            con =DriverManager.getConnection(serverInfo);

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return con;
    }
}

12.Student.java
package ch6;

import java.io.*;

public class Student implements Serializable {

    /* 私有字段 */

    private String sid;

    private String name;

    private int sex;//0:代表男 1:代表女

    private String phone;

    private String birth;

    /* JavaBean属性访问方法 */

    /** 共公方法 */


    public String getBirth() {
        return birth;
    }
    public String getName() {
        return name;
    }
    public String getPhone() {
        return phone;
    }
    public int getSex() {
        return sex;
    }
    public String getSid() {
        return sid;
    }
    public void setBirth(String birth) {
        this.birth = birth;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public void setSex(int sex) {
        this.sex = sex;
    }
    public void setSid(String sid) {
        this.sid = sid;
    }
}

13.StudentUtil.java
package ch6;

import java.sql.*;
import java.util.*;

/**
* StudentUtil包含和Student表相关的操作
*/
public class StudentUtil {
    private Connection con;

    //构造方法，获得数据库的连接。
    public StudentUtil() {
        this.con =DataBaseConnection.getConnection();
    }

    /**
    * 搜索所有的学生信息,返回由Student值对象组成的Collection
    */
    public Collection getAllStudent() throws Exception {
        Statement stmt = con.createStatement();
        ResultSet rst =stmt.executeQuery("select * from student");
        Collectionret = new ArrayList();//定义数组，存放VO对象实例
        while (rst.next()) {
            Student tempSt = new Student();//定义VO对象，将结果集中的每条记录赋值给VO对象
           tempSt.setSid(rst.getString("sid"));
           tempSt.setName(rst.getString("name"));
           tempSt.setSex(rst.getInt("sex"));
           tempSt.setPhone(rst.getString("phone"));
           tempSt.setBirth(rst.getString("birth"));

            ret.add(tempSt);//将VO对象加入数组
        }
        stmt.close();
        con.close();
        return ret;
    }

    /**
    * 按照学生的学号查找学生，返回由Student值对象组成的Collection
    */
    public Collection getStudentBySid(String sid) throwsException {
        Statement stmt = con.createStatement();
        ResultSet rst =stmt.executeQuery("select * from student where sid='"
                +sid + "'");
        Collection ret = new ArrayList();
        while (rst.next()) {
            Student tempSt = newStudent();
            tempSt.setSid(rst.getString("sid"));
           tempSt.setName(rst.getString("name"));
           tempSt.setSex(rst.getInt("sex"));
           tempSt.setPhone(rst.getString("phone"));
           tempSt.setBirth(rst.getString("birth"));

            ret.add(tempSt);
        }
        stmt.close();
        con.close();
        return ret;
    }

   /**
    * 添加一个学生，使用Student值对象作为参数传给这个方法。
    */
    public void addStudent(Student student) throws Exception {

        PreparedStatement pstmt = con
               .prepareStatement("insert into student values(?,?,?,?,?)");
        pstmt.setString(1, student.getSid());
        pstmt.setString(2, student.getName());
        pstmt.setInt(3, student.getSex());
        pstmt.setString(4, student.getPhone());
        pstmt.setString(5, student.getBirth());

        pstmt.execute();
        pstmt.close();
        con.close();
    }

    /**
    * 更改学生的信息，使用Student值对象作为参数传给这个方法。
    */
    public void modifyStudent(Student student) throws Exception{

        PreparedStatement pstmt = con
               .prepareStatement("update student set name=?, sex=?,phone=?,birth=? wheresid=?");
        pstmt.setString(1, student.getName());
        pstmt.setInt(2, student.getSex());
        pstmt.setString(3, student.getPhone());
        pstmt.setString(4, student.getBirth());
        pstmt.setString(5, student.getSid());
        pstmt.execute();
        pstmt.close();
        con.close();
    }

    /**
    * 删除指定学号的学生
    */
    public void deleteStudent(String sid) throws Exception {
        Statement stmt = con.createStatement();
        stmt.execute("delete from studentwhere sid='" + sid + "'");
        stmt.close();
        con.close();
    }

    /**
    * 返回给定学号的学生的信息，返回的是值对象
    */
    public Student getStudentInfo(String sid) throws Exception {

        Statement stmt = con.createStatement();
        ResultSet rst =stmt.executeQuery("select * from student where sid='"
                +sid + "'");
        Student student = null;
        while (rst.next()) {
            student = newStudent();
           student.setSid(rst.getString("sid"));
           student.setName(rst.getString("name"));
           student.setSex(rst.getInt("sex"));
            student.setPhone(rst.getString("phone"));
           student.setBirth(rst.getString("birth"));
        }
        stmt.close();
        con.close();
        return student;
    }

    public synchronized static java.util.DategetStringToDate(String date,
            String type) throwsException {
        if (type == null ||type.equals("") || type.equals("null")) {
            type ="yyyyMMdd";
        }
        java.text.SimpleDateFormat jts = newjava.text.SimpleDateFormat(type);
        java.util.Date fact = jts.parse(date);
        return fact;
    }
}

下面的代码是以前写的和这个实例无关，但是基本功能和StudentUtil.java类似
ProjectManageDAO.java
package com.selab.spdss.dao.system.project;

import java.sql.*;
import java.util.ArrayList;

import com.selab.spdss.common.DBManager;
import com.selab.spdss.vo.system.project.ProjectInfoVO;

public class ProjectManageDAO {
   /*
    * 列表显示项目信息
    */
    public ArrayList getProjectList() {
        ArrayListtmpList = new ArrayList();//定义数组，存放VO对象实例
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "SELECT * FROMPROJECTS";
        try{
            conn =DBManager.getConnection();
            stmt =conn.createStatement();
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                ProjectInfoVO vo = new ProjectInfoVO();//将结果集中的每条记录赋值给VO对象
               vo.setProjectID(rs.getString("PROJID"));
               vo.setProjectName(rs.getString("PROJNAME"));
               vo.setProjectDesc(rs.getString("PROJDESC"));
                tmpList.add(vo);   //将VO对象实例加入数组
            }
        } catch (SQLException e) {
           e.printStackTrace();
          }finally {
               DBManager.close(rs);
               DBManager.close(stmt);
               DBManager.close(conn);
                }

        return tmpList;
    }

    public void delProject(String projectID) {
        Connection conn = null;
        Statement stmt = null;
        String sql = "DELETE FROM PROJECTSWHERE PROJID ='"+projectID+"'";
        try{
            conn =DBManager.getConnection();
            stmt =conn.createStatement();
           stmt.executeUpdate(sql);

        } catch (SQLException e) {
           e.printStackTrace();
          }finally {
               DBManager.close(stmt);
               DBManager.close(conn);
                }

    }

    public String getNewProjectID() {
        String newID = new String();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "select max(PROJID)from PROJECTS";
        try{
            conn =DBManager.getConnection();
            stmt =conn.createStatement();
            rs =stmt.executeQuery(sql);

            if(rs.next()){
               String tmp = rs.getString(1);
               if(tmp != null){
                   newID = String.valueOf(Integer.parseInt(tmp)+1);
               }else{
                   newID = "1";
                }
                           }
            } catch (SQLExceptione) {
           e.printStackTrace();
          }finally {
               DBManager.close(rs);
               DBManager.close(stmt);
               DBManager.close(conn);
                }
        return newID;
    }
   /*
    * 插入一条新记录
    */
    public void insertProject(ProjectInfoVO vo) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "insert into PROJECTS (PROJID,PROJNAME,PROJDESC)values (?,?,?)";
        try{
            conn =DBManager.getConnection();
            pstmt =conn.prepareStatement(sql);
           pstmt.setString(1,vo.getProjectID());
           pstmt.setString(2,vo.getProjectName());
           pstmt.setString(3,vo.getProjectDesc());
            pstmt.executeUpdate();
            } catch (SQLExceptione) {
           e.printStackTrace();
          }finally {
           DBManager.close(pstmt);
            DBManager.close(conn);
        }

    }
    public ProjectInfoVO getProjectInfo(String projectID) {
        ProjectInfoVO vo = new ProjectInfoVO();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from PROJECTSwhere PROJID = ?";
        try{
            conn =DBManager.getConnection();
            pstmt =conn.prepareStatement(sql);
           pstmt.setString(1,projectID);
            rs =pstmt.executeQuery();
            if(rs.next()){
               vo.setProjectID(rs.getString(1));
               vo.setProjectName(rs.getString(2));
               vo.setProjectDesc(rs.getString(3));
            }

        } catch (SQLException e) {
           e.printStackTrace();
          }finally {
               DBManager.close(rs);
               DBManager.close(pstmt);
               DBManager.close(conn);
                }
        return vo;
    }
    /*
    * 保存一条修改记录
    */
    public void update(ProjectInfoVO vo) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        String sql = "update PROJECTS setPROJNAME=?,PROJDESC=? where PROJID=?";
        try{
            conn =DBManager.getConnection();
            pstmt =conn.prepareStatement(sql);
           pstmt.setString(1,vo.getProjectName());
           pstmt.setString(2,vo.getProjectDesc());
           pstmt.setString(3,vo.getProjectID());
            pstmt.executeUpdate();
        } catch (SQLException e) {
           e.printStackTrace();
          }finally {

               DBManager.close(pstmt);
               DBManager.close(conn);
                }

    }
}

下面的代码是以前写的也和这个实例无关，但是基本功能和DataBaseConnection.java类似
DBManager.java
package com.selab.spdss.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
* 数据库操作类
*/
public class DBManager {
    public DBManager(){
        }
    public static Connection getConnection() throws SQLException{
            try {

             Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
             System.out.println( "类实例化成功!" );
              returnDriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=SPDSS","sa","123456");
            }
            catch (Exception e) {
             e.printStackTrace();
              throw newSQLException(e.getMessage());
            }
        }

    /**
       * 关闭ResultSet对象
       */
    public static void close(ResultSet rs) {
        if (rs != null) {
          try {
            rs.close();
          }
          catch (SQLException e) {
           e.printStackTrace();
          }
        }
    }

    /**
       * 关闭Statement对象
       */
    public static void close(Statement st) {
        if (st != null) {
          try {
            st.close();
          }
          catch (SQLException e) {
           e.printStackTrace();
          }
        }
    }

    /**
       * 关闭Connection对象
       */
    public static void close(Connection conn) {
        if (conn != null) {
          try {
            conn.close();
          }
          catch (SQLException e) {
           e.printStackTrace();
          }
        }
    }
}

MVC设计模式实现的javaWEB增、删、改、查

009-08-01 15:23

项目名称：briefnessTest
开发工具：myEclipse6.0\mysql

<1>表示层：4个JSP页面：分别是：index.jsp(登录页);home.jsp（主页面）;error.jsp(登录错误返回页);add.jsp（增加和更新页）

图<1>

jsp页面代码如下所示:
index.jsp:

<[email=%@page]%@page[/email] contentType="text/html;charset=gbk"%>
<html>
   <head>
<title>
   BriefnessTest
</title>
<link rel="stylesheet" type="text/css"href="user.css">
   </head>
   <body>
     <form action="validate.do"method="post">
     <table align="center"width="90%">
       <tr align="center">
      <td><h1>welcome!</h1></td>
       </tr>
       <tr>
       <td>
         <hr/>
       </td>
       </tr>
     </table>
     <table align="center">
       <tr>

       </tr>
       <tr>
       <td>
       用户名:
       </td>
       <td><input type="text"name="uname"/></td>
       </tr>
       <tr>
       <td>密码:</td>
       <td><inputtype="password"name="upass"/></td>
       </tr>
       <tr>
       <td>
         <inputtype="submit" value="提交"/>
       </td>
       <td>
         <input type="reset"value="重置"/>
       </td>
       </tr>
     </table>
     </form>
   </body>
</html>

home.jsp:

<[email=%@page]%@page[/email]contentType="text/html;charset=gbk"%>
<[email=%@page]%@page[/email]import="java.util.*,org.ljw.userdb.*" %>
<html>
<head>
<title>home</title>
<link rel="stylesheet" type="text/css"href="user.css">
</head>
<body>
<table width="90%" align="center">
   <tr>
   <tdalign="center"><h1>welcome:</h1><h3><%=session.getAttribute("uname")%></h3></td>
   </tr>
   <tr>
   <td colspan="2"><hr/></td>
   </tr>
</table>
<table border="1" align="center"width="50%">
   <tr>
   <td colspan="3" align="center">用户信息如下</td>
   </tr>
   <tr>
   <td>姓名:</td>
   <td>更新</td>
   <td>删除</td>
   </tr>
   <%
   UserDataBase ud=new UserDataBase();
   String luSql="select *from userinfo";
   List li=new ArrayList();
   li=ud.lookUp(luSql);
   Iterator iter=li.iterator();
   while(iter.hasNext()){
   String uname=iter.next().toString();
   %>
   <tr>
   <td><%=uname%></td>
   <td><a href="add.jsp?state=更新&uname=<%=uname%>">更新</a></td>
   <td><a href="addServlet.do?states=删除&delname=<%=uname%>">删除</a></td>
   </tr>
   <%
   }
   %>
   <tr>
   <td colspan="3">
     <a href="add.jsp?state=增加&mailto:%@page">%@pagecontentType="text/html;charset=gbk"%>
<html>
<head>
   <title>Add</title>
</head>
<body>
   <form action="addServlet.do"method="post">
   <table>
     <tr>
     <td>
       用户名:
     </td>
     <%
      request.setCharacterEncoding("gbk");
       String state = newString(request.getParameter("state").getBytes(
         "ISO-8859-1"),"GBK");
       String uname = newString(request.getParameter("uname").getBytes(
         "ISO-8859-1"),"GBK");
     %>
     <td>
       <input type="text"value="<%=uname%>"
       name="uname" />
       <input type="hidden"value="<%=uname%>"
       name="unames" />
     </td>
     <td>
       <input type="submit"value="<%=state%>" name="submit" />
     </td>
     </tr>
   </table>
   </form>
</body>
</html>

error.jsp:

<[email=%@page]%@page[/email] contentType="text/html;charset=gbk"%>
<html>
<head>
<title>error</title>
</head>
<body>
<a href="index.jsp"><h3>您输入的用户名或密码错误请单击此处返回登录页!</h3></a& gt;
</body>
</html>

如上所述本案例中所写的jsp页面以上以全部给出!

<2>此案例中使用了数据库所以在此将建数据库的代码写出：
create database usertable;
use usertable;
create table userinfo(
username varchar(10));

insert into userinfo values('ljw')
insert into userinfo values('lijiawei')

<3>数据层：新建一个数据库操作类(以便实现MVC模式、提高代码的复用性)
UserDataBase:
package org.ljw.userdb;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class UserDataBase {
private static String dbDriver="com.mysql.jdbc.Driver";
private String dbUrl="jdbc:mysql://localhost:3306/usertable";
private Connection con=null;
private PreparedStatement ps=null;
private ResultSet r=null;

static{
   try{
   Class.forName(dbDriver);
   }catch(Exception e){
   e.printStackTrace();
   }
}

public void conn()throws Exception{
   Class.forName(dbDriver);
  con=DriverManager.getConnection(dbUrl,"root","root");
}

public List lookUp(String sql)throws Exception{
   if(con==null ||con.isClosed()){
   this.conn();
   }
   List list=new ArrayList();
   ps=con.prepareStatement(sql);
   r=ps.executeQuery();
   while(r.next()){
   list.add(r.getString(1));
   }
   r.close();
   ps.close();
   return list;
}
public void update(String sql,String uname)throws Exception{
   if(con==null ||con.isClosed()){
   this.conn();
   }
   ps=con.prepareStatement(sql);
   ps.setString(1, uname);
   ps.executeUpdate();
   ps.close();
   con.close();
}
public void update(String sql,String uname,String unames)throws Exception{
   if(con==null ||con.isClosed()){
   this.conn();
   }
   ps=con.prepareStatement(sql);
   ps.setString(1, uname);
   ps.setString(2, unames);
   ps.executeUpdate();
   ps.close();
   con.close();
}
}

<4>接下来就是业务逻辑层：新建２个Servlet：

UserServlet:

package org.ljw.testservlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class UserServlet extends HttpServlet{
public void doGet(HttpServletRequest request,HttpServletResponseresponse)throws ServletException,IOException{
   response.setContentType("text/html;charset=gbk");
   request.setCharacterEncoding("gbk");
   String uname=request.getParameter("uname");
   String upass=request.getParameter("upass");
   request.getSession().setAttribute("uname", uname);
   if("lijiawei".equals(uname)&&"lijiawei".equals(upass)){
  request.getRequestDispatcher("home.jsp").forward(request, response);
   }else{
   response.sendRedirect("error.jsp");
   }
}
public void doPost(HttpServletRequest request,HttpServletResponse response)throwsServletException,IOException{
   this.doGet(request, response);
}
}

AddServlet:

package org.ljw.testservlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.ljw.userdb.UserDataBase;
public class AddServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
   request.setCharacterEncoding("gbk");
   String uname = request.getParameter("uname");
   String unames = request.getParameter("unames");
   String submit = request.getParameter("submit");
   UserDataBase ud = new UserDataBase();
   if ("更新".equals(submit)) {
   String sql = "update userinfo set username=? whereusername=?";
   try {
     ud.update(sql, uname, unames);
   } catch (Exception e) {
     e.printStackTrace();
   }
  request.getRequestDispatcher("home.jsp").forward(request, response);
   } else if ("增加".equals(submit)){
   String inSql = "insert into userinfo values(?)";
   try {
     ud.update(inSql, uname);
   } catch (Exception e) {
     e.printStackTrace();
   }
  request.getRequestDispatcher("home.jsp").forward(request, response);
   }
   else{
   String delName=newString(request.getParameter("delname").getBytes("ISO-8859-1"),"GBK");
   String deSql = "delete from userinfo where username=?";
   try {
     ud.update(deSql, delName);
   } catch (Exception e) {
     e.printStackTrace();
   }
  request.getRequestDispatcher("home.jsp").forward(request, response);
   }
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
   throws ServletException, IOException {
   this.doGet(request, response);
}
}

<5>上面的写完以后接下来就是改web.xml配置文件了!

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<welcome-file-list>
   <welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
   <servlet-name>userServlet</servlet-name>
  <servlet-class>org.ljw.testservlet.UserServlet</servlet-class>
</servlet>
<servlet>
   <servlet-name>addServlet</servlet-name>
  <servlet-class>org.ljw.testservlet.AddServlet</servlet-class>
</servlet>
<servlet-mapping>
   <servlet-name>addServlet</servlet-name>
   <url-pattern>/addServlet.do</url-pattern>
</servlet-mapping>
<servlet-mapping>
   <servlet-name>userServlet</servlet-name>
   <url-pattern>/validate.do</url-pattern>
</servlet-mapping>
</web-app>

此案例的页面中引用了一个外部的CSS样式是我在写完程序的时候后补上去的。代码如下：

user.css:

h3
{
color:red;
}
h1
{
font-family: arial;
}