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

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语句时,需重复写上很多条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();
}
}
上一篇:SpringMVC+MyBatis项目分析与开发实例


下一篇:cmd光标移动