Sql学习11

jDbc的事务的处理

Connection对象

（1） void setAutoCommit(boolean autoCommit)  true为自动提交 false为手动提交 可以在事务开启之前先置为false然后 事务结束后还原为true 不影响其他的sql

 

批处理

使用preparedStatement.addBatch()添加批处理 然后通过

preparedStatement.executeBatch() 执行批处理

 

进行批处理需要设置连接的 rewriteBatchedStatements=true  这个mysql默认是关闭的

 

1. conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test_deletable?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8",  "mysql", "mysql"); // 链接本地MYSQL  

@Test

     public void test2() {

          Properties properties = new Properties();

          try {

               properties.load(ClassLoader.getSystemResourceAsStream("jdbc.properties"));

               Class.forName(properties.getProperty("drivername"));

                   

              String sql = "insert into student values(null,?,?,?,?,null)";

                   

              long startTime = System.currentTimeMillis();

              // 获取连接

              Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties);

              // 这个对象不需要重复创建多次

              PreparedStatement prepareStatement = connection.prepareStatement(sql);

              for (int i = 0; i < 2000; i++) {

                   prepareStatement.setString(1, "名字");

                   prepareStatement.setInt(2, 2);

                   prepareStatement.setString(3, "ceec");

                   prepareStatement.setInt(4, 2);

//                 prepareStatement.setBlob(5, new FileInputStream("./pic/temp.jpg"));  //这个是存储图片为二进制的大文件

                   prepareStatement.addBatch();

                   if (i % 500 == 0) {

                        prepareStatement.executeBatch();

                        prepareStatement.clearBatch();

                   }

              }

              prepareStatement.executeBatch();

              prepareStatement.close();

              connection.close();

              long endTime = System.currentTimeMillis();

              System.out.println("消耗的时间----" + (endTime - startTime));

          } catch (Exception e) {

              e.printStackTrace();

          }

     }

 

 

(2)如何获取自动增长的键值

preparedStatement=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

............

preparedStatement.execteUpdate();

ResultSet rs=preparedStatement.getGeneratedKeys();

Object oid;

    if(rs.next()){

        oid=rs.getObject(1);

    }

Systom.out.println("获取的新订单的编号："+oid)

 

 

查询的代码

@Test

     public void testOne() {

          try {

              Properties properties = new Properties();

               properties.load(ClassLoader.getSystemResourceAsStream("jdbc.properties"));

               Class.forName(properties.getProperty("drivername"));

              // // ②通过gid查询商品的的价格和名称

              String sql_2 = "select gname,price from goodstable where gid = " + 3;

              // 获取连接

              Connection connection_2 = DriverManager.getConnection(properties.getProperty("shopping_url"), properties);

              // // 这个对象不需要重复创建多次

              PreparedStatement prepareStatement_2 = connection_2.prepareStatement(sql_2,

                        Statement.RETURN_GENERATED_KEYS);

              ResultSet resultSet = prepareStatement_2.executeQuery();

              String goodsNname = "";

              double goodsPrice = 0;

              ResultSetMetaData rsmd = resultSet.getMetaData();

              HashMap<String, Object> map = new HashMap();

//这是将所有的列都遍历到map中，然后根据需要获取相应的字段的值

              while (resultSet.next()) {

                   // 把列名的别名和列值分别取出来放到map中作为键值出现(resultSet和rsmd结合得到的就是一个表，和数据库表一样)，

                   // 由ResultSetMetaData得到每一列的别名，

                   for (int i = 0; i < rsmd.getColumnCount(); i++) {

                        String columnLabel = rsmd.getColumnLabel(i + 1);

                        Object columnValue = resultSet.getObject(columnLabel);

                        System.out.println("columnLabel---" + columnLabel + "   columnValue---" + columnValue);

                        map.put(columnLabel, columnValue);

                   }

                   

                   System.out.println(resultSet.getObject(1));

                   System.out.println(resultSet.getObject(2));

                   

                   

              }

              // 遍历map对象，用反射填充对象属性值

              for (Map.Entry<String, Object> entry : map.entrySet()) {

                   String fieldName = entry.getKey();

                   Object fieldValue = entry.getValue();

              }

              System.out.println("goodsNname----" + goodsNname);

              System.out.println("goodsPrice----" + goodsPrice);

              prepareStatement_2.close();

              connection_2.close();

          } catch (Exception e) {

              // TODO: handle exception

              e.printStackTrace();

          }

     }