- <pre name="code" class="html"> </pre><pre name="code" class="html"></pre><pre name="code" class="html">CREATE TABLE SCOTT.USERS_NING
- ( ID NUMBER,
- PWD NUMBER
- );
-
- insert into users_ning values(1,1234);
- insert into users_ning values(123,1234);
- 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
- 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 <= size; i++) {
- stmt.setObject(i, obj[i - 1]);// 数组下标从0开始
- }
- if (size < num) {
- for (int k = size + 1; k <= num; k++) {
- stmt.setObject(k, null);// 数组下标从0开始
- }
- }
- rs = stmt.executeQuery();
- return rs;
- }
-
- public static void doInsert(String sql) throws SQLException {// 传入 sql 语句
- // 实现插入操作
- st = conn.createStatement();
- st.execute(sql);
- }
-
- public static void doInsert(String sql, Object... args) throws Exception {// 传入参数
- // 利用
- // PreparedStatement
- // 实现插入
- // 传入的参数是任意多个 因为有Object 。。。args
- int size = args.length;// 获得 Object ...obj 传过来的参数的个数
- stmt = conn.prepareStatement(sql);
- for (int i = 1; i <= size; i++) {
- stmt.setObject(i, args[i - 1]);// 数组下标从0开始
- }
- stmt.execute();
- }
-
- public static int doUpdate(String sql) throws Exception {// 传入 sql 实现更新操作
- st = conn.createStatement();
- int num = st.executeUpdate(sql);
- return num;
- }
-
- public static void doUpdate(String sql, Object... obj) throws Exception {
- // 传入参数 利用 PreparedStatement实现更新
- // 传入的参数是任意多个 因为有Object 。。。args
- int size = obj.length;// 获得 Object ...obj 传过来的参数的个数
- stmt = conn.prepareStatement(sql);
- for (int i = 1; i <= size; i++) {
- stmt.setObject(i, obj[i - 1]);// 数组下标从0开始
- }
- stmt.executeUpdate(sql);
- }
-
- public static boolean doDeleteById(String tableName, int id)
- throws SQLException {// 删除记录 by id
- st = conn.createStatement();
- boolean b = st.execute("delete from " + tableName + " where id=" + id
- + "");
- return b;
- }
-
- public static boolean doDeleteByAll(String sql, Object... args)
- throws SQLException {// 删除记录 可以按任何关键字
- sql = sql.replaceAll(";", "");
- sql = sql.trim();
- stmt = conn.prepareStatement(sql);
- String[] strs = sql.split("\\?");// 将sql 以? 非开
- int num = strs.length;// 得到?的个数
- int size = args.length;
- for (int i = 1; i <= size; i++) {
- stmt.setObject(i, args[i - 1]);// 数组下标从0开始
- }
- if (size < num) {
- for (int k = size + 1; k <= num; k++) {
- stmt.setObject(k, null);// 数组下标从0开始
- }
- }
- boolean b = stmt.execute();
- return b;
- }
-
- public static void getMetaDate() throws Exception {// 获取数据库元素数据
- conn = DBUtils.getConnORCALE();
- DatabaseMetaData dmd = conn.getMetaData();
- System.out.println(dmd.getDatabaseMajorVersion());
- System.out.println(dmd.getDatabaseProductName());
- System.out.println(dmd.getDatabaseProductVersion());
- System.out.println(dmd.getDatabaseMinorVersion());
- }
-
- public static String[] getColumnNamesFromMySQL(String sql) throws Exception {
- conn = DBUtils.getConnMySQL();
- return getColumnName(sql);
-
- }
-
- public static String[] getColumnNamesFromOrcale(String sql)
- throws Exception {
- conn = DBUtils.getConnORCALE();
- return getColumnName(sql);
-
- }
-
- private static String[] getColumnName(String sql) throws Exception {// 返回表中所有的列名
- conn = DBUtils.getConnORCALE();
- st = conn.createStatement();
- rs = st.executeQuery(sql);
- ResultSetMetaData rsmd = rs.getMetaData();
- int num = rsmd.getColumnCount();
- System.out.println("ColumnCount=" + num);
- String[] strs = new String[num];
- // 显示列名
- for (int i = 1; i <= rsmd.getColumnCount(); i++) {
- String str = rsmd.getColumnName(i);
- strs[i - 1] = str;
- System.out.print(str + "\t");
- }
- return strs;
- }
-
- public static void getColumnDataFromMySQL(String sql) throws Exception {// 输出表中的数据
- conn = DBUtils.getConnMySQL();
- getColumnData(sql);
- }
-
- public static void getColumnDataFromORCALEL(String sql) throws Exception {// 输出表中的数据
- conn = DBUtils.getConnORCALE();
- getColumnData(sql);
- }
-
- public static void getColumnData(String sql) throws Exception {// 输出表中的数据
- st = conn.createStatement();
- rs = st.executeQuery(sql);
- ResultSetMetaData rsmd = rs.getMetaData();
- System.out
- .println("\n------------------------------------------------------------------------------------------------------------------------");
- while (rs.next()) {
- for (int i = 1; i <= rsmd.getColumnCount(); i++) {
- System.out.print(rs.getString(i) + "\t");
- }
- System.out.println();
- }
- System.out
- .println("------------------------------------------------------------------------------------------------------------------------");
-
- }
-
- public static void getTableDataFromOrcale(String sql) throws Exception {// 输出表的列名
- // 和表中的全部数据
- conn = DBUtils.getConnORCALE();
- getTableData(sql);
-
- }
-
- public static void getTableDataFromMysql(String sql) throws Exception {// 输出表的列名
- // 和表中的全部数据
- conn = DBUtils.getConnMySQL();
- getTableData(sql);
-
- }
-
- private static void getTableData(String sql) throws SQLException {
- // getTableDataFromMysql
- // getTableDataFromOrcale
- st = conn.createStatement();
- rs = st.executeQuery(sql);
- ResultSetMetaData rsmd = rs.getMetaData();
- int num = rsmd.getColumnCount();
- System.out.println("ColumnCount=" + num);
- String[] strs = new String[num];
- // 显示列名
- for (int i = 1; i <= rsmd.getColumnCount(); i++) {
- String str = rsmd.getColumnName(i);
- strs[i - 1] = str;
- System.out.print(str + "\t");
- }
- System.out
- .println("\n------------------------------------------------------------------------------------------------------------------------");
- while (rs.next()) {
- for (int i = 1; i <= rsmd.getColumnCount(); i++) {
- System.out.print(rs.getString(i) + "\t");
- }
- System.out.println();
- }
- System.out
- .println("------------------------------------------------------------------------------------------------------------------------");
- }
- }
- </pre><br>
- <br>
- <pre></pre>
- <pre name="code" class="sql"></pre><pre name="code" class="java"><pre name="code" class="java"><pre></pre>
- <pre></pre>
- <pre></pre>
- <pre></pre>
- <pre></pre>
- <pre></pre>
- <pre></pre>
- <pre></pre>
- <pre></pre>
- </pre></pre>
|