• 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)学会层与层之间的耦

    课堂练习1

    <%@ 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对象进行封装。

       MySQL分页的分析图示

    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();
    }
}
  • 相关阅读:
    Serverless 工程实践 | Serverless 应用开发观念的转变
    如何高效学习 Kubernetes 知识图谱？
    互动赠新书｜当云原生遇到混合云：如何实现“求变”与“求稳”的平衡
    5 款阿里常用代码检测工具，免费用！
    AI与传统编译器
    OpenArkCompiler方舟编译
    传统编译原理
    LLVM基础技术图例
    双极型与低频大功率晶体管
    TVM，Relay，Pass
  • 原文地址：https://www.cnblogs.com/sunhan/p/3542139.html
Copyright © 2020-2023  润新知