Statement和PreparedStatement的特点 MySQL数据库分页存取大对象批处理获取数据库主键值

1 Statement和PreparedStatement的特点
a)对于创建和删除表或数据库，我们可以使用executeUpdate()，该方法返回0，表示未影向表中任何记录
b)对于创建和删除表或数据库，我们可以使用execute()，该方法返回false，表示创建和删除数据库表
c)除了select操作返回true之除，其它的操作都返回false
d)PreparedStatement有发下的特点：
    >>解决SQL注入问题，在绑定参数时，动态检测
    >>在发送相同结构的SQL时，较Statement效率有所提升
    >>使用？占位符替代真实的绑定值
    >>项目中，优先使用PreparedStatement

新版的crud：

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import cn.itcast.web.jdbc.util.JdbcUtil;

//基于JDBC的CURD操作
public class Crud {
    public void create(String name,String gender,float salary){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into user(name,gender,salary) values(?,?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setString(2,gender);
            pstmt.setFloat(3,salary);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    public void read(String name){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        //使用PreparedStement的参数使用占位符替代
        String sql = "select * from user where name = ?";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            //能过setXxxx()方法为占位符赋值，
            //在赋值的过程中动态检测，预防SQL注入问题的发生
            pstmt.setString(1,name);
            rs = pstmt.executeQuery();
            while(rs.next()){
                name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(name+":"+gender);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            //多态原则
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    public static void main(String[] args) {
        Crud crud = new Crud();
        //crud.read(" 'or true or' ");
        crud.create("tim","male",5000);
    }
}

*2 Jsp+Servlet+Javabean+Jdbc+Mysql（用户登录）
   总结：
      a)如何在Servlet处理类似的业务逻辑
        doGet/doPost
        private login()
        private register()

b)学会层与层之间的耦

<%@ page language="java" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <body>
      <form action="/day13/UserServlet?method=login" method="post">
          <table border="1" align="center">
              <caption>用户登录</caption>
              <tr>
                  <th>用户名</th>
                  <td>
                      <input type="text" name="username"/>
                  </td>
              </tr>
              <tr>
                  <th>密码</th>
                  <td>
                      <input type="password" name="password"/>
                  </td>
              </tr>
              <tr>
                  <td colspan="2" align="center">
                      <input type="submit" value="提交"/>
                  </td>
              </tr>
          </table>
      </form>
  </body>
</html>

<%@ page language="java" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <body>
      <form action="/day13/UserServlet?method=register" method="post">
          <table border="1" align="center">
              <caption>用户注册</caption>
              <tr>
                  <th>用户名</th>
                  <td>
                      <input type="text" name="username"/>
                  </td>
              </tr>
              <tr>
                  <th>密码</th>
                  <td>
                      <input type="password" name="password"/>
                  </td>
              </tr>
              <tr>
                  <th>生日</th>
                  <td>
                      <input type="text" name="birthday"/>
                  </td>
              </tr>
              <tr>
                  <th>期望薪水</th>
                  <td>
                      <input type="text" name="salary"/>
                  </td>
              </tr>
               <tr>
                  <td colspan="2" align="center">
                      <input type="submit" value="提交"/>
                  </td>
              </tr>
          </table>
      </form>
  </body>
</html>

*3 MySQL数据库分页
1)为什么要分页？
2)MySQL数据库中有没有分页的语句？
select * from user LIMIT 第几条记录号-1,需要显示记录的条数;
3)为什么返回List不行，需要返回Page类？

MySQL分页的实现：

•select * from table limit M,N

•M：记录开始索引位置

•N：取多少条记录。

完成WEB页面的分页显示

•先获得需分页显示的记录总数，然后在web页面中显示页码。

•根据页码，从数据库中查询相应的记录显示在web页面中。

•以上两项操作通常使用Page对象进行封装。

use mydb2;
drop table if exists user;
create table if not exists user(
 id int primary key auto_increment,
 username varchar(20) not null,
 password varchar(6) not null,
 birthday date not null,
 salary float
);

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.itcast.web.jdbc.domain.User;
import cn.itcast.web.jdbc.util.JdbcUtil;

public class UserDao implements IUserDao{
    //用户登录
    public boolean find(String username,String password){
        boolean flag = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from user where username=? and password=?";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,username);
            pstmt.setString(2,password);
            rs = pstmt.executeQuery();
            if(rs.next()){
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
        return flag;
    }
    //用户注册
    public boolean add(User user){
        boolean flag = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,user.getUsername());
            pstmt.setString(2,user.getPassword());
            pstmt.setDate(3,new java.sql.Date(user.getBirthday().getTime()));
            pstmt.setFloat(4,user.getSalary());
            int i = pstmt.executeUpdate();
            if(i>0){
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
        return flag;
    }
    public List<User> find(int start, int size) {
        List<User> userList = new ArrayList<User>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from user limit ?,?";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,start);//0
            pstmt.setInt(2,size);//5
            rs = pstmt.executeQuery();
            //关系和对象映射，即ORMapping
            while(rs.next()){
                User user = new User();
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                java.sql.Date birthday = rs.getDate("birthday");
                float salary = rs.getFloat("salary");
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setBirthday(birthday);
                user.setSalary(salary);
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
        return userList;
    }
    //取得总记录数
    public int getAllRecordNO() {
        int sum = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select count(*) from user";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                Long temp = (Long) rs.getObject(1);
                sum = temp.intValue();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
        return sum;
    }
}

package cn.itcast.web.jdbc.factory;

import cn.itcast.web.jdbc.dao.IUserDao;
import cn.itcast.web.jdbc.dao.UserDao;

//Dao(单线程)单例工厂
public class DaoFactory {
    //NO1
    private DaoFactory(){}
    
    //NO2
    private static DaoFactory daoFactory;
    
    //NO3
    public static DaoFactory getDaoFactory(){
        if(daoFactory==null){
            daoFactory = new DaoFactory();
        }
        return daoFactory;
    }
    
    //取得UserDao的实例(多态的体现)
    public IUserDao getUserDao(){
        return new UserDao();
    }
}

package cn.itcast.web.jdbc.dao;

import java.util.List;
import cn.itcast.web.jdbc.domain.User;

public interface IUserDao {
    //用户登录
    public boolean find(String username,String password);
    //用户注册
    public boolean add(User user);
    //分页查询所有用户信息
    public List<User> find(int start,int size);
    //取得总记录数
    public int getAllRecordNO();
}

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import cn.itcast.web.jdbc.util.JdbcUtil;

//基于JDBC的CURD操作
public class Crud {
    public void create(String name,String gender,float salary){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into user(name,gender,salary) values(?,?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setString(2,gender);
            pstmt.setFloat(3,salary);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    public void read(String name){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        //使用PreparedStement的参数使用占位符替代
        String sql = "select * from user where name = ?";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            //能过setXxxx()方法为占位符赋值，
            //在赋值的过程中动态检测，预防SQL注入问题的发生
            pstmt.setString(1,name);
            rs = pstmt.executeQuery();
            while(rs.next()){
                name = rs.getString("name");
                String gender = rs.getString("gender");
                System.out.println(name+":"+gender);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            //多态原则
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    public static void main(String[] args) {
        Crud crud = new Crud();
        //crud.read(" 'or true or' ");
        crud.create("tim","male",5000);
    }
}

package cn.itcast.web.jdbc.service;

import java.util.List;

import cn.itcast.web.jdbc.dao.IUserDao;
import cn.itcast.web.jdbc.domain.Page;
import cn.itcast.web.jdbc.domain.User;
import cn.itcast.web.jdbc.factory.DaoFactory;

public class UserService {
    //private UserDao userDao = new UserDao();
    //private IUserDao iUserDao = new UserDao();
    private IUserDao iUserDao = DaoFactory.getDaoFactory().getUserDao();
    
    //用户登录
    public boolean login(String username,String password){
        //return userDao.find(username,password);
        return iUserDao.find(username,password);
    }
    //用户注册
    public boolean add(User user){
        return iUserDao.add(user);
    }
    //分页查询所有用户信息
    public Page fy(int currPageNO){//1
        Page page = new Page();
        
        page.setCurrPageNO(currPageNO);//封装当前显示的页号
        
        int allRecordNO = iUserDao.getAllRecordNO();
        page.setAllRecordNO(allRecordNO);//封装总记录数，总页数
        
        int size = page.getPerPageNO();
        int start = (page.getCurrPageNO()-1) * page.getPerPageNO();
        
        List<User> userList = iUserDao.find(start,size);
        page.setUserList(userList);//封装当前显示的内容
        
        return page;
    }
}

package cn.itcast.web.jdbc.web;

import java.io.IOException;
import java.util.Locale;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.locale.converters.DateLocaleConverter;
import cn.itcast.web.jdbc.dao.Demo2;
import cn.itcast.web.jdbc.domain.Page;
import cn.itcast.web.jdbc.domain.User;
import cn.itcast.web.jdbc.service.UserService;

public class UserServlet extends HttpServlet {
    //将像片保存到数据库
    private void upload(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        String photoPath = request.getParameter("photo");
        Demo2 demo = new Demo2();
        boolean flag = demo.write(photoPath);
        if(flag){
            request.setAttribute("message","成功");
        }else{
            request.setAttribute("message","失败");
        }
        request.getRequestDispatcher("/message.jsp").forward(request,response);
    }
    //分页查询所有用户信息
    private void fy(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        String currPageNO = request.getParameter("currPageNO");
        if(currPageNO==null){
            //默认用户访问第1页
            currPageNO = "1";
        }
        UserService userService = new UserService();
        Page page = userService.fy(Integer.parseInt(currPageNO));
        request.setAttribute("page",page);
        request.getRequestDispatcher("/WEB-INF/fy.jsp").forward(request,response);
    }
    //请求发分器
    public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        String method = request.getParameter("method");
        if(method!=null){
            if("fy".equals(method)){
                this.fy(request,response);
            }
        }else{
            this.fy(request,response);
        }
    }
    //请求发分器
    public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String method = request.getParameter("method");
        if(method!=null){
            if("login".equals(method)){
                this.login(request,response);
            }else if("register".equals(method)){
                this.register(request,response);
            }else if("upload".equals(method)){
                this.upload(request,response);
            }
        }
    }
    //用户登录
    private void login(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        UserService userService = new UserService();
        boolean flag = userService.login(username,password);
        if(flag){
            request.setAttribute("message","登录成功");
        }else{
            request.setAttribute("message","登录失败");
        }
        request.getRequestDispatcher("/message.jsp").forward(request,response);
    }
    //用户注册
    private void register(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        java.util.Enumeration<String> enums = request.getParameterNames();
        User user = new User();
        ConvertUtils.register(
                new DateLocaleConverter(Locale.getDefault(),"yyyy-MM-dd"),
                java.util.Date.class);
        while(enums.hasMoreElements()){
            String key = enums.nextElement();
            String[] values = request.getParameterValues(key);
            try {
                BeanUtils.setProperty(user,key,values);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        UserService userService = new UserService();
        boolean flag = userService.add(user);
        if(flag){
            request.setAttribute("message","注册成功");
        }else{
            request.setAttribute("message","注册失败");
        }
        request.getRequestDispatcher("/message.jsp").forward(request,response);
    }
}

package cn.itcast.web.jdbc.domain;

import java.util.ArrayList;
import java.util.List;

//分页类
public class Page {
    private int allRecordNO;//总记录数
    private int perPageNO = 10;//每页显示记录数
    private int allPageNO;//总页数(总记录数/每页显示记录数)
    private int currPageNO = 1;//显示的当前页号
    private List<User> userList = new ArrayList<User>();//当前页的所有信息
    public Page(){}
    public int getAllRecordNO() {
        return allRecordNO;
    }
    public void setAllRecordNO(int allRecordNO) {
        this.allRecordNO = allRecordNO;
        if(this.allRecordNO % this.perPageNO == 0){
            this.allPageNO = this.allRecordNO / this.perPageNO;
        }else{
            this.allPageNO = this.allRecordNO / this.perPageNO + 1;
        }
    }
    public int getPerPageNO() {
        return perPageNO;
    }
    public void setPerPageNO(int perPageNO) {
        this.perPageNO = perPageNO;
    }
    public int getAllPageNO() {
        return allPageNO;
    }
    public void setAllPageNO(int allPageNO) {
        this.allPageNO = allPageNO;
    }
    public int getCurrPageNO() {
        return currPageNO;
    }
    public void setCurrPageNO(int currPageNO) {
        this.currPageNO = currPageNO;
    }
    public List<User> getUserList() {
        return userList;
    }
    public void setUserList(List<User> userList) {
        this.userList = userList;
    }
}

*4 存取大对象
LOB
    a)Character LOB -> CLOB (Text有四个子类型)[字符]
      存：
    pstmt.setString(1,UUID.randomUUID().toString());
    URL url = Demo1.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/62.txt");
    File file = new File(url.getPath());
    Reader reader = new FileReader(file);
    pstmt.setCharacterStream(2,reader,(int)file.length());

      取：
    Reader reader = rs.getCharacterStream("content");
    Writer writer = new FileWriter("d:\62.txt");
    int len = 0;
    char[] cuf = new char[1024];
        while( (len=reader.read(cuf))>0 ){
            writer.write(cuf,0,len);
        }
    reader.close();
    writer.close();

注意：在能完成业务的情况下，尽早关闭连接对象
关闭连接对象，不能够发送SQL到数据库方，并不是不能读写数据

    b)Binary    LOB -> BLOB (Blob有四个子类型)[字节]

     存：
    pstmt.setString(1,UUID.randomUUID().toString());
    URL url = Demo2.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/d1.jpg");
    File file = new File(url.getPath());
    InputStream is = new FileInputStream(file);
    pstmt.setBinaryStream(2,is,(int)file.length());

      取：
    is = rs.getBinaryStream("content");
    os = new FileOutputStream("d:\d1.jpg");
    int len = 0;
    byte[] buf = new byte[1024];
    while( (len=is.read(buf))>0 ){
        os.write(buf,0,len);
    }

在实际开发中，程序需要把大文本或二进制数据保存到数据库。

基本概念：大数据也称之为LOB(Large Objects)，LOB又分为：

•clob和blob

•clob用于存储大文本。

•blob用于存储二进制数据，例如图像、声音、二进制文等。

对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是text，text和blob分别又分为：

•TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT

•TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

对于MySQL中的Text类型，可调用如下方法设置：

PreparedStatement.setCharacterStream(index, reader, length);//注意length长度须设置，并且设置为int型

对MySQL中的Text类型，可调用如下方法获取：

reader = resultSet. getCharacterStream(i);

reader = resultSet.getClob(i).getCharacterStream();

string s = resultSet.getString(i);

对于MySQL中的BLOB类型，可调用如下方法设置：

PreparedStatement. setBinaryStream(i, inputStream, length);

对MySQL中的BLOB类型，可调用如下方法获取：

InputStream in = resultSet.getBinaryStream(i);

InputStream in = resultSet.getBlob(i).getBinaryStream();

package cn.itcast.web.jdbc.dao;

import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.UUID;

import cn.itcast.web.jdbc.util.JdbcUtil;

/*
drop table if exists test_clob;
create table if not exists test_clob(
 id varchar(40) primary key,
 content text
);
*/
public class Demo1 {
    //将CLOB类型的数据从MySQL数据库取出，放到d:62.txt
    public static void read() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from test_clob";
        Reader reader = null;
        Writer writer = null;
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                reader = rs.getCharacterStream("content");
            }
        } catch (Exception e) {
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
        try {
            writer = new FileWriter("d:\62.txt");
            int len = 0;
            char[] cuf = new char[1024];
            while( (len=reader.read(cuf))>0 ){
                writer.write(cuf,0,len);
            }
        } catch (Exception e) {
        }finally{
            if(reader!=null){
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(writer!=null){
                try {
                    writer.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    //写CLOB类型的数据存入MySQL数据库
    public static void write() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into test_clob(id,content) values(?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,UUID.randomUUID().toString());
            URL url = Demo1.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/62.txt");
            File file = new File(url.getPath());
            Reader reader = new FileReader(file);
            pstmt.setCharacterStream(2,reader,(int)file.length());
            int i = pstmt.executeUpdate();
            System.out.println(i>0?"成功":"失败");
        } catch (Exception e) {
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    public static void main(String[] args) {
        //write();
        read();
    }
}

package cn.itcast.web.jdbc.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.UUID;
import cn.itcast.web.jdbc.util.JdbcUtil;

/*
drop table if exists test_blob;
create table test_blob(
 id varchar(40) primary key,
 content mediumblob
); 
*/
public class Demo2 {
    public boolean write(String photoPath) {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into test_blob(id,content) values(?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,UUID.randomUUID().toString());
            File file = new File(photoPath);
            InputStream is = new FileInputStream(file);
            pstmt.setBinaryStream(2,is,(int)file.length());
            int i = pstmt.executeUpdate();
            if(i>0){
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
        return flag;
    }
    //将BLOB类型数据存入数据库
    public static void write() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "insert into test_blob(id,content) values(?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            //绑定ID
            pstmt.setString(1,UUID.randomUUID().toString());
            //取得图片的路径
            URL url = Demo2.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/d1.jpg");
            //封装成File对象
            File file = new File(url.getPath());
            //取得字节输入流
            InputStream is = new FileInputStream(file);
            //绑定CONTENT
            //参数1占位符的编号，从1开始
            //参数2文件字节输入流
            //参数3文件的大小
            pstmt.setBinaryStream(2,is,(int)file.length());
            int i = pstmt.executeUpdate();
            System.out.println(i>0?"成功":"失败");
        } catch (Exception e) {
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    //将BLOB类型数据从数据库中取出
    public static void read() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from test_blob";
        InputStream is = null;
        OutputStream os = null;
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                is = rs.getBinaryStream("content");
            }
        } catch (Exception e) {
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
        try {
            os = new FileOutputStream("d:\d1.jpg");
            int len = 0;
            byte[] buf = new byte[1024];
            while( (len=is.read(buf))>0 ){
                os.write(buf,0,len);
            }
        } catch (Exception e) {
        }finally{
            if(is!=null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(os!=null){
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void main(String[] args) {
        //write();
        read();
    }
}

*5 批处理
1)想发送多条SQL，又要降低与数据库系统的交互，这时使用批处理
2)Statement对象：适合对不同结构的SQL做批处理操作
3)PreparedStatement对象：适合对相同结构的SQL做批处理操作
业务场景：当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。

实现批处理有两种方式，第一种方式：

•Statement.addBatch(sql)

执行批处理SQL语句

•executeBatch()方法：执行批处理命令

•clearBatch()方法：清除批处理命令

Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
    conn = JdbcUtil.getConnection();
    String sql1 = "insert into user(name,password,email,birthday) 
    values('kkk','123','abc@sina.com','1978-08-08')";
    String sql2 = "update user set password='123456' where id=3";
    st = conn.createStatement();
    st.addBatch(sql1);  //把SQL语句加入到批命令中
     st.addBatch(sql2);  //把SQL语句加入到批命令中
    st.executeBatch();
} finally{
    JdbcUtil.free(conn, st, rs);
}

采用Statement.addBatch(sql)方式实现批处理：

•优点：可以向数据库发送多条不同的ＳＱＬ语句。

•缺点：

•SQL语句没有预编译。

•当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：

Insert into user(name,password) values(‘aa’,’111’);

Insert into user(name,password) values(‘bb’,’222’);

Insert into user(name,password) values(‘cc’,’333’);

Insert into user(name,password) values(‘dd’,’444’);

实现批处理的第二种方式：

•PreparedStatement.addBatch()

conn = JdbcUtil.getConnection();
        String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
        st = conn.prepareStatement(sql);
        for(int i=0;i<50000;i++){
        st.setString(1, "aaa" + i);
        st.setString(2, "123" + i);
        st.setString(3, "aaa" + i + "@sina.com");
        st.setDate(4,new Date(1980, 10, 10));

        st.addBatch();
        if(i%1000==0){
        st.executeBatch();
        st.clearBatch();
        }
        }
        st.executeBatch();
        st.clearBatch();

采用PreparedStatement.addBatch()实现批处理

•优点：发送的是预编译后的SQL语句，执行效率高。

•缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.itcast.web.jdbc.util.JdbcUtil;

//Statement和PreparedStatment的批处理
public class Demo3 {
    public static void statementBatch() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String insertSQL = "insert into user(username,password,birthday,salary) values('jack','000111','2011-10-26',5000)";
        String updateSQL = "update user set username='杰克' where username='jack'";
        try {
            conn = JdbcUtil.getMySqlConnection();
            stmt = conn.createStatement();
            //将需要执行的多条命令加入到批对象中
            stmt.addBatch(insertSQL);
            stmt.addBatch(updateSQL);
            //一次性发送批对象到数据库端执行,返回每条SQL的结果
            int[] is = stmt.executeBatch();
            //将批对象清空
            stmt.clearBatch();
            //显示结果
            System.out.println(is[0]+":"+is[1]);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(stmt);
            JdbcUtil.close(conn);
        }
    }
    public static void preparedBatch() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String insertSQL = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(insertSQL);
            long begin = System.currentTimeMillis();
            for(int i=1;i<=1000;i++){
                pstmt.setString(1,"jack"+i);
                pstmt.setString(2,"111111");
                pstmt.setDate(3,new java.sql.Date(12345));
                pstmt.setFloat(4,5000);
                //加入到批对象中
                pstmt.addBatch();
                if(i%100==0){
                    //执行批对象
                    pstmt.executeBatch();
                    //清空批对象
                    pstmt.clearBatch();
                }
            }
            //执行批对象
            pstmt.executeBatch();
            //清空批对象
            pstmt.clearBatch();
            long end = System.currentTimeMillis();
            System.out.println((end-begin)/1000+"秒");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    public static void main(String[] args) {
        //statementBatch();
        preparedBatch();
    }
}

6 获取数据库主键值
1)当需要获取刚插入主键信息的时候，需要使用获取主键值方法
2)关键代码：
    pstmt = conn.prepareStatement(sqlA,Statement.RETURN_GENERATED_KEYS);
    rs = pstmt.getGeneratedKeys();
    if(rs.next()){
        Long temp = (Long) rs.getObject(1);
        pid = temp.intValue();
    }

Connection conn = JdbcUtil.getConnection();

String sql = "insert into user(name,password,email,birthday) 
            values('abc','123','abc@sina.com','1978-08-08')";
PreparedStatement st = conn.
            prepareStatement(sql,Statement.RETURN_GENERATED_KEYS );

st.executeUpdate();
ResultSet rs = st.getGeneratedKeys();  //得到插入行的主键
if(rs.next())
    System.out.println(rs.getObject(1));

package cn.itcast.web.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.itcast.web.jdbc.util.JdbcUtil;

//Statement和PreparedStatment的批处理
public class Demo3 {
    public static void statementBatch() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String insertSQL = "insert into user(username,password,birthday,salary) values('jack','000111','2011-10-26',5000)";
        String updateSQL = "update user set username='杰克' where username='jack'";
        try {
            conn = JdbcUtil.getMySqlConnection();
            stmt = conn.createStatement();
            //将需要执行的多条命令加入到批对象中
            stmt.addBatch(insertSQL);
            stmt.addBatch(updateSQL);
            //一次性发送批对象到数据库端执行,返回每条SQL的结果
            int[] is = stmt.executeBatch();
            //将批对象清空
            stmt.clearBatch();
            //显示结果
            System.out.println(is[0]+":"+is[1]);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(stmt);
            JdbcUtil.close(conn);
        }
    }
    public static void preparedBatch() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String insertSQL = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
        try {
            conn = JdbcUtil.getMySqlConnection();
            pstmt = conn.prepareStatement(insertSQL);
            long begin = System.currentTimeMillis();
            for(int i=1;i<=1000;i++){
                pstmt.setString(1,"jack"+i);
                pstmt.setString(2,"111111");
                pstmt.setDate(3,new java.sql.Date(12345));
                pstmt.setFloat(4,5000);
                //加入到批对象中
                pstmt.addBatch();
                if(i%100==0){
                    //执行批对象
                    pstmt.executeBatch();
                    //清空批对象
                    pstmt.clearBatch();
                }
            }
            //执行批对象
            pstmt.executeBatch();
            //清空批对象
            pstmt.clearBatch();
            long end = System.currentTimeMillis();
            System.out.println((end-begin)/1000+"秒");
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            JdbcUtil.close(rs);
            JdbcUtil.close(pstmt);
            JdbcUtil.close(conn);
        }
    }
    public static void main(String[] args) {
        //statementBatch();
        preparedBatch();
    }
}

相关阅读:
6. Flask请求和响应
 5. Flask模板
 FW:Software Testing
What is the difference between modified duration, effective duration and duration?
How to push master to QA branch in GIT
FTPS Firewall
Query performance optimization of Vertica
(Forward)5 Public Speaking Tips That'll Prepare You for Any Interview
(转)The remote certificate is invalid according to the validation procedure
Change
原文地址：https://www.cnblogs.com/sunhan/p/3542139.html

Statement和PreparedStatement的特点 MySQL数据库分页 存取大对象 批处理 获取数据库主键值

Statement和PreparedStatement的特点 MySQL数据库分页存取大对象批处理获取数据库主键值