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

用到的术语：

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使用方法类似，不做过多演示　

　　

最新文章

  1. caffe学习系列（1）：图像数据转换成db（leveldb/lmdb)文件
  2. HttpGet
  3. python编码-2
  4. JS操作iframe
  5. JSPatch中的OC高级语法
  6. Best Time to Buy and Sell Stock | &amp; || &amp; III
  7. C#完全无客户端访问Oracle
  8. [HDOJ - 5282] Senior&#39;s String 【DP】
  9. UNIX/Linux进程间通信IPC---管道--全总结(实例入门)
  10. css3属性——border-radius用法
  11. LeetCode My Solution: Minimum Depth of Binary Tree
  12. 关于ClassLoader
  13. 知识点干货—多线程同步【6】之synchronized
  14. 对于spring中事务@Transactional注解的理解
  15. Spring核心之IOC
  16. 手动实现staticmethod和classmethod装饰器
  17. Error: Cannot find module PhantomJS
  18. 基于CentOS 搭建 Seafile 专属网盘
  19. oracle 导出表
  20. JSONCPP to Visual Studio

热门文章

  1. js开关菜单
  2. Abaqus脚本接口及简单应用
  3. RTT学习之sensor设备
  4. 「题解」「JOISC 2014 Day1」历史研究
  5. 利用 systemd 实现 Clash 开机自启
  6. spring web 测试用例
  7. 如何让div中的img图片显示在div下面。
  8. 微权获取openid信授
  9. 并行效果&amp;迭代器
  10. 【SSM】Log4j 日志配置