JDBC-Learn01

JDBC学习

JDBC基本概念

快速入门

对JDBC中各个接口和类的详解

JDBC

1.概念

概念 : Java DataBase Connectivity Java数据库连接 Java语言操作数据库

JDBC本质: 其实是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口, 各个数据库厂商去实现这套接口，提供数据库驱动jar包,我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类

图解实例

2.快速入门

步骤:

导入驱动jar包-mysql-connector-java-5.1.37-bin.jar

在项目目录下创建libs目录然后复制mysql-connector-java-5.1.37-bin.jar到项目的lib目录下.

选择导入的jar包-->Add As Library

注册驱动

获取数据库连接对象 Connection

定义sql

获取执行sql语句的对象 Statement

执行sql 接受返回结果

处理结果

释放资源

代码:

package com.levizhao.demo01.mysql;

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

public class Demo01_JDBC {
   public static void main(String[] args) throws Exception {

       //1.导入驱动jar包
       //2.注册驱动
       Class.forName("com.mysql.jdbc.Driver");
       //3.获取数据库连接对象
       //Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db4", "root", "root");
       Connection conn = DriverManager.getConnection("jdbc:mysql:///db4", "root", "root");
       //4.定义sql语句
       String sql = "update account set balance = 50000 where id";
       //5.获取执行sql的对象Statement
       Statement stmt = conn.createStatement();
       //6.执行sql
       int count = stmt.executeUpdate(sql);
       //7.处理结果
       System.out.println(count);
       //8.释放资源
       stmt.close();
       conn.close();


   }

}

3.详解各个对象

DriverManager:驱动管理对象

功能:

驱动注册:告诉程序该使用哪一个数据库驱动jar

//static void registerDriver(Driver driver)：注册与给定的驱动程序DriverManager
    
//写代码使用:Class.forName("com.mysql.jdbc.Driver");

//通过查看源码发现:在com.mysql.jdbc.Driver类中存在静态代码块
    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }

//注意：mysql5之后的确定jar包可以省略注册驱动的步骤.

获取数据库连接:

/*
* 方法：static Connection getConnection(String url,String user, String password)
* 参数：
	url：指定连接路径
		*语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
		*例子：jdbc:mysql://localhost:3306/db3
		*细节: 如果连接的是本地的mysql服务器,并且端口是3306,则url可以简写为：jdbc:mysql:///数据库名称
			
	user:数据库用户名
	password:数据库密码


*/

Connection:数据库连接对象

1.功能:

获取执行sql的对象
- Statement createStatement()
- PreparedStatement prepareStatement(String sql)
. 管理事务:
- 开启事务：setAutoCommit(boolean autoCommit):调用该方法设置参数为false, 即开启事务
- 提交事务:Commit()
- 回滚事务:rollback()

. Statement: 执行sql的对象

执行sql

boolean execute(String sql):可以执行任意的sql

int executeUpdate(String sql):执行DML(insert、update、delete)语句、DDL(create、alter、drop)语句

// 返回值：影响的行数,可以通过这个影响的函数判断DML语句是否执行成功 返回值>=0的则执行成功,反之失败

代码1

package com.levizhao.demo01.mysql;

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

/**
 *  account 表 添加一条记录 insert语句
 */

public class Demo02_JDBC {
    public static void main(String[] args) {
            Statement stmt = null;
            Connection conn = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //2.定义sql
            String sql = "insert into account values(null,'赵增印',60000)";
            //3.获取Connection对象
                conn = DriverManager.getConnection("jdbc:mysql:///db4", "root", "root");
            //4.获取执行sql的对象Statement
                stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql); //影响的行数
            //6.处理结果
            System.out.println(count);
            if(count>0){
                System.out.println("Add Sucessed");
            }else{
                System.out.println("Add Faild");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //stmt.close();
            //避免空指针异常
            //7.释放资源
            if (stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

代码2

package com.levizhao.demo01.mysql;

import java.sql.*;

/**
 * account 修改表记录
 */
public class Demo03_JDBC {
    public static void main(String[] args) {
            Connection conn = null;
            Statement stat = null;


        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获取Connection对象
            conn = DriverManager.getConnection("JDBC:mysql:///db4?useSSL=false","root","root");
            //3. 获取执行sql对象Statement
            stat = conn.createStatement();
            //4. 定义要执行的sql语句
            String sql = "UPDATE account SET  NAME=\"李四\",balance = 35000 WHERE id =2;";
            //5. 获取执行sql语句返回的值
            int count = stat.executeUpdate(sql);

            //6. 如果count大于等于0说明已经成功执行sql语句,如果小于0则说明sql语句没有执行
            System.out.println(count);
            if (count>=0){
                System.out.println("Data had modified");
            }else{
                System.out.println("Data no change");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {

            //7. 关闭conn和stat,释放资源.
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (stat != null){
                try {
                    stat.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
}

代码3

package com.levizhao.demo01.mysql;

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

/**
 *  account表 删除一条记录
 */

public class Demo04_JDBC {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stat = null;


        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql:///db4?useSSL=false","root","root");
            //3. 获取执行sql对象Statement
            stat= conn.createStatement();
            //4. 定义要执行的sql语句
            String sql = "DELETE FROM account WHERE  id =4";
            //5. 获取执行sql语句返回的值
            int i = stat.executeUpdate(sql);
            System.out.println(i);

            //6. 如果i大于等于0说明已经成功执行sql语句,如果小于0则说明sql语句没有执行
            if (i>-0){
                System.out.println("The record has been deleted");
            }else{
                System.out.println("The record no change");
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            //7. 关闭conn和stat,释放资源.
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (stat != null){
                try {
                    stat.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
}

ResultSet executeQuery(String sql): 执行DQL(select)语句

. ResultSet:结果级对象,封装查询结果

boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true
getXXX(参数):获取数据
- xxx：代表数据类型如: int getInt(), String getString()
- 参数：
  1. int ：代表列的编号,从1开始如:getInt(1)
  2. String: 代表列名称. 如：getDouble("balance")
注意：
- 使用步骤：
  1. 游标向下移动一行
  2. 判断是否有数据
  3. 获取数据

// 循环判断游标释放是最后一行末尾
while(rs.next()){
   int id = rs.getInt("id");
   String name = rs.getString("name
   boolean gender = rs.getBoolean("gender");
   Date birthday = rs.getDate("birthday");
System.out.println("编号: "+id+", 姓名: "+name+", 性别: "+gender+", 生日: "+birthday);
}

代码

package com.levizhao.demo03.mysql;

import java.sql.*;

public class demo01_JDBC {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql:///db4?useSSL=false";
            String username = "root";
            String password = "root";
            conn = DriverManager.getConnection(url, username, password);
            stat = conn.createStatement();
            String sql = "select * from person";
            resultSet = stat.executeQuery(sql);

            while(resultSet.next()){
              int id = resultSet.getInt("id");
              String name = resultSet.getString("name");
              int age = resultSet.getInt("age");
              double score = resultSet.getDouble("score");
              String birthday = resultSet.getString("birthday");
              String insert_time = resultSet.getString("insert_time");

                System.out.println("id="+id+", "+"name="+name+", "
                +"age"+age+", "+"score="+score+", "+"birthday="+birthday+", "+
                        "insert_time="+insert_time
                );

            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (stat != null){
                try {
                    stat.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

练习：

定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。
1. 定义Emp类
2. 定义方法 public List findAll(){}
3. 实现方法 select * from emp;

Emp.java类

// 查询emp表的数据将其封装为对象,然后打印

package com.levizhao.domain;

public class Emp {
    private int id;
    private String name;
    private int age;
    private String sex;
    private String address;
    private int math;
    private int english;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getMath() {
        return math;
    }

    public void setMath(int math) {
        this.math = math;
    }

    public int getEnglish() {
        return english;
    }

    public void setEnglish(int english) {
        this.english = english;
    }

    @Override
    public String toString() {
        return "emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", math=" + math +
                ", english=" + english +
                '}';
    }

    public void setEname(String ename) {
    }
}

打印代码

package com.levizhao.demo03.mysql;
import com.levizhao.domain.Emp;
/**
 * * 定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。
 */

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test01_JDBC {
    public static void main(String[] args) {
        List all = new Test01_JDBC().findAll();
        System.out.println(all);
        System.out.println(all.size());

    }

    /**
     * 查询所有emp对象
     * @return
     */
    public List findAll(){
        Connection conn = null;
        Statement stat = null;
        ResultSet resultSet =null;
        List<Emp> list = null;

        try {
             //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql:///db4?useSSL=false";
            String username = "root";
            String password = "root";
            //2.获取连接
            conn = DriverManager.getConnection(url, username, password);	//4.获取执行sql的对象
            stat = conn.createStatement();
            //3.定义sql
            String sql = "select * from people";
            //5.执行sql
            resultSet = stat.executeQuery(sql);
            //6.遍历结果集，封装对象，装载集合
            Emp emp = null;
            list = new ArrayList<Emp>();
            while(resultSet.next()){
                //获取数据
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String sex= resultSet.getString("sex");
                int math = resultSet.getInt("math");
                int english = resultSet.getInt("english");
				// 创建emp对象,并赋值
                emp = new Emp();
                emp.setId(id);
                emp.setName(name);
                emp.setAge(age);
                emp.setSex(sex);
                emp.setMath(math);
                emp.setEnglish(english);
                //装载集合
                list.add(emp);


            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            if (stat != null){
                try {
                    stat.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }
}

抽取JDBC工具类:JDBCUtils

简化书写

分析：

注册驱动也抽取
抽取一个方法获取连接对象
抽取一个方法释放资源

驱动抽取

1.创建jdbc.properties文件

url=jdbc:mysql:///d3
user=root
password=root
driver=com.mysql.jdbc.Driver

2.创建JDBCUtil.java文件

package com.levizhao.util;

/**
 * JDBC工具类
 */
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {

    private  static String url;
    private  static String username;
    private  static String password;
    private  static String driver;
    
    /**
     * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
     */

    static {
          //读取资源文件，获取值。
        try {
              //1. 创建Properties集合类。
            Properties pro = new Properties();
            //获取src路径下的文件的方式--->ClassLoader 类加载器
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL resource = classLoader.getResource("jdbc.properties");
            String path = resource.getPath();
            // 需要对路径进行转码，路径中有特殊字符或者空格如果不转码的话会获取失败
            String ConfigPath= URLDecoder.decode(path, "utf-8");
            pro.load(new FileReader(ConfigPath));
			// 获取数据，赋值
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
			// 注册驱动
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public  static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);

    }

    /**
     *
     * @param stmt
     * @param conn
     */

    public  static  void  close(Statement stmt, Connection conn){
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     *
     * @param rs
     * @param stmt
     * @param conn
     */

    public static void close(ResultSet rs, Statement stmt, Connection conn){
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        if (conn != null){
            try {
               conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (stmt!= null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

代码

package cn.itcast.jdbc;

import cn.itcast.domain.Emp;
import cn.itcast.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * * 定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。
 */
public class JDBCDemo8 {

    public static void main(String[] args) {
        List<Emp> list = new JDBCDemo8().findAll2();
        System.out.println(list);
        System.out.println(list.size());
    }
    

    /**
     * 演示JDBC工具类
     * @return
     */
    public List<Emp> findAll2(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Emp> list = null;
        try {
           /* //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");*/
            conn = JDBCUtils.getConnection();
            //3.定义sql
            String sql = "select * from emp";
            //4.获取执行sql的对象
            stmt = conn.createStatement();
            //5.执行sql
            rs = stmt.executeQuery(sql);
            //6.遍历结果集，封装对象，装载集合
            Emp emp = null;
            list = new ArrayList<Emp>();
            while(rs.next()){
                //获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");
                // 创建emp对象,并赋值
                emp = new Emp();
                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                //装载集合
                list.add(emp);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            /*if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }*/

            JDBCUtils.close(rs,stmt,conn);
        }
        return list;
    }

}

登录案例

package com.levizhao.demo03.jdbc;
import com.levizhao.util.JDBClogin;

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

public class Demo01_JDBC {
    public static void main(String[] args) {

        Scanner sc =  new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = sc.nextLine();
        System.out.println("请输入密码:");
        String password = sc.nextLine();

        boolean login = new Demo01_JDBC().login(username, password);

        if (login){
            System.out.println("验证通过,登录成功");
        }else {
            System.out.println("登录失败,请检查账户或密码");
        }


    }

    public  boolean login (String username, String password) {

        if (username == null || password == null){
            return  false;
        }

        Connection conn = null;
        ResultSet rs = null;
        Statement stat = null;
        try {

            conn = JDBClogin.getConnection();
            String sql = "select * from login where username = '" +username +"' and password = '"+password +"'";
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
            return  rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
           JDBClogin.close(rs,stat,conn);
        }

        return  false;
    }
}

posted @ 2021-02-01 17:15 withLevi 阅读(54) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

withLevi

CI/CD