在IDEA中搭建连接Oracle的环境
步骤一:打开IDEA新建一个Java Enterprise项目,点击next后仅使用默认勾选的servlet,点击下一步,创建完成。
步骤二:进入新建的项目后,在IDEA右上角的Database,
然后在弹出的界面中点击“+”号,在Data Source 中选择Oracle,如下图。
步骤三:在弹出的如下界面中设置相应的参数
将相应的参数更改如图
第一次设置时注意点击左下角的"Test Connection"进行连接测试
完成这一步就能得到一个与Oracle数据库连通的控制台,输入sql语句测试一下
这个界面就代表成功了。
然后开始写代码,目录结构如下:
IndexServlet中的代码:
package com.example.second_do_it.servlet;
import com.example.second_do_it.until.bak;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
@WebServlet(name = "IndexServlet", value = "/IndexServlet")
public class IndexServlet extends HttpServlet {
/*将stu表的数据备份到stu_bak*/
/* protected void mycopy(HttpServletRequest request, HttpServletResponse response) throws Exception {
System.out.println("开始调用存储过程");
bak thebak=new bak();
thebak.stu_copy();
request.getRequestDispatcher("index.jsp").forward(request,response);
}*/
/*根据学号删除stu表中的信息*/
protected void mydelete(HttpServletRequest request, HttpServletResponse response) throws Exception {
String Sno=request.getParameter("xuehao");
System.out.println("开始调用存储过程");
bak thebak=new bak();
thebak.stu_de(Sno);
request.getRequestDispatcher("index.jsp").forward(request,response);
}
/*将学生信息插入到stu表中*/
protected void myinsert(HttpServletRequest request, HttpServletResponse response) throws Exception {
String Sno=request.getParameter("xuehao");
String Sname=request.getParameter("xinming");
String Ssex=request.getParameter("sex");
System.out.println("开始调用存储过程");
bak thebak=new bak();
thebak.stu_in(Sno,Sname,Ssex);
request.getRequestDispatcher("index.jsp").forward(request,response);
}
/*更新stu表*/
protected void myupdate(HttpServletRequest request, HttpServletResponse response) throws Exception {
String Sno=request.getParameter("xuehao");
String Sname=request.getParameter("xinming");
String Ssex=request.getParameter("sex");
System.out.println("开始调用存储过程");
bak thebak=new bak();
thebak.stu_up(Sno,Sname,Ssex);
request.getRequestDispatcher("index.jsp").forward(request,response);
}
protected void put_stu(HttpServletRequest request, HttpServletResponse response) throws Exception {
String sql="select * from stu";
try(
Connection conn=bak.open();
//PrepareStatement 对象来执行sql语句
PreparedStatement pst=conn.prepareStatement(sql);
) {
ResultSet rs = pst.executeQuery();//接收sql语句返回的结果
response.setContentType("text/html; charset=utf-8");//修改页面响应的编码格式和编码结果
PrintWriter out=response.getWriter();//out来接收响应并打印到页面
out.println("<html><head><title>用户信息</title></head><body>");
out.println("<h2>stu表</h2><table border='1' width='200px'>");
out.println("<tr><td width='40px'>Sno</td><td width='40px'>Sname</td><td width='40px'>Ssex</td></tr></table>");
System.out.println(rs);
while(rs.next()){
out.println("<table border='1' width='200px'><tr><td width='40px'>"+rs.getString("Sno")+
"</td><td width='40px'>"+rs.getString("Sname")+
"</td><td width='40px'>"+rs.getString("Ssex")+
"</td></tr></table>");
}
bak.close(rs,conn,pst);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
protected void put_stuAndstu_bak(HttpServletRequest request, HttpServletResponse response) throws Exception {
String sql1="select * from stu";
String sql2="select * from stu_bak";
try(
Connection conn1=bak.open();
//PrepareStatement 对象来执行sql语句
Connection conn2=bak.open();
PreparedStatement pst1=conn1.prepareStatement(sql1);
PreparedStatement pst2=conn2.prepareStatement(sql2);
) {
ResultSet rs1 = pst1.executeQuery();//接收sql语句返回的结果
ResultSet rs2 = pst2.executeQuery();//接收sql语句返回的结果
response.setContentType("text/html; charset=utf-8");//修改页面响应的编码格式和编码结果
PrintWriter out=response.getWriter();//out来接收响应并打印到页面
out.println("<html><head><title>用户信息</title></head><body>");
out.println("<h2>stu表</h2><table border='1' width='200px'>");
out.println("<tr><td width='40px'>Sno</td><td width='40px'>Sname</td><td width='40px'>Ssex</td></tr></table>");
System.out.println(rs1);
while(rs1.next()){
out.println("<table border='1' width='200px'><tr><td width='40px'>"+rs1.getString("Sno")+
"</td><td width='40px'>"+rs1.getString("Sname")+
"</td><td width='40px'>"+rs1.getString("Ssex")+
"</td></tr></table>");
}
out.println("<h2>stu_bak表</h2><table border='1' width='200px'>");
out.println("<tr><td width='40px'>Sno</td><td width='40px'>Sname</td><td width='40px'>Ssex</td></tr></table>");
System.out.println(rs2);
while(rs2.next()){
out.println("<table border='1' width='200px'><tr><td width='40px'>"+rs2.getString("Sno")+
"</td><td width='40px'>"+rs2.getString("Sname")+
"</td><td width='40px'>"+rs2.getString("Ssex")+
"</td></tr></table>");
}
bak.close(rs1,conn1,pst1);
bak.close(rs2,conn2,pst2);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String action = request.getParameter("action");
if("mycopy".equals(action)){
try {
//this.mycopy(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}else if("mydelete".equals(action)){
try {
this.mydelete(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}else if("myupdate".equals(action)){
try {
this.myupdate(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}else if("myinsert".equals(action)){
try {
this.myinsert(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}else if("put_stu".equals(action)){
try {
this.put_stu(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}else if("put_stuAndstu_bak".equals(action)){
try {
this.put_stuAndstu_bak(request,response);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
bak中的代码:
package com.example.second_do_it.until;
import java.sql.*;
import java.sql.Connection;
import java.sql.SQLException;
public class bak {
public static Connection open() throws Exception {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection连接
Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");//获得数据库连接
return conn;
}
public static void close(ResultSet rs, Connection conn, PreparedStatement pst){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/* public void stu_copy() throws Exception{
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//得到Connection连接
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/book_software", "root", "3966298");
//得到预编译的Statement对象
CallableStatement pstm=connection.prepareCall("{call THEBAK}");
//执行执行存储过程
pstm.execute();
System.out.println("flag2");
//释放资源
pstm.close();
connection.close();
}*/
public void stu_in(String Sno,String Sname,String Ssex) throws Exception{
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection连接
Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");
//得到预编译的Statement对象
CallableStatement pstm=connection.prepareCall("{call STU_INSERT('"+Sno+"','"+Sname+"','"+Ssex+"')}");
//执行数据库存储过程操作
pstm.execute();
System.out.println("调用存储过程STU_INSERT进行插入");
//释放资源
pstm.close();
connection.close();
}
public void stu_up(String Sno,String Sname,String Ssex) throws Exception{
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection连接
Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");
//得到预编译的Statement对象
CallableStatement pstm=connection.prepareCall("{call STU_UPDATE('"+Sno+"','"+Sname+"','"+Ssex+"')}");
//执行数据库存储过程操作
pstm.execute();
System.out.println("调用存储过程STU_UPDATE进行更新");
//释放资源
pstm.close();
connection.close();
}
public void stu_de(String Sno) throws Exception{
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到Connection连接
Connection connection= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system as sysdba", "a123");
//得到预编译的Statement对象
CallableStatement pstm=connection.prepareCall("{call STU_DELETE('"+Sno+"')}");
//执行数据库存储过程操作
pstm.execute();
System.out.println("调用存储过程STU_DELETE进行删除");
//释放资源
pstm.close();
connection.close();
}
}
index.jsp中的代码:
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" language="java" %>
<!DOCTYPE html>
<html>
<head>
<title>学生信息</title>
</head>
<body>
<form action="IndexServlet" method="post">
<input type="hidden" name="action" value="myinsert">
学号:<input type="text" name='xuehao' id="sn"/><br />
姓名:<input type="text" name="xinming" id="sm"/><br />
性别:<input type="radio" name="sex" value="男" id="se"/>男
<input type="radio" name="sex" value="女" id="se">女<br />
<input type="submit" value="录入" />
<input type="button" value="删除" onclick="mydelete()"/>
<input type="button" value="更改" onclick="myupdate()"/>
<input type="button" value="查看stu表" onclick="lookstu()">
<input type="button" value="查看stu表和stu_bak表" onclick="lookall()">
</form>
<br />
<script>
function mydelete(){
var thesno=document.getElementById("sn").value;
console.log(thesno);
window.location.href ="IndexServlet?action=mydelete&xuehao="+thesno;
}
function mycopy(){
window.location.href ="IndexServlet?action=mycopy";
}
function myupdate(){
var thesno=document.getElementById("sn").value;
var thesname=document.getElementById("sm").value;
var thessex=document.getElementById("se").value;
window.location.href ="IndexServlet?action=myupdate&xuehao="+thesno+"&xinming="+thesname+"&sex="+thessex;
}
function lookstu(){
window.location.href ="IndexServlet?action=put_stu";
}
function lookall(){
window.location.href ="IndexServlet?action=put_stuAndstu_bak";
}
</script>
</body>
</html>
以上代码全部写好后,不出意外的话,你应该运行不了,哈哈
你可能会遇到:
以及
第一个错误是因为缺少下面这个依赖项
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.7.0.0</version>
</dependency>
第二个错误是因为缺少下面这个依赖项
<!-- https://mvnrepository.com/artifact/com.oracle.database.nls/orai18n -->
<dependency>
<groupId>com.oracle.database.nls</groupId>
<artifactId>orai18n</artifactId>
<version>19.7.0.0</version>
</dependency>
pom.xml文件中dependencise标签中的内容:
<dependencies>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.7.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle.database.nls/orai18n -->
<dependency>
<groupId>com.oracle.database.nls</groupId>
<artifactId>orai18n</artifactId>
<version>19.7.0.0</version>
</dependency>
</dependencies>
到此为止,这个小demo就完成了,前端获取数据并调用存储过程对oracle数据库进行操作的流程大概就是这样了,改成连接mysql或连接到云服务器上的mysql数据库也是可以的。
ok,祝你成功!