• 手把手教你实现一个JDBC程序

    文章目录

    一、JDBC的操作步骤

    (1)导入Mysql的驱动jar包

    (2)加载驱动字符串

    (3)获取数据库的连接对象：connection

    (4)由连接对象获取语句对象：statement

    (5)由语句对象执行sql语句得到结果集对象：resultSet

    (6)遍历结果集对象

    二、对上述步骤进行java的封装

    (1)将数据库的驱动字符串、连接字符串、用户名、密码等信息写入属性(资源文件)文件中

    mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mvc?useSSl=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
mysql.user=root
mysql.password=123456

    • 1
    • 2
    • 3
    • 4

    (2)在Java的源程序中读取资源文件

    代码如下（示例）：

        static {//静态初始化器
        properties = new Properties();
        ClassLoader classLoader = DBMySQL.class.getClassLoader();
        InputStream is = classLoader.getResourceAsStream("jdbc.properties");
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    (3)定义类：将获取数据库的连接对象、语句对象;关闭这两个对象的方法进行封装

    package com.tools;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBMySQL {
    private static Properties properties = null;
    private Connection connection = null;

    static {//静态初始化器
        properties = new Properties();
        ClassLoader classLoader = DBMySQL.class.getClassLoader();
        InputStream is = classLoader.getResourceAsStream("jdbc.properties");
        try {
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //关闭数据库连接对象
    public void releaseConnection(){
        try {
            if(connection!=null)
            {
            connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //定义方法获得数据库的连接对象
    public Connection getMysqlConnection(){
        String driver = properties.getProperty("mysql.driver").trim();
        String url = properties.getProperty("mysql.url").trim();
        String user = properties.getProperty("mysql.user").trim();
        String password = properties.getProperty("mysql.password").trim();
        try {
            //加载驱动
            Class.forName(driver);
            if(connection ==null){
                //连接数据库，获取连接对象
                connection = DriverManager.getConnection(url,user,password);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56

    (4)创建实体类：一个实体类对应数据库中的一张表,表的列就是实体类的属性

    package com.tools;

public class Student {
    private  String sid;
    private  String sname;
    private Integer sage;
    private String sex;

    public Student(String sid, String sname, Integer sage, String sex) {
        this.sid = sid;
        this.sname = sname;
        this.sage = sage;
        this.sex = sex;
    }
    public Student() {
    }

    @Override
    public String toString() {
        return
                "学号='" + sid + '\'' +
                ", 姓名='" + sname + '\'' +
                ", 年龄=" + sage +
                ", 性别='" + sex + '\'' +
                '}';
    }

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public Integer getSage() {
        return sage;
    }

    public void setSage(Integer sage) {
        this.sage = sage;
    }

    public String getSex() {
        return sex;
    }

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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60

    (5)定义接口方法

    import com.tools.Student;
import java.util.List;
public interface IStudentDao {
    public boolean insert(Student s);
    public boolean delete (String id);
    public boolean update(Student s);
    public List<Student> findAll();
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    (6)定义接口的实现类实现接口方法（增删改查）

    package com.impl;

import com.crud.IStudentDao;
import com.tools.DBMySQL;
import com.tools.Student;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoimpl implements IStudentDao {
    private Connection con =null;
    private PreparedStatement ps =null;
    private ResultSet rs = null;

   @Override
   public boolean insert(Student s) {
        DBMySQL dbms= new DBMySQL();
        String sql = "insert into stu values(?,?,?,?)";
        //获取数据库连接对象
        con = dbms.getMysqlConnection();
        int row =0;
        try {
            //获取语句对象，同时对sql语句进行预编译
            ps = con.prepareStatement(sql);
            ps.setString(1,s.getSid());
            ps.setString(2,s.getSname());
            ps.setString(4,s.getSex());
            ps.setInt(3,s.getSage());
            row =ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return  row > 0;
    }

    @Override
    public boolean delete(String id) {
       DBMySQL dbms = new DBMySQL();
       con = dbms.getMysqlConnection();
       int row = 0;
       String sql = "delete from stu where sid = ?";
        try {
            ps =con.prepareStatement(sql);
            ps.setString(1,id);
            row =ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return row>0;
    }

    @Override
    public boolean update(Student s) {
       DBMySQL dbms = new DBMySQL();
       con = dbms.getMysqlConnection();
       String sql = "update stu set sid =? ,age =?,gender =? where sname =?";
       int row = 0;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1,s.getSid());
            ps.setInt(2,s.getSage());
            ps.setString(3,s.getSex());
            ps.setString(4,s.getSname());
            row = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return row > 0;
    }

    @Override
    public List<Student> findAll() {
        List<Student> studentList = new ArrayList<>();
        DBMySQL dbms = new DBMySQL();
        String query_sql = "select * from stu";
        con = dbms.getMysqlConnection();//获取数据库的连接对象
        try {
            ps = con.prepareStatement(query_sql);//获取语句对象，同时对sql语句进行预编译
            rs = ps.executeQuery();

            //遍历结果集：将结果集中的每行数据封装到一个Student对象中
            while(rs.next()){
                Student s = new Student();
                s.setSid(rs.getString("sid"));
                s.setSname(rs.getString("sname"));
                s.setSage(rs.getInt("age"));
                s.setSex(rs.getString("gender"));
                studentList.add(s);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        dbms.releaseConnection();
        return studentList;
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105

    (7)进行测试

    package com.impl;

import com.crud.IStudentDao;
import com.tools.Student;
import org.junit.Before;
import org.junit.Test;
import java.util.Iterator;
import java.util.List;

public class Main {
    private IStudentDao dao  = null;
    //在执行test方法前会去执行Before里的方法
    @Before
     public void  aa() {
         dao = new StudentDaoimpl();
    }
        @Test
        public void a() {
            List<Student> list = dao.findAll();
            Iterator<Student> it = list.iterator();
            while (it.hasNext()) {
                System.out.println(it.next());
            }
        }

    @Test
    public void in(){
        IStudentDao studentDao = new StudentDaoimpl();
        Student s = new Student("s_1002","刘备",25,"男");
        boolean flag = studentDao.insert(s);
        if(flag){
            System.out.println("插入成功");
        }
        else{
            System.out.println("插入失败");
        }
    }
    @Test
    public void delete(){
            boolean flag = dao.delete("s_1002");
            if(flag){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
    }
    @Test
    public void update(){
            Student s = new Student("s_1003","张三",26,"男");
            boolean flag  = dao.update(s);
            if(flag){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
    }

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59

    (8)执行结果

    在这里插入图片描述

    学号='s_1003', 姓名='张三', 年龄=26, 性别='男'}
学号='1001', 姓名='李四', 年龄=15, 性别='男'}


    • 1
    • 2
    • 3
  • 相关阅读:
    2019ICPC南京站
    [Linux] yum安装软件
    C. Zero-Sum Prefixes Codeforces Round #833 (Div. 2)（前缀和+贪心）
    pytorch 笔记：KLDivLoss
    云原生技术实践：Kubernetes集群的部署与运维
    c++day4
    java计算机毕业设计留见二手书赠送网页系统源码+系统+数据库+lw文档+mybatis+运行部署
    人工智能知识全面讲解：特征工程
    react实现一个搜索部门(input + tree)
    【LeetCode】2022 8月 每日一题
  • 原文地址：https://blog.csdn.net/m0_59879385/article/details/127637300