Statement和PreparedStatement的特点 MySQL数据库分页 存取大对象 批处理 获取数据库主键值
1 Statement和PreparedStatement的特点
a)对于创建和删除表或数据库，我们可以使用executeUpdate()，该方法返回0，表示未影向表中任何记录
b)对于创建和删除表或数据库，我们可以使用execute()，该方法返回false，表示创建和删除数据库表
c)除了select操作返回true之除，其它的操作都返回false
d)PreparedStatement有发下的特点：
>>解决SQL注入问题，在绑定参数时，动态检测
>>在发送相同结构的SQL时，较Statement效率有所提升
>>使用？占位符替代真实的绑定值
>>项目中，优先使用PreparedStatement
新版的crud：
package cn.itcast.web.jdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import cn.itcast.web.jdbc.util.JdbcUtil; //基于JDBC的CURD操作 public class Crud { public void create(String name,String gender,float salary){ Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into user(name,gender,salary) values(?,?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,name); pstmt.setString(2,gender); pstmt.setFloat(3,salary); pstmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public void read(String name){ Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; //使用PreparedStement的参数使用占位符替代 String sql = "select * from user where name = ?"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); //能过setXxxx()方法为占位符赋值， //在赋值的过程中动态检测，预防SQL注入问题的发生 pstmt.setString(1,name); rs = pstmt.executeQuery(); while(rs.next()){ name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(name+":"+gender); } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); //多态原则 JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public static void main(String[] args) { Crud crud = new Crud(); //crud.read(" 'or true or' "); crud.create("tim","male",5000); } }
*2 Jsp+Servlet+Javabean+Jdbc+Mysql（用户登录）
总结：
a)如何在Servlet处理类似的业务逻辑
doGet/doPost
private login()
private register()
b)学会层与层之间的耦
<%@ page language="java" pageEncoding="UTF-8"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <body> <form action="/day13/UserServlet?method=login" method="post"> <table border="1" align="center"> <caption>用户登录</caption> <tr> <th>用户名</th> <td> <input type="text" name="username"/> </td> </tr> <tr> <th>密码</th> <td> <input type="password" name="password"/> </td> </tr> <tr> <td colspan="2" align="center"> <input type="submit" value="提交"/> </td> </tr> </table> </form> </body> </html>
<%@ page language="java" pageEncoding="UTF-8"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <body> <form action="/day13/UserServlet?method=register" method="post"> <table border="1" align="center"> <caption>用户注册</caption> <tr> <th>用户名</th> <td> <input type="text" name="username"/> </td> </tr> <tr> <th>密码</th> <td> <input type="password" name="password"/> </td> </tr> <tr> <th>生日</th> <td> <input type="text" name="birthday"/> </td> </tr> <tr> <th>期望薪水</th> <td> <input type="text" name="salary"/> </td> </tr> <tr> <td colspan="2" align="center"> <input type="submit" value="提交"/> </td> </tr> </table> </form> </body> </html>
*3 MySQL数据库分页
1)为什么要分页？
2)MySQL数据库中有没有分页的语句？
select * from user LIMIT 第几条记录号-1,需要显示记录的条数;
3)为什么返回List不行，需要返回Page类？
MySQL分页的实现：
•select * from table limit M,N
•M：记录开始索引位置
•N：取多少条记录。
完成WEB页面的分页显示
•先获得需分页显示的记录总数，然后在web页面中显示页码。
•根据页码，从数据库中查询相应的记录显示在web页面中。
•以上两项操作通常使用Page对象进行封装。
use mydb2; drop table if exists user; create table if not exists user( id int primary key auto_increment, username varchar(20) not null, password varchar(6) not null, birthday date not null, salary float );
package cn.itcast.web.jdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.web.jdbc.domain.User; import cn.itcast.web.jdbc.util.JdbcUtil; public class UserDao implements IUserDao{ //用户登录 public boolean find(String username,String password){ boolean flag = false; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from user where username=? and password=?"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,username); pstmt.setString(2,password); rs = pstmt.executeQuery(); if(rs.next()){ flag = true; } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } return flag; } //用户注册 public boolean add(User user){ boolean flag = false; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,user.getUsername()); pstmt.setString(2,user.getPassword()); pstmt.setDate(3,new java.sql.Date(user.getBirthday().getTime())); pstmt.setFloat(4,user.getSalary()); int i = pstmt.executeUpdate(); if(i>0){ flag = true; } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } return flag; } public List<User> find(int start, int size) { List<User> userList = new ArrayList<User>(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from user limit ?,?"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setInt(1,start);//0 pstmt.setInt(2,size);//5 rs = pstmt.executeQuery(); //关系和对象映射，即ORMapping while(rs.next()){ User user = new User(); int id = rs.getInt("id"); String username = rs.getString("username"); String password = rs.getString("password"); java.sql.Date birthday = rs.getDate("birthday"); float salary = rs.getFloat("salary"); user.setId(id); user.setUsername(username); user.setPassword(password); user.setBirthday(birthday); user.setSalary(salary); userList.add(user); } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } return userList; } //取得总记录数 public int getAllRecordNO() { int sum = 0; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select count(*) from user"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ Long temp = (Long) rs.getObject(1); sum = temp.intValue(); } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } return sum; } }
package cn.itcast.web.jdbc.factory; import cn.itcast.web.jdbc.dao.IUserDao; import cn.itcast.web.jdbc.dao.UserDao; //Dao(单线程)单例工厂 public class DaoFactory { //NO1 private DaoFactory(){} //NO2 private static DaoFactory daoFactory; //NO3 public static DaoFactory getDaoFactory(){ if(daoFactory==null){ daoFactory = new DaoFactory(); } return daoFactory; } //取得UserDao的实例(多态的体现) public IUserDao getUserDao(){ return new UserDao(); } }
package cn.itcast.web.jdbc.dao; import java.util.List; import cn.itcast.web.jdbc.domain.User; public interface IUserDao { //用户登录 public boolean find(String username,String password); //用户注册 public boolean add(User user); //分页查询所有用户信息 public List<User> find(int start,int size); //取得总记录数 public int getAllRecordNO(); }
package cn.itcast.web.jdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import cn.itcast.web.jdbc.util.JdbcUtil; //基于JDBC的CURD操作 public class Crud { public void create(String name,String gender,float salary){ Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into user(name,gender,salary) values(?,?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,name); pstmt.setString(2,gender); pstmt.setFloat(3,salary); pstmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public void read(String name){ Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; //使用PreparedStement的参数使用占位符替代 String sql = "select * from user where name = ?"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); //能过setXxxx()方法为占位符赋值， //在赋值的过程中动态检测，预防SQL注入问题的发生 pstmt.setString(1,name); rs = pstmt.executeQuery(); while(rs.next()){ name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(name+":"+gender); } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); //多态原则 JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public static void main(String[] args) { Crud crud = new Crud(); //crud.read(" 'or true or' "); crud.create("tim","male",5000); } }
package cn.itcast.web.jdbc.service; import java.util.List; import cn.itcast.web.jdbc.dao.IUserDao; import cn.itcast.web.jdbc.domain.Page; import cn.itcast.web.jdbc.domain.User; import cn.itcast.web.jdbc.factory.DaoFactory; public class UserService { //private UserDao userDao = new UserDao(); //private IUserDao iUserDao = new UserDao(); private IUserDao iUserDao = DaoFactory.getDaoFactory().getUserDao(); //用户登录 public boolean login(String username,String password){ //return userDao.find(username,password); return iUserDao.find(username,password); } //用户注册 public boolean add(User user){ return iUserDao.add(user); } //分页查询所有用户信息 public Page fy(int currPageNO){//1 Page page = new Page(); page.setCurrPageNO(currPageNO);//封装当前显示的页号 int allRecordNO = iUserDao.getAllRecordNO(); page.setAllRecordNO(allRecordNO);//封装总记录数，总页数 int size = page.getPerPageNO(); int start = (page.getCurrPageNO()-1) * page.getPerPageNO(); List<User> userList = iUserDao.find(start,size); page.setUserList(userList);//封装当前显示的内容 return page; } }
package cn.itcast.web.jdbc.web; import java.io.IOException; import java.util.Locale; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.BeanUtils; import org.apache.commons.beanutils.ConvertUtils; import org.apache.commons.beanutils.locale.converters.DateLocaleConverter; import cn.itcast.web.jdbc.dao.Demo2; import cn.itcast.web.jdbc.domain.Page; import cn.itcast.web.jdbc.domain.User; import cn.itcast.web.jdbc.service.UserService; public class UserServlet extends HttpServlet { //将像片保存到数据库 private void upload(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { String photoPath = request.getParameter("photo"); Demo2 demo = new Demo2(); boolean flag = demo.write(photoPath); if(flag){ request.setAttribute("message","成功"); }else{ request.setAttribute("message","失败"); } request.getRequestDispatcher("/message.jsp").forward(request,response); } //分页查询所有用户信息 private void fy(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { String currPageNO = request.getParameter("currPageNO"); if(currPageNO==null){ //默认用户访问第1页 currPageNO = "1"; } UserService userService = new UserService(); Page page = userService.fy(Integer.parseInt(currPageNO)); request.setAttribute("page",page); request.getRequestDispatcher("/WEB-INF/fy.jsp").forward(request,response); } //请求发分器 public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { String method = request.getParameter("method"); if(method!=null){ if("fy".equals(method)){ this.fy(request,response); } }else{ this.fy(request,response); } } //请求发分器 public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { request.setCharacterEncoding("UTF-8"); String method = request.getParameter("method"); if(method!=null){ if("login".equals(method)){ this.login(request,response); }else if("register".equals(method)){ this.register(request,response); }else if("upload".equals(method)){ this.upload(request,response); } } } //用户登录 private void login(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { String username = request.getParameter("username"); String password = request.getParameter("password"); UserService userService = new UserService(); boolean flag = userService.login(username,password); if(flag){ request.setAttribute("message","登录成功"); }else{ request.setAttribute("message","登录失败"); } request.getRequestDispatcher("/message.jsp").forward(request,response); } //用户注册 private void register(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException { java.util.Enumeration<String> enums = request.getParameterNames(); User user = new User(); ConvertUtils.register( new DateLocaleConverter(Locale.getDefault(),"yyyy-MM-dd"), java.util.Date.class); while(enums.hasMoreElements()){ String key = enums.nextElement(); String[] values = request.getParameterValues(key); try { BeanUtils.setProperty(user,key,values); } catch (Exception e) { e.printStackTrace(); } } UserService userService = new UserService(); boolean flag = userService.add(user); if(flag){ request.setAttribute("message","注册成功"); }else{ request.setAttribute("message","注册失败"); } request.getRequestDispatcher("/message.jsp").forward(request,response); } }
package cn.itcast.web.jdbc.domain; import java.util.ArrayList; import java.util.List; //分页类 public class Page { private int allRecordNO;//总记录数 private int perPageNO = 10;//每页显示记录数 private int allPageNO;//总页数(总记录数/每页显示记录数) private int currPageNO = 1;//显示的当前页号 private List<User> userList = new ArrayList<User>();//当前页的所有信息 public Page(){} public int getAllRecordNO() { return allRecordNO; } public void setAllRecordNO(int allRecordNO) { this.allRecordNO = allRecordNO; if(this.allRecordNO % this.perPageNO == 0){ this.allPageNO = this.allRecordNO / this.perPageNO; }else{ this.allPageNO = this.allRecordNO / this.perPageNO + 1; } } public int getPerPageNO() { return perPageNO; } public void setPerPageNO(int perPageNO) { this.perPageNO = perPageNO; } public int getAllPageNO() { return allPageNO; } public void setAllPageNO(int allPageNO) { this.allPageNO = allPageNO; } public int getCurrPageNO() { return currPageNO; } public void setCurrPageNO(int currPageNO) { this.currPageNO = currPageNO; } public List<User> getUserList() { return userList; } public void setUserList(List<User> userList) { this.userList = userList; } }
*4 存取大对象
LOB
a)Character LOB -> CLOB (Text有四个子类型)[字符]
存：
pstmt.setString(1,UUID.randomUUID().toString());
URL url = Demo1.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/62.txt");
File file = new File(url.getPath());
Reader reader = new FileReader(file);
pstmt.setCharacterStream(2,reader,(int)file.length());
取：
Reader reader = rs.getCharacterStream("content");
Writer writer = new FileWriter("d:\\62.txt");
int len = 0;
char[] cuf = new char[1024];
while( (len=reader.read(cuf))>0 ){
writer.write(cuf,0,len);
}
reader.close();
writer.close();
注意：在能完成业务的情况下，尽早关闭连接对象
关闭连接对象，不能够发送SQL到数据库方，并不是不能读写数据
b)Binary LOB -> BLOB (Blob有四个子类型)[字节]
存：
pstmt.setString(1,UUID.randomUUID().toString());
URL url = Demo2.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/d1.jpg");
File file = new File(url.getPath());
InputStream is = new FileInputStream(file);
pstmt.setBinaryStream(2,is,(int)file.length());
取：
is = rs.getBinaryStream("content");
os = new FileOutputStream("d:\\d1.jpg");
int len = 0;
byte[] buf = new byte[1024];
while( (len=is.read(buf))>0 ){
os.write(buf,0,len);
}
在实际开发中，程序需要把大文本或二进制数据保存到数据库。
基本概念：大数据也称之为LOB(Large Objects)，LOB又分为：
•clob和blob
•clob用于存储大文本。
•blob用于存储二进制数据，例如图像、声音、二进制文等。
对MySQL而言只有blob，而没有clob，mysql存储大文本采用的是text，text和blob分别又分为：
•TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
•TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB
对于MySQL中的Text类型，可调用如下方法设置：
PreparedStatement.setCharacterStream(index, reader, length);//注意length长度须设置，并且设置为int型
对MySQL中的Text类型，可调用如下方法获取：
reader = resultSet. getCharacterStream(i);
reader = resultSet.getClob(i).getCharacterStream();
string s = resultSet.getString(i);
对于MySQL中的BLOB类型，可调用如下方法设置：
PreparedStatement. setBinaryStream(i, inputStream, length);
对MySQL中的BLOB类型，可调用如下方法获取：
InputStream in = resultSet.getBinaryStream(i);
InputStream in = resultSet.getBlob(i).getBinaryStream();
package cn.itcast.web.jdbc.dao; import java.io.File; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.io.Reader; import java.io.Writer; import java.net.URL; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.UUID; import cn.itcast.web.jdbc.util.JdbcUtil; /* drop table if exists test_clob; create table if not exists test_clob( id varchar(40) primary key, content text ); */ public class Demo1 { //将CLOB类型的数据从MySQL数据库取出，放到d:\62.txt public static void read() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from test_clob"; Reader reader = null; Writer writer = null; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ reader = rs.getCharacterStream("content"); } } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } try { writer = new FileWriter("d:\\62.txt"); int len = 0; char[] cuf = new char[1024]; while( (len=reader.read(cuf))>0 ){ writer.write(cuf,0,len); } } catch (Exception e) { }finally{ if(reader!=null){ try { reader.close(); } catch (IOException e) { e.printStackTrace(); } } if(writer!=null){ try { writer.close(); } catch (IOException e) { e.printStackTrace(); } } } } //写CLOB类型的数据存入MySQL数据库 public static void write() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into test_clob(id,content) values(?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,UUID.randomUUID().toString()); URL url = Demo1.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/62.txt"); File file = new File(url.getPath()); Reader reader = new FileReader(file); pstmt.setCharacterStream(2,reader,(int)file.length()); int i = pstmt.executeUpdate(); System.out.println(i>0?"成功":"失败"); } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public static void main(String[] args) { //write(); read(); } }
package cn.itcast.web.jdbc.dao; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URL; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.UUID; import cn.itcast.web.jdbc.util.JdbcUtil; /* drop table if exists test_blob; create table test_blob( id varchar(40) primary key, content mediumblob ); */ public class Demo2 { public boolean write(String photoPath) { boolean flag = false; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into test_blob(id,content) values(?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString(1,UUID.randomUUID().toString()); File file = new File(photoPath); InputStream is = new FileInputStream(file); pstmt.setBinaryStream(2,is,(int)file.length()); int i = pstmt.executeUpdate(); if(i>0){ flag = true; } } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } return flag; } //将BLOB类型数据存入数据库 public static void write() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "insert into test_blob(id,content) values(?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); //绑定ID pstmt.setString(1,UUID.randomUUID().toString()); //取得图片的路径 URL url = Demo2.class.getClassLoader().getResource("cn/itcast/web/jdbc/config/d1.jpg"); //封装成File对象 File file = new File(url.getPath()); //取得字节输入流 InputStream is = new FileInputStream(file); //绑定CONTENT //参数1占位符的编号，从1开始 //参数2文件字节输入流 //参数3文件的大小 pstmt.setBinaryStream(2,is,(int)file.length()); int i = pstmt.executeUpdate(); System.out.println(i>0?"成功":"失败"); } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } //将BLOB类型数据从数据库中取出 public static void read() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String sql = "select * from test_blob"; InputStream is = null; OutputStream os = null; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ is = rs.getBinaryStream("content"); } } catch (Exception e) { }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } try { os = new FileOutputStream("d:\\d1.jpg"); int len = 0; byte[] buf = new byte[1024]; while( (len=is.read(buf))>0 ){ os.write(buf,0,len); } } catch (Exception e) { }finally{ if(is!=null){ try { is.close(); } catch (IOException e) { e.printStackTrace(); } } if(os!=null){ try { os.close(); } catch (IOException e) { e.printStackTrace(); } } } } public static void main(String[] args) { //write(); read(); } }
*5 批处理
1)想发送多条SQL，又要降低与数据库系统的交互，这时使用批处理
2)Statement对象：适合对不同结构的SQL做批处理操作
3)PreparedStatement对象：适合对相同结构的SQL做批处理操作
业务场景：当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。
实现批处理有两种方式，第一种方式：
•Statement.addBatch(sql)
执行批处理SQL语句
•executeBatch()方法：执行批处理命令
•clearBatch()方法：清除批处理命令
Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); String sql1 = "insert into user(name,password,email,birthday) values('kkk','123','abc@sina.com','1978-08-08')"; String sql2 = "update user set password='123456' where id=3"; st = conn.createStatement(); st.addBatch(sql1); //把SQL语句加入到批命令中 st.addBatch(sql2); //把SQL语句加入到批命令中 st.executeBatch(); } finally{ JdbcUtil.free(conn, st, rs); }
采用Statement.addBatch(sql)方式实现批处理：
•优点：可以向数据库发送多条不同的ＳＱＬ语句。
•缺点：
•SQL语句没有预编译。
•当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：
Insert into user(name,password) values(‘aa’,’111’);
Insert into user(name,password) values(‘bb’,’222’);
Insert into user(name,password) values(‘cc’,’333’);
Insert into user(name,password) values(‘dd’,’444’);
实现批处理的第二种方式：
•PreparedStatement.addBatch()
conn = JdbcUtil.getConnection(); String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)"; st = conn.prepareStatement(sql); for(int i=0;i<50000;i++){ st.setString(1, "aaa" + i); st.setString(2, "123" + i); st.setString(3, "aaa" + i + "@sina.com"); st.setDate(4,new Date(1980, 10, 10)); st.addBatch(); if(i%1000==0){ st.executeBatch(); st.clearBatch(); } } st.executeBatch(); st.clearBatch();
采用PreparedStatement.addBatch()实现批处理
•优点：发送的是预编译后的SQL语句，执行效率高。
•缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。
package cn.itcast.web.jdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import cn.itcast.web.jdbc.util.JdbcUtil; //Statement和PreparedStatment的批处理 public class Demo3 { public static void statementBatch() { Connection conn = null; Statement stmt = null; ResultSet rs = null; String insertSQL = "insert into user(username,password,birthday,salary) values('jack','000111','2011-10-26',5000)"; String updateSQL = "update user set username='杰克' where username='jack'"; try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); //将需要执行的多条命令加入到批对象中 stmt.addBatch(insertSQL); stmt.addBatch(updateSQL); //一次性发送批对象到数据库端执行,返回每条SQL的结果 int[] is = stmt.executeBatch(); //将批对象清空 stmt.clearBatch(); //显示结果 System.out.println(is[0]+":"+is[1]); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } public static void preparedBatch() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String insertSQL = "insert into user(username,password,birthday,salary) values(?,?,?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(insertSQL); long begin = System.currentTimeMillis(); for(int i=1;i<=1000;i++){ pstmt.setString(1,"jack"+i); pstmt.setString(2,"111111"); pstmt.setDate(3,new java.sql.Date(12345)); pstmt.setFloat(4,5000); //加入到批对象中 pstmt.addBatch(); if(i%100==0){ //执行批对象 pstmt.executeBatch(); //清空批对象 pstmt.clearBatch(); } } //执行批对象 pstmt.executeBatch(); //清空批对象 pstmt.clearBatch(); long end = System.currentTimeMillis(); System.out.println((end-begin)/1000+"秒"); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public static void main(String[] args) { //statementBatch(); preparedBatch(); } }
6 获取数据库主键值
1)当需要获取刚插入主键信息的时候，需要使用获取主键值方法
2)关键代码：
pstmt = conn.prepareStatement(sqlA,Statement.RETURN_GENERATED_KEYS);
rs = pstmt.getGeneratedKeys();
if(rs.next()){
Long temp = (Long) rs.getObject(1);
pid = temp.intValue();
}
Connection conn = JdbcUtil.getConnection(); String sql = "insert into user(name,password,email,birthday) values('abc','123','abc@sina.com','1978-08-08')"; PreparedStatement st = conn. prepareStatement(sql,Statement.RETURN_GENERATED_KEYS ); st.executeUpdate(); ResultSet rs = st.getGeneratedKeys(); //得到插入行的主键 if(rs.next()) System.out.println(rs.getObject(1));
package cn.itcast.web.jdbc.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import cn.itcast.web.jdbc.util.JdbcUtil; //Statement和PreparedStatment的批处理 public class Demo3 { public static void statementBatch() { Connection conn = null; Statement stmt = null; ResultSet rs = null; String insertSQL = "insert into user(username,password,birthday,salary) values('jack','000111','2011-10-26',5000)"; String updateSQL = "update user set username='杰克' where username='jack'"; try { conn = JdbcUtil.getMySqlConnection(); stmt = conn.createStatement(); //将需要执行的多条命令加入到批对象中 stmt.addBatch(insertSQL); stmt.addBatch(updateSQL); //一次性发送批对象到数据库端执行,返回每条SQL的结果 int[] is = stmt.executeBatch(); //将批对象清空 stmt.clearBatch(); //显示结果 System.out.println(is[0]+":"+is[1]); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(stmt); JdbcUtil.close(conn); } } public static void preparedBatch() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String insertSQL = "insert into user(username,password,birthday,salary) values(?,?,?,?)"; try { conn = JdbcUtil.getMySqlConnection(); pstmt = conn.prepareStatement(insertSQL); long begin = System.currentTimeMillis(); for(int i=1;i<=1000;i++){ pstmt.setString(1,"jack"+i); pstmt.setString(2,"111111"); pstmt.setDate(3,new java.sql.Date(12345)); pstmt.setFloat(4,5000); //加入到批对象中 pstmt.addBatch(); if(i%100==0){ //执行批对象 pstmt.executeBatch(); //清空批对象 pstmt.clearBatch(); } } //执行批对象 pstmt.executeBatch(); //清空批对象 pstmt.clearBatch(); long end = System.currentTimeMillis(); System.out.println((end-begin)/1000+"秒"); } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(rs); JdbcUtil.close(pstmt); JdbcUtil.close(conn); } } public static void main(String[] args) { //statementBatch(); preparedBatch(); } }
