24H免费课程咨询  TEL:13401595960   QQ:1870218756  微信:13401595960(李老师)

东方博宜

网站首页 > 软件开发资讯 > Java开发

JDBC连接MySql数据库基础(二)

2017-08-28 19:31:36 东方博宜 阅读

1、批处理

st.addBath(sql);//存储sql语句

st.executeBath();//执行bath中的sql语句

 

例子:

         /**

          * 批处理

          * @param sql

          * @return

          */

         public static int[] changeDatas(String[] sqls){

                   int[] r = null;

                   Connection con = null;

                   Statement st = null;

                  

                  

                   try{

                            con = ConnectionMgr.getConnection();

                            st = con.createStatement();

                           

                            //sql语句一句一句的绑定到处理容器中

                            if(sqls != null){

                              for(int i = 0;i < sqls.length;i++){

                                        st.addBatch(sqls[i]);

                              }

                            }

                           

                            //执行bath中的sql语句

                            r = st.executeBatch();

                   }catch(Exception e){

                            e.printStackTrace();

                   }finally{

                            ConnectionMgr.closeResource(con, st, null);

                   }

                  

                   return r;

         }

        

         public static void main(String[] args) {

                   String[] sqls = {"insert into emp(ename,sal,job) values('张小芳',5000,'开发')","update emp set sal=sal*1.1 where ename='张小芳'"};

                  

                   int[] x = changeDatas(sqls);

                   for(int i = 0;i < x.length;i++){

                            System.out.println(x[i]);

                   }

         }

 

2、采用预编译容器增强sql的安全性

PreparedStatement:预编译的容器,可以防止参数有类似之类符号,引起sql报错!

 

         /**

          * 执行增加、修改、删除语句

          */

         public static int preparedChange(){

                   int r = 0;

                   Connection con = null;

                   PreparedStatement pst = null;

                  

                   try{

                            con = ConnectionMgr.getConnection();

                            //提供带?sql

                            String sql = "insert into emp(ename,sal,job) values(?,?,?)";

                           

                            // 获取预编译的容器

                            pst = con.prepareStatement(sql);

                            //为参数赋值

                            pst.setString(1, "张晓敏");

                            pst.setDouble(2, 6000.0);

                            pst.setObject(3, "部门经理");

                           

                           

                            r = pst.executeUpdate();

                           

                   }catch(Exception e){

                            e.printStackTrace();

                   }finally{

                            ConnectionMgr.closeResource(con, pst, null);

                   }

                  

                   return r;

         }

        

         /**

          * 复用度较高的PreparedStatement

          */

         public static int preparedChange(String sql,Object[] params){

                   int r = 0;

                   Connection con = null;

                   PreparedStatement pst = null;

                  

                   try{

                            con = ConnectionMgr.getConnection();

                           

                            // 获取预编译的容器

                            pst = con.prepareStatement(sql);

                            //循环为sql语句中的?参数赋值

                            if(params != null){

                                     for(int i = 0;i < params.length;i++){

                                         pst.setObject(i + 1,params[i]);       

                                     }

                            }

                           

                            r = pst.executeUpdate();

                           

                   }catch(Exception e){

                            e.printStackTrace();

                   }finally{

                            ConnectionMgr.closeResource(con, pst, null);

                   }

                  

                   return r;

         }

        

         public static void main(String[] args) {

//               int x = preparedChange();

//               System.out.println(x);

                  

                   String sql = "update emp set sal=?,hiredate=? where ename=?";

                   Object[] params = {6500.0,"2012-2-3","张晓敏"};

                   int x = preparedChange(sql, params);

                   System.out.println(x);

         }

 

预编译容器和普通容器的区别?

A、预编译容器(PreparedStatement)需要在生成容器时,绑定sql语句,普通容器是在执行时绑定sql语句;

B、预编译容器可以为?参数赋值,从而防止sql注入,普通容器不可以!

 

3、事物处理

事物处理:一组动作,同时成功或者失败(增加、修改、删除),我们叫做一组事物!

比如:删除数据和记录日志!

 

先删除数据,后记录日志,哪怕删除成功了,由于记录日志失败,我们也需要将删除成功的数据撤销回来!

 

步骤:

A、撤销jdbc自动的事物管理机制:con.setAutoCommit(false);

B、在执行成功的地方,确认事物的提交:con.commit();

C、在执行是失败的地方(异常),撤销刚刚执行的所有语句(哪怕成功的):con.rollback();

 

例子:

         /**

          * 批处理

          * @param sql

          * @return

          */

         public static int[] changeDatas(String[] sqls){

                   int[] r = null;

                   Connection con = null;

                   Statement st = null;

                  

                  

                   try{

                            con = ConnectionMgr.getConnection();

                            st = con.createStatement();

                            //1、撤销jdbc自动的事物管理

                            con.setAutoCommit(false);

                           

                            //sql语句一句一句的绑定到处理容器中

                            if(sqls != null){

                              for(int i = 0;i < sqls.length;i++){

                                        st.addBatch(sqls[i]);

                              }

                            }

                           

                            //执行bath中的sql语句

                            r = st.executeBatch();

                           

                            //2、执行完毕没有异常,执行提交事物的操作

                            con.commit();

                   }catch(Exception e){

                            //3、进入catch说明有异常,撤销所有的动作

                            try {

                                     con.rollback();

                            } catch (Exception e1) {

                                     e1.printStackTrace();

                            }

                           

                            e.printStackTrace();

                   }finally{

                            ConnectionMgr.closeResource(con, st, null);

                   }

                  

                   return r;

         }

        

         public static void main(String[] args) {

                   //第一条sql是正确的,第二条sql是错误的,第一句的执行也会撤销回来

                   String[] sqls = {"insert into emp(ename,sal,job) values('张大兵',3000.0,'实习生')","update emp set sal='五千' where empno=36"};

                   int[] x = changeDatas(sqls);

                   for(int i = 0;x != null && i < x.length;i++){

                            System.out.println(x[i]);

                   }

         }

 

4、分页查询

rs.absolute(start);//从第start条开始

 

for(int i = 1;i <= pagesize;i++){

   

if(rs.next == false){

    break;

}

}

 

例子:

         /**

          * 分页获取emp表的数据

          * @param sql

          * @param page 查询的页数

          * @param pageSize 每页显示的条数

          * @return

          */

         public static List getEmps(String sql,int page,int pageSize){

                   List list = new ArrayList();

                   //ConnectionStatementResultSet定义为生命周期更长的变量

                   Connection con = null;

                   Statement st = null;

                   ResultSet rs = null;

                  

                   try{

                            con = ConnectionMgr.getConnection();

                            //S:获取语句分析容器(操作sql语句的容器)

                            st = con.createStatement();

                           

                            //R:执行sql得到结果集

                            rs = st.executeQuery(sql);

                           

                            //计算需要定位到哪一条

                            int start = (page - 1) * pageSize + 1;

                           

                            //定位到需要的那一条

                            rs.absolute(start);

                           

                            //向下查询pageSize

                            for(int i = 1;i <= pageSize;i++){

                                     int empno = rs.getInt("empno");

                                     String ename = rs.getString("ename");

                                     String hiredate = rs.getString("hiredate");

                                     double sal = rs.getDouble("sal");

                                     String job = rs.getString("job");

                                    

                                     //生成Map封装每组参数

                                     Emp emp = new Emp(empno, ename, sal, job, hiredate);

                                    

                                     //map存放到List

                                     list.add(emp);

                                    

                                     //游标后移,获取下一条

                                     if(rs.next() == false){

                                               break;//如果到了最后一条,则不需要再循环获取参数,直接停止循环

                                     }

                            }

                   }catch(Exception e){

                            e.printStackTrace();

                   }finally{

                            ConnectionMgr.closeResource(con, st, rs);

                   }

                  

                   return list;

         }

        

         public static void main(String[] args) {

                   List list = getEmps("select * from emp",6,6);

                   for(int i = 0;i < list.size();i++){

                            Emp e = (Emp) list.get(i);

                            System.out.println(e.getEmpno() + " " + e.getEname() + " " + e.getSal());

                   }

         }

 

图片.png

Powered by 东方博宜教育咨询江苏有限公司  ©2008-2017 www.czos.cn