Oracle Java Demo Simple CRUD

开始,话不多说,直接上代码

是使用tomcat启动和 Servlet结合使用

Oracle 连接工具类

package com.kunze.oraclejdbc.util;

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

public class OracleConnectionUtil {

	// 连接 驱动
	private static String dirverName = "oracle.jdbc.driver.OracleDriver";
	private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static String user = "system";
	private static String password = "Yzh164725";
	
	public static Connection getConnection() {
		// 初始化连接
		Connection conn = null;
		try {
			Class.forName(dirverName);
			// 获取连接
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void closeResultSet(ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void closePreparedStatement(PreparedStatement stmt) {
		if(stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void closeConnection(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

实体类,大家可以自定义

package com.kunze.oraclejdbc.bean;

import java.io.Serializable;

public class Student implements Serializable {
	private static final long serialVersionUID = -7278346411710850373L;
	
	private Integer id;
	private Integer xh;
	private String xm;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public Integer getXh() {
		return xh;
	}
	public void setXh(Integer xh) {
		this.xh = xh;
	}
	public String getXm() {
		return xm;
	}
	public void setXm(String xm) {
		this.xm = xm;
	}
}

DAO层

package com.kunze.oraclejdbc.dao;

import java.util.ArrayList;

import com.kunze.oraclejdbc.bean.Student;

public interface StudentDao {
	
	public boolean insert(Student student);
	
	public boolean delete(Integer id);
	
	public ArrayList<Student> query();
	
	public boolean update(Student student);
}

DAO实现层

package com.kunze.oraclejdbc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.kunze.oraclejdbc.bean.Student;
import com.kunze.oraclejdbc.dao.StudentDao;
import com.kunze.oraclejdbc.util.OracleConnectionUtil;

public class StudentDaoImpl implements StudentDao {

	public boolean insert(Student student) {
		boolean flag = false;
		PreparedStatement pstmt = null;
		// 连接
		Connection conn = null;
		try {
			conn = OracleConnectionUtil.getConnection();

			String sql = "insert into STUDENT(id,xh,xm)VALUES(?,?,?)";
			
			// 填充参数
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, student.getId());
			pstmt.setInt(2, student.getXh());
			pstmt.setString(3, student.getXm());
			
			// 执行
			flag = pstmt.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnectionUtil.closePreparedStatement(pstmt);
			OracleConnectionUtil.closeConnection(conn);
		}
		return flag;
	}

	public ArrayList<Student> query() {
		ArrayList<Student> list = new ArrayList<Student>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 连接
		Connection conn = null;
		try {
			String sql = "SELECT * FROM student";
			
			conn = OracleConnectionUtil.getConnection();
			
			// 创建SQL
			pstmt = conn.prepareStatement(sql);
			
			//ִ 获取结果集
			rs = pstmt.executeQuery();
			if(rs != null) {
				while(rs.next()) {
					Student student = new Student();
					student.setId(rs.getInt("id"));
					student.setXh(rs.getInt("xh"));
					student.setXm(rs.getString("xm"));
					list.add(student);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnectionUtil.closeResultSet(rs);
			OracleConnectionUtil.closePreparedStatement(pstmt);
			OracleConnectionUtil.closeConnection(conn);
		}
		return list;
	}

	public Student queryById(int id) {
		Student student = new Student();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 连接
		Connection conn = null;
		try {
			String sql = "SELECT * FROM student where id=" + id;
			
			conn = OracleConnectionUtil.getConnection();
			
			// 创建SQL
			pstmt = conn.prepareStatement(sql);
			
			//ִ 获取结果集
			rs = pstmt.executeQuery();
			if(rs != null) {
				while(rs.next()) {
					student.setId(rs.getInt("id"));
					student.setXh(rs.getInt("xh"));
					student.setXm(rs.getString("xm"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnectionUtil.closeResultSet(rs);
			OracleConnectionUtil.closePreparedStatement(pstmt);
			OracleConnectionUtil.closeConnection(conn);
		}
		return student;
	}

	public boolean delete(Integer id) {
		boolean flag = false;
		PreparedStatement pstmt = null;
		// 连接
		Connection conn = null;
		try {
			String sql = "delete from student where id="+id;
			
			conn = OracleConnectionUtil.getConnection();
			
			// 创建SQL
			pstmt = conn.prepareStatement(sql);
			
			//ִ 执行
			int rst = pstmt.executeUpdate();
			if(rst > 0) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnectionUtil.closePreparedStatement(pstmt);
			OracleConnectionUtil.closeConnection(conn);
		}
		return flag;
	}

	public boolean update(Student student) {
		boolean flag = false;
		PreparedStatement pstmt = null;
		// 连接
		Connection conn = null;
		try {
			String sql = "update student set xh=?,xm=? where ID=?";
			
			conn = OracleConnectionUtil.getConnection();
			
			// 创建SQL
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, student.getXh());
			pstmt.setString(2, student.getXm());
			pstmt.setInt(3, student.getId());
			
			//ִ 执行
			int rst = pstmt.executeUpdate();
			if(rst != 0) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnectionUtil.closePreparedStatement(pstmt);
			OracleConnectionUtil.closeConnection(conn);
		}
		return flag;
	}
}

Servlet类

package com.kunze.oraclejdbc;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.kunze.oraclejdbc.bean.Student;
import com.kunze.oraclejdbc.dao.impl.StudentDaoImpl;

/**
 * Servlet implementation class QueryServlet
 */
public class AallServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
    /**
     * Default constructor. 
     */
    public AallServlet() {
        // TODO Auto-generated constructor stub
    }
    
	@Override
	public void init(ServletConfig config) throws ServletException {
		System.out.println("初始化QueryServlet");
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		int methodName = Integer.parseInt(request.getParameter("methodName"));
		try {
			switch (methodName) {
			case 1:
				select(request, response);
				break;
			case 2:
				insert(request, response);
				break;
			case 3:
				queryById(request, response);
				break;
			case 4:
				update(request, response);
				break;
			case 5:
				delete(request, response);
				break;
			case 6:
				addPage(request, response);
				break;
			default:
				break;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	public void select(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		StudentDaoImpl studentDaoImpl = new StudentDaoImpl();
		ArrayList<Student> query = studentDaoImpl.query();
		request.setAttribute("result", query);
		request.getRequestDispatcher("index.jsp").forward(request, response);
	}
	
	public void addPage(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.getRequestDispatcher("add.jsp").forward(request, response);
	}
	
	public void insert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Student student = new Student();
		student.setId(Integer.parseInt(request.getParameter("id")));
		student.setXh(Integer.parseInt(request.getParameter("stuno")));
		student.setXm(request.getParameter("name"));
		StudentDaoImpl studentDaoImpl = new StudentDaoImpl();
		boolean insert = studentDaoImpl.insert(student);
		System.out.println(insert?"添加成功":"添加失败");
		if(insert) {
			request.setAttribute("msg", "添加失败");
			request.getRequestDispatcher("error.jsp").forward(request, response);
		}else {
			ArrayList<Student> query = studentDaoImpl.query();
			request.setAttribute("result", query);
			request.getRequestDispatcher("index.jsp").forward(request, response);
		}
	}
	
	public void queryById(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int id = Integer.parseInt(request.getParameter("id"));
		StudentDaoImpl studentDaoImpl = new StudentDaoImpl();
		Student student = studentDaoImpl.queryById(id);
		if(student != null) {
			request.setAttribute("result", student);
			request.getRequestDispatcher("update.jsp").forward(request, response);
		}else {
			request.setAttribute("msg", "查询失败");
			request.getRequestDispatcher("error.jsp").forward(request, response);
		}
	}
	
	public void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Student student = new Student();
		student.setId(Integer.parseInt(request.getParameter("id")));
		student.setXh(Integer.parseInt(request.getParameter("xh")));
		student.setXm(request.getParameter("xm"));
		StudentDaoImpl studentDaoImpl = new StudentDaoImpl();
		boolean update = studentDaoImpl.update(student);
		System.out.println("更新结果="+update);
		if(update) {
			ArrayList<Student> query = studentDaoImpl.query();
			request.setAttribute("result", query);
			request.getRequestDispatcher("index.jsp").forward(request, response);
		}else {
			request.setAttribute("msg", "更新失败");
			request.getRequestDispatcher("error.jsp").forward(request, response);
		}
	}
	
	public void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int id = Integer.parseInt(request.getParameter("id"));
		StudentDaoImpl studentDaoImpl = new StudentDaoImpl();
		boolean delete = studentDaoImpl.delete(id);
		System.out.println("删除结果="+delete);
		if(delete) {
			ArrayList<Student> query = studentDaoImpl.query();
			request.setAttribute("result", query);
			request.getRequestDispatcher("index.jsp").forward(request, response);
		}else {
			request.setAttribute("msg", "删除失败");
			request.getRequestDispatcher("error.jsp").forward(request, response);
		}
	}

	
	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

web.xml

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
  <display-name>Archetype Created Web Application</display-name>
  <servlet>
  	<servlet-name>AallServlet</servlet-name>
  	<display-name>AallServlet</display-name>
  	<description></description>
  	<servlet-class>com.kunze.oraclejdbc.AallServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>AallServlet</servlet-name>
  	<url-pattern>/AallServlet</url-pattern>
  </servlet-mapping>
</web-app>

Index页面

<%@page import="com.kunze.oraclejdbc.bean.Student"%>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="com.kunze.oraclejdbc.bean.Student" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>学生信息</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
 <script type="text/javascript">
    function confirmdialog(){
       if(window.confirm("您确定要删除此条信息?")){
       	return true;
       }else{
       	return false;
       }      
    }
 </script>
  </head>
  
  <body>
    
    <br>
    <h1>学生信息</h1>  <br>  <hr>    
  
     <br>
  <h3>全部学生信息如下</h3>
   <table width="510" border="100" cellSpacing=1 style="border: 1pt dashed ; font-size: 15pt;" height="31">
    <tr>
    <td>ID</td>
    <td>学号</td>
    <td>姓名</td>
    <td><a href="AallServlet?methodName=6">添加</a></td>
    </tr>
    <%
     response.setCharacterEncoding("UTF-8");
     request.setCharacterEncoding("UTF-8");
     List<Student> subResult=(List<Student>)request.getAttribute("result");
       if(!subResult.isEmpty()){
       for(int i=0;i<subResult.size();i++){
    	    Student st=subResult.get(i);
            out.print("<tr>");
            out.print("<td>"+st.getId()+"</td>");
            out.print("<td>"+st.getXh()+"</td>");
            out.print("<td>"+st.getXm()+"</td>");
            
     %>
     <td>
	     <a href="AallServlet?id=<%=st.getId() %>&methodName=5" οnclick="return confirmdialog()">删除</a>
	     <a href="AallServlet?id=<%=st.getId() %>&methodName=3">修改</a>
     </td>
    <% 
    		out.print("</tr>");
         }
       }else{
           out.print("<td colspan='4'>暂无数据</td>");
       }
     %>
     </table>
	<br>
  </body>
</html>

Add页面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>学生信息输入</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
	<script type="text/javascript"">
    function validate(){
	    var id=document.forms[0].id.value;
	    var stuno=document.forms[0].stuno.value;
	    var name=document.forms[0].name.value;
	    if(id<=0){
	    	alert("ID不能为空,请输入ID!");
	    	return false;
	    }else if(stuno<=0){
	    	alert("学号不能为空,请输入学号!");
	    	return false;
	    }else if(name.length<=0){
	    	alert("姓名不能为空,请输入姓名!");
	    	return false;
	    }else{
	    	return true;
	    }
    }
    </script>
  </head>
  
  <body>
  <br>
  <center><h2>学生信息输入</h2><hr>
 	<form action="AallServlet?methodName=2" method="post" id="form" onSubmit="return validate()" >
		<h4>  ID:<input type="text" name="id" title="ID必须为数字"></input><br></h4>
		<h4>  学号:<input type="text" name="stuno" title="学号必须为数字"></input><br></h4>
		<h4>  姓名:<input type="text" name="name" title="姓名不能为空"></input><br></h4>
	 	<input type="submit" value="提交"/>
  	</form>
  	<br>
  </center>
  </body>
</html>

Update页面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="com.kunze.oraclejdbc.bean.Student" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>学生信息修改</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
 
  </head>
  
  <body>
    <br>
    <h2>学生信息</h2>  <hr>    
    <br> 
  <h3>要修改的学生信息如下</h3>
  <%
    	Student result=new Student();
        result=(Student)request.getAttribute("result");
		int id=result.getId();
		int xh=result.getXh();
		String xm=result.getXm();
     %>
    <h3>学生信息更改:</h3>
	 <form action="AallServlet" method="post" >
	 <input type="hidden" name="methodName" value="4"/>
	 <input type="hidden" name="id" value="<%=id %>"/>
	 <h4> 学号:<input type="text" name="xh" value="<%=xh %>" title="学号不能改变"></input><br></h4>
	 <h4> 姓名:<input type="text" name="xm" value="<%=xm %>" title="姓名不能为空"></input><br></h4>
	 <input type="submit" value="修改"/>
  </form>
 
 <br>
	<h3><a href="AallServlet?methodName=1">返回信息查询页面</a></h3>
  </body>
</html>

Error页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!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>Insert title here</title>
</head>
<body>
	<%
	response.setCharacterEncoding("UTF-8");
    request.setCharacterEncoding("UTF-8");
    String msg = request.getAttribute("msg").toString();
    out.print("<h1>"+msg+"</h1>");
	%>
	<h1><a href="AallServlet?methodName=1">返回</a></h1>
</body>
</html>

Oracle SQL 驱动需要自己去引入,有什么问题请大家多多指教,有大神愿意指点我的可以加我的QQ:1647258251,谢谢

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值