JDBC 5：批处理、存储过程_JAVA_编程开发_程序员俱乐部

        1.Emp的pojo类
       

class="java">package com.lxd.dao;

import java.io.Serializable;

public class Emp implements Serializable {
	private static final long serialVersionUID = 1L;
	private Integer empno;
	private String ename;
	private Double sal;

	public Emp() {

	}

	public Emp(Integer empno, String ename, Double sal) {
		this.empno = empno;
		this.ename = ename;
		this.sal = sal;
	}

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public Double getSal() {
		return sal;
	}

	public void setSal(Double sal) {
		this.sal = sal;
	}

}

        2.数据库工具类DBUtil
       

package com.lxd.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 通过 Properties 文件获得数据库连接
 * 
 * @author Xiangdong
 * 
 */
public class DBUtil {
	private static Properties props;

	static {
		props = new Properties();

		InputStream in = DBUtil.class.getClassLoader().getResourceAsStream(
				"jdbc.properties");
		try {
			props.load(in);
			Class.forName(props.getProperty("drivername"));
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() {
		Connection conn = null;
		String url = props.getProperty("url");
		String username = props.getProperty("username");
		String password = props.getProperty("password");

		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
		// 关闭原则:先开后关,后开先关

		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// public static void main(String[] args) {
	// System.out.println(getConnection());
	// }
}

        3.批处理：Emp的dao类。
       

package com.lxd.dao.batch;

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

import com.lxd.dao.DBUtil;
import com.lxd.dao.Emp;

/**
 * 批处理
 * 
 * @author Xiangdong
 * 
 */
public class EmpDao {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;

	public boolean batch(List<Emp> emps) {
		boolean flag = true;

		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);

			String sql = "insert into emp(empno,ename,sal) ";
			sql += " values(?,?,?)";

			for (Emp emp : emps) {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, emp.getEmpno());
				ps.setString(2, emp.getEname());
				ps.setDouble(3, emp.getSal());
				ps.executeUpdate();
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e.printStackTrace();
			}
			flag = false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return flag;
	}

	public static void main(String[] args) {
		List<Emp> emps = new ArrayList<Emp>();
		emps.add(new Emp(9999, "Tom", 99d));
		emps.add(new Emp(9998, "Tom", 99d));
		emps.add(new Emp(9997, "Tom", 99d));
		emps.add(new Emp(9996, "Tom", 99d));
		emps.add(new Emp(9995, "Tom", 99d));

		EmpDao dao = new EmpDao();
		if (dao.batch(emps)) {
			System.out.println("批处理成功");
		} else {
			System.out.println("保存出错.");
		}
	}
}

        4.Java应用程序调用存储过程

        存储过程代码：
       

--输入一个empno，输出ename，sal
create or replace procedure p_emp(
  v_empno emp.empno%type,
  v_ename out emp.ename%type,
  v_sal out emp.sal%type
) as

begin
  select ename,sal into v_ename,v_sal
  from emp where empno=v_empno;
exception
  when others then
    v_ename:='查无此人';
    v_sal:=0;
end p_emp;

        JDBC调存储过程代码：
       

package com.lxd.dao.procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import com.lxd.dao.DBUtil;
import com.lxd.dao.Emp;

/**
 * Java 应用程序 调 存储过程
 * 
 * @author Xiangdong
 * 
 */
public class ProcedureDemo {
	private Connection conn;
	// 1.操作句柄:用来执行存储过程的SQL语句
	// 是PreparedStatement的子类
	private CallableStatement cs;
	private ResultSet rs;

	public Emp call(Integer empno) {
		Emp emp = new Emp();

		try {
			conn = DBUtil.getConnection();
			// 2.调存储过程的SQL语句
			String sql = "{call p_emp(?,?,?)}";
			cs = conn.prepareCall(sql);
			// 3.输入参数
			cs.setInt(1, empno);
			// 3.注册输出参数(参数一:占位符序号/参数二:输出参数类型)
			cs.registerOutParameter(2, Types.VARCHAR);
			cs.registerOutParameter(3, Types.DOUBLE);

			// 4.执行存储过程
			cs.executeUpdate();
			// 5.获得输出参数的返回值 并封装到 emp 对象中
			emp.setEmpno(empno);
			emp.setEname(cs.getString(2));
			emp.setSal(cs.getDouble(3));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, cs, rs);
		}
		return emp;
	}

	public static void main(String[] args) {
		ProcedureDemo dao = new ProcedureDemo();
		Emp emp = dao.call(7788);
		System.out.println(emp.getEmpno() + " " + emp.getEname() + " "
				+ emp.getSal());
	}
}

        补充：使用PL/SQL执行存储过程：
       

declare
  v_empno emp.empno%type;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  v_empno:=&empno;
  p_emp(v_empno,v_ename,v_sal);
  dbms_output.put_line(v_ename||' '||v_sal);
end;

• jdbc.rar (299 Bytes)
• 下载次数: 0