java-JDBC简单的利用存储过程来《查询》或者《插入》

首先我创建了一个插入学生信息的存储过程，名为pro_insert

CREATE procedure [dbo].[pro_insert]
	@name varchar(20),
	@sex varchar(20),
	@age int,
	@classID int
as
begin
	insert into [dbo].[Student] values(@name,@sex,@age,@classID)
end

然后我创建了一个根据学生id来返回学生姓名的存储过程，名为pro_selectById

CREATE procedure [dbo].[pro_selectById]
	@sId int,
	@name varchar(20) output
as
begin
	select @name=stuName from [dbo].[Student] where sid = @sId
end

创建好数据库之后，开始java程序啦

<pre name="code" class="java">

/**
 * 学生实体
 * @author Administrator
 *
 */
public class Student {
	private int sId;
	private String stuName;
	private String sex;
	private int age;
	private int classId;
	
	public Student(String stuName, String sex, int age, int classId) {
		super();
		this.stuName = stuName;
		this.sex = sex;
		this.age = age;
		this.classId = classId;
	}
	public int getsId() {
		return sId;
	}
	public void setsId(int sId) {
		this.sId = sId;
	}
	public String getStuName() {
		return stuName;
	}
	public void setStuName(String stuName) {
		this.stuName = stuName;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public int getClassId() {
		return classId;
	}
	public void setClassId(int classId) {
		this.classId = classId;
	}
	@Override
	public String toString() {
		return "Student [sId=" + sId + ", stuName=" + stuName + ", sex=" + sex
				+ ", age=" + age + ", classId=" + classId + "]";
	}
	
}

<pre name="code" class="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class DbUtil {
	private static Connection connection;

	private static final String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static final String url = "jdbc:sqlserver://localhost:1433;databaseName=student";
	private static final String userName = "sa";
	private static final String userPwd = "123456";

	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	protected DbUtil() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * 创建链接
	 * @return
	 */
	public static Connection getCon() {
		try {
			connection = DriverManager.getConnection(url, userName, userPwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * 关闭连接
	 * @param con
	 * @param ps
	 * @param st
	 */
	public static void close(Connection con, PreparedStatement ps, Statement st) {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

<pre name="code" class="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.company.exercise14.Dao.Student;
import com.company.exercise14.Util.DbUtil;

public class StudentImpl {

	/**
	 * 利用存储过程，通过学生编号查询学生姓名
	 * 
	 * @param sId
	 * @return
	 */
	public static String selectBySId(int sId) {
		Connection con = DbUtil.getCon();
		CallableStatement cs = null;
		String stuName = null;
		try {
			cs = con.prepareCall("{? = call pro_selectById(?)}");
			cs.registerOutParameter(1, java.sql.Types.VARCHAR);
			cs.setInt(2, sId);
			cs.executeUpdate();
			stuName = cs.getString("stuName");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stuName;
	}

	/**
	 * 利用存储过程，插入一条学生信息
	 * @param student
	 * @return
	 */
	public static int insert(Student student) {
		int i = 0;
		Connection con = DbUtil.getCon();
		CallableStatement cs = null;
		try {
			cs = con.prepareCall("{call pro_insert(?,?,?,?)}");

			cs.setString(1, student.getStuName());
			cs.setString(2, student.getSex());
			cs.setInt(3, student.getAge());
			cs.setInt(4, student.getClassId());

			i = cs.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * 通过班级名称查找班级编号
	 * @param stuName
	 * @return
	 */
	public static int selectClassIdByClassName(String stuName){
		Connection con = DbUtil.getCon();
		PreparedStatement ps = null;
		int classId = -1;
		try {
			ps = con.prepareStatement("select * from stuclass where className = ?");
			ps.setString(1, stuName);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				classId = rs.getInt("classID");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return classId;
	}
}

<pre name="code" class="java">

import java.util.Scanner;

<span style="font-size:10px;">import com.company.exercise14.Dao.Student;</span>
<span style="font-size:10px;">import com.<span style="font-family: Arial, Helvetica, sans-serif;">company</span><span style="font-family: Arial, Helvetica, sans-serif;">.exercise14.impl.StudentImpl;</span></span>

/**
 * 添加学生信息时。应注意学生年龄，验证年龄合法性
	年龄不符合规范时。则提示不合法操作，可设置年龄列的约束条件
 * @author Administrator
 *
 */
public class Test {
	public static void main(String[] args) {
		Scanner sc=new Scanner(System.in);
		
		System.out.println("请输入学生姓名：");
		String inputName = sc.next();
		System.out.println("请输入学生性别：");
		String inputSex = sc.next();
		System.out.println("请输入学生年龄：");
		int inputAge = sc.nextInt();
		System.out.println("请输入学生班级");
		String inputClassName = sc.next();
		
		if(inputName.length() == 0 || inputName == null){
			System.out.println("学生姓名有误！");
			return;
		}
		if(!inputSex.equals("男") && !inputSex.equals("女")){
			System.out.println("学生性别输入有误(男/女)！");
			return;
		}
		if(inputAge <= 0 || inputAge>150){
			System.out.println("学生年龄不能小于0，不能大于150");
			return;
		}
		int classId = StudentImpl.selectClassIdByClassName(inputClassName);
		if(classId == -1){
			System.out.println("找不到该班级，请重新输入！");
			return;
		}
		Student stu = new Student(inputName,inputSex,inputAge,classId);
		
		int i=StudentImpl.insert(stu);
		System.out.println("影响了"+i+"行");
	}
}