JDBC-Oracle的Statement接口例子（增删改查）

最新推荐文章于 2022-12-09 10:48:10 发布

噩先生

最新推荐文章于 2022-12-09 10:48:10 发布

阅读量589

点赞数

本文链接：https://blog.csdn.net/qq_37805065/article/details/100673654

版权

以下是JDBC连接Oracle数据库使用Statement接口对象，对数据表增删改查的例子

运行代码

package JDBC;

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

public class JDBCStatement {
	public static void main(String[] args) {
		JDBCStatement jdbc = new JDBCStatement();
		ArrayList<Emp> result=jdbc.selectEmpAll();
		for(Emp e:result) {
			System.out.println(e.toString());
		}
//		jdbc.insertSQL();//插入语句
//		jdbc.updateSQL();//更新语句
//		jdbc.deleteSQL();//delete语句
	}
	/**
	 * select语句
	 * @return
	 */
	public ArrayList selectEmpAll() {
		Connection con = null;
		Statement statement = null;
		ResultSet result = null;
		try {
			// 1 载入JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2 定义连接URL
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";// <主机名(localhost)或IP>:1521(默认端口号):数据库名字
			// 3 建立连接
			String username = "scott";// 用户
			String password = "123456";// 密码
			con = DriverManager.getConnection(url, username, password);
			// 4 创建Statement对象
			statement = con.createStatement();
			// 5 执行查询或更新
			String sql = "select * from emp";
			statement.execute(sql);
			result = statement.executeQuery(sql);// select查询语句
//			statement.executeUpdate(sql);//insert、update、delete语句
//			statement.executeBatch();//批量更新
			// 6 结果处理
			ArrayList<Emp> empList = new ArrayList<>();
			while (result.next()) {
				// 创建一个Emp类对象
				Emp emp = new Emp();
				// 通过result的get()方法给emp对象属性赋值
				emp.setEmpno(result.getInt("empno"));
				emp.setEname(result.getString("ename"));
				emp.setJob(result.getString("job"));
				emp.setMgr(result.getInt("mgr"));
				emp.setHiredate(result.getDate("hiredate"));
				emp.setSal(result.getDouble("sal"));
				emp.setComm(result.getDouble("comm"));
				emp.setDeptno(result.getInt("deptno"));
				// 添加
				empList.add(emp);
			}
			return empList;

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 7 关闭连接（先打开后关闭）
			if (result != null) {
				try {
					result.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	/**
	 * insert语句
	 */
	public void insertSQL() {
		Connection con = null;
		Statement sta = null;

		try {
			// 1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			// 3 建立连接
			String username = "scott";
			String password = "123456";
			con = DriverManager.getConnection(url, username, password);
			// 4 创建Statement对象
			sta = con.createStatement();
			// 5 执行SQL语句
			String sql = "insert into emp(empno,ename,job) values(2192,'test','student')";
			sta.executeUpdate(sql);

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (sta != null) {
				try {
					sta.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * update语句
	 */
	public void updateSQL() {
		Connection con = null;
		Statement sta = null;

		try {
			// 1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			// 3 建立连接
			String username = "scott";
			String password = "123456";
			con = DriverManager.getConnection(url, username, password);
			// 4 创建Statement对象
			sta = con.createStatement();
			// 5 执行SQL语句
			String sql = "update emp set ename='TTTTTT' where empno=2182";
			int result=sta.executeUpdate(sql);
			
			System.out.println(result);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (sta != null) {
				try {
					sta.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * delete语句
	 */
	public void deleteSQL() {
		Connection con = null;
		Statement sta = null;

		try {
			// 1 加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			// 3 建立连接
			String username = "scott";
			String password = "123456";
			con = DriverManager.getConnection(url, username, password);
			// 4 创建Statement对象
			sta = con.createStatement();
			// 5 执行SQL语句
			String sql = "delete from emp where empno=2212";
			int result=sta.executeUpdate(sql);
			
			System.out.println(result);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 6 关闭连接
			if (sta != null) {
				try {
					sta.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
}

Emp类

package JDBC;

/**
 * 员工表
 */
import java.util.Date;

public class Emp {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private Date hiredate;
	private double sal;
	private	double comm;
	private int deptno;
	
	public Emp() {
		super();
	}
	
	public Emp(int empno, String ename, String job, int mgr, Date hiredate, double sal, double comm, int deptno) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}
	public int getEmpno() {
		return empno;
	}
	public void setEmpno(int empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public int getMgr() {
		return mgr;
	}
	public void setMgr(int mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public double getSal() {
		return sal;
	}
	public void setSal(double sal) {
		this.sal = sal;
	}
	public double getComm() {
		return comm;
	}
	public void setComm(double comm) {
		this.comm = comm;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
				+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
	
	
	
	
}

查询运行结果

Emp [empno=8888, ename=BOB, job=CLERK, mgr=7788, hiredate=1985-03-03, sal=3000.0, comm=0.0, deptno=0]
Emp [empno=2222, ename=test, job=student, mgr=0, hiredate=null, sal=0.0, comm=0.0, deptno=0]
Emp [empno=2182, ename=TTTTTT, job=student, mgr=0, hiredate=null, sal=0.0, comm=0.0, deptno=0]
Emp [empno=3333, ename=3333, job=student, mgr=0, hiredate=null, sal=6700.0, comm=0.0, deptno=0]
Emp [empno=2552, ename=te22st, job=student, mgr=0, hiredate=null, sal=0.0, comm=0.0, deptno=0]
Emp [empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=1980-12-17, sal=800.0, comm=0.0, deptno=20]
Emp [empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=1981-02-20, sal=1600.0, comm=300.0, deptno=30]
Emp [empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=1981-02-22, sal=1250.0, comm=500.0, deptno=30]
Emp [empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=1981-04-02, sal=2975.0, comm=0.0, deptno=20]
Emp [empno=7654, ename=MARTIN, job=SALESMAN, mgr=7698, hiredate=1981-09-28, sal=1250.0, comm=1400.0, deptno=30]
Emp [empno=7698, ename=BLAKE, job=MANAGER, mgr=7839, hiredate=1981-05-01, sal=2850.0, comm=0.0, deptno=30]
Emp [empno=7782, ename=CLARK, job=MANAGER, mgr=7839, hiredate=1981-06-09, sal=3050.0, comm=0.0, deptno=10]
Emp [empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=1987-04-19, sal=3000.0, comm=0.0, deptno=20]
Emp [empno=7839, ename=KING, job=PRESIDENT, mgr=0, hiredate=1981-11-17, sal=5600.0, comm=0.0, deptno=10]
Emp [empno=7844, ename=TURNER, job=SALESMAN, mgr=7698, hiredate=1981-09-08, sal=1500.0, comm=0.0, deptno=30]
Emp [empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=1987-05-23, sal=1100.0, comm=0.0, deptno=20]
Emp [empno=7900, ename=JAMES, job=CLERK, mgr=7698, hiredate=1981-12-03, sal=950.0, comm=0.0, deptno=30]
Emp [empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=1981-12-03, sal=3000.0, comm=0.0, deptno=20]
Emp [empno=7934, ename=MILLER, job=CLERK, mgr=7782, hiredate=1982-01-23, sal=1900.0, comm=0.0, deptno=10]

噩先生

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JDBC-Oracle的Statement接口例子（增删改查）

以下是JDBC连接Oracle数据库使用Statement接口对象，对数据表增删改查的例子运行代码package JDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.St...
复制链接

扫一扫