笔记-JavaWeb学习之旅4

JDBC:

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

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

package cn.itcast.jdbc;

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

public class JdbcDemo1 {
    public static void main(String[] args) throws Exception{
        //导入驱动jar包
        //加载Driver类进内存，执行Driver类的静态代码块，就可以注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取数据库连接对象
        // DriverManager驱动管理对象，
        //DriverManager是一个类
        // DriverManager类中的方法getConnection ,会返回一个Connection对象
        //Connection叫做数据库连接对象
        //Connection是一个接口
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3","root","12345");
        //定义一条sql语句
        String sql = "update emp set salary = 10000 where id = 1001";
        //DriverManager.getConnection返回的是一个Connection对象，所以可以使用该对象conn调用需要的方法
        //该createStatement方法将会获得一个可以执行sql语句的对象
        //获取执行sql对象 Statement
        Statement stmt = conn.createStatement();
        //执行sql语句，返回值Int代表着有多少行执行成功，受到影响
        int count = stmt.executeUpdate(sql);
        System.out.println(count);
        stmt.close();
        conn.close();
    }
}

JDBC练习

package cn.itcast.jdbc;

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

// 通过JDBC添加一条记录
public class JdbcDemo2 {
    public static void main(String[] args){
       Statement stmt = null;
        Connection conn = null;
        try{
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //定义sql语句
        String sql = "insert into emp values(null,'张飞','男',5000,'2014-05-20',3)";
        //获取执行Connection对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","12345");
            //获取执行sql的对象
           stmt = conn.createStatement();
            //执行sql
            stmt.executeUpdate(sql);

        }catch(Exception e){
            e.printStackTrace();
        }finally{
            //避免空指针异常
            if(stmt !=null){
                try{
                    stmt.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }else{
                System.out.println("发生null指针异常");
            }

            if(conn !=null){
                try {
                    conn.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }else{
                System.out.println("null指针异常");
            }

        }
    }
}

ResultSet:结果集对象，封装查询结果

常用方法有两个：

boolean next():游标向下移动一行，判断当前行是否是最后一行末尾

getxxx(参数) xxx代表数据类型，参数代表编号，编号从1开始，也可以代表列的名称

package cn.itcast.jdbc;

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

import static javax.swing.text.html.parser.DTDConstants.NAME;

// 通过JDBC添加一条记录
public class JdbcDemo2 {
    public static void main(String[] args){
       Statement stmt = null;
        Connection conn = null;
        ResultSet rs = null;
        try{
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //定义sql语句
        String sql = "select * from emp";
        //获取执行Connection对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","12345");
            //获取执行sql的对象
           stmt = conn.createStatement();
            //执行sql
           rs = stmt.executeQuery(sql);
           //re.next的返回值是boolean类型
            while(rs.next()){
                //若为真，则获取数据
              int i= rs.getInt(1);//这个参数是编号参数
                String s = rs.getString("name"); //这个参数是一个列名称参数
                String s1 = rs.getString("gender");
                System.out.println(i+" "+s1+" "+s);
            }

        }catch(Exception e){
            e.printStackTrace();
        }finally{
            //避免空指针异常
            if(rs!=null){
                try{
                    stmt.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }else{
                System.out.println("发生null指针异常");
            }


        }
    }
}

//定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回
import java.util.Date;

/**
 * 封装Emp表数据的JavaBean
 */
public class Emp {
    private int id;
    private String ename;
    private int job_id;
    private int mgr;
    private Date joindate;
    private double salary;
    private double bonus;
    private int dept_id;


    public int getId() {
        return id;
    }

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

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public int getJob_id() {
        return job_id;
    }

    public void setJob_id(int job_id) {
        this.job_id = job_id;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getJoindate() {
        return joindate;
    }

    public void setJoindate(Date joindate) {
        this.joindate = joindate;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }


    public int getDept_id() {
        return dept_id;
    }

    public void setDept_id(int dept_id) {
        this.dept_id = dept_id;
    }


    public double getBonus() {
        return bonus;
    }

    public void setBonus(double bonus) {
        this.bonus = bonus;
    }


    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr=" + mgr +
                ", joindate=" + joindate +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }
}

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

public class JDBCDemo3 {
   public static void main(String[] args){
    List<Emp> list = new JDBCDemo3().findALL();
       System.out.println(list);
   }
   public List<Emp> findALL() {
       Connection conn = null;
       Statement stat = null;
       ResultSet rs = null;
       List<Emp> list = null;
       try {
           Class.forName("com.mysql.jdbc.Driver");
           //获取到Connection对象
           conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "12345");
           //有了Connecttion对象才会有Statement执行sql语句的对象
           stat = conn.createStatement();
           //定义sql语句
           String sql = "SELECT * FROM emp";
           //执行sql语句,返回的是结果集对象ResultSet()
           rs = stat.executeQuery(sql);
           //通过结果集对象的方法，可以获取到每一行的数据
           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  = 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 (Exception e) {
           e.printStackTrace();
       } finally {
           if (rs != null) {
               try {
                   rs.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
           if (stat != null) {
               try {
                   rs.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
           if (conn != null) {
               try {
                   rs.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }
       return list;
   }
}

通过JDBCutils工具类改进代码

package cn.itcast.jdbc;


import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
        private static String url;
        private static String user;
        private static String password;
        private static String driver;
       static {

           try{
               Properties pro = new Properties();
               //可以通过字节码来获取类加载器
               ClassLoader classLoader = JDBCUtils.class.getClassLoader();
               //getResource方法可以找到sre文件夹下的配置文件
               URL res = classLoader.getResource("jdbc.properties");
                String path = res.getPath();
                pro.load(new FileReader(path));
                url = pro.getProperty("url");
               user = pro.getProperty("user");
               password = pro.getProperty("password");
               driver = pro.getProperty("driver");
               Class.forName(driver);
           }catch(IOException e){
               e.printStackTrace();
           }catch(ClassNotFoundException e){
               e.printStackTrace();
           }
       }
    //调用方法，返回Connection对象
    public static Connection getConnection() throws SQLException{
            return DriverManager.getConnection(url,user,password);
    }

    //执行增删改需要释放Statement和Connection对象
    public static void close(Statement stmt, Connection conn){
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

    //执行查询需要释放resultset，Statement和Connection对象
    public static void close(ResultSet rs, Statement stmt, Connection conn){

        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();
            }
        }
    }
}

package cn.itcast.jdbc;

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

public class JDBCDemo3 {
   public static void main(String[] args){
    List<Emp> list = new JDBCDemo3().findALL();
       System.out.println(list);
   }
   public List<Emp> findALL() {
       Connection conn = null;
       Statement stat = null;
       ResultSet rs = null;
       List<Emp> list = null;
       try {
           conn = JDBCUtils.getConnection();
           //有了Connecttion对象才会有Statement执行sql语句的对象
           stat = conn.createStatement();
           //定义sql语句
           String sql = "SELECT * FROM emp";
           //执行sql语句,返回的是结果集对象ResultSet()
           rs = stat.executeQuery(sql);
           //通过结果集对象的方法，可以获取到每一行的数据
           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  = 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 (Exception e) {
           e.printStackTrace();
       } finally {
           JDBCUtils.close(rs,stat,conn);
       }
       return list;
   }
}

JDBC练习_登录案例

需求：通过键盘录入用户名和密码，查看数据库，判断是否登录成功

CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32),
    PASSWORD VARCHAR(32)
);
INSERT INTO USER VALUES
(NULL,'zhangsan','123'),
(NULL,'lisi','234');

package cn.itcast.newjdbc;


import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class JDBCDemo5 {
    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 flag = new JDBCDemo5().login(username,password);
        if(flag){
            System.out.println("登录成功");
        }else{
            System.out.println("用户名或密码错误");
        }
    }
    public boolean login(String username,String password){
        //若传入参数为null，自接返回false
        if(username == null|| password == null){
            return  false;
        }
        Connection conn = null;
        Statement stme = null;
        ResultSet rs = null;
        try{
            //获得数据库连接Connection
            conn=  JDBCUtils.getConnection();
            //写sql语句
            String sql = "select * from user where username = '"+username+"'and password='"+password+"'";
            //获得执行sql语句对象
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            return rs.next();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return  false;
    }
}

JDBC管理事务：

事务：一个包含多个步骤的业务操作，如果这个业务被操作被事务管理，则这多个步骤要么同时成功，要么同时失败

使用Connection对象来管理事务操作：

1.开启事务：在执行sql语句之前开启事务

2.提交事务，当所有sql都执行完提交事务

3，回滚事务，在catch中回滚事务

使用Connection对象来管理事务

数据库连接池

概念：其实就是一个容器，存放数据库连接的容器

数据库连接池c3p0的使用

步骤：

1. 导入jar包,jar包要单独一个目录
2. 定义配置文件
3. 创建核心对象
4. 获取连接

package cn.itcast.datasourcejdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Demo1 {
    public static void main(String[] args) {
        //DataSource是一个接口
        //创建数据库连接池对象
        //使用父类引用指向子类对象，多态
        DataSource ds = new ComboPooledDataSource();
        //获取连接对象
        try {
            Connection conn = ds.getConnection();
            System.out.println(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}