JDBC基础操作汇总

于 2019-02-22 18:42:48 发布
阅读量195 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： java学习笔记 文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/haohulala/article/details/87883368

基础的工具类

JDBC进行CRUD操作会有很多重复的代码，我们把重复的代码汇总到一个类里就可以让程序变得简洁很多

package JDBCLearning;

import javax.xml.transform.Result;
import java.sql.*;
import java.util.ArrayList;

/**
 * Created by junk beat on 2019/2/22.
 */
public class jdbcUtil {
    //连接数据库的基础信息
    private static final String connectionURL = "jdbc:mysql://localhost:3306/web01?useUnicode=true&characterEncoding=UTF-8";
    private static final String username = "root";  //登录数据库的账号
    private static final String password = "root";  //登录数据库的密码

    //建立连接
    public static Connection createConnection(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(connectionURL, username, password);
        } catch (ClassNotFoundException e){
            e.printStackTrace();
        } catch (SQLException e){
            e.printStackTrace();
        }

        return null;    //建立连接失败返回空指针
    }

    //关闭连接操作
    public static void close(ResultSet rs, Statement stmt, Connection con){
        closeResult(rs);
        closeStatement(stmt);
        closeConnection(con);
    }

    //关闭结果集
    private static void closeResult(ResultSet rs){
        try {
            if (rs != null) rs.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    //关闭语句执行声明
    private static void closeStatement(Statement stmt){
        try {
            if (stmt != null) stmt.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    //关闭连接
    private static void closeConnection(Connection con){
        try {
            if (con != null) con.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
}

表的结构

我们创建一个简单的表进行测试

use web01;
/*创建表*/
drop table if exists test;
create table test (
  id int primary key auto_increment,
  username varchar(16) not null unique,
  password varchar(16) not null,
  account int default 5000
);

/*插入数据*/
insert into test (username, password) values ("王大柱", "123456");
insert into test (username, password) values ("Ben", "123456");
insert into test (username, password) values ("White", "123456");
insert into test (username, password) values ("Jessis Pinkman", "123456");
insert into test (username, password) values ("Hank", "123456");
insert into test (username, password) values ("王兰花", "123456");
insert into test (username, password) values ("李铁蛋", "123456");

selectAll方法

查询表中所有数据的方法

    //查询所有结果方法
    public static void selectAll(String tableName){
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            stmt = con.createStatement();
            rs = stmt.executeQuery("select * from " + tableName);//执行sql语句

            while(rs.next()){
                System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getInt(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

登录效验

   //登录效验
    public static boolean selectByUsernamePassword(String username, String password){
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            stmt = con.createStatement();

            String sql = "select * from test where username = '"+username+"' and password = '"+password+"'";
            rs = stmt.executeQuery(sql);
            if(rs.next()){
                System.out.println("登录成功");
                return true;
            } else {
                System.out.println("登录失败");
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
        System.out.println("登录失败");
        return false;
    }

防止SQL注入的登录效验

    //防止SQL注入的登录效验
    public static boolean selectByUP(String username, String password){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            String sql = "select * from test where username = ? and password = ?";
            pstmt = con.prepareStatement(sql);

            pstmt.setString(1, username);
            pstmt.setString(2, password);


            rs = pstmt.executeQuery();
            if(rs.next()){
                System.out.println("登录成功");
                return true;
            } else {
                System.out.println("登录失败");
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, pstmt, con);
        }
        System.out.println("登录失败");
        return false;
    }

分页查询

查询的数据是从 (page - 1)*count 到 count

    //分页查询
    //参数分别是要查询的页数和每一页有多少行数据
    public static void PagingQuery(int page, int count){
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            con = jdbcUtil.createConnection();

            String sql = "select * from test limit " + (page - 1) * count + ", " + count;
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getInt(4));
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

插入，删除和修改操作

    //插入，删除和修改操作
    public static void insert(String username, String password){
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            String sql = "insert into test (username, password) values (?, ?)";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, password);
            int result = stmt.executeUpdate();  //返回值代表受到影响的行数
            if(result != -1){
                System.out.println("插入成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

    public static void delete(String username){
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();

            String sql = "delete from test where username = ?";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, username);
            int result = stmt.executeUpdate();
            if(result > 0){
                System.out.println("删除成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeP(rs, stmt, con);
        }
    }

    public static void update(String username, String newPassword){
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            String sql = "update test set password = ? where username = ?";
            stmt = con.prepareStatement(sql);
            stmt.setString(1,newPassword);
            stmt.setString(2, username);
            int result = stmt.executeUpdate();
            if(result > 0){
                System.out.println("修改成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

事务操作

    //事务操作
    public static void transferAccounts(String username1, String username2, int money){
        Connection con = null;
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            //开启事务
            con.setAutoCommit(false);  //是否自动提交

            String sql = "update test set account = account - ? where username = ?";
            stmt1 = con.prepareStatement(sql);
            stmt1.setInt(1,money);
            stmt1.setString(2, username1);
            stmt1.executeUpdate();

            //String s = null;
            //s.charAt(2);

            sql = "update test set account = account + ? where username = ?";
            stmt2 = con.prepareStatement(sql);
            stmt2.setInt(1,money);
            stmt2.setString(2, username2);
            stmt2.executeUpdate();

            con.commit();   //提交事务
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt1, stmt2, con);
        }
    }

连接池

使用完后归还到ArrayList，不用关闭，这样就可以节省创建连接的时间

    private static ArrayList<Connection> conList = new ArrayList<Connection>();
    //静态代码块先于方法执行
    static {
        for(int i=0; i<5; i++){
            Connection con = createConnection();
            conList.add(con);
        }
    }

    public static Connection getConnection(){
        if(conList.isEmpty()==false){
            Connection con = conList.get(0);
            conList.remove(con);    //移除
            return con;
        } else {
            return createConnection(); //创建一个新链接
        }
    }

所有代码汇总

package JDBCLearning;

import java.sql.*;

/**
 * Created by junk beat on 2019/2/22.
 */
public class JDBCop {
    public static void main(String[] args) throws SQLException{
        //selectAll("test");
        //selectByUsernamePassword("王兰花","123456");
        //selectByUP("王兰花","123456");
        //PagingQuery(1, 5);
        //insert("李大嘴", "1235480");
        //delete("李大嘴");
        //update("王兰花","5462145");
        transferAccounts("王兰花","White", 1000);
    }

    //查询所有结果方法
    public static void selectAll(String tableName){
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            stmt = con.createStatement();
            rs = stmt.executeQuery("select * from " + tableName);//执行sql语句

            while(rs.next()){
                System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getInt(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

    //登录效验
    public static boolean selectByUsernamePassword(String username, String password){
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            stmt = con.createStatement();

            String sql = "select * from test where username = '"+username+"' and password = '"+password+"'";
            rs = stmt.executeQuery(sql);
            if(rs.next()){
                System.out.println("登录成功");
                return true;
            } else {
                System.out.println("登录失败");
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
        System.out.println("登录失败");
        return false;
    }

    //防止SQL注入的登录效验
    public static boolean selectByUP(String username, String password){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            String sql = "select * from test where username = ? and password = ?";
            pstmt = con.prepareStatement(sql);

            pstmt.setString(1, username);
            pstmt.setString(2, password);


            rs = pstmt.executeQuery();
            if(rs.next()){
                System.out.println("登录成功");
                return true;
            } else {
                System.out.println("登录失败");
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, pstmt, con);
        }
        System.out.println("登录失败");
        return false;
    }

    //分页查询
    //参数分别是要查询的页数和每一页有多少行数据
    public static void PagingQuery(int page, int count){
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            con = jdbcUtil.createConnection();

            String sql = "select * from test limit " + (page - 1) * count + ", " + count;
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getInt(4));
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

    //插入，删除和修改操作
    public static void insert(String username, String password){
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            String sql = "insert into test (username, password) values (?, ?)";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, password);
            int result = stmt.executeUpdate();  //返回值代表受到影响的行数
            if(result != -1){
                System.out.println("插入成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

    public static void delete(String username){
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();

            String sql = "delete from test where username = ?";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, username);
            int result = stmt.executeUpdate();
            if(result > 0){
                System.out.println("删除成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeP(rs, stmt, con);
        }
    }

    public static void update(String username, String newPassword){
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            String sql = "update test set password = ? where username = ?";
            stmt = con.prepareStatement(sql);
            stmt.setString(1,newPassword);
            stmt.setString(2, username);
            int result = stmt.executeUpdate();
            if(result > 0){
                System.out.println("修改成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt, con);
        }
    }

    //事务操作
    public static void transferAccounts(String username1, String username2, int money){
        Connection con = null;
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;
        ResultSet rs = null;
        try {
            con = jdbcUtil.createConnection();

            //开启事务
            con.setAutoCommit(false);  //是否自动提交

            String sql = "update test set account = account - ? where username = ?";
            stmt1 = con.prepareStatement(sql);
            stmt1.setInt(1,money);
            stmt1.setString(2, username1);
            stmt1.executeUpdate();

            //String s = null;
            //s.charAt(2);

            sql = "update test set account = account + ? where username = ?";
            stmt2 = con.prepareStatement(sql);
            stmt2.setInt(1,money);
            stmt2.setString(2, username2);
            stmt2.executeUpdate();

            con.commit();   //提交事务
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, stmt1, stmt2, con);
        }
    }
}

工具类

package JDBCLearning;

import javax.xml.transform.Result;
import java.sql.*;
import java.util.ArrayList;

/**
 * Created by junk beat on 2019/2/22.
 */
public class jdbcUtil {
    //连接数据库的基础信息
    private static final String connectionURL = "jdbc:mysql://localhost:3306/web01?useUnicode=true&characterEncoding=UTF-8";
    private static final String username = "root";  //登录数据库的账号
    private static final String password = "root";  //登录数据库的密码

    private static ArrayList<Connection> conList = new ArrayList<Connection>();
    //静态代码块先于方法执行
    static {
        for(int i=0; i<5; i++){
            Connection con = createConnection();
            conList.add(con);
        }
    }
    public static Connection getConnection(){
        if(conList.isEmpty()==false){
            Connection con = conList.get(0);
            conList.remove(con);    //移除
            return con;
        } else {
            return createConnection(); //创建一个新链接
        }
    }

    //建立连接
    public static Connection createConnection(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(connectionURL, username, password);
        } catch (ClassNotFoundException e){
            e.printStackTrace();
        } catch (SQLException e){
            e.printStackTrace();
        }

        return null;    //建立连接失败返回空指针
    }

    //关闭连接操作
    public static void close(ResultSet rs, Statement stmt, Connection con){
        closeResult(rs);
        closeStatement(stmt);
        closeConnection(con);
    }

    public static void close(ResultSet rs, PreparedStatement pstmt, Connection con){
        closeResult(rs);
        closePrepareStatement(pstmt);
        closeConnection(con);
    }

    public static void close(ResultSet rs, PreparedStatement pstmt1, PreparedStatement pstmt2, Connection con){
        closeResult(rs);
        closePrepareStatement(pstmt1);
        closePrepareStatement(pstmt2);
        closeConnection(con);
    }
    //关闭结果集
    private static void closeResult(ResultSet rs){
        try {
            if (rs != null) rs.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    //关闭语句执行声明
    private static void closeStatement(Statement stmt){
        try {
            if (stmt != null) stmt.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    //关闭pstmt
    private static void closePrepareStatement(PreparedStatement pstmt){
        try {
            if (pstmt != null) pstmt.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    //关闭连接
    private static void closeConnection(Connection con){
        try {
            if (con != null) con.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
}

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值