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