当前位置：Gxlcms > 数据库问题 > mysql创建 存储过程 并通过java程序调用该存储过程

mysql创建 存储过程 并通过java程序调用该存储过程

时间：2021-07-01 10:21:17 帮助过：11人阅读 

  1. create table users_ning(id primary key auto_increment,pwd int);
  2.  insert into users_ning values(id,1234);
  3.   insert into users_ning values(id,12345);
  4.  insert into users_ning values(id,12);
  5.  insert into users_ning values(id,123);
  6.   CREATE  PROCEDURE login_ning(IN p_id int,IN p_pwd int,OUT flag int)
  7. BEGIN
  8. DECLARE
  9. v_pwd int;
  10.   select pwd INTO v_pwd from users_ning
  11.   where id = p_id;
  12.  if v_pwd = p_pwd then
  13.       
  14. set flag:=1;
  15.   else 
  16. select v_pwd;
  17.   set flag := 0;
  18.   end if;
  19. END 
  20. package demo20130528;
  21. import java.sql.*;
  22. import demo20130526.DBUtils;
  23. /**
  24.  * 測试JDBC API调用过程
  25.  * @author tarena
  26.  *
  27.  */
  28. public class ProcedureDemo2 {
  29.   /**
  30.    * @param args
  31.  * @throws Exception 
  32.    */
  33.   public static void main(String[] args) throws Exception {
  34.     System.out.println(login(123, 1234));
  35.   }
  36.   /**
  37.    * 调用过程,实现登录功能
  38.    * @param id 考生id
  39.    * @param pwd 考试密码
  40.    * @return if成功:1; if密码错:0; if没实用户:-1
  41.  * @throws Exception 
  42.    */
  43.   public static int login(int id, int pwd) throws Exception{
  44.     int flag = -1;
  45.     String sql = "{call login_ning(?,?<p></p><p>,?)}";//*****
  46.     Connection conn = DBUtils.getConnMySQL();
  47.     CallableStatement stmt = null;
  48.     try{
  49.       stmt = conn.prepareCall(sql);
  50.       //传递输入參数
  51.       stmt.setInt(1, id);
  52.       stmt.setInt(2, pwd);
  53.       //注冊输出參数,第三个占位符的数据类型是整型
  54.       stmt.registerOutParameter(3, Types.INTEGER);//*****
  55.       //运行过程
  56.       stmt.execute();
  57.       //获得过程运行后的输出參数
  58.       flag = stmt.getInt(3);//*****
  59.       
  60.     }catch(Exception e){
  61.       e.printStackTrace();
  62.     }finally{
  63.     stmt.close();
  64.     DBUtils.dbClose();
  65.     }
  66.     
  67.     
  68.     return flag;
  69.   }
  70. }
  71. </p>
  1. </pre><pre name="code" class="java">
  1. </pre><pre name="code" class="java">
  1. package demo20130526;
  2. import java.io.File;
  3. import java.io.FileInputStream;
  4. import java.io.FileNotFoundException;
  5. import java.io.IOException;
  6. import java.sql.Connection;
  7. import java.sql.DatabaseMetaData;
  8. import java.sql.DriverManager;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.ResultSetMetaData;
  12. import java.sql.SQLException;
  13. import java.sql.Statement;
  14. import java.util.Properties;
  15. public class DBUtils {
  16. <span style="white-space:pre">
  17. </span>static Connection conn = null;
  18. <span style="white-space:pre">
  19. </span>static PreparedStatement stmt = null;
  20. <span style="white-space:pre">
  21. </span>static ResultSet rs = null;
  22. <span style="white-space:pre">
  23. </span>static Statement st = null;
  24. <span style="white-space:pre">
  25. </span>static String username = null;
  26. <span style="white-space:pre">
  27. </span>static String password = null;
  28. <span style="white-space:pre">
  29. </span>static String url = null;
  30. <span style="white-space:pre">
  31. </span>static String driverName = null;
  32. <span style="white-space:pre">
  33. </span>public static Connection getConnMySQL() throws Exception {// 连接mysql 返回conn
  34. <span style="white-space:pre">
  35. </span>getUrlUserNamePassWordClassNameMySQL();
  36. <span style="white-space:pre">
  37. </span>conn = DriverManager.getConnection(url, username, password);
  38. <span style="white-space:pre">
  39. </span>// conn.setAutoCommit(false);设置自己主动提交为false
  40. <span style="white-space:pre">
  41. </span>return conn;
  42. <span style="white-space:pre">
  43. </span>}
  44. <span style="white-space:pre">
  45. </span>public static Connection getConnORCALE() throws Exception {// 连接orcale
  46. <span style="white-space:pre">
  47. </span>// 返回conn
  48. <span style="white-space:pre">
  49. </span>getUrlUserNamePassWordClassNameORCALE();
  50. <span style="white-space:pre">
  51. </span>conn = DriverManager.getConnection(url, username, password);
  52. <span style="white-space:pre">
  53. </span>// conn.setAutoCommit(false);
  54. <span style="white-space:pre">
  55. </span>return conn;
  56. <span style="white-space:pre">
  57. </span>}
  58. <span style="white-space:pre">
  59. </span>private static void getUrlUserNamePassWordClassNameORCALE()
  60. <span style="white-space:pre">
  61. </span>throws Exception {
  62. <span style="white-space:pre">
  63. </span>// 从资源文件 获取 orcale的username password url等信息
  64. <span style="white-space:pre">
  65. </span>Properties pro = new Properties();
  66. <span style="white-space:pre">
  67. </span>File path = new File("src/all.properties");
  68. <span style="white-space:pre">
  69. </span>pro.load(new FileInputStream(path));
  70. <span style="white-space:pre">
  71. </span>String paths = pro.getProperty("filepath");
  72. <span style="white-space:pre">
  73. </span>File file = new File(paths + "orcale.properties");
  74. <span style="white-space:pre">
  75. </span>getFromProperties(file);
  76. <span style="white-space:pre">
  77. </span>}
  78. <span style="white-space:pre">
  79. </span>public static void getUrlUserNamePassWordClassNameMySQL() throws Exception {
  80. <span style="white-space:pre">
  81. </span>// 从资源文件 获取mysql的username password url等信息
  82. <span style="white-space:pre">
  83. </span>Properties pro = new Properties();
  84. <span style="white-space:pre">
  85. </span>File path = new File("src/all.properties");
  86. <span style="white-space:pre">
  87. </span>pro.load(new FileInputStream(path));
  88. <span style="white-space:pre">
  89. </span>String paths = pro.getProperty("filepath");
  90. <span style="white-space:pre">
  91. </span>File file = new File(paths + "mysql.properties");
  92. <span style="white-space:pre">
  93. </span>getFromProperties(file);
  94. <span style="white-space:pre">
  95. </span>}
  96. <span style="white-space:pre">
  97. </span>public static void getFromProperties(File file) throws IOException,
  98. <span style="white-space:pre">
  99. </span>FileNotFoundException, ClassNotFoundException {// 读资源文件的内容
  100. <span style="white-space:pre">
  101. </span>Properties pro = new Properties();
  102. <span style="white-space:pre">
  103. </span>pro.load(new FileInputStream(file));
  104. <span style="white-space:pre">
  105. </span>username = pro.getProperty("username");
  106. <span style="white-space:pre">
  107. </span>password = pro.getProperty("password");
  108. <span style="white-space:pre">
  109. </span>url = pro.getProperty("url");
  110. <span style="white-space:pre">
  111. </span>driverName = pro.getProperty("driverName");
  112. <span style="white-space:pre">
  113. </span>Class.forName(driverName);
  114. <span style="white-space:pre">
  115. </span>}
  116. <span style="white-space:pre">
  117. </span>public static void dbClose() throws Exception {// 关闭所有
  118. <span style="white-space:pre">
  119. </span>if (rs != null)
  120. <span style="white-space:pre">
  121. </span>rs.close();
  122. <span style="white-space:pre">
  123. </span>if (st != null)
  124. <span style="white-space:pre">
  125. </span>st.close();
  126. <span style="white-space:pre">
  127. </span>if (stmt != null)
  128. <span style="white-space:pre">
  129. </span>stmt.close();
  130. <span style="white-space:pre">
  131. </span>if (conn != null)
  132. <span style="white-space:pre">
  133. </span>conn.close();
  134. <span style="white-space:pre">
  135. </span>}
  136. <span style="white-space:pre">
  137. </span>public static ResultSet getById(String tableName, int id) throws Exception {// 用id来查询结果
  138. <span style="white-space:pre">
  139. </span>st = conn.createStatement();
  140. <span style="white-space:pre">
  141. </span>rs = st.executeQuery("select * from " + tableName + "  where id=" + id
  142. <span style="white-space:pre">
  143. </span>+ " ");
  144. <span style="white-space:pre">
  145. </span>return rs;
  146. <span style="white-space:pre">
  147. </span>}
  148. <span style="white-space:pre">
  149. </span>public static ResultSet getByAll(String sql, Object... obj)
  150. <span style="white-space:pre">
  151. </span>throws Exception {// 用keyword 实现查询 keyword额能够随意
  152. <span style="white-space:pre">
  153. </span>sql = sql.replaceAll(";", "");
  154. <span style="white-space:pre">
  155. </span>sql = sql.trim();
  156. <span style="white-space:pre">
  157. </span>stmt = conn.prepareStatement(sql);
  158. <span style="white-space:pre">
  159. </span>String[] strs = sql.split("\\?<p></p><p>");// 将sql 以? 非开
  160. <span style="white-space:pre">
  161. </span>int num = strs.length;// 得到?</p><p>的个数
  162. <span style="white-space:pre">
  163. </span>int size = obj.length;
  164. <span style="white-space:pre">
  165. </span>for (int i = 1; i <= size; i++) {
  166. <span style="white-space:pre">
  167. </span>stmt.setObject(i, obj[i - 1]);// 数组下标从0開始
  168. <span style="white-space:pre">
  169. </span>}
  170. <span style="white-space:pre">
  171. </span>if (size < num) {
  172. <span style="white-space:pre">
  173. </span>for (int k = size + 1; k <= num; k++) {
  174. <span style="white-space:pre">
  175. </span>stmt.setObject(k, null);// 数组下标从0開始
  176. <span style="white-space:pre">
  177. </span>}
  178. <span style="white-space:pre">
  179. </span>}
  180. <span style="white-space:pre">
  181. </span>rs = stmt.executeQuery();
  182. <span style="white-space:pre">
  183. </span>return rs;
  184. <span style="white-space:pre">
  185. </span>}
  186. <span style="white-space:pre">
  187. </span>public static void doInsert(String sql) throws SQLException {// 传入 sql 语句
  188. <span style="white-space:pre">
  189. </span>// 实现插入操作
  190. <span style="white-space:pre">
  191. </span>st = conn.createStatement();
  192. <span style="white-space:pre">
  193. </span>st.execute(sql);
  194. <span style="white-space:pre">
  195. </span>}
  196. <span style="white-space:pre">
  197. </span>public static void doInsert(String sql, Object... args) throws Exception {// 传入參数
  198. <span style="white-space:pre">
  199. </span>// 利用
  200. <span style="white-space:pre">
  201. </span>// PreparedStatement
  202. <span style="white-space:pre">
  203. </span>// 实现插入
  204. <span style="white-space:pre">
  205. </span>// 传入的參数是随意多个 由于有Object 。。。</p><p>args
  206. <span style="white-space:pre">
  207. </span>int size = args.length;// 获得 Object ...obj 传过来的參数的个数
  208. <span style="white-space:pre">
  209. </span>stmt = conn.prepareStatement(sql);
  210. <span style="white-space:pre">
  211. </span>for (int i = 1; i <= size; i++) {
  212. <span style="white-space:pre">
  213. </span>stmt.setObject(i, args[i - 1]);// 数组下标从0開始
  214. <span style="white-space:pre">
  215. </span>}
  216. <span style="white-space:pre">
  217. </span>stmt.execute();
  218. <span style="white-space:pre">
  219. </span>}
  220. <span style="white-space:pre">
  221. </span>public static int doUpdate(String sql) throws Exception {// 传入 sql 实现更新操作
  222. <span style="white-space:pre">
  223. </span>st = conn.createStatement();
  224. <span style="white-space:pre">
  225. </span>int num = st.executeUpdate(sql);
  226. <span style="white-space:pre">
  227. </span>return num;
  228. <span style="white-space:pre">
  229. </span>}
  230. <span style="white-space:pre">
  231. </span>public static void doUpdate(String sql, Object... obj) throws Exception {
  232. <span style="white-space:pre">
  233. </span>// 传入參数 利用 PreparedStatement实现更新
  234. <span style="white-space:pre">
  235. </span>// 传入的參数是随意多个 由于有Object 。。</p><p>。args
  236. <span style="white-space:pre">
  237. </span>int size = obj.length;// 获得 Object ...obj 传过来的參数的个数
  238. <span style="white-space:pre">
  239. </span>stmt = conn.prepareStatement(sql);
  240. <span style="white-space:pre">
  241. </span>for (int i = 1; i <= size; i++) {
  242. <span style="white-space:pre">
  243. </span>stmt.setObject(i, obj[i - 1]);// 数组下标从0開始
  244. <span style="white-space:pre">
  245. </span>}
  246. <span style="white-space:pre">
  247. </span>stmt.executeUpdate(sql);
  248. <span style="white-space:pre">
  249. </span>}
  250. <span style="white-space:pre">
  251. </span>public static boolean doDeleteById(String tableName, int id)
  252. <span style="white-space:pre">
  253. </span>throws SQLException {// 删除记录 by id
  254. <span style="white-space:pre">
  255. </span>st = conn.createStatement();
  256. <span style="white-space:pre">
  257. </span>boolean b = st.execute("delete from " + tableName + " where id=" + id
  258. <span style="white-space:pre">
  259. </span>+ "");
  260. <span style="white-space:pre">
  261. </span>return b;
  262. <span style="white-space:pre">
  263. </span>}
  264. <span style="white-space:pre">
  265. </span>public static boolean doDeleteByAll(String sql, Object... args)
  266. <span style="white-space:pre">
  267. </span>throws SQLException {// 删除记录 能够按不论什么keyword
  268. <span style="white-space:pre">
  269. </span>sql = sql.replaceAll(";", "");
  270. <span style="white-space:pre">
  271. </span>sql = sql.trim();
  272. <span style="white-space:pre">
  273. </span>stmt = conn.prepareStatement(sql);
  274. <span style="white-space:pre">
  275. </span>String[] strs = sql.split("\\?</p><p>");// 将sql 以?</p><p> 非开
  276. <span style="white-space:pre">
  277. </span>int num = strs.length;// 得到?的个数
  278. <span style="white-space:pre">
  279. </span>int size = args.length;
  280. <span style="white-space:pre">
  281. </span>for (int i = 1; i <= size; i++) {
  282. <span style="white-space:pre">
  283. </span>stmt.setObject(i, args[i - 1]);// 数组下标从0開始
  284. <span style="white-space:pre">
  285. </span>}
  286. <span style="white-space:pre">
  287. </span>if (size < num) {
  288. <span style="white-space:pre">
  289. </span>for (int k = size + 1; k <= num; k++) {
  290. <span style="white-space:pre">
  291. </span>stmt.setObject(k, null);// 数组下标从0開始
  292. <span style="white-space:pre">
  293. </span>}
  294. <span style="white-space:pre">
  295. </span>}
  296. <span style="white-space:pre">
  297. </span>boolean b = stmt.execute();
  298. <span style="white-space:pre">
  299. </span>return b;
  300. <span style="white-space:pre">
  301. </span>}
  302. <span style="white-space:pre">
  303. </span>public static void getMetaDate() throws Exception {// 获取数据库元素数据
  304. <span style="white-space:pre">
  305. </span>conn = DBUtils.getConnORCALE();
  306. <span style="white-space:pre">
  307. </span>DatabaseMetaData dmd = conn.getMetaData();
  308. <span style="white-space:pre">
  309. </span>System.out.println(dmd.getDatabaseMajorVersion());
  310. <span style="white-space:pre">
  311. </span>System.out.println(dmd.getDatabaseProductName());
  312. <span style="white-space:pre">
  313. </span>System.out.println(dmd.getDatabaseProductVersion());
  314. <span style="white-space:pre">
  315. </span>System.out.println(dmd.getDatabaseMinorVersion());
  316. <span style="white-space:pre">
  317. </span>}
  318. <span style="white-space:pre">
  319. </span>public static String[] getColumnNamesFromMySQL(String sql) throws Exception {
  320. <span style="white-space:pre">
  321. </span>conn = DBUtils.getConnMySQL();
  322. <span style="white-space:pre">
  323. </span>return getColumnName(sql);
  324. <span style="white-space:pre">
  325. </span>}
  326. <span style="white-space:pre">
  327. </span>public static String[] getColumnNamesFromOrcale(String sql)
  328. <span style="white-space:pre">
  329. </span>throws Exception {
  330. <span style="white-space:pre">
  331. </span>conn = DBUtils.getConnORCALE();
  332. <span style="white-space:pre">
  333. </span>return getColumnName(sql);
  334. <span style="white-space:pre">
  335. </span>}
  336. <span style="white-space:pre">
  337. </span>private static String[] getColumnName(String sql) throws Exception {// 返回表中所有的列名
  338. <span style="white-space:pre">
  339. </span>conn = DBUtils.getConnORCALE();
  340. <span style="white-space:pre">
  341. </span>st = conn.createStatement();
  342. <span style="white-space:pre">
  343. </span>rs = st.executeQuery(sql);
  344. <span style="white-space:pre">
  345. </span>ResultSetMetaData rsmd = rs.getMetaData();
  346. <span style="white-space:pre">
  347. </span>int num = rsmd.getColumnCount();
  348. <span style="white-space:pre">
  349. </span>System.out.println("ColumnCount=" + num);
  350. <span style="white-space:pre">
  351. </span>String[] strs = new String[num];
  352. <span style="white-space:pre">
  353. </span>// 显示列名
  354. <span style="white-space:pre">
  355. </span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
  356. <span style="white-space:pre">
  357. </span>String str = rsmd.getColumnName(i);
  358. <span style="white-space:pre">
  359. </span>strs[i - 1] = str;
  360. <span style="white-space:pre">
  361. </span>System.out.print(str + "\t");
  362. <span style="white-space:pre">
  363. </span>}
  364. <span style="white-space:pre">
  365. </span>return strs;
  366. <span style="white-space:pre">
  367. </span>}
  368. <span style="white-space:pre">
  369. </span>public static void getColumnDataFromMySQL(String sql) throws Exception {// 输出表中的数据
  370. <span style="white-space:pre">
  371. </span>conn = DBUtils.getConnMySQL();
  372. <span style="white-space:pre">
  373. </span>getColumnData(sql);
  374. <span style="white-space:pre">
  375. </span>}
  376. <span style="white-space:pre">
  377. </span>public static void getColumnDataFromORCALEL(String sql) throws Exception {// 输出表中的数据
  378. <span style="white-space:pre">
  379. </span>conn = DBUtils.getConnORCALE();
  380. <span style="white-space:pre">
  381. </span>getColumnData(sql);
  382. <span style="white-space:pre">
  383. </span>}
  384. <span style="white-space:pre">
  385. </span>public static void getColumnData(String sql) throws Exception {// 输出表中的数据
  386. <span style="white-space:pre">
  387. </span>st = conn.createStatement();
  388. <span style="white-space:pre">
  389. </span>rs = st.executeQuery(sql);
  390. <span style="white-space:pre">
  391. </span>ResultSetMetaData rsmd = rs.getMetaData();
  392. <span style="white-space:pre">
  393. </span>System.out
  394. <span style="white-space:pre">
  395. </span>.println("\n------------------------------------------------------------------------------------------------------------------------");
  396. <span style="white-space:pre">
  397. </span>while (rs.next()) {
  398. <span style="white-space:pre">
  399. </span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
  400. <span style="white-space:pre">
  401. </span>System.out.print(rs.getString(i) + "\t");
  402. <span style="white-space:pre">
  403. </span>}
  404. <span style="white-space:pre">
  405. </span>System.out.println();
  406. <span style="white-space:pre">
  407. </span>}
  408. <span style="white-space:pre">
  409. </span>System.out
  410. <span style="white-space:pre">
  411. </span>.println("------------------------------------------------------------------------------------------------------------------------");
  412. <span style="white-space:pre">
  413. </span>}
  414. <span style="white-space:pre">
  415. </span>public static void getTableDataFromOrcale(String sql) throws Exception {// 输出表的列名
  416. <span style="white-space:pre">
  417. </span>// 和表中的所有数据
  418. <span style="white-space:pre">
  419. </span>conn = DBUtils.getConnORCALE();
  420. <span style="white-space:pre">
  421. </span>getTableData(sql);
  422. <span style="white-space:pre">
  423. </span>}
  424. <span style="white-space:pre">
  425. </span>public static void getTableDataFromMysql(String sql) throws Exception {// 输出表的列名
  426. <span style="white-space:pre">
  427. </span>// 和表中的所有数据
  428. <span style="white-space:pre">
  429. </span>conn = DBUtils.getConnMySQL();
  430. <span style="white-space:pre">
  431. </span>getTableData(sql);
  432. <span style="white-space:pre">
  433. </span>}
  434. <span style="white-space:pre">
  435. </span>private static void getTableData(String sql) throws SQLException {
  436. <span style="white-space:pre">
  437. </span>// getTableDataFromMysql
  438. <span style="white-space:pre">
  439. </span>// getTableDataFromOrcale
  440. <span style="white-space:pre">
  441. </span>st = conn.createStatement();
  442. <span style="white-space:pre">
  443. </span>rs = st.executeQuery(sql);
  444. <span style="white-space:pre">
  445. </span>ResultSetMetaData rsmd = rs.getMetaData();
  446. <span style="white-space:pre">
  447. </span>int num = rsmd.getColumnCount();
  448. <span style="white-space:pre">
  449. </span>System.out.println("ColumnCount=" + num);
  450. <span style="white-space:pre">
  451. </span>String[] strs = new String[num];
  452. <span style="white-space:pre">
  453. </span>// 显示列名
  454. <span style="white-space:pre">
  455. </span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
  456. <span style="white-space:pre">
  457. </span>String str = rsmd.getColumnName(i);
  458. <span style="white-space:pre">
  459. </span>strs[i - 1] = str;
  460. <span style="white-space:pre">
  461. </span>System.out.print(str + "\t");
  462. <span style="white-space:pre">
  463. </span>}
  464. <span style="white-space:pre">
  465. </span>System.out
  466. <span style="white-space:pre">
  467. </span>.println("\n------------------------------------------------------------------------------------------------------------------------");
  468. <span style="white-space:pre">
  469. </span>while (rs.next()) {
  470. <span style="white-space:pre">
  471. </span>for (int i = 1; i <= rsmd.getColumnCount(); i++) {
  472. <span style="white-space:pre">
  473. </span>System.out.print(rs.getString(i) + "\t");
  474. <span style="white-space:pre">
  475. </span>}
  476. <span style="white-space:pre">
  477. </span>System.out.println();
  478. <span style="white-space:pre">
  479. </span>}
  480. <span style="white-space:pre">
  481. </span>System.out
  482. <span style="white-space:pre">
  483. </span>.println("------------------------------------------------------------------------------------------------------------------------");
  484. <span style="white-space:pre">
  485. </span>}
  486. }
  487. </p>


mysql创建 存储过程 并通过java程序调用该存储过程

标签：删除   base   api   register   cut   ati   import   cti   comm   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！