[大数据]jdbc&连接池

jdbc&连接池

在sqlyog里

– 创建数据库 jdbc01
CREATE DATABASE jdbc01;
– 使用这个数据库
USE jdbc01;
– 创建分类表category
– cid 主键自增长
– cname 字符类型变长 100长度
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100)
);
– 初始化数据
INSERT INTO category VALUES(1,‘家电’);
INSERT INTO category VALUES(2,‘服饰’);
INSERT INTO category VALUES(3,‘化妆品’);

导入jar包

开发步骤

1. 注册驱动
2. 获取连接
3. 获取执行sql语句的对象
4. 执行sql语句，并返回结果
5. 处理结果
6. 释放资源

package com.dbsy.jdbc;


import com.mysql.jdbc.Driver;

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

public class Demo01JDBC {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //注册驱动
        //DriverManager.registerDriver(new Driver());
        Class.forName("com.mysql.jdbc.Driver");
        //获取数据库连接对象Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc01", "root", "123456");
        System.out.println(conn);
        //获取执行sql语句的执行者对象Statement
        Statement stat = conn.createStatement();
        System.out.println(stat);
        //执行sql语句,获取结果
        int row = stat.executeUpdate("insert into category(cname) values('零食');");
        //处理结果
        System.out.println(row+"行数据添加成功");
        //释放资源
        stat.close();
        conn.close();
    }
}

获得数据库连接失败

package com.dbsy.utils;

import java.sql.Connection;

public class Demo02TestJDBCUtils {
    public static void main(String[] args){
        Connection conn = JDBCUtils.getConnection();
        System.out.println(conn);
        JDBCUtils2.close(null,null,conn);
    }
}

package com.dbsy.utils;

import java.sql.*;

public class JDBCUtils {
    //创建一些静态成员变量,储存数据库的连接信息(注册驱动的地址,url,用户名,密码)
    private static String driver="com.mysql.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/jdbc01";
    private static String user="root";
    private static String password="12345";
    //静态代码块:优先执行,里面的代码只会执行一次
    static{
        try {
            //注册驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //创建一个静态方法,获取数据库的连接对象Connection
    public static Connection getConnection(){
        Connection conn=null;
        try {
            //注册驱动
            Class.forName(driver);
            //获取数据库连接对象
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
           // e.printStackTrace();
            /*
            编程技巧:获取数据库连接对象Connection如果失败了,值就是null
            我们拿到null什么都干不了,所以我们就没有必要返回null
            我们就可以把编译异常变成运行时异常,让程序停下来
            当Connection不是null,再让程序执行
             */
            throw new RuntimeException("获得数据库连接失败"+e);
        }
        return conn;
    }
    //创建一个静态方法,用于释放资源
    public static void close(ResultSet rs, Statement stat, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

Exception in thread "main" java.lang.RuntimeException: 获得数据库连接失败java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.dbsy.utils.JDBCUtils.getConnection(JDBCUtils.java:36)
	at com.dbsy.utils.Demo02TestJDBCUtils.main(Demo02TestJDBCUtils.java7)

用java代码实现数据库的增删改查

package com.dbsy.jdbc;

import com.dbsy.utils.JDBCUtils;

import java.sql.*;

public class Demo03JDBC {
    public static void main(String[] args) throws SQLException {
//        insert();
//        update();
//        delete();
//        select_row();
        select();
    }

    private static void select() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        Statement stat = conn.createStatement();
        String sql="select * from category;";
        ResultSet rs = stat.executeQuery(sql);
        while (rs.next()){
            int cid = rs.getInt("cid");
            String cname=rs.getString("cname");
            System.out.println(cid+"\t"+cname);
        }
        JDBCUtils.close(rs,stat,conn);
    }

    private static void select_row() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        Statement stat = conn.createStatement();
        String sql="select * from category where cid=2;";
        ResultSet rs = stat.executeQuery(sql);
        //处理结果
        if (rs.next()){
            int cid = rs.getInt("cid");
            String cname=rs.getString("cname");
            System.out.println(cid+"\t"+cname);
        }else{
            System.out.println("没有查询到结果");
        }
        JDBCUtils.close(rs,stat,conn);
    }

    private static void delete(){
        Connection conn = JDBCUtils.getConnection();
        Statement stat =null;
        try {
            stat = conn.createStatement();
            String sql = "delete from category where cid in(4,5);";
            int row = stat.executeUpdate(sql);
            System.out.println(1 / 0);
            System.out.println(row + "行数据删除成功");
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            //一定会执行的代码,一般用于释放资源
            JDBCUtils.close(stat,conn);
        }
    }

    private static void update() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        Statement stat = conn.createStatement();
        String sql="update category set cname='可乐' where cid=6;";
        int row = stat.executeUpdate(sql);
        System.out.println(row+"行数据修改成功");
        JDBCUtils.close(stat,conn);
    }

    private static void insert() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        Statement stat = conn.createStatement();
        String sql="insert into category(cname) values('饮料');";
        int row = stat.executeUpdate(sql);
        System.out.println(row+"行数据插入成功");
        JDBCUtils.close(null,stat,conn);
            }
}

在sqlyog里

CREATE TABLE users(
  uid INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20),
  PASSWORD VARCHAR(20)
);
INSERT INTO users VALUES(1,'jack','1234');
INSERT INTO users VALUES(2,'rose','5678');
-- 可以查到密码
SELECT * FROM users WHERE username='jack' 
                    AND PASSWORD='123' OR '1=1';

模拟用户登录案例

package com.dbsy.login;

import com.dbsy.utils.JDBCUtils;

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

public class Demo01Login {
    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入你的用户名");
        String username = sc.nextLine();
        System.out.println("请输入你的密码");
        String password = sc.nextLine();
        Connection conn = JDBCUtils.getConnection();
        Statement stat = conn.createStatement();
        String sql="select * from users where username='"+username+"' and password='"+password+"';";
        ResultSet rs = stat.executeQuery(sql);
        if (rs.next()){
            String username1 = rs.getString("username");
            String password1 = rs.getString("password");
            System.out.println("恭喜您登录成功:"+username1+"\t"+password1);
        }else{
            System.out.println("没有查询结果,登陆失败!");
        }
        JDBCUtils.close(rs,stat,conn);
    }
}



请输入你的用户名
jack
请输入你的密码
    //可以实现通过用户名获取密码
123 ' or ' 1=1
恭喜您登录成功:jack	1234