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

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

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

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

CREATE procedure [dbo].[pro_selectById]@sId int,@name varchar(20) outputasbeginselect @name=stuName from [dbo].[Student] where sid = @sIdend

创建好数据库之后，开始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;}@Overridepublic 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 blocke.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 blocke.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 blocke.printStackTrace();}}if (ps != null) {try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (st != null) {try {st.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.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 blocke.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+"行");}}