学习笔记：jdbc调用存储过程等

statement    //超父接口

preparedstatement  //父接口  是java sql 包下的一个接口，通过调用connection preparedStatement(sql)方法可以获得PreparedStatement对象，数据库系统会对其进行预编译，这样，它就比Statement对象生成的查询速度快。PreparedStatement可以写动态参数化查询 用它你可以写带参数的sql查询语句。同时，PreparedStatement拥有更佳的性能优势。Sql语句会在预编译的数据库系统中，执行计划同样会被缓存起来，它允许数据库做参数化查询，使用预处理语句比普通语句的查询速度更快，因为它做的工作更少。另外还可以防止sql注入：类似于username="'1'or'1'='1'";

callablestatement 调用存储过程用的

JDBC中的事物

如：

修改员工薪资同时提交修改信息表，要求必须修改完成，并且在表中插入修改信息。

步骤：

创建表和序列

1.在orcle中创建表和序列：

create table logs_emp(
id number(4) primary key,
who varchar2(30),
when date default sysdate,
what varchar2(50)
);
create sequence my_log_emp start with 1000 increment by 1;

2.利用java JDBC

package day110;


import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;


import util.ConnectionUtils;
public class Test01 {
public static void main(String[] args) {
update(5000,7369);
}
public static  void update(int empno,int salary){
String sql="update emp set sal="+salary+"where empno="+empno;
String sql02="insert into logs_emp values(my_log_emp.nextval,user,sysdate,'update salary')";
Connection conn=null;
Statement stmt=null;
try {
conn=ConnectionUtils.openConnection();//利用创建的工具包建立连接
stmt=conn.createStatement();//创建statement实例

conn.setAutoCommit(false);
int n=stmt.executeUpdate(sql);
int n02=stmt.executeUpdate(sql02);
if(n==1&n02==1){
conn.commit();;
}else{
conn.rollback();
}

conn.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


}

二.JDBC执行批处理，往test表中插入1-105

package day110;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


import util.ConnectionUtils;


public class BatchDemo {
public static void main(String[] args) {
executeSql();
}
public static void executeSql(){
String sql = "insert into test(ID) values(?)";
Connection conn = null;
PreparedStatement stmt= null;
try {
conn=ConnectionUtils.openConnection();

conn.setAutoCommit(false);
stmt=conn.prepareStatement(sql);
for(int i=1;i<=105;i++){
stmt.setInt(1, i);
stmt.addBatch();
if(i%10==0){
stmt.executeBatch();
stmt.clearBatch();
}
}
stmt.executeBatch();
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ConnectionUtils.closeConnection(conn);
}
}

三.分页

一次性把数据全部取出来放在缓存里，

只访问数据库一次，第一次取比较慢，以后每页都从缓存数据中取，仅适合数据量不大的情况。

基于缓存：

package day110;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import util.ConnectionUtils;


public class BufferPageDemo {
public static void main(String[] args) {
getPage(10,3);
}
private static void getPage(int pageSize, int page){
int begin =(page-1)*pageSize+1;
String sql="select * from test";
Connection conn =null;
Statement stmt = null;
ResultSet rs= null;
try {
conn=ConnectionUtils.openConnection();
stmt=conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY
);
rs=stmt.executeQuery(sql);
rs.absolute(begin);
for(int i=0;i<pageSize;i++){
System.out.println(rs.getInt("ID"));

if(!rs.next()){
break;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();

}finally{
ConnectionUtils.closeConnection(conn);
ConnectionUtils.closeResultSet(rs);
}
}
}

如果访问的页数超过总页数，或者是负数时

package day110;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import util.ConnectionUtils;


public class BufferPageDemo2 {
public static void main(String[] args) {
getPage(10,"8888");
}
private static void getPage(int pageSize,String j){
int page=1;
try{
page=Integer.parseInt(j);
    }catch(NumberFormatException e){
    page=1;
    }
int totalPage= getTotalPage(pageSize);
if(page>totalPage){
page=totalPage;
}
if(page<1){
page=1;
}
int begin=(page-1)*pageSize+1;
String sql="select * from test";
Connection conn= null;
Statement stmt= null;
ResultSet rs= null;
try {
conn=ConnectionUtils.openConnection();
stmt=conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY
);
rs=stmt.executeQuery(sql);
rs.absolute(begin);
for(int i=0;i<pageSize;i++){
System.out.println(rs.getInt("ID"));

if(!rs.next()){
break;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();


}finally{
ConnectionUtils.closeConnection(conn);
ConnectionUtils.closeResultSet(rs);
}
}
private static int getTotalPage(int pageSize){
int totalTableCount = 0;
totalTableCount = getTotalTableCount();
int totalPage = 0;
int mode= totalTableCount%pageSize;
if(mode==0){
totalPage = totalTableCount/pageSize;
}else{
totalPage = totalTableCount/pageSize+1;
}
return totalPage;
}
private static int getTotalTableCount(){
int count =0;
String sql ="select count(*) num from test";
Connection conn=null;
Statement stmt=null;
ResultSet rs= null;
try {
conn = ConnectionUtils.openConnection();
stmt=conn.createStatement();
rs= stmt.executeQuery(sql);
rs.next();

count=rs.getInt("num");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnectionUtils.closeConnection(conn);
ConnectionUtils.closeStatemenet(stmt);
}
return count;
}


}

基于查询的方式：

package day110;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import util.ConnectionUtils;


public class SelectPageDemo {
public static void main(String[] args) {
getPage(10,3);
}
private static void getPage(int pageSize,int page){
int begin = (page-1)*pageSize+1;
int end=begin+pageSize-1;
String sql="select ID from ( select ID,rownum rn from test)where rn between? and ?";
Connection conn=null;
PreparedStatement stmt =null;
ResultSet rs=null;
try {
   conn=ConnectionUtils.openConnection();
stmt=conn.prepareStatement(sql);
stmt.setInt(1, begin);
stmt.setInt(2, end);
rs=stmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("ID"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
ConnectionUtils.closeConnection(conn);
ConnectionUtils.closeResultSet(rs);
ConnectionUtils.closeStatemenet(stmt);
}

}
}

基于查询的分页策略：直接从数据表中取出部分数据（x到y条的数据）

基于缓存的分页策略：获得数据表中的全部数据，得到可滚动结果集，通过移动指针从结果集中取出部分数据，适用于数据量小的表

存储过程的调用：

package day110;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;


import util.ConnectionUtils;


public class ProcDemo {
public static void main(String[] args) {
int flag= checkUser(1001,"1234");
if(flag==1){
System.out.println("登陆成功");
}else if(flag==0){
System.out.println("密码错误");
}else if(flag==-1){
System.out.println("账号不存在");
}else{
System.out.println("其他错误");
}
}
private static int checkUser(int id , String pwd){
int flag=-2;
String sql="{call checkuser_newland(?,?,?)}";
Connection conn = null;
CallableStatement stmt = null;
try {
conn=ConnectionUtils.openConnection();

stmt=conn.prepareCall(sql);
stmt.setInt(1, id);
stmt.setString(2, pwd);
stmt.registerOutParameter(3, Types.INTEGER);
stmt.execute();
flag=stmt.getInt(3);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
ConnectionUtils.closeConnection(conn);
ConnectionUtils.closeStatemenet(stmt);
}
return flag;
}
}