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

时间：2023-01-01 18:24:20

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

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

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

Statement和PreparedStatement的特点 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);//

            pstmt.setInt(2,size);//

            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){//

        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();

    }

}

相关文章