jdbc学习over

这次的代码和之前学习到一般的代码主要就是将一些很常见的操作（建立连接、清除连接）不管做什么操作都需要用到它们，所以将它们单独放到另一个工具类里面去。

用到的术语：

1.事务：https://www.cnblogs.com/cstdio1/p/11626657.html

2.缓冲池（数据源）：https://www.cnblogs.com/chy18883701161/p/11374731.html

主逻辑代码：

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

import JDBCUtils.JDBCUtils;
import JDBCUtils.JDCPDataSource;

public class MysqlDemo1 {

    public static void main(String[] args) {
        selectAll();
        //System.out.println(selectByUsernamePassword2("zs","123"));
        //sql注入
        //System.out.println(selectByUsernamePassword("zs","12347'or'1'='1"));
        //PageSearch(1,2);
        //insert("sdf","249.1");
        //delete("sdf");
        //update("zs","123","000");
        transAccount("zs","ls",1000);
    }
    public static void selectAll(){
    // TODO Auto-generated method stub
    Connection con=null;
    Statement stmt=null;
    ResultSet rs=null;
    try {
        con = JDCPDataSource.getConnection();
        stmt = con.createStatement();
        String SqlRequest = "select * from student";
        rs= stmt.executeQuery(SqlRequest);
        while(rs.next()){
        System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));    
        //System.out.println(rs.getString("id")+" "+rs.getString("stu_name")+" "+rs.getString("stu_sex")+" "+rs.getString("stu_score"));
        }
        
        
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        JDCPDataSource.closeResource(rs, stmt, con);
        //JDBCUtils.closeResource(rs, stmt, con);
    }        
        
        
        
        
}
    
    public static boolean selectByUsernamePassword(String username,String password){//验证用户名和密码（字符串拼接的方式存在sql注入的问题）

        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
        
        try {
            con = JDBCUtils.getConnection();
            stmt = con.createStatement();
            String requestSql="select * from user where u_name='"+username+"'and u_password='"+password+"'";
           System.out.print(requestSql);
            rs = stmt.executeQuery(requestSql);
             if(rs.next()){
                 return true;
             }else{
                 return false;
             }
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            
            JDBCUtils.closeResource(rs, stmt, con);
        }
        
        return false;
        
    }

    public static boolean selectByUsernamePassword2(String username,String password){//验证用户名和密码(版本2可以防止sql注入)

        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        
        try {
            con = JDBCUtils.getConnection();
            String RequestSql="select *from user where u_name=? and u_password=? ";
             pstmt = con.prepareStatement(RequestSql);
            
            pstmt.setString(1, username);
            pstmt.setString(2,password);
            rs = pstmt.executeQuery();
             if(rs.next()){
                 return true;
             }else{
                 return false;
             }
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{

            JDBCUtils.closeResource(rs, pstmt, con);
        }
        
        return false;
        
        
        
    }
    /*
     *PageNum：查询第几页
     *LineNum：总共显示多少行 
     */
    public static void PageSearch(int PageNum,int LineNum){
               //分页查询
        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        
        try {
            
            con = JDBCUtils.getConnection();
               String RequestSql="select *from user limit ?,?";
            pstmt = con.prepareStatement(RequestSql);
            
            pstmt.setInt(1,(PageNum-1)*LineNum);
            pstmt.setInt(2,LineNum);
            rs = pstmt.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString(1)+" "+rs.getString(2));
            }
            
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtils.closeResource(rs, pstmt, con);
            
        }
            
            
    }

    public static void insert(String UserName,String Password){
               //新注册的信息进行插入操作
        
        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        int mark=0;
        try {
            con = JDBCUtils.getConnection();
            String RequestSql="insert into user(u_name,u_password) values(?,?)";
            pstmt = con.prepareStatement(RequestSql);
            
            pstmt.setString(1, UserName);
            pstmt.setString(2,Password);
            mark = pstmt.executeUpdate();
            if(mark>0){
                System.out.println("插入成功");
            }else{
                System.out.println("插入失败");
            }
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{

            JDBCUtils.closeResource(rs, pstmt, con);
        }
        
        
        
        
    }

    public static void delete(String UserName){

        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        int mark=0;
        try {
            con = JDBCUtils.getConnection();
            String RequestSql="delete from user where u_name = ?";
            pstmt = con.prepareStatement(RequestSql);
            
            pstmt.setString(1, UserName);
            mark = pstmt.executeUpdate();
            if(mark>0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }

            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{

            JDBCUtils.closeResource(rs, pstmt, con);
        }
    }

    public static void update(String UserName,String OldPassword,String NewPassword){
        //修改用户密码

        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        int mark=0;
        try {
            con = JDBCUtils.getConnection();
            String RequestSql="update user set u_password = ? where u_name = ? and u_password = ? ";
            pstmt = con.prepareStatement(RequestSql);
            
            pstmt.setString(1, NewPassword);
            pstmt.setString(2, UserName);
            pstmt.setString(3, OldPassword);
            mark = pstmt.executeUpdate();
            if(mark>0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }

            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{

            JDBCUtils.closeResource(rs, pstmt, con);
        }
    }

    public static void transAccount(String UserName1,String UserName2,int money){
    //转账操作(利用事务)
        Connection con=null;
        PreparedStatement pstmt1=null;
        PreparedStatement pstmt2=null;
        ResultSet rs=null;
        try {
            con = JDBCUtils.getConnection();
            con.setAutoCommit(false);//开启事务
                String sql="update user set u_balance = u_balance - ? where u_name = ? ";
            pstmt1 = con.prepareStatement(sql);
            
            pstmt1.setInt(1, money);
            pstmt1.setString(2, UserName1);
            pstmt1.executeUpdate();
        
//            String s=null;模拟异常情况(断电、数据库崩溃)
//            s.charAt(2);

            sql = "update user set u_balance = u_balance + ? where u_name = ? ";
            pstmt2 = con.prepareStatement(sql);
            pstmt2.setInt(1, money);
            pstmt2.setString(2, UserName2);
            pstmt2.executeUpdate();            
            
            con.commit();//事务完成
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{

            JDBCUtils.closeResource(rs, pstmt1, con);
            JDBCUtils.closeResource(null, pstmt2, null);
        }
        
    }
}

分页查询的公式：

JDBC工具类：

package JDBCUtils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class JDBCUtils {
    private static final String url="jdbc:mysql://localhost:3306/dy?useUnicode=true&characterEncoding=utf-8&useSSL=false"; 
    private static final String user="root";
    private static final String password="root";
    private static ArrayList <Connection> Clist = new ArrayList<Connection>();//保存连接
    
    static{
        for(int i=0;i<10;i++){
        Connection con = createConnection();//创建连接
        Clist.add(con);//添加到容器中
        }
    }
    
    public static Connection getConnection(){
        if(Clist.isEmpty()==false){
            Connection con = Clist.get(0);//得到容器中的连接
            Clist.remove(con);
            return con;
        }else{
            return createConnection();//创建连接
        }
    }
    public static Connection createConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(url,user,password);//建立和mysql数据库的连接
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }//选择注册驱动
        
        return null;
    }
    
    public static void closeResource(ResultSet rs,Statement stmt,Connection con){
        try {
            if(rs!=null)
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    
        try {
            if(stmt!=null)
            stmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

//        try {
//            if(con!=null)
//            con.close();
//        } catch (SQLException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
        Clist.add(con);
    }
    
    public static void closeResource(ResultSet rs,PreparedStatement pstmt,Connection con){
        try {
            if(rs!=null)
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    
        try {
            if(pstmt!=null)
            pstmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

//        try {
//            if(con!=null)
//            con.close();
//        } catch (SQLException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
        Clist.add(con);//这里的连接不需要关闭，因为在不断建立连接和取消连接的过程会消耗很多时间，所以引入了缓冲池（数据源）的概念
    }
}

我们上面的工具类的数据源是我们自己利用容器去模拟它去实现的，实际上已经有一些现成的我们可以直接使用的，例如：dbcp、c3p0。

DBCP数据源（工具类版本2）：

package JDBCUtils;

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

import org.apache.commons.dbcp2.BasicDataSource;

public class JDCPDataSource {
    private static final String url="jdbc:mysql://localhost:3306/dy?useUnicode=true&characterEncoding=utf-8&useSSL=false"; 
    private static final String user="root";
    private static final String password="root";
    private static BasicDataSource ds;
    
    static{
        ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl(url);
        ds.setUsername(user);
        ds.setPassword(password);
        
        ds.setInitialSize(5);//设置初始连接
        ds.setMaxTotal(20);//设置最大连接数
        ds.setMinIdle(3);//设置最小空闲连接（一旦小于最小空闲连接它会自动创建连接以达到最小空闲连接）
    }
    public static Connection getConnection(){
        
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return null;
    }
    public static void closeResource(ResultSet rs,Statement stmt,Connection con){
        try {
            if(rs!=null)
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    
        try {
            if(stmt!=null)
            stmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            if(con!=null)
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    public static void closeResource(ResultSet rs,PreparedStatement pstmt,Connection con){
        try {
            if(rs!=null)
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    
        try {
            if(pstmt!=null)
            pstmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            if(con!=null)
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    
    }
}　　

注意：使用dbcp时需要下载：第一个是核心包、后面两个都是核心包所依赖的东西，不下程序会显示找不到Class

c3p0使用方法类似，不做过多演示