数据库连接-----MySQL -> JDBC

安装好mysql数据库 安装后检查是否有connection J 的程序
第一步导入架包，加载驱动程序 发起请求

第一步的程序
url 如果写成?jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf8 ； 有可能会报错
——>java.sql.SQLException: The server time zone value ........?这时候参考博文：https://blog.csdn.net/Hello_World_QWP/article/details/80421533?
也就是用下面我的url? 里 +？......的部分

Class.forName("com.mysql.jdbc.Driver");//一般都是已经加载好的 加入后可能会警告
        System.out.println("驱动加载成功");
        String url = "jdbc:mysql://localhost:3306/mysql2_1709?serverTimezone=UTC";
        String user = "root";
        String password = "88888888";
        Connection con =  DriverManager.getConnection(url, user, password);

第二步进行语句操作 查询完关闭

//第二步 进行语句操作
        Scanner sc = new Scanner(System.in); //可以输入sql查询语句
        Statement stm = con.createStatement();
        System.out.println("输入sql语句");
        String sql = sc.nextLine();
        ResultSet res = stm.executeQuery(sql);
        while(res.next()) {//输出结果集
            System.out.println(res.getInt(1)+","+res.getString(2)+","+res.getString(3));
        }
        if(res!=null)
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(pstm !=null)
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(con !=null)
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

完整代码

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import java.sql.Connection;
 
public class JDBC {
 
    public static void main(String[] args) {
        // 第一步 导入架包 加载驱动程序
        // Class.forName("com.mysql.jdbc.Driver");//一般都是已经加载好的
        
        System.out.println("驱动加载成功");
        String url = "jdbc:mysql://localhost:3306/mysql2_1709?serverTimezone=UTC";
        String user = "root";
        String password = "88888888";
        Connection con = null;
        Statement stm = null;
        ResultSet res = null;
        
        try {
            con = DriverManager.getConnection(url, user, password);
            Scanner sc = new Scanner(System.in);
            stm = con.createStatement();
            System.out.println("输入sql语句");
            String sql = sc.nextLine();
            res = stm.executeQuery(sql);
 
            while (res.next()) {
                System.out.println(res.getInt(1) + "," + res.getString(2) + "," + res.getString(3));
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {// 每个关闭语句都要单独 放 不然一个有异常 会导致其他资源的关闭
            if(res!=null)
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(pstm !=null)
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(con !=null)
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        // 第二步 进行语句操作
 
    }
}

查询语句可以改为

System.out.println(res.getInt("id") + "," + res.getString("name") + "," + res.getString("sex"));

整理代码 把操作写到方法里面
查询数据库所有信息

    public static void selectAll() {
        Connection con = null;
        Statement stm = null;
        ResultSet res = null;
 
        try {
            con = DriverManager.getConnection(url, user, password);
            Scanner sc = new Scanner(System.in);
            stm = con.createStatement();
            res = stm.executeQuery("select * from t1_user");
 
            while (res.next()) {
                System.out.println(res.getInt("id") + "," + res.getString("name") + "," + res.getString("sex"));
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        // 每个关闭语句都要单独 放 不然一个有异常 会导致其他资源的关闭
        finally {
            if(res!=null)
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(pstm !=null)
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(con !=null)
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

通过 指定的id 和name 来查询
注意 namevalue 要用单引号括起来

public static boolean selectByIdName(String id, String name) {// 查询语句是否存在指定的id 以及 name
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;
        try {
            con = DriverManager.getConnection(url, user, password);
            sta = con.createStatement();
            res = sta.executeQuery("select * from t1_user where id= " + id + " and  name = '" + name + "'");
            
            // 有数据就返回true 并输出相对应的信息
            if (res.next()) {
                while (res.next()) {
                    System.out.println(res.getInt("id") + "," + res.getString("name"));
                }
                return true;
            }
            else
                return false;
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(res!=null)
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(pstm !=null)
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(con !=null)
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return false;
    }
}

数据库要小心sql注入 被黑客攻击的漏洞 id 和 name 是数据库里面没有的 结果还输出数据

防止sql注入 采用 preparedstatement 语句
将上面sql定义的数据改写为

        Connection con = null;
        PreparedStatement psta = null;
        ResultSet res = null;
        try {
            con = DriverManager.getConnection(url, user, password);
            String sql = "select * from t1_user where id = ? and name = ?";
            psta = con.prepareStatement(sql);//创建后再设置参数
            psta.setInt(1, id);
            psta.setString(2, name);
            
            res = psta.executeQuery();//记得要赋值给 res

分页查询

limit 查询的方法 查询第几页

    public static void selectUserByPage(int pageNumber , int pageCount) {
        Connection con = null;
        PreparedStatement pstm =null;
        ResultSet res = null;
        try {
            con = DriverManager.getConnection(url,user,password);
            String sql = "select * from t1_user limit ? , ?";
            pstm = con.prepareStatement(sql);
            pstm.setInt(1, (pageNumber-1)*pageCount);
            pstm.setInt(2, pageCount);
            res = pstm.executeQuery();
            while(res.next()) {
                System.out.println(res.getInt(1)+","+res.getString(2)+",");
            }
        }
        catch (Exception e) {
            System.out.println(e);
        }finally {
            if(res!=null)
                try {
                    res.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(pstm !=null)
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(con !=null)
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

对代码进行整合（重复代码放到一个工具类中） 以及实现一些小功能

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.Scanner;
 
public class JDBCUtills {
    
    private static final String url =  "jdbc:mysql://localhost:3306/mysql2_1709?serverTimezone=UTC";
    public static Connection getConn() {
        Scanner sc = new Scanner(System.in);
        System.out.println("输入登陆账号");
        String user = sc.nextLine();
        System.out.println("输入密码");
        String password = sc.nextLine();
        Connection con = null;
        PreparedStatement pstm =null;
        ResultSet res = null;
        
        try {
            con = DriverManager.getConnection(url,user,password);
            System.out.println("登陆成功");
            return con;
        }
        catch (Exception e) {
            System.out.println("密码账号出错");
        }
        return con;
    }
    public static void close(ResultSet res, PreparedStatement pstm, Connection con) {
        if(res!=null)
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(pstm !=null)
            try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
    public static void close(ResultSet res, Statement stm, Connection con) {
        if(res!=null)
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(stm !=null)
            try {
                stm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if(con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import java.sql.Connection;
 
public class JDBC {
    static String user;
    static String password;
    static String url;
    static Connection con;
 
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        con = JDBCUtills.getConn();
        if (con != null)
            selectAll();
    }
 
    public static void selectAll() {
        Statement stm = null;
        ResultSet res = null;
        try {
            stm = con.createStatement();
            res = stm.executeQuery("select * from t1_user");
            while (res.next()) 
                System.out.println(res.getInt("id") + "," + res.getString("name") + "," + res.getString("sex"));
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {// 每个关闭语句都要单独 放 不然一个有异常 会导致其他资源的关闭
            JDBCUtills.close(res, stm, con);
        }
    }
 
    public static boolean selectByIdName(int id, String name) {// 查询语句是否存在指定的id 以及 name
        PreparedStatement psta = null;
        ResultSet res = null;
        try {
            String sql = "select * from t1_user where id = ? and name = ?";
            psta = con.prepareStatement(sql);// 创建后再设置参数
            psta.setInt(1, id);
            psta.setString(2, name);
            res = psta.executeQuery();
 
            // 有数据就返回true 并输出相对应的信息
            if (res.next()) {
                while (res.next()) 
                    System.out.println(res.getInt("id") + "," + res.getString("name"));
                return true;
            }
            else
                return false;
        } 
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            JDBCUtills.close(res, psta, con);
        }
        return false;
    }
 
    public static void selectUserByPage(int pageNumber, int pageCount) {
        PreparedStatement pstm = null;
        ResultSet res = null;
        try {
            String sql = "select * from t1_user limit ? , ?";
            pstm = con.prepareStatement(sql);
            pstm.setInt(1, (pageNumber - 1) * pageCount);
            pstm.setInt(2, pageCount);
            res = pstm.executeQuery();
            while (res.next()) {
                System.out.println(res.getInt(1) + "," + res.getString(2) + ",");
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            JDBCUtills.close(res, pstm, con);
        }
    }
}

插入语句的实现代码

    public static void insert(int id , String name , String sex) {
        PreparedStatement pstm = null;
        ResultSet res = null;
        try {
        String sql = "insert into t1_user(id,name,sex) values(?,?,?)";
        pstm = con.prepareStatement(sql);
        pstm.setInt(1, id);
        pstm.setString(2, name);
        pstm.setString(3, sex);
        int  n = pstm.executeUpdate();//受影响的行数
        System.out.println(n);
        }catch(Exception e) {
            System.out.println("插入失败");
        }
        finally {
            JDBCUtills.close(res, pstm, con);;
        }
    }

更新代码的实现

public static void update(int id , String name) {
        PreparedStatement pstm = null;
        ResultSet res = null;
        try {
            String sql = "update t1_user set name = ? where id = ?";
            pstm = con.prepareStatement(sql);
            pstm.setString(1, name);
            pstm.setInt(2, id);
            int n = pstm.executeUpdate();
            System.out.println(n);
        }catch(Exception e ) {
            System.out.println("更新失败");
        }finally {
            JDBCUtills.close(res, pstm, con);
        }
    }

转账操作加事务

    public static void transferMoney(int id ,int id1, int money) {//两个以上的操作数据库 要加上事务
        PreparedStatement pstm = null;
        PreparedStatement pstm1 = null;
        ResultSet res = null;
        try {
            con.setAutoCommit(false);
            String sql = "update t1_user set money = money - ? where id = ?";
            pstm = con.prepareStatement(sql);
            pstm.setInt(1, money);
            pstm.setInt(2, id);
            pstm.executeUpdate();
            ///////////////////////这里可以加一段 会报错的语句 1/0
            sql = "update t1_user set money = money + ? where id = ?";
            pstm1 = con.prepareStatement(sql);
            pstm1.setInt(1, money);
            pstm1.setInt(2, id1);
            pstm1.executeUpdate();
            System.out.println("成功");
            
            con.commit();//提交  如果中间报错则 回滚数据
        }catch(Exception e ) {
            System.out.println("转账失败");
        }finally {
            JDBCUtills.close(res, pstm1, con);
            JDBCUtills.close(res, pstm, con);
        }
    }

将工具类改写成连接池

public class JDBCUtills {
    private static ArrayList<Connection> al = new ArrayList<Connection>();
    static {
        for (int i = 0; i < 5; i++) {
            Connection con = createConn();
            al.add(con);
        }
    }
    private static final String url = "jdbc:mysql://localhost:3306/mysql2_1709?serverTimezone=UTC";
 
    public static Connection getConn() {
        if(!al.isEmpty()) {
        Connection con = al.get(0);
        al.remove(con);
        return con;//一般在用完之后就放回去到连接池里面 就在后面那个关闭Connection 那里加一个归还操作
        }
        else return createConn();
    }
 
    private static Connection createConn() {
        // TODO Auto-generated method stub
        Scanner sc = new Scanner(System.in);
        System.out.println("输入登陆账号");
        String user = sc.nextLine();
        System.out.println("输入密码");
        String password = sc.nextLine();
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet res = null;
 
        try {
            con = DriverManager.getConnection(url, user, password);
            System.out.println("登陆成功");
            return con;
        } catch (Exception e) {
            System.out.println("密码账号出错");
        }
        return con;
    }

下面把代码交给dbcp进行管理?
运行的时候出现的错误：
https://blog.csdn.net/anaini1314/article/details/71157791（mysql版本比较新）
https://blog.csdn.net/mqs1990/article/details/76167679（缺少某些第三方库）

import java.sql.Connection;
import java.sql.SQLException;
 
import org.apache.commons.dbcp2.BasicDataSource;
 
public class dataSource {
    private static final String url = "jdbc:mysql://localhost:3306/mysql2_1709?serverTimezone=UTC";
    private static final String user = "root";
    private static final String password = "88888888";
    private static BasicDataSource ds ;
    static {
        ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUrl(url);
        ds.setUsername(user);
        ds.setPassword(password);
        ds.setInitialSize(5);//初始化连接个数
        ds.setMaxTotal(20);//达到二十个不会再创建 只有调用close 才会归还对象//可以用以一个for试试
        ds.setMinIdle(4);//最小连接
    }
    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }
}

c3p0 （虽然差不多 但这个用起来不是很方便）

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
public class c3p0dataSource {
    private static final String url = "jdbc:mysql://localhost:3306/mysql2_1709?serverTimezone=UTC";
    private static final String user = "root";
    private static final String password = "88888888";
    private static ComboPooledDataSource cpds ;
    static {
        cpds = new ComboPooledDataSource ();
        try {
            cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
            cpds.setUser(url);
            cpds.setUser(user);
            cpds.setPassword(password);
            cpds.setInitialPoolSize(5);//初始化连接个数
            cpds.setMaxPoolSize(20);//达到二十个不会再创建 只有调用close 才会归还对象
            cpds.setMinPoolSize(4);//最小连接
        } catch (PropertyVetoException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }public static Connection getConnection() {
        try {
            return cpds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

教学视频：https://www.bilibili.com/video/av37325712/?p=8

数据库连接-----MySQL -> JDBC

原文：https://www.cnblogs.com/cznczai/p/11151371.html