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_pwdint,OUT flagint)
BEGIN
DECLARE v_pwd int;
select pwd INTO v_pwd from users_ning
where id = p_id;
ifv_pwd = p_pwd then
set flag:=1;
else
set flag := 0;
end if;
END
packagedemo20130528;
importjava.sql.*;
importdemo20130526.DBUtils;
/**
* 测试JDBC API调用过程
* @author tarena
*
*/
publicclassProcedureDemo2 {
/**
* @param args
* @throws Exception
*/
publicstaticvoidmain(String[] args)throwsException {
System.out.println(login(123,1234));
}
/**
* 调用过程,实现登录功能
* @param id 考生id
* @param pwd 考试密码
* @return if成功:1; if密码错:0; if没有用户:-1
* @throws Exception
*/
publicstaticintlogin(intid,intpwd)throwsException{
intflag = -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();
}
returnflag;
}
}
packagedemo20130526;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.FileNotFoundException;
importjava.io.IOException;
importjava.sql.Connection;
importjava.sql.DatabaseMetaData;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.ResultSetMetaData;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.Properties;
publicclassDBUtils {
staticConnection conn =null;
staticPreparedStatement stmt =null;
staticResultSet rs =null;
staticStatement st =null;
staticString username =null;
staticString password =null;
staticString url =null;
staticString driverName =null;
publicstaticConnection getConnMySQL()throwsException {// 连接mysql 返回conn
getUrlUserNamePassWordClassNameMySQL();
conn = DriverManager.getConnection(url, username, password);
// conn.setAutoCommit(false);设置自动提交为false
returnconn;
}
publicstaticConnection getConnORCALE()throwsException {// 连接orcale
// 返回conn
getUrlUserNamePassWordClassNameORCALE();
conn = DriverManager.getConnection(url, username, password);
// conn.setAutoCommit(false);
returnconn;
}
privatestaticvoidgetUrlUserNamePassWordClassNameORCALE()
throwsException {
// 从资源文件 获取 orcale的username password url等信息
Properties pro = newProperties();
File path = newFile("src/all.properties");
pro.load(newFileInputStream(path));
String paths = pro.getProperty("filepath");
File file = newFile(paths +"orcale.properties");
getFromProperties(file);
}
publicstaticvoidgetUrlUserNamePassWordClassNameMySQL()throwsException {
// 从资源文件 获取mysql的username password url等信息
Properties pro = newProperties();
File path = newFile("src/all.properties");
pro.load(newFileInputStream(path));
String paths = pro.getProperty("filepath");
File file = newFile(paths +"mysql.properties");
getFromProperties(file);
}
publicstaticvoidgetFromProperties(File file)throwsIOException,
FileNotFoundException, ClassNotFoundException {// 读资源文件的内容
Properties pro = newProperties();
pro.load(newFileInputStream(file));
username = pro.getProperty("username");
password = pro.getProperty("password");
url = pro.getProperty("url");
driverName = pro.getProperty("driverName");
Class.forName(driverName);
}
publicstaticvoiddbClose()throwsException {// 关闭所有
if(rs !=null)
rs.close();
if(st !=null)
st.close();
if(stmt !=null)
stmt.close();
if(conn !=null)
conn.close();
}
publicstaticResultSet getById(String tableName,intid)throwsException {// 用id来查询结果
st = conn.createStatement();
rs = st.executeQuery("select * from "+ tableName +" where id="+ id
+ " ");
returnrs;
}
publicstaticResultSet getByAll(String sql, Object... obj)
throwsException {// 用关键字 实现查询 关键字额可以任意
sql = sql.replaceAll(";","");
sql = sql.trim();
stmt = conn.prepareStatement(sql);
String[] strs = sql.split("\\?");// 将sql 以? 非开
intnum = strs.length;// 得到?的个数
intsize = obj.length;
for(inti =1; i <= size; i++) {
stmt.setObject(i, obj[i - 1]);// 数组下标从0开始
}
if(size
for(intk = size +1; k <= num; k++) {
stmt.setObject(k, null);// 数组下标从0开始
}
}
rs = stmt.executeQuery();
returnrs;
}
publicstaticvoiddoInsert(String sql)throwsSQLException {// 传入 sql 语句
// 实现插入操作
st = conn.createStatement();
st.execute(sql);
}
publicstaticvoiddoInsert(String sql, Object... args)throwsException {// 传入参数
// 利用
// PreparedStatement
// 实现插入
// 传入的参数是任意多个 因为有Object 。。。args
intsize = args.length;// 获得 Object ...obj 传过来的参数的个数
stmt = conn.prepareStatement(sql);
for(inti =1; i <= size; i++) {
stmt.setObject(i, args[i - 1]);// 数组下标从0开始
}
stmt.execute();
}
publicstaticintdoUpdate(String sql)throwsException {// 传入 sql 实现更新操作
st = conn.createStatement();
intnum = st.executeUpdate(sql);
returnnum;
}
publicstaticvoiddoUpdate(String sql, Object... obj)throwsException {
// 传入参数 利用 PreparedStatement实现更新
// 传入的参数是任意多个 因为有Object 。。。args
intsize = obj.length;// 获得 Object ...obj 传过来的参数的个数
stmt = conn.prepareStatement(sql);
for(inti =1; i <= size; i++) {
stmt.setObject(i, obj[i - 1]);// 数组下标从0开始
}
stmt.executeUpdate(sql);
}
publicstaticbooleandoDeleteById(String tableName,intid)
throwsSQLException {// 删除记录 by id
st = conn.createStatement();
booleanb = st.execute("delete from "+ tableName +" where id="+ id
+ "");
returnb;
}
publicstaticbooleandoDeleteByAll(String sql, Object... args)
throwsSQLException {// 删除记录 可以按任何关键字
sql = sql.replaceAll(";","");
sql = sql.trim();
stmt = conn.prepareStatement(sql);
String[] strs = sql.split("\\?");// 将sql 以? 非开
intnum = strs.length;// 得到?的个数
intsize = args.length;
for(inti =1; i <= size; i++) {
stmt.setObject(i, args[i - 1]);// 数组下标从0开始
}
if(size
for(intk = size +1; k <= num; k++) {
stmt.setObject(k, null);// 数组下标从0开始
}
}
booleanb = stmt.execute();
returnb;
}
publicstaticvoidgetMetaDate()throwsException {// 获取数据库元素数据
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());
}
publicstaticString[] getColumnNamesFromMySQL(String sql)throwsException {
conn = DBUtils.getConnMySQL();
returngetColumnName(sql);
}
publicstaticString[] getColumnNamesFromOrcale(String sql)
throwsException {
conn = DBUtils.getConnORCALE();
returngetColumnName(sql);
}
privatestaticString[] getColumnName(String sql)throwsException {// 返回表中所有的列名
conn = DBUtils.getConnORCALE();
st = conn.createStatement();
rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
intnum = rsmd.getColumnCount();
System.out.println("ColumnCount="+ num);
String[] strs = newString[num];
// 显示列名
for(inti =1; i <= rsmd.getColumnCount(); i++) {
String str = rsmd.getColumnName(i);
strs[i - 1] = str;
System.out.print(str + "\t");
}
returnstrs;
}
publicstaticvoidgetColumnDataFromMySQL(String sql)throwsException {// 输出表中的数据
conn = DBUtils.getConnMySQL();
getColumnData(sql);
}
publicstaticvoidgetColumnDataFromORCALEL(String sql)throwsException {// 输出表中的数据
conn = DBUtils.getConnORCALE();
getColumnData(sql);
}
publicstaticvoidgetColumnData(String sql)throwsException {// 输出表中的数据
st = conn.createStatement();
rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
System.out
.println("\n------------------------------------------------------------------------------------------------------------------------");
while(rs.next()) {
for(inti =1; i <= rsmd.getColumnCount(); i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println();
}
System.out
.println("------------------------------------------------------------------------------------------------------------------------");
}
publicstaticvoidgetTableDataFromOrcale(String sql)throwsException {// 输出表的列名
// 和表中的全部数据
conn = DBUtils.getConnORCALE();
getTableData(sql);
}
publicstaticvoidgetTableDataFromMysql(String sql)throwsException {// 输出表的列名
// 和表中的全部数据
conn = DBUtils.getConnMySQL();
getTableData(sql);
}
privatestaticvoidgetTableData(String sql)throwsSQLException {
// getTableDataFromMysql
// getTableDataFromOrcale
st = conn.createStatement();
rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
intnum = rsmd.getColumnCount();
System.out.println("ColumnCount="+ num);
String[] strs = newString[num];
// 显示列名
for(inti =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(inti =1; i <= rsmd.getColumnCount(); i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println();
}
System.out
.println("------------------------------------------------------------------------------------------------------------------------");
}
}