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 {
<span style="white-space:pre"> </span>static Connection conn = null;
<span style="white-space:pre"> </span>static PreparedStatement stmt = null;
<span style="white-space:pre"> </span>static ResultSet rs = null;
<span style="white-space:pre"> </span>static Statement st = null;
<span style="white-space:pre"> </span>static String username = null;
<span style="white-space:pre"> </span>static String password = null;
<span style="white-space:pre"> </span>static String url = null;
<span style="white-space:pre"> </span>static String driverName = null;
<span style="white-space:pre"> </span>public static Connection getConnMySQL() throws Exception {// 连接mysql 返回conn
<span style="white-space:pre"> </span>getUrlUserNamePassWordClassNameMySQL();
<span style="white-space:pre"> </span>conn = DriverManager.getConnection(url, username, password);
<span style="white-space:pre"> </span>// conn.setAutoCommit(false);设置自动提交为false
<span style="white-space:pre"> </span>return conn;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static Connection getConnORCALE() throws Exception {// 连接orcale
<span style="white-space:pre"> </span>// 返回conn
<span style="white-space:pre"> </span>getUrlUserNamePassWordClassNameORCALE();
<span style="white-space:pre"> </span>conn = DriverManager.getConnection(url, username, password);
<span style="white-space:pre"> </span>// conn.setAutoCommit(false);
<span style="white-space:pre"> </span>return conn;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>private static void getUrlUserNamePassWordClassNameORCALE()
<span style="white-space:pre"> </span>throws Exception {
<span style="white-space:pre"> </span>// 从资源文件 获取 orcale的username password url等信息
<span style="white-space:pre"> </span>Properties pro = new Properties();
<span style="white-space:pre"> </span>File path = new File("src/all.properties");
<span style="white-space:pre"> </span>pro.load(new FileInputStream(path));
<span style="white-space:pre"> </span>String paths = pro.getProperty("filepath");
<span style="white-space:pre"> </span>File file = new File(paths + "orcale.properties");
<span style="white-space:pre"> </span>getFromProperties(file);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getUrlUserNamePassWordClassNameMySQL() throws Exception {
<span style="white-space:pre"> </span>// 从资源文件 获取mysql的username password url等信息
<span style="white-space:pre"> </span>Properties pro = new Properties();
<span style="white-space:pre"> </span>File path = new File("src/all.properties");
<span style="white-space:pre"> </span>pro.load(new FileInputStream(path));
<span style="white-space:pre"> </span>String paths = pro.getProperty("filepath");
<span style="white-space:pre"> </span>File file = new File(paths + "mysql.properties");
<span style="white-space:pre"> </span>getFromProperties(file);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getFromProperties(File file) throws IOException,
<span style="white-space:pre"> </span>FileNotFoundException, ClassNotFoundException {// 读资源文件的内容
<span style="white-space:pre"> </span>Properties pro = new Properties();
<span style="white-space:pre"> </span>pro.load(new FileInputStream(file));
<span style="white-space:pre"> </span>username = pro.getProperty("username");
<span style="white-space:pre"> </span>password = pro.getProperty("password");
<span style="white-space:pre"> </span>url = pro.getProperty("url");
<span style="white-space:pre"> </span>driverName = pro.getProperty("driverName");
<span style="white-space:pre"> </span>Class.forName(driverName);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void dbClose() throws Exception {// 关闭所有
<span style="white-space:pre"> </span>if (rs != null)
<span style="white-space:pre"> </span>rs.close();
<span style="white-space:pre"> </span>if (st != null)
<span style="white-space:pre"> </span>st.close();
<span style="white-space:pre"> </span>if (stmt != null)
<span style="white-space:pre"> </span>stmt.close();
<span style="white-space:pre"> </span>if (conn != null)
<span style="white-space:pre"> </span>conn.close();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static ResultSet getById(String tableName, int id) throws Exception {// 用id来查询结果
<span style="white-space:pre"> </span>st = conn.createStatement();
<span style="white-space:pre"> </span>rs = st.executeQuery("select * from " + tableName + " where id=" + id
<span style="white-space:pre"> </span>+ " ");
<span style="white-space:pre"> </span>return rs;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static ResultSet getByAll(String sql, Object... obj)
<span style="white-space:pre"> </span>throws Exception {// 用关键字 实现查询 关键字额可以任意
<span style="white-space:pre"> </span>sql = sql.replaceAll(";", "");
<span style="white-space:pre"> </span>sql = sql.trim();
<span style="white-space:pre"> </span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre"> </span>String[] strs = sql.split("\\?");// 将sql 以? 非开
<span style="white-space:pre"> </span>int num = strs.length;// 得到?的个数
<span style="white-space:pre"> </span>int size = obj.length;
<span style="white-space:pre"> </span>for (int i = 1; i stmt.setObject(i, obj[i - 1]);// 数组下标从0开始
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>if (size for (int k = size + 1; k stmt.setObject(k, null);// 数组下标从0开始
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>rs = stmt.executeQuery();
<span style="white-space:pre"> </span>return rs;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void doInsert(String sql) throws SQLException {// 传入 sql 语句
<span style="white-space:pre"> </span>// 实现插入操作
<span style="white-space:pre"> </span>st = conn.createStatement();
<span style="white-space:pre"> </span>st.execute(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void doInsert(String sql, Object... args) throws Exception {// 传入参数
<span style="white-space:pre"> </span>// 利用
<span style="white-space:pre"> </span>// PreparedStatement
<span style="white-space:pre"> </span>// 实现插入
<span style="white-space:pre"> </span>// 传入的参数是任意多个 因为有Object 。。。args
<span style="white-space:pre"> </span>int size = args.length;// 获得 Object ...obj 传过来的参数的个数
<span style="white-space:pre"> </span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre"> </span>for (int i = 1; i stmt.setObject(i, args[i - 1]);// 数组下标从0开始
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>stmt.execute();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static int doUpdate(String sql) throws Exception {// 传入 sql 实现更新操作
<span style="white-space:pre"> </span>st = conn.createStatement();
<span style="white-space:pre"> </span>int num = st.executeUpdate(sql);
<span style="white-space:pre"> </span>return num;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void doUpdate(String sql, Object... obj) throws Exception {
<span style="white-space:pre"> </span>// 传入参数 利用 PreparedStatement实现更新
<span style="white-space:pre"> </span>// 传入的参数是任意多个 因为有Object 。。。args
<span style="white-space:pre"> </span>int size = obj.length;// 获得 Object ...obj 传过来的参数的个数
<span style="white-space:pre"> </span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre"> </span>for (int i = 1; i stmt.setObject(i, obj[i - 1]);// 数组下标从0开始
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>stmt.executeUpdate(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static boolean doDeleteById(String tableName, int id)
<span style="white-space:pre"> </span>throws SQLException {// 删除记录 by id
<span style="white-space:pre"> </span>st = conn.createStatement();
<span style="white-space:pre"> </span>boolean b = st.execute("delete from " + tableName + " where id=" + id
<span style="white-space:pre"> </span>+ "");
<span style="white-space:pre"> </span>return b;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static boolean doDeleteByAll(String sql, Object... args)
<span style="white-space:pre"> </span>throws SQLException {// 删除记录 可以按任何关键字
<span style="white-space:pre"> </span>sql = sql.replaceAll(";", "");
<span style="white-space:pre"> </span>sql = sql.trim();
<span style="white-space:pre"> </span>stmt = conn.prepareStatement(sql);
<span style="white-space:pre"> </span>String[] strs = sql.split("\\?");// 将sql 以? 非开
<span style="white-space:pre"> </span>int num = strs.length;// 得到?的个数
<span style="white-space:pre"> </span>int size = args.length;
<span style="white-space:pre"> </span>for (int i = 1; i stmt.setObject(i, args[i - 1]);// 数组下标从0开始
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>if (size for (int k = size + 1; k stmt.setObject(k, null);// 数组下标从0开始
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>boolean b = stmt.execute();
<span style="white-space:pre"> </span>return b;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getMetaDate() throws Exception {// 获取数据库元素数据
<span style="white-space:pre"> </span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre"> </span>DatabaseMetaData dmd = conn.getMetaData();
<span style="white-space:pre"> </span>System.out.println(dmd.getDatabaseMajorVersion());
<span style="white-space:pre"> </span>System.out.println(dmd.getDatabaseProductName());
<span style="white-space:pre"> </span>System.out.println(dmd.getDatabaseProductVersion());
<span style="white-space:pre"> </span>System.out.println(dmd.getDatabaseMinorVersion());
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static String[] getColumnNamesFromMySQL(String sql) throws Exception {
<span style="white-space:pre"> </span>conn = DBUtils.getConnMySQL();
<span style="white-space:pre"> </span>return getColumnName(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static String[] getColumnNamesFromOrcale(String sql)
<span style="white-space:pre"> </span>throws Exception {
<span style="white-space:pre"> </span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre"> </span>return getColumnName(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>private static String[] getColumnName(String sql) throws Exception {// 返回表中所有的列名
<span style="white-space:pre"> </span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre"> </span>st = conn.createStatement();
<span style="white-space:pre"> </span>rs = st.executeQuery(sql);
<span style="white-space:pre"> </span>ResultSetMetaData rsmd = rs.getMetaData();
<span style="white-space:pre"> </span>int num = rsmd.getColumnCount();
<span style="white-space:pre"> </span>System.out.println("ColumnCount=" + num);
<span style="white-space:pre"> </span>String[] strs = new String[num];
<span style="white-space:pre"> </span>// 显示列名
<span style="white-space:pre"> </span>for (int i = 1; i String str = rsmd.getColumnName(i);
<span style="white-space:pre"> </span>strs[i - 1] = str;
<span style="white-space:pre"> </span>System.out.print(str + "\t");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>return strs;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getColumnDataFromMySQL(String sql) throws Exception {// 输出表中的数据
<span style="white-space:pre"> </span>conn = DBUtils.getConnMySQL();
<span style="white-space:pre"> </span>getColumnData(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getColumnDataFromORCALEL(String sql) throws Exception {// 输出表中的数据
<span style="white-space:pre"> </span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre"> </span>getColumnData(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getColumnData(String sql) throws Exception {// 输出表中的数据
<span style="white-space:pre"> </span>st = conn.createStatement();
<span style="white-space:pre"> </span>rs = st.executeQuery(sql);
<span style="white-space:pre"> </span>ResultSetMetaData rsmd = rs.getMetaData();
<span style="white-space:pre"> </span>System.out
<span style="white-space:pre"> </span>.println("\n------------------------------------------------------------------------------------------------------------------------");
<span style="white-space:pre"> </span>while (rs.next()) {
<span style="white-space:pre"> </span>for (int i = 1; i System.out.print(rs.getString(i) + "\t");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out.println();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out
<span style="white-space:pre"> </span>.println("------------------------------------------------------------------------------------------------------------------------");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getTableDataFromOrcale(String sql) throws Exception {// 输出表的列名
<span style="white-space:pre"> </span>// 和表中的全部数据
<span style="white-space:pre"> </span>conn = DBUtils.getConnORCALE();
<span style="white-space:pre"> </span>getTableData(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public static void getTableDataFromMysql(String sql) throws Exception {// 输出表的列名
<span style="white-space:pre"> </span>// 和表中的全部数据
<span style="white-space:pre"> </span>conn = DBUtils.getConnMySQL();
<span style="white-space:pre"> </span>getTableData(sql);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>private static void getTableData(String sql) throws SQLException {
<span style="white-space:pre"> </span>// getTableDataFromMysql
<span style="white-space:pre"> </span>// getTableDataFromOrcale
<span style="white-space:pre"> </span>st = conn.createStatement();
<span style="white-space:pre"> </span>rs = st.executeQuery(sql);
<span style="white-space:pre"> </span>ResultSetMetaData rsmd = rs.getMetaData();
<span style="white-space:pre"> </span>int num = rsmd.getColumnCount();
<span style="white-space:pre"> </span>System.out.println("ColumnCount=" + num);
<span style="white-space:pre"> </span>String[] strs = new String[num];
<span style="white-space:pre"> </span>// 显示列名
<span style="white-space:pre"> </span>for (int i = 1; i String str = rsmd.getColumnName(i);
<span style="white-space:pre"> </span>strs[i - 1] = str;
<span style="white-space:pre"> </span>System.out.print(str + "\t");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out
<span style="white-space:pre"> </span>.println("\n------------------------------------------------------------------------------------------------------------------------");
<span style="white-space:pre"> </span>while (rs.next()) {
<span style="white-space:pre"> </span>for (int i = 1; i System.out.print(rs.getString(i) + "\t");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out.println();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out
<span style="white-space:pre"> </span>.println("------------------------------------------------------------------------------------------------------------------------");
<span style="white-space:pre"> </span>}
}
성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
图文详解mysql架构原理May 17, 2022 pm 05:54 PM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于架构原理的相关内容，MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层，下面一起来看一下，希望对大家有帮助。
mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM在mysql中，可以利用char()和REPLACE()函数来替换换行符；REPLACE()函数可以用新字符串替换列中的换行符，而换行符可使用“char(13)”来表示，语法为“replace(字段名,char(13),'新字符串') ”。
mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PMmysql的msi与zip版本的区别：1、zip包含的安装程序是一种主动安装，而msi包含的是被installer所用的安装文件以提交请求的方式安装；2、zip是一种数据压缩和文档存储的文件格式，msi是微软格式的安装包。
mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM方法：1、利用right函数，语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”；2、利用substring函数，语法为“select substring(指定字段,2)..”。
mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM转换方法：1、利用cast函数，语法“select * from 表名 order by cast(字段名 as SIGNED)”；2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。
MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于MySQL复制技术的相关问题，包括了异步复制、半同步复制等等内容，下面一起来看一下，希望对大家有帮助。
带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了mysql高级篇的一些问题，包括了索引是什么、索引底层实现等等问题，下面一起来看一下，希望对大家有帮助。
mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM在mysql中，可以利用REGEXP运算符判断数据是否是数字类型，语法为“String REGEXP '[^0-9.]'”；该运算符是正则表达式的缩写，若数据字符中含有数字时，返回的结果是true，反之返回的结果是false。
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
AI Hentai Generator
AI Hentai를 무료로 생성하십시오.
인기 기사
R.E.P.O. 에너지 결정과 그들이하는 일 (노란색 크리스탈)
2 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
Repo : 팀원을 부활시키는 방법
4 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
헬로 키티 아일랜드 어드벤처 : 거대한 씨앗을 얻는 방법
4 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
스플릿 소설을이기는 데 얼마나 걸립니까?
3 몇 주 전ByDDD
R.E.P.O. 파일 저장 위치 : 어디에 있고 그것을 보호하는 방법은 무엇입니까?
3 몇 주 전ByDDD
뜨거운 도구
맨티스BT
Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.
SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.
드림위버 CS6
시각적 웹 개발 도구
mPDF
mPDF는 UTF-8로 인코딩된 HTML에서 PDF 파일을 생성할 수 있는 PHP 라이브러리입니다. 원저자인 Ian Back은 자신의 웹 사이트에서 "즉시" PDF 파일을 출력하고 다양한 언어를 처리하기 위해 mPDF를 작성했습니다. HTML2FPDF와 같은 원본 스크립트보다 유니코드 글꼴을 사용할 때 속도가 느리고 더 큰 파일을 생성하지만 CSS 스타일 등을 지원하고 많은 개선 사항이 있습니다. RTL(아랍어, 히브리어), CJK(중국어, 일본어, 한국어)를 포함한 거의 모든 언어를 지원합니다. 중첩된 블록 수준 요소(예: P, DIV)를 지원합니다.
DVWA
DVWA(Damn Vulnerable Web App)는 매우 취약한 PHP/MySQL 웹 애플리케이션입니다. 주요 목표는 보안 전문가가 법적 환경에서 자신의 기술과 도구를 테스트하고, 웹 개발자가 웹 응용 프로그램 보안 프로세스를 더 잘 이해할 수 있도록 돕고, 교사/학생이 교실 환경 웹 응용 프로그램에서 가르치고 배울 수 있도록 돕는 것입니다. 보안. DVWA의 목표는 다양한 난이도의 간단하고 간단한 인터페이스를 통해 가장 일반적인 웹 취약점 중 일부를 연습하는 것입니다. 이 소프트웨어는
