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

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 {// 用keyword 实现查询 keyword额能够随意

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 {// 删除记录 能够按不论什么keyword

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("------------------------------------------------------------------------------------------------------------------------");

}

}