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<LoginInfo> 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<LoginInfo> getLogin() {
List<LoginInfo> list=new ArrayList<LoginInfo>();
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<LoginInfo> list=login.getLogin();
Iterator<LoginInfo> 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.运行结果
