数据库通过与java语言连接进行操作

目录

一.java连接数据库的流程

二.增删改

三.查询

四.将查询的结果封装到一个实体类中

五.将对某张表的所有操作封装到一个类中

六.抽取一个Dao的公共父类

七.整套流程

一.java连接数据库的流程

1.首先下载用于“翻译”数据库语言的jar包

2.将该包拖入项目中，创建一个独立的包用于存放，并且解压你创建的包

3.编写代码引入该包并执行sql语句

public class Test01 {
    public static void main(String[] args)throws Exception {
        //1.加载驱动---理解为引用翻译
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接对象
       
        // jdbc:mysql 协议：数据库的种类
        // localhost:3306 端口号
        // mydb 要连接的数据库名
        // serverTimezone=Asia/Shanghai 时区
 
        String url="jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
        //数据库账号
        String user="root";
        //数据库密码
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);

        //3.获取执行sql语句的对象。
        Statement statement=connection.createStatement();

        //4.执行sql语句。增删改
        String sql="delete from t_student where id=1";
        statement.executeUpdate(sql);
    }
}

总结：

1. 加载驱动 Class.forName("com.mysql.cj.jdbc.Driver");
2. 获取连接对象 Connection connection=DriverManager.getConnection(url,u,pass);
3. 获取执行sql语句的对象. Statement statement=connection.createStatement();
4. 执行sql语句: statement.executeUpdate(sql);

二.增删改

1.增

public class Demo5 {
    protected Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    @Test
    public void insert(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
           connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai","root","123456789");
            String sql = "insert into s_stu values(null,'卡莎',1)";
            ps = connection.prepareStatement(sql);
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            try {
                if(ps!=null){
                    ps.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

注意：这里获取sql语句对象和执行sql语句的时候使用PreparedStatement而不适用Statement是因为使用Statement会出现安全注入问题

2.删

    public void delete(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai","root","123456789");
            String sql = "delete from s_stu where sid=?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1,4);
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

3.改

    public void upDate(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai","root","123456789");
            String sql = "update s_stu set c_name=?,classid=? where sid=?";//？为占位符号
            ps = connection.prepareStatement(sql);
            //为占位符赋值
            ps.setObject(1,"猪八戒");//给第一个占位符赋值为猪八戒
            ps.setObject(2,2);//给第二个占位符赋值为2
            ps.setObject(3,3);//给第三个占位符赋值为3
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(connection!=null){
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

三.查询

1.查询表的所有信息

@Test  //理解为main函数。可以独立运行。
    public void testQuery() throws Exception { //抛出异常只是为了操作方便。真正在开发时应该try--catch
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai", "root", "root");

        Statement statement = conn.createStatement();

        String sql = "select id,name,age,address from t_student";
        //执行查询sql语句 并把数据库表中记录返回到ResultSet对象中进行保存。
        ResultSet rs = statement.executeQuery(sql);

        //取出ResultSet中表的记录。rs.next() 判断指针是否可以移动。如果可以移动则返回true,否则返回false
        while (rs.next()) {
            int id = rs.getInt("id"); //指针移动并获取指定列的值。
            String name = rs.getString("name");
            String address=rs.getString("address");
            int age=rs.getInt("age");
            System.out.println("id:"+id+";name:"+name+";age:"+age+";address:"+address);
        }

    }

2.根据条件查询

    @Test
    public void testQueryByCondition() throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai", "root", "root");

        Statement statement = conn.createStatement();
        String sql="select * from t_student where id=3";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            int id = rs.getInt("id"); //指针移动并获取指定列的值。
            String name = rs.getString("name");
            String address=rs.getString("address");
            int age=rs.getInt("age");
            System.out.println("id:"+id+";name:"+name+";age:"+age+";address:"+address);
        }


    }

四.将查询的结果封装到一个实体类中

概述：

1. 把数据库中每张表封装成一个类---简称实体类。
2. 把表中的列封装成实体类中的属性。
3. 表中的每条记录封装成实体类的对象。
4. 表中多条记录封装成集合ArrayList。

（1）.首先创建一个实体类

//类名跟表名对应
public class Dept {
//属性跟表的字段名对应
    int id;
    String name;

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

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + ''' +
                '}';
    }
}

（2）.查询表中所有记录并完成封装

@Test
    public void testSelect() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "select * from tb_dept"; //因为这里没有占位符 所以可以不用为占位符赋值
        PreparedStatement ps= connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        List list = new ArrayList<>(); //集合存放数据库表中所有记录
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            //创建一个实体类对象 存储表中的一条记录。
            Dept d = new Dept();
            //将查询到的记录放入对象中
            d.setId(id);
            d.setName(name);
            //因为是多条记录所以将对象中的多条记录放入集合中
            list.add(d);
        }
        System.out.println(list); //正常再开发中应该把该对象list返回给前端。
    }

（3）.根据条件查询表中信息并且完成封装

    @Test
    public void testSelectOne() throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "select * from tb_dept where id=?"; //这里根据id查询的结果一定是一条记录。因为id是主键。
        PreparedStatement ps= connection.prepareStatement(sql);
        ps.setInt(1,9);

        ResultSet rs = ps.executeQuery();
        Dept d=null;//创建实体类对象。
        while (rs.next()){
            d =  new Dept();
            //将查询到的记录放入对象中
            int id = rs.getInt("id");
            String name = rs.getString("name");
            d.setId(id);
            d.setName(name);
        }

        System.out.println(d); //以后可以把d返回给前端调用者

    }

五.将对某张表的所有操作封装到一个类中

概述：因为我们想要对某一张表进行操作时，不可能没次操作都要写一遍该操作的代码，所一我们将其封装到一个类中，使用时调用该方法即可，该类的类名一般以Dao为后缀。

public class DeptDao {
 
    private String driverName="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
    private String user="root";
    private String password="root";
 
    //根据id查询信息
    public Dept findOne(int id) throws Exception{
        Class.forName(driverName);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "select * from tb_dept where id=?"; //这里根据id查询的结果一定是一条记录。因为id是主键。
        PreparedStatement ps= connection.prepareStatement(sql);
        ps.setInt(1,id);
 
        ResultSet rs = ps.executeQuery();
        Dept d=null;//声明部门实体类对象。
        while (rs.next()){
            d =  new Dept(); //因为进入该语句表示从数据库中查询到相应的记录了。
            d.setId(rs.getInt("id"));
            d.setName(rs.getString("name"));
        }
        return d;
    }
 
    //查询操作--查询所有信息
    public List findAll() throws Exception{
        List list=new ArrayList<>();
        Class.forName(driverName);
        Connection connection = DriverManager.getConnection(url,user,password);
        String sql= "select * from tb_dept";
        PreparedStatement ps=connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
 
        while (rs.next()){
            //创建一个实体类对象 存储表中的一条记录。
            Dept d = new Dept();
            d.setId(rs.getInt("id"));
            d.setName(rs.getString("name"));
            list.add(d);
        }
        return list;
    }
 
    //增加操作
    public void insertDept(Dept dept) throws Exception{ //把前端输入得部门信息封装到相应得实体类。
        Class.forName(driverName);
        Connection connection = DriverManager.getConnection(url,user,password);
        String sql = "insert into tb_dept values(null,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1,dept.getName());
        ps.executeUpdate();
    }
 
    //删除操作--根据id删除
    public void delete(int id)throws Exception{
        Class.forName(driverName);
        Connection connection = DriverManager.getConnection(url,user,password);
        String sql = "delete from tb_dept where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1,id);
        ps.executeUpdate();
    }
   //修改
   public void upDate(Dept dept)throws Exception{
       Class.forName(driverName);
       Connection connection = DriverManager.getConnection(url,user,password);
       String sql = "update tb_dept set name="?" where id=?";
       PreparedStatement ps = connection.prepareStatement(sql);
       ps.setObject(1,dept.getName());
       ps.setObject(2,dept.getId());
       ps.executeUpdate();
  }
}

六.抽取一个Dao的公共父类

概述：就是将上面操作类中所有公共的部分抽取出来放进父类中，方便维护。

public class BaseDao {
    //公共属性
    private String driverName="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/my0426?serverTimezone=Asia/Shanghai";
    private String user="root";
    private String password="001024";
    protected Connection connection=null;
    protected PreparedStatement preparedStatement=null;
    protected ResultSet resultSet=null;
    //获取连接对象方法
    public Connection getConnection()throws Exception{
        Class.forName(driverName);
        connection= DriverManager.getConnection(url,user,password);
        return connection;
    }
    //关闭资源方法
    public void closeAll(){
        try {
            if (resultSet!=null) {
                resultSet.close();
            }
            if (preparedStatement!=null){
                preparedStatement.close();
            }
            if (connection!=null){
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //增删改的方法
    public void edit(String sql,Object ... params){
        try {
            connection=getConnection();
            preparedStatement=connection.prepareStatement(sql);
            for (int i=0;i 
此时操作类就变成了这个样子：
 
public class EmpDao extends BaseDao{
    //添加操作
    public void empInsert(Emp emp){
        String sql="insert into tb_emp values(null,?,?,?,?,?,?,?)";
        edit(sql,emp.getName(),emp.getAge(), emp.getJob(),
                emp.getSalary(),emp.getEntryDate(),emp.getManagerId(),emp.getDeptId());
    }
    //删除操作
    public void empDelete(int id){
        String sql="delete from tb_emp where id=?";
        edit(sql,id);
    }
    //修改操作
    public void empUpdate(Emp emp){
        String sql="update tb_emp set name=?,age=?,job=?,salary=?," +
                "entryDate=?,managerId=?,dept_id=? where id=?";
        edit(sql,emp.getName(),emp.getAge(), emp.getJob(), emp.getSalary(),
                emp.getEntryDate(),emp.getManagerId(),emp.getDeptId(),emp.getId());
    }
    //根据id查询
    public Emp empFineOne(int id){
        Emp emp =null;
        try {
            connection=getConnection();
            String sql="select * from tb_emp where id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setObject(1,id);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                emp=new Emp();
                emp.setId(resultSet.getInt("id"));
                emp.setName(resultSet.getString("name"));
                emp.setAge(resultSet.getInt("age"));
                emp.setJob(resultSet.getString("job"));
                emp.setSalary(resultSet.getInt("salary"));
                emp.setEntryDate(resultSet.getDate("entryDate"));
                emp.setManagerId(resultSet.getInt("managerId"));
                emp.setDeptId(resultSet.getInt("dept_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return emp;
    }
    //查询所有
    public List empFineAll(){
        List list = new ArrayList<>();
        try {
            connection=getConnection();
            String sql="select * from tb_emp";
            preparedStatement=connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                Emp emp = new Emp();
                emp.setId(resultSet.getInt("id"));
                emp.setName(resultSet.getString("name"));
                emp.setAge(resultSet.getInt("age"));
                emp.setJob(resultSet.getString("job"));
                emp.setSalary(resultSet.getInt("salary"));
                emp.setEntryDate(resultSet.getDate("entryDate"));
                emp.setManagerId(resultSet.getInt("managerId"));
                emp.setDeptId(resultSet.getInt("dept_id"));
                list.add(emp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }
}
 
七.整套流程 
1.实体类：
 
public class TbEmp {
    int id;
    String name;
    int age;
    String job;
    int salary;
    Date entrydate;
    int managerid;
    int  deptId;

    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 getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getSalary() {
        return salary;
    }

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

    public Date getEntrydate() {
        return entrydate;
    }

    public void setEntrydate(Date entrydate) {
        this.entrydate = entrydate;
    }

    public int getManagerid() {
        return managerid;
    }

    public void setManagerid(int managerid) {
        this.managerid = managerid;
    }

    public int getDeptId() {
        return deptId;
    }

    public void setDeptId(int deptId) {
        this.deptId = deptId;
    }

    @Override
    public String toString() {
        return "TbEmp{" +
                "id=" + id +
                ", name='" + name + ''' +
                ", age=" + age +
                ", job='" + job + ''' +
                ", salary=" + salary +
                ", entrydate=" + entrydate +
                ", managerid=" + managerid +
                ", deptId=" + deptId +
                '}';
    }
}
 
2.父类：
 
public class BaseDao02 {
//公共属性
    List list = null;
    protected Connection connection = null;
    protected PreparedStatement ps = null;
    protected ResultSet rs = null;
    protected String s = "com.mysql.cj.jdbc.Driver";
    protected String url = "jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai";
    protected String user = "root";
    protected String password = "123456789";
//公共的链接对象的方法
    public void getConn() throws Exception{
        Class.forName(s);
        connection = DriverManager.getConnection(url,user,password);
    }
//公共的关闭资源的方法
    public void colos(){
        try {
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //增删改中公共的部分，只有sql语句和为占位符不同，所以将其当作参数
    public void caozuo(String sql,Object ... maras){
        try {
            getConn();
            ps = connection.prepareStatement(sql);

            for(int i=0;i< maras.length;i++){
                ps.setObject(i+1,maras[i]);
            }
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            colos();
        }
    }
}
 
3.操作类：
 
public class TbEmpDao extends BaseDao02 {
    //增
    public void add(TbEmp tbe){
        String sql = "insert into tb_emp values(null,?,?,?,?,?,?,?)";
        caozuo(sql,tbe.getName(),tbe.getAge(),tbe.getJob(),tbe.getSalary(),tbe.getEntrydate(),tbe.getManagerid(),tbe.getDeptId());
    }
    //删除
    public void delete(int id){
        String sql = "delete from tb_emp where id=?";
        caozuo(sql,id);
    }
    //修改
    public void upDate(TbEmp tbe){
        String sql = "update tb_emp set name=?,age=?,job=?,salary=?,entrydate=?,managerid=?,dept_id=? where id=?";
        caozuo(sql,tbe.getName(),tbe.getAge(),tbe.getJob(),tbe.getSalary(),tbe.getEntrydate(),tbe.getManagerid(),tbe.getDeptId(),tbe.getId());
    }
    //查找全部信息
    public List selectAll(){
        try {
            getConn();
            String sql = "select * from tb_emp";
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            list = new ArrayList<>();
            while (rs.next()){
                TbEmp te = new TbEmp();
                te.setId(rs.getInt("id"));
                te.setName(rs.getString("name"));
                te.setAge(rs.getInt("age"));
                te.setJob(rs.getString("job"));
                te.setSalary(rs.getInt("salary"));
                te.setEntrydate(rs.getDate("entrydate"));
                te.setDeptId(rs.getInt("dept_id"));

                list.add(te);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            colos();
        }
        return list;
    }

    //根据id查询某一条信息
    public TbEmp selectOne(int id){
        TbEmp te = null;
        try {
            getConn();
            String sql = "select * from tb_emp where id=?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            te = null;
            while (rs.next()){
                te = new TbEmp();
                te.setId(rs.getInt("id"));
                te.setName(rs.getString("name"));
                te.setAge(rs.getInt("age"));
                te.setJob(rs.getString("job"));
                te.setSalary(rs.getInt("salary"));
                te.setEntrydate(rs.getDate("entrydate"));
                te.setDeptId(rs.getInt("dept_id"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            colos();
        }
        return te;
    }

}
 
4.测试类：
 
public class Text02 {
    TbEmpDao tbs = new TbEmpDao();
//因为我们无法直接为数据库中的Datelei类型赋值，所以先将日期写为String类型在转为Datel类型。
    String s = null;
    Date sqlDate = null;
//测试添加
    @Test
    public void textAdd(){
        s = "2000-01-01";
        sqlDate = Date.valueOf(s);
        TbEmp te = new TbEmp();
        te.setName("张三");
        te.setAge(20);
        te.setJob("学习");
        te.setSalary(5000);
        te.setEntrydate(sqlDate);
        te.setManagerid(2);
        te.setId(4);
        tbs.add(te);
    }
//测试删除
    @Test
    public void textDelete(){
       tbs.delete(3);
    }
//测试修改
    @Test
    public void textUpDate(){
        s="2001-03-09";
        sqlDate = Date.valueOf(s);
        TbEmp tb = new TbEmp();
        tb.setName("诸葛亮");
        tb.setAge(60);
        tb.setJob("参谋");
        tb.setSalary(50000);
        tb.setEntrydate(sqlDate);
        tb.setManagerid(1);
        tb.setDeptId(1);
        tb.setId(4);
        tbs.upDate(tb);
    }
//测试查询所有信息
    @Test
    public void textSelextAll(){
        List tbEmps = tbs.selectAll();

        for(TbEmp te: tbEmps){
            System.out.println
                    (te.getId()+" "+te.getName()+" "+te.getAge()+" "+te.getSalary()+" "+te.getEntrydate()+" "+te.getManagerid()+" "+te.getDeptId());
        }
    }
//测试根据id查询某一条信息
    @Test
    public void textSelectOne(){
        TbEmp tbEmp = tbs.selectOne(4);
        System.out.println(tbEmp);

    }
}