//模拟查询id为2的信息        String id = "2";        Connection connection = UtilsDemo.getConnection();        String sql = "SELECT * FROM users WHERE id = ?";        PreparedStatement preparedStatement = connection.preparedStatement(sql);        //第一个参数表示第几个占位符【也就是?号】，第二个参数表示值是多少        preparedStatement.setString(1,id);        ResultSet resultSet = preparedStatement.executeQuery();        if (resultSet.next()) {            System.out.println(resultSet.getString("name"));        }        //释放资源        UtilsDemo.release(connection, preparedStatement, resultSet);

        /*        * Statement执行批处理        *        * 优点：        *       可以向数据库发送不同的SQL语句        * 缺点：        *       SQL没有预编译        *       仅参数不同的SQL，需要重复写多条SQL        * */        Connection connection = UtilsDemo.getConnection();        Statement statement = connection.createStatement();        String sql1 = "UPDATE users SET name='zhongfucheng' WHERE id='3'";        String sql2 = "INSERT INTO users (id, name, password, email, birthday)" +                " VALUES('5','nihao','123','ss@qq.com','1995-12-1')";        //将sql添加到批处理        statement.addBatch(sql1);        statement.addBatch(sql2);        //执行批处理        statement.executeBatch();        //清空批处理的sql        statement.clearBatch();        UtilsDemo.release(connection, statement, null);

        /*        * PreparedStatement批处理        *   优点：        *       SQL语句预编译了        *       对于同一种类型的SQL语句，不用编写很多条        *   缺点：        *       不能发送不同类型的SQL语句        *        * */        Connection connection = UtilsDemo.getConnection();        String sql = "INSERT INTO test(id,name) VALUES (?,?)";        PreparedStatement preparedStatement = connection.prepareStatement(sql);        for (int i = 1; i <= 205; i++) {            preparedStatement.setInt(1, i);            preparedStatement.setString(2, (i + "zhongfucheng"));            //添加到批处理中            preparedStatement.addBatch();            if (i %2 ==100) {                //执行批处理                preparedStatement.executeBatch();                //清空批处理【如果数据量太大，所有数据存入批处理，内存肯定溢出】                preparedStatement.clearBatch();            }        }        //不是所有的%2==100，剩下的再执行一次批处理        preparedStatement.executeBatch();        //再清空        preparedStatement.clearBatch();        UtilsDemo.release(connection, preparedStatement, null);

/**用JDBC操作MySQL数据库去操作大文本数据**setCharacterStream(int parameterIndex,java.io.Reader reader,long length)*第二个参数接收的是一个流对象，因为大文本不应该用String来接收，String太大会导致内存溢出*第三个参数接收的是文件的大小** */public class Demo5 {    @Test    public void add() {        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = JdbcUtils.getConnection();            String sql = "INSERT INTO test2 (bigTest) VALUES(?) ";            preparedStatement = connection.prepareStatement(sql);            //获取到文件的路径            String path = Demo5.class.getClassLoader().getResource("BigTest").getPath();            File file = new File(path);            FileReader fileReader = new FileReader(file);            //第三个参数，由于测试的Mysql版本过低，所以只能用int类型的。高版本的不需要进行强转            preparedStatement.setCharacterStream(1, fileReader, (int) file.length());            if (preparedStatement.executeUpdate() > 0) {                System.out.println("插入成功");            }        } catch (SQLException e) {            e.printStackTrace();        } catch (FileNotFoundException e) {            e.printStackTrace();        } finally {            JdbcUtils.release(connection, preparedStatement, null);        }    }    /*    * 读取大文本数据，通过ResultSet中的getCharacterStream()获取流对象数据    *     * */    @Test    public void read() {        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = JdbcUtils.getConnection();            String sql = "SELECT * FROM test2";            preparedStatement = connection.prepareStatement(sql);            resultSet = preparedStatement.executeQuery();            if (resultSet.next()) {                Reader reader = resultSet.getCharacterStream("bigTest");                FileWriter fileWriter = new FileWriter("d:\abc.txt");                char[] chars = new char[1024];                int len = 0;                while ((len = reader.read(chars)) != -1) {                    fileWriter.write(chars, 0, len);                    fileWriter.flush();                }                fileWriter.close();                reader.close();            }        } catch (SQLException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            JdbcUtils.release(connection, preparedStatement, resultSet);        }            }

/** 使用JDBC连接MYsql数据库操作二进制数据* 如果我们要用数据库存储一个大视频的时候，数据库是存储不到的。* 需要设置max_allowed_packet，一般我们不使用数据库去存储一个视频* */public class Demo6 {    @Test    public void add() {        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = JdbcUtils.getConnection();            String sql = "INSERT INTO test3 (blobtest) VALUES(?)";            preparedStatement = connection.prepareStatement(sql);            //获取文件的路径和文件对象            String path = Demo6.class.getClassLoader().getResource("1.wmv").getPath();            File file = new File(path);            //调用方法            preparedStatement.setBinaryStream(1, new FileInputStream(path), (int)file.length());            if (preparedStatement.executeUpdate() > 0) {                System.out.println("添加成功");            }        } catch (SQLException e) {            e.printStackTrace();        } catch (FileNotFoundException e) {            e.printStackTrace();        } finally {            JdbcUtils.release(connection, preparedStatement, null);        }    }    @Test    public void read() {        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = JdbcUtils.getConnection();            String sql = "SELECT * FROM test3";            preparedStatement = connection.prepareStatement(sql);            resultSet = preparedStatement.executeQuery();            //如果读取到数据，就把数据写到磁盘下            if (resultSet.next()) {                InputStream inputStream = resultSet.getBinaryStream("blobtest");                FileOutputStream fileOutputStream = new FileOutputStream("d:\aa.jpg");                int len = 0;                byte[] bytes = new byte[1024];                while ((len = inputStream.read(bytes)) > 0) {                    fileOutputStream.write(bytes, 0, len);                }                fileOutputStream.close();                inputStream.close();            }        } catch (SQLException e) {            e.printStackTrace();        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            JdbcUtils.release(connection, preparedStatement, null);        }    }

//使用JDBC连接Oracle数据库操作二进制数据    /** 对于Oracle数据库和Mysql数据库是有所不同的。* 1.Oracle定义了BLOB字段，但是这个字段不是真正地存储二进制数据* 2.向这个字段存一个BLOB指针，获取到Oracle的BLOB对象,把二进制数据放到这个指针里面,指针指向BLOB字段* 3.需要事务支持** */public class Demo7 {    @Test    public void add() {        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = UtilsDemo.getConnection();            //开启事务            connection.setAutoCommit(false);            //插入一个BLOB指针            String sql = "insert into test4(id,image) values(?,empty_blob())";            preparedStatement = connection.prepareStatement(sql);            preparedStatement.setInt(1, 1);            preparedStatement.executeUpdate();            //把BLOB指针查询出来,得到BLOB对象            String sql2 = "select image from test4 where id= ? for update";            preparedStatement = connection.prepareStatement(sql2);            preparedStatement.setInt(1, 1);            resultSet = preparedStatement.executeQuery();            if (resultSet.next()) {                //得到Blob对象--当成是Oracle的Blob,不是JDBC的,所以要强转[导的是oracle.sql.BLOB包]                BLOB  blob = (BLOB) resultSet.getBlob("image");                //写入二进制数据                OutputStream outputStream = blob.getBinaryOutputStream();                //获取到读取文件读入流                InputStream inputStream = Demo7.class.getClassLoader().getResourceAsStream("01.jpg");                int len=0;                byte[] bytes = new byte[1024];                while ((len = inputStream.read(bytes)) > 0) {                    outputStream.write(bytes, 0, len);                }                outputStream.close();                inputStream.close();                connection.setAutoCommit(true);            }        } catch (SQLException e) {            e.printStackTrace();        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            UtilsDemo.release(connection, preparedStatement, null);        }    }    @Test    public void find() {                Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = UtilsDemo.getConnection();            String sql = "SELECT * FROM test4 WHERE id=1";            preparedStatement = connection.prepareStatement(sql);            resultSet = preparedStatement.executeQuery();            if (resultSet.next()) {                //获取到BLOB对象                BLOB blob = (BLOB) resultSet.getBlob("image");                //将数据读取到磁盘上                InputStream inputStream = blob.getBinaryStream();                FileOutputStream fileOutputStream = new FileOutputStream("d:\zhongfucheng.jpg");                int len=0;                byte[] bytes = new byte[1024];                while ((len = inputStream.read(bytes)) > 0) {                    fileOutputStream.write(bytes, 0, len);                }                inputStream.close();                fileOutputStream.close();            }        } catch (SQLException e) {            e.printStackTrace();        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            UtilsDemo.release(connection, preparedStatement, null);        }    }}

    @Test    public void test() {        Connection connection = null;        PreparedStatement preparedStatement = null;        ResultSet resultSet = null;        try {            connection = JdbcUtils.getConnection();            String sql = "INSERT INTO test(name) VALUES(?)";            preparedStatement = connection.prepareStatement(sql);            preparedStatement.setString(1, "ouzicheng");            if (preparedStatement.executeUpdate() > 0) {                //获取到自动主键列的值                resultSet = preparedStatement.getGeneratedKeys();                if (resultSet.next()) {                    int id = resultSet.getInt(1);                    System.out.println(id);                }            }                    } catch (SQLException e) {            e.printStackTrace();        } finally {            JdbcUtils.release(connection, preparedStatement, null);        }

/*    jdbc调用存储过程    delimiter $$        CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam varchar(255))        BEGIN            SELECT CONCAT('zyxw---', inputParam) into inOutParam;        END $$    delimiter ;*///我们在JDBC调用存储过程,就像在调用方法一样public class Demo9 {    public static void main(String[] args) {        Connection connection = null;        CallableStatement callableStatement = null;        try {            connection = JdbcUtils.getConnection();                        callableStatement = connection.prepareCall("{call demoSp(?,?)}");            callableStatement.setString(1, "nihaoa");                        //注册第2个参数,类型是VARCHAR            callableStatement.registerOutParameter(2, Types.VARCHAR);            callableStatement.execute();                        //获取传出参数[获取存储过程里的值]            String result = callableStatement.getString(2);            System.out.println(result);        } catch (Exception e) {            e.printStackTrace();        }finally {            try {                connection.close();                callableStatement.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }

----------------------------------------------------------------------------------过程#修改mysql语句的结果符为//mysql > delimiter //#定义一个过程，获取users表总记录数，将10设置到变量count中create procedure simpleproc(out count int)begin    select count(id) into count from users;end//#修改mysql语句的结果符为;mysql > delimiter ;#调用过程，将结果覆给变量a，@是定义变量的符号call simpleproc(@a);#显示变量a的值select @a;//以下是Java调用Mysql的过程    String sql = "{call simpleproc(?)}";    Connection conn = JdbcUtil.getConnection();    CallableStatement cstmt = conn.prepareCall(sql);    cstmt.registerOutParameter(1,Types.INTEGER);    cstmt.execute();    Integer count = cstmt.getInt(1);    System.out.println("共有" + count + "人");----------------------------------------------------------------------------------函数#修改mysql语句的结果符为//mysql > delimiter //#定义一个函数，完成字符串拼接create function hello( s char(20) ) returns char(50) return concat('hello，',s,'!');//#修改mysql语句的结果符为;mysql > delimiter ;#调用函数select hello('world');//以下是Java调用Mysql的函数    String sql = "{? = call hello(?)}";    Connection conn = JdbcUtil.getConnection();    CallableStatement cstmt = conn.prepareCall(sql);    cstmt.registerOutParameter(1,Types.VARCHAR);    cstmt.setString(2,"zhaojun");    cstmt.execute();    String value = cstmt.getString(1);    System.out.println(value);    JdbcUtil.close(cstmt);    JdbcUtil.close(conn);