java mysql 基本操作

1.新建表

CREATE TABLE test (
`id`  int(20) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4
ROW_FORMAT=COMPACT
;

2.新建存储过程

#1.创建存储过程，指定参数值
drop procedure if exists pr_param_in2out;
create procedure pr_param_in2out
(
in pid int,
out pname varchar(50) CHARSET utf8
)
begin
# 创建存储过程，指定参数的字符集，如果带有中文varchar需要指定 它的字符集为 utf-8，否则 java 取值会报错的
 SELECT `name` INTO pname from test where id=pid;
end;

#2.创建存储过程，返回多个结果集
drop procedure if exists pr_param_in2out1;
create procedure pr_param_in2out1
(
in pid int,
)
begin
# 可以返回多个结果集
 SELECT * from test where id=pid;
 SELECT * from test;
end;

#3创建存储过程，返回单个结果集
drop procedure if exists pr_param_in2out2;
create procedure pr_param_in2out2
(
in pid int
)
begin
 SELECT * from test where id=pid;
end;

#4. 创建带有游标的存储过程
drop procedure if exists pro_handledata;
CREATE PROCEDURE `pro_handledata`()
BEGIN
 #本存储过程用来根据student表和class表来加工生成stu_info表的数据
 DECLARE done INT DEFAULT 0;
 #定义stu_info表所需要的变量
 DECLARE var_sid INTEGER(10);
 DECLARE var_sname VARCHAR(50) CHARSET utf8  DEFAULT '';
 DECLARE var_classid INTEGER(10);

 DECLARE var_classname VARCHAR(50) CHARSET utf8  DEFAULT '';

 #定义游标
 DECLARE cur_student CURSOR FOR
 SELECT sid,sname,classid  from student;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 #循环处理纳税主体的数据
 OPEN cur_student;

   FETCH cur_student INTO var_sid,var_sname,var_classid;

   WHILE done = 0 DO
    #获取var_classid所对应的班级名称
    SELECT getClassName(var_classid) INTO var_classname;
    #插入数据
    INSERT INTO stu_info VALUES(var_sid,var_sname,var_classname);
     
    FETCH cur_student INTO var_sid,var_sname,var_classid;

   END WHILE;   

  CLOSE cur_student;

 END;


#5.创建函数，指定返回值
drop FUNCTION if exists fc_getName;
CREATE FUNCTION `fc_getName`(pid INTEGER)
RETURNS varchar(50)
DETERMINISTIC
BEGIN
 #本函数根据传递进来的班级编号，获取它所在班级名称
 DECLARE pname VARCHAR(50) CHARSET utf8  DEFAULT '';
 SELECT `name` INTO name from test where id=pid;
 RETURN pname;
 END;

java 操作mysql 类

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.sql.Types;public class MySqlTester {public static String user;public static String password;public static String url;public static Connection conn;public static void main(String[] args) throws Exception {init_mysql();//insert();//update();delete();//insertAndGetGeneraKey();//executeProcedureInAndOut();//executeProcedureGetMultiResultSet();//executeProcedureGetResultSet();//executeFunction1();//executeFunction2();}   /**       * 描述:新增 * <pre> * 举例： * </pre> * @throws Exception       */public static void insert() throws Exception {Statement stmt = conn.createStatement();String insert = "insert into test(name) values('hehe')";stmt.execute(insert);String query = "select * from test";ResultSet result = stmt.executeQuery(query);while (result.next()) {System.out.println("id : " + result.getInt("id")+ "\tname : " + result.getString("name"));}}/**       * 描述:修改 * <pre> * 举例： * </pre> * @throws Exception       */public static void update() throws Exception {Statement stmt = conn.createStatement();String update = "update test set name='haha' where id=3";stmt.execute(update);String query = "select * from test";ResultSet result = stmt.executeQuery(query);while (result.next()) {System.out.println("id : " + result.getInt("id")+ "\tname : " + result.getString("name"));}}/**       * 描述:删除 * <pre> * 举例： * </pre> * @throws Exception       */public static void delete() throws Exception {Statement stmt = conn.createStatement();String update = "delete from test where id=3";stmt.execute(update);String query = "select * from test";ResultSet result = stmt.executeQuery(query);while (result.next()) {System.out.println("id : " + result.getInt("id")+ "\tname : " + result.getString("name"));}}/** * 新增数据并且返回自增的 key 值 *  * @throws Exception */public static void insertAndGetGeneraKey() throws Exception {String sqlInsert = "insert into test(name) values('test')";PreparedStatement pstmt = null;pstmt = conn.prepareStatement(sqlInsert,Statement.RETURN_GENERATED_KEYS);pstmt.execute();ResultSet result = pstmt.getGeneratedKeys();result.next();int key = result.getInt(1);System.out.println("自增之后的 id ： " + key);}/** * 描述:jdbc 操作存储过程:根据输入值获取一个返回值 *  * <pre> * 举例： * </pre> *  * @throws Exception */public static void executeProcedureInAndOut() throws Exception {String sql = "{call pr_param_in2out(?,?)}";CallableStatement callableStatement = conn.prepareCall(sql);callableStatement.registerOutParameter(2, Types.VARCHAR);callableStatement.setInt(1, 1);callableStatement.execute();String name = callableStatement.getString(2);System.out.println(name);}/** * 描述:jdbc 操作存储过程:返回多个返回结果集 *  * <pre> * 举例： * </pre> *  * @throws Exception */public static void executeProcedureGetMultiResultSet() throws Exception {String sql = "{call pr_param_in2out1(?)}";CallableStatement callableStatement = conn.prepareCall(sql);callableStatement.setInt(1, 1);boolean hadResults = callableStatement.execute();int i = 0;while (hadResults) {System.out.println("result No:----" + (++i));ResultSet rs = callableStatement.getResultSet();while (rs != null && rs.next()) {int id1 = rs.getInt(1);String name1 = rs.getString(2);System.out.println(id1 + ":" + name1);}hadResults = callableStatement.getMoreResults(); // 检查是否存在更多结果集}}/** * 描述:jdbc 操作存储过程:返回单个返回结果集 *  * <pre> * 举例： * </pre> *  * @throws Exception */public static void executeProcedureGetResultSet() throws Exception {String sql = "{call pr_param_in2out2(?)}";CallableStatement callableStatement = conn.prepareCall(sql);callableStatement.setInt(1, 1);callableStatement.execute();ResultSet rs = callableStatement.getResultSet();while (rs != null && rs.next()) {int id1 = rs.getInt(1);String name1 = rs.getString(2);System.out.println(id1 + ":" + name1);}}/** * 描述:jdbc 操作函数1:根据输入值获取一个返回值 *  * <pre> * 举例： * </pre> *  * @throws Exception */public static void executeFunction1() throws Exception {String sql = "{?=call fc_getName(?)}";CallableStatement callableStatement = conn.prepareCall(sql);// 注意这个设置值得时候，第一个 ? 并没有被记入到参数个数中，后面括号里面有多少 ? 就是多少个callableStatement.registerOutParameter(1, Types.VARCHAR);callableStatement.setInt(1, 1);callableStatement.execute();String name = callableStatement.getString(1);System.out.println(name);}/** * 描述:jdbc 操作函数2:根据输入值获取一个返回值 *  * <pre> * 举例： * </pre> *  * @throws Exception */public static void executeFunction2() throws Exception {String sql = "select fc_getName(?)";CallableStatement callableStatement = conn.prepareCall(sql);// 注意这个设置值得时候，第一个 ? 并没有被记入到参数个数中，后面括号里面有多少 ? 就是多少个callableStatement.setInt(1, 1);callableStatement.execute();ResultSet rs = callableStatement.getResultSet();String name = null;if (rs.next()) {name = rs.getString(1);}System.out.println(name);}/** * 描述:初始化数据库连接 *  * <pre> * 举例： * </pre> *  * @return */public static Connection init_mysql() {try {// 初始化url,user,password// url参数:"jdbc:数据库类型:主机IP/数据库名?用户名=&密码=&characterEncoding="url = "jdbc:mysql://localhost/test1?characterEncoding=utf-8";user = "root";password = "123456";// 加载驱动Class.forName("org.gjt.mm.mysql.Driver");// 建立连接conn = DriverManager.getConnection(url, user, password);} catch (Exception e) {System.out.println("数据库连接异常!");e.printStackTrace();}return conn;}}