mysql创造 存储过程 并通过java程序调用该存储过程
程序员文章站
2022-05-08 09:50:06
...
mysql创建 存储过程 并通过java程序调用该存储过程 create table users_ning(id primary key auto_increment,pwd int); insert into users_ning values(id,1234); insert into users_ning values(id,12345); insert into users_ning values(id,12); insert in
mysql创建 存储过程 并通过java程序调用该存储过程create table users_ning(id primary key auto_increment,pwd int); insert into users_ning values(id,1234); insert into users_ning values(id,12345); insert into users_ning values(id,12); insert into users_ning values(id,123); CREATE PROCEDURE login_ning(IN p_id int,IN p_pwd int,OUT flag int) BEGIN DECLARE v_pwd int; select pwd INTO v_pwd from users_ning where id = p_id; if v_pwd = p_pwd then set flag:=1; else select v_pwd; set flag := 0; end if; END package demo20130528; import java.sql.*; import demo20130526.DBUtils; /** * 测试JDBC API调用过程 * @author tarena * */ public class ProcedureDemo2 { /** * @param args * @throws Exception */ public static void main(String[] args) throws Exception { System.out.println(login(123, 1234)); } /** * 调用过程,实现登录功能 * @param id 考生id * @param pwd 考试密码 * @return if成功:1; if密码错:0; if没有用户:-1 * @throws Exception */ public static int login(int id, int pwd) throws Exception{ int flag = -1; String sql = "{call login_ning(?,?,?)}";//***** Connection conn = DBUtils.getConnMySQL(); CallableStatement stmt = null; try{ stmt = conn.prepareCall(sql); //传递输入参数 stmt.setInt(1, id); stmt.setInt(2, pwd); //注册输出参数,第三个占位符的数据类型是整型 stmt.registerOutParameter(3, Types.INTEGER);//***** //执行过程 stmt.execute(); //获得过程执行后的输出参数 flag = stmt.getInt(3);//***** }catch(Exception e){ e.printStackTrace(); }finally{ stmt.close(); DBUtils.dbClose(); } return flag; } }
package demo20130526; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class DBUtils { static Connection conn = null; static PreparedStatement stmt = null; static ResultSet rs = null; static Statement st = null; static String username = null; static String password = null; static String url = null; static String driverName = null; public static Connection getConnMySQL() throws Exception {// 连接mysql 返回conn getUrlUserNamePassWordClassNameMySQL(); conn = DriverManager.getConnection(url, username, password); // conn.setAutoCommit(false);设置自动提交为false return conn; } public static Connection getConnORCALE() throws Exception {// 连接orcale // 返回conn getUrlUserNamePassWordClassNameORCALE(); conn = DriverManager.getConnection(url, username, password); // conn.setAutoCommit(false); return conn; } private static void getUrlUserNamePassWordClassNameORCALE() throws Exception { // 从资源文件 获取 orcale的username password url等信息 Properties pro = new Properties(); File path = new File("src/all.properties"); pro.load(new FileInputStream(path)); String paths = pro.getProperty("filepath"); File file = new File(paths + "orcale.properties"); getFromProperties(file); } public static void getUrlUserNamePassWordClassNameMySQL() throws Exception { // 从资源文件 获取mysql的username password url等信息 Properties pro = new Properties(); File path = new File("src/all.properties"); pro.load(new FileInputStream(path)); String paths = pro.getProperty("filepath"); File file = new File(paths + "mysql.properties"); getFromProperties(file); } public static void getFromProperties(File file) throws IOException, FileNotFoundException, ClassNotFoundException {// 读资源文件的内容 Properties pro = new Properties(); pro.load(new FileInputStream(file)); username = pro.getProperty("username"); password = pro.getProperty("password"); url = pro.getProperty("url"); driverName = pro.getProperty("driverName"); Class.forName(driverName); } public static void dbClose() throws Exception {// 关闭所有 if (rs != null) rs.close(); if (st != null) st.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } public static ResultSet getById(String tableName, int id) throws Exception {// 用id来查询结果 st = conn.createStatement(); rs = st.executeQuery("select * from " + tableName + " where id=" + id + " "); return rs; } public static ResultSet getByAll(String sql, Object... obj) throws Exception {// 用关键字 实现查询 关键字额可以任意 sql = sql.replaceAll(";", ""); sql = sql.trim(); stmt = conn.prepareStatement(sql); String[] strs = sql.split("\\?");// 将sql 以? 非开 int num = strs.length;// 得到?的个数 int size = obj.length; for (int i = 1; i
上一篇: php怎么解决线程池的问题呢?
下一篇: php mysql查询数据
推荐阅读
-
Python通过调用mysql存储过程实现更新数据功能示例
-
创建一个存储过程,以部门号为参数,查询该部门的平均工资,并输出该部门中比平均工资高的员工号、员工名。并且调用该存储过程,要求根据输入部门编号,查询平均工资及输出比平均工资高的员工号、员工名
-
PHP 调用 MySQL 存储过程并获得返回值
-
Yii调用mysql存储过程,该怎么解决
-
Python简单调用MySQL存储过程并获得返回值的方法
-
java调用存储过程并封装成map
-
java调用存储过程并封装成map
-
php程序中调用mysql存储过程实例_PHP教程
-
Yii调用mysql存储过程,该如何解决
-
PHP调用MySQL存储过程并返回值的方法