😉JDBC

初始化与关闭流

    private Connection connection;
    private PreparedStatement preparedStatement;
    @Before
    public void init() throws ClassNotFoundException, SQLException {
        // 加载驱动
        //Driver，import org.apache.phoenix.queryserver.client.Driver;
        Class.forName("org.apache.phoenix.queryserver.client.Driver");
        // 获取连接
        String url = ThinClientUtil.getConnectionUrl("hadoop102", 8765);
        connection = DriverManager.getConnection(url);
        connection.setAutoCommit(true);
    }
    public void close() throws SQLException {
        // 执行
        // 关闭
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection!=null) {
            connection.close();
        }
    }

DML增删改查

@Test
    public void upsert() throws SQLException {
        // preparestatement有预编译功能，后面需要用？作为参数，防止sql注入
        preparedStatement = connection.prepareStatement("upsert into student2 values(?,?,?)");
        // setString设置上面的？参数
        preparedStatement.setString(1,"1002");
        preparedStatement.setString(2,"ldc");
        preparedStatement.setString(3,"33");
        preparedStatement.executeUpdate();
    }
@Test
    public void upsertBatch() throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("upsert into student2 values(?,?,?)");
        for (int i=0;i<1200;i++) {
            preparedStatement.setString(1,"1003"+i);
            preparedStatement.setString(2,"ldc"+i);
            preparedStatement.setString(3,"12"+1);
            preparedStatement.addBatch();
            if (i%500==0){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        // 提交最后一个不满500条数据的批次
        preparedStatement.executeBatch();
    }
    @Test
    public void query() throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("select * from student2 where id > ?");
        preparedStatement.setString(1,"1003");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String name = resultSet.getString("name");
            String age = resultSet.getString("age");
            System.out.println("id="+id+"name="+name+"age="+age);
        }
    }
    @Test
    public void delete() throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("delete from student2 where id > ?");
        preparedStatement.setString(1,"100310");
        preparedStatement.executeUpdate();
    }

DDL

    @Test
    public void createTable() throws ClassNotFoundException, SQLException {
        // 获取statement对象
        preparedStatement = connection.prepareStatement("create table student2(" +
                "id varchar primary key," +
                "name varchar," +
                "age varchar)COLUMN_ENCODED_BYTES=0");
        // 参数赋值
        //执行
        preparedStatement.execute();
    }
    @Test
    public void dropTable() throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("drop table student2");
        preparedStatement.execute();
    }