1, 注册页面
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Strict//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>用户注册</title>
</head>
<body>
<form id="uform" action="saveuser" method="post">
用户代码：<input id="userid" type="text" name="userid" width=30 />
<br><br>
用户名称：<input id="username" type="text" name="username" width=30 />
<br><br>
登录密码：<input id="password" type="password" name="password" width=30 />
<br><br>
确认密码：<input id="queren" type="password" name="queren" width=30 />
<br><br>
<input type="submit" value="注册" /><br><a href="findoracle">显示所有用户</a>
</form>
</body>
</html>
2, 保存用户数据
package com.hanqi; import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; public class saveuser extends HttpServlet {
private static final long serialVersionUID = 1L; public saveuser() {
super();
} protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8"); //设置字符集
response.setContentType("text/html; charset=UTF-8");
response.setCharacterEncoding("UTF-8");
String strUserid = request.getParameter("userid"); //获取传递过来的参数
String strUsername = request.getParameter("username");
String strPW = request.getParameter("password");
if(strUserid == null||strUserid.trim().length()==0) { //判断获取的参数是否为空
response.getWriter().append("用户ID不能为空");
}
else if(strUsername == null||strUsername.trim().length()==0) {
response.getWriter().append("用户名称不能为空");
}
else {
try {
Class.forName("oracle.jdbc.driver.OracleDriver"); //注册Oracle
String url = "jdbc:oracle:thin:@localhost:1521:orcl"; //设置连接oracle数据库
Connection conn = DriverManager.getConnection(url, "test1204", "1234"); //登录oracle, 连接oracle用户
if(conn!=null) {
//操作数据库
String sql = "insert into T_Test (user_id, user_name, password)"+"values(?,?,?)"; //设置oracle语句的命令
//执行语句的类
PreparedStatement ps = conn.prepareStatement(sql); //创建类ps, 并下达执行命令
//strUsername = new String(strUsername.getBytes("iso-8859-1"),"utf-8");
//转码的问题, 当这个页面接收过来的参数是以post方法传来的, 完全可以只用一个setCharacterEncoding("UTF-8");就能进行转码
ps.setString(1, strUserid); //设置写入oracle表中的值
ps.setString(2, strUsername);
ps.setString(3, strPW);
int row = ps.executeUpdate(); //执行语句的命令并返回数据行数
if(row>0) {
response.getWriter().append("保存数据成功, 保存"+row+"条数据成功!");
}
else {
response.getWriter().append("保存数据失败");
}
ps.close();
conn.close();
}
else
{
response.getWriter().append("连接数据库失败");
}
} catch (Exception e) {
e.printStackTrace();
response.getWriter().append(e.getMessage());
}
}
response.getWriter().append("<br><a href='zhuce.html'>返回注册</a>");
response.getWriter().append("<br><a href='findoracle'>显示所有用户</a>");
} protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
3, 工具类
package com.hanqi;
//关于用户的实体类
public class User {
//用户代码
private String UserID;
public String getUserID()
{
return UserID;
}
public void setUserID(String UID)
{
this.UserID = UID;
} //用户名称
private String UserName;
public String getUserName()
{
return UserName;
}
public void setUserName(String userName)
{
UserName = userName;
} //密码
private String PassWord;
public String getPassWord()
{
return PassWord;
}
public void setPassWord(String passWord)
{
PassWord = passWord;
}
} //定义连接数据库的方体
package com.hanqi; import java.sql.*; public class DBHelper {
public static Connection getconnection() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
Connection conn = DriverManager.getConnection(url, "test1204", "1234");
return conn;
} }
4, 修改数据
package oraclejdbc; import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.hanqi.DBHelper; public class Edit extends HttpServlet {
private static final long serialVersionUID = 1L; public Edit() {
super();
} protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8"); //设置字符集
response.setContentType("text/html; charset=UTF-8");
String strUserid = request.getParameter("userid"); //获取传递过来的参数
String strUsername = request.getParameter("username");
String strPW = request.getParameter("password");
if(strUserid == null||strUserid.trim().length()==0) { //判断获取的参数是否为空
response.getWriter().append("用户ID不能为空");
}
else if(strUsername == null||strUsername.trim().length()==0) {
response.getWriter().append("用户名称不能为空");
}
else {
try {
Class.forName("oracle.jdbc.driver.OracleDriver"); //注册Oracle
String url = "jdbc:oracle:thin:@localhost:1521:orcl"; //设置连接oracle数据库
Connection conn = DBHelper.getconnection();
if(conn!=null) { //to_date （ ‘2007-12-20 18:31:34’ ， ‘YYYY-MM-DD HH24:MI:SS’ ）
//操作数据库
String sql = "update T_Test set user_name=?, password=?"+"where user_id=?"; //设置oracle语句的命令
//执行语句的类
PreparedStatement ps = conn.prepareStatement(sql); //创建类ps, 并下达执行命令
ps.setString(1, strUsername); //设置写入oracle表中的值
ps.setString(2, strPW);
ps.setString(3, strUserid);
int row = ps.executeUpdate(); //执行语句的命令并返回数据行数
ps.close();
conn.close();
response.sendRedirect("findoracle");
}
else
{
response.getWriter().append("连接数据库失败");
}
} catch (Exception e) {
response.getWriter().append(e.getMessage());
}
}
} protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
5, 删除数据(包括提交事务, 添加日志)
package oraclejdbc; import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hanqi.DBHelper; import java.sql.*; public class oracledelete extends HttpServlet {
private static final long serialVersionUID = 1L; public oracledelete() {
super();
} protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8"); //设置字符集
response.setContentType("text/html; charset=UTF-8");
response.setCharacterEncoding("UTF-8");
String userid = request.getParameter("userid"); try {
Connection conn = DBHelper.getconnection();
if(userid!=null&&userid.trim().length()>0) {
conn.setAutoCommit(false);
//可以直接组合语句, 但是这样有一定的风险, 当后面的参数值比较多的时候容易遗漏
//String sql = "delete from t_test where user_id = '"+userid+"'";
String sql = "delete from t_test where user_id = ?";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, userid.trim());
int row = pst.executeUpdate();
//保存日志
try {
//构建sql语句
sql = "insert into t_log (log_id, log, create_time) values(sq_log_id.nextval,?,?)";
pst = conn.prepareStatement(sql);
pst.setString(1, "删除了"+userid.trim()+"的记录");
//实例化一个时间
java.util.Date dt = new java.util.Date();
//java.sql.Date d = new java.sql.Date(dt.getTime()); //只是生成一个日期的值
java.sql.Time d = new java.sql.Time(dt.getTime()); //日期和时间都会生成
pst.setTime(2, d);
pst.executeUpdate(); //执行构建的语句
conn.commit();
}
catch(Exception ex) {
conn.rollback();
//response.getWriter().append("写入日志时出错");
}
finally { //使用finally关键字, 不管出错与否, 都要释放资源
try {
pst.close();
conn.close();
}
catch(Exception ex1) {
conn.close();
}
}
}
else {
response.getWriter().append("userid不能为空");
}
}
catch (Exception e) {
response.getWriter().append(e.getMessage()+"异常信息");
}
response.sendRedirect("findoracle");
} protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
6, 批处理数据
package oraclejdbc; import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.hanqi.DBHelper; import java.sql.*;
import java.util.*; public class BatchAdd extends HttpServlet {
private static final long serialVersionUID = 1L; public BatchAdd() {
super();
} protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8"); //设置字符集
response.setContentType("text/html; charset=UTF-8");
response.setCharacterEncoding("UTF-8");
PreparedStatement pps = null;
Connection conn = null;
try {
conn = DBHelper.getconnection();
if(conn!=null) {
try {
//批量插入
String sql = "insert into t_test(user_id, user_name, password) values(?,?,?)";
pps = conn.prepareStatement(sql);
Random rd = new Random();
//用for循环控制插入的个数
for(int i = 0; i<10; i++) {
int m = rd.nextInt(1000);
pps.setString(1, ""+m);
pps.setString(2, "abc"+i);
pps.setString(3, ""+(++i+119));
pps.addBatch();
}
int[] n = pps.executeBatch(); //返回值是一个int[]型数组, 如果插入成功的话, 那数组的每个值都是 -2
response.getWriter().append("已添加"+n.length+"条数据");
} catch(Exception e2) {
response.getWriter().append(e2.getMessage());
}
finally {
pps.close();
conn.close();
}
} //这个括号是if 的
} catch(Exception e) {
response.getWriter().append(e.getMessage());
}
} protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
7, 显示数据
package com.hanqi; import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; public class findoracle extends HttpServlet {
private static final long serialVersionUID = 1L; public findoracle() {
super();
} protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8"); //设置字符集
response.setContentType("text/html; charset=UTF-8");
try {
//这三行可以封装一个类
Class.forName("oracle.jdbc.driver.OracleDriver"); //注册Oracle驱动
String url = "jdbc:oracle:thin:@localhost:1521:orcl"; //设置连接oracle数据库
Connection conn = DriverManager.getConnection(url, "test1204", "1234"); //登录oracle, 连接oracle用户
if(conn!=null) {
//操作数据库
String sql = "select * from t_test"; //设置oracle语句的命令
//strUsername = new String(strUsername.getBytes("iso-8859-1"),"utf-8"); //转码
Statement stm = conn.createStatement();
//执行查询语句, 返回值为ResultSet结果集
ResultSet rs = stm.executeQuery(sql);
ArrayList<User> array = new ArrayList<User>();
//User u = new User();
if(rs!=null) {
//遍历查询结果
while(rs.next()) { //next();判断rs是否有下一个值, 返回的是一个bealoon型数据
User u = new User();
//取出结果集中的value, 也可以写成"u.setUserID(rs.getString(int));"
u.setUserID(rs.getString("user_id"));
u.setUserName(rs.getString("user_name"));
u.setPassWord(rs.getString("password"));
//将取出的结果添加到一个集合中去, 这里集合的内容会经常用到, Result型结果集取出后通常都放在集合里面
array.add(u);
//response.getWriter().append(u.getUserID()+u.getUserName()+u.getPassWord()+"<br>");
}
request.setAttribute("userlist", array);
conn.close();
stm.close();
rs.close();
}
else {
response.getWriter().append("无查询结果");
}
}
else {
response.getWriter().append("连接数据库失败");
}
} catch (Exception e) {
response.getWriter().append(e.getMessage());
}
//跳转页面
request.getRequestDispatcher("show.jsp").forward(request, response);
//response.sendRedirect("show");这个语句不起作用的
} protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
} }
8, 显示页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="com.hanqi.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>显示查询结果</title>
</head>
<body>
<%
Object obj = request.getAttribute("userlist");
if(obj!=null) {
ArrayList<User> array = (ArrayList<User>)obj;
//foreach遍历,输出结果的时候写一个out.print("");就可以了,为了看的直观写了好几个out.print("");
for(User u:array) {
out.print(u.getUserID()+"/ "+u.getUserName()+"/ "+u.getPassWord()+"<br>");
out.print("\t <a href='oracledelete?userid="+u.getUserID()+"'>删除</a><br>");
out.print("<form action='Edit' method='post'>");
//设置隐藏域的意义在于, 修改用户名和密码的时候必须先将用户代码取出作为唯一的值, 修改完再将用户代码传回表中
out.print("<input type='hidden' name='userid' value='"+u.getUserID()+"'/>"); //隐藏域的应用
out.print("用户名称:<input type='text' name='username' value='"+u.getUserName()+"' /><br>");
out.print("用户密码:<input type='text' name='password' value='"+u.getPassWord()+"' /><br>");
out.print("<input type='submit' value='修改' />");
out.print("</form>");
out.print("<br>"+"———————————————————"+"<br>");
}
}
else {
out.print("无数据返回");
}
%>
<br>
<a href="zhuce.html">注册用户信息</a><br><br><a href="BatchAdd">批量添加用户</a>
</body>
</html>