JDBC进阶功能2

一,Prepare
package jdbc2;

import Util.jdbcUtil;
import org.junit.Test;

import javax.management.Query;
import java.sql.*;

/**
* Created by dllo on 17/6/2.
*/
public class jdbc1Prepare {
/*
* preparestatement
* 1,可以防止SQL 注入
* 2,采取预编译的操作方式,将sql语句先交给数据库编译好
* 只需要等待执行就可以了;
* 当多个重复语句被执行时效率会比statement高,速度快
* */
@Test
public void t1() throws ClassNotFoundException, SQLException {
Class.forName(“com.mysql.jdbc.Driver”);
Connection conn = DriverManager.getConnection(
“jdbc:mysql://localhost:3306/jdbc”
,”root”
,”111111”
);
// 旧的写法
Statement statement = conn.createStatement();
//今天
//问号就是一个占位符
PreparedStatement pstm = conn.prepareStatement(“INSERT INTO stu VALUES (NULL ,?,?)”);
//第一个参数是?的位置
//与VALUES后面括号中是否有null无关 是否有具体值无关
//只看问号 从1开始数

    pstm.setString(1,"张三");
    pstm.setInt(2,40);

    pstm.executeUpdate();
}
         @Test
public void t2Update() throws SQLException {
    Connection conn = jdbcUtil.getconnection();

    String update = "update  stu set name=? where id =?;";
    //获得预处理对象
    PreparedStatement pstmt = conn.prepareStatement(update);

    // 将参数传到sql语句中
    //查找id 为1 的数据
    //将该条数据的NAME 字段改为刘振洲
    pstmt.setString(1,"liuzhenzhou");
    pstmt.setInt(2,2);

    //执行语句
    pstmt.executeUpdate();
}
@Test
public void t3Query() throws SQLException {
    Connection conn = jdbcUtil.getconnection();
    String Query = "SELECT * FROM stu WHERE name = ?;";
    PreparedStatement pstmt = conn.prepareStatement(Query);
    pstmt.setString(1,"zhangsan");


    ResultSet resultSet =pstmt.executeQuery();
    while (resultSet.next()){
        System.out.println();
    }
}

}
二,Batch
package jdbc2;

import Util.jdbcUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
* Created by dllo on 17/6/2.
*/
public class jdbc2Batch {
public static void main(String[] args) throws SQLException {
Connection conn = jdbcUtil.getconnection();
PreparedStatement pstmt = conn.prepareStatement(“INSERT INTO stu VALUES (?,?,?)”);
pstmt.setInt(1,36);
pstmt.setString(2,”haha”);
pstmt.setInt(3,30);

    //通过这个方法可以实现批处理操作
    //调用这个方法后,会将这SQL先存起来,等待执行
    pstmt.addBatch();

    pstmt.setInt(1,21);
    pstmt.setString(2,"hengheng");
    pstmt.setInt(3,31);

    //又存了一条,等待执行
    pstmt.addBatch();
    //执行批处理中存着的SQL 语句
    // 批处理中各个SQL 语句之间执行成功或失败是无联系的
    //即使第一条语句出现主键重复的错误
    //也不会影响第二条语句将数据插入到数据库里
    pstmt.executeBatch();
}

@Test
public void t1() throws SQLException {
    Connection conn = jdbcUtil.getconnection();
    PreparedStatement pstmt = conn.prepareStatement(
            "UPDATE stu SET  name = 'heihei'WHERE  name ='haha'");
    /*
    * 将上面的更新name为heihei的sql语句
    * 加入到批处理缓存中
    * */
    pstmt.addBatch();
  //再添加一条删除语句到批处理缓存中
    pstmt.addBatch("DELETE  FROM  stu WHERE name = 'hengheng'");

    pstmt.executeBatch();


}

}
三,Transaction
package jdbc2;

import Util.jdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
* Created by dllo on 17/6/2.
*/
public class jsbc3Transaction {
public static void main(String[] args) throws SQLException {
Connection conn = jdbcUtil.getconnection();
//设置提交方式为手动提交
//开启事务
try {
conn.setAutoCommit(false);
//创建一个预处理对象
PreparedStatement pstmt = conn.prepareStatement(
“UPDATE money SET m=? WHERE name = ?”);

        pstmt.setInt(1,900);
        pstmt.setString(2,"曹云金");

        pstmt.executeUpdate();

         int a = 100/0;
        pstmt.setInt(1,1100);
        pstmt.setString(2,"郭德纲");

        pstmt.executeUpdate();

        conn.commit();
    } catch (Exception e) {
        try {
            conn.rollback();
            System.out.println("回滚了");
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

    }
}

}
四,DBUtils
import Util.jdbcUtil;
import jdbc2.Stu;
import jdk.internal.org.objectweb.asm.Handle;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
* Created by dllo on 17/6/2.
*/
public class Jdbc4DBUtils {
/*
* DbUtils 是Apache 提供的一个
* 开源的方便我们操作jdbc的jar包
*
* QueryRunner类,是该jar包的核心类
* 所有的操作数据库的方法都被封装在这个类中
* 更新,查询都是使用QueryRunner类
* */
@Test
public void ti() throws SQLException {
//获得连接对象
Connection conn = jdbcUtil.getconnection();
//建立一个QueryRunner对象 用来执行sql语句
QueryRunner qr = new QueryRunner();

    String sql = "insert into stu values (null,'zyd',55)";
    //调用update方法可以执行增,删,改等sql语句.
    qr.update(conn, sql);

    conn.close();
}

@Test
public void t2() throws SQLException {
    Connection conn = jdbcUtil.getconnection();
    QueryRunner qr = new QueryRunner();

    String queryOne = "select *from Stu where id = 11;";

    Stu query = qr.query(conn, queryOne, new BeanHandler<Stu>(Stu.class));

    System.out.println(query);
}

/*
 * 演示beanlisthandler 的使用
 * 使用beanlisthandler可以得到
 * 一个装载指定类型对象的集合
 */

@Test
public void t3() throws SQLException {
    Connection conn = jdbcUtil.getconnection();
    QueryRunner qr = new QueryRunner();

    String queryall = "select * from stu;";
    List<Stu> query = qr.query(conn, queryall, new BeanListHandler<Stu>(Stu.class));

    //iter +tab
    for (Stu stu : query) {
        System.out.println(toString());
    }
}

@Test
public void t4() throws SQLException {
    Connection conn = jdbcUtil.getconnection();
    QueryRunner qr = new QueryRunner();

    String queryall = "select name,age from stu;";

    List<Map<String, Object>> query = qr.query(conn, queryall, new MapListHandler());
    System.out.println(query);
    //iter + tab
    for (Map<String, Object> stringObjectMap : query) {
        for (String s : stringObjectMap.keySet()) {
            System.out.println(query);
        }
    }

}

@Test
public void t5() throws SQLException {
    Connection conn = jdbcUtil.getconnection();
    QueryRunner qr = new QueryRunner();

    String sql = "select *from stu;";

    List<Object[]> query = qr.query(conn, sql, new ArrayListHandler());
    for (Object[] objects : query) {
        for (Object object : objects) {
            System.out.printf(object + "_");
        }
        System.out.println();
    }

    System.out.println(query);
}

/*
* resultsethandler 是接口
* beamhandler ,beanlisthandler等类都
* 实现了resultsethandler 是接口
*
* 接口回调
* @
* */
@Test
public void t6() throws SQLException {
    Connection conn = jdbcUtil.getconnection();
    QueryRunner qr = new QueryRunner();
    String sql = "select *from stu where id = 1;";
    qr.query(conn, sql, new ResultSetHandler<Stu>() {
        @Override
        public Stu handle(ResultSet resultSet) throws SQLException {
            while (resultSet.next()) {
                Stu stu = new Stu();
                stu.setName(resultSet.getString(2));
                stu.setId(resultSet.getInt(1));
                stu.setAge(resultSet.getInt(3));
                return stu;
            }

            return null;
        }
    });

}

}