首页 > 代码库 > JDBC增删改查存储过程之类

JDBC增删改查存储过程之类

  1 //JDBC连接数据库  2 //先在项目中引入mysql驱动jar包,网上下载有,之后才能进行连接  3 String className = "com.mysql.jdbc.Driver";  4 //或者 new com.mysql.jdbc.Driver();  效果一样  5 //每个URL是不同的。最后的格式是编码问题,容易导致java向mysql插入中文时,mysql数据库中显示是?问号  6 String url = "jdbc:mysql://localhost/db_stu?useUnicode=true&characterEncoding=utf8";  7 String user = "root";  8 String password ="";  9 Connection conn = null; 10 Statement stmt = null;         11     try 12     { 13         Class.forName(className); 14         conn = DriverManager.getConnection(url, user, password); 15         stmt = conn.createStatement(); 16         String sql = "insert into db_student values (1010,‘张三‘,‘23‘,‘男‘,‘BeiJing‘)"; 17         //返回SQL语句影响的行数 18         int i = stmt.executeUpdate(sql); 19         System.out.println("change :" + i); 20         System.out.println("all is good."); 21     } 22     catch(Exception e) 23     { 24         e.printStackTrace(); 25     } 26     finally 27     { 28         try 29         { 30             if(stmt != null) 31             { 32                 stmt.close(); 33                 stmt = null; 34             } 35             if(conn != null) 36             { 37                 conn.close(); 38                 conn = null; 39             } 40         } 41         catch(Exception e) 42         { 43             e.printStackTrace(); 44         } 45     } 46          47          48 //JDBC使用PreparedStatement进行设置参数 49 Connection conn = null; 50 PreparedStatement pstmt = null; 51 Class.forName(className); 52 conn = DriverManager.getConnection(url, user, password); 53 //插入参数是未知的,使用? 54 pstmt = conn.prepareStatement("insert into db_student values (?,?,?,?,?)"); 55 //设置参数 56 pstmt.setInt(1, 1004); 57 pstmt.setString(2, "Fuck"); 58 pstmt.setString(3,"22"); 59 pstmt.setString(4, "man"); 60 pstmt.setString(5, "TianJin"); 61 pstmt.executeUpdate(); 62 //JDBC使用存储过程 63 Connection conn = null; 64 CallableStatement cstmt = null;             65 Class.forName(className); 66 conn = DriverManager.getConnection(url, user, password); 67 //处理存储过程,p(?,?,?,?)是存储过程,第一个,第二个是输入参数,第三个是输出参数,第四个即时输入参数又是输出参数 68 cstmt = conn.prepareCall("call p(?,?,?,?)"); 69 //指定第三个,第四个是输出参数 70 cstmt.registerOutParameter(3, Types.INTEGER); 71 cstmt.registerOutParameter(4, Types.INTEGER); 72 //设置第一,第二,第四参数 73 cstmt.setInt(1, 3); 74 cstmt.setInt(2, 4); 75 cstmt.setInt(4, 5); 76 cstmt.execute();                 77 System.out.println(cstmt.getInt(3)); 78 System.out.println(cstmt.getInt(4)); 79 //JDBC进行批处理,一个SQL语句需要一个Statement,使用批处理减少使用Statement 80 //JDBC的PreparedStatement也可以执行批处理 81 Statement stmt=conn.CreateStatement(); 82 stmt.addBatch("insert into db_student values (1001,‘jack‘,22)"); 83 stmt.addBatch("insert into db_student values (1001,‘jack‘,22)"); 84 stmt.addBatch("insert into db_student values (1001,‘jack‘,22)"); 85 stmt.executeBatch(); 86 stmt.close(); 87 //JDBC处理事务,默认的SQL语句是一个个提交,我们更改后,变成一次性提交 88 try 89 { 90     conn.setAutoComit(false); 91     Statement stmt=conn.CreateStatement(); 92     stmt.addBatch("insert into db_student values (1001,‘jack‘,22)"); 93     stmt.addBatch("insert into db_student values (1001,‘jack‘,22)"); 94     stmt.addBatch("insert into db_student values (1001,‘jack‘,22)"); 95     stmt.executeBatch(); 96     //JDBC进行提交 97     conn.comit(); 98     //将原来的设置改回去 99     conn.setAutoComit(true);100     stmt.close();101 }102 catch(ClassNotFoundException e)103 {104     e.printStackTrace();105 }106 catch(SQLException e)107 {108     e.printStackTrace();109     try110     {111         if(conn != null)112         {113             conn.rollBack();114             conn.setAutoComit(true);115         }116     }117     catch(SQLException e)118     {119         e.printStackTrace();120     }121 }122 finally123 {124     try125     {126         if(stmt != null)127         {128             stmt.close();129             stmt = null;130         }131         if(conn != null)132         {133             conn.close();134             conn = null;135         }136     }137     catch(SQLException e)138     {139         e.printStackTrace();140     }141 }142 143 144 //JDBC可滚动的结果集145 Class.forName(className);146 conn = DriverManager.getConnection(url, user, password);147 //结果集可以滚动来回移动,第一个参数是对于滚动不敏感,第二个参数是并发只读148 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  ResultSet.CONCUR_READ_ONLY);149 rs = stmt.executeQuery("select * from db_student");150 rs.next();151 //以int型取出第一个字段152 System.out.println(rs.getInt(1));153 //跳到最后一行154 rs.last();155 //以String型取出第二个字段156 System.out.println(rs.getString(2));157 //是否是最后一行158 System.out.println(rs.isLast());159 //是否是倒数第二行160 System.out.println(rs.isAfterLast());161 //这是第几行162 System.out.println(rs.getRow());163 //整体前进一行164 rs.previous();165 System.out.println(rs.getString(2));166 //定位到第二行167 rs.absolute(2);168 System.out.println(rs.getInt(1));169 170 //JDBC可更新的结果集171 Class.forName(className);172 conn = DriverManager.getConnection(url, user, password);173 //对回滚不敏感,并发是处理,即ResultSet再内存中修改,然后同步数据库修改174 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);175 rs = stmt.executeQuery("select * from db_student");    176 rs.next();177 //更新一行数据178 rs.updateString(4, "man");179 rs.updateRow();        180 //插入新行181 rs.moveToInsertRow();182 rs.updateInt(1, 1007);183 rs.updateString(2, "FUCK");184 rs.updateString(3, "22");185 rs.updateString(4, "man");186 rs.updateString(5, "hennan");187 rs.insertRow();188 rs.moveToCurrentRow();189 //定位到第五行,然后删除190 rs.absolute(6);191 rs.deleteRow();192 193 //DateSource & RowSet194 /*195 DateSource196     DriverManager的替代197     连接池实现198     分布式实现199 RowSet200     新的ResultSet201     从ResultSet继承202     支持断开的结果集203     支持JavaBean标准204 */

 

JDBC增删改查存储过程之类