数据库分页(一)
来源:互联网 发布:hadoop windows 可行 编辑:程序博客网 时间:2024/06/05 10:17
1 Statement和PreparedStatement的特点
a)对于创建和删除表或数据库,我们可以使用executeUpdate(),该方法返回0,表示未影向表中任何记录
b)对于创建和删除表或数据库,我们可以使用execute(),该方法返回false,表示创建和删除数据库表
c)除了select操作返回true之除,其它的操作都返回false
d)PreparedStatement有发下的特点:
>>解决SQL注入问题,在绑定参数时,动态检测
>>在发送相同结构的SQL时,较Statement效率有所提升
>>使用?占位符替代真实的绑定值
>>项目中,优先使用PreparedStatement
使用PreparedStatement重构CURD操作
@Test
public void create() {
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs = null;
String sql = "insert into user1(username,password,salary) values(?,?,?)";
try {
conn=JdbcUtil.getMySqlConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, "zhangtingting1");
pstmt.setString(2, "123456");
pstmt.setFloat(3, 1111);
pstmt.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
@Test
public void read() {
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs = null;
String sql = "select * from user1 where username=?";
try {
conn=JdbcUtil.getMySqlConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, "zhangtingting3");
rs = pstmt.executeQuery();
while (rs.next()) {
int id =rs.getInt("id");
String username = rs.getString("username");
String password =rs.getString("password");
float salary =rs.getFloat("salary");
System.out.println("id="+id+"\t"+"username="+username+"\t"+"password="+password+"\t"+"salary="+salary);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
JdbcUtil.close(conn);
}
}
*2 Jsp+Servlet+Javabean+Jdbc+Mysql(用户登录与注册)
总结:
a)如何在Servlet处理类似的业务逻辑
doGet/doPost
private login()
private register()
b)学会层与层之间的耦
/day13/UserServlet?method=register
private void register(HttpServletRequestrequest, 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);
}
//用户注册
public boolean add(Useruser){
return iUserDao.add(user);
}
public boolean add(Useruser){
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;
}
*3 MySQL数据库分页
1)为什么要分页?
2)MySQL数据库中有没有分页的语句?
select * from user LIMIT 第几条记录号-1,需要显示记录的条数;
3)为什么返回List不行,需要返回Page类?
思考:分页的其它方式
关于分页的逻辑思考
需求:
*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到数据库方,并不是不能读写数据
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();
}
}
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);
}
*5 批处理
1)想发送多条SQL,又要降低与数据库系统的交互,这时使用批处理
2)Statement对象:适合对不同结构的SQL做批处理操作
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);
}
}
3)PreparedStatement对象:适合对相同结构的SQL做批处理操作
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);
}
}
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();
}
7 存储过程
- 数据库分页(一)
- sql数据库分页一
- php数据库查询分页显示操作(一)
- 分页(一)
- 大话分页(一)
- JSP 分页(一)
- 分页插件(一)
- 数据库开发必看 -- 用索引优化SQL,开发优秀的分页存储过程(一)
- mybatis分页(数据库oracle)
- 数据库(七)数据库分页处理
- asp.net分页(一)
- JSP分页效果(一)
- jsp分页实现(一)
- php 分页操作(一)
- 数据分页显示(一)
- 海量数据库的查询优化及分页算法方案(一)
- 数据库分页
- 数据库分页
- [个人笔记]ajax使用步骤
- vcs ucli 仿真中突然断电 防止措施
- spring 定时任务之行两次
- 微信小程序 填坑(--持续更新)
- 牛腩记账本core版本源码
- 数据库分页(一)
- python实例(水仙花数)
- Organic polysilazane
- ZOJ 3878 Convert QWERTY to Dvorak
- Windows下更改 git bash 软件的启动默认路径
- okhttp请求+recycleview+网络是否连接成功
- HTML小知识点
- DPDK-POLL MODE DRIVER
- C#开发Android-文件的读写