java数据库的存储过程_java操作mysql数据库数据(存储过程)

1.按照上一篇的做法，可以很快将mysql数据库设置成utf8编码。

mysql>show variables like '%character%';

mysql>show variables like '%collation%';

2.编写存储过程

mysql> select body from mysql.proc;

+--------------------------------------------+

| body |

+--------------------------------------------+

| begin

select count(*) into s from login;

end |

+--------------------------------------------+

1 row in set

mysql> drop procedure procyw;

Query OK, 0 rows affected

mysql> delimiter //

mysql> create procedure procyw_login_getcount(out s int)

-> begin

-> select count(*) into s from login;

-> end

-> //

Query OK, 0 rows affected

mysql> create procedure procyw_login_findbyid(in p_in int)

-> begin

-> select p_in;

-> set p_in=2;

-> select p_in;

-> end;

-> //

Query OK, 0 rows affected

mysql> set @p_in=1;

-> call procyw_login_findbyid(@p_in);

-> //

Query OK, 0 rows affected

+------+

| p_in |

+------+

| 1 |

+------+

1 row in set

+------+

| p_in |

+------+

| 2 |

+------+

1 row in set

Query OK, 0 rows affected

mysql> alter procedure procyw_login_findbyid;

-> //

Query OK, 0 rows affected

mysql> delimiter ;

mysql> drop procedure if exists procyw_login_findbyid;

Query OK, 0 rows affected

mysql> delimiter //

mysql> create procedure procyw_login_findbyid(in p_in int)

-> begin

-> select * from login where id=p_in;

-> end

-> //

Query OK, 0 rows affected

mysql> delimiter ;

mysql> set @p_in=1

-> ;

Query OK, 0 rows affected

mysql> call procyw_login_findbyid(@p_in);

+----+------+

| id | name |

+----+------+

| 1 | yw |

+----+------+

1 row in set

Query OK, 0 rows affected

mysql> set @p_in=3

-> ;

Query OK, 0 rows affected

mysql> call procyw_login_findbyid(@p_in);

+----+------+

| id | name |

+----+------+

| 3 | 杨文 |

+----+------+

1 row in set

Query OK, 0 rows affected

mysql> delimiter //

mysql> create procedure procyw_login_findall(in p_in int)

-> begin

-> select * from login where id=p_in;

-> select * from login;

-> end

-> //

Query OK, 0 rows affected

3.java代码

======================basedao=======================================

package com.yw.basedao;

import java.sql.*;

import java.sql.DriverManager;

import java.sql.SQLException;

public class BaseDao {

protected static String driverClassName = "com.mysql.jdbc.Driver";

protected String user = "root";

private String password = "wb";

private String url = "jdbc:mysql://localhost:3306/test";

public Connection getConnection() {

Connection conn = null;

try {

Class.forName(driverClassName);

conn = DriverManager.getConnection(url, user, password);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

public void closeAll(Connection conn, Statement stmt, ResultSet rs) {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (stmt != null) {

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

=================LoginInfo====================================

package com.yw.entity;

public class LoginInfo {

private int id;

private String name;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

}

=====================LoginDao====================================

package com.yw.entitydao;

import java.util.List;

import com.yw.entity.LoginInfo;

public interface LoginDao {

List getLogin();

}

==================LoginDaoImpl===========================

package com.yw.entitydaoimpl;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import java.util.List;

import com.yw.basedao.BaseDao;

import com.yw.entity.LoginInfo;

import com.yw.entitydao.LoginDao;

public class LoginDaoImpl extends BaseDao implements LoginDao{

private PreparedStatement pstmt;

private ResultSet rs;

private Connection conn;

private String sql;

int count;

private CallableStatement cstmt;

public void executeProcedureGetcount(){

System.out.println("使用存储过程procyw_login_getcount输出。");

conn=this.getConnection();

try{

cstmt=conn.prepareCall("{call procyw_login_getcount(?)}");

//设置输出参数

cstmt.registerOutParameter(1, Types.INTEGER);

//执行存储过程

boolean flg=cstmt.execute();

System.out.println("execute返回的是记录集？"+flg);

int i=cstmt.getInt(1);

System.out.println("当前login表中记录数量为："+i);

}catch(SQLException e){

e.printStackTrace();

}finally{

this.closeAll(conn, cstmt, rs);

}

}

public void executeProcedureFindByID(){

System.out.println("使用存储过程executeProcedureFindByID输出。");

conn=this.getConnection();

try{

cstmt=conn.prepareCall("{call procyw_login_findbyid(?)}");

//设置输入参数

cstmt.setInt(1, 5);

//执行存储过程

rs=cstmt.executeQuery();

int id;

String name;

if(rs.next()){

id=rs.getInt(1);

name =rs.getString(2);

System.out.println(id+"\t"+name);

}

}catch(SQLException e){

e.printStackTrace();

}finally{

this.closeAll(conn, cstmt, rs);

}

}

public void executeProcedureFindAll(){

System.out.println("使用存储过程executeProcedureFindAll输出。");

conn=this.getConnection();

try{

cstmt=conn.prepareCall("{call procyw_login_findall(?)}");

//设置输入参数

cstmt.setInt(1, 5);

// //执行单个结果集存储过程

// rs=cstmt.executeQuery();

//

// int id;

// String name;

// if(rs.next()){

// id=rs.getInt(1);

// name =rs.getString(2);

// System.out.println(id+"\t"+name);

// }

//执行

cstmt.execute();

//获取结果集

System.out.println("获得procyw_login_findall存储过程里面第一个结果集");

rs=cstmt.getResultSet();

while(rs.next()){

System.out.println(rs.getInt(1)+"\t"+rs.getString(2));

}

//判断是否有下一个

while(cstmt.getMoreResults()){

System.out.println("获得procyw_login_findall存储过程里面又一个结果集");

//得到结果集

rs=cstmt.getResultSet();

while(rs.next()){

System.out.println(rs.getInt(1)+"\t"+rs.getString(2));

}

}

}catch(SQLException e){

e.printStackTrace();

}finally{

this.closeAll(conn, cstmt, rs);

}

}

@Override

public List getLogin() {

List list=new ArrayList();

sql="select * from login";

conn=this.getConnection();

try{

pstmt=conn.prepareStatement(sql);

rs=pstmt.executeQuery();

while(rs.next()){

LoginInfo login=new LoginInfo();

login.setId(rs.getInt("id"));

login.setName(rs.getString("name"));

list.add(login);

}

}catch(SQLException e){

e.printStackTrace();

}finally{

this.closeAll(conn, pstmt, rs);

}

return list;

}

}

==================LoginManager===================================

package com.yw.manager;

import java.util.Iterator;

import java.util.List;

import com.yw.entity.LoginInfo;

import com.yw.entitydaoimpl.LoginDaoImpl;

public class LoginManager {

/**

* @param args

*/

public static void main(String[] args) {

LoginDaoImpl login=new LoginDaoImpl();

List list=login.getLogin();

Iterator it=list.iterator();

System.out.println("id\tname");

System.out.println("------------------------");

while(it.hasNext()){

LoginInfo loginInfo=it.next();

System.out.println(loginInfo.getId()+"\t"+loginInfo.getName() );

}

//使用存储过程

System.out.println("===================");

login.executeProcedureGetcount();

System.out.println("===================");

login.executeProcedureFindByID();

System.out.println("===================");

login.executeProcedureFindAll();

}

}

4.运行结果