JDBC-Oracle的PreparedStetement接口例子(增删改查)

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

代码

package JDBC;

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

public class JDBCPreparedStatement {

	public static void main(String[] args) {
		JDBCPreparedStatement jdbc=new JDBCPreparedStatement();
//		Emp e=jdbc.selectEmp(7839);
//		System.out.println(e.toString());
//		jdbc.insertEmp(3333, "3333", "student");
		jdbc.updateEmp(10);
//		jdbc.deleteEmp(222);
	}
	/**
	 * select语句
	 * @param empno
	 * @return
	 */
	public Emp selectEmp(int empno) {
		Connection con = null;
		PreparedStatement presta = 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";
			// 3 获取连接
			con = DriverManager.getConnection(url, "scott", "123456");
			// 4 建立PrepareStatement对象
			String sql = "select * from emp where empno=?";//?占位符
			presta = con.prepareStatement(sql);
			// 5 设置占位符并执行SQL语句
			presta.setInt(1, empno);//将第一个占位符补全
			System.out.println(sql);
			result = presta.executeQuery();
			// 6 结果处理
			Emp e = new Emp();
			while (result.next()) {
				e.setEmpno(result.getInt("empno"));
				e.setEname(result.getString("ename"));
				e.setJob(result.getString("job"));
				e.setHiredate(result.getDate("hiredate"));
				e.setSal(result.getDouble("sal"));
				e.setComm(result.getDouble("comm"));
				e.setDeptno(result.getInt("deptno"));
			}
			return e;

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(result!=null) {
				try {
					result.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(presta!=null) {
				try {
					presta.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语句
	 * @param empno
	 * @param ename
	 * @param job
	 */
	public void insertEmp(int empno,String ename,String job) {
		Connection con = null;
		PreparedStatement presta = null;
		try {
			// 1 载入JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			// 3 获取连接
			con = DriverManager.getConnection(url, "scott", "123456");
			// 4 建立PrepareStatement对象
			String sql = "insert into emp(empno,ename,job) values(?,?,?)";//?占位符
			presta = con.prepareStatement(sql);
			// 5 设置占位符并执行SQL语句
			presta.setInt(1, empno);//将第一个占位符补全
			presta.setString(2, ename);
			presta.setString(3, job);
			int result = presta.executeUpdate();
			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 {
			if(presta!=null) {
				try {
					presta.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语句
	 * @param empno
	 */
	public void updateEmp(int empno) {
		Connection con = null;
		PreparedStatement presta = null;
		try {
			// 1 载入JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			// 3 获取连接
			con = DriverManager.getConnection(url, "scott", "123456");
			// 4 建立PrepareStatement对象
			String sql = "update emp set sal=sal+200 where deptno=?";//?占位符
			presta = con.prepareStatement(sql);
			// 5 设置占位符并执行SQL语句
			System.out.println(empno);
			presta.setInt(1, empno);
			System.out.println(presta.toString());
			System.out.println(sql);
			
			presta.executeUpdate();
//			int result = presta.executeUpdate();
//			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 {
			if(presta!=null) {
				try {
					presta.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语句
	 * @param empno
	 */
	public void deleteEmp(int empno) {
		Connection con = null;
		PreparedStatement presta = null;
		try {
			// 1 载入JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2 定义url连接
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			// 3 获取连接
			con = DriverManager.getConnection(url, "scott", "123456");
			// 4 建立PrepareStatement对象
			String sql = "delete from emp where empno=?";//?占位符
			presta = con.prepareStatement(sql);
			// 5 设置占位符并执行SQL语句
			presta.setInt(1, empno);//将第一个占位符补全
			int result = presta.executeUpdate();
			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 {
			if(presta!=null) {
				try {
					presta.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();
				}
			}
		}
	}
}