JDBC增删改查存储过程之类

//JDBC连接数据库
//先在项目中引入mysql驱动jar包，网上下载有，之后才能进行连接
String className = "com.mysql.jdbc.Driver";
//或者 new com.mysql.jdbc.Driver();  效果一样
//每个URL是不同的。最后的格式是编码问题，容易导致java向mysql插入中文时，mysql数据库中显示是？问号
String url = "jdbc:mysql://localhost/db_stu?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password ="";
Connection conn = null;
Statement stmt = null;        
    try
    {
        Class.forName(className);
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.createStatement();
        String sql = "insert into db_student values (1010,'张三','23','男','BeiJing')";
        //返回SQL语句影响的行数
        int i = stmt.executeUpdate(sql);
        System.out.println("change :" + i);
        System.out.println("all is good.");
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    finally
    {
        try
        {
            if(stmt != null)
            {
                stmt.close();
                stmt = null;
            }
            if(conn != null)
            {
                conn.close();
                conn = null;
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
        
        
//JDBC使用PreparedStatement进行设置参数
Connection conn = null;
PreparedStatement pstmt = null;
Class.forName(className);
conn = DriverManager.getConnection(url, user, password);
//插入参数是未知的，使用？
pstmt = conn.prepareStatement("insert into db_student values (?,?,?,?,?)");
//设置参数
pstmt.setInt(1, 1004);
pstmt.setString(2, "Fuck");
pstmt.setString(3,"22");
pstmt.setString(4, "man");
pstmt.setString(5, "TianJin");
pstmt.executeUpdate();
//JDBC使用存储过程
Connection conn = null;
CallableStatement cstmt = null;            
Class.forName(className);
conn = DriverManager.getConnection(url, user, password);
//处理存储过程，p(?,?,?,?)是存储过程，第一个，第二个是输入参数，第三个是输出参数，第四个即时输入参数又是输出参数
cstmt = conn.prepareCall("call p(?,?,?,?)");
//指定第三个，第四个是输出参数
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.INTEGER);
//设置第一，第二，第四参数
cstmt.setInt(1, 3);
cstmt.setInt(2, 4);
cstmt.setInt(4, 5);
cstmt.execute();                
System.out.println(cstmt.getInt(3));
System.out.println(cstmt.getInt(4));
//JDBC进行批处理，一个SQL语句需要一个Statement，使用批处理减少使用Statement
//JDBC的PreparedStatement也可以执行批处理
Statement stmt=conn.CreateStatement();
stmt.addBatch("insert into db_student values (1001,'jack',22)");
stmt.addBatch("insert into db_student values (1001,'jack',22)");
stmt.addBatch("insert into db_student values (1001,'jack',22)");
stmt.executeBatch();
stmt.close();
//JDBC处理事务，默认的SQL语句是一个个提交，我们更改后，变成一次性提交
try
{
    conn.setAutoComit(false);
    Statement stmt=conn.CreateStatement();
    stmt.addBatch("insert into db_student values (1001,'jack',22)");
    stmt.addBatch("insert into db_student values (1001,'jack',22)");
    stmt.addBatch("insert into db_student values (1001,'jack',22)");
    stmt.executeBatch();
    //JDBC进行提交
    conn.comit();
    //将原来的设置改回去
    conn.setAutoComit(true);
    stmt.close();
}
catch(ClassNotFoundException e)
{
    e.printStackTrace();
}
catch(SQLException e)
{
    e.printStackTrace();
    try
    {
        if(conn != null)
        {
            conn.rollBack();
            conn.setAutoComit(true);
        }
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
}
finally
{
    try
    {
        if(stmt != null)
        {
            stmt.close();
            stmt = null;
        }
        if(conn != null)
        {
            conn.close();
            conn = null;
        }
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
}


//JDBC可滚动的结果集
Class.forName(className);
conn = DriverManager.getConnection(url, user, password);
//结果集可以滚动来回移动,第一个参数是对于滚动不敏感，第二个参数是并发只读
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("select * from db_student");
rs.next();
//以int型取出第一个字段
System.out.println(rs.getInt(1));
//跳到最后一行
rs.last();
//以String型取出第二个字段
System.out.println(rs.getString(2));
//是否是最后一行
System.out.println(rs.isLast());
//是否是倒数第二行
System.out.println(rs.isAfterLast());
//这是第几行
System.out.println(rs.getRow());
//整体前进一行
rs.previous();
System.out.println(rs.getString(2));
//定位到第二行
rs.absolute(2);
System.out.println(rs.getInt(1));

//JDBC可更新的结果集
Class.forName(className);
conn = DriverManager.getConnection(url, user, password);
//对回滚不敏感，并发是处理，即ResultSet再内存中修改，然后同步数据库修改
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from db_student");    
rs.next();
//更新一行数据
rs.updateString(4, "man");
rs.updateRow();        
//插入新行
rs.moveToInsertRow();
rs.updateInt(1, 1007);
rs.updateString(2, "FUCK");
rs.updateString(3, "22");
rs.updateString(4, "man");
rs.updateString(5, "hennan");
rs.insertRow();
rs.moveToCurrentRow();
//定位到第五行，然后删除
rs.absolute(6);
rs.deleteRow();

//DateSource & RowSet
/*
DateSource
    DriverManager的替代
    连接池实现
    分布式实现
RowSet
    新的ResultSet
    从ResultSet继承
    支持断开的结果集
    支持JavaBean标准
*/