JDBC 存储过程

ゝ一纸荒年。 2023-08-17 17:17 115阅读 0赞

存储过程

DROP PROCEDURE IF EXISTS `addUser`;
    CREATE PROCEDURE `addUser` 
    (in pname VARCHAR(45),in birthday date,in money float,out pid int)
    BEGIN
      INSERT into `user`(name,birthday,money) VALUES(pname,birthday,money);
      SELECT LAST_INSERT_ID() into pid;
    END;

java

package jdbc;
    
    import java.sql.*;
    
    public class PsTest {
        public static void main(String[] args) throws SQLException {
            ps();
        }
    
        static void ps() throws SQLException {
            Connection conn = null;
            CallableStatement cs = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection();
                String sql = "{ call addUser(?,?,?,?) }";
                cs = conn.prepareCall(sql);
                cs.registerOutParameter(4, Types.INTEGER);
                cs.setString(1, "ps name");
                cs.setDate(2, new java.sql.Date(System.currentTimeMillis()));
                cs.setFloat(3, 100.0f);
                cs.executeUpdate();
                int res = cs.getInt(4);
                System.out.println("res= " + res);
    
            } finally {
                JdbcUtils.free(rs, cs, conn);
            }
        }
    }

api

package jdbc;
    
    import java.sql.*;
    
    public class OtherApi {
        public static void main(String[] args) throws SQLException {
            int id = create();
            System.out.println(id);
        }
    
        static int create() throws SQLException {
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection();
                String sql = "insert into user(name,birthday,money) values('name1','1987-01-01',400)";
                ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.executeUpdate();
                rs = ps.getGeneratedKeys();
                int id = 0;
                if (rs.next()) {
                    id = rs.getInt(1);
                }
                return id;
    
            } finally {
                JdbcUtils.free(rs, ps, conn);
            }
        }
    }

转载于:https://www.cnblogs.com/kikyoqiang/p/11605368.html