1 Statement和PreparedStatement的特点
a)对于创建和删除表或数据库，我们可以使用executeUpdate()，该方法返回0，表示未影向表中任何记录
b)对于创建和删除表或数据库，我们可以使用execute()，该方法返回false，表示创建和删除数据库表
c)除了select操作返回true之除，其它的操作都返回false
d)PreparedStatement有发下的特点：
>>解决SQL注入问题，在绑定参数时，动态检测
>>在发送相同结构的SQL时，较Statement效率有所提升
>>使用？占位符替代真实的绑定值
>>项目中，优先使用PreparedStatement
新版的crud：
package cn.itcast.web.jdbc.dao; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import cn.itcast.web.jdbc.util.JdbcUtil; //基于JDBC的CURD操作
public class Crud {
public void create(String name,String gender,float salary){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "insert into user(name,gender,salary) values(?,?,?)";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,gender);
pstmt.setFloat(3,salary);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
public void read(String name){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
//使用PreparedStement的参数使用占位符替代
String sql = "select * from user where name = ?";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
//能过setXxxx()方法为占位符赋值，
//在赋值的过程中动态检测，预防SQL注入问题的发生
pstmt.setString(1,name);
rs = pstmt.executeQuery();
while(rs.next()){
name = rs.getString("name");
String gender = rs.getString("gender");
System.out.println(name+":"+gender);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
//多态原则
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
public static void main(String[] args) {
Crud crud = new Crud();
//crud.read(" 'or true or' ");
crud.create("tim","male",5000);
}
}
*2 Jsp+Servlet+Javabean+Jdbc+Mysql（用户登录）
总结：
a)如何在Servlet处理类似的业务逻辑
doGet/doPost
private login()
private register()
b)学会层与层之间的耦
<%@ page language="java" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<form action="/day13/UserServlet?method=login" method="post">
<table border="1" align="center">
<caption>用户登录</caption>
<tr>
<th>用户名</th>
<td>
<input type="text" name="username"/>
</td>
</tr>
<tr>
<th>密码</th>
<td>
<input type="password" name="password"/>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="提交"/>
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<form action="/day13/UserServlet?method=register" method="post">
<table border="1" align="center">
<caption>用户注册</caption>
<tr>
<th>用户名</th>
<td>
<input type="text" name="username"/>
</td>
</tr>
<tr>
<th>密码</th>
<td>
<input type="password" name="password"/>
</td>
</tr>
<tr>
<th>生日</th>
<td>
<input type="text" name="birthday"/>
</td>
</tr>
<tr>
<th>期望薪水</th>
<td>
<input type="text" name="salary"/>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="提交"/>
</td>
</tr>
</table>
</form>
</body>
</html>
*3 MySQL数据库分页
1)为什么要分页？
2)MySQL数据库中有没有分页的语句？
select * from user LIMIT 第几条记录号-1,需要显示记录的条数;
3)为什么返回List不行，需要返回Page类？
MySQL分页的实现：
•select * from table limit M,N
•M：记录开始索引位置
•N：取多少条记录。
完成WEB页面的分页显示
•先获得需分页显示的记录总数，然后在web页面中显示页码。
•根据页码，从数据库中查询相应的记录显示在web页面中。
•以上两项操作通常使用Page对象进行封装。
use mydb2;
drop table if exists user;
create table if not exists user(
id int primary key auto_increment,
username varchar(20) not null,
password varchar(6) not null,
birthday date not null,
salary float
);
package cn.itcast.web.jdbc.dao; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.itcast.web.jdbc.domain.User;
import cn.itcast.web.jdbc.util.JdbcUtil; public class UserDao implements IUserDao{
//用户登录
public boolean find(String username,String password){
boolean flag = false;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from user where username=? and password=?";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,password);
rs = pstmt.executeQuery();
if(rs.next()){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
return flag;
}
//用户注册
public boolean add(User user){
boolean flag = false;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,user.getUsername());
pstmt.setString(2,user.getPassword());
pstmt.setDate(3,new java.sql.Date(user.getBirthday().getTime()));
pstmt.setFloat(4,user.getSalary());
int i = pstmt.executeUpdate();
if(i>0){
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
return flag;
}
public List<User> find(int start, int size) {
List<User> userList = new ArrayList<User>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from user limit ?,?";
try {
conn = JdbcUtil.getMySqlConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,start);//
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();
}
}